How to use Db Provider Factories with System.Data.SqlServerCe

I'm using SQL Server Compact Edition, but in the future I would like to be able to switch to another SQL Server Edition or even a different database. To achieve this, Microsoft recommends using DB Provider Factories (see: Writing Provider Independent Code in ADO.NET, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=674426&SiteID=1).

I enumerated the available data providers on my PC with:

Code Snippet

System.Reflection.Assembly[] myAssemblies = System.Threading.Thread.GetDomain().GetAssemblies();

The important entry is:
"SQL Server CE Data Provider"
".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition"
"Microsoft.SqlServerCe.Client"
"Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

When executing:

Code Snippet

dataFactory = DbProviderFactories.GetFactory("System.Data.SqlServerCe");

I got at first this error run time message:
Failed to find or load the registered .Net Framework Data Provider.

I added a reference to "Microsoft.SqlServerCe.Client" at C:\Programme\Microsoft Visual Studio 8\Common7\IDE\Microsoft.SqlServerCe.Client.dll and the program runs.

Of course, it uses "Microsoft.SqlServerCe.Client" instead of "System.Data.SqlServerCe". Laxmi Narsimha Rao ORUGANTI from Microsoft writes in the post "SSev and Enterprise Library" that "Microsoft.SqlServerCe.Client" is not meant to be used and that we should add the following entry to the machine.config file:

Code Snippet

<add name="SQL Server Everywhere Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Everywhere Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

After changing the code to:

Code Snippet

dataFactory = DbProviderFactories.GetFactory("Microsoft.SqlServerCe.Client");

I get the same error message as before, even after adding a reference to "System.Data.SqlServerCe" at C:\Programme\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlServerCe.dll.

Any suggestion what I should do ? Just use "Microsoft.SqlServerCe.Client" ? Anyway, I don’t like the idea that I have to change the machine.config file, since I want to use click once deployment.

It seems there is no DbProviderFactory for System.Data.SqlServerCe. At least I couldn’t find one, no matter how hard I searched on the Internet. I only found Microsoft.SqlServerCe.Client.SqlCeClientFactory. But we are not supposed to use Microsoft.SqlServerCe.Client and the 2 classes do have quiet some differences among their members. So I decided to write my own factory:

Code Snippet

public class SqlCeClientFactory: DbProviderFactory {

public static readonly SqlCeClientFactory Instance = new SqlCeClientFactory();

public override DbCommand CreateCommand() {
return new SqlCeCommand();
}

public override DbCommandBuilder CreateCommandBuilder() {
return new SqlCeCommandBuilder();
}

public override DbConnection CreateConnection() {
return new SqlCeConnection();
}

public override DbDataAdapter CreateDataAdapter() {
return new SqlCeDataAdapter();
}

public override DbParameter CreateParameter() {
return new SqlCeParameter();
}
}

That was easy enough, right ? I spent 1 week investigating the problem, 10 minutes solving it. I wonder why Microsoft didn’t include this class, because they have already the code in Microsoft.SqlServerCe.Client. I guess they have their reasons, but of course, they don’t tell us. After wasting one more month, I probably can tell. Oh, how I hate this.

Or has anyone an idea what might be the problem ?
|||I went through the same problem a couple years ago. I think the 3.5v has what we were looking. I guess MS didnt care to do the Factory when the main target for the SQLCE was Mobile, but now they're expanding this to other environments they need this. It's weird being something so easy to implement that they didnt do it since v3.0 or 3.1.