Data Points

The Enterprise Library Data Access Application Block, Part 3

John Papa

Code download available at:DataPoints0510.exe(216 KB)

Contents

Saving Data via SQL
Stored Procedures and One-Liners
Wrap a Transaction
Save a Row via UpdateDataSet
Save Multiple Rows at Once
Test as You Go
Wrapping It Up

E nterprise applications can have a wide variety of data update requirements. Sometimes you need to save multiple rows of changes at once within a single transaction. Other times, the user must be allowed to enter multiple rows of data, send them to the database in a batch; and if a row or two fails, only the rows that succeeded should be committed and remain committed. Fortunately, the Enterprise Library Data Access Application Block (DAAB), which I covered in my past two columns, exposes several ways to commit data changes to a database provider using ADO.NET (see The Enterprise Library Data Access Application Block, Part 1 and The Enterprise Library Data Access Application Block, Part 2).

This month, I will demonstrate several ways the Enterprise Library DAAB can modify data, how you can implement transactions with it, and how to set up NUnit to test the data access code.

I will start by building upon and modifying the Windows® Forms project from the August issue so that all data retrieval and modification are performed within the same project. (All of the code samples for the project I will discuss in this column can be downloaded from the MSDN® Magazine Web site.)

Saving Data via SQL

In the August Data Points column, I explained how to set up a project to refer to and use the DAAB. So, you should be ready to start saving data. The DAAB exposes several methods to retrieve and save data and they all have different purposes (see Figure 1). This month I'll focus on ExecuteNonQuery and UpdateDataSet.

Figure 1 DAAB Get and Save Methods

My first example shows how to pass a SQL statement to the DAAB to execute and save data to the database. After creating an instance of the DAAB Database class, I create an instance of a DBCommandWrapper class by passing the SQL statement to the GetSqlStringCommandWrapper method of the Database object.

I use the AddInParameter method of the DBCommandWrapper to set the values of the parameters that I used in the UPDATE SQL statement. I could have used the AddParameter method as well, which allows a little more flexibility since you can specify the direction. However, in this case the AddInParameter was sufficient as it wraps the basic settings I wanted for the input parameters. Executing the command is as easy as invoking the ExecuteNonQuery method of the Database object and passing to it the instance of the DBCommandWrapper. Finally, I query the database to get the customer record so I can return it to the form so the user can see that the changes took effect. This code is quite simple to execute and properly uses parameters to help alleviate the risk of a SQL injection attack.

Stored Procedures and One-Liners

The customer data could be updated using a stored procedure just as easily. The code in Figure 2 could be modified slightly to accept a stored procedure name instead of the SQL statement. For example, by replacing the SQL statement with the name of the stored procedure and creating the DBCommandWrapper by invoking the GetStoredProcCommandWrapper method of the Database object, the code will now execute a stored procedure:

string proc = "prUpdateCustomer"; DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(proc);

Figure 2 Updating Data with ExecuteNonQuery

public string SaveCustomersViaSql(string customerID, string companyName, string city, string country) { string sql = "UPDATE Customers SET CompanyName = @companyName, " + "City = @city, Country = @country WHERE CustomerID = @customerID"; Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper cmd = db.GetSqlStringCommandWrapper(sql); cmd.AddInParameter("companyName", DbType.String, companyName); cmd.AddInParameter("city", DbType.String, city); cmd.AddInParameter("country", DbType.String, country); cmd.AddInParameter("customerID", DbType.String, customerID); db.ExecuteNonQuery(cmd); return GetCustomerViaOutputParameters(customerID); }

If you want to try these two techniques, you can run them through the Windows Forms project (shown in Figure 3) by executing list items 8 and 9. List item 10 in the Windows Forms also executes a stored procedure to update a customer. However, the difference with this technique is that it executes the stored procedure using a single line of code. Notice that the following method executes the stored procedure in the first line of code and then gets the modified row again to display to the user (which you could choose to omit):

