SnapDAL Configuration

Configuration is done in one key file, DataFactory.config, and sets of other supporting files for statements.

DataFactory.config

1. SqlClient - the .net Sql Server ado.net provider
2. OleDB - the .net OleDB ado.net provider
3. Mock - a provider built from the .net Mock Objects project

<?xml version="1.0" encoding="utf-8" ?> 
<DataFactory>
  <Provider name="ODBC" assembly="System.Data">
    <Connection type="System.Data.Odbc.OdbcConnection" />
    <Command type="System.Data.Odbc.OdbcCommand" />
    <Parameter type="System.Data.Odbc.OdbcParameter" />
    <DataAdapter type="System.Data.Odbc.OdbcDataAdapter" />
    <DataReader type="System.Data.Odbc.OdbcDataReader" />
  </Provider>
</DataFactory>

The file is pretty self explanatory. Under the DataFactory element are Provider elements. Under those you define the full Type name that your provider uses to implement the listed ado.net interfaces. The provider Name attribute is very important to you. In this case, because the "name" attribute is "ODBC", you would use "ODBC" as the provider type to create a DataFactory that uses and ODBC connection string and the Odbc.* parts of ado.net to execute statements. Here is an example of a Provider element to setup the ByteFx MySql provider.

  <Provider name="MySql" assembly="ByteFX.MySqlClient.dll" />
    <Command type="ByteFX.Data.MySQLClient.MySqlCommand" />
    <Connection type="ByteFX.Data.MySQLClient.MySqlConnection" />
    <Parameter type="ByteFX.Data.MySQLClient.MySqlParameter" />
    <DataAdapter type="ByteFX.Data.MySQLClient.MySqlDataAdapter" />
    <DataReader type="ByteFX.Data.MySQLClient.MySqlDataReader" />
  </Provider>
  
  to use:
  DataFactory dal = new DataFactory(connectionString, "MySql");

Statement configuration

In the introduction we discussed the statement xml files used to declare the statments available to SnapDAL by name, as opposed to the "anonymous" dynamic sql style. SnapDAL requires these files to be located in a well known folder, or it will default to the bin folder of whatever app it is running under. The directory structure is important also. Let's call the top level directory the StatementRoot (it's also known as CacheDirectory for historical reasons). For each provider you should have a subdirectory. These subdirectories are used for provider specific statements and can be used for other provider specific configuration. So, the structure looks like this.

StatementRoot
   SqlClient
   Mock
   OleDB
   ODBC
   *any other providers*

Place statement files in the StatementRoot when the syntax of the SQL will work for all providers. For example "select * from customers" would work with almost any ansi 92 compatible database. So, whether your code is using SqlClient or MySql, by placing the statement in the StatementRoot, they would all share the statement.

Place statment files in the provider subdirectories when there is something unique to that provider. For example SnapDAL strongly encourages the use of parameterized queries. However, ODBC providers don't accept named parameters. So for the SqlClient verion you would have a statement such as that shown in our intro. Since it works for OleDb and SqlClient, you would place it the StatementRoot. But to override the the statement for ODBC, place this config file in the ODBC subdirectory.

<?xml version="1.0" encoding="Windows-1252" ?>
<DataFactory>
    <Statement name="query_customer" type="Text">
        <Sql>Select companyname from customers where customerid=?</Sql>
            <Parameters>
                <Param name="customerid" SqlName="@customerid" type="nchar" maxLength="5" direction="Input" />
            </Parameters>
    </Statement>

</DataFactory>

Statement file names

SnapDAL uses file names to indicate some important things. In normal use, SnapDAL reads a given statement file just once. This is for the performance gain that holding these definition in memory gives you. But, depending on the file name, the statement will be loaded at different times. Files that end with the extension ".config", must be named for the statement for the same statement it contains. For example the file listed in the introduction would be called "query_customers.config". Case matters, the statement name and file name must match! A config file will be loaded "on demand", not happening until the first time the statement is used.

Files ending in ".xml" are treated a little differently. Most are ignored unless included (read below). The first time SnapDAL is used, it will look to the statement root directory for any file(s) called Statements.xml It will look in all provider subdirectories as well and load this file. So, the net effect is that and statements defined in Statements.xml will be preloaded before any execute commands are called.

The syntax for Statements.xml is identical to that of a config file described in the introduction with two exceptions.

• A Statements.xml file can contain any number of Statements. Just put them together
• A Statements.xml file can "Include" statements from any another file in the same XML format

Here is an example

<DataFactory>
    <Statement name="system_sp_who2" type="StoredProcedure" >
        <Sql>sp_who2</Sql>
            <Parameters>
                <Param name="RETURN_VALUE" SqlName="@RETURN_VALUE" type="int" direction="ReturnValue" />
            </Parameters>
    </Statement>
    <Statement name="query_customer" type="Text">
        <Sql>Select companyname from customers where customerid=@customerid</Sql>
            <Parameters>
                <Param name="customerid" SqlName="@customerid" type="nchar" maxLength="5" direction="Input" />
            </Parameters>
    </Statement>
    <Include name="AnotherFileOfStatements.xml" />	
</DataFactory>

This file shows a number of things to help summarize the options available to you. First of all the Include element has one option, the name of the file, relative to the Statements.xml file, where the included file should exist.

There are other attributes that can be set on statements. There are largely defined on the Statement and Parm classes in the documentation. They are not all required at the same time, for example a maxLength property wouldn't make sense on a int parameter type.

Another important attribute is the "type" attribute. The two options are Text and StoredProcedure. Make sure to get that right, or you can get some unexpected results.

Stored procedures have one other feature built in. You notice in the last example that the attribute "RETURN_VALUE" is a parameter of the stored procedure. Return values are special in that supplying the parameter is acceptable, but for stored procedures, the element will be ignored and supplied automatically by the statement loader. For return values from type Text, you would have to include it as SnapDAL has no way to know if a return parameter would be present in that case.

The type attribute of the Param element

This deserves a longer explanation. While ADO.net has defined many aspects of data access in a generic way through the interfaces, there are some really big holes making truly generic access difficult. One of these areas is the types of parameters that get sent to the database which are always specific to the provider. For example SqlServer has SqlDbType.Int16, SqlDtType.Int32 and so on. There is a more generic DbType.* available however not all providers are required to work with these definitions uniformly. So, SnapDAL has taken this approach to the problem.

• All parameter types are a single, all lower case name
• standard clr types such as int, string, decimal etc., are mapped to the correct provider specific types
by the provider.
• If the DbType names are used, these have also been mapped to the specific provider types with the same caviat as with the clr types
• You can always use a lowercase version of the provider specific type, such as int32 for SqlServer. In this case you must be aware that there may not be a generic equivalent if the statement is to be used by another provider
• At present, any custom provider, including the ODBC provider, will use the DbType mappings.
• The Mock provider can only use the generic DbType names. Many of the Sql Server have been mapped back to the DbType names, but some will certainly fail
• There has not been extensive testing of all combinations of all possible specific and generic mappings as the number of possibilities is combinatorial and unknowable with the ability to add custom providers. So, please report any difficulties to our mailing list and we'll turn it around as quickly as possible
• It should be possible to add another element to DataFactory.config to allow customer providers a listing of mappings from clr or DbType to the specific provider type for that custom provider. However, it's not done now. If you want to help out here ......

Proably the best advice is to use clr types (string, int, decimal, datetime) whenever possible and allow the provider to match an appropriate parameter type as best it can. If that doesn't work, use a DbType. If that isn't possible, use a lower case version of the specific type for the provider which will work for all but custom providers.