Get Started with the Enterprise Library Data Access Application Block

Microsoft has redesigned version 2.0 of the Data Access Block to take advantage of new ADO.NET 2.0 features. You can download the Enterprise Library Data Access Application Block from MSDN.

1] Using Application Data Block (Change in Web.Config)
 To use the Data Access Block successfully, you will need to go through the steps listed below:

Add a reference to the Microsoft.Practices.EnterpriseLibrary.Common.dll and Microsoft.Practices.EnterpriseLibrary.Data.dll assemblies from your solution. You can do this by using the “Add Reference” option and navigating to the <Drive Name>:\Program Files\Microsoft Enterprise Library\bin folder.
Add the necessary configuration entries to the web.config or app.config file or a custom configuration file. To this end, you add the below <configSections> element under the root <configuration> element.
   
         <configSections>
            <section   
              name=”dataConfiguration”            
              type=”Microsoft.Practices.          
              EnterpriseLibrary.Data.
              Configuration.
              DatabaseSettings, 
              Microsoft.Practices.
              EnterpriseLibrary.Data” />
          </configSections>

Then you also add the <dataConfiguration><configuration> element as shown below:

         <dataConfiguration         
            defaultDatabase=
            “AdventureWorksDB”/>

In this example, I have marked AdventureWorks as the default database, declared separately under the <connectionStrings> element.
          <connectionStrings>
            <add
             name=”AdventureWorksDB”
              providerName=
              “System.Data.SqlClient”  
              connectionString=
              “server=localhost;
              database=AdventureWorks;
              UID=user;PWD=word;” />
          </connectionStrings>

2]. Retriving value from the ExecuteDataReader

      Database db = DatabaseFactory.CreateDatabase();
       string sqlCommand = @”Select EmployeeID,
  NationalIDNumber, LoginID, Title from  Employee “;
       DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
       using (IDataReader reader = db.ExecuteReader(dbCommand))
       {
         //Bind with DataGrid
       }               
     }

Retriving single row

 Here is the store procedure
 Create Procedure GetEmployeeDetails
 (
      @EmployeeID int,
      @NationalIDNumber nvarchar(15)
        OUTPUT,
      @LoginID nvarchar(256) OUTPUT,
      @Title nvarchar(50) OUTPUT
 )
 AS
      Select @NationalIDNumber =
        NationalIDNumber,
        @LoginID = LoginID,
        @Title = Title from
        Employee
      Where EmployeeID = @EmployeeID
 GO

Here is the code for it.

Database db = DatabaseFactory.CreateDatabase();
string sqlCommand =”GetEmployeeDetails”;
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
//DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
//dbCommand.CommandType = CommandType.StoredProcedure;
db.AddInParameter(dbCommand, “EmployeeID”, DbType.Int32, 1);
db.AddOutParameter(dbCommand, “NationalIDNumber”, DbType.String, 15);
db.AddOutParameter(dbCommand, “LoginID”, DbType.String, 256);
db.AddOutParameter(dbCommand, “Title”, DbType.String, 50);
db.ExecuteNonQuery(dbCommand);
Response.Write(“NationalID : ” + db.GetParameterValue(dbCommand, “NationalIDNumber”) + “<br>”);
3]. Retriving DataSet/DataTable

       string   sqlCommand = @”Select EmployeeID,
          NationalIDNumber, LoginID, Title from Employee “;
       DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
       DataSet dst = db.ExecuteDataSet(dbCommand);

 //Bind with Datagrid

4]. Insert the records with Return Value

 CREATE PROCEDURE InsertRecordIntoCustomer
 (
    @CustomerID NCHAR(5),
    @CompanyName VARCHAR(50)
 )
 AS
 DECLARE @Result int
 IF EXISTS
 (
    SELECT
       NULL
   FROM
       [Customers]
   WHERE
       [CustomerID] LIKE @CustomerID
 )
    BEGIN
       SELECT @Result = -1
       END
 ELSE
    BEGIN
       INSERT INTO [Customers]
   (
   [CustomerID],
   [CompanyName]
   )
   VALUES
   (
   @CustomerID,
   @CompanyName
   )
       SELECT @Result = @@ERROR
       END
 RETURN @Result
 GO
