Data Access Application Block Streamlines Your Data-Access Layer, Page 2

Executing a Stored Procedure with Output Parameters

To understand how to utilize the Data Access Application Block to execute a stored procedure that returns output parameters, consider a stored procedure that returns product details in the form of output parameters:

CREATE PROCEDURE dbo.GetProductDetails
(
       @ProductID int,
       @ProductName nvarchar(40) output,
       @UnitPrice money output
)
 
AS
       SET NOCOUNT ON
       Select @ProductName = ProductName, @UnitPrice = UnitPrice
       from Products Where ProductID = @ProductID

As you can see, the stored procedure returns the product name and unit price for a specific product as output parameters. The Click event of the btnRetrieveSingleRow button executes the GetProductDetails using the Data Access Application Block:

private void btnRetrieveSingleRow_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase("Northwind");
string procName = "GetProductDetails";
DBCommandWrapper dbc = db.GetStoredProcCommandWrapper(procName);
dbc.AddInParameter("@ProductID", DbType.Int32, 1);
dbc.AddOutParameter("@ProductName", DbType.String, 50);
dbc.AddOutParameter("@UnitPrice", DbType.Currency, 8);
db.ExecuteNonQuery(dbc);
lblResult.Text = "Product Name: " + 
dbc.GetParameterValue("@ProductName") + "
";
lblResult.Text += "Unit Price: " + dbc.GetParameterValue("@UnitPrice") 
+ "
";
}

As with the previous examples, the preceding code also specifies the input parameters using the AddInParameter() method of the DBCommandWrapper object. To specify the output parameters, you use the AddOutParameter() method. Once all the parameters are added, executing the stored procedure is very simple and straightforward. Just invoke the ExecuteNonQuery() method of the Database object. Finally, you retrieve the output parameter values returned by the stored procedure through the GetParameterValue() method of the DBCommandWrapper object. Figure 3 shows the output produced by the code.

Click here for a larger image.

Figure 3. Stored Procedure with Output Parameters

Retrieving a Single Scalar Value

The Data Access Application Block provides an ExecuteScalar() method to retrieve a scalar value from a SELECT query or stored procedure, but returns a value of type Object. You can simply cast this return type to the appropriate type you want. For this example, create a procedure named GetProductName that returns the name of the product based on the supplied product ID:

Create PROCEDURE dbo.GetProductName
(
    @ProductID int           
)
 
AS
       SET NOCOUNT ON
       Select ProductName from Products 
Where ProductID = @ProductID

The following is the code required to execute this stored procedure:

private void btnRetrieveSingleItem_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase("Northwind");
string productName = (string) db.ExecuteScalar("GetProductName", 1);
lblResult.Text = "Product Name: " +  productName;
}             

Once you have an instance of the Database class, you can then easily execute the stored procedure using the ExecuteScalar() method to pass in the name of the stored procedure and the parameter value to the stored procedure. The ExecuteScalar() method simply returns the product name as an object, which is then typecast into a string for display purposes.

Executing Code Within a Transaction

The Data Access Application Block, by itself, does not provide any functionalities for executing data-access code within the scope of transactions. However, you can use ADO.NET's transaction support to accomplish this. The Database object exposes a method named GetConnection() that allows you to reference the underlying connection object. Once you have reference to the connection object (an object that inherits from the IDbConnection interface), you can easily create a transaction object (that is, of type IDbTransaction) using the BeginTransaction() method of the connection object. After that, you can either commit or rollback the transaction depending on whether the code in the try..catch block is executed successfully or not:

private void TransactionExample()
{
Database db = DatabaseFactory.CreateDatabase();
using (IDbConnection connection = db.GetConnection())
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
int amount = 1000;
string srcAccount = "AccountOne";
string dstAccount = "AccountTwo";
db.ExecuteNonQuery(transaction, "CreditAccount", 
srcAccount, amount);
db.ExecuteNonQuery(transaction, "DebitAccount", 
dstAccount, amount );
transaction.Commit();
}
catch
{
transaction.Rollback();
}  
connection.Close();
}
}

The above code example executes two stored procedures named CreditAccount and DebitAccount within the scope of a single transaction. If both the procedures are executed successfully, the transaction is committed; otherwise, it is rolled back.

Freedom from Tedious Data-Access Details

The EntLib Data Access Application Block allows you to considerably speed your application development by encapsulating the common plumbing code required to execute stored procedures or SQL text commands, specify parameters, and return SqlDataReader, DataSet, XmlReader objects. Using the Data Access Application Block frees you from the tedious details of data access and allows you to concentrate on the business logic in your applications. This results in reduced errors, bugs, and typos in your application as well.

Download the Code

To download the accompanying source code for the examples, click here.

About the Author

Thiru Thangarathinam has many years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. His certifications include MCAD for .NET, MCSD, and MCP. Thiru is an expert in ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.