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.

Mocks and stubs

I am getting to a point where I am designing more and more complex applications. Since I am doing this alone I sometimes find it over whelming when I realize everything I am going to need to do as I progress though an application. Combine this with performing TDD at the same time and you begin to realize just how much work you have ahead of yourself.

Some of the things that I have begun to use in order to make things easier are mocks and stubs. I first heard about mocks and stubs while researching TDD and am beginning to use them for more than just testing. There are some subtle differences between mocks and stubs but they are similar.

Stubs are methods or functions that are only meant as place holders. I have yet to see a stub actually do anything. An example of a stub would be:

public static void SomeMethod(string MyString)
{

}

It doesn't do anything but it will allow code to compile that will need to use this method. Stubs are one of the first steps towards getting your unit tests to pass, if you are following a test driven approach.

Mocks can be anywhere from a little more complex to a lot more complex than a stub. Mocks allow you to create methods/functions that return expected data. They are usually the next logical step in TDD after a stub. Building on the above stub a mock could be:

public static string SomeMethod(string MyString)
{
return "This is my new modified string";
}

This will get a test to pass and it will allow you to test other portions of your code that depend on this method without fully implementing it before you are ready.

Now where mocks really shine is when you start using interfaces. The way I use this is as follows.

public interface IStringManipulator
{
public static string SomeMethod(string MyString)
public static bool IsNull(string MyString)
}

Now that the interface is designed you can start with your mock.

public class mockStringManipulator : IStringManipulator
{
public static string SomeMethod(string MyString)
{
return "This is my new modified string";
}
public static bool IsNull(string MyString)
{
return true;
}
}

You have your mock, now you can use the mock as a placeholder.

public class WebConnector
{
public void Connection(string Username)
{
String NewUsername;
if(!mockStringManipulator.IsNull(Username))
{
NewUsername = mockStringManipulator.SomeMethod(Username)
return NewUsername;
}
else
{
throw NullReferenceException;
}

}
}

You will of course be writing unit tests on the WebConnector class and when you get around to fully implementing the StringManipulator class you will be using the IStringManipulator interface. This ensures that all the only difference between your mock class and your production class is in the implementation. The signatures and everything else matches exactly and since you will be performing TDD on the production class you will have a high degree of confidence that it will work as expected before you change from the mock to the fully developed class.