Browse DevX
Sign up for e-mail newsletters from DevX


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

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
Thanks for your registration, follow us on our social networks to keep up-to-date