Data Access Application Block

Today I will be showing you a simple example of using the Data Access Application Block from the Enterprise Library v3.1.  Here I’ll show how to use the Enterprise Library Configuration utility to create your app.config or web.config sections, so that the application block knows where to get it’s info.   After implementing the method using the Data Access Application Block I’ll show you what it’s like without using it and you can see for yourself how much easier it is.

We’ll be using the Northwind database plus a simple stored procedure we’ll add ourselves.  You can download the Nortwind database directly from Microsoft.  Once you’ve got it installed add the following stored procedure:

create Procedure [dbo].[GetProductsByCategory] 
(  @CategoryID int  ) 

AS 

SELECT ProductID, ProductName, CategoryID, UnitPrice FROM Products WHERE CategoryID = @CategoryID 

Configure

The first thing we need to do is create some entries in our config files so that the Data Access Application Block knows how to connect to the database. So fire up the Configuration Utility which you should find in your start menu under” Microsoft patterns & practices -> Enterprise Library 3.1-May 2007 ->Enterprise Library Configuration”.

Click For Image

Right Click on Enterprise Library Configuration and select “New Application”

Click for Image

Right click on Application Configuration and select “New->Data Access Application Block”

Click For Image

In the right hand side, in the ConnectionString Box, click the elipses (…) and populate the Connection Properties dialog box according to your database settings and make sure to select the Northwind Database you either already had or installed earlier.

Click for Image

 That’s the bare minimum amount of information you need to provide to get started with the Data Access Application Block.  If your environment is more complex and your application uses more than one database, you can add another connection string, by right clicking on the Connection String item in the left hand menu.

Now we are going to save this information by clicking the Save Icon at the top of the page, name this file something meaningful to you and put it somewhere you will find it later.

Click For Image

 That’s it for the Configuration Tool.  Now on with the coding:

Code
So for the code portion we’re going to implement a dataset that grabs all the products from the Nortwind database by their CategoryID, that’s what the stored procedure we added earlier does.  For ease of explanation we’ll be doing all this in the code behind of the Default.aspx page but ideally you’d do this as an external library.

Let’s start with an empty web project. Fire up Visual Studio (2005 or 2008) for this demo.  Click “Create Project and Select Visual C# and ASP.NET Web Application from the Templates box on the right.  If anyone wants to see it in VB drop me a line.  Name the project like I have here:

Click for Image

 The next thing you need to do is make sure to bring in the Configuration Settings we made earlier.  Find that document you saved earlier and open it in Notepad.  First take the XML Element named <section> that should look like:

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

Copy this into your web.config file just before the end of the <configSections> tag like this:

Click for Image

 Next, from the file we made earlier take both the <dataConfiguration> and <connectionStrings> settings and copy them over the <connectionStrings/> element so it looks like this:

Click for Image

 Now add the following two references Enterprise Library Data Access Application Block and Enterprise Library Shared Library, which you can find in the .Net tab of the references dialog box. (Right Click on References in your project view.)

Open up the Default.aspx and drag and drop a Grid View Controll from the tool box onto the design surface: (note I left the ID as Gridview1)

Click for Image

Now in the code behind add the following references to your “using” statements:

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

in your partial class create the following method:

public DataSet GetProductsInCategory(int Category)

        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "GetProductsByCategory";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
           DataSet productDataSet = db.ExecuteDataSet(dbCommand);
            return productDataSet;
        }

Also in your partial class add these two lines to your Page_Load method:
            GridView1.DataSource = GetProductsInCategory(5);
            GridView1.DataBind();

And that’s it…you should be able to right click on Default.aspx and click View In Browser and see your page with some data in it.Now…what’s it doing?
 

Database db = DatabaseFactory.CreateDatabase(); 
This line creates and opens a database connection to your default database via the defaultDatabase attribute of the <dataConfiguration> section of the web config.  If you added a second Connection String named say “Production” you could open that database using the following method: 
Database db = DatabaseFactory.CreateDatabase(“Production”); 

Next: 
string sqlCommand = "GetProductsByCategory";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand); 

The first part of this is simply creating a string variable and assigning it a value that is the name of our stored procedure.  The second part is creating a DbCommand object and assigning it some properties associated with our stored procedure. 

Next: 
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category); 

Remember that our stored procedure has a parameter named “CategoryID”, here we are assigning that value to our db object in preparation to execute the stored procedure. 

Next: 
DataSet productDataSet = db.ExecuteDataSet(dbCommand); 

Here were creating a DataSet and populating it with the ExecuteDataSet method provided by the Database Object. 

Finally: 
return productDataSet; 

And here we just return the newly created and populated dataset. At this point you should be able to view the page in your browser and see the resulting Gridview with products in it.  Five lines of code compared to a non Data Access Application Block method which could look like this: 
public DataSet GetProductsInCategory2(int Category)
        {
            SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=12345");
            con.Open();
            DataSet myDataSet = new DataSet();
            SqlCommand CommandObject = new SqlCommand();
            CommandObject.CommandType = CommandType.StoredProcedure;
            CommandObject.CommandText = "GetProductsByCategory";
            CommandObject.Parameters.AddWithValue("@CategoryID", Category);
            CommandObject.Connection = con;
            SqlDataAdapter myDataAdapter = new SqlDataAdapter(CommandObject);
            myDataAdapter.Fill(myDataSet, "ProductData");
            con.Close();
            return myDataSet;
    }

Which is 11 lines of code (not counting the return line), plus you have to keep reusing the SQL connection string which has a significant amount of code stink to it.  I know you could write a custom library to ease some of this, but the Data Access Application Block is already written…don’t reinvent the wheel. J  

Benefits

I hope the benefits of this are starting to become clear:

·         A standardized way of accessing your data in a manner that has been thoroughly tested. 

·         Built on Best Practices

·         Looks Good on Your Resume J

·         Don’t have to remember the connection strings

Going Further And Suggested Reading

If after going through this demo you feel Data Access Application Blocks can help you out please check out the following sites for more information:

www.pnpguidance.com – David Hayden’s repository for Patterns and Practices Goodness.

davidhayden.com/blog/dave/archive/2007/09/08/RepositoryFactoryScreencastDataAccessLayerCodeGenerationWalkthru.aspx – David Hayden shows how to build a DAL (Data Access Layer) using the Repository Factory.