Monday, August 11, 2008

Updating identity columns in a Dataset

One of the things I have noticed about working with DataSets is that there is not a lot of information out there on how to work with Foreign Key constraints and merging 2 databases together. Let me rephrase that, there is a but it feels scattered about and I felt it didn't teach me what I needed to know. After spending some time researching the subject here is the best way I have found to deal with the issue.

First you need to setup your connection.

string ConnectionString = "server=" + _DatabaseServer + ";user id=" + UserName + ";password=" + Password + ";Trusted_Connection=no;connection timeout=30";

Establish your connection

Connection = new SqlConnection(ConnectionString);
Connection.Open();

Now we setup our adapter
string ParentQuery = "SELECT * FROM ParentTable";
string ChildQuery = "SELECT * FROM ChildTable";
SqlDataAdapter ParentAdapter = new SqlDataAdapter(ParentQuery, Connection);
SqlDataAdapter ChildAdapter = new SqlDataAdapter(ChildQuery, Connection);

At this point we can fill our DataSet. Before we do we need to setup an event handler for updating the Identity column in each row when it gets written back to the database. The reason for this is depending on how you you merge DataSets or add rows to a DataSet the Identity column will have a place holder. This place holder will not necessarily be the same value as what the database will contain. This wouldn't be a problem if you were only updating 1 table but when you have child tables that need the same value you will run into issues.

Here is where you add the event handler

ParentAdapter.RowUpdated += new SqlRowUpdatedEventHandler(AdapterRowUpdated);

And here is the event handler

private void AdapterRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
SqlCommand IdentityCommand = new SqlCommand("SELECT @@IDENTITY", e.Command.Connection);

e.Row["ID"] = IdentityCommand.ExecuteScalar();
e.Row.AcceptChanges();
}

Now when you Update the database all your rows will have the same IDs.

Go ahead and fill our DataSet.

ParentAdapter.FillSchema(MyDataSet, SchemaType.Source, "ParentTable");
ProfileAdapter.Fill(MyDataSet, "ParentTable");
ChildAdapter.FillSchema(MyDataSet, SchemaType.Source, "ChildTable");
ChildAdapter.Fill(MyDataSet, "ChildTable");

We have 1 DataSet with 2 DataTables in it, ParentTable and ChildTable. The database has an Identity field and a Foregn Key constraint. Those are not replicated down to the DataSets so we have to do this manually.

The Unique constraint
UniqueConstraint ParentConstraints = new UniqueConstraint(new DataColumn[] { MyDataSet.Tables["ParentTable"].Columns["ID"] });
MyDataSet.Tables["ID"].Constraints.Add(ParentConstraints);

Pay attention to the UpdateRule setting for the Foreign Key constraint. We set it as Rule.Cascade. This is done so that when we update the ID column for a row that ID gets propagated to any row within the child tables that contain the same Identity.

ForeignKeyConstraint ParentFK = new ForeignKeyConstraint("IDFK",
MyDataSet.Tables["ParentTable"].Columns["ID"],
MyDataSet.Tables["ChildTable"].Columns["ID"]);
ProfileFK.UpdateRule = Rule.Cascade;
MyDataSet.Tables["ChildTable"].Constraints.Add(ParentFK);

If you are merging 2 databases together I would recommend following the same steps above for the second database. That way you have 2 identical DataSets when you go to merge them together and you can reuse all the same code again. You don't have to do this if you don't want to as there are ways around it by using MissingSchemaAction but I prefer to have identical DataSets to work from.

We have 2 DataSets and we need to merge them together. The problem is that when you Fill your DataSet by default it sets all Rows to a RowState of NoChange. This means when you merge your 2 DataSets nothing will get written to your database because nothing has changed. Any new data should be marked as Added so the Adapter will Insert it into the database when you perform the Update. This is not very efficient if you have multiple tables that need to be updated. In that case wrap the below foreach loop in another foreach loop to iterate through each table.

foreach (DataRow row in NewData.Tables["ParentTable"].Rows)
{
row.BeginEdit();
row.SetAdded();
row.EndEdit();
}


Merge the 2 DataSets. This is very easy using the Merge method in your Destination DataSet, NewData being the DataSet I want to merge into my existing data. You have to set preserveChanges to true or the Merge will throw out all your changes and nothing will make it into the new database.

MyDataSet.Merge(NewData, true, MissingSchemaAction.Add);

Our DataSets are now merged and ready to be Updated in the new location. This is pretty trivial. The only thing to remember is that the Identity column is marked as ReadOnly, therefore you can't update it without first changing it.

SqlCommandBuilder ParentCommandBuilder = new SqlCommandBuilder(ParentAdapter);
SqlCommandBuilder ChildCommandBuilder = new SqlCommandBuilder(ChildAdapter);
MyDataSet.Tables["ParentTable"].Columns["ID"].ReadOnly = false;
ParentAdapter.Update(MyDataSet.Tables["ParentTable"]);
MyDataSet.Tables["ParentTable"].Columns["ID"].ReadOnly = true;
ChildAdapter.Update(MyDataSet.Tables["ChildTable"]);

Now just check the destination database and everything should be there. Just repeat the steps above for multiple tables and multiple databases and you can merge as many databases and tables as you need to.

6 comments:

Karuna said...

Thanks, that was a terrific post. I'm a newbie and had everything working except the foreign key constraint. I appreciate the help and will be visting your site again.
Best regards,
K

TypicalAmerican said...

Glad I was able to help you out.

Roger Garstang said...

Just wanted to let you know your AdapterRowUpdated function was awesome. I've been searching forever for a simple way to get the identity field and update my dataset and your example combined everything I had seen in a nice and simple way. I knew there had to be something easier than others had posted, but couldn't see the forest for all those darn trees.

Roger Garstang said...

And, I improved it for my code too since it is only needed to be used on Inserts-

private static void AdapterRowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
{
if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert))
{
SqlCeCommand IdentityCommand = new SqlCeCommand("SELECT @@IDENTITY", e.Command.Connection);

e.Row["ID"] = IdentityCommand.ExecuteScalar();
e.Row.AcceptChanges();
}
}

Ranjan Dailata said...

Hi,

Just wanted to let you know that the class you have created: mockStringManipulator is nothing but a stub object and it's not a Mock object

Anonymous said...

Too Good Sir,
long awaited to know it, today i understood SqlAdapter and identity update gocha .
Thanks a Ton sirji