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.

Enterprise-Library1.jpg

Image 1.

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

 

  1. <configSections>  
  2. <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" />  
  3. </configSections>  
  4. <dataConfiguration defaultDatabase="NorthWNDConnectionString" />  
  5. <connectionStrings>  
  6. <add name="NorthWNDConnectionString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|NORTHWND.MDF;User Instance=true"  
  7. providerName="System.Data.SqlClient" />  
  8. </connectionStrings>

The following are my database Stored Procedures:

  1. CREATE PROCEDURE dbo.GetCustomers  
  2. AS  
  3.           /* SET NOCOUNT ON */  
  4.           SELECT top 10 * FROM CUSTOMERS  
  5.           RETURN  
  6. CREATE PROCEDURE CustOrdersDetail @OrderID int  
  7. AS  
  8. SELECT ProductName,  
  9.     UnitPrice=ROUND(Od.UnitPrice, 2),  
  10.     Quantity,  
  11.     Discount=CONVERT(int, Discount * 100),  
  12.     ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)  
  13. FROM Products P, [Order Details] Od  
  14. 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.

 

  1. using Microsoft.Practices.EnterpriseLibrary.Data;  
  2. using Microsoft.Practices.EnterpriseLibrary.Common;  
  3. using System.Data;  
  4. using Microsoft.Practices.EnterpriseLibrary.Data.Sql;  
  5.   //This code is used to call stored procedure without parameter and bind gridview  
  6.   Database objDataBase = DatabaseFactory.CreateDatabase();  
  7.   DataSet ds = new DataSet();  
  8.   try  
  9.   {  
  10.       ds = objDataBase.ExecuteDataSet("GetCustomers");  
  11.       GridView1.DataSource = ds;  
  12.       GridView1.DataBind();  
  13.   }  
  14.   catch (Exception ex)  
  15.   {  
  16.        throw ex;  
  17.   }

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

  1. DataTable dt = new DataTable();  
  2. Database objDB = DatabaseFactory.CreateDatabase();  
  3. DbCommand cmd;  
  4. try  
  5. {  
  6.     cmd = objDB.GetStoredProcCommand("CustOrdersDetail");  
  7.     objDB.AddInParameter(cmd, "@OrderID ", DbType.Int32, orderId);  
  8.     dt = objDB.ExecuteDataSet(cmd).Tables[0];  
  9.     GridView1.DataSource = ds;  
  10.     GridView1.DataBind();  
  11. }  
  12. catch (Exception ex)  
  13. {  
  14.     throw ex;  
  15. } 

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

  1. Database db = DatabaseFactory.CreateDatabase();  
  2. DataTable dt = new DataTable();  
  3. Int32 orderid = 10248;  
  4. 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";  
  5. DbCommand command;  
  6. command = db.GetSqlStringCommand(query);  
  7. db.AddInParameter(command, "@OrderID", DbType.Int32, orderid);  
  8. dt = db.ExecuteDataSet(command).Tables[0];  
  9. GridView3.DataSource = dt;  
  10. GridView3.DataBind(); 

Press F5 to run the application.

Enterprise-Library2.jpg
Image 2.

Enterprise-Library3.jpg

Image 3.