In the last version of Visual Studio 2013, support for SQL Server compact (.SDF) has been dropped. What’s alternative for this? SQLite is a lightweight database engine which you can use in small projects without installation of complex database systems.

Generate EDMX model

Below instruction is 100% compatible with Visual Studio Express 2013 with .NET 4.0 target (for Windows XP compatibility) and x86 platform.

In Express edition of Visual Studio, we cannot install external plugins. The SQLite bundle installer do not work with this edition. However, SQLite Data model wizard with VS Profesionall edition is also very problematic (do not show SQLite provider list). It is a workaround to generate EDMX model. We can use edmgen.exe and edmgen2.exe tools to generate EDMX file.

Edmgen – create EDMX model

With edmgen tool you can create .ssdl .msl and .csdl files. Edmgen2.exe is needed to creating .edmx file from them.

  1. First, you have to download SQLite pack with precompiled statically-linked binaries from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki. It is important because this pack contains required SQLite.Interop.dll. If you have 64 bit OS, download for x64.
  2. Unpack SQLite pack and copy edmgen.exe and edmgen2.exe to the directory
  3. We have to configure SQLite provider for edmgen.exe. Create edmgen.exe.config with content:
    [code lang=”xml”]
    <configuration>
    <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, Version=1.0.94.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
    </system.data>
    </configuration>
    [/code]
  4. Create script file gen.cmd with below content:
    [code lang=”shell”]
    edmgen.exe /mode:fullgeneration /c:"Data Source=<PATH_TO_SQLITE>" /provider:System.Data.SQLite /entitycontainer:<ENTITY_NAME> /project:<ENTITY_NAME> /language:CSharp
    edmgen2.exe /ToEdmx <ENTITY_NAME>.csdl <ENTITY_NAME>.msl <ENTITY_NAME>.ssdl
    [/code]
    Fill apropriate fields (database path and project name). Run the command. If you receive BadImageFormatException, that means you have incorrect version of SQLite.Interop.dll.

Visual Studio

  1. Create project with target .NET 4.0 and x86 platform.
  2. Install SQLite via Nuget
  3. Add generated data model
  4. Add missing references. In my case – System.Data.Entity
  5. Add sqlite database
  6. Update app.config
    [code lang=”XML”]
    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!– For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 –>
    </configSections>
    <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>
    </configuration>
    [/code]
  7. Create connection string
    [code lang=”csharp”]
    var conn = new EntityConnectionStringBuilder();
    conn.ProviderConnectionString = "data source=<PATH_TO_DATABASE>";
    conn.Provider = "System.Data.SQLite";
    conn.Metadata = "res://*/";
    [/code]
  8. Build project. Don’t miss to add appropriate SQLite.Interp.dll to the output directory.

Problems

VS shows me error for my EDMX model (provider is not registered), but project compiles fine. Reading/writing from database is working

Summary

SQL Server Compact (.sdf) is not fully supported from current version of Visual Studio 2013 (older projects can be compiled). Alternatives? We can use LocalDB, but this approach requires installing additional software. I think that, SQLite is the best alternative, but setup it with Entity Framework is horrible.


Related posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *