Data Points

Saving Parent-child Data in a Multitiered App Using ADO.NET

John Papa

Code download available at:DataPoints0405.exe(146 KB)


ASP.NET Parent-child Web Form
Marshaling Data Changes
The Transaction and Sequence
Wrapping It Up

In this month's column I will focus on some data management techniques commonly required in enterprise applications. These include saving parent-child data in a multitier application using ADO.NET, ADO.NET transactions, merge techniques, and a number of other ADO.NET features. I'll also discuss the best process for incorporating consistent and informative exception-handling techniques and debugging strategies into your multitier app.

One of the most robust features of the ADO.NET DataSet is that you can store multiple related rowsets using DataTable and DataRelation objects as well as the original values and changes to them. Through the DataSet, an application can have a parent-child relationship represented by a single data store. This allows the application to store changes locally to both the parent and the child rowsets in the DataSet. The DataSet's changes can be cached locally and then later saved to a database all at once. For example, through a front-end application, a user could make changes to data, including deletions, updates, the addition of new records, and the addition of detail records. The changes would be cached locally and then could either be canceled entirely or sent back to the database all in one transaction.

In the Data Points column in the July 2003 issue of MSDN® Magazine I focused on managing batch inserts from a DataSet, detailing how to maintain the integrity of the parent-child relationship and IDENTITY values. This month I'll demonstrate how to manage inserts, updates, and deletes from both parent and child tables all in one batch. Using the Orders and [Order Details] tables of the SQL Server™ Northwind database, I'll walk through a sample application that can manipulate several Order and Order Detail records, storing changes in a relational ADO.NET DataSet that contains DataTable objects. The DataSet will be stored locally and only its changes will be marshaled to a business services object. I will discuss how to enforce and relax constraints on the DataSet, the SqlDataAdapter's RowUpdated event handler, how to implement transactions via ADO.NET, and how to merge DataSets together. I will also discuss one of the most critical factors in saving all types of changes from a hierarchical DataSet: the sequence in which the action queries are executed against the database.

ASP.NET Parent-child Web Form

Figure 1 shows the application that I will be referring to throughout this column. It loads all orders for a given customer (the application assumes the customer is always called "AROUT") and allows four basic functions to be performed on the local DataSet containing Orders and their Order Details: viewing, adding, deleting, and updating. The application also allows the user to either save all the DataSet changes to the database or cancel them.

Figure 1 Load Orders

Figure 1** Load Orders **

The UI should be intuitive as there are links and buttons labeling the main functions. These links and buttons all hook into a series of events that are defined in the WebForm1.aspx file. The following code snippet shows the properties of the Orders DataGrid—specifically, the event handlers that will be fired when the listed events occur:

<asp:DataGrid id="grdOrder" Runat="server" ••• OnPageIndexChanged="grdOrder_OnPageIndexChanged" OnItemCommand="grdOrder_OnItemCommand" OnEditCommand="grdOrder_OnEditCommand" OnUpdateCommand="grdOrder_OnUpdateCommand" OnCancelCommand="grdOrder_OnCancelCommand" >

The OnItemCommand event fires when the user clicks the Delete or View link in any Order row. In the same manner, the OnEditCommand, OnUpdateCommand, and OnCancelCommand events fire when the user clicks the Edit, Save, or Cancel link, respectively. Both the grdOrder and grdOrderDetail grids include various events, which are linked to corresponding event handlers located in the codebehind file called WebForm1.aspx.

The grdOrder_OnUpdateCommand event handler, which fires when the user has finished, grabs the values in the editable controls for the current row. The code that follows shows how this event handler finds the controls using the FindControl method:

Label lblOrderID = (Label)e.Item.FindControl("lblOrderID"); int nOrderID = Convert.ToInt32(lblOrderID.Text); TextBox txtOrderDate = (TextBox)e.Item.FindControl("txtOrderDate"); DateTime dtOrderDate = Convert.ToDateTime(txtOrderDate.Text); TextBox txtCity = (TextBox)e.Item.FindControl("txtCity"); string sCity = txtCity.Text; TextBox txtCountry = (TextBox)e.Item.FindControl("txtCountry"); string sCountry = txtCountry.Text;

