Asp – Advice on Subsonic Batch Insert Query

asp.net-mvcsubsonicsubsonic3

hi am running batch insert with subsonic 3.0.0.3 and MVC and i have wrote the following example:

var myquery1 = new Insert(provider).Into<orderitem>("orderitem_orderid", "orderitem_productid", "orderitem_qty", "orderitem_total",
                    "orderitem_sessionid", "orderitem_internal", "orderitem_measurement").Values("1", "1", "1", "0.00", "12345", "0", "1x1");
        batch.QueueForTransaction(myquery1);

        var myquery2 = new Insert(provider).Into<orderitem>("orderitem_orderid", "orderitem_productid", "orderitem_qty", "orderitem_total",
                    "orderitem_sessionid", "orderitem_internal", "orderitem_measurement").Values("2", "1", "1", "0.00", "12345", "0", "1x1");
        batch.QueueForTransaction(myquery2);

        batch.ExecuteTransaction();

this works and all is fine, however does anyone know how i can get away with not listing all the column names, but instead have some reference to the subsonic class for the table ???? and then simply have my values in the correct order.

the above works but seems a bit untidy, and i have much bigger tables than this too.

many thanks

Best Answer

It sounds like you want to be able to do something like:

var myquery1 = new Insert(provider).Into<orderitem>().Values("1", "1", "1", "0.00", "12345", "0", "1x1");

batch.QueueForTransaction(myquery1);

However this won't work and to be honest it's not really advisable either. The reason it won't work is that SubSonic won't know which values to map to which columns in your table. The reason I don't believe it's advisable is that any modification to the underlying tables could easily result in the values being inserted into unexpected columns and also the actual code is very opaque. It's all very well for someone with an intimate knowledge of the database but if you look at that code from a maintenance perspective it's impossible to easily tell what is actually happening.

EDIT: If you want intellisense for your column names you can do the following (check Structs.cs to see what gets generated automatically for your table/column names):

var myquery1 = new Insert(provider).Into<orderitem>(OrderItemTable.ProductIdColumn).Values("1");

batch.QueueForTransaction(myquery1);

Related Solutions

Inserting a new record pattern in SubSonic 3

First and foremost: the t4 templates are there for you to change as needed with SS3. That was the main idea with using T4 - I don't want to back you into my silliness :).

To the question at hand - I think this might be a bug in our templates that refuses to stick a value into the PK field:

    ISqlQuery BuildInsertQuery(T item) {
        ITable tbl = _db.FindTable(typeof(T).Name);
        Insert query = null;
        if (tbl != null) {
            var hashed = item.ToDictionary();
            query = new Insert(_db.Provider).Into<T>(tbl); ;
            foreach (string key in hashed.Keys) {
                IColumn col = tbl.GetColumn(key);
                if (!col.IsPrimaryKey) {
                    query.Value(key, hashed[key]);
                }
            }
        }
        return query;
    }

In this, our check should actually be...

                if (!col.IsPrimaryKey && !col.AutoIncrement) {
                    query.Value(key, hashed[key]);
                }

In this way, the non-identity will be inserted. But in reading your issue here, it sounds to me like you're not trying to insert into a non-identity.

The email you sent me doesn't say anything about PKs as identity - your PK was a thing called "NAme" which is a string type and not and identity (auto-increment).

I'm wondering about when I cannot get around this issue--when I have to add a new record to the database and have an identity as my primary key.

This is what SubSonic assumes - that your PK is an IDENTITY column. If you ONLY have an IDENTITY column, we can't help you because this is a deadlocked table in that you can't insert any value into it, therefor you can't tick the IDENTITY column. Your only recourse at this point is to SET IDENTITY INSERT="off", which defeats the purpose.

Hopefully this will answer your question? If I'm not getting it - can you do this for me:

One sentence: what can't you do and what's the error
What did you expect

Thanks Will and I hope I'm not being thick.

Asp – Subsonic 3 Union Possible

It looks like you're bumping into a bug in SubSonic's implementation of Union/Concat, you should report it to the google code site. You should just be able to do the following, which I'm pretty sure you'd already worked out:

var unionList = dd.Concat(ss).ToList<Menu>();

In the meantime the following should be pretty close to the outer join you're after:

var ss =  from menu in Menu.All()
    group join pages in WebPage.All() on menu2.PageID equals pages.ID
      into pagesMenu from pm in pagesMenu.DefaultIfEmpty()
    group join pagesRoles in PageRole.All() on pages.ID equals pagesRoles.PageID
      into pagesRolesPages from prp in pagesRolesPages.DefaultIfEmpty()
    group join roles in aspnet_Role.All() on pagesRoles.RoleId equals roles.RoleId
      into pagesRolesRoles from prr in pagesRolesRoles.DefaultIfEmpty()
  where menu.PageID == null || 
    (Roles.GetRolesForUser().Contains(roles.RoleName) && menu2.CategoryID == 6)
  select menu;

Best Answer

Related Solutions

Inserting a new record pattern in SubSonic 3

Asp – Subsonic 3 Union Possible

Related Topic