SQLCipher for ADO.NET - Full Database Encryption for .NET on Windows

SQLCipher for ADO.NET provides full database SQLite encryption for .NET 4 based applications on x86 / x64 (non-ARM) platforms that are distributed outside of the Windows Store. This ADO.NET provider, based on System.Data.SQLite, makes it quick and easy to integrate SQLCipher's AES-256 full database encryption into an application. It is fully interoperable with other SQLCipher platform libraries.

The SQLCipher ADO.NET provider is a Commercial Edition library that can be purchased directly from the Zetetic Store. Licensed software is delivered immediately upon payment.

Buy SQLCipher for ADO.NET Now »

Folder Structure and Native Preloading

The SQLCipher ADO.NET provider uses an approach called native preloading to separate platform specific interop assemblies for each supported architecture into individual folders. This allows managed code to load the correct interop library based on the platform the application is running within (i.e. x86 or x64).

This approach allows an application to be built for "Any CPU". Then, the application using SQLCipher is packaged or installed along with the native interop assemblies. When the application is executed the .NET runtime will detect the current platform architecture, look for a matching named folder under the root of the application folder, and then load the appropriate interop library found there. This allows SQLCipher's managed-only assembly to reference native code in a cross-platform way.

Native Preloading requires a specific folder setup, as follows, which is described in this tutorial:

\App.exe (optional, managed-only application executable assembly)
\App.dll (optional, managed-only application library assembly)
\System.Data.SQLite.dll (required, managed-only core assembly)
\System.Data.SQLite.Linq.dll (optional, managed-only LINQ assembly)
\System.Data.SQLite.EF6.dll (optional, managed-only EF6 assembly)
\x86\SQLite.Interop.dll (required, x86 native interop assembly)
\x64\SQLite.Interop.dll (required, x64 native interop assembly)

Within this structure the application executables and supporting library assemblies (e.g. System.Data.SQLite.* from the SQLCipher ADO.NET package) exist at the top level of the application folder. The x86 and x64 subfolders exist under the top level and contain the native interop assemblies from the SQLCipher ADO.NET Package.

Integration Tutorial

The remainder of this document provides a practical example of how to integrate SQLCipher for ADO.NET. This tutorial assumes the use of Visual Studio 2013 on a local development machine, with SQLCipher being added to an existing project.

Unzip Package

Start by unzipping the SQLCipher binary package, sqlcipher-ado.

Unzip the SQLCipher package

Next, move the expanded resources somewhere into the project folder structure.

Copy SQLCipher resources

Add References

Open the project in Visual Studio. Right click on References in the Solution Explorer, and select "Add References".

Add References

Switch to the "Browse" mode, and then locate the folder containing the binary packages and add System.Data.SQlite.dll and System.Data.SQLite.Linq.dll to the project:

Add Assemblies

Add Interop Libraries

Right click on the project root in the Solution Explorer, and select "Add, New Folder".

Add Folders

Create two folders at the top level, one named x86 and one named x64

Right click on the new x86 folder, and choose "Add, Existing Item". Browse to locate the SQLite.Interop.dll file from the corresponding x86 folder of the SQLCipher package.

Add Item

Add Interop Assembly

Select the x86 SQLite.Interop.dll files in Solution Explorer, and change the "Copy to Output Directory" Property to "Copy always"

Copy Always

Repeat the same process to create the x64 folder, add the corresponding x64 SQLite.Interop.dll file, and set itto "Copy Always".

Use SQLCipher

Next, add "using System.Data.SQLite;" to the program source code to begin using the library.

Add Using

When the application is compiled the final build output will include the x86 and x64 folders. Make sure these resources are installed along with the application.

Final Folder Structure

Code Example

The following simplified example code demonstrates how to create and query an encrypted SQLCipher database.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;

namespace SQLCipherApp
{
    class Program
    {
        private static string _connectionString =
                    "Data Source=sqlcipher.db;Pooling=false;SetDefaults=false;";

        // in a real application this would be collected from the user or 
        // some other source, not hard-coded
        private static string _password = "test123"; 

        static void Main(string[] args)
        {

            // create the database
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.SetPassword(_password);
                conn.Open();

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE  IF NOT EXISTS t1(a,b);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO t1(a,b) VALUES ('test0', 'test1');";
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }

            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.SetPassword(_password);
                conn.Open();

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM t1;";
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        System.Console.WriteLine(string.Format("{0} | {1}", reader.GetString(0), reader.GetString(1)));
                    }
                }
            }
        }

    }
}


Using ADO.NET System.Data.Common with SQLCipher

It's also possible to use the System.Data.Common ADO.NET interfaces with SQLCipher. Using this API, the SQLCipher provider is loaded via the DbProviderFactory interfaces:


using System.Data.Common;
using System.Data.SQLite;

...

DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection conn = provider.CreateConnection())
{
    conn.ConnectionString = "Data Source=sqlcipher.db;Pooling=false;Synchronous=Full;";
    ((SQLiteConnection)conn).SetPassword("secret");
    conn.Open();

    using (IDbCommand command = conn.CreateCommand())
    {
       command.CommandText = "CREATE TABLE t1(a,b);";
       command.ExecuteNonQuery();
    }
    ...
}

When using System.Data.Common the following App.config changes are required so that the provider can be loaded at runtime (note that the Version for the provider changes from release to release, and is found in the package README.


<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.XX.0, Culture=neutral, PublicKeyToken=2afdc9349188e576" />
    </DbProviderFactories>
  </system.data>
</configuration>

SQLCipher with .NET 4

SQLCipher's ADO.NET provides full support for .NET 4. Older versions of the .NET framework are not supported.

Get SQLCipher for ADO.NET

SQLCipher for ADO.NET can be purchased directly from the Zetetic Store and licensed software is delivered immediately upon payment.

Buy SQLCipher for ADO.NET Now »