Adding SQLite Functionality to Mini SQL Query

The sub-title should probably be – Adding a non-standard database provider to Mini SQL Query...

If you are familiar with the generic approach of my query tool it would probably come as no surprise that adding support for non-standard providers is easy (when I say non-standard I simply mean one that is not installed by default with the .net framework).

I say easy because I simply run everything off a current provider/connection. There are almost no provider specific tweaks in the code, so if a provider supports the get schema methods and the core creation of commands, plus the execution of a command you can use it with Mini SQL query. I get the list of providers via a call to the System.Data.Common.DbProviderFactories.GetFactoryClasses() method. Here is a snippet that just gets the “names” of the providers:

namespace MiniSqlQuery.Core

{

       /// <summary>

       /// Some basic helper functions.

       /// </summary>

       public static class Utility

       {

              /// <summary>

              /// Returns an array of SQL provider types supported by the current platform.

              /// </summary>

              /// <returns>An array of SQL provider types.</returns>

              public static string[] GetSqlProviderNames()

              {

                     DataTable providers = DbProviderFactories.GetFactoryClasses();

                     List<string> providerNames = new List<string>();

                     foreach (DataRow row in providers.Rows)

                     {

                           providerNames.Add(row["InvariantName"].ToString());

                     }

                     return providerNames.ToArray();

              }

I have been using Pheonix Software’s implementation of a managed SQLite provider. I have been using http://sourceforge.net/project/showfiles.php?group_id=132486...

If you install the provider SQLite will show up with no further effort. You can also add the provider to the DLL search path of Mini SQL Query and update the “MiniSqlQuery.exe.config” file. Here is a template, just add the “system.data” section at the bottom (and ensure the “System.Data.SQLite.dll” is in the application path!):

<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" />

    </DbProviderFactories>

  </system.data>

</configuration>

For example:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

       ...

    </configSections>

        ...

    </applicationSettings>
    <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" />
      </DbProviderFactories>
    </system.data>
</configuration>

See the SQLite help docs for more details on usage, the GAC, versioning etc.

You can use that process for any third party provider....