Click here to Skip to main content
Click here to Skip to main content

Inserting relational data using DataSet and DataAdapter

, 15 Mar 2003
Rate this:
Please Sign up or sign in to vote.
This article describes the process of inserting data in a DataSet and then submitting this changes to the database. It targets the issue when having IDENTITY/AutoNumber columns in the database.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Mauricio Ritter
Web Developer
Brazil Brazil
Mauricio Ritter lives in Brazil, in the city of Porto Alegre. He is working with software development for about 8 years, and most of his work was done at a bank, within a home and office banking system.
Mauricio also holds MCSD, MCSE, MCDBA, MCAD and MCT Microsoft certifications and work as a trainer/consultant in some MS CTEC in his city.
Mauricio also works in his own programming site, aimed to Brazilian Developers: http://www.dotnetmaniacs.com.br
 
In his spare time he studys korean language...

Comments and Discussions

 
QuestionStoring data into an sql or access database PinmemberMember 407735121-Oct-09 9:14 
GeneralThanks Mauricio! PinmemberDave Zeigler10-Jun-09 11:02 
GeneralIn my application code does not work :( Pinmembermoscow-happy-man23-Feb-09 21:36 
QuestionSyntax error in INSERT INTO statement Pinmemberjaycons18-Nov-08 15:05 
QuestionNice ; How to do this Typed Dataset ? PinmemberMember 40927537-Oct-08 21:00 
AnswerRe: Nice ; How to do this Typed Dataset ? Pinmembersergeyslepov26-Aug-09 7:01 
GeneralRe: Nice ; How to do this Typed Dataset ? PinmemberMathiyazhagan26-Aug-09 19:17 
Generaldeleting problem and built in queries Pinmemberkpakpo26-Feb-08 9:18 
QuestionHow to get the auto generated key back to be displayed for user to see Pinmembermbomas18-Sep-07 21:07 
GeneralRe: How to get the auto generated key back to be displayed for user to see PinmemberTomic5-Feb-08 1:19 
GeneralExecute application about database in c# 2005 Pinmemberreza7874-Aug-07 4:33 
QuestionInserting relational data using DataSet and DataAdapter Pinmembermkalyan198024-Jul-07 3:14 
GeneralColumn 'ID' does not allow nulls PinmemberHector C13-Mar-07 16:32 
GeneralRe: Column 'ID' does not allow nulls PinmemberHector C13-Mar-07 18:17 
GeneralRe: Column 'ID' does not allow nulls Pinmemberkritbodin8-Jan-09 0:21 
QuestionHow can we update and delete data in multiple tables using the same technique??????? plz help me PinmemberMember #35635329-Jan-07 8:06 
GeneralChild row is not getting updated - Please help Pinmembercompliedmonkey28-Nov-06 13:26 
GeneralRe: Child row is not getting updated - Please help Pinmemberpavanmay225-Jan-07 0:19 
GeneralI Love VB.net 2005 Pinmemberyaser9520-Jul-06 15:29 
Generalcreate a table in dataBase from a dataSet table Pinmembercosmindp27-Jun-06 7:14 
Generalduplicated keys [modified] Pinmemberfan wei fang29-May-06 4:03 
GeneralRe: duplicated keys Pinmemberdjtripoli12-Jun-06 10:38 
GeneralSelect from Dataset not from DataTable Pinmemberdjtripoli28-Apr-06 8:39 
Questioncan i use data set and dataadapters to insert records for the first time in database Pinmemberrs53725-Mar-06 10:22 
AnswerRe: can i use data set and dataadapters to insert records for the first time in database PinmemberJyotsna.Sonawane5-Mar-07 23:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 16 Mar 2003
Article Copyright 2003 by Mauricio Ritter
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid