ASP.NET: Using SQLite with Enterprise Library 3.1

Share |

Summary of how to use the EntlibContrib SQLite provider with Enterprise Library 3.1 (May 2007) and ASP.NET

Information and download of System.Data.SqLite:
http://sqlite.phxsoftware.com/

Enterprise Library 3.1 (May 2007):
http://www.microsoft.com/downloads/details.aspx?FamilyID=4c557c63-708f-4280-8f0c-637481c31718&displaylang=en

EntLibContrib Project at codeplex:
http://www.codeplex.com/entlibcontrib

I've extolled the virtues of the amazing SQLite database engine a number of times here (just use our Search widget with "SQLITE") so I won't go into much detail about it. Here are a few bullet points, though:

1. Complete ADO.NET 2.0 Implementation
2. Supports the Full and Compact .NET Framework as well as native C/C++
3. Completely portable database files
4. Incredibly fast, faster than most every other embedded database, including Sql Server Mobile
5. Encryption Support
6. Full Text Search
7. Visual Studio 2005 Design-Time Support
8. Single file redistributable under 500kb -- nothing to "install"!
9. Extensive SQL support
10. User-Defined Functions & Collating Sequences, Triggers
11. Full Source Included.  100% Free

You can thank my friend Robert Simpson for all this effort and contribution. In sum, SQLite is very fast, requires no configuration  or installation (only that the assembly be present), and implements most all of the SQL 92 spec. With the ADO.NET Provider, you are dealing with datareaders, DataSets and all the familiar programming tools you are used to. The SQL syntax is a bit of a variant from T-SQL, but the differences are quite minor.
At Codeplex, there is a Enterprise Library "contrib" project with a number of enhancements written by users like you and me. One in particular that caught my attention was the SQLite provider, written by Ken Scott  http://freqken.net/.

The solution here shows how to wire up your Entlib configuration to use the ADO.NET 2.0 System.Data.SqLite engine with Enterprise Library 3.1

First you will need the required EntLib and Contrib assemblies:

Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Common
EnterpriseLibrary.Contrib.Data.SqLite
EnterpriseLibrary.Contrib.Data

Then, you'll set up your configuration, which can be done with the EntLib Configuration Add-on from within Visual Studio:

My sample config looks like this:

<?xml version="1.0"?>

<configuration>

  <configSections>

    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

  </configSections>

  <dataConfiguration defaultDatabase="ConnectionString">

    <providerMappings>

      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Contrib.Data.SqLite, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

        name="System.Data.SqLite" />

    </providerMappings>

  </dataConfiguration>

  <connectionStrings>

    <add name="ConnectionString" connectionString="Data Source=dbpath;New=True;UTF8Encoding=True;Version=3"

      providerName="System.Data.SQLite" />

  </connectionStrings>

  <appSettings>

    <add key="Setting" value="" />

  </appSettings>

  <system.web>  

    <compilation debug="true" />   

    <authentication mode="Windows" />   

  </system.web>

</configuration>

Finally, I've got two sample classes that I ripped right out of the test fixtures from the Contrib project, and massaged to fit this demo:

using System.Configuration;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;

namespace  SQLiteEntLib 

{

    public class TestConfigurationSource

    {

        public static DictionaryConfigurationSource CreateConfigurationSource()

        {

            DictionaryConfigurationSource source = new DictionaryConfigurationSource();

            DatabaseSettings settings = new DatabaseSettings();

            settings.DefaultDatabase = "Quotes";

            ConnectionStringsSection section = new ConnectionStringsSection();

            string dbPath = System.Web.HttpContext.Current.Server.MapPath("quotes.db3");

            section.ConnectionStrings.Add(new ConnectionStringSettings("ConnectionString", @"Data Source=" + dbPath , "System.Data.SQLite"));

            source.Add(DatabaseSettings.SectionName, settings);

            source.Add("connectionStrings", section);

            return source;

        }

    }

}

using System;

using System.Data;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace SQLiteEntLib 

{

    public class SQLiteExecuteDatasetHelper

    {

        private const string queryString = "Select * from quotes where authorname like 'a%'";

        private Database db;

        public DataSet  ExecuteDataSetFromSqlString()

        {

            DatabaseProviderFactory factory = new DatabaseProviderFactory(TestConfigurationSource.CreateConfigurationSource());

            db = factory.Create("ConnectionString");

            DataSet dataSet = db.ExecuteDataSet(CommandType.Text, queryString);

            return dataSet;

        }

    }

}

And that's it! The Database object has most of the same familiar methods that are present for all the other Providers. The only thing you don't have is stored procedures.

However, based on all the "stored procs are evil" diatribe I've listened to and read, that could be a good thing!

Note that I am doing a string Replace on the "dbPath" string with Server.MapPath and the name of the database file to make this more portable for ASP.NET.