I used this method because the controls are dynamically created based upon which row is being edited at the time. The lblOrderID Label is located first and is cast to a Label control. I do this to retrieve the primary key of the Orders DataTable, which will be used to locate the current row in the DataTable. The remaining values are then retrieved in succession.

Once the values are retrieved and stored in local variables, the corresponding DataRow is located in the Orders DataTable by passing the primary key of the DataTable to the Find method. Then the edited values are set to the DataRow in the DataSet:

DataRow oRow = oDs.Tables["Orders"].Rows.Find(nOrderID); oRow["OrderDate"] = dtOrderDate; oRow["ShipCity"] = sCity; oRow["ShipCountry"] = sCountry;

Afterwards, the grids are bound once again to the DataSet and the applied changes are shown to the user in the form. After this code executes, the current value of the OrderDate DataColumn for this DataRow is set to the new value. This feature of the DataSet allows both an original and a current value to be stored in a DataSet. The DataSet can compare these values and retrieve all rows that have any columns with different original and current values using the DataSet's or DataTable's GetChanges method or the DataTable object's Select method. Either of these methods can be used to retrieve changed rows which can then be marshaled back to a business tier and then on to the database.

The following code is a snippet from the DeleteOrder section of the grdOrder_OnItemCommand event handler:

nOrderID = Convert.ToInt32(((Label)e.Item.FindControl("lblOrderID")).Text); DataRow oDelRow = oDs.Tables["Orders"].Rows.Find(nOrderID); oDelRow.Delete();

It similarly grabs the OrderID value for the current row (which is being deleted), locates the corresponding DataRow in the Orders DataTable, and deletes the row from the DataSet. The Delete method does not actually remove the row from the DataTable. When a row is deleted, the current values of the columns of the DataRow are wiped out, leaving only the original values.

When a row is added to the DataSet, the original values of the columns of the DataRow do not exist. The new values are all represented by the current values of the columns. The following code sample is a snippet of what is executed when the user adds an order to the DataSet:

//-- Create an Order table DataRow DataRow oRow = oDs.Tables["Orders"].NewRow(); //?- Set the values for the new row oRow["CustomerID"] = sCustomerID; oRow["OrderDate"] = dtOrderDate_New; oRow["ShipCity"] = sCity_New; oRow["ShipCountry"] = sCountry_New; //-- Add the new row to the Orders DataTable oDs.Tables["Orders"].Rows.Add(oRow);

The NewRow method creates an empty new row from the Orders DataTable. The values for the row are set and the new DataRow object is added to the Orders DataTable. Notice that the OrderID DataColumn's value is never set explicitly in this code sample. Because the Northwind Orders table's OrderID column has an IDENTITY constraint, it will automatically be assigned an incremental value. Since the application does not know what value to set due to concurrency issues and the disconnected nature of scalable applications, an alternative is available in ADO.NET. The DataColumn has a series of properties that, when initiated, work together to increment the column automatically. The following code is from the GetData method of the Order class:

oDa.Fill(oDs, "Orders"); //-- Set the auto incrementing aspects oDs.Tables["Orders"].Constraints.Add("PK_Orders", oDs.Tables["Orders"].Columns["OrderID"], true); oDs.Tables["Orders"].Columns["OrderID"].AutoIncrement = true; oDs.Tables["Orders"].Columns["OrderID"].AutoIncrementSeed = -1; oDs.Tables["Orders"].Columns["OrderID"].AutoIncrementStep = -1;

After the Orders DataTable is filled by a SqlDataAdapter, a primary key constraint is placed on the OrderID DataColumn. Keep in mind that this constraint is in ADO.NET, not on the actual table in the underlying database. After the primary key constraint is established, the AutoIncrement property is set to true for the OrderID DataColumn. The first new row that is added to the DataTable will have its value automatically set to -1. All subsequent rows added before either RejectChanges or AcceptChanges are called will increase by the AutoIncrementStep value. The technique shown starts the new OrderID values at -1 and increases them by -1. Thus new rows will have the values of -1, -2, -3, and so on. Initializing them in a negative range ensures that the values will not collide with actual OrderID values in the database. The application does not care what the OrderID's value is as long as it can be used to uniquely identify a pending row and all of its related order detail records. Thus, the OrderID values that are generated by the DataColumn for new pending rows will not be the same as for any existing row in the database.

