ASP.NET: Using SQLite with Enterprise Library 3.1

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>

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.

The download has a small SQLite database of quotations by famous authors (a subset, for size) and the ASP.NET app displays the results of a query in a GridView. You can run this without downloading anything as it already contains all the required assemblies. Just unzip, load into Visual Studio 2005, and press the green button!

Of course, I recommend that you download the three projects and sources from the links at the very top of this article and install each so you can look at code and learn more.

or at least, my take on it:

http://petesbloggerama.blogspot.com/2007/07/database-agnostic-or-database-specific.html