Inserting relational data using DataSet and DataAdapter

Download Code

Introduction

Since the introduction of ADO.NET, we started to use a different concept in the development of database-driven applications, the disconnected environment. Most of you are already aware of this change, but this new development model brings some problems that need to be solved, in order to make our application work in a fully disconnected environment.
One of the problems I've found when I was developing an application using this approach was the method of updating relational data using a DataSet and a DataAdapter objects. If you update just one DataTable there is no big deal in creating the code for the DataAdapter, but if you work on more than one table, and these tables have a parent-child relationship, the update/insert process can be a little tricky, specially if the parent table has an IDENTITY/AutoNumber column. In this article, I'll show some techniques to workaround this problem, specially if you work with AutoNumber/IDENTITY columns in your database.
In the example I'll use a database with 2 tables, one that holds Order and one that holds OrderDetails. The OrderDetails have a foreign key relationship with the Order table in the OrderId column, that is an IDENTITY/AutoNumber column.
The article is divided in two parts, one that shows an implementation using an Access 2000 database, and another that using a SQL Server Database with stored procedures. Hope you enjoy this!

Creating the structure (Access 2000)

The first thing you need to do in order to complete our example is to create some basic variables to hold our DataSet and two DataAdapter objects (one for the parent and one for the child table). We are going to use the System.Data.OleDb namespace, since we are working with Access 2000.

// Create the DataSet object

DataSet oDS = new DataSet();
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; 
                        Data Source=orders.mdb");
conn.Open();

// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter

OleDbDataAdapter oOrdersDataAdapter = new 
    OleDbDataAdapter(new OleDbCommand("SELECT * FROM Orders", conn));
OleDbCommandBuilder oOrdersCmdBuilder = new
    OleDbCommandBuilder(oOrdersDataAdapter);
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);

DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";

// Create the DataTable "OrderDetails" in the Dataset 

//and the OrderDetailsDataAdapter

OleDbDataAdapter oOrderDetailsDataAdapter = new
    OleDbDataAdapter(new OleDbCommand("SELECT * FROM OrderDetails", conn));
OleDbCommandBuilder oOrderDetailsCmdBuilder = new
    OleDbCommandBuilder(oOrderDetailsDataAdapter);
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);

pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";

In the previous code sample, we have created a connection to our database and two DataAdapter objects, one to update the parent table (Orders) and one to update the child table (OrderDetails). We have used the FillSchema method of the DataAdapter to create both DataTable objects in the DataSet, so that they have the same structure of the database tables (including the AutoNumber field).
We've also used the OleDbCommand builder to create the additional commands for both DataAdapter, so that we can submit our changes in the DataSets to the database later on.

Creating the relationship between the DataTables

Now we need to add a relationship between the two key columns of both tables. We'll do this by creating a new DataRelation object and attaching it to the Relations collection of our DataSet. The following code does exactly this.

// Create the relationship between the two tables

oDS.Relations.Add(new DataRelation("ParentChild",
    oDS.Tables["Orders"].Columns["OrderId"],
    oDS.Tables["OrderDetails"].Columns["OrderId"]));

In the above code, we used the DataSet (oDS) to get a reference to both key columns of the DataTable objects, and created a DataRelation object, adding it to the Relations collection of the DataSet.

Inserting Data

Now that we have everything set, we need to insert the data into to the DataSet, prior to update the data in the Access database. We'll insert a new row in the Order table and a new row in the OrderDetails table.

// Insert the Data

DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);

DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;

oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);

Notice that we have used the SetParentRow method to create the relationship between the two rows. This is the most important part when you want to update to tables that have a relationship and a AutoNumber column.

Updating the DataSet

Now that we have the data inserted into the DataSet, its time to update the database.

oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");

conn.Close();

Solving the AutoNumber column issue

If you check the data in the database you'll notice that the rows inserted in the OrderDetails table have the OrderId column set to zero, and the inserted OrderId in the Orders table is set to one. This occurs due to some issues with Access 2000 and the DataAdapter object. When the DataAdapter object updates the data in the first table (Order) it does not return the generated AutoNumber column value, so the DataTable Orders in the DataSet stays with the value zero within it. In order to correct the problem, we need to map the RowUpdate event to update this information.
To map the RowUpdate event we'll create an event handler, and get the value of the new auto-generated number to save in the DataSet. You'll new to add this line of code after the creation of the oOrdersDataAdapter object.

oOrdersDataAdapter.RowUpdated += new
     OleDbRowUpdatedEventHandler(OrdersDataAdapter_OnRowUpdate);

