Introduction

I'm tired of watching tutorials and explanations of how to implement SQLite in Enterprise Library which did not reach anything or official articles worked, configurations, nothing about for a single example of such could be put to work, route until reaching the version 6 it appears completely closed the support! I have worked on a fully functional example of the necessary provider to make this work!

Background

It's true that SQLite not support stored procedures, but this is not an inconvenience, since it can create queries that simulate stored procedures, do these simulations are not slow, think there is no longer the problem of SQL Server, no connections, everything works local, direct, and SQLite can create custom functions to avoid the problem.

This provider and adapter not only creates the necessary tables to work, makes the inserts as you would the stored procedures, the created tables and relations is fully compatible with the work of the original Trace Listener.

Using the code

Simply in your App.config / Web.config attach the adapters, the second isn't necessary because we not use EL6, the intention is use in the future, for example with Entity Framework

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EL6" />
      <add name="SQLite Data Provider EL6" invariant="System.Data.SQLite.EL6" description="EnterPrise Library 6 Data Provider for SQLite" type="EntLibContrib.Data.SQLite.SQLiteDatabase, EntLibContrib.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>

And this in your Entlib.config

<configSections>
...
    <section name="dataConfiguration" 
      type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, 
        Microsoft.Practices.EnterpriseLibrary.Data, Version=6.0.0.0, Culture=neutral, 
        PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
  </configSections>

  <dataConfiguration defaultDatabase="Service_Dflt">
    <providerMappings>
      <add databaseType="EntLibContrib.Data.SQLite.SQLiteDatabase, 
                EntLibContrib.Data.SQLite" name="System.Data.SQLite.EL6" />
    </providerMappings>
  </dataConfiguration>

  <connectionStrings>
    <add name="Service_Dflt" providerName="System.Data.SQLite.EL6" 
       connectionString="Data Source=|DataDirectory|ELSqLiteLog@|CurrentDate|.db;Version=3;" />
  </connectionStrings> 

    <listeners>
...
      <add name="Database Trace Listener" 
          type="EntLibContrib.Data.SQLite.FormattedDatabaseTraceListener, EntLibContrib.Data.SQLite"
          listenerDataType="EntLibContrib.Data.SQLite.Configuration.
                      FormattedDatabaseTraceListenerData, EntLibContrib.Data.SQLite"
          databaseInstanceName="Service_Dflt" 
          formatter="Text Formatter" /> 
    </listeners>

Now you can use the "Database Trace Listener" in your <categorySources> and <specialSources> sections!

Points of Interest

Ok Ok, this is only how can you configure the Adapter and the TraceListener, but what about it, where is the magic? all the magic is in FormattedDatabaseTraceListener, you can see the code inside of the attached file.

This custom DatabaseTraceListener, is the modified version of the original of the MSSQL, the table creation are inside of code (scripts), the queries than emulates the stored procedures are too inside, because the difference of the original FormattedDatabaseTraceListener only has 2 parameter less (writeLogStoredProcName & addCategoryStoredProcName) and the calls, the result is a equal work than the original

The SQLiteDatabase object has a little interesting present for you "|CurrentDate|" Environment Variable, can you use in the ConnectionString to make a Log file each day!

History

1.1 Now supports Visual Studio 2010 Framework 4 with Enterprise Library 5.
1.0 Initial public release of the provider, adapter, and example.

Enterprise Library 6 with SQLite Logging and Exception Handling, REAL!