Click here to Skip to main content
15,920,110 members
Articles / Programming Languages / C#

Inserting Relational Data using DataSet and DataAdapter

Rate me:
Please Sign up or sign in to vote.
4.68/5 (137 votes)
15 Mar 20036 min read 1.2M   9.9K   321   157
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.

C#
// 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.

C#
// 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.

C#
// 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, it's time to update the database.

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

C#
oOrdersDataAdapter.RowUpdated += new
     OleDbRowUpdatedEventHandler(OrdersDataAdapter_OnRowUpdate);

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

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

C#
// 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:

SQL
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, it's 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).

C#
// 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.

History

  • 16th March, 2003: Initial version

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.


Written By
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

 
GeneralRe: Access vs ? Pin
User 1942899-Dec-04 0:02
User 1942899-Dec-04 0:02 
GeneralMultiple data adapters not needed ... Pin
Himanshu R. Swami13-Apr-04 20:21
Himanshu R. Swami13-Apr-04 20:21 
GeneralRe: Multiple data adapters not needed ... Pin
Anonymous30-Nov-04 18:55
Anonymous30-Nov-04 18:55 
GeneralRe: Multiple data adapters not needed ... Pin
Himanshu R. Swami1-Dec-04 0:37
Himanshu R. Swami1-Dec-04 0:37 
GeneralPossible Problems.... Pin
mikasa31-Mar-04 2:55
mikasa31-Mar-04 2:55 
GeneralIdentity is bad idea Pin
thunder_smit24-Mar-04 20:45
thunder_smit24-Mar-04 20:45 
GeneralRe: Identity is bad idea Pin
mikasa31-Mar-04 2:30
mikasa31-Mar-04 2:30 
GeneralRe: Identity is bad idea Pin
Krzemo9-Nov-04 4:44
Krzemo9-Nov-04 4:44 
GUIDs are too big and too slow.
Proper select statement would be:
"SELECT SCOPE_IDENTITY()"

>Select Ident_Current('Orders')
may return IDENTITY of row inserted by other session

Krzemo
GeneralRe: Identity is bad idea Pin
mikasa9-Nov-04 4:50
mikasa9-Nov-04 4:50 
QuestionCreate Access table via the adaptor? Pin
NITH15-Mar-04 22:51
NITH15-Mar-04 22:51 
AnswerRe: Create Access table via the adaptor? Pin
mikasa31-Mar-04 2:31
mikasa31-Mar-04 2:31 
Questiongood but ... more? Pin
Hovik Melkomian27-Feb-04 20:37
Hovik Melkomian27-Feb-04 20:37 
Generalrelation n:n Pin
Estrova18-Nov-03 6:17
Estrova18-Nov-03 6:17 
GeneralEscape characters in SQL statements Pin
Anonymous5-Oct-03 21:28
Anonymous5-Oct-03 21:28 
GeneralRe: Escape characters in SQL statements Pin
Mauricio Ritter6-Oct-03 4:35
Mauricio Ritter6-Oct-03 4:35 
GeneralRe: Escape characters in SQL statements Pin
Anonymous6-Oct-03 15:53
Anonymous6-Oct-03 15:53 
GeneralIdentity not getting updated Pin
xnera2-Oct-03 5:40
xnera2-Oct-03 5:40 
GeneralRe: Identity not getting updated Pin
Salcio14-Oct-03 8:19
Salcio14-Oct-03 8:19 
GeneralRe: Identity not getting updated Pin
FerBri20-Oct-04 12:45
FerBri20-Oct-04 12:45 
GeneralA different approach Pin
Ricky Helgesson1-Oct-03 8:17
Ricky Helgesson1-Oct-03 8:17 
GeneralRe: A different approach Pin
xnera1-Oct-03 10:14
xnera1-Oct-03 10:14 
GeneralRe: A different approach Pin
Ricky Helgesson1-Oct-03 23:57
Ricky Helgesson1-Oct-03 23:57 
GeneralRe: A different approach Pin
Ricky Helgesson2-Oct-03 0:31
Ricky Helgesson2-Oct-03 0:31 
GeneralRe: A different approach Pin
xnera2-Oct-03 5:02
xnera2-Oct-03 5:02 
GeneralRe: A different approach Pin
Ricky Helgesson2-Oct-03 5:15
Ricky Helgesson2-Oct-03 5:15 

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.