I have a dataset with two tables tied together per a relationship : the PARENT table and the CHILD table.
I cannot use a DataAdapter to Update the database, but instead must call stored procedures to update the database based on scouring the state of the datarows.
Goes with out saying on a delete (without implement of cascading deletes) of the PARENT, the CHILD must be deleted first. Conversely, to create a CHILD I need to be sure the PARENT exists and if it doesn't, create it.
If for example, the dataset has newly added PARENT and CHILD records, again the parent must be created first and the primary key for the newly created parent row would need to taken into consideration when the CHILD record is created.
When a DataAdapter is involved, all of these issues are resolved silently. Since however I'm forced to handle the CUD (in the CRUD), I have to build this into my code, scouring relations within the dataset, checking the row states of of both the PARENT and CHILD records, making sure the CHILD foreign key to the PARENT is correct before attempting its insert, etc...
Is it possible to leverage boiler-plate .NET (ADO) to simplify this? Perhaps by examining the execution plan that would occur IF the DataAdapter were performing the work?
---------------------------- EDIT ----------------------------
I don't think it will help but here's some of the rather simplistic code I wrote to handle the tables. Per the code, I didn't take into consideration that both a new PARENT and a CHILD could be help within the DataSet, since all testing to date has assumed that the PARENT already existed. This assumption was is place because, oddly enough, I was originally asked to DEV in a UAT environment - go figure.
Anyway, I check for changes to a specific table and if found, I process. Again, didn't take into consideration both PARENT and CHILD having new records. The code below brute force handles deletes by first checking the CHILD and then the PARENT, but beyond that, there could be NEW, UPDATE and DELETE on both tables so the complexity, at least right now, is overwhelming.
High-level check on changes to the table. I've added a Priority variable for sorting later. Per the example, I set both Priority values to "1", implying a relationship that I'll capitalize later.
var ds = myDataSet; //easier to reference!
#region Delegates
Func<DBTable, bool> ChangesFound = (table) =>
{
var tbl = table.ToString();
if (ds.Tables.Contains(tbl) && ds.Tables[tbl].HasChanges())
return true;
else
return false;
};
Func<DBTable, DataTable> Table = (table) =>
{
var tbl = table.ToString();
return ds.Tables[tbl];
};
#endregion Delegates
if (ChangesFound(DBTable.CHILD)) Process_CHILD(1,Table(DBTable.CHILD));
if (ChangesFound(DBTable.PARENT)) Process_PARENT(1,Table(DBTable.PARENT));
The CHILD call sets the 2nd level priority based on the activity carried out. As a child, the Delete trumps the Insert, so it has the highest priority.
public void Process_CHILD(int priority, DataTable modifiedTable)
{
foreach (DataRow row in modifiedTable.Rows)
{
if (row.RowState != DataRowState.Added && row.RowState != DataRowState.Modified && row.RowState != DataRowState.Deleted)
continue;
DataRowVersion rowVersion = row.RowState == DataRowState.Deleted ? DataRowVersion.Original : DataRowVersion.Current;
if (row.RowState == DataRowState.Added)
{
PriorityTwo = 1;
// Build Insert Stored Procedure
}
else if (row.RowState == DataRowState.Modified)
{
PriorityTwo = 2;
// Build Update Stored Procedure
}
else if (row.RowState == DataRowState.Deleted)
{
PriorityTwo = 3;
// Build Delete Stored Procedure
}
}
}
The PARENT call sets the 2nd level priority based on the activity carried out. As a parent, the Insert trumps the Delete, so it has the highest priority.
public void Process_PARENT(int priority, DataTable modifiedTable)
{
foreach (DataRow row in modifiedTable.Rows)
{
if (row.RowState != DataRowState.Added && row.RowState != DataRowState.Modified && row.RowState != DataRowState.Deleted)
continue;
DataRowVersion rowVersion = row.RowState == DataRowState.Deleted ? DataRowVersion.Original : DataRowVersion.Current;
if (row.RowState == DataRowState.Added)
{
PriorityTwo = 3;
// Build Insert Stored Procedure
}
else if (row.RowState == DataRowState.Modified)
{
PriorityTwo = 2;
// Build Update Stored Procedure
}
else if (row.RowState == DataRowState.Deleted)
{
PriorityTwo = 1;
// Build Delete Stored Procedure
}
}
}
After all stored procedures are created, I sort by both priority variables and process each. Again, because I've set the 1st level priority on these two tables as the same, all their table processing occurs together, ordered next by PriorityTwo.
foreach (var proc in storedProcedures
.OrderByDescending(o => o.Priority)
.ThenByDescending(t => t.PriorityTwo))
{
// Call the procedure, etc...
}
This solution is pretty bad: I'm still brute-forcing the order of operations when really I should be depending on the dataset relationships already defined. I may end up building something that is driven by the relationships defined within the dataset, but again am hoping something out there already exists before I bite the bullet and rebuild what to me seems like already written boiler-plate code.
And if my troubles weren't bad enough, yet one more issue I need to resolve which I noted above. When the PARENT is created, an Identity value for its primary key is passed back to c# and I use it to update the datarow from which the stored procedure was generated. I still need to figure out how to pass that to the CHILD record so that on its stored procedure insert, referential integrity is maintained... And I need to do so without hardcoding...
Yeah the solution "as is" is bad...
Turns out I made this a lot more difficult than it needed to be. I think that was due to my aversion working with DataTable Relationships. But working through the Relationship list was (at least for me), the way to go.
I determined my solution was to either carry out all of the Inserts before any of the Deletes, or all of the Delete before any of the Inserts, and be sure when carrying out either activity (Inserts/Deletes), to do so in the proper order as dictated by the Relationships within the DataSet.
First up was to generate a list of all tables defined as a "Parent" within DataTable relations that are never also defined as a "Child" in any other relationship. These "Parents" later serve as starting points in the hierarchy processing order:
var topParents = new List<DataTable>();
foreach (DataRelation rel in ds.Relations)
{
var parent = rel.ParentTable;
var isTopParent = true;
foreach (DataRelation rel2 in ds.Relations)
{
if (parent.TableName == rel2.ChildTable.TableName) isTopParent = false;
}
if (isTopParent && !topParents.Contains(parent))
topParents.Add(parent);
}
Along with a sequential index, I next build a Dictionary List to hold table processing order. The index allows me to sort the results: Ascending for Inserts, and Descending for Deletes. I leverages Recursion to ferret out the list definition:
Dictionary<int, DataTable> insertTableOrder = new Dictionary<int, DataTable>();
foreach (var parent in topParents)
{
foreach (DataRelation rel1 in ds.Relations)
{
if (rel1.ParentTable == parent)
{
var child1 = rel1.ChildTable;
var relName1 = rel1.RelationName;
if (insertTableOrder.Where(w => w.Value.TableName == parent.TableName).Count() == 0)
insertTableOrder.Add(index++, parent);
if (insertTableOrder.Where(w => w.Value.TableName == child1.TableName).Count() == 0)
insertTableOrder.Add(index++, child1);
Recursive(insertTableOrder, ref index, child1, ds);
}
}
}
The Recursive function as:
public void Recursive(Dictionary<int, DataTable> insertTableOrder, ref int index, DataTable child1, DataSet ds)
{
foreach (DataRelation rel in ds.Relations)
if (child1 == rel.ParentTable)
{
var child2 = rel.ChildTable;
var relName2 = rel.RelationName;
if (insertTableOrder.Where(w => w.Value.TableName == child2.TableName).Count() == 0)
insertTableOrder.Add(index++, child2);
Recursive(insertTableOrder, ref index, child2, ds);
}
}
Now that I have a list of tables in the proper sequential processing order, I can build my Stored Procedures (SQLCommands) that will carry out operations on the database. I've made it a point to name the methods after the DataTables in the DataSet so that I can leverage Reflection to instantiate the methods. Since I've determined Updates can be carried out "anywhere", I process them here also:
foreach (var table in insertTableOrder.OrderBy(o => o.Key))
{
var targetTable = table.Value.GetChanges(DataRowState.Added | DataRowState.Modified);
if (targetTable != null && targetTable.HasChanges())
{
var methodName = "CreateSP_" + targetTable.TableName;
System.Reflection.MethodInfo createSP = this.GetType().GetMethod(methodName);
object result = createSP.Invoke(this, new object[] { index++, targetTable });
}
}
Then come the Deletes:
foreach (var table in insertTableOrder.OrderByDescending(o => o.Key))
{
var targetTable = table.Value.GetChanges(DataRowState.Deleted);
if (targetTable != null && targetTable.HasChanges())
{
var methodName = "CreateSP_" + targetTable.TableName;
SetAction(string.Format("Invoking Reflected method: [{0}], for {1}", methodName, purpose), "");
System.Reflection.MethodInfo createSP = this.GetType().GetMethod(methodName);
object result = createSP.Invoke(this, new object[] { index++, targetTable });
}
}
And then finally I process all other tables that not defined within a Relationship:
foreach (DataTable table in ds.Tables)
{
if (insertTableOrder.Values.Contains(table)) continue;
if (table.HasChanges())
{
var methodName = "CreateSP_" + table.TableName;
System.Reflection.MethodInfo createSP = this.GetType().GetMethod(methodName);
object result = createSP.Invoke(this, new object[] { index++, table });
}
}
With all DataBase operations generated, it's time to carry the commands. Of particular interest for the Inserts is to make note of the Primary Key assigned by the DataBase (MSSQL in the case) that is later required by Foreign Key Constraints for Children. The base 'storedProcedures' object has coded into it, all the necessary elements used below to make sure that the SQLCommand (Stored Procedure) Child of Parent is properly updated with the Foreign Key prior to hitting the database. In addition, since the original DataSet isn't being utilized by the DataAdapter, it falls on me to be sure the DataSet reflects the state of the DataBase. So for inserts, I update the rows within each DataTable of the DataSet, with the primary key assigned to the physical row when the Stored Procedure performed the insert it into the backend table.
The code below is wrapped into a Try/Catch to handle in particular, Reject Changes on the DataSet if there is a failure:
using (var conn = new System.Data.SqlClient.SqlConnection(ApplicationConfig.DbConnectInfo.ConnectionString))
{
conn.Open();
using (var transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
foreach (var parent in storedProcedures.OrderBy(o => o.Sequence))
{
var spName = parent.StoredProcedureName;
var originalValue = parent.OriginalPrimaryKeyValue;
var primaryKey = parent.PrimaryKeyUsed;
parent.Execute(conn, transaction);
if (parent.Operation == DatabaseOperaion.Insert)
{
var scopeIdentityValue = (int)parent.SQLParameters[0].Value;
foreach (DataRelation rel1 in ds.Relations)
{
var parentColumn = rel1.ParentColumns[0].ToString().ToLower();
var childColumn = rel1.ChildColumns[0].ToString().ToLower();
var childTable = rel1.ChildTable.TableName.ToLower();
//if (parent.Table == rel1.ParentTable)
if (parent.Table.TableName == rel1.ParentTable.TableName)
{
var children = storedProcedures
.Where(w => w.Table.TableName.ToLower() == childTable);
foreach (var child in children)
foreach (var parm in child.SQLParameters)
if (parm.ParameterName.Substring(1).ToLower() == childColumn)
if ((decimal)parm.Value == originalValue)
parm.Value = scopeIdentityValue;
}
var where = string.Format("{0}={1}", primaryKey, originalValue);
DataRow[] rows = ds.Tables[parent.Table.TableName].Select(where);
foreach (DataRow row in rows)
{
row.BeginEdit();
row[primaryKey] = scopeIdentityValue;
row.EndEdit();
}
}
action = string.Empty;
}
}
transaction.Commit();
ds.AcceptChanges();
} // Transaction
} // SQL Connection
Anyway, it goes without saying the solution above is specific to my needs and may not serve your particular purpose. But I do hope it helps you get past whatever difficulties you run into.