There are other controls besides the DataGrids in this application, such as buttons and labels. The button controls have event handlers that are fired when the button controls are clicked. There is a button for saving all changes to the database and one for canceling all local changes and refreshing from the database. There is also a Label server control called lblErrorMessage that is used to display any error messages thrown by the application in either the presentation or the business tier. Solid exception-handling techniques can be invaluable in finding and fixing the causes of errors.

In this application I have incorporated an exception-handling technique that creates and throws a CustomException object around the application's tiers. (This technique and the CustomException object are explained in detail in my March 2004 column.)

If an exception occurs, it is trapped and a custom exception is created and thrown back up the call stack. Once it reaches the originating event handler, the exception is then logged for posterity and displayed on the Web page in the lblErrorMessage Label. The message and its call stack are displayed, so it is easy to identify and pinpoint where the exception occurred. Figure 2 shows an example of an exception that this code reported. It occurred when I clicked the "Reload from Database" button after I removed EXECUTE permissions from the SqlTestUser SQL Login on the prGet_Order stored procedure.

Figure 2 Execute Permission Error

Figure 2** Execute Permission Error **

In order to examine the form's codebehind and the business classes (that could comprise the middle tier), I will first make several changes to the Orders and Order Details rowsets via their respective ASP.NET DataGrid server controls. To truly test the application, a series of tests with a thorough quality assurance process would be necessary. Since I have limited space here, I will throw one test scenario at the application by entering a few inserts, deletes, and updates for Orders and Order Details. I will then save the entire batch to the database in a single transaction by clicking the Save All Changes button. Now let's walk through the code that executes after the Save All Changes button is clicked.

Marshaling Data Changes

Before saving changes to the DataSet, it is wise to make sure there actually are changes to save. Thus, as soon as the user tries to save the changes, the code checks the Boolean value of the DataSet's HasChanges method. Now I'm ready to send the changes back to the middle tier so I create an instance of the Order class and call its SaveData method. This method accepts the user ID, password, and a DataSet to save as parameters. While the application would work if the entire DataSet was sent to the business tier, there is no need to pass any more than is necessary. The DataSet exposes a GetChanges method which returns another DataSet containing the same DataTable and DataRelation schema as the original DataSet. Since only the rows that were added, edited, or deleted will be contained within the DataSet, this DataSet is much smaller. This is especially important if the DataSet contains DataTable objects that have hundreds of rows, only a handful of which were actually modified in previous transactions.

This does pose a minor problem that will have to be dealt with after the changes have been applied to the database. The problem is that now there are two DataSet objects with the same schema: the original DataSet and the delta DataSet, which contains all rows that have changes from their original values. Keep in mind that the added rows in the Orders DataTable have artificial OrderID values such as -1 and -2. After the new order rows have been saved to the database, the new values will be returned to the delta DataSet in the Order class. I will tackle the techniques of merging these DataSet objects and their data back together after I review the actual saving of the data in the business tier.

The Transaction and Sequence

The Order class's SaveData method accepts the delta DataSet and promptly establishes a connection to the database. Before the connection is opened, oDa_Handle_RowUpdated is added as an event handler for the SqlDataAdapter's RowUpdated event, which is invoked when a row is modified by the SqlDataAdapter. The event handler is critical to the success of this application as it is used to assist in the preparation and synchronization of the delta DataSet with its originating DataSet back in the WebForm1.aspx.cs codebehind. When an Order row is modified via the SqlDataAdapter, this event will fire and evaluate if the modification was an insert or a delete. If it was either of those, it will set the status of the event to UpdateStatus.SkipCurrentRow. This effectively prevents the AcceptChanges method from affecting this row. Thus the original value of the OrderID would still be the artificial ID of -1, for example, and the current value for the OrderID would be the next IDENTITY value that the database assigned (such as 17004). If this row is not skipped, then the values would both be set to the new value. Why skip the row? The row is left with different original and current values so that when I merge the delta DataSet with the originating DataSet, they will sync with each other properly, using the original values of the OrderID DataColumns. The OrderDetail class's version of this event is even more critical, as I will demonstrate in the following code:

