3 ways to use the SQLite NuGet with Telerik Data Access

From the various database engines supported by Telerik Data Access, SQLite is one of those often used in desktop or client applications. The combination of the versatility of Telerik Data Access and the strengths of SQLite as a backend will make your development of a DAL for such applications, an easy and seamless process.

However when developing application developed with Telerik Data Access and SQLite, you could face a problem when loading the database drivers. Not to worry, the issue is simple and fixed easily with one of the approaches I will describe later in this post.

Database Provider not Installed

Telerik Data Access uses two approaches for resolving the ADO.NET driver for the different backends:

1. Requesting the driver from the DbProviderFactories class.
2. Attempt to load the driver using the fully qualified name of the assembly including a hard coded version number.

If the required backend driver is not registered in the DbProviderFactories class and the locally copied driver assembly is not found or has a different version, both attempts will fail and Telerik Data Access would not be able to load the driver. Such issue can sometimes occur when developing an application which uses the SQLite NuGet package. It is the reason for the following exception:

System.Configuration.ConfigurationErrorsException: Database provider System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.86.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139 not installed

Solutions

There are several possible solutions to this problem which allow you to load the SQLite driver and deploy its assembly .dll files together with your application:

1. The first solution is to register the SQLite driver assembly through the application`s .config file by adding the following XML node:

<system.data>
    <DbProviderFactories>
      <add name="SQLite Data Provider for .NET" invariant="System.Data.SQLite"
       description="SQLite Data Provider for .NET" type="System.Data.SQLite.SQLiteFactory, 
       System.Data.SQLite, Version=1.0.89.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"/>
    </DbProviderFactories>
</system.data>

This is the fastest approach since it addresses the potential failure of the first attempt at loading the SQLite driver. Note that when the SQLite NuGet is updated, the version number of the assembly should also be updated in the .config file.

2. Another approach is to add an assembly redirect in the .config file of your application:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SQLite" publicKeyToken="db937bc2d44ff139" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.0.89.0" newVersion="1.0.89.0" />
      </dependentAssembly>
    </assemblyBinding>
</runtime>

This approach addresses the potential failure of the second attempt to load the SQLite driver. As with the previous approach you will have to manually set the version number of the assembly when updating the SQLite NuGet.

3. The last approach is to use AppDomain.AssemblyResolve Event to load the driver at runtime:

AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;

static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    if (args.Name.StartsWith("System.Data.SQLite,"))
    {
        return typeof(System.Data.SQLite.SQLiteFactory).Assembly;
    }
    return null;
}
 

The event handler detects the SQLite assembly name and returns an instance of the assembly. Since the assembly version information is taken directly from the loaded type, the only thing you have to do when updating the SQLite NuGet is to rebuild the project.

As you can see, each of the approaches has its own specifics. You can see them all in action in this sample application. The choice which is the most appropriate for your scenario rests with you. To run the sample application you would need to install Telerik Data Access. Download it here for free!