SnapDAL Requires only three things in order to operate and execute a query. Like any database call, it requires a connection string appropriate for the type of database you are connecting to. It also requires an xml configuration file for each database call you make that defines the text and paramters of the call. This is placed in the bin folder of the application by default. Finally it requires an identifier for the type of provider, such as SqlClient, OleDb etc.. This tells SnapDAL the specific ADO.net provider to use when making the call.
Let's start with the xml config file. This example simply calls the well known Northwind database for a particular customer. In plain SQL this would like like this:
declare @cname nvarchar(40) set @cname = 'test' select * from customers where companyname = @cname
To do the equivalent of this statement using SnapDAL you would first create the xml config file for the query. SnapDAL can help automatically generate these files from your existing stored procedures or tables (SqlClient only at present) but they are also very simple to create manually.
<?xml version="1.0" ?> <DataFactory> <Statement name="query_customers" type="Text"> <Sql>Select * from customers where companyname=@cname</Sql> <Parameters> <Param name="cname" SqlName="@cname" type="nvarchar" direction="Input" /> </Parameters> </Statement> </DataFactory>
Here we have created a statement that will be refered to by the name "query_customers". It's a normal parameterized sql statement, type="Text" as opposed to a "StoredProcedure". The actual sql that will be executed is in the Sql element. Notice the parameter cname is in the text of this element. This is related to the Parameters element where the name you will refer to the parameter in code is releated to the element you will refer to in the actual sql.
Next, the relevant code. This is quite simple:
using System; using System.Data; using System.Collections.Specialized; using SnapDAL; string connString = "standard connect string"; string cname = "Around the Horn"; DataFactory dal = new DataFactory(connString, "SqlClient"); HybridDictionary parms = new HybridDictionary(); parms.Add("cname", cname); IDataReader rdr = dal.ExecuteDataReader("query_customers", parms); using (rdr) while (rdr.Read()) //get your results from the data reader
This code shows some of the "helper" role of SnapDAL. The way parameters are passed is probably the most notable difference between the SnapDAL approach and the hand coded approach. Parameters are passed by using a dictionary collection. HybridDictionary was chosen because it can be very lightweight when the collection size is small. The name of the parameter matches the name in the config file for the statement. So, even though the real database call uses a parameter name of "@cname", the code will use cname. This allows you to have an application specific name for your parameters that doesn't have to match the actual parameter name at all.
In this simple example, you may not immediately see any great advantage over using ADO.Net directly. The number of lines of code is less in the c# code but since you have to create the xml config, it may be a wash. Even with this example, SnapDAL has some advantages.
So, at this point you really just need to get your system setup and try it all out. To work with the samples or to walk through the unit tests, you must have a copy of sql server or msde at your disposal. Since Sql Server comes with the northwind sample database, there are no special instructions for use on that. You only need to make sure your NT login works. If not you will find that your logins fail. Find the various config files for the applcations you are working with in the bin folder and change the connection string to one appropriate for your configuration.