private void oDa_Handle_RowUpdated(object o, SqlRowUpdatedEventArgs e) { if (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Delete) { e.Status = UpdateStatus.SkipCurrentRow; } }

Since many changes can be made to the database separately (updates, inserts, and deletes for both orders and order details) a transaction is necessary to make sure that either all changes are applied or that none are. In the SaveData method (the core of which is shown in Figure 3) the BeginTransaction method of the SqlConnection object is invoked. This begins a transaction on the existing SqlConnection object and on the underlying database. A SqlTransaction object is created and stored to a local variable. This oTrn variable can then be used to commit or roll back the transaction as needed. It can also be used to retrieve a reference to the SqlConnection that it was started on.

Figure 3 Order's SaveData Method

oDa.RowUpdated += new SqlRowUpdatedEventHandler(oDa_Handle_RowUpdated); //============================================================= //--Set up the Connection //============================================================= oCn = new SqlConnection(sConnString); //============================================================= //-- Open the Connection and create the Transaction //============================================================= oCn.Open(); oTrn = oCn.BeginTransaction(); //============================================================= //-- Set up the INSERT Command //============================================================= sProcName = "prInsert_Order"; oInsCmd = new SqlCommand(sProcName, oCn, oTrn); oInsCmd.CommandType = CommandType.StoredProcedure; oInsCmd.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, "CustomerID")); oInsCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oInsCmd.Parameters.Add(new SqlParameter("@nNewID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "OrderID", DataRowVersion.Default, null)); oInsCmd.UpdatedRowSource = UpdateRowSource.OutputParameters; oDa.InsertCommand = oInsCmd; //============================================================= //-- Set up the UPDATE Command //============================================================= sProcName = "prUpdate_Order"; oUpdCmd = new SqlCommand(sProcName, oCn, oTrn); oUpdCmd.CommandType = CommandType.StoredProcedure; oUpdCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oUpdCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oDa.UpdateCommand = oUpdCmd; //============================================================= //-- Set up the DELETE Command //============================================================= sProcName = "prDelete_Order"; oDelCmd = new SqlCommand(sProcName, oCn, oTrn); oDelCmd.CommandType = CommandType.StoredProcedure; oDelCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oDa.DeleteCommand = oDelCmd; //============================================================= //-- Update the Order record(s) //============================================================= oDa.Update(oDs.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent)); //============================================================= //-- Add the Order record(s) //============================================================= oDa.Update(oDs.Tables["Orders"].Select("", "", DataViewRowState.Added)); //============================================================= //-- Update, Insert and Delete the Order Detail records //============================================================= OrderDetail oOD = new OrderDetail(); oOD.SaveData(sUserID, sPassword, oTrn, oDs); //============================================================= //-- Delete the Order record(s) //============================================================= oDa.Update(oDs.Tables["Orders"].Select("", "", DataViewRowState.Deleted)); oTrn.Commit(); oCn.Close();

The SqlCommand objects are then created and their SqlParameter objects set appropriately for the insert, update, and delete types of action queries. The SqlCommand objects are created using the three-argument constructor that in this method accepts the name of stored procedure, the SqlConnection object to use, and the SqlTransaction object to enlist in its transaction scope. Notice that in the code to create the InsertCommand (called oInsCmd) the oInsCmd object's UpdatedRowSource property is set to UpdateRowSource.OutputParameters. In this application, this will be used to pass the new OrderID value back to the SqlCommand object from the database, which is how the OrderID DataColumn gets its original and current values to be different (in other words, -1 and 17004). This code (along with the code directly preceding it that creates the output parameter directly) is used to grab the new OrderID from the database by way of the stored procedure's output parameter. Then the value is taken and set to the OrderID column in the DataTable.

