How to create DbProviderFactory object for SQL Server CE?

Question

I'm trying to create an object for DbProviderFactory using the following code,

 try
 {
     var sql = ConfigurationManager.ConnectionStrings["sql"];
     DbProviderFactory sqlfactory = DbProviderFactories.GetFactory ( sql.ProviderName ); // This one works fine

     var sqlCE = ConfigurationManager.ConnectionStrings["sqlCE"];
     DbProviderFactory sqlCEfactory = DbProviderFactories.GetFactory ( sqlCE.ProviderName ); // This doesnt 
  }
  catch ( Exception ex )
  {
      Console.WriteLine ( ex.Message );
  }

and app.config:

<connectionStrings>
   <clear/>
   <add name="sql" 
        providerName="System.Data.SqlClient" 
        connectionString="Server=.\SQLExpress;Database=TestResults;Trusted_Connection=Yes;"/>
   <add name="sqlCE" 
        providerName="System.Data.SqlServerCe" 
        connectionString="DATA SOURCE=DataBase\dbTestResults.sdf"/>  
</connectionStrings>

The first one (sqlFactory) creates successfully, but the second object (sqlCEfactory) throws an exception:

Am I missing something?

Thanks!

Answer 1

You have the wrong provider names specified in your config file.

The SQL Server CE invariant provider names are

System.Data.SqlServerCe.4.0 

(for version 4.0 runtime)

System.Data.SqlServerCe.3.5 

(for version 3.5 runtime)