My last post covered getting Berkeley DB up and running with .NET. Now it’s time to take it one step further and build the open source System.Data.SQLite ADO.NET library, replacing the SQLite 3 engine with Oracle’s version of the SQLite.Interop C library that gets embedded into the .NET System.Data.SQLite assembly.

In other words, when you complete the steps below, you’ll have a System.Data.SQLite library that can, supposedly, drop into your .NET projects that currently use the ADO.NET library found on the sqlite.org site. The real difference is that instead of SQLite with single threaded writes, or file locking for writes rather, you will be using the latest Berkeley DB storage engine which supports page level locking to allow more writers, assuming the writers are writing to different pages.

For more information on Oracle’s implementation of the SQLite API which they call the Berkeley DB SQL API and to learn where they differ, you should read the whitepaper: Oracle Berkeley DB SQL API vs. SQLite API.

1. Download the Berkeley DB dbsql-adodotnet-5.2.28.zip source.
2. Unlock the zip file (Right-click and select Properties and click the Unlock button.)
3. Extract the contents to an empty directory of your choice.
4. Open the SQLite.NET.2010.sln solution file.
5. Choose Release and then build.
6. Optionally show all files in the System.Data.SQLite.2010 project and open the Assembly.cs file and change the AssemblyProduct string to something like “System.Data.SQLite-BDB” so that it will show up in file Properties Details tab. This is the only way you will know that this assembly has the Berkeley DB interop engine built into rather than the SQLite engine.

If you’re curious, do a diff between the SQLite.Interop project files in the Oracle version and the original sqlite.org version. Clearly very different animals. Now the only thing that is left is to write up a nice little test in C# to compare the two libraries. The subject of a future post.

UPDATE #1 (9/5/2011): Tests today show that this “new” System.SQLite.Data library DOES NOT create a Berkeley DB database. At least as far as I can tell. So while there is a libdb_sql50.dll in the main Berkeley DB build that implements the sqlite3.dll API, there is no way that I can find so far to use that library from C#. More experimentation to come.

UPDATE #2 (9/5/2011): Modify the UnsafeNativeMethods.cs with line 34 as

private const string SQLITE_DLL = “libdb_sql52.dll”;

and then change the solution’s projects conditional compilation symbols to “SQLITE_STANDARD” and then build. This prevents the SQLite interop embedded DLL from being used. Now when you run your test app, you’ll need to copy to the bin directory of your test app the new built DLLs (see previous post):

libdb_sql52.dll
libdb_stl52.dll
libdb52.dll

I’ll post more on my tests later. So far, I’ve got the basics working but when I push the limits, I get some nasty crashes, so I’m skeptical of this scheme to use the SQLite API over the Berkeley DB engine.