Advice on Subsonic Batch Insert Query

Question

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

Answer 1

Subsonic uses the IActiveRecord pattern so if you used the T4 templates to gen objects for you then you should have classes for records for your tables.

On the active record classes its simple to just gen up a new class, set some properties and call the Add() method to effectively do what you doing in a much more concise manner.

Just make sure you have a Primary Key set on your table, that your columns are named sanely and things just "just work" for you.

Something like;

OrderItem item = new OrderItem();
item.Total = 24.32;
item.Qty = 3;
item.ItemID = 23;
item.CreatedBy = 'bjones';
item.Add();

Answer 2

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);

Answer 3

Maybe you could at least "cache" the columns you want to insert to? I forget if params string[] will let you do that or not.

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

        var myquery2 = new Insert(provider).Into<orderitem>(columnList).Values("2", "1", "1", "0.00", "12345", "0", "1x1");
        batch.QueueForTransaction(myquery2);

        batch.ExecuteTransaction();

If it does, you could also try converting the Columns struct for the table to a string array, but I don't know which 3.0 templates would generate that if any. (Still loving 2.2 and not ready to start over with 3.0)