13 September 2009 ~ 4 Comments

SQLCE Insert Multiple Values

If you are a newbie at Windows Mobile development and you need to create an application which is using SQLCE database, you might have bumped into this problem. Inserting multiple records into SQLCE database is quite different from doing the same thing on full blown SQL Server.

If you are like me, a developer who likes to be in control of it’s source code you are most likely using INSERT INTO statements to add new records to your database. Doing that on a SQL Server is not a new thing and it is a perfectly normal code to insert new records into your tables. Running this query on a SQL Server would get you to three new rows in a table:

INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFirstVal, MySecondVal);
INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyThirdVal, MyFourthVal);
INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFifthVal, MySixthVal);

Or you might have been using this variant:

INSERT INTO MyTable (MyFirstCol, MySecondCol) SELECT 'MyFirstVal' , 'MySecondVal' UNION ALL SELECT 'MyThirdVal' , 'MyFourthVal' UNION ALL SELECT 'MyFifthVal' , 'MySixthVal'

SQL Server 2008 made it even easyer and all you had to do is run a query similar to this one:

INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES ('MyFirstVal', 'MySecondVal'), ('MyThirdVal', 'MyFourthVal'), ('MyFifthVal', 'MySixthVal')

And whichever syntax you might have been using would work. But, SQLCE is not like your regular SQL Server and running this kind of a query against SQLCE would inevitably throw an Exception like similar to this one:

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

And all of a sudden nothing makes sense. Your query is correct but SqlCeCommand is refusing to run this query on a SQLCE database!

Solution is quite simple and there are couple of them. First solution is my choice because I like writing my SQL queries. What you need to do is to create SQL INSERT INTO statement and run it against your SQLCE database. Than repeat it as many times as you need to insert all of your values. Something like this:

string MyInsert
int RowsAffected = 0;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol);
VALUES (MyFirstVal, MySecondVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyThirdVal, MyFourthVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFifthVal, MySixthVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;

While this is OK for inserting fewer values, good practice to follow when inserting hundreds or thousand records is to use parametrized queries using SqlCeCommand. The code to this would be similar to this one

int RowsAffected = 0;
SqlCeCommand MyCommand = SqlCeConnection.CreateCommand();
MyCommand.CommandText = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (@val01, @val02)";
MyCommand.Parametres.Add("@val01", "MyFirstValue");
MyCommand.Parametres.Add("@val02", "MySecondValue");
MyCommand.Parametres.Add("@val01", "MyThirdValue");
MyCommand.Parametres.Add("@val02", "MyFourthValue");
MyCommand.Parametres.Add("@val01", "MyFifithValue");
MyCommand.Parametres.Add("@val02", "MySixthValue");
RowsAffected = MyCommand.ExecuteNonQuery();

Both of these methods work just fine when executed against SQLCE database but as I mentioned it earlier, it might be reasonable to use the second example when inserting large sets of data as performance of this example is quite better than of the first example.

4 Responses to “SQLCE Insert Multiple Values”

  1. Alex 14 July 2010 at 13:57 Permalink

    This is not correct. It is not possible to insert multiple rows by simply adding parameters. Try that, and you’ll see an error with “Too many parameters…”. It’s better to remove that part of code, since it confuses people with no reason.

  2. prideaux 14 October 2010 at 22:54 Permalink

    Beginner’s mistake or what? as the previous poster has said, you are just updating parameters without doing anything, then when you execute only 1 row will be inserted.

  3. Eddie 10 October 2011 at 11:52 Permalink

    Just spent a good 10 minutes setting up that second method. Got this

    The SqlCeParameter with this name is already contained by this SqlCeParameterCollection.

    I really should have read the comments first.

  4. tobias 24 July 2012 at 11:59 Permalink

    This example is incorrect!
    Do *not* misuse the parameters as row’s.

    If this works in one case, there’s no guarantee that it would work on other!

    SQLServerCE does not support inserting multiple rows.
    See http://social.msdn.microsoft.com/Forums/en/sqlce/thread/18a6e510-ac7d-486b-8445-0da6fc47a488

Leave a Reply