Tuesday, March 14, 2006

"Vanilla DAL" Preview

I promised I would post some sample code as soon as the emerging "Vanilla DAL" prototype succeeded in its first database roundtrip. So, here we go...

Vanilla requires a configuration XML-file. The configuration file's schema definition (XSD) makes it easy to let a tool like XMLSpy auto-generate the basic element structure. In this case I entered one sample SQL-statement:

<?xml version="1.0" encoding="UTF-8"?>
  <ConnectionString>Data Source=(local);Initial Catalog=
  Northwind;Integrated Security=True</ConnectionString>
        select  * 
        from    customers 
        where   city = @city and
        (       select count(*)
                      from   orders 
                      where  orders.customerid =
                >= @minordercount

The ADO.NET connectionstring can be specified at runtime as well. So the configuration-file basically tells Vanilla to which database to connect, and which SQL-statements are available for execution. Additionally Vanilla supports a simple dataset-to-db mapping, with no need to hand-code any SQL.

Typically the configuration-file will be compiled into the client's assembly. At runtime the client instantiates a so called DBAccessor:

VanillaConfig config =
IDBAccessor accessor =

IDBAccessor is an interface that every database-specific implementation has to support. Working against this interface, the client will never be contaminated with database-specific code. When connecting to another database, all that is required is to use a different the configuration file. Multiple configurations can be applied at the same time as well.

At this point Vanilla is ready to go and can execute its first command:

accessor.Fill(new FillParameter(
    new ParameterList(
        new Parameter("city", "Salzburg")

Here we populate a datatable with the query's result. All we need to know is the target datatable (here: part of a typed dataset), the name of the database table, and a list of parameters.

Next we will do some in-memory data manipulation, and then update the database accordingly:

foreach (NorthwindDataSet.CustomersRow cust in
    northwindDataSet.Customers) {
    cust.City = "Vienna";
accessor.Update(new UpdateParameter(
    northwindDataSet.Customers, "Customers"));

Let's see how we can execute the SQL-statement from our configuration-file:

accessor.Fill(new FillParameter(
    new StatementID("CustomersByCityAndMinOrderCount"),
    new ParameterList(
        new Parameter("city", "Vienna"),
        new Parameter("minordercount", 2)

And this is IDBAccessor's current interface (subject to change):

public interface IDBAccessor {
    IDbCommand CreateCommand(CommandParameter param);
    IDbConnection CreateConnection();
    IDbDataAdapter CreateDataAdapter();

    void Fill(FillParameter param);
    int Update(UpdateParameter param);
    int ExecuteNonQuery(NonQueryParameter param);
    object ExecuteScalar(ScalarParameter param);

    void ExecuteTransaction(UnitOfWorkList workList);

The API will still be subject of change as this is just a tentative draft. Anyway, this completes our little sneak preview of Vanilla DAL. Please let me know about remarks and suggestions on the Vanilla DAL forums.