I and a colleague are developing applications, one for a Windows Mobile 5.0 device, the other for a Windows PC. They both have two replicating functions, reading out from a Database, to a dataset and saving the Dataset as XML, and obviously the opposite of that, reading in the XML files to a dataset and then inserting into the database. On the Windows Application this works fine using the esUtlity FillDataSet for taking things out of the Database, and ExecuteNonQuery for inserts to the Database. To provide less of an overhead we are inserting everything back into the database as a bulk String containing SQL commands seperated by a semi-colon and passing that to the ExecuteNonQuery method. This works fine on the Windows PC, however the replicated code on the WM5 device falls over with a "error parsing the query". Could this be due to it having semi-colons? As we have tested this running one SQL statement by itself on the WM5 device and that works, however it takes a long time.

We're writing both applications in C#, and we're trying to stay away from using SqlCeCommand.ExecuteNonQuery if we can, as I'm assuming it'll work roughly the same way, and it will require us to open another connection to the database which we'd rather not do.

Thanks for any help you can provide.
 

Our ExecuteNonQuery will map directly to the ADO.NET data providers ExecuteNonQuery, ie, SqlCeCommand. I am not sure that SqlCe (the database) can even handle bulk SQL statements and this could be the problem. I think your application might actually perform better if you just used ES rather than DataSets and XML, that's a lot to ask WM5 applications to do? My experience is the DataSet/XML approach is slow, and very verbose.

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

We had thought of the solution of importing the SqlCe dll into the Windows application and creating the CE database then transferring that to the device. We have actually pasted a bulk statement into SqlCe on the device and that ran through quite quickly. Quicker than we expected. And as using esUtility requires the least amount of code change that is the laziest and therefore more preferable solution .

Because we're transferring data from the Windows Application to the WM5 application (not sure if I was clear on that), this has been the only way we could realistically think of it fitting in with the architechture that we have proposed.

 
Thanks for the response.
 

Okay, we want to help. If you are getting an error can you post the call stack? We want you guys to be able to use this, and you should be able to.

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

The full error message plus Stack Trace that we get are as follows:

System.Data.SqlServerCe.SqlCeException was unhandled
  Message="There was an error parsing the query. [ Token line number = 1,Token line offset = 210,Token in error = INSERT ]"
  HResult=-2147217900
  NativeError=25501
  Source="SQL Server 2005 Mobile Edition ADO.NET Data Provider"
  StackTrace:
    at System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
    at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
    at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
    at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
    at EntitySpaces.SqlClientProvider.Ce.DataProvider.EntitySpaces.Interfaces.IDataProvider.ExecuteNonQuery()
    at EntitySpaces.Interfaces.esDataProvider.ExecuteNonQuery()
    at EntitySpaces.Core.esEntityCollection.ExecuteNonQuery()
    at EntitySpaces.Core.esEntityCollection.ExecuteNonQuery()
    at EntitySpaces.Core.esUtility.ExecuteNonQuery()
    at Mobile_Survey_Client.frmMenu.loadData()
    at Mobile_Survey_Client.frmMenu.menuNav()
    at Mobile_Survey_Client.frmMenu.outlookShortcutBar1_MouseUp()
    at System.Windows.Forms.Control.OnMouseUp()
    at Resco.Controls.OutlookControls.OutlookShortcutBar.OnMouseUp()
    at System.Windows.Forms.Control.WnProc()
    at System.Windows.Forms.ContainerControl.WnProc()
    at System.Windows.Forms.Control._InternalWnProc()
    at Microsoft.AGL.Forms.EVL.EnterMainLoop()
    at System.Windows.Forms.Application.Run()
    at Mobile_Survey_Client.Program.Main()

From that it does look like a SqlCe issue. It could just be that you can't execute multiple statements through ExecuteNonQuery, which seems weird as you can do it directly into SqlCe. The Sql that it falls over with is:

"INSERT INTO tblSurveySection(SurveySectionID, SurveyID, SectionID, HSID) VALUES ('1f22d882-9b86-4897-9481-184c93567bce', '4d88b4b7-e5c9-479f-86fb-04197dcae5ec', 'f79487a6-bf24-47fc-b17a-19aac03eab8a', '307'); INSERT INTO tblSurveySection(SurveySectionID, SurveyID, SectionID, HSID) VALUES ('7e84fdf0-cbfc-4f75-b99f-429b038acf02', '4d88b4b7-e5c9-479f-86fb-04197dcae5ec', '00146308-387c-43cb-9352-7bd89102588c', '308'); "

We've ran that in MS SQL Server and it works, and on the device and it works.

Thanks again for the help.
 

Ya, I'll bet is a SqlCe provider issue. Traditionally, file based databases usually disallow bulk statements as the logic necessary to perform them makes the provider too big for wireless devices. You could easily verify this in a little test app using the SqlCe provider directly, but again, we are just passing it straight through as well.

In Google if you search for "SqlCe" and "Bulk" you will find many posts like this:

=========================
 

Looping through rows is the only way, there's no "bulk insert" in SQL CE. Of course you should not create insert statements for every row (as it would be extremely slow) and should use single parametrized insert command for all rows.  

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2503170&SiteID=1

 

PS: Our Dynamic Updates uses parameterized inserts   - It might faster to just use Traditional EntitySpaces techniques ... (Business Entities)

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

Thanks for the replies Mike, the help is much appreciated.