Fatest way to insert data in Sql Server CE

Question

I have been working on C# Compact Framework 4.0 for my product.

I have an server and an mobile and i have to synchronize all the data from the server to my mobile device .

Usually around 6000 entries will be inserted into my mobile device on an single table .

Now i use table direct with index name of the table specified .

It takes around 55 sec for 6000 entries.

Is there an faster way to insert ??

SqlCeCommand cmdItem = conn.CreateCommand();
SqlCeResultSet rsItem;
cmdItem.CommandText = "item_info";
cmdItem.IndexName = "PK_item_info";
cmdItem.CommandType = CommandType.TableDirect;
rsItem = cmdItem.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while(reader.read()){

    SqlCeUpdatableRecord recItem = rsItem.CreateRecord();
    recItem.SetString(1, cmdItem.SerialNo);
    recItem.SetInt32(10, 0);
    rsItem.Insert(recItem);  

}

Answer 1

Using this library you can use SqlBulkCopy (SQLBulkCopy Class (Microsoft website)) with your SQL Server CE database.

Library which supports SQL Server CE

Answer 2

Remove " | ResultSetOptions.Scrollable"

And move the line:

SqlCeUpdatableRecord recItem = rsItem.CreateRecord();

Above the while loop.

var cmdItem = conn.CreateCommand();
cmdItem.CommandText = "item_info";
cmdItem.CommandType = CommandType.TableDirect;
var rsItem = cmdItem.ExecuteResultSet(ResultSetOptions.Updatable);
var recItem = rsItem.CreateRecord();
while(reader.read())
{        
    recItem.SetString(1, cmdItem.SerialNo);
    recItem.SetInt32(10, 0);
    rsItem.Insert(recItem);
}