Using the Enterprise Library Data Access Block for .Net 2.0

Using the Enterprise Library Data Access Block for .NET 2.0

Read the Article

Summery :-

Writing database-access code is a repetitious and time-consuming task, but now that it's available as a reusable Enterprise Data Access Application Block, you'll never have to write such code again.

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 EntLib Data Access Block from MSDN.
Using the Data Access Block
To use the Data Access Block successfully, you will need to go through the steps listed below:

1. 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 January 2006\bin folder.
   
2. 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></SPAN< class=pf element directly under the root <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>
   

   
   
3. Import the core Microsoft.Practices.EnterpriseLibrary.Data namespace for the Data Access Block.
   
4. Start writing code against the classes in the preceding namespace.

Whenever you work with the Data Access Block, you'll have to deal with the Database class first. The Database class represents the database itself, and provides methods  that you can use to perform CRUD (Create, Read, Update and Delete) operations against the database.

Execute a Query Returning a DbDataReader

I'll start with a simple example. You'll execute a simple SQL statement against the AdventureWorks database and retrieve the results in the form of a DbDataReader object, which you'll then bind to a GridView control in an ASP.NET page.  contains the full page code. Here's the Page_Load script:

   <script runat="server">
     void Page_Load(object sender, EventArgs e)
     {
       Database db = DatabaseFactory.CreateDatabase();
       string sqlCommand = "Select " + "EmployeeID, 
         NationalIDNumber," + "LoginID, Title from " +
         "HumanResources.Employee ";
       DbCommand dbCommand = db.GetSqlStringCommand
         (sqlCommand);
       using (IDataReader reader = db.ExecuteReader(
         dbCommand))
       {
         gridEmployees.DataSource = reader;
         gridEmployees.DataBind();
       }                
     }
   </script>

Transactional Code Block for Performing
Multiple Updates

There are times where you may want to execute
multiple operations against a database,
but perform them all within the scope of a
single transaction.
The EntLib Data Access Block enables this scenario by
providing the Database.CreateConnection() method
that allow you to get a reference to an ADO.NET 2.0 
DbConnection object. Using the DbConnection object,
you can obtain a reference to a DbTransaction object
by calling the BeginTransaction() method,
assigning the return value to a DbTransaction
variable. Subsequently, you can then easily control
the transaction behavior by invoking either the
Commit() or Rollback() methods when the execution
succeeds or fails, respectively.
The following pseudo code shows how to execute
multiple stored procedures within the scope of a
single transaction.

   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();
     }
   }