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 :-)

46 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!

Charles Crookes said...

The intention of the new guy is improved for the designs for the citizens. The instances are fixed to write my essay service for the offers and all components for the utility for the people.

Anonymous said...

SQL database is one of the better data handling mechanisms for the users. The addition of the rows is difficult task for the untrained man. However, this training is given for the multiple additions online writing review of the rows for the managers of the database.

Myyellow said...

Nice information, keep going.

styvi said...

هذه العاب اطفال جميلة يمكن الركوب في داخلها وقيادتها بصفة حقيقيية واحترافية، تتميز أغلب هذه السيارات بأنها تشبه سيارة شرطة كبيره في الشكل والتصميم وحتى الألوان وكل ما تحتويه الشرطة من خصائص.

Unknown said...

If you are in the UK, you cannot view any pop videos due to YouTube falling out with the RIAA (or something similar) over royalties being paid to the musicians.

For more info, please visit here

https://quickseotools.net/ said...

Nice approach to do insert multiple approach in Sql

https://99market.org/ said...

Happiness depends upon ourselves.

https://trendytarzan.com/ said...

Start by doing what's necessary; then do what's possible; and suddenly you are doing the impossible.

Anonymous said...

Food is an important part of a balanced diet.

https://youtubetomp3.sc/ said...

I always wanted to be somebody, but now I realize I should have been more specific.

https://mp3convert.co/ said...

i agree with you claus konard

lindi said...

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...?) emus4u

Mark Gentleman said...

thank you so much for sharing this blog. we all should try to make these chemical less products at home to save our skin. plus its cheaper in cost and much more effective than the market products.imagenes para dibujar

Robert Kingsman said...

I can set up my new idea from this post. It gives in depth information. Thanks for this valuable information for all,.. CBD Oil

Barry Caisse said...

Really i appreciate the effort you made to share the knowledge. Read more at

John B. Tanner said...

Interestingly you write, I will address you'll find exciting and interesting things on similar topics. Visit here

Brandon Blogs said...

In this case you will begin it is important, it again produces a web site a strong significant internet site: read more

Brandon Blogs said...

Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informativeContinue Reading

Anonymous said...

i wish more writers of this sort of substance would take the time you did to investigate and compose so well. I am exceptionally awed with your vision and knowledge

Anonymous said...

I would like to say that this blog really convinced me to do it! Thanks, very good post useful link

Anonymous said...

Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well i really get amazed to read this. Its really good click to read more

Anonymous said...

Thank you for this fascinating post, I am happy I observed this website on Google. Not just content, in fact, the whole site is fantastic. my company

Anonymous said...

wonderful put up, very informative. I ponder why the other experts of this sector don’t realize this. You should continue your writing. I’m confident, you have a huge readers’ base already Homepage

Anonymous said...

I have a mission that I’m just now working on, and I have been at the look out for such information blog link

Brandon Blogs said...

It was such an extraordinary thing to run over this site gives me all these data. Thank a lot for this. This Content

Jake saranealis said...

Interestingly you write, I will address you'll find exciting and interesting things on similar topics. online Streaming

Anonymous said...

I was extremely satisfied to discover this site.I needed to thank you for this incredible read!! I certainly getting a charge out of each and every piece of it and I have you bookmarked to look at new stuff you post. Read more at

Anonymous said...

The post is written in very a good manner and it contains many useful information for me Continue Reading

Michele Blog said...

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. visit this site

Jennifer blog said...

Positive site, where did u come up with the information on this posting? onlypdf.net

bilal said...

Hi admin
This is very useful for those who work in the SQL field. I read your article it looks good and informative. I am not working in this field. I am working in the health field. You can visit this healthadvicenetwork.com at once. I have also very useful and a huge amount of content related to health. Thanks for sharing the information.

Steven Heves said...

This is a great inspiring article.I am pretty much pleased with your good work https://whyandhow.net/

Thomas Digiacomo said...
This comment has been removed by the author.
Thomas Digiacomo said...

Great Article it its really informative and innovative keep us posted with new updates https://youtubetomp4converter.org/

Trump Campbell said...

This is exceptionally instructive substance and composed well for a change. It's pleasant to see that a few individuals still see how to compose a quality post! visit here

Martin Theories said...

This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. yttomp3

Jacob Nivix said...

Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. smartestcomputing.us.com

Sara Carillo said...


Hiya, I’m really glad I have found this info. Nowadays bloggers publish just about gossips and web and this is really frustrating. A good blog with exciting content, that is what I need. Thank you for keeping this site, I’ll be visiting it. Do you do newsletters? Cant find it visit now

Sophia Isabella said...

Hi everyone! Hope you are doing well. I just came across your website and I have to say that your work is really appreciative. Your content is exceptional. The ease to use your site is remarkable. I have been searching for sites related to different industries. If you are looking for heat press machines. Here is the link of best site website https://www.heatpressreview.net/

iPhone/XCode - not all cases are equal!

This bit me! Having made some changes to an iPhone application (Obj-C); everything worked fine in the simulator. But, when deploying the s...