Connecting to SQL CE db using SQLConnection

Question

Pretty straightforward question. I'm building an app which reads data from a SQL Server 2005 instance. I wanted to run some tests on my laptop (which does not have SQL 2005) so I was looking at swapping in a local database for the purpose of the tests.

I'm using VS2008 so the Compact Edition DB seemed a natural choice. I had hoped to just swap out my connection string, but it seems It will only let me connect to the CE database using the SqlCeConnection and not SqlConnection. Any way around this, modifiers I can use in the connection string perhaps?

score 8 · Accepted Answer · answered Apr 27 '09 at 11:37

8

It's actually very possible to user SQL CE instead of full-blown SQL Server by only modifying configuration parameters: change connection string and use IDbXXX family interfaces wherever possible instead of platform-specific SqlXXX and SqlCeXXX ones. See DbProviderFactories.

Be advised, however, of differences in SQL dialects of these two platforms.

answered Apr 27 '09 at 11:37

Anton Gogolev

113,561
39
200
288

1

Do you know a good link that highlights the "differences in dialects"? It'd be really useful if there was a page listing major things it doesn't support that are present in SQL Server. – noelicus Dec 04 '12 at 09:33

score 6 · Answer 2 · answered Dec 04 '12 at 14:41

6

Yes you can use SQL Compact and/or SQL Server by referring to the base classes instead. For example:

IDbConnection Connection;

if (Compact)
    Connection = new SqlCeConnection();
else
    Connection = new SqlConnection();

The connection string needs to point at your data file for SQL Compact, for example: "Data Source=urData.sdf;Persist Security Info=False;". More examples here.

This link will explain what differences there are between SQL Server and SQL Compact, as it's not identical.

answered Dec 04 '12 at 14:41

noelicus

14,468
3
92
111

Thanks for the extra info on the dialect differences, that's useful. I think the example code will also work, but isn't quite as clean (having to explicity reference CE connection types) as avoiding explicit class references and using DbProvider factories and using the configuration to manage the connection type instead( as per the selected answer). – TygerKrash Dec 05 '12 at 13:39

score 2 · Answer 3 · answered Apr 27 '09 at 11:47

All of the SQL related objects you need for either DB inherit from the base abstract Db... (ie DbConnection, DbDataAdapter etc...). You can therefore write some kind of DatabaseManager class that when instantiated needs to know if you're dealing with Sql or Sql Ce. Then, from that point forward you deal just with the base class objects (DbConnection etc..). That way, all you need to change every time, is that instantiation of your manager class.

Another plus to doing it this way, is if you later decide to switch to another provider altogether, not much code needs to change.

Connecting to SQL CE db using SQLConnection

3 Answers3

Linked