Configuration is done in one key file, DataFactory.config, and sets of other supporting files for statements.
DataFactory.configThis file is placed in the same directory as SnapDAL.dll. It's purpose is to setup the providers that are available to your code beyond the built in providers. The built in providers are
<?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");
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.
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.
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.