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"?>
<Config 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ConnectionString>Data Source=(local);Initial Catalog=
  Northwind;Integrated Security=True</ConnectionString>
  <DatabaseType>SQLServer</DatabaseType>
  <Statements>
    <Statement>
      <StatementType>Text</StatementType>
      <ID>CustomersByCityAndMinOrderCount</ID>
      <Code><![CDATA[ 
        select  * 
        from    customers 
        where   city = @city and
        (       select count(*)
                      from   orders 
                      where  orders.customerid =
                             customers.customerid)
                >= @minordercount
      ]]><Code>
      <Parameters>
        <Parameter>
          <Name>city</Name>
          <Type>NVarChar</Type>
        </Parameter>
        <Parameter>
          <Name>minordercount</Name>
          <Type>Int</Type>
        </Parameter>
      </Parameters>
    </Statement>
  </Statements>
</Config>


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 =
    VanillaConfig.CreateConfig(
    Assembly.GetExecutingAssembly().
    GetManifestResourceStream(
    "VanillaClient.sql.xml"));
IDBAccessor accessor =
    VanillaFactory.CreateDBAccessor(config);


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(
    northwindDataSet.Customers,
    "Customers",
    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(
    northwindDataSet.Customers,
    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.