Now that the SqlConnection is opened, the SqlTransaction is initiated and the series of SqlCommand objects are established, the SaveData method begins to make the changes to the database. The sequence of the action queries is important since I'm dealing with a relational data structure of orders and their order detail records. For example, it would not be prudent to insert an [Order Details] record into the database without a corresponding and valid Orders table record. A good rule of thumb for sequencing your action queries via SqlCommand objects is as follows:

  1. Update parent rows
  2. Insert parent rows
  3. Update child rows
  4. Insert child rows
  5. Delete child rows
  6. Delete parent rows

I found that when inserting the parent rows prior to updating them, the DataViewRowState of the new rows was changed from Added to ModifiedCurrent. This happened because the OrderID value was changed by the insert SqlCommand (oInsCmd), thus changing the row's state to be ModifiedCurrent. So why is this important? I found that new rows being created were then being marked as modified, which caused them to be issued to the database a second time via the update SqlCommand (oUpdCmd). There are a couple of ways to avoid this, but the simplest that I found was to perform updates before the inserts.

The SqlDataAdapter pushes the changes to the database via the Update method. It can accept a variety of arguments through its polymorphic nature, but in this code it accepts an array of DataRow objects from the Select method of the Orders DataTable. The Select method is used here to retrieve all DataRow objects that match the type of SQL action query that will be issued. To issue updates to the database, the DataRows returned from the Select method will have a DataViewRowState of ModifedCurrent. The deletes and inserts will have a DataViewRowState of Deleted and Added, respectively. Once all of the changes are issued to the database, the SqlTransaction object's Commit method is called and all of the changes are committed to the database.


Before the transaction can be committed, any changes to the OrderDetail DataTable have to be sent to the database, too. After the parent rows were updated and inserted into the database, an instance of the OrderDetail class is created and its SaveData method is invoked. Figure 4 shows the core of the SaveData method of the OrderDetail class. Note that it turns off the enforcement of constraints on the DataSet. This effectively tells the DataSet that an OrderID in the parent and the child DataTable objects are allowed to be out of sync. This is turned off so that the oDa_Handle_RowUpdated event handler for the OrderDetail class can set the OrderID values appropriately, as I will demonstrate. Once the constraints are off, the series of updates, inserts, and deletes are issued in sequence. When any rows in the delta OrderDetails DataTable are sent to the database, the event handler is invoked (see Figure 5).

Figure 5 RowUpdated Event Handler

//-- See if the change is an insert if (e.StatementType == StatementType.Insert) { //--Prevent AcceptChanges from being called on this row e.Status = UpdateStatus.SkipCurrentRow; //--Get the current primary key value int nCurrentKey = (int)e.Row["OrderID"]; //--Get the pseudo key value from the parent and set it as the child // row's primary key e.Row["OrderID"] = e.Row.GetParentRow("OrdersToOrderDetails")["OrderID", DataRowVersion.Original]; //--Make the Order ID the pseudo ID (-1) for both Original and // Current value. e.Row.AcceptChanges(); //--Reset the primary key value in the foreign key column of the // child row //--Makes the current value the value in the database. e.Row["OrderID"] = nCurrentKey; }

Figure 4 OrderDetail's SaveData Method

//============================================================= //-- Turn constraints off because we may have to synchronize //-- parent and child rows in the update row handler. //============================================================= oDs.EnforceConstraints = false; //============================================================= //-- Update the Order Details record(s) //============================================================= oDa.Update(oDs.Tables["OrderDetails"].Select("", "", DataViewRowState.ModifiedCurrent)); //============================================================= //-- Add the Order Details record(s) //============================================================= oDa.Update(oDs.Tables["OrderDetails"].Select("", "", DataViewRowState.Added)); //============================================================= //-- Delete the Order Details record(s) //============================================================= oDa.Update(oDs.Tables["OrderDetails"].Select("", "", DataViewRowState.Deleted)); //============================================================= //-- Turn constraints back on //============================================================= oDs.EnforceConstraints = true;

