Enterprise Library: Data Access Application Block 5.0 in ASP.NET

The sample in this article shows how to use Data Access Application Block 5.0 with a real-world example in ASP.Net.

What is a Data Access Application Block (DAAB)?  A Data Access Application Block encapsulates the performance and resource management best practices for accessing Microsoft SQL Server databases. It can easily be used as a building block in your own .NET-based application. If you use it then you will reduce the amount of custom code you need to create, test, and maintain. It comes with a single assembly with a class that has many useful methods. It reduces the amount of custom code.

A Data Access Application Block provides the following benefits:

• It uses the functionality provided by ADO.NET 2.0 and with it, you can use ADO.NET functionality along with the application block's functionality.
• It reduces the need to write boilerplate code to perform standard tasks.
• It helps maintain consistent data access practices, both within an application and across the enterprise.
• It reduces difficulties in changing the database type.
• It relieves developers from learning different programming models for different types of databases.
• It reduces the amount of code that developers must write when they port applications to different types of databases. Read more in http://msdn.microsoft.com/en-us/library/cc309168.aspx.

Install Enterprise Library

Please follow this link to download the Enterprise Library:

http://www.microsoft.com/en-in/download/details.aspx?id=15104

Getting Started

Begin using the following procedure:

• Start Visual Studio
• Create a new website
• Provide the name and location of website
• Click "Next"

Now add a reference for the following two assemblies in the bin folder:

Microsoft.Practices.EnterpriseLibrary.Data.dll

Microsoft.Practices.EnterpriseLibrary.Common.dll

Now open web.config in edit mode and provide database settings.

Image 1.

As you can see, two things have been added in web.config.

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />

</configSections>

 

<dataConfiguration defaultDatabase="NorthWNDConnectionString" />

 

<connectionStrings>

<add name="NorthWNDConnectionString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|NORTHWND.MDF;User Instance=true"

providerName="System.Data.SqlClient" />

</connectionStrings>
 
The following are my database Stored Procedures:
 

CREATE PROCEDURE dbo.GetCustomers

         

AS

          /* SET NOCOUNT ON */

          SELECT top 10 * FROM CUSTOMERS

          RETURN

CREATE PROCEDURE CustOrdersDetail @OrderID int

AS

SELECT ProductName,

    UnitPrice=ROUND(Od.UnitPrice, 2),

    Quantity,

    Discount=CONVERT(int, Discount * 100),

    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)

FROM Products P, [Order Details] Od

WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
 
Now to work on the coding part.

Drag and drop a GridView to the page.
 
Add these namespaces to the code file.
 
The following code shows how to call a Stored Procedure that returns a DataSet.

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.Common;

using System.Data;

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

 

  //This code is used to call stored procedure without parameter and bind gridview

  Database objDataBase = DatabaseFactory.CreateDatabase();

  DataSet ds = new DataSet();

  try

  {

      ds = objDataBase.ExecuteDataSet("GetCustomers");

      GridView1.DataSource = ds;

      GridView1.DataBind();

   }

  catch (Exception ex)

 {

       throw ex;

 }
 
The following code shows how to call a Stored Procedure with a parameter that returns a DataTable.
 

DataTable dt = new DataTable();

  Database objDB = DatabaseFactory.CreateDatabase();

        DbCommand cmd;

        try

        {

            cmd = objDB.GetStoredProcCommand("CustOrdersDetail");

            objDB.AddInParameter(cmd, "@OrderID ", DbType.Int32, orderId);

            dt = objDB.ExecuteDataSet(cmd).Tables[0];

      GridView1.DataSource = ds;

      GridView1.DataBind();

 

        }

        catch (Exception ex)

        {

       throw ex;

        }
 
The following code shows a direct statement with a parameter that returns a DataTable.
 

Database db = DatabaseFactory.CreateDatabase();

DataTable dt = new DataTable();

        Int32 orderid = 10248;

        string query = "SELECT ProductName,UnitPrice=ROUND(Od.UnitPrice, 2),Quantity,Discount=CONVERT(int, Discount * 100),ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) FROM Products P, [Order Details] Od WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID";

        DbCommand command;

        command = db.GetSqlStringCommand(query);

        db.AddInParameter(command, "@OrderID", DbType.Int32, orderid);

        dt = db.ExecuteDataSet(command).Tables[0];

        GridView3.DataSource = dt;

        GridView3.DataBind();

Press F5 to run the application.

Image 2.

Image 3.