Using the Enterprise Library Data Access Block for .NET 2.0 : Page 4

Executing a Stored Procedure to Retrieve a Single Row
Sometimes you need to retrieve a single row instead of a group of rows. Instead of returning this single row as a DataSet or a DataReader, you can have the stored procedure return the single row as a set of output parameters. To do this, you use the ExecuteNonQuery() method of the Database class. As part of the preparation of the DbCommand, you need to add the output parameters to the DbCommand object. Here's the stored procedure declaration.

   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 
       HumanResources.Employee
     Where EmployeeID = @EmployeeID
   GO

The following Page_Load code executes the GetEmployeeDetails stored procedure and returns the NationalIDNumber, LoginID, and Title column values as output parameters.

   <script runat="server">
     void Page_Load(object sender, EventArgs e)
     {
       int employeeID = 1;
       Database db = DatabaseFactory.CreateDatabase();
       string sqlCommand ="GetEmployeeDetails";
       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>");
       Response.Write("Login ID : " + 
         db.GetParameterValue(dbCommand, 
         "LoginID") + "<br>");
       Response.Write("Title : " + db.GetParameterValue(
         dbCommand, "Title") + "<br>");        
     }
   </script>
   

In the preceding code, you add the output parameters using the AddOutParameter() method of the DbCommand object. After executing the stored procedure, you simply retrieve the output parameter values using the GetParameterValue() method of the DbCommand object.

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

The majority of the work happens inside the using block. That block starts the transaction using the BeginTransaction() method and also commits or rolls back the transaction using either Commit() or Rollback().

Keep It Short
As you have seen from this article, the EntLib Data Access Block obviates the need to write the repetitive data access plumbing code by providing a set of highly reusable classes for performing operations against the database. Through the use of these classes, you can eliminate errors, bugs, and typos in your application and focus on the core business logic of the application thereby gain productivity.

Previous Page