Tip of the Day: Maintain Order

Last week I was finishing up an upgrade for an ASP.NET app to 3.5, while testing I came across a problem loading data correctly into a strongly type dataset. I notice while debugging the application, that one of the DataTable object was empty, meaning no data and another DataTable object containing the wrong data. What happen? Let's find out. In this post I will provide a sample application that will help you understand the scenario that I was facing.

First I have a stored procedure called dbo.CustomerData which has three select statements. The first select statement returns all Northwind employees that have worked with a customer, the second returns the customer name and primary contract full name and the third returns all orders made by the customer.

Next I have a strongly type dataset called CustomerData which has three DataTable objects:

• Customer - this contains columns for customer name and primary contact.
• CustomerSalesRep - this contains columns for last name, first name and title.
• CustomerOrderHistory - this contains columns for customer name, order date and order total.

I'm using the LoadDataset method to populate CustomerData to be used with my sample application. Below is actual implementation:

As you can see the LoadDataSet method accepts three parameters, a DbCommand object, a Dataset object and a string array. I want to point out that the string array is a collection of DataTable object names that comes from the CustomerData dataset. When the LoadDataSet method is called, each DataTable object will be populate based on the order they exist in the string array, so in this case the order is Customer, CustomerSalesRep and CustomerOrderHistory. Now we're ready to run the application.

When I ran the application the customer name and primary contact information was missing. What happen? The application ran without any exceptions, so I know that the dataset was loaded properly. What could of happen? Next I debugged the application and I had set a breakpoint right above the line where the code will display the customer name and what I found was that the Customer DataTable object was empty. Also the CustomerSalesRep had exceptions, but the CustomerOrderHistory contained valid data. Below is a 'Watch' window view which shows the details for the CustomerData DataSet object.

That's strange....only the CustomerOrderHistory DataTable object contains valid data. My next thought was to run the dbo.GetCustomerData stored procedure to review the result sets.

Ahh yes, the problem was with the order in which the return data came in. Notice how the customer sales rep came before the company data. The LoadDataSet is not smart of enough, and it shouldn't be, to identify DataTable objects with its corresponding result sets. The LoadDataSet just populates the dataset DataTable objects one by one based on the order in the string array, so in our case the customer sales rep result set was loaded with the Customer DataTable object, then the customer result set was loaded with the CustomerSalesRep DataTable object and then finally the customer order history result set was loaded with the CustomerOrderHistory DataTable object.

To fix the problem all we have to do is switch the customer select statement with the customer sales rep select statement:

As shown below, the customer result set is first, customer sales rep is second and then customer order history is third.

As you can see if we re-run the application again all DataTable objects are populate with the correct data.

The lesson to take from here is to make sure you maintain order with both your sql result sets and the string array being passed into the LoadDataSet method. Just as a side note, remember seeing a 'System.Data.StrongTypingException' for each column in the CustomerSalesRep DataTable object? The exception is thrown whenever a column is null. The DataTable object has a property called NullValue, which determines how to handle column values that return a null value. By default, the option is set to throw an exception. I guess I never changed the option when creating this DataTable object.

I hope you learned something. Enjoy!!!

Attached is a sample project that includes full sample code that I've used in this post. This project uses the Northwind database and uses the Enterprise Library 3.1. You can download the Enterprise Library 3.1 from here and you can download the Northwind database from here .