Introduction
SQLite is a great solution for a portable SQL database. There is even a .NET class that wraps SQLite into managed code that follows the ADO.NET design. I needed a self-contained database file, for a data driven application that could be passed between clients and computers. The C# System.Data.SQLite assembly and helper classes worked very well until I needed to insert BLOBs into the row set. In addition to a 64-bit size descriptor mismatch in the API for BLOBs, the C# helper classes required that the BLOBs fully reside in memory prior to the execution of the SQL INSERT or UPDATE command. Though I would recommend use of the System.Data.SQLite first, because it is fully managed and complies with the ADO.NET model, to get what I needed I had to wrap the unmanaged version. This gave me more control over the characteristics of the SQLite engine, such as increasing the supported BLOB size from 1TB to 2TBs as well as the direct handling of file I/O from the SQLite engine through a file I/O loadable extension.
The download includes:
- SQLite3ODP.cs - the source for the managed ObjectDataProvider class for SQLite
- sqlite3.dll - a 64-bit Windows DLL of the 3.8.7.4 open source code base for SQLite built with custom flags for larger row storage capacity
- sqlite3.exe - a 64-bit command shell of SQLite built with matching custom flags; which I find useful for the setup and maintenance of the database as well as any needed debugging of SQL statements
- sqlite3 directory of the 3.8.7.4 SQLite C source including the file I/O extension and Visual Studio 2013 solution files to build the unmanaged .dll and .exe deliverables
Background
See http://sqlite.org/ for the latest SQLite source and documentation.
http://tech.pro/tutorial/852/csharp-tutorial-writing-a-dotnet-wrapper-for-sqlite was reference for this work.
Caveats
Unlike the System.Data.SQLite assembly, the data objects returned from SQLite3ODP are not bi-directional, any data changes made to the returned data objects will not be automatically updated back to the database.
There is a SQL injection risk if you allow substitutions into the SQL command from open-ended user inputs. Converters can be used to mitigate this risk as well as ensure that embedded quotes are handled correctly in the SQL statements.
Part I: XAML Usage
To use the SQLite3ODP in your XAML, load the DataProvider and the Converters into your <Page> or <Window> in the resource section:
<Page.Resources>
<local:SQLite3DataProvider Connection="
[ApplicationData]MyProject\TheDB.sqlite3" x:Key="ARDVARC" />
<local:SQLite3QueryConverter x:Key="SQLite3QueryConverter" />
<local:SQLite3NonQueryConverter x:Key="SQLite3NonQueryConverter" />
</Page.Resources>
When the <Page> (or <Window>) loads, the SQLite3DataProvider will be instantiated with a simple connection string to a database file. This can be an absolute file path, though shown here is a notation that supports the substitution of any of the Environment.SpecialFolder enums to provide the root path for the database file. The Key attribute, after the Connection attribute, is used later to reference this instance of the SQLite3DataProvider.
<ComboBox x:Name="_Deployment" >
<ComboBox.ItemsSource>
<MultiBinding Converter="{StaticResource
SQLite3QueryConverter}" Mode="OneTime">
<MultiBinding.ConverterParameter>
SELECT id, name FROM Deployments ORDER BY id ASC
</MultiBinding.ConverterParameter>
<Binding Source="{StaticResource ARDVARC}" />
</MultiBinding>
</ComboBox.ItemsSource>
<ComboBox.DisplayMemberPath>name</ComboBox.DisplayMemberPath>
<ComboBox.SelectedValuePath>id</ComboBox.SelectedValuePath>
</ComboBox>
Here, a ComboBox is instantiated with selection values and names from a specified SQL query. The SQLite3QueryConverter expects the SQL command in the ConverterParameter and the data provider as the first binding source. Note that the DisplayMemberPath and the SelectedValuePath have to match the column names returned from the SELECT statement.
<ListView x:Name="_Artifacts"
IsSynchronizedWithCurrentItem="True" SelectionMode="Single" >
<ListView.View>
<GridView>
<GridViewColumn Header="ID"
DisplayMemberBinding="{Binding Path=id}" />
<GridViewColumn Header="Type"
DisplayMemberBinding="{Binding Path=name}" />
<GridViewColumn Header="Source"
DisplayMemberBinding="{Binding Path=source}" />
</GridView>
</ListView.View>
<ListView.ItemsSource>
<MultiBinding Converter="{StaticResource
SQLite3QueryConverter}" Mode="OneWay">
<MultiBinding.ConverterParameter>
SELECT a.id, at.name, a.source FROM ArtifactDeployments
AS ad JOIN Artifacts AS a ON ad.artifactID = a.id JOIN ArtifactTypes
AS at ON a.artifactType = at.id WHERE ad.deploymentID = '{1}'
ORDER BY ad.precedence ASC, at.id ASC
</MultiBinding.ConverterParameter>
<Binding Source="{StaticResource ARDVARC}" />
<Binding ElementName="_Deployment" Path="SelectedValue" />
</MultiBinding>
</ListView.ItemsSource>
</ListView>
Here, a ListView is populated from a SQL query that includes the injected value from the selected item of the previous ComboBox. The ListView's columns are mapped to the SQL SELECT columns via the GridViewColumn definitions. The MultiBinding.ConverterParameter is used to specify the SQL command, with its many gratuitous JOINs. But note the WHERE clause that specifies a C# style replacement parameter (e.g. '{1}'). Multiple replacement parameters are supported and are matched to the ordered bindings specified in the XAML. If needed, converters can be used on these substitution bindings to mitigate the risk of SQL injection or coerce single quote compliance to keep the SQL statement non-hostile and well formed. The first binding listed is for the SQLite DataProvider, referenced by the x:Key specified in the Resource section of the XAML. Subsequent bindings (if any) are used to provide substitution values into the ConverterParameter which modifies the SQL query at runtime.
Part II: Code-Behind Usage
SQLite3DataProvider s3db_ = MyWindow.Resources["ARDVARC"] as SQLite3DataProvider;
The above line of code retrieves the instantiated SQLite3DataProvider from the WPF <Page> or <Window>. In the code-behind, this class provides two versions each of three database manipulation functions:
Query(string command)/Query(string command, string[] parms, objects[] values)NonQuery(string command)/NonQuery(string command, string[] parms, objects[] values)Scalar(string command)/Scalar(string command, string[] parms, objects[] values)
Query() returns a DataView of results from the query, NonQuery() returns an int of the number of rows affected by the 'nonquery' command, and Scalar() returns the value of the first column and the first row of the returned data set or null if no values are returned.
if (s3db_.NonQuery("INSERT INTO Releases (releaseState,name) VALUES (1,'.')") == 1)
releaseid = (Int64)s3db_.Scalar("SELECT MAX(id) FROM Releases");
The above code relies upon the AUTOINCREMENT feature set on the Releases table to set the id of the newly inserted row. It double checks that only one row was inserted from the return value of the call to NonQuery(). And then, it retrieves the value of the row id of newly created row. The return from Scalar() must be cast as the SQLite3DataProvider does not provide implicit casting operators.
byte [] blob = (byte [])s3db_.Scalar(
"SELECT body FROM Blobs WHERE artifactID=@artifactID AND releaseID=@releaseID",
new string [] {"@artifactID", "@releaseID"},
new object [] {artifactid, releaseid}
);
SHA1 sha_one = SHA1.Create();
s3db_.NonQuery(
"UPDATE Blobs SET fingerprint=@fingerprint
WHERE artifactID=@artifactID AND releaseID=@releaseID",
new string[] { "@artifactID", "@releaseID", "@fingerprint" },
new object[] { artifactid, releaseid, sha_one.ComputeHash(blob) }
)
Here a BLOB is read out of the database and a SHA1 fingerprint is computed and put back into the database. Unlike C# substitutions used in the XAML converters above, these calls use the SQLite approach, which is required for the lower-level BLOB support. The first parameter to the call (the SQL statement) is embedded with '@' prefixed substitution placeholders. The second parameters to the call matches the substitution placeholder names with the order of the values array passed in as the third parameter.
Leveraging SQLite Extensions
s3db_.NonQuery(
"INSERT INTO Blobs(artifactID, releaseID, date, size, body) Values
(@artifactID, @releaseID, @date, @size, readfile(@filepath))",
new string [] {"@artifactID", "@releaseID", "@date", "@size", "@filepath"},
new object [] {artifactid, releaseid, file_info.LastWriteTimeUtc.ToString(),
file_info.Length, file_info.FullName}
);
SQLite has command extensions. They are suprisingly easy to write, given moderate expertise in old-school C programming. Pick a useful data manipulation or aggregation function and it can be plugged into the SQLite engine and applied to any data set through a SQL statement.
One of the useful stock ones from the SQLite source site and compiled into the downloadable sqlite3.dll, is the file I/O extension. You can see it used in the above code where 'readfile()' is used in the SQL statement as if it were a standard SQL function (like COUNT( )). The file I/O extension implements a 'readfile()' and a 'writefile()' command to import and export a column's data directly from and to Operating System files.
Part III: The SQLite DataProvider Class Implementation Notes
C# does an amazing job handling the transitions between managed and unmanaged code. A few of the rough edges are that constants and defines must be re-specified by hand. Similarly, with functions exported from the sqlite3.dll. If anything is copied incorrectly or changes in a future release of SQLite, errors and exceptions can be introduced.
Because of the nature of the XAML integration, the Connection property of the class is the true constructor. Equivalent to a very primitive database connection string, this is where the explicit path to the SQLite database is set or reset. As mentioned earlier, I included a nonstandard substitution to Environment.SpecialFolder enums for my project's convenience.
Unlike memory or file handles that require strict, sparse, and paired Open() and Close() handling, the SQLite3DPO has loosely bound and reentrant Open() and Close() handling. For the most part, you will not need to call these functions from your application. In part, this is because WPF has a just-in-time instantiation practice; thus the low-level database open call is separated from the object instantiation and made automatically, as needed, closer to when the data is needed by the application. Thus missing, multiple, or unpaired calls to Open() and Close() are not disruptive. Close() can be used to flush file writes and Dispose() can be used to release unmanaged resources.
Conclusion
SQLite is a solid database infrastructure, especially for solutions that require portability. This class has been implemented to minimize the C# and XAML integration needed to leverage SQLite database functionality. My hope is that you will find it useful too.
