SQL Server CE 3.5 SP1 Stored Procedures

Question

I have been tasked with taking an existing WinForms application and modifying it to work in an "occasionally-connected" mode. This was to be achieved with SQL Server CE 3.5 on a user's laptop and sync the server and client either via SQL Server Merge Replication or utilizing Microsoft's Sync Framework.

Currently, the application connects to our SQL Server and retrieves, inserts, updates data using stored procedures. I have read that SQL Server CE does not support stored procedures.

Does this mean that all my stored procedures will need to be converted to straight SQL statements, either in my code or as a query inside a tableadapter?

If this is true, what are my alternatives?

Answer 1

Yes, they are not supported, and the best way is to build them into parameterized queries in code. You can build your own kind of framework for accessing them like stored procedures by enum and then keep them in one clean place in code.

Although if you plan on scaling sql compact at all (outer joins of multiple tables with thousands of rows) you will want to use SqlCeResult sets and the Seek method. It is extremely fast and you can even open indexes directly and seek on them. http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset(VS.80).aspx

Answer 2

Since SQL Server CE is considered to be an "application data store", it is assumed that any complex logic that you might normally implement in a SQL Server Stored Procedure will be implemented in the application itself. Many traditional database concepts are not supported in SQL CE, such as constraints, covering indexes, stored procs, UDFs... you name it, SQLCE doesn't have it!

Because SQL CE is single-user, this assumption more-or-less makes sense; you don't really need to worry about concurrency or atomicity issues when you have total control over everything that's happening at the DB level. It helps to not really think of SQL CE as a full-fledged database; it's more of an alternative to something like SQLite or MS Access.

Your only options are:

• Rewrite your application to behave differently (i.e. use simple queries or direct table access) when operating in "disconnected" mode;

• Disallow the application from performing the more complex operations unless it is "connected";

• Switch to SQL Express instead, which has a much larger footprint but does support Stored Procedures and most of the other SQL Server goodness.

Answer 3

Another option is to use Linq to Datasets. It can "store" stored procedure like methods for you. It is not stored on the database, but it gives you that illusion (though all of these methods need to be attached to a table and still need to me fairly simple).

Answer 4

Another alternative is VistaDB. It does support T-SQL Procs and all the same datatypes as SQL Server (more than SQL CE actually).

You may want to look at this SO post on advantages of VistaDB for more information.