Wednesday, March 7, 2012

Get SqlLite to work with .NET 4.0 and VS 2010

Of extreme use is SqlLite under .NET 4.0 and Visual Studio 2010. I am writing this short article to help those in need, get quickly up and running. This example is made with a 64 bit version of Windows 7. I made sure to activate all of the features, so that linq can be used within Visual Studio. Here is a list the process from beginning to end:
  1. First step is to download ADO.NET 2.0 Provider for SQLite here.
  2. Run the file SQLite-1.0.66.0-setup.exe.
  3. Let it install into the default location.
  4. Make sure to check the box allowing the installer to change Visual Studio 2010.
  5. Check to make sure it installed correctly, by starting Visual Studio and opening the Server-Explorer pane, then connecting to a Data Source:














  6. Close all instances of Visual Studio.
  7. Download x86 and x64 versions of the system.data.sqlite dlls. For our purposes, sqlite-netFx40-binary-Win32-2010-1.0.79.0.zip and sqlite-netFx40-binary-x64-2010-1.0.79.0.zip.
  8. Expand each zip into a corresponding temporary directory and label them x86 and x64 to differentiate between them.
  9. Do NOT execute the installer, but rather copy the x86 files to C:\program files (x86)\SQLite.NET\bin and the x64 files to C:\program files (x86)\SQLite.NET\bin\x64.
  10. The program SQLite-1.0.66.0-setup.exe, when installed, registered the incorrect files into the gac. You must point instead to the files you just copied. Open the Visual Studio command prompt in admin mode.
  11. Navigate to C:\Program Files (x86)\SQLite.NET\bin and enter the following commands: gacutil /if SQLite.Designer.dll
    gacutil /if System.Data.SQLite.dll
    gacutil /if System.Data.SQLite.Linq.dll
  12. Proceed until you have registered every dll with the GAC. After you are done there, go to the bin\x64 directory and repeat again.
    gacutil /if SQLite.Designer.dll
    gacutil /if System.Data.SQLite.dll
    gacutil /if System.Data.SQLite.Linq.dll
  13. Download an SQLite adminstration program, such as http://sqliteadmin.orbmu2k.de/ and create a database.
  14. Start Visual Studio and use Server Explorer to view this database.
  15. Create a .NET 4.0 application project.
  16. Add an App.config file to your project, by right-clicking over your project and inserting a new item: Application Configuration File.
  17. To App.config, add the following line, directly under the configuration level:
    startup uselegacyv2runtimeactivationpolicy="true" supportedruntime="" version="v4.0" blockquote="">
  18. Add a new component: ADO.NET Entity Data Model. Give it an appropriate name.
  19. Choose to generate the model from the database you previously created.
  20. Give the name something useful. You will be typing this namespace often.
  21. Then select the tables you want and choose plural names if you want (recommended.)
  22. Your model will then appear as an opened edmx file.
  23. Include a reference to System.Data.Linq.
  24. Go ahead and create a function and start typing your linq code.
  25. i.e. from d in (new MyEntities()).myTable select d.myValue.

Have fun!



3 comments:

Nyu said...

Can't get it to work.
It keeps saying that Data.SQLite.Interop.dll isn't a valid module, or something like that.
It must be my computer fault though.

Unknown said...

Hi,

I came across your blog and tried your solution without success.
I was running SQLite 1.0.66 with VS2010, .NET 3.5.
For a few reasons, I needed to upgrade the solution in .NET4
No matter which SQLite dlls I use, I cannot get the solution to compile.

Error 107: The best overloaded method match for 'System.Data.Objects.ObjectContext.ObjectContext(System.Data.EntityClient.EntityConnection, string)' has some invalid arguments

This is one of the auto generated ctors in the edmx cs file.

Hav eyou seen this before?

darichkid said...

Andrew Roberts

This may help, here is Linq to SQLite.
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx