20

I am using Entity Framework 4 in a desktop application with SQL Compact. I want to use a private installation of SQL Compact with my application, so that my installer can install SQL Compact without giving the user a second installation to do. It also avoids versioning hassles down the road.

My development machine has SQL Compact 3.5 SP1 installed as a public installation, so my app runs fine there, as one would expect. But it's not running on my test machine, which does not have SQL Compact installed. I get this error:

The specified store provider cannot be found in the configuration, or is not valid.

I know some people have had difficulty with SQL Compact private installations, but I have used them for a while, and I really like them. Unfortunately, my regular private installation approach isn't working. I have checked the version numbers on my SQL CE files, and they are all 3.8.8078.0, which is the SP2 RC version.

Here are the files I have included in my private installation:

  • sqlcecompact35.dll
  • sqlceer35EN.dll
  • sqlceme35.dll
  • sqlceqp35.dll
  • sqlcese35.dll
  • System.Data.SqlServerCe.dll
  • System.Data.SqlServerCe.Entity.dll

I have added a reference to System.Data.SqlServerCe to my project, and I have verified that all of the files listed above are being copied to the application folder on the installation machine.

Here is the code I use to configure an EntityConnectionStringBuilder when I open a SQL Compact file:

var sqlCompactConnectionString = string.Format("Data Source={0}", filePath);

// Set Builder properties
builder.Metadata = string.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", edmName);
builder.Provider = "System.Data.SqlServerCe.3.5";
builder.ProviderConnectionString = sqlCompactConnectionString;
var edmConnectionString = builder.ToString();

Am I missing a file? Am I missing a configuration stepp needed to tell Entity Framework where to find my SQL Compact DLLs? Any other suggestions why EF isn't finding my SQL Compact DLLs on the installation machine? Thanks for your help.

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
David Veeneman
  • 18,912
  • 32
  • 122
  • 187

5 Answers5

37

I figured out how to do it, thanks to a blog post by Steve Lasker. Basically, here is what you have to do:

(1) Set a reference to System.Data.SqlServerCe.dll in your project. Set the CopyLocal property to True.

(2) In the App.config for your project, add the following XML markup. It tells EntityFramework to look to your private installation of SQL Compact for its data provider:

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SqlServerCe.3.5"/>
        <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>

(3) In the Setup project, add the following files to the Application Folder in the File System Editor:

  • sqlcecompact35.dll
  • sqlceme35.dll
  • sqlcese35.dll
  • System.Data.SqlServerCe.Entity.dll
David Veeneman
  • 18,912
  • 32
  • 122
  • 187
22

Just for the record, with SQL CE 4 the web.config entries are the following:

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

Version=4.0.0.1 in case of a private deployement and Version=4.0.0.0 in case of a general deployment.

Jonx
  • 1,075
  • 12
  • 16
  • 1
    but besides that sqlce4 doesn't have anything else when it comes to install right? I can just xcopy the required dlls into the application directory or bin and it should work right? – gideon Feb 17 '11 at 03:09
  • Avoid the 4.0.0.1 version, or you will run into Medium trust issues – ErikEJ Apr 29 '11 at 08:59
  • @ErikEJ not sure what is meant here Private vs. General deployment, but I have added the most recent packages to my project, it includes the lines specified above, and all the x86 and amd64 stuff is deployed to Bin. When I run it it says it cannot load, and the inner exception says it cannot find the assembly at all. What am I missing here? (4.0.0.1 btw) – one.beat.consumer Feb 17 '12 at 01:47
  • See this reply: http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450 – ErikEJ Feb 17 '12 at 17:00
2

thanks for your tip - helped me alot. There's a post on the SQL Server Compact-Team blog which add some additional information for the Sql Server Compact 3.5 SP2 release.

After struggling for a while with the private deployment of sql server compact i found out some additional requirements.

I tried out my app on several different systems and recognized that my app not worked properly on some of them.

Example: Try this:

-I've set up a clean winxp sp3 installation

-installed the .net Framework 4.0 extended

-deployed my app to the new installation (including all necessary dll's and tweaks described in your/sql server compact team blog post's)

So, after some research i found out that in addition to the .net framework 4 installation i had to install the .net framework 2 as well and it worked fine.

So here is my question: which component is used by the sql server compact edition which is not contained in the .net framework 4?

I don't want overload my setup and chain two frameworks to my bootstrapper... Does anybody know any good Tip?

Many thanks to the sql server compact Team!

SQL Server Compact v3.5 depends on ‘Visual C++ Runtime 2005 (or 8.0)’ (also known as CRT80). We package the CRT80 modules in our MSI. In case of private deployment, you need to take care of this dependency. Things work with .NET FX v2.0 on the system automatically because .NET FX v2.0 also packages and installs CRT80 modules.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Renato Heeb
  • 719
  • 8
  • 18
  • You can download the 2.6 MB redists here: http://www.microsoft.com/downloads/details.aspx?familyid=200B2FD9-AE1A-4A14-984D-389C36F85647&displaylang=en and here: http://www.microsoft.com/downloads/details.aspx?familyid=EB4EBE2D-33C0-4A47-9DD4-B9A6D7BD44DA&displaylang=en – ErikEJ Jun 24 '10 at 18:18
  • Adn thanks to you for the tip on SP2. I just switched over to it, and your reference solved my problem! – David Veeneman Sep 16 '10 at 04:37
1

This is what worked for me:

You've got a machine.config file for dotNet versions 2.0 and 4.0:

dotNET 2.0 

C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config
dotNet 4.0 
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

If you open up the file for version 2.0, you'll see that it has a <system.data> node, which probably looks something like this:

<system.data>
    <DbProviderFactories>
        <add name="Odbc Data Provider" ... stuff here ... />
        <add name="OracleClient Data Provider" ... stuff here ...>
        ... more lines similar to the one above ...
        <add name="Microsoft SQL Server Compact Data Provider" Invariant="System.Data.SqlServerCe.3.5" ...>
    </DbProviderFactories>
</system.data>

Whereas, if you open up the one for dotNet 4.0, it looks rather more miserably like:

<system.data>
    <DbProviderFactories>        
    </DbProviderFactories>
</system.data>

Or maybe it doesn't even have a <system.data> node at all !!! In either case, simply copy the <system.data> node in it's entirety from the machine.config file for v2 to the one for v4.

SIDE NOTE

If you are having trouble saving your edit of the v4 machine.config, then you might have to right click a couple of times when clicking on the editor launch icon so as to get to the run in admin mode.

david.barkhuizen
  • 5,239
  • 4
  • 36
  • 38
0

Being stuck with the same problem,

"the specified store provider cannot be found in the configuration or is not valid."

I came around this post. I tried almost everything. I had installed "System.Data.SqlServerCe" using nugets.

So i already had below line of code added in my web.config

<DbProviderFactories>
  <remove invariant="System.Data.SqlServerCe.4.0" />
  <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe" />
  <!--<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />-->
  <!--<add name="Microsoft EntityClient" invariant="System.Data.EntityClient.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.EntityClient, System.Data.EntityClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />-->
</DbProviderFactories>

Still the error continued..

I got rid of problem by uncommenting the last two commented lines in above code...so now it becomes

<DbProviderFactories>
  <remove invariant="System.Data.SqlServerCe.4.0" />
  <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe" />
  <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  <add name="Microsoft EntityClient" invariant="System.Data.EntityClient.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.EntityClient, System.Data.EntityClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>

Hope it helps.. Thanks.

Neelam
  • 1,028
  • 12
  • 25