Failed to batch insert in Subsonic3 with error "Must declare the scalar variable..."

Question

I have met a problem about inserting multiple rows in a batch with Subsonic3. My development environment includes:

1. Visual Studio 2010, but use .NET 3.5
2. Active Record Mode in SubSonic 3.0.0.4
3. SQL Server 2005 express
4. Northwind sample database

I am using Active Reecord mode to insert mutiple "Product" into table "Products". If I insert the rows one by one, either call "aProduct.Add()" or call "Insert.Execute()" mutiple times (just like the codes below), it works fine.

        private static Product[] CreateProducts(int count)
        {
            Product[] products = new Product[count];
            for (int index = 0; index < products.Length; ++index)
            {
                products[index] = new Product
                {
                    ProductName = string.Format("cheka-test-{0}", index.ToString()),
                    Discontinued = (index % 2 == 0),                        
                };
            }
            return products;
        }
        private static void SucceedByMultiExecuteInsert()
        {
            Product[] products = CreateProducts(2);

            // -------------------------------- prepare batch
            NorthwindDB db = new NorthwindDB();

            var inserts = from prod in products
                          select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued);

            // -------------------------------- batch insert
            var selectAll = Product.All();
            Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString());

            foreach (Insert insert in inserts)
                insert.Execute();

            Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
                products.Length.ToString(), selectAll.Count().ToString());
        }

but if I use "BatchQuery" like the codes below,

    private static void FailByBatchInsert()
    {
        Product[] products = CreateProducts(2);

        // -------------------------------- prepare batch
        NorthwindDB db = new NorthwindDB();
        BatchQuery batchquery = new BatchQuery(db.Provider, db.QueryProvider);

        var inserts = from prod in products
                      select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued);

        foreach (Insert insert in inserts)
            batchquery.Queue(insert);

        // -------------------------------- batch insert
        var selectAll = Product.All();
        Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString());

        batchquery.Execute();

        Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
            products.Length.ToString(), selectAll.Count().ToString());
    }

then it failed with the exception : " Unhandled Exception: System.Data.SqlClient.SqlException: Must declare the scalar variable "@ins_ProductName". Must declare the scalar variable "@ins_ProductName". "

Please give me some help to solve this problem. Many thanks.

Answer 1

I ran into this problem as well. If you look at the query it's attempting to run, you'll see it doing something like this (this isn't actual code but you'll get the point):

exec_sql N'insert into MyTable (SomeField) Values (@ins_SomeField)',N'@0 varchar(32)','@0=SomeValue'

For some reason it defines the parameters in the query with "@ins_"+FieldName but then passes the parameters as ordinals. I have yet to determine the pattern for why/when it does this but I've lost enough time during this dev cycle futzing with SubSonic to try and diagnose the problem properly.

The work-around I implemented will involve you downloading the 3.0.0.4 source from github and making a change on line 179 of Insert.cs.

Where it reads

ParameterName = _provider.ParameterPrefix + "ins_" + columnName.ToAlphaNumericOnly(),

Changing it to

ParameterName = _provider.ParameterPrefix + Inserts.Count.ToString(),

seemed to do the trick for me. I make no warranties about this solution for you, expressed or implied. It did work for me but your mileage may vary.

I should also note that there's similar logic around the "update" statements as well in Update.cs on lines 181 and 194 but I haven't had these give me problems... yet.

Honestly, I don't think SubSonic is ready for primetime and that's a shame because I really like how Rob set it up. That said, it's in my product for better or worse now so you make the best with what you got.