Click here to Skip to main content
12,512,130 members (60,071 online)
Click here to Skip to main content
Add your own
alternative version

Stats

937.5K views
9.4K downloads
316 bookmarked
Posted

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...

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionDo you haveany solution for me? Pin
Meysam Tolouee30-Jul-15 15:27
memberMeysam Tolouee30-Jul-15 15:27 
QuestionMauricio Please share code for Update relational data using DataSet and DataAdapter Pin
SHERAZ SHIEKH15-Dec-14 4:35
memberSHERAZ SHIEKH15-Dec-14 4:35 
QuestionTrying to write same code in vb.net but not working Pin
SHERAZ SHIEKH5-Dec-14 8:14
memberSHERAZ SHIEKH5-Dec-14 8:14 
QuestionStoring data into an sql or access database Pin
Member 407735121-Oct-09 9:14
memberMember 407735121-Oct-09 9:14 
GeneralThanks Mauricio! Pin
Dave Zeigler10-Jun-09 11:02
memberDave Zeigler10-Jun-09 11:02 
GeneralIn my application code does not work :( Pin
moscow-happy-man23-Feb-09 21:36
membermoscow-happy-man23-Feb-09 21:36 
QuestionSyntax error in INSERT INTO statement Pin
jaycons18-Nov-08 15:05
memberjaycons18-Nov-08 15:05 
QuestionNice ; How to do this Typed Dataset ? Pin
Member 40927537-Oct-08 21:00
memberMember 40927537-Oct-08 21:00 
AnswerRe: Nice ; How to do this Typed Dataset ? Pin
sergeyslepov26-Aug-09 7:01
membersergeyslepov26-Aug-09 7:01 
GeneralRe: Nice ; How to do this Typed Dataset ? Pin
Mathiyazhagan26-Aug-09 19:17
memberMathiyazhagan26-Aug-09 19:17 
Generaldeleting problem and built in queries Pin
kpakpo26-Feb-08 9:18
memberkpakpo26-Feb-08 9:18 
QuestionHow to get the auto generated key back to be displayed for user to see Pin
mbomas18-Sep-07 21:07
membermbomas18-Sep-07 21:07 
GeneralRe: How to get the auto generated key back to be displayed for user to see Pin
Tomic5-Feb-08 1:19
memberTomic5-Feb-08 1:19 
GeneralExecute application about database in c# 2005 Pin
reza7874-Aug-07 4:33
memberreza7874-Aug-07 4:33 
QuestionInserting relational data using DataSet and DataAdapter Pin
mkalyan198024-Jul-07 3:14
membermkalyan198024-Jul-07 3:14 
GeneralColumn 'ID' does not allow nulls Pin
Hector C13-Mar-07 16:32
memberHector C13-Mar-07 16:32 
GeneralRe: Column 'ID' does not allow nulls Pin
Hector C13-Mar-07 18:17
memberHector C13-Mar-07 18:17 
GeneralRe: Column 'ID' does not allow nulls Pin
kritbodin8-Jan-09 0:21
memberkritbodin8-Jan-09 0:21 
QuestionHow can we update and delete data in multiple tables using the same technique??????? plz help me Pin
Member #35635329-Jan-07 8:06
memberMember #35635329-Jan-07 8:06 
GeneralChild row is not getting updated - Please help Pin
compliedmonkey28-Nov-06 13:26
membercompliedmonkey28-Nov-06 13:26 
GeneralRe: Child row is not getting updated - Please help Pin
pavanmay225-Jan-07 0:19
memberpavanmay225-Jan-07 0:19 
GeneralI Love VB.net 2005 Pin
yaser9520-Jul-06 15:29
memberyaser9520-Jul-06 15:29 
Generalcreate a table in dataBase from a dataSet table Pin
cosmindp27-Jun-06 7:14
membercosmindp27-Jun-06 7:14 
Generalduplicated keys [modified] Pin
fan wei fang29-May-06 4:03
memberfan wei fang29-May-06 4:03 
GeneralRe: duplicated keys Pin
djtripoli12-Jun-06 10:38
memberdjtripoli12-Jun-06 10:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 16 Mar 2003
Article Copyright 2003 by Mauricio Ritter
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid