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:

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:

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.