DbLinq and Mono - Jonathan Pryor's web log

« Extension Method Documentation | Main | Mono 2.4 and mdoc-update »

DbLinq and Mono

.NET 3.5 introduced Language Integrated Query (LINQ), which allowed for querying groupings of data across diverse "paradigms" -- collections (arrays, lists, etc.), XML, and relational data, called LINQ to SQL. LINQ to SQL support is within the System.Data.Linq assembly, which is one of the assemblies Mono is currently implementing.

However, LINQ to SQL has one limitation: it only works with Microsoft SQL Server and Microsoft SQL Server Compact Edition, leaving numerous other databases users unable to use this assembly.

Enter DbLinq, an effort to provide LINQ to SQL functionality for other databases, including Firebird, Ingres, MySQL, Oracle, PostgreSql, SQLite, and SQL Server. DbLinq provides a System.Data.Linq-compatible implementation for these databases (compatible implying the same types and methods, but located within a different namespace).

Which brings us to Mono. Mono is using DbLinq as the foundation for Mono's System.Data.Linq.dll implementation, allowing Mono's System.Data.Linq.dll to support all the databases that DbLinq supports. Mono also has sqlmetal (based on DbLinq's DbMetal.exe sources), which can be used to generate C# types to interact with databases.

DbLinq On Mono

MonoDevelop can load the DbLinq solutions. However, it has a problem with building all of the projects within the solution. At the time of this writing, MonoDevelop can build the following assemblies: DbLinq.dll, DbLinq.Sqlite_test_mono_strict.dll, DbLinq.SqlServer.dll, DbLinq.SqlServer_test.dll, DbLinq.SqlServer_test_ndb.dll, DbLinq.SqlServer_test_strict.dll, and DbLinq_test_ndb_strict.dll. The *_test* assemblies are unit tests, so this leaves the core DbLinq.dll assembly and SQL Server support.

Thus, DbLinq is usually built with Visual Studio.NET (the free Visual Studio Express can be used). Once built, you can run some of the unit tests under Mono:

cd $path_to_dblinq2007_checkout/build.dbg

# Core tests
$ for test in DbLinq_test.dll DbLinq_test_ndb_strict.dll DbMetal_test.dll ; do \
  nunit-console2 $test \
done
# Verbose output omitted

# SQLite tests
$ nunit-console2 DbLinq.Sqlite_test_mono.dll
# Verbose output omitted

# Plus many tests for the other providers...

Most of the tests require an accessible database, so I've been limiting my current tests to SQLite (as setup is easier).

DbLinq In Mono

As mentioned before, DbLinq is being used to implement Mono's System.Data.Linq.dll. (For those reading the DbLinq source, the Mono-specific bits are within MONO_STRICT conditional code.) This allows us to write code that depends only on .NET assemblies (though this is of dubious value, as the mechanisms used to support SQLite and other databases won't work with .NET proper, but it's still a cute trick).

To play along, you'll need Mono trunk.

1. Grab a SQLite database file to use with LINQ to SQL:

   wget http://dblinq2007.googlecode.com/svn/trunk/src/Northwind.db3

2. Use sqlmetal to generate C# bindings for the database:

   sqlmetal /namespace:nwind /provider:Sqlite "/conn:Data Source=Northwind.db3" /code:nwind.cs

3. Write some code to interact with the generated source code:

   // File: nwind-app.cs
   // Compile as: 
   //    gmcs nwind-app.cs nwind.cs -r:System.Data \
   //    	-r:System.Data.Linq -r:Mono.Data.Sqlite
   using System;
   using System.Data.Linq;
   using System.Linq;
   
   using Mono.Data.Sqlite;
   
   using nwind;
   
   class Test {
       public static void Main ()
       {
           var conn = new SqliteConnection (
                   "DbLinqProvider=Sqlite;" + 
                   "Data Source=Northwind.db3"
           );
           Main db = new Main (conn);
           var pens =
               from p in db.Products 
               where p.ProductName == "Pen"
               select p;
           foreach (var pen in pens) {
               Console.WriteLine ("     CategoryID: {0}",  pen.CategoryID);
               Console.WriteLine ("   Discontinued: {0}",  pen.Discontinued);
               Console.WriteLine ("      ProductID: {0}",  pen.ProductID);
               Console.WriteLine ("    ProductName: {0}",  pen.ProductName);
               Console.WriteLine ("QuantityPerUnit: {0}",  pen.QuantityPerUnit);
               Console.WriteLine ("   ReorderLevel: {0}",  pen.ReorderLevel);
               Console.WriteLine ("     SupplierID: {0}",  pen.SupplierID);
               Console.WriteLine ("      UnitPrice: {0}",  pen.UnitPrice);
               Console.WriteLine ("   UnitsInStock: {0}",  pen.UnitsInStock);
               Console.WriteLine ("   UnitsOnOrder: {0}",  pen.UnitsOnOrder);
           }
       }
   }

4. Compile:

   gmcs nwind-app.cs nwind.cs -r:System.Data -r:System.Data.Linq -r:Mono.Data.Sqlite

5. Run:

   $ mono nwind-app.exe 
        CategoryID: 
      Discontinued: False
         ProductID: 1
       ProductName: Pen
   QuantityPerUnit: 10
      ReorderLevel: 
        SupplierID: 1
         UnitPrice: 
      UnitsInStock: 12
      UnitsOnOrder: 2

Notice that we use the database connection string to specify the database vendor to use, specifically the DbLinqProvider value specifies the database vendor, and must be present when connecting to a database other than Microsoft SQL Server (which is the default vendor).

If using the DataContext(string) constructor directly (and not through a generated subclass as used above), you should also provide the DbLinqConnectionType parameter, which is the assembly-qualified type name to use for the IDbConnection implementation. This allows you to use multiple different IDbConnection implementations that use similar SQL implementations, e.g. Mono.Data.Sqlite.dll and System.Data.SQLite.dll, both of which wrap the SQLite database.

blog comments powered by Disqus