The C# code used to execute the above stored procedure is as follows:
 try
 {
    // Create DataBase Instance
    Database db = DatabaseFactory.CreateDatabase();
    // Initialize the Stored Procedure
    DBCommand dbCommand = db.GetStoredProcCommand(“InsertRecordIntoCustomer”);
    // If There are output parameters, use ExecuteNonQuery only, better performance
    dbCommand.AddInParameter(“@CustomerID”, DbType.String, “JohnY”);
    dbCommand.AddInParameter(“@CompanyName”, DbType.String, “Microsoft”);
    object Internalvalue = new object();
    dbCommand.AddParameter(“@Result”, DbType.Int32, ParameterDirection.ReturnValue, “@Result”, DataRowVersion.Default,Internalvalue);
    // Get output
    int GetResult = 0;
    // Execute Stored Procedure
    db.ExecuteNonQuery(dbCommand);
    GetResult = (int)dbCommand.GetParameterValue(“@Result”);
    switch ( GetResult )
    {
       case 0:
           Response.Write(“Record Inserted.”);
           break;
       case -1:
           Response.Write(“Record Already Found.”);
           break;
       default:
          Response.Write(“Record Not Inserted.”);
          break;
    }
 }
 catch (Exception ex)
 {
    Response.Write(ex.ToString());
 }
5]. Example with Tansactions

   Database db = DatabaseFactory.CreateDatabase();
  
   //Two operations, one to add the order and another to add order details
   string sqlCommand = “InsertOrder”;
   DbCommand orderCommand = db.GetStoredProcCommand(sqlCommand);
   //Add InsertOrder parameters
  
   sqlCommand = “InsertOrderDetails”;
   DbCommand orderDetailsCommand = db.GetStoredProcCommand(sqlCommand);
   //Add InsertOrderDetails parameters

   using (DbConnection connection = db.CreateConnection())
   {
     connection.Open();
     DbTransaction transaction = connection.BeginTransaction();
     try
     {
       //Execute the InsertOrder
       db.ExecuteNonQuery(orderCommand, transaction);
       //Execute the InsertOrderDetails   
       db.ExecuteNonQuery( orderDetailsCommand, transaction);
       //Commit the transaction
       transaction.Commit();                   
     }
     catch
     {
       //Roll back the transaction.
       transaction.Rollback();
     }
   } 

6]. Retrieve a Single Field
 
 CREATE PROCEDURE [dbo].[GetCustomerSingleColumn]
 (
    @CustomerID NCHAR(5)
 )
 AS
 SET NOCOUNT ON
    SELECT
       [CompanyName]
    FROM
       [Customers]
    WHERE
       [CustomerID] LIKE @CustomerID
 GO
The C# code used to execute the above stored procedure is as follows:
 try
 {
    // Create DataBase Instance
    Database db = DatabaseFactory.CreateDatabase();
    // Initialize the Stored Procedure
    DBCommand dbCommand = db.GetStoredProcCommand(“GetCustomerSingleColumn”);
    dbCommand.AddInParameter(“@CustomerID”, DbType.String, “ALFKI”);
    //Execute the stored procedure
    string GetCompanyName = (string) db.ExecuteScalar(dbCommandWrapper);
    //Display results of the query
    string results = string.Format(“Company Name : {0}”,GetCompanyName);
    Response.Write(results);
 }
 catch (Exception ex)
 {
    Response.Write(ex.ToString());
 }

Search following text in Google you can find lots of articles about it 
” how to get the return value from store procedure in application data block dbcommand ”

Happy Programming !!!!