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.