Entity Framework connection to SQLite database not working after deployment

Question

I changed a dataset based approach to my sqlite database to one using the Entity Framework. I created the entities using Visual studio 2008's wizard and had the connection string stored in the app.config. A client program then communicates through WCF with the database. This works well on the development computer when I host the WCF service in a console application. After hosting the service in IIS7 on the remote computer I get the error: "The underlying provider failed on open". It is generated the first time a read operation is attempted from the database.

The autogenerated connection string is:

     <add name="xPMDbEntities" connectionString="metadata=res://*/PM_EDM.csdl|
            res://*/PM_EDM.ssdl|
            res://*/PM_EDM.msl;
            provider=System.Data.SQLite;
            provider connection string=&quot;
            data source=G:\PMPersistence\xPMDb.s3db&quot;"          
            providerName="System.Data.EntityClient" />

(I broke up the string in a few lines to make it more readable).

On the server this is not working, even if I locate the database in the exact same location as on the development computer. Does anything of the rest of the connection string, (of which I do not understand the first thing) need to be edited too? A link to a good resource about how to construct EF connection strings would be very helpful too!

EDIT: I also added the section below to the web.config file:

<system.data>
     <DbProviderFactories>
       <remove invariant="System.Data.SQLite"/>
       <add name="SQLite Data Provider" invariant="System.Data.SQLite" 
            description=".Net Framework Data Provider for SQLite"  
            type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>
     </DbProviderFactories>
  </system.data>

This seems to work (Thomas suggests it should be located in the machine.config file) as before I added this entry I got an error saying that System.Data.SQLite could not be found.

EDIT2: I installed the System.Data.SQLite dll on the server rather than just xcopying the dll to the BIN directory. Both the machine.config in the Framework/Config and Framework64/Config folders now have the above DbFactory entry. It does not make a diference (unless I have to restart the server??)

Dabblernl · Accepted Answer · 2010-02-08 21:50:45Z

6

The connection string was indeed OK. The problem was that you have to deploy both the system.data.sqlite.dll and the system.data.sqlite.linq.dll to the Bin folder of the web service that hosts the database connection.

answered Feb 8, 2010 at 21:50

Dabblernl

16.2k21 gold badges100 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abel Over a year ago

Indeed. Keep in mind that System.Data.SQLite.Linq.dll does not have any public classes. As a result, just adding it as a reference, may not result in it being deployed (some tools only deploy references that are actually used, not references that only occur in your *.csproj file). For instance, in TeamCity, we had to add a manual step to copy the Linq file to the bin directory, and in our installer-builder, we had to do the same. Alternatively, you can add both to the GAC.

Thomas Levesque · Accepted Answer · 2010-02-08 01:48:50Z

4

Your connection string seems correct to me. Are you sure the SQLite ADO.NET provider is properly installed on the server ? Open your machine.config file, and check if there is an entry for System.Data.SQLite in the DbProviderFactories section

A link to a good resource about how to construct EF connection strings would be very helpful too!

Basically, you have to specify : - the model metadata (the parts with "res://...") - the store provider ("System.Data.SQLite" in your case) - the store connection string, which varies depending on which provider you use

The easiest way to construct a connection string dynamically is to use the EntityConnectionStringBuilder class, along with the connection string builder of your store provider.

edited Feb 8, 2010 at 1:48

answered Feb 7, 2010 at 23:54

Thomas Levesque

294k73 gold badges639 silver badges769 bronze badges

4 Comments

Thomas Levesque Over a year ago

What's the exact error message you get ? PS : I updated my answer to address your question about the connection string.

Dabblernl Over a year ago

The errormessage is: "The underlying provider failed on Open". The stack trace points to the first line in code where the database is actually accessed.

Thomas Levesque Over a year ago

and what is the InnerException ?

Dabblernl Over a year ago

The inner exception gave the clue, it stated that *a null was returned after calling the 'GetService' method. This pointed me to a post that explained the solution below.

echo · Accepted Answer · 2010-02-09 01:27:45Z

1

I agree with Thomas, the conn. string looks OK to me. But if you are looking for information about building connection strings, check this out:

http://msdn.microsoft.com/en-us/library/bb738533.aspx

edited Feb 9, 2010 at 1:27

answered Feb 8, 2010 at 0:12

echo

7,9252 gold badges38 silver badges33 bronze badges

Comments

ram · Accepted Answer · 2010-02-08 00:23:42Z

0

try double slashes for path ??

G:\\PMPersistence\\xPMDb.s3db

Also specifying your assembly name instead of * would help when the application is looking for ssdl,csdl,msl

res://YourAssemblyName(without the dll extn)/PM_EDM.ssdl|

answered Feb 8, 2010 at 0:23

ram

11.7k16 gold badges68 silver badges89 bronze badges

Collectives™ on Stack Overflow

Entity Framework connection to SQLite database not working after deployment

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related