System.Data.SQLite
Artifact Content
Not logged in

Artifact 17af8687428d907bfbfe25821b8b0f22bb802d78:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title></title>
  </head>
  <body>
    ADO.NET 2.0 SQLite Data Provider<br>
    Version 1.0.12 - Aug 2, 2005<br>
    Interop using SQLite 3.22<br>
    Written by Robert Simpson (<a href="mailto:robert@blackcastlesoft.com">robert@blackcastlesoft.com</a>)<br>
    Released to the public domain, use at your own risk!<br>
    <br>
    This provider was written and tested using the Visual Studio 2005 Beta 2 
    release.<br>
    <br>
    The latest version can be downloaded <a href="http://sourceforge.net/projects/sqlite-dotnet2">
      here</a>
    <br>
    <br>
    <b></b>
    <h2>
      <b>Features:</b><br>
    </h2>
    <ul>
      <li>
      DbProviderFactory support, just add the XML below at the machine.config and/or 
      app.config level.
      <li>
        Full support for ATTACH'ed databases.&nbsp; Exposed as <i>Catalogs</i>
      in the schema.&nbsp; When cloning a connection, all attached databases are 
      automatically re-attached to the new connection.
      <li>
        DbConnection.GetSchema(...) support includes the <i>MetaDataCollections</i>, <i>DataSourceInformation</i>,
        <i>Columns</i>, <i>Tables</i>, <i>Views</i>, <i>Catalogs</i> and <i>Indexes</i>
      keywords.
      <li>
      Enhanced DbDataReader.GetSchemaTable() functionality returns catalog, namespace 
      and detailed schema information even for complex queries.
      <li>
      Named and unnamed parameters.
      <li>
      Full UTF-8 and UTF-16 support.
      <li>
      Multiple simultaneous DataReaders (one DataReader per Command however).
      <li>
      Full support for user-defined scalar and aggregate functions, encapsulated into 
      an easy-to-use base class in which only a couple of overrides are necessary to 
      implement new SQL functions.
      <li>
        Full support for user-defined collating sequences, every bit as simple to 
        implement as user-defined functions and uses the same base class.</li></ul>
    <strong></strong>
    <h2>
      <strong>DbFactory Support (Non-Compact Framework)</strong></h2>
    In order to use the SQLiteFactory and have the SQLite data provider enumerated 
    in the DbProviderFactories methods, you must add the following segment into 
    either your application's app.config or the system's machine.config located in 
    the %SystemRoot%\Microsoft.Net\Framework\v2.xxxx\Config folder:<br>
    <br>
    <pre>
&lt;configuration&gt;
  &lt;system.data&gt;
    &lt;DbProviderFactories&gt;
      &lt;add name="SQLite Data Provider" invariant="System.Data.SQLite" support="3F" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /&gt;
    &lt;/DbProviderFactories&gt;
  &lt;/system.data&gt;
