Data Points

Managing Hierarchical Inserts in ASP.NET and ADO.NET

John Papa

Code download available at:DataPoints0307.exe(145 KB)

Contents

Sample Application
DataGrid Setup
AutoIncrement Columns
Retrieving AutoIDs and Cascading
Reintegrating Deltas
Wrap-up

Many online applications require an interface that allows the user to enter multiple data rows at once and then send them to the database in a single shot. For example, a clerk may need to enter several orders, each with multiple line items, into their purchasing system quickly while a customer is on the phone. The application should not save the orders and their line items to the database until they've all been entered and verified by repeating back the orders to the customer. Such an application requires both orders and line items to be added and persisted in some type of local cache until the user decides to save all of the new data to the database. Some common issues that must be dealt with include determining how to persist the data prior to committing it to the database, keeping up with record changes, and dealing with columns that have auto-incrementing values.

In this installment of Data Points, I'll demonstrate how to manage hierarchical inserts using the SQL Server™ Northwind database's Orders and Order Details tables. I'll walk through an application that lets the user add several Order and Order Detail records to a relational ADO.NET DataSet with DataTable objects representing both the Orders and Order Details in the database. Along the way, I will provide sample code techniques that store changes locally, marshal only changed values to a business services object, retrieve auto-incrementing column values, and initiate atomic transactions. Then I will proceed to explain how to reintegrate the new data back into the DataSet that is bound to the UI's DataGrid, so the user sees the latest changes that were entered, including the new auto-increment values.

This column will also demonstrate the tools and techniques that ASP.NET and ADO.NET use with the sample application. I will start by explaining how the two DataGrids are set up and how the user interface interacts with its event handlers and validation rules. Next, I will discuss how the order and order detail data are retrieved from the business objects and related to each other, and how vital constraints are implemented on the data structure. After adding rows to the persisted DataSets and submitting them, I will demonstrate how to save the data within a transaction and retrieve new key values. Finally, I will show how to render those changes to the user interface layer as well as how to reintegrate those changes with the client-side DataSet.

Sample Application

The sample application that I will be referring to (see Figure 1) loads all orders for a given customer. For simplicity's sake, the application assumes the customer is always AROUT, so all examples will be reviewing the AROUT-related records and details. (Of course, it would be a simple process of replacing the hardcoded customer ID of "AROUT" with a parameter that is passed in from a user-selectable list.)

Figure 1 Sample Order Application

Figure 1** Sample Order Application **

Before I jump into the technical aspects of the application, I'll describe its intended behavior and requirements. The sample application requires some basic functionality, including paging and a "View" hyperlink next to each order that drills down into the selected order's detail. The footer of each grid must also allow the user to enter a new order or order detail record and then add the record to the grid. The application should store all pending inserts of both orders and order details so they may be submitted as a group. There should also be a hyperlink to reload the data from the database, thus canceling any pending changes, and another hyperlink to save all pending changes to the database.

DataGrid Setup

So now that you know how the application must behave, let's take a look at some of the key aspects of its implementation. The application uses two ASP:DataGrid server controls to manage the interface of the order and order detail data. The Orders DataGrid, named grdOrder, contains some basic formatting settings to allow for paging and alternating row colors as well as a special footer row to allow the user to add an order to the grid. The columns in grdOrder are broken down into two categories: those that are read-only and those that can be inserted. Read-only columns, such as the column that represents the Customer ID field, are defined with the standard asp:BoundColumn element:

<asp:BoundColumn HeaderText="Customer ID" DataField="CustomerID" readonly="true" visible="true"/>

Columns that can be entered so that a new order can be inserted use the asp:TemplateColumn element. The definition for the column for the OrderDate field in the grdOrder DataGrid, shown in Figure 2, tells the DataGrid to display the order date in an asp:Label element in the grid and to display the order date in an asp:TextBox element in the footer. The footer will be used to enter any new order records from the user. To make sure that a valid date is entered, an ASP.NET validator control is included as well. The txtOrderData_Validator control (asp:CompareValidator) is used by this code to compare the order date value entered by the user with the Date datatype.