Now we need to create an EventHandler function to handle this RowUpdate event.

static void OrdersDataAdapter_OnRowUpdate(object sender,
                    OleDbRowUpdatedEventArgs  e)
{
    OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY"
                                         e.Command.Connection);

    e.Row["OrderId"] = oCmd.ExecuteScalar();
    e.Row.AcceptChanges();
}

For each update in the OrdersDataAdapter, we'll call a new SQL command that will get the newly inserted AutoNumber column value. We'll do this by using the SELECT @@IDENTITY command. This command works only in Access 2000/2002, not in the prior versions. After the value update in the DataSet, we need to call the AcceptChanges method of the Row, in order to maintain this row in an "updated" state, and not in a "changed" state.
If you try to execute the code again you'll see that now the row in the OrderDetails table contains the correct value in the column.
Now we'll see how to target this same issue in SQL Server 2000. The method that I presented for Access database can be as well applied to SQL Server, but if you're working with stored procedures, there are other ways to do this.

Creating the structure (SQL Server 2000)

We'll start by creating the same structure that we used for Access 2000, but instead of creating the DataAdapter commands using the CommandBuilder, we'll create them by code, since we're going to use a SQL Server stored procedure to update the data.

// Create the DataSet object

DataSet oDS = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=.;
        Initial Catalog=Orders;Integrated Security=SSPI");
conn.Open();

// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter

SqlDataAdapter oOrdersDataAdapter = new
    SqlDataAdapter(new SqlCommand("SELECT * FROM Orders", conn));

oOrdersDataAdapter.InsertCommand = new 
    SqlCommand("proc_InsertOrder", conn);
SqlCommand cmdInsert = oOrdersDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;

cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].Direction = ParameterDirection.Output;
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";

cmdInsert.Parameters.Add(new SqlParameter("@CustomerName", 
                             SqlDbType.VarChar,50,"CustomerName"));
                             
cmdInsert.Parameters.Add(new SqlParameter("@ShippingAddress",
                             SqlDbType.VarChar,50,"ShippingAddress"));

oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);

DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";

// Create the DataTable "OrderDetails" in the

// Dataset and the OrderDetailsDataAdapter


SqlDataAdapter oOrderDetailsDataAdapter = new
      SqlDataAdapter(new SqlCommand("SELECT * FROM OrderDetails", conn));

oOrderDetailsDataAdapter.InsertCommand = new 
      SqlCommand("proc_InsertOrderDetails", conn);
      
cmdInsert = oOrderDetailsDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductId", SqlDbType.Int));
cmdInsert.Parameters["@ProductId"].SourceColumn = "ProductId";
cmdInsert.Parameters.Add(new 
   SqlParameter("@ProductName", SqlDbType.VarChar,50,"ProductName"));
cmdInsert.Parameters.Add(new 
   SqlParameter("@UnitPrice", SqlDbType.Decimal));
cmdInsert.Parameters["@UnitPrice"].SourceColumn = "UnitPrice";
cmdInsert.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int ));
cmdInsert.Parameters["@Quantity"].SourceColumn = "Quantity";

oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);

pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";

// Create the relationship between the two tables

oDS.Relations.Add(new DataRelation("ParentChild",
    oDS.Tables["Orders"].Columns["OrderId"],
    oDS.Tables["OrderDetails"].Columns["OrderId"]));

In this piece of code we're manually creating a SqlCommand to do all the inserts in the database table through the DataAdapter. Each SqlCommand calls a stored procedure in the database that has the parameters structure equal to the table structure.
The most important thing here is the OrderId parameter of the first DataAdapter's command. This parameter has a different direction than the others. The parameter has an output direction and a source column mapped to the OrderId column of the DataTable. With this structure, after each execution, the stored procedure will return the value to this parameter, that will be copied to the OrderId source column. The OrderId parameter receives the @@IDENTITY inside the procedure, like the one below.

CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
 @CustomerName varchar(50),
 @ShippingAddress varchar(50)
)
 AS

INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)

SELECT @OrderId=@@IDENTITY

Inserting the Data

Now that we set the entire structure, its time to insert the data. The process is exactly the same as we have done with the Access database, using the SetParentRow method to maintain the relationship and guarantee that the IDENTITY column will be copied to the child table (OrderDetails).

// Insert the Data

DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);

DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;

oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);

oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");

conn.Close();

If you check the database you'll see that the OrderId column is updated with the correct IDENTITY column value.