Sql CE Inconsistent with Multiple Statements

dapper sql-server-ce

Question

It has long been true that you can execute multiple statements with SQL CE. And in fact I'm using SQL Server Compact Toolbox to do exactly that. But when I take the same multiple-statement commands and execute them from Dapper...

public const string SampleDml = @"
   INSERT INTO [Plugin](Name, TypeName) VALUES ('Blog','Shroom.Blog');
   GO
   INSERT INTO [ContentDef](PluginID, Name, Placement, IsStatic) VALUES(@@IDENTITY,'MyBlog','Layout:Left',1);
   GO
";

I then keep getting this error:

There was an error parsing the query. [ Token line number =3, Token line offset = 1, Token in error = GO ]

The version of the SQL CE library I'm using is version 4.0.0.0 (runtime version v2.0.50727). I'm using Dapper 1.12.0.0 (runtime version v4.0.30319) and Dapper Extensions 1.3.2.0 (runtime v4.0.30319).

The SQL CE library seems like the wrong run-time, but web platform installer says I have the latest (so that really is the latest?). Thoughts?

Accepted Answer

You actually can only execute a single statement per batch with SQL Server Compact, all I do (I am the SQL Server Compact Toolbox author), is split the string per GO and newline.

I have code like this:

        using (StringReader reader = new StringReader(script))
        {
            string line;
            while ((line = reader.ReadLine()) != null)
            {
                line = line.Trim();
                if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
                {
                    RunCommand(sb.ToString(), dataset);
                    sb.Remove(0, sb.Length);
                }
                else
                {
                    sb.Append(line);
                    sb.Append(Environment.NewLine);
                }
            }
        }

Popular Answer

I have created a small library to solve this problem. It splits a single command into several sub-commands (using semicolon (;) as a statement separator) and executes them one-by-one.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why