public string SaveCustomersViaStoredProcedureWithoutDBCommandWrapper( string customerID, string companyName, string city, string country) { DatabaseFactory.CreateDatabase().ExecuteNonQuery( "prUpdateCustomer", customerID, companyName, city, country); return GetCustomerViaOutputParameters(customerID); }

This line of code creates an instance of the Database object and invokes its ExecuteNonQuery method. One of the overloaded signatures for the ExecuteNonQuery method accepts the name of a stored procedure and a parameter array of parameter values for the stored procedure. Of course, the list of parameter values must match the data types and the number of parameters that the stored procedure accepts. Otherwise, an exception will be raised.

Figure 3** The Project **

The DAAB handles parameters nicely in this situation. When a stored procedure is executed by name and its parameters are passed to the ExecuteNonQuery through the parameter array, the DAAB asks the database for the list of parameters and figures this out for you. In the past this sort of operation has been very costly since every call to a stored procedure meant a costly search and discovery of the parameters in addition to the call to execute the stored procedure. The DAAB alleviates some of this concern by introducing parameter caching so that subsequent calls use the cached parameter information instead of hitting up the database to discover the parameters again.

Wrap a Transaction

Most enterprise applications at one time or another need to use transactions to wrap multiple action queries in an atomic unit of work. The DAAB wraps the transactional features of ADO.NET and exposes them so they can be used with the DAAB.

When using transactions, it is best to keep them open for as short a period of time as possible, performing only the minimal, essential queries inside of the transaction's scope. Any code that can be run outside of the transaction should be. This helps to minimize the lifetime of the locks that are held open during the transaction.

Figure 4 shows a method that accepts a list of regions and a list of territories that it will insert into the database. I create the transaction by first getting the connection object from the Database object's GetConnection method. Once I open the connection, I create an instance of the transaction and begin the transaction by calling the BeginTransaction method of the connection object:

IDbTransaction transaction = connection.BeginTransaction();

Figure 4 Using Transactions