&lt;/configuration&gt;
</pre>
    <br>
    <h3>
      Compiling for the Compact Framework</h3>
    <p>
      Just change the target platform from Win32 to Compact Framework and 
      recompile.&nbsp; <STRONG>The Compact Framework has no support for enumerating 
        attributes in an assembly, therefore all user-defined collating sequences and 
        functions must be explicitly registered.</STRONG>&nbsp; See the <STRONG>testce</STRONG>
      sample application for an example of how to explicitly register user-defined 
      collating sequences and functions.</p>
    <h2>
      <b><A name="redist"></A>Distributing The SQLite Engine and ADO.NET Assembly</b></h2>
    <P>For Win32 platforms, only the <STRONG>System.Data.SQLite.DLL</STRONG> file in 
      the <STRONG>bin</STRONG> folder should be distributed with your 
      application(s).&nbsp; This DLL contains both the managed wrapper and the native 
      SQLite3 codebase.</P>
    <P>For Compact Framework platforms, your application should reference the <STRONG>System.Data.SQLite.DLL</STRONG>
      file in the <STRONG>bin\CompactFramework</STRONG> folder.&nbsp; The <STRONG>System.Data.SQLite.DLL</STRONG>
      and <STRONG>SQLite.Interop.DLL</STRONG> files from the <STRONG>bin\CompactFramework</STRONG>
      folder must be distributed with your application(s).&nbsp; They contain the 
      managed wrapper and the native SQLite3 codebase respectively.&nbsp; The Compact 
      Framework does not currently support managed C++, thus the reason for two 
      files.</P>
    <H2><B>Development Notes Regarding the SQLite 3 Source Code</B></H2>
    <p></p>
    <p>
      Steps for merging the sqlite3 core codebase:</p>
    <p>
      <strong>This section is obsolete, but remains for historical purposes.&nbsp; The 
        1.0.11+ versions of the ADO.NET 2.0 SQLite provider make significant changes to 
        the core sqlite3 codebase in order to support C++ /clr compiling.&nbsp; 
        Specifically, all the implicit casts throughout the core codebase must be 
        modified.&nbsp; There are several hundred of these casts and they must be fixed 
        up every time a new sqlite version is released.</strong></p>
    <ol>
      <li>
        <FONT color="silver">Download the latest sqlite3 source from </FONT><a href="http://www.sqlite.org/download.html">
          <FONT color="silver">http://www.sqlite.org/download.html</FONT></a><FONT color="silver">
        </FONT>
      <li>
        <FONT color="silver">Extract the source code to the <b>SQLite.Interop\src</b> directory 
          of this project </FONT>
      <li>
        <FONT color="silver">Open the <b>src\select.c</b> file. </FONT>
      <li>
        <FONT color="silver">Add <b>#include "../interop.h" </b>to the top of the file 
          where the other include's are. </FONT>
      <li>
        <FONT color="silver">Scroll down the <b>select.c</b> file to around line <b>748</b>.&nbsp; 
          Change the name of the function <b>static void generateColumnNames </b>to <b>static 
            void _generateColumnNames</b> (<i>note the underscore in front of the name</i>).
        </FONT>
      <li>
        <FONT color="silver">Compile it. </FONT>
      </li>
    </ol>
    <b></b>
    <h2>
      <b>Version History</b></h2>
    <b>1.0.12 - August 2, 2005</b><br>
    <ul>
      <li>
        Full support for the Compact Framework.&nbsp; Each build (Debug/Release) now 
        has a&nbsp;platform, either Win32 or Compact Framework.&nbsp; The correct 
        projects are built accordingly.&nbsp; See the&nbsp;<a href="#redist">Distributing 
          SQLite</a> section for information on what files need to be distributed for 
        each platform.&nbsp;</li>
    </ul>
    <b>1.0.11 - August 1, 2005</b><br>
    <ul>
      <li>
        <strong>For everything except the Compact Framework, System.Data.SQLite.DLL is 
          now the <em>only</em> DLL required to use this provider!</strong>&nbsp; The 
      assembly is now a multi-module assembly, containing both the native SQLite3 
      codebase and the C# classes built on top of it.&nbsp; The Compact Framework 
      version (when completed) will not be able to support this feature, so backwards 
      compatibility with the Compact Framework has been preserved for the future.
      <li>
        Fixed a bug in SQLiteCommand.ExecuteScalar() that caused it to stop executing 
        commands once it obtained the first column of the first row-returning 
        resultset.&nbsp; Any remaining statements after the row-returning statement was 
        ignored.</li>
    </ul>
    <b>1.0.10 - June 10, 2005</b><br>
    <ul>
      <li>
      Fixed a bug in the SQLite3.cs Prepare() function that created a statement even 
      when the SQLite engine returned a NULL pointer. Typically this occurs when 
      multiple statements are processed and there are trailing comments at the end of 
      the statement.
      <li>
        Fixed a bug in SQLiteStatement.cs that retrieved parameter names for a 
        parameterized query.&nbsp; SQLite's parameters are 1-based, and the function 
        was starting at 0.&nbsp; This was fine when all parameters were unnamed, but 
        for named parameters it caused the parameters to be out of whack.</li>
    </ul>
    <b>1.0.09a - May 25, 2005</b><br>
    <ul>
      <li>
      Fixed a broken helpfile and corrected some obsolete help remarks in 
      SQLiteFunction.cs<li>Added a version resource to the SQLite.Interop.DLL.&nbsp;</li></ul>
    <b>1.0.09 - May 24, 2005</b><br>
    <ul>
      <li>
      Code merge with the latest 3.21 version of SQLite.
      <li>
        Removed obsolete methods and properties for Whidbey Beta 2</li></ul>
    <b>1.0.08 Refresh - Mar 24, 2005<br>
    </b>
    <ul>
      <li>
      Code merge with the latest 3.20 version of SQLite.
      <li>
        Recompiled the help file to fix a build error in it.</li>
    </ul>
    <b>1.0.08 - Mar 11, 2005<br>
    </b>
    <ul>
      <li>
      Added additional #if statements to support the old beta 1 edition of VS2005.
      <li>
        Code merged the SQLite 3.14 source.</li>
    </ul>
    <b>1.0.07 - Mar 5, 2005</b><br>
    <ul>
      <li>
      Made more optimizations to frequently-called functions, resulting in 
      significant performance gains in all tests.
      <li>
        Recompiled the binaries using the latest VS2005 February CTP, resulting in yet 
        more significant speed gains.&nbsp; The 100k insert test used to take 3.5 
        seconds and the insertwithidentity took almost 8 seconds.&nbsp; With the above 
        two changes, those tests are now executing in 1.9 and 4.9 seconds respectively.</li></ul>
    <p>
      <b>1.0.06 - Mar 1, 2005<br>
      </b>
    </p>
    <ul>
      <li>
      Speed-ups to SQLiteDataReader.&nbsp; It was interop'ing unnecessarily every 
      time it tried to fetch a field due to a logic error.
      <li>
      Changed/Added some code to SQLiteConvert's internal DbType, Type and 
      TypeAffinity functions.
      <li>
      Fixed the SQLiteDataReader to obey the flags set in the optional 
      CommandBehavior flag from SQLiteCommand.ExecuteReader().
      <li>
      Changed the default page size to 1024 to reflect the defaults of SQLite.&nbsp; 
      Ignores the "Page Size" connection string option for memory databases, as tests 
      revealed that changing it resulted in memory corruption errors.
      <li>
        Performance enhancements to the SQLiteCommand and SQLiteStatement classes which 
        reduced the 100,000 row insert execution time as well as the various Function 
        execution times significantly.</li>
    </ul>
    <b>
      <br>
      1.0.05 - Feb 25, 2005</b>
    <ul>
      <li>
      Fixed the SQLite3 C# class step/reset functions to accomodate schema changes 
      that invalidate a prepared statement.&nbsp; Statements are recompiled 
      transparently.
      <li>
      Moved all native DLL declarations to an UnsafeNativeMethods class.
      <li>
      Split several classes into their own modules for readability.
      <li>
      Renamed many internal variables, reviewed access to variables marked as 
      internal and altered their protection levels accordingly.
      <li>
      Due to the presence of the altered sqlite3 codebase and so many added interop 
      functions, I decided to rename the sqlite3 C project and the DLL to 
      SQLite.Interop.DLL.&nbsp; This is the same core sqlite3 codebase but designed 
      specifically for this ADO.NET provider.&nbsp; This eliminates any possibility 
      of someone dropping another build of sqlite3.dll into the system and rendering 
      the provider inoperable.&nbsp; In the future if the folks at sqlite.org finally 
      introduce a method of retrieving column usage for an arbitrary prepared 
      statement, I'll retool this library to be a lightweight function call wrapper 
      around the core binary distribution.
      <li>
      Added [SuppressUnmanagedCodeSecurity] attribute to the UnsafeNativeMethods 
      class which brings VS2005 November CTP execution speeds inline with the 
      December CTP.
      <li>
        Added a <b>bin</b>
      directory to the project root where pre-compiled binaries can be found.
      <li>
        Added a <b>doc</b>
      directory where preliminary documentation on the class library can be found.
      <li>
        Documented a lot more of the classes internally.</li>
    </ul>
    <b>
      <br>
      1.0.04 - Feb 24, 2005</b>
    <ul>
      <li>
      Removed the SQLiteContext class and revamped the way UserFunctions work to 
      simplify the imlementation.
      <li>
      Fixed a counting bug in the TestCases class, specifically in the function tests 
      where I wasn't resetting the counter and it was consequently reporting 
      intrinsic and raw select calls as being much much faster than they actually 
      were.&nbsp; The numbers are now much closer to what I expected for performance, 
      with .NET user-functions still being the slowest, but only by a small margin.
      <li>
      Small performance tweaks to SQLiteDataReader.
      <li>
      Added PageSize to the SQLiteConnectionStringBuilder and subsequently to the 
      SQLiteConnection
      <li>
        Added a PRAGMA encoding=XXX execution statement to the SQLiteConnection after 
        opening a connection.</li>
    </ul>
    <b>1.0.03 - Feb 23, 2005</b>
    <ul>
      <li>
      Fixed up SQLiteCommandBuilder to correct implementation errors, which resulted 
      in an enormous performance boost in the InsertMany test.&nbsp;&nbsp; 10,000 row 
      insert that executed in 1500ms now executes in 500ms.
      <li>
      Fixed several errors in the SQLite3_UTF16 class.&nbsp; ToString() was working 
      incorrectly and the Open() method failed to register user defined functions and 
      collations.
      <li>
      Fixed a bug in SQLiteCommand.ClearCommands() whereby only the first statement 
      was being properly cleaned up.
      <li>
      Fixed a bug in SQLiteDataReader whereby calling NextResult() would not properly 
      reset the previously-executed command in the sequence.
      <li>
        Added an InsertManyWithIdentityFetch test, which appends a select clause to 
        populate the ID of the last inserted row into the InsertCommand, demonstrating 
        ADO.NET's ability to auto-fetch identity columns on insert.</li>
    </ul>
    <p>
      <b>1.0.02 - Feb 21, 2005</b></p>
    <ul>
      <li>
      Tweaks to the xxx_interop functions that return char *'s, so they also return 
      the length.&nbsp; Saves an interop call to get the UTF-8 string length during 
      conversion to a .NET string.
      <li>
      Reworked the whole interop.c thing into interop.h and reduced the code required 
      to merge the main sqlite3 codebase.
      <li>
        Added support for user-defined collations.</li>
    </ul>
  </body>
</html>