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.