public void InsertRegionsAndTerritoriesInTransaction( ArrayList territories, ArrayList regions) { Database db = DatabaseFactory.CreateDatabase(); IDbConnection connection = db.GetConnection(); connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { string insertRegionProc = "prInsertRegion"; foreach(RegionEntity newRegion in regions) { DBCommandWrapper regionCmd = db.GetStoredProcCommandWrapper(insertRegionProc); regionCmd.AddInParameter("id", DbType.Int32, newRegion.ID); regionCmd.AddInParameter("region", DbType.String, newRegion.Description); db.ExecuteNonQuery(regionCmd, transaction); } string insertTerritoryProc = "prInsertTerritory"; foreach(TerritoryEntity newTerritory in territories) { DBCommandWrapper territoryCmd = db.GetStoredProcCommandWrapper(insertTerritoryProc); territoryCmd.AddInParameter("id", DbType.Int32, newTerritory.ID); territoryCmd.AddInParameter("territory", DbType.String, newTerritory.Description); territoryCmd.AddInParameter("regionID", DbType.Int32, newTerritory.RegionID); db.ExecuteNonQuery(territoryCmd, transaction); } transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { if (connection.State == ConnectionState.Open) connection.Close(); } }

Now that I have a valid, open transaction I enclose all of my commands in a try/catch block so I can handle a rollback in the event that something goes wrong. I loop through the ArrayList of region objects that were passed in to this method, using the ExecuteNonQuery method of the Database object to invoke a stored procedure for each. The one key difference here is that I use the overloaded signature for the ExecuteNonQuery method that accepts an IDbTransaction object. When a transaction is passed to this method, the Database object will enlist the DBCommandWrapper object's command in the transaction. In my example I have three regions in the regions list, so the stored procedure is executed three times, all of which are enlisted within a single overarching transaction.

I then loop through an ArrayList of territories to insert into the database. Following the same model that the region code did, I use the ExecuteNonQuery method to execute the stored procedure that inserts the territories within the same transaction. In my example I execute three territories in addition to the three regions. All six stored procedure calls are wrapped inside of the same transaction so they all fail or commit as a single unit of work.

Save a Row via UpdateDataSet

One of the more popular features of ADO.NET is that it allows you to modify a row in a DataSet and use the DataAdapter's Update method to send the changes to the database. The DataAdapter will iterate through the changed rows and determine which command to execute (InsertCommand, UpdateCommand, or DeleteCommand) based upon the rowstate of the row in the DataSet's DataTable. In the next example, I get a DataSet full of customers and I modify a single customer row by changing the CompanyName and the City. I then pass the DataSet to the method shown in Figure 5, which uses the DAAB to save the row to the database.

Figure 5 Save One Row of a DataSet

public void Save1CustomerViaUpdateDataSet(DataSet ds) { Database db = DatabaseFactory.CreateDatabase(); string updateProc = "prUpdateCustomer"; DBCommandWrapper updateCmd = db.GetStoredProcCommandWrapper(updateProc); updateCmd.AddInParameter("customerID", DbType.String, "CustomerID", DataRowVersion.Current); updateCmd.AddInParameter("companyName", DbType.String, "CompanyName", DataRowVersion.Current); updateCmd.AddInParameter("city", DbType.String, "City", DataRowVersion.Current); updateCmd.AddInParameter("country", DbType.String, "Country", DataRowVersion.Current); int rowsAffected = db.UpdateDataSet(ds, "Customers", null, updateCmd, null, UpdateBehavior.Standard); }

The code in the method in Figure 5 is quite familiar since I create the Database object, the DBCommandWrapper, and add the parameters. The difference is in how I execute the stored procedure to update the customer row. Instead of invoking the stored procedure through the ExecuteNonQuery method, I use the UpdateDataSet method. The UpdateDataSet method accepts as its first two arguments the DataSet instance and the name of the DataTable that should be examined and updated. The next three arguments represent three DBCommandWrapper objects that are to be used as the INSERT, UPDATE, and DELETE commands, respectively. In this case, I know I am only updating a single row and that it is indeed an UPDATE, so I only pass the updateCommand argument for the DBCommandWrapper and I pass null for the other two command arguments. (In the next example I will show how to use all three command arguments.)

Since there is only a single row updated in this example, the last argument is irrelevant. The UpdateBehavior enumeration allows you to indicate how the DAAB (and ADO.NET) should behave if more than one row is being modified and one of the rows fails. If a row fails and the behavior is Standard (as it is in Figure 5), then an exception will be thrown and the entire batch will fail. If the behavior is ContinueOnError and one of the rows fails, then that row is logged but the commands continue to execute the remaining modified rows to the database. The rows in error can be examined later and displayed to the user or logged if you so desire.

Save Multiple Rows at Once

In the example in Figure 5 I updated a single customer record using the UpdateDataSet method. When the DataSet and the UpdateDataSet method work together, they can update, insert, and delete several rows based on each row's rowstate. The UpdateDataSet method tells the DAAB to iterate through the changed rows in the DataSet's specified DataTable. For example, when it sees that a row has been modified, it executes the update command and when a row has been deleted, it executes the delete command.

The parameters are filled in using the values from the DataSet's DataTable's row values. (That's a mouthful!) So as the DataTable's changed rows are iterated through, each row is examined and the parameters' values are grabbed from the current row. Figure 6 demonstrates how this can be done. The full code (including the code that modifies the rows, adds new rows, deletes some rows in the DataSet, and then calls this method) is included in the code download on the MSDN Magazine Web site.

Figure 6 Saving Multiple Rows at Once via UpdateDataSet

public void SaveCustomersViaUpdateDataSet(DataSet ds) { Database db = DatabaseFactory.CreateDatabase(); string insertProc = "prInsertCustomer"; DBCommandWrapper insertCmd = db.GetStoredProcCommandWrapper(insertProc); insertCmd.AddInParameter("customerID", DbType.String, "CustomerID", DataRowVersion.Current); insertCmd.AddInParameter("companyName", DbType.String, "CompanyName", DataRowVersion.Current); insertCmd.AddInParameter("city", DbType.String, "City", DataRowVersion.Current); insertCmd.AddInParameter("country", DbType.String, "Country", DataRowVersion.Current); string updateProc = "prUpdateCustomer"; DBCommandWrapper updateCmd = db.GetStoredProcCommandWrapper(updateProc); updateCmd.AddInParameter("customerID", DbType.String, "CustomerID", DataRowVersion.Current); updateCmd.AddInParameter("companyName", DbType.String, "CompanyName", DataRowVersion.Current); updateCmd.AddInParameter("city", DbType.String, "City", DataRowVersion.Current); updateCmd.AddInParameter("country", DbType.String, "Country", DataRowVersion.Current); string deleteProc = "prDeleteCustomer"; DBCommandWrapper deleteCmd = db.GetStoredProcCommandWrapper(deleteProc); deleteCmd.AddInParameter("customerID", DbType.String, "CustomerID", DataRowVersion.Current); IDbConnection connection = db.GetConnection(); connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { int rowsAffected = db.UpdateDataSet(ds, "Customers", insertCmd, updateCmd, deleteCmd, transaction); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { if (connection.State == ConnectionState.Open) connection.Close(); } }

Test as You Go

Since the DAAB itself includes the unit tests that were used during the test-driven development (TDD) cycle when the DAAB was developed, I thought it would be interesting to show some examples of how to create your own unit tests. I created basic unit tests for each example method that is found in the included Windows Forms project. (For brevity I did not create extensive unit tests.) For my examples I use the NUnit unit-testing framework, which you can obtain from www.nunit.org.

If you look inside of the sample project in the download, you will see a subfolder named Tests. This folder contains the unit tests for the sample project. The unit tests are intended to verify that each method (or unit of work) succeeds under ideal situations and that each fails under expected failure circumstances. There are a few basic steps I used to create these unit tests.

1. I named my test class CustomerFixture so that it is easily identifiable, referenced the NUnit.Framework, and imported the relevant NUnit namespaces with using statements.
2. I placed the CustomerFixture class file inside of the Tests subfolder and surrounded the test class with a compilation directive so that they only included in specific builds:

#if UNIT_TESTS ... #endif

2. I added the compilation variable UNIT_TESTS to the project's debug configuration.

3. I decorated the unit test class with the [TestFixture] attribute (NUnit looks for these attributes) and each test method with the [Test] attribute.

4. I added the Category attribute to each of my test methods so I could easily organize my units tests. (I choose to create a category for all my Gets and Saves).

5. I used Assert statements in order to look for both valid and invalid conditions.

I find that it is a good practice to set the data back to its original state if you modify it during a test. This allows you to rerun the tests without having to manually change data. For a good look at unit testing data access layers, see Roy Osherove's article in the June 2005 issue of MSDN Magazine, available at Know Thy Code: Simplify Data Layer Unit Testing using Enterprise Services. If you want to see more examples of TDD beyond what I provided in the sample project included here, you can look at the Enterprise Library blocks.

Wrapping It Up

Over the past three installments of Data Points I have examined several aspects of the Enterprise Library DAAB including why it exists, its value, how to set up your project to use it, how to read and save data with it, and how to implement transactions. Now you're ready to use the Enterprise Library DAAB in data-centric apps.

Send your questions and comments for John to  mmdata@microsoft.com.

John Papa is a Senior .NET Consultant with ASPSOFT and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server, and can often be found speaking at industry conferences such as VSLive or blogging at codebetter.com/blogs/john.papa.