sqlserverCE sdf

[Finn J Johnsen]

Hi

Is there a way to generate a sdf -file on a PC, and move it for the
SQLServerCE -device for usage there?

I've got aprox 100 000 records to insert to the sdf -file, and have
built a webservice for fetching chunks of records, inserting it to the
sdf. This process takes hours. I guess overhead in Webservices to be the
cause, and figure a TCP sollution would speed things up. However, doing
all of this - serverside would be the quickest I'm sure.

Using a pocketPC/winCE emulator on the server, generating sdf would do
the trick I guess. But smells messy.

Thanks,
Finn

 

[Chris Tacke, eMVP]

Not with the current version of SSCE. SQL Mobile will allow it.

-Chris

 

[Ginny Caughey [MVP]]

Finn,

You could do this using the emulator (for creating intial test data), but
generally the answer is no for production until Sql Server Mobile Edition as
Chris said.

 

[Chuck Peper]

Don't even use a web service, way to much text transfered.
Hit an aspx page that has the delimited records or copy a text file to
the device. I use the webpage and read/load using a thread.
30,000 records ~ 5 minutes.

Load into sqlce using
http://www.fitiri.com/SSCEDirect.html
which is 10x faster than ado.net

 

[Jon Skeet [C# MVP]]

Don't even use a web service, way to much text transfered.
Hit an aspx page that has the delimited records or copy a text file to
the device. I use the webpage and read/load using a thread.
30,000 records ~ 5 minutes.

I'd suggest investigating compression (using SharpZipLib on the client
side) if you're able to - we found that increased performance hugely in
terms of network traffic.

Load into sqlce using
http://www.fitiri.com/SSCEDirect.html
which is 10x faster than ado.net

I have two comments about SSCEDirect:

1) It works. It's fast. The company is responsive (as far as I've
seen).

2) It's not that hard to write something similar yourself, if the cost
of SSCEDirect puts you off... (I wrote what I needed in a couple of
days; in fact, SSCEDirect wouldn't quite have cut it, as I needed to
truncate a table in the same transaction as inserting the new rows.)

Basically, if you've got the development time, consider doing it
yourself (especially if you know OLE DB - I didn't know anything about
it before starting) - for convenience, buy from Fitiri

 

[Jon Skeet [C# MVP]]

This kind of licensing is too anying for me:
Per-device license: $10/ea.
Over 50 units: $7/ea.
Over 100 units: $5/ea.

They do have potential for unlimited licensing, of course - but they
don't give a price for that on the web page.

So I'de definetively try what you did. Could you give me a pinpoint on
where to start doing this? Do I have to "reverse engeneer" the sdf and
see how it works when accessing it normally?

Heck no. I just looked at the SQL Server CE documentation and the OLE
DB documenation, built a DLL to handle open/execute arbitrary
SQL/insert row/close functionality, and then wrote a simple managed
interface to the DLL. Fortunately OLE DB does all the hard work in
terms of converting strings to things like numbers - although I admit
my code doesn't cover various types such as NTEXT, IMAGE, or even
DATETIME as I don't need those at the moment.

Effectively you just need to get an IRowsetChange, start a transaction,
insert some rows, and then commit the transaction. The SQL Server CE
sample code gives examples for inserting rows - it's a good starting
point.

 

[Finn J Johnsen]

2) It's not that hard to write something similar yourself, if the cost
of SSCEDirect puts you off... (I wrote what I needed in a couple of
days; in fact, SSCEDirect wouldn't quite have cut it, as I needed to
truncate a table in the same transaction as inserting the new rows.)

This kind of licensing is too anying for me:
Per-device license: $10/ea.
Over 50 units: $7/ea.
Over 100 units: $5/ea.

So I'de definetively try what you did. Could you give me a pinpoint on
where to start doing this? Do I have to "reverse engeneer" the sdf and
see how it works when accessing it normally?

thanks,
Finn

 

[Chris Tacke, eMVP]

But how does this allow using a SQLCE database actually on the PC? The
SqlCE OLEDB stuff is all device-side. Am I missing something?

-Chris

http://www.gui-innovations.com/html/remotesqlce.html

 

[Jon Skeet [C# MVP]]

But how does this allow using a SQLCE database actually on the PC? The
SqlCE OLEDB stuff is all device-side. Am I missing something?

Nope. I was only responding to Chuck's message which mentioned using
SSCEDirect.

Doing things directly means it takes a lot less time, which may well
mean that Finn doesn't need to worry about trying to do it from the
desktop in the first place.

 

[rjklindsay]

Are you saying that there is NO way of opening a sdf file on the desktop, other than the Management Studio?
The SQL Server Merge replication stuff is very flaky and difficult to set up, and hardly ever works right.
Is there no ODBC or OLEDB desktop drivers that can read sdf files directly?

If so, then this severely cripples the usefullness of sqlserver compact edition.
In that case, wouldnt an XML based database be a far more flexible and powerful solution?
Am I missing something?