The code in Figure 5 prevents the current row from having AcceptChanges called on it, thus keeping the DataRow's original and current values separate. If the row is not skipped, then the original value would be set to be the same as the Current value (if AcceptChanges was allowed to proceed). After the row is skipped, the current value of the OrderID is retrieved and stored to a local variable. This is the OrderID value that was created by the database. This OrderID value is then used to travel up to the parent DataTable (Orders) and retrieve the original value of the OrderID. For example, the nCurrentKey variable would be set to a value such as 17004 and then this would be used to find the DataRow in the parent DataTable that has its current value as 17004 as well.

Once the row is found using the GetParentRow method, the original value of the linked OrderID DataColumn in the Orders DataTable is retrieved. This value (for example, -1, -2, and so forth) is set to be the child DataTable's OrderID current value. The AcceptChanges method is then issued so that the current value is copied to the original value. Now the original and current values are both set to -1. Finally, the current value of the DataRow's OrderID column is set to the database's value (17004). Basically, all of the fuss in this event handler exists to toggle the original and current values of the OrderID DataColumn. This allows the constraints to be enforced again as the child records are now in sync with the relationship established between it and its parent DataTable.

Once the database changes are complete, the SqlTransaction is committed and the delta DataSet is returned to the WebForm1.aspx.cs codebehind event that called it originally. What is important to remember is that the delta DataSet contains the changed records including the new and original OrderID values.

The following code snippet shows the retrieval of the delta DataSet (oDsDelta) returned from SaveData. A generic function (Common.DisplayDataSet) is used to display the contents of both the original and delta DataSets before the merge to the output window when it is in debug mode:

DataSet oDsDelta = oOrder.SaveData(oDs.GetChanges()); oOrder = null; Common.DisplayDataSet(oDs, "*** BEFORE MERGE - ORIGINAL ***"); Common.DisplayDataSet(oDsDelta, "*** BEFORE MERGE - DELTA ***"); oDs.EnforceConstraints = false; oDs.Merge(oDsDelta.Tables["Orders"]); oDs.Merge(oDsDelta.Tables["OrderDetails"]); oDs.AcceptChanges(); oDs.EnforceConstraints = true;

This function is a crude technique but is valuable in locating problems as it is easy to see the contents of the entire delta DataSet and its originating DataSet to compare them.

The originating DataSet (oDs) then turns off its constraints so that any violations that may have occurred during the merge are completely ignored. Then the two DataSet objects are merged into the originating DataSet using the original values of the OrderID DataColumn objects in each respective DataSet.

Now that all of the data is merged into a single DataSet, the OrderID values should be set to the new values (such as 17004). Because of this, the AcceptChanges method is called on the oDs DataSet and the constraints are enforced once again. Finally, each of the ASP.NET DataGrid objects are bound again and the changes that were made to the records are displayed to the user.

Wrapping It Up

When you're using a DataSet that represents a parent-child relationship via two related DataTable objects, the child table's foreign key that references the parent table's primary key must stay in sync with its values to maintain the relationship. This is fairly straightforward to manage, but it gets a bit more involved when the parent table's primary key is an auto-incrementing column. When a row is added to the parent DataTable in the DataSet, a new ID value must be generated. Then, the value must be propagated to the child DataTable. Since all of this happens locally to the DataSet, when the changes are sent back to the database, a new ID value must be retrieved and synchronized with the DataSet's DataTable objects to maintain the DataSet's parent-child relationships. Also, assuming that only the changes in the DataSet are marshaled to the database via a middle tier, possibly in a physically separate server, there is the matter of synchronizing the original DataSet with the marshaled DataSet. ADO.NET makes it possible to implement this process across multiple classes, and even multiple tiers, by using events such as SqlDataAdapter RowUpdated.

In this month's column I focused on how to implement a parent-child series of data modifications through ASP.NET and ADO.NET, taking into account marshaling only modified data, instituting atomic transactions, retrieving IDENTITY values, and keeping the parent-child DataTables in sync. The business tier could be set up as a separate assembly containing Order and OrderDetail classes. For the sake of simplicity I have included all of the code in the same application, but in an enterprise application these classes could be split into their own projects, built into separate assemblies and referenced by the Web Form project. The sample application contains more exception handling and debugging code. You can download it and a readme instruction file for setting it up on the MSDN Magazine Web site.

Send your questions and comments for John to

John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and 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.