Figure 2 Listing Orders for the AROUT Customer

<asp:TemplateColumn HeaderText="Order Date" Visible="true"> <ItemTemplate> <asp:Label runat="server" ID="lblOrderDate" text=<%#DataBinder.Eval(Container.DataItem, "OrderDate", "{0:d}")%> ></asp:label> </ItemTemplate> <FooterTemplate> <asp:TextBox runat="server" ID="txtOrderDate" text=<%#DataBinder.Eval(Container.DataItem, "OrderDate", "{0:d}")%> Width="70"></asp:textbox> <asp:CompareValidator Runat="server" ID="txtOrderData_Validator" ControlToValidate="txtOrderDate" ErrorMessage="Please enter a valid date MM/DD/YYYY" Operator="DataTypeCheck" Type="Date" Display="Dynamic" BackColor="#ff0000" ForeColor="#ffffff"></asp:CompareValidator> </FooterTemplate> </asp:templatecolumn>

The application must allow the user to add the order to the DataGrid, so there is an asp:TemplateColumn defined for this. The column, of type asp:Button, has a CommandName of AddOrder, will only be displayed in the footer, and will invoke the handler defined in the grdOrder DataGrid's OnItemCommand attribute. In this case, the handler for the OnItemCommand of the grdOrder DataGrid is called grdOrder_OnItemCommand.

<asp:TemplateColumn HeaderText="" Visible="true"> <FooterTemplate> <asp:Button CommandName="AddOrder" Text="Add" ID="btnAddOrder" Runat="server" /> </FooterTemplate> </asp:TemplateColumn>

Another special column in grdOrder is the column that displays the View hyperlink for each order row. This column has a CommandName of ViewOrderDetail and is linked to the same OnItemCommand handler as grdOrder.

<asp:ButtonColumn ButtonType="LinkButton" HeaderText="" CommandName="ViewOrderDetail" Text="View"/>

Figure 3 shows the code snippet in the grdOrder_OnItemCommand that executes when the View link is clicked.

Figure 3 Invoking grdOrder_OnItemCommand Handler

