Here is the first part of the Application Code Blocks. In this part, we will give you a theoretical overview of the Data Access Application Block (DAAB) (Based of what I found for reading on the sites in the resources paragraph). After this examination, we will add a second part, where we will change the DAAB for use with the Oracle DB. After this, we will put a short overview of real life experience with our modified DAAB.
What is DAAB?
The Database Access Application Block is a .Net component that has optimized code on board to access your database. Till version 2, this is only for a SQL database. The component returns SqlDataReader, Dataset and XmlReader objects.
Download, what's included and user requirements?
You can download DAAB from the download center of Microsoft
After you download and installed the component, you will find a new menu under "start --> programs”: Microsoft Application Blocks for .NET. Under this menu you will find:
- The VS.Net project files
- The documentation
Be sure that you have following requirements when running the DAAB version 2:
- Win 2000 / Win XP / Win 2003
- .Net Framework SDK 1.1
- VS.net 2003
- SQL Server 7.0 or later
Why using DAAB?
You have the traditional Data Access to applications (including web). That Data Access can be from different sources:
- Traditional RDBMS (MS SQL, Oracle, ...)
- Custom Data Store (XML file, flat file, ...)
Using this, often leads to redundant, repeated code, which can be written by experienced developers in their sleep. In this case the code repetition increase the change for a bug in the code. And it cost time. We can say that a typical Data Access can be split up in several parts:
- Specify the query
- Create connection object
- Create command object
- Add parameters if necessary
- For a DataReader use ExecuteReader()
For DataSets or DataTables use a DataAdapter and its Fill() method
- Bind the control to the DataReader/DataSet/DataTable
- Close DataReader, Connection
The solution is encapsulation or better-called wrapped classes. Most of you will use a framework or has written their own wrapper. Our wrapper could have step 1 till 5 included.
Another solution is to use the DAAB.
Graphical overview:
Basic procedure:
- Download the application block from the MSDN site
- Navigate in your 'start menu --> program files' to "Microsoft Application Blocks for .Net", select "data access" and select the language of your choice.
- The Application Block Project will open. Build this project.
- Once you got the assembly, do following in your project:
- Add reference to "Microsoft.ApplicationBlocks.data.dll"
- Add Using (C#) or Imports (VB.Net) for the "Microsoft.ApplicationBlocks.data.dll","System.Data" and "System.Data.SQLClient"
Be aware: If creating an ASP.Net project you has to do following steps:
- Copy the DAAB Assembly to your web application's "/Bin" dir
- In the ASP.net web page where you need DAAB add : <%@ Import Namespace=“Microsoft.ApplicationBlocks.Data” %>
Extensive Use of DAAB:
After installing and implementing the DAAB, you got the SqlHelper and SqlHelperParameterCache available in your application.
The SQLHelper class provides us static methods, while SqlHelperParameterCache is used to improve performance.
The SQLHelper class:
The SQLHelper provides us with 13 methods we can use (for a complete list, see the MSDN DAAB section - SqlHelper Class Implementation Details). Each method has it's set of overloads. This gives the developer all the flexibility how they want to access the data and how they want to pass the connection string, transaction and parameters.
We will show an example.
The Execute reader:
Normally you would write this kind of code :
SqlConnection myConnection = new SqlConnection(connString);
const string strSQL = "SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName";
SqlCommand myCommand = new SqlCommand(strSQL, myConnection);
myConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
When using DAAB, we could reduce the code to this:
const string strSQL = "SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName";
SqlDataReader reader = SqlHelper.ExecuteReader(connString, CommandType.Text, strSQL);
Know that the every method has it's own set of overloaded method. For example: the ExecuteReader has also a method where you could pass an already open connection,... Also you have other related methods, like:
- Executing non-returning queries (Update/insert/...)
- Executing scalar-returning queries
- ...
These related methods, have also the overloaded options as the ExecuteReader().
Parameterize:
We can use parameters with the DAAB. DAAB accepts two kinds of parameters:
- Explicit sqlparameter Instances
example:
dgOrders.DataSource = SqlHelper.ExecuteDataset(connString, CommandType.Text, strSQL, new SqlParameter("@CustomerID", value));
- Implicit parameter values
example:
dgProducts.DataSource = SqlHelper.ExecuteDataset(connString,"getOrders", value);
The explicit method can be used with SQL Query as with Stored Procedures. The implicit method works only with Stored Procedures.
In the previous examples we only had one parameter. What if you have more parameters? Well in this case you just include a comma-delimited list of parameter values:
- dgProducts.DataSource = SqlHelper.ExecuteDataset(connString, CommandType.Text, query, new SqlParameter("@ParamName1", value1), new SqlParameter("@ParamName2", value2), …, new SqlParameter("@ParamNameN", valueN));
- dgProducts.DataSource = SqlHelper.ExecuteDataset(connString, "getProducts", Value1, value2, …, valueN);
When using implicit parameters we have a performance issue. DAAB has to do 2 steps:
- Use the DeriveParameters method (SqlCommandBuilder class) to determine the parameters
- Iterated through the parameter array to assign the values to the discovered parameter list.
To solve this problem, Microsoft has added the SqlHelperParameterCache class.
The SqlHelperParameterCache Class:
This class caches the SP parameter list. The class contains 3 public methods to manage parameters. These methods are :
- Cache parameters: CacheParameterSet
- Retrieve cached parameters: GetCachedParameters
- Retrieve the correct parameters for a Stored Procedure: GetSpParameterSet
Internally, the Application block creates a hash table when storing your parameters. The key of this hashtable is a simple string concatenating of the connectionstring and the command text. When reading the parameters we will get the name, direction (In- of Output), the datatype and so on.
When using a Stored Procedure, we can use the 3rd method. This method will connect to the DB, looking for the SP and retrieve all the information about the parameters for this SP.
Future :
Microsoft is working on the next version. On the date of this writing it is version 3. This version is available for test at the GotDotNet Community. This version provides the concept of an abstract provider, whereas version 1 & 2 using a concrete provider (SQLClient). Version 3 will have 3 build-in providers :
So it will be possible to use for instance an Oracle DB, or even (if you really, really wants MS Access).
See you at the next part. In here we will talk about modifying the DAAB so it can be used with an Oracle DB. In part 3, we will also describe our life experience of the DAAB.
Resources:
MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
.Net Data Access Architecture Guide
4GuysFromRolla: http://aspnet.4guysfromrolla.com/articles/070203-1.aspx
GotDotNet: http://www.gotdotnet.com/workspaces/workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431
Microsoft Download :
http://download.microsoft.com/download/VisualStudioNET/daabref/RTM/NT5/EN-US/DataAccessApplicationBlock.msi