Populate a *.sdf database from a dataset

[pmartike]

Hi! In my program i receive a data through TCP socket and i put them into a dataset. How can i insert these values into a .sdf (pocket pc/compact framework) database. Is there a method other than using an INSERT command for every dataset row? Because using INSERT is too slow. :sigh: Thank you!

-------------------------------- visit: http://pmartike.deviantart.com/

[Colin Angus Mackay]

I've found it usually isn't the INSERT that is slow it is the database roundtrips that are slow. If the framework supports it then you should batch a number of INSERTs into one Command and send them together.

---

Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website

[pmartike]

But how can i do this?

-------------------------------- visit: http://pmartike.deviantart.com/

[Mike Dimmick]

For SQL Server 2005 Mobile Edition/SQL Server CE 3.1, with .NET Compact Framework 2.0, you can manipulate the tables directly (as it's an in-process database engine). Open a SqlCeResultSet directly on the table you want to insert into. Use the CreateRecord method to get a SqlCeUpdatableRecord, which you set the properties of. Then use the resultset's Insert method to update the table. Reportedly, this is much faster than using any SQL INSERT query, even if you Prepare the command object. In fact SQL Server CE is reportedly much faster if you manipulate tables directly and use the navigation methods on SqlCeResultSet or SqlCeDataReader than using any form of SQL. Only use a SELECT query when you want to do a complicated join. You can open an updatable resultset directly on a table by doing:

using( SqlCeCommand cmd = new SqlCeCommand( "MyTable", conn ) )
{
cmd.CommandType = CommandType.TableDirect;

using( SqlCeResultSet rs =
cmd.ExecuteResultSet( ResultSetOptions.Updatable ) )
{
// code to manipulate the resultset
}
}

Note that this is unique to SQL Server CE - you cannot open tables directly with the full SQL Server 2000 or 2005.

Stability. What an interesting concept. -- Chris Maunder

[Mike Dimmick]

SQL Server CE is in-process so the round-trip problem doesn't apply. The parser only accepts one statement per batch. The parser is appallingly slow and the query execution engine isn't much better. You actually get best performance by simply opening the tables directly (specify the table name for SqlCeCommand.CommandText and set SqlCeCommand.CommandType to CommandType.TableDirect) and not using SQL at all (ironically). In CF 1.0 you could read tables directly but not update them - you had to use INSERT queries and Prepare the command object to avoid the query compiler overhead. CF 2.0 has a SqlCeResultSet class which is back to the bad good old days of ADO Recordset - you can scroll back and forth with a client-side cursor, seek to specific values, update or delete existing rows and insert new rows.

Stability. What an interesting concept. -- Chris Maunder

[Colin Angus Mackay]

Mike Dimmick wrote:

SQL Server CE is in-process so the round-trip problem doesn't apply. The parser only accepts one statement per batch.

Ah, okay - I've never used it.

---

Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website

[pmartike]

Thank you :) I'll try it..

-------------------------------- visit: http://pmartike.deviantart.com/

[Mike Dimmick]

Knew I'd seen an article on this somewhere: SQL Compact Edition Insert Performance[^]

Stability. What an interesting concept. -- Chris Maunder