public void grdOrder_OnItemCommand(object sender, DataGridCommandEventArgs e) { int nOrderID; DataSet oDs = null; switch (e.CommandName) { //=========================================================== //-- ViewOrderDetail command //=========================================================== case "ViewOrderDetail": //-- Get the current OrderID nOrderID = Convert.ToInt32(((Label)e.Item.FindControl ("lblOrderID")).Text); //-- Bind the Order Details DataGrid BindOrderDetails(nOrderID); //-- Store the current Order ID ViewState["nLastOrderID"] = nOrderID; break;

The grdOrder_OnItemCommand handler catches all ItemCommand events for the DataGrid. A DataGridCommandEventArgs argument is passed to the handler within the e.CommandName parameter, making it possible to determine what caused the handler to execute. Thus, a simple switch statement makes handling ItemCommand events straightforward. Figure 3 shows that the ViewOrderDetail CommandName is evaluated, the current Order ID is retrieved, and the private method BindOrderDetails is called. BindOrderDetails (shown in Figure 4) retrieves a cached DataSet from the Session and filters it to show only the selected OrderID by creating a DataView object. In this example, the DataView accepts the table, an expression to filter the rows, a sort expression (in this case the rows will be sorted by ProductName), and the state of the data to view. Linking these two DataGrid controls together is important so that only the details for the currently selected order are displayed. The sample application accomplishes this by using the DataView to filter the appropriate rows. In Figure 4, the state of the data that will be used in the view is CurrentRows. This tells the DataView to get the current values (as opposed to the original values) which will be used for display in the grdOrderDetail object.

Figure 4 Displaying Order Details for Selected Order

private void BindOrderDetails(int nOrderID) { //-- Get the DataSet DataSet oDs = (DataSet)Session["oDs"]; //-- Create a sorted and filtered DataView DataView oDv = new DataView(oDs.Tables["OrderDetails"], "OrderID = " + nOrderID.ToString(), "ProductName", DataViewRowState.CurrentRows); //-- Bind the DataView to the OrderDetail DataGrid grdOrderDetail.DataSource = oDv; grdOrderDetail.DataBind(); }

The grdOrderDetail DataGrid works similarly to the grdOrder DataGrid with a few minor exceptions. One difference is that the grdOrderDetail DataGrid uses an asp:DropDownList control to give the user a list from which to add a product to the order. Also, the grdOrderDetail DataGrid is only displayed and bound to order detail information when the user clicks on the View hyperlink in the orders grid. However, the grdOrderDetail DataGrid contains a footer for adding a row to the order details and is tied to a handler to catch its ItemCommand events.

When a new order is entered, the grdOrder_OnItemCommand handler fires and creates the new order within the orders DataTable (shown in Figure 5). The values for the new order are retrieved from the current row via its FindControl method. Then, a new DataRow object is created from the orders' DataTable using the NewRow method. The NewRow method creates a new row by using the schema from the DataTable from whence it was invoked. Therefore, the new DataRow object is an empty orders row containing all of the columns of an order.

Figure 5 Adding an Order to the Orders DataTable

//=========================================================== //-- AddOrder command //=========================================================== case "AddOrder": try { //-- Get the DataSet out of the Session oDs = (DataSet)Session["oDs"]; } catch(Exception ex) { throw(new Exception("Ugh, no DataSet in the Session", ex)); } //-- Get the values for the new Order row from the footer row's //-- controls string sCustomerID = lblCustomerID.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; //-- Create an Order table DataRow DataRow oRow = oDs.Tables["Orders"].NewRow(); //-- Set the values for the new row oRow["CustomerID"] = sCustomerID; oRow["OrderDate"] = dtOrderDate; oRow["ShipCity"] = sCity; oRow["ShipCountry"] = sCountry; //-- Add the new row to the Orders DataTable oDs.Tables["Orders"].Rows.Add(oRow); //-- Store the DataSet Session["oDs"] = oDs; nOrderID = (int)oRow["OrderID"]; //-- Store the current Order ID ViewState["nLastOrderID"] = nOrderID; oRow = null; //-- Bind the DataSet to the Orders DataGrid BindOrders(); //-- Clear the Order Details DataGrid grdOrderDetail.DataSource = null; grdOrderDetail.DataBind(); break;

The next step is to fill the new order row and add it into the orders DataTable. At first you'll probably think that when you create the NewRow method of the DataTable that it is also adding the row to the DataTable, but it doesn't. The NewRow method creates a detached row, so it can be filled properly and then added to the DataTable by passing it to the Rows collection's Add method. By detaching the row, you avoid errors that might have occurred if an empty row was added to the DataTable. (For example, errors that might occur if the DataTable had constraints on its DataColumn objects that made them not allow null values.)

AutoIncrement Columns

When an order row is added to the grdOrder DataGrid (shown in Figure 5), an Order ID is generated automatically. Since the DataTable is disconnected, it has to be told that the OrderID DataColumn needs to automatically increment its integer value when a new row is created. The following code snippet from the GetData method of the Orders class in the business service layer of the app assigns the primary key constraint to the OrderID DataColumn and tells it that it is an automatically incrementing field:

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;

The AutoIncrementSeed property is set to start at -1 with a step of -1 for each value. Thus, the first new row will get an OrderID of -1, the next will get -2, and so on. Why start at -1 and go south? Because the underlying OrderID field in the database is defined as an IDENTITY column and since there is no lock held on the table by the application, I cannot reliably determine what value the OrderID should receive. For example, let's assume that the last order that was created in the database had an OrderID of 10000 and the OrderID of the new row is set to 10001. The changes are then saved to the database and the OrderID is set to 10005. The DataTable had it set to 10001 and the database ended up creating 10005 because in the short time that the database was checked for the highest OrderID, several new orders were saved and created in the database. The bottom line is that the application does not care what the OrderID's value is as long as while a row is pending it can be used to uniquely identify that row and all of its related records.

Thus, by setting the initial value of the AutoIncrementSeed to -1 and increasing it by -1 (or decreasing by 1), the OrderID values that are generated by the DataColumn for new pending rows will not be the same as any existing row in the database. Because the OrderID DataColumn's values are not saved to the database, the value is irrelevant as long as it is unique and not interfering with existing OrderIDs.

Retrieving AutoIDs and Cascading

The key ingredient in building hierarchical applications is to create related DataTable objects within a DataSet. This sample application relies on the relationship between the orders and their order detail records for displaying the values and for updating the values to the database. To establish this symbiosis, a DataRelation object is created and set up to link the orders DataTable and the order details DataTable. If a relation is created on a field that is already a foreign key to the same parent table, the relation will use the existing foreign key; otherwise, it will create a new foreign key. By setting the UpdateRule of the foreign key to Rule.Cascade, when the OrderID's value in the parent table is modified, the OrderID's value in the child table will reflect the new value. Why would you change the OrderID's value in the orders DataTable? Well, you wouldn't, at least not directly. To explain why this is important, I will continue with the previous scenario of adding the new order row and order details rows and having their OrderID generated and set to -1. Once the pending new rows are saved to the database, the IDENTITY constraint on the orders table's OrderID column will generate a value, which will not be -1. Assuming that the new value was 11110, it would be necessary to get this value and put it back into the DataTable to replace the -1 value and have it reflected in the user interface's DataGrids. Otherwise it won't be possible to insert any more child order detail rows for this parent.

To accomplish this retrieval of the OrderID value into the DataSet's DataTable object, it is important to examine how the data is being saved in the stored procedure (see Figure 6). After the stored procedure prInsert_Order has inserted the new row, it retrieves the new ID value into the output parameter @nNewID. This parameter is then read and mapped to the DataSet by the SqlCommand object automatically, when the UpdatedRowSource property is set to OutputParameters, as shown here:

oInsCmd.Parameters.Add(new SqlParameter("@nNewID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "OrderID", DataRowVersion.Default, null)); oInsCmd.UpdatedRowSource = UpdateRowSource.OutputParameters;

Figure 6 prInsert_Order Stored Procedure

CREATE PROCEDURE prInsert_Order @sCustomerID CHAR(5), @dtOrderDate DATETIME, @sShipCity VARCHAR(15), @sShipCountry VARCHAR(15), @nNewID INT OUTPUT AS SET NOCOUNT ON INSERT INTO Orders (CustomerID, OrderDate, ShipCity, ShipCountry) VALUES (@sCustomerID, @dtOrderDate, @sShipCity, @sShipCountry) SELECT @nNewID = SCOPE_IDENTITY GO

The new value for the OrderID, let's say 11110, replaces the -1 value in the orders DataTable's OrderID DataColumn. And because the foreign key was set to cascade on changes (shown in Figure 7), the value 11110 replaces the -1 value in the order details DataTable's OrderID DataColumn as well. The UpdateRowSource property and the output parameter from the stored procedure retrieve the changed value and store it in the root DataTable. In addition, the foreign key constraint's cascading UpdateRule setting makes sure that OrderID's value stays the same throughout the DataSet's appropriate DataRelation.

Figure 7 Creating the Foreign Key and Relation

//============================================================ //-- Create the Foreign Key //============================================================ ForeignKeyConstraint oFK = new ForeignKeyConstraint("FK_OrderDetails_Orders_OrderID", oDs.Tables["Orders"].Columns["OrderID"], oDs.Tables["OrderDetails"].Columns["OrderID"]); oFK.UpdateRule = Rule.Cascade; //-- The default setting oDs.Tables["OrderDetails"].Constraints.Add(oFK); //============================================================ //-- Create the relation between the Orders and Order Details //============================================================ oDs.Relations.Add("OrdersToOrderDetails", oDs.Tables["Orders"].Columns["OrderID"], oDs.Tables["OrderDetails"].Columns["OrderID"]);

Another possible way to get the changed OrderID value and put it back into the DataSet is to use the SqlDataAdapter's RowUpdated event handler which fires when a row is updated. Thus it could be used in order to immediately grab the new IDENTITY value and store it in the DataSet.

An example of the handler is shown in Figure 8. It retrieves the new OrderID by hitting the database again and setting the value to the DataTable. While this technique works for most cases, using UpdatedRowSource is simpler and more reliable.

Figure 8 RowUpdated Handler Setting New OrderID

private void m_oDa_Handle_RowUpdated(object sender, SqlRowUpdatedEventArgs e) { if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert)) { e.Row["OrderID"].GetType() = GetIdentity(); e.Row.AcceptChanges(); } } private int GetIdentity() { SqlCommand oCmd = new SqlCommand("SELECT @@IDENTITY", this.m_oCn, this.m_oTrn); object x = oCmd.ExecuteScalar(); return (int)x; }

Reintegrating Deltas

When the user submits the changes to be saved, it makes sense to send only the pending inserts to the business layer to be saved to the database. After all, why send more data across the network than is necessary? There could be 10 order rows and 100 order detail rows that were not changed, while only 1 order and 1 order detail were added. Thus, instead of sending all of the data down to the business layer, the GetChanges method of the DataSet retrieves and sends only the pending changes. A second DataSet (oDsDelta) is created containing the newly inserted data from the original DataSet's GetChanges method. The delta DataSet is passed to the business layer and is used to update the database.

Once the pending inserts have been saved to the database and the OrderID values have been propagated throughout the DataSet appropriately, the OrderID values in the original DataSet also need to be updated. However, the delta DataSet has been updated with the new OrderID values in the business layer (in the Order class) and the original DataSet is back in the calling Web Form's ASP.NET code. So to reintegrate the changes, the delta DataSet must first be returned from the Order class's SaveData method so that both DataSet objects are at the same level.

//-- Submit modified rows for all DataTables to the database and //-- retrieve the changes DataSet oDsDelta = oOrder.AddData("sa", "", oDs.GetChanges(System.Data.DataRowState.Added));

The goal is to get the new values back into the original DataSet. One way to accomplish this is to get the OrderID value from the delta DataSet for the new order, find the corresponding row in the original DataSet, and set its OrderID value. If there are several new order rows to synchronize, you can use a loop to set all of the values. However, if there are several fields, not just the OrderID field, and they need to be reintegrated (timestamp fields are one example), another technique can be implemented. The DataSet has a Merge method which does the job quite nicely. The Merge method accepts the delta DataSet as its argument and looks for matching rows in the original and the delta DataSet. When a matching row is found, the differing values in the delta DataSet's rows are overwritten into the original DataSet.

The only catch with using the Merge method in this sample application is that a match is not found since the primary key values differ. Therefore, the delta DataSet's rows would be added to the original DataSet. The original DataSet would end up with two versions of the order as well as duplicates for each of the new order detail rows. To avoid this, you must prevent AcceptChanges from being called on the delta DataSet's rows before calling Merge. By preventing AcceptChanges from being called, the inserted rows will have the old identity value as their original value, and the value from the database as the current value. When the merge is done, the DataSet will match rows based on the original value, and update the rows with the new value.

In order to prevent AcceptChanges from being called, you register a RowUpdated handler on the DataAdapter that sets the row's status to SkipCurrentRow. This prevents further processing (specifically, calling of AcceptChanges) for the newly inserted row, as shown in Figure 9.

Figure 9 Registering a RowUpdated Handler

//-- Register this event handler on the DataAdapter // prior to calling Update() as follows: // oDa.RowUpdated += // new SqlRowUpdatedEventHandler(m_oDa_Handle_RowUpdated); private void m_oDa_Handle_RowUpdated(object o, SqlRowUpdatedEventArgs e) { //-- See if the change is an insert if (e.StatementType == StatementType.Insert) { //-- Prevent AcceptChanges from being called on this row e.Status = UpdateStatus.SkipCurrentRow; } }

Wrap-up

While there is some code that has to be written to achieve the desired functionality using ADO.NET in a Web environment, developing this functionality with classic ADO would be much more daunting. ADO.NET has certainly brought with it a more involved model for developers. However, with this also comes a more in-depth set of features that helps resolve common architectural issues that developers face.

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

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. You can reach him at mmdata@microsoft.com.