03 June, 2010

How to insert multiple rows in a SQL CE database

Doing some mobile application development (win CE) I was recently faced with a task, that immediately would seem trivial. Come on – how hard can it be to insert more than one row in a database? Well – when the database you are dealing with is a SQL Compact Edition (v. 3.5); you are in for a surprise (or at least I was ;-)).

The default access to the database is a table adapter, which is a very downscaled version compared to the Entity Framework you are used to and take for granted by now. The table adapter unfortunately only allows for single row inserts; which means you are to call this multiple times by default. That does not work well, if you are having more than say 10 rows you want inserted! I was attempting this model at first with approx. 47.000 rows and ended up with a duration of 2.5 hours! So – what do do?

Well – an alternative route exists that brought the duration dramatically down (2.5h –> 6 sec!) for the same number of rows.

By using the below code, you are utilizing a direct access to the SQL CE table and gain a significant performance boost compared to the default table adapter. Do note that when using this route, you do no longer have the luxury of fieldnames, but are limited to using their ordinal places in the table. Note that the ordinal value is the same value you will find in a create table statement for the table you are targeting (in the below example: ‘OnlineProducts’).

Note also in the below code, that the same SqlCeUpdatableRecord (variable: ‘rec’) is used over and over again in the foreach loop. This means no memory optimization is performed by the CLR, which is intentional; before the method is exited.

/// <summary>
///
Inserts/updates rows.
/// </summary>
/// <param name="newRows">
The new rows.</param>
public void InsertRows(ProductDataSet.OnlineProductRow[] newRows)
{
//insert using direct sql
var cnn = new SqlCeConnection(GetSDFConnection());
var cmd = cnn.CreateCommand();

try
{
cnn.Open();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "OnlineProducts"; //tablename!

var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
var rec = rs.CreateRecord();

//update records (use same row)
foreach (var row in newRows)
{
try
{
rec.SetString(0, row.ProductName);
rec.SetString(1, row.ProductUnit);
rec.SetFloat(2, row.StandardPrice);
rec.SetDateTime(3, row.Updated);
rec.SetString(4, row.ProductNr);
rec.SetString(5, row.ProductNameLong ?? "");
rec.SetValue(6, row.Image);
rec.SetString(7, row.AlternativeProduct ?? string.Empty);
rec.SetString(8, row.EAN);
//rec.SetInt32(9, row.OnlineProductID);
rec.SetDouble(10, row.SalesMultiplier);

rs.Insert(rec);
}
catch (Exception ex)
{
CELogger.LogException(ex);
}
}

rs.Close();
rs.Dispose();
}
finally
{
cmd.Dispose();
cnn.Close();
cnn.Dispose();
}
}
Update: You might want to initialize the fields of the record before doing the inserts. Otherwise you end up "inheriting" the old values from last iteration :-)

7 comments:

Anonymous said...

nice article..
just one question in my case this is not working

rec.SetFloat = (5, float.Parse(dRow[4].ToString()));

Claus Konrad said...

Well - the rec.SetFloat is a method; not a property. You can't use it in the way you shown.

You need to go:
rec.SetFloat(5, <value>);

Anonymous said...

thanx for ur response

in my case I am reading a data from sql2005 server and writing it in sqlce.

in sql2005 its a decimal field which I have read and stored in dataset.Now sqlce doesnot have a decimal datatype so i have to use float for this one.I am using ur example code for insertion.

here when I try to parse the decimal value into float and try to insert with SetFloat its showing error.attaching the code

public static bool InsertRowsDepreciationBook(DataSet newRows)
{
//insert using direct sql
SqlCeConnection cnn = new SqlCeConnection(str_sqlConnection);
SqlCeCommand cmd = cnn.CreateCommand();
try
{
cnn.Open();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "FA Depreciation Book"; //tablename!
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = rs.CreateRecord();

//update records (use same row)
foreach (DataRow row in newRows.Tables[0].Rows)
{
try
{
rec.SetString(0,row[0].ToString().Replace("'", "''"));//No
rec.SetString(1,row[1].ToString().Replace("'", "''"));//Depreciation Book Code
rec.SetString(2,row[2].ToString().Replace("'", "''"));//Depreciation Method
rec.SetDateTime(3,Convert.ToDateTime(row[3]));//Depreciation Start Date
floatflNoofYear = decimal.Parse(row[4].ToString()); //dep year
float flNoofMonth = float.Parse(row[5].ToString());
rec.SetFloat(4,float.Parse(row[4].ToString()));//No.of Depreciation year
rec.SetFloat(5, flNoofMonth);//No.of Depreciation months
rec.SetDateTime(6,Convert.ToDateTime(row[7]));//Depreciation ending Date
rec.SetFloat(7,float.Parse("0"));//Acquisition cost
rec.SetString(8,row[8].ToString().Replace("'", "''"));//Voucher No
rec.SetString(9,row[9].ToString().Replace("'", "''"));//Bill of entry
rec.SetString(10,row[10].ToString().Replace("'", "''"));//CustomPassbook no
rec.SetString(11,row[11].ToString().Replace("'", "''"));//Invoice No
rec.SetDateTime(12,Convert.ToDateTime(row[12]));//Invoice Date
rec.SetDateTime(13,Convert.ToDateTime(row[13]));//Voucher Date
rec.SetFloat(14,float.Parse(row[14].ToString()));//Asset Value
rec.SetDateTime(15,Convert.ToDateTime(row[6]));//Disposal date
rs.Insert(rec);
}
catch (Exception ex)
{
return false;
}
}
rs.Close();
rs.Dispose();
GC.Collect();
return true;
}

Anonymous said...

Hi ..thanks for a nice article,its help me a lot.Please answer my query i am new to this programming.

can i use SqlCeUpdatableRecord.Update for a conditional update statement like( update tbl set cost = 5.0 where FANo_ = 'F-000300')

If yes what will be syntax??

Claus Konrad said...

First of all - I think you have an awful lot of conversions in your code? Do not rely on the conversion to succeed, but make ready a collection of rows to be inserted. Then perform the insertion process as shown in the example.

Regarding the flot problem. Try insert a simple float (say '5.0') and see if that works. In positive case - your error is in the conversions; in negative case - your error is in the target table ('FA Depreciation Book', is this really a table name...?)

Omar said...

At first I tried this and saw amazing performance gains
But then I realized the performance gains were due to the connection being kept open between inserts.
If you simply remember to open your table adapter's connection beforehand and leave it open. you'll get almost the same performance benefit.
So this change is not necessary.

Claus Konrad said...

Thanks for the comments. Good point there!

InRiver: Not loading your extensions?

(You really need to in the loop to appreciate the issue this post addresses). Man, I've been fighting this problem for hours before I ...