Introduction
When you work with relational data you often have to work with DataSets. The .NET DataSet supports AutoIncrement columns. It's quite easy to manage updates to a single table with such an identity column. Problem arises when you try to add (insert) new records to a DataSet where the tables have parent-child relationships.
Even if a database is properly designed, you can encounter the following major problems when using DataSets:
- The order of updating the tables must be known (you cannot update the child, while there's no parent information in the database).
- When there are more client applications connected to the same database with disconnected
DataSets, upon update the database will generate its own identity values - which may differ from the identity values in the disconnected DataSet. So, further updates to a child table may result in error or in corrupted data.
Background
There were a few articles on this problem, but I couldn't find an elegant and easy to use solution. So, I decided to create some kind of a component - a base class, which is easy to use, and which presents a solution to this problem.
The ideas given in other articles were quiet OK, but they had some errors:
- First, we don't need a stored procedure (with output parameter) to acquire the identity number generated by the SQL database.
- Second, we don't have to use the
OrdersDataAdapter_OnRow event to update the identities in our DataSets when using MS Access.
The next step
The solution is simple. I've generated a class, which has a connection string and a DataSet or DataTable as input parameter. This class will generate the SELECT, INSERT, UPDATE and DELETE statements for the table (by using SqlCommandBuilder).
AutoIncrement
The idea is to modify the INSERT statement so that we can acquire the identity generated by the database. For this we have to:
- Find the AutoIncrement column in the table.
- Add an output parameter
@autoIdentity and extend the INSERT statement with : "select @autoIdentity = @@identity" - also we have to set the UpdateRowSource.Both parameter.
This solves the problem of AutoIncrement fields. If the parent field's AutoIncrement values are updated, this change is reflected in all the child foreign keys.
Order of update
I found an article (possibly on CodeProject) where someone was complaining about the update. The problem is that the update-order cannot be arbitrary. First the parent table must be updated, then the child. So, either you keep track of the relationships, or you create an algorithm which examines the correct order of update. This code was checked with the DataSet in picture, and also with a bigger system, where user info was stored. This problem was solved by using the DataTable.ParentRelations property.
The easiest solution:
- First, find the master parents (parent with no parents).
- Second, find the tables that can be updated (possible candidates are child tables of the master table). If a child table is found and can be updated, then try to find an updateable table inside the child of this table...(recursion).
So upon initialization, all the SQL queries and also the update order is calculated. I've finally added some functions:
There is also a function to define filters for the fill (for the query). Also, a property IsUpToDate, which returns true, if the table is not modified (no changes after the fill).
Using the code
ds1 = new Dataset1();
ba1 = new BaseAccessor(
System.Configuration.ConfigurationSettings.AppSettings["ConnStr"], ds1);
ba1.AddFilter(ds1.Orders, "uid = @i_a", new object[] {55});
ba1.AddFilter(ds1.OrderedItems, "orderUid = @i_a", new object[] {55});
ba1.Fill();
ba1.Update();
Points of interest
This class can be used as a base class for data manipulations.
| You must Sign In to use this message board. |
|
|
 |
|
|
I tried converting this to use with mySQL. I am running into issues with getting the identity value of an autoincrement field on db Update.
mySQL does not have a INSERT ... SELECT statement that works as it was coded originally by the writer of this. I tried changing to this:
if (idColumn != null)
{
MySqlParameter outPar = new MySqlParameter();
outPar.ParameterName = "@" + idColumn.ColumnName;
outPar.MySqlDbType = MySqlDbType.Int32;
outPar.Direction = ParameterDirection.Output;
outPar.SourceColumn = idColumn.ColumnName;
tp.insertCommand.UpdatedRowSource = UpdateRowSource.Both;
tp.insertCommand.CommandText += "; SELECT @" + idColumn.ColumnName + " = LAST_INSERT_ID()";
tp.insertCommand.Parameters.Add(outPar);
}
but I've had no luck. The DataSet is not updating the row with the new identity value that the database has created on update.
The original code was written to execute an INSERT SELECT which inserted the new row and got the identity and put it into a SqlParameter that was linked to the column in the DataSet. I am trying to do the same thing but I cannot execute the INSERT SELECT query so I tried doing two queries in the insertCommand.CommandText
Has anyone been able to get something like this to work where it returns the new identity value to a mySqlParameter with the properties specified in the code block above?
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
I have just read your article.
Where can I pick up populated copies of the database tables you use in
AutoIncrement in a disconnected DataSet?
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
|
|
|
Hello!
Thanks for the article.
I also used such an approach to call updates in correct order.
My algorithm firstly invokes inserts recursively (first parent rows, then children). Then it invokes updates (simple, just in any order). At last it invokes deletes recursively (first children, then parents).
It works very fine, even in case of self references (a table references itself). Try to test it when some node in hierarchy changes its parent and that parent is deleted. It will work!
But! Consider such situation:
We have a unique constraint on a field 'Code'.
We have the following values of Code in db:
Code1
Code2
Then, we made changes in dataset:
Delete row with value Code1.
Modify row with Code2 - change the value to Code1.
Save dataset to db.
Saving will cause an exception saying that unique constraint was violated,
because the algorithm tries to update before delete.
I cannot think over any algorithm that could solve simultaneously all the problems that are solved with above algorithm and unique constraint problem.
Does anybody had such problem and solved it elegantly?
By the way, does anybody know if DataSet.GetChanges() returns changes in correct order to be saved?
Thanks.
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
When you works with identity you can use very simple and effective technic as described in MSDN
I understand that you can automatically generate needed sentence for adapter's InsertCommand.
But why do you need for procedures for updates reordering ? ADO.NET does it in right order automatically by Relations. Am I right ?
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
1) actually i saw some technics on MSDN for working with identities, but they were not so elegant solution. either we needed a stored procedure or we had to assign a delegate (event) for each row update. and the solution WAS NOT UNIVERSAL.
2) i'm not sure if ado.net is able to update the entire dataset. i do not any reordering, simply just identify the correct order of updating the tables. IF YOU FIND A WAY TO UPDATE AN ENTIRE DATASET WITH RELATION, PLEASE LET ME KNOW... I did not checked it  I only saw an article where someone was complaining about update-order
b.
C#, ASPX, SQL
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
|
|
|
