System.Data.SQLite

Hi All,

I'm fairly new to using SQLite ADO.NET provider, but not C# and ADO in general, so I'm a bit stummped as to why I'm getting

Keyword not supported: 'data source'.

When i try the following

theNamesContext db = new theNamesContext("Data Source=C:\\Documents and Settings\\Administrator\\Desktop\\theNames.s3db");

'theNamesContext' is the class defined in my ADO.NET EF Class (edmx file), and all works as expected (that is the connection string is loaded from app.config) if I leave the connection string parameter out.

No matter how hard I try

'data source' "data source" 'Data source' and as many other derivitives I can think of. 

I just can't seem to make it work.

In case it was somthing wierd in my typing, I even tried using the connection string builder

SQLiteConnectionStringBuilder strBuilder = new SQLiteConnectionStringBuilder();
strBuilder.DataSource = "C:\\Documents and Settings\\Administrator\\Desktop\\theNames.s3db";
theNamesContext db = new theNamesContext(strBuilder.ConnectionString);

and  I get exactly the same exception.

I've pulled most of my hair out so far and still not figured out whats wrong, if I compare my various attepts at generating the string to that in the app.config file they're identical, and those in the app.config work!!!

I'd appreciate any ideas any one has, I need to make this work beacuse I need to be able to dynamicly change the connection settings from within the program code.

Cheers

Shawty

 Update:

 After a couple of sleepless nights, and a lot of head scratching, It turns out that when using the entity framework you need to specify the entire string complete with meta data, this string if you look in your app.config file will have been created by the entity framework designers.

In my case my full string was:

"metadata=res://*/datacon.csdl|res://*/datacon.ssdl|res://*/datacon.msl;provider=System.Data.SQLite;provider connection string='Data Source=C:\\Documents and Settings\\Administrator\\Desktop\\theNames.s3db'";

Notice that the classic "Connection String" part is actually in it's own set of '..' single quotes.

This is beacuse "Data Source" is not directly understood by the EF, but it is understood by the EF parser, and presumably passed directly to ADO.NET once it's seperated out.

Note allso the various "datacon.*" parts..   "datacon" is the name of your edmx file created by the EF designer.  so if your EF class was called "mydatabase.edmx" then you need to replace all occurences of "datacon" with "mydatabase".

Once you have the string in this format, then you can add all the other stuff such as "Password=xxx" and "Version=3" as long as they remain within the single quotes surrounding the connection string part.

The way Iv'e chosen to approach it is to keep the clasic connection string seperate (and manipulated as normal) then to join it with a static constant that contains the meta data etc just before using it.

Of course if you have no need to change parameters during the run time of your app, then you need not do any of this as the EF will handle it for you, In my case however I need to be able to change passwords and other parameters, and not have them stored in clear text in app.config

I hope this helps anyone else coming along with a similar issue, and that it serves to educate just how much different EF connection strings are from classic connection strings.

Best regards

Shawty

I ran into this and many other issues with EF as well.  I switched over to Telerik OpenAccess recently, and love it!  It allows you to use a standard connection string too.  :)

 I ran into this and many other issues with EF as well. But i figured out the problem and now like the software.