I have a program that is inserting one row into a table. When I get done
with the update, the inserted row is in the DataTable twice, although it is
only in the database once. For the reasons discussed in the summary, I am
using the user-written method below to update the dataset, rather than a
simple SqlDataAdapter.Update(). From the Console output it appears that the
problem is actually in the dataset.Merge because it is not removing the new
row from the original dataset.
I have tried changing the bool parameter on the Merge to true. That doesn't
solve the problem.
/// <summary>
/// Handles updates to more than one table in a single transaction.
/// Does Inserts first, then updates, then deletes.
/// Keeps the DataTables in sync so that the row status is not updated until
all the changes have been applied.
/// Be sure to put tables in the arrays starting with high-level first down
to low-level
/// </summary>
public static void UpdateDB(ArrayList dataAdapters, ArrayList tableNames,
DataSet dataSet)
{
int cnt;
if (dataAdapters.Count == tableNames.Count)
{
cnt = dataAdapters.Count;
}
else
{
throw new Exception ("Array sizes must match");
}
SqlTransaction sqlTrans = Global.SqlConn.BeginTransaction();
for (int i=0;i<cnt;i++)
{
((SqlDataAdapter)dataAdapters[i]).InsertCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters[i]).DeleteCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters[i]).UpdateCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters[i]).InsertCommand.Transaction = sqlTrans;
((SqlDataAdapter)dataAdapters[i]).DeleteCommand.Transaction = sqlTrans;
((SqlDataAdapter)dataAdapters[i]).UpdateCommand.Transaction = sqlTrans;
}
try
{
// first do Inserts
Console.WriteLine(dataSet.Tables["WRDeferral"].Rows.Count +" Start");
DataSet dsInsert = dataSet.GetChanges(DataRowState.Added);
if (dsInsert != null)
{
Console.WriteLine(dsInsert.Tables["WRDeferral"].Rows.Count +" Before Add
Update ");
for (int i=0;i<cnt;i++)
{
((SqlDataAdapter)dataAdapters[i]).Update
(dsInsert.Tables[tableNames[i].ToString()]);
}
Console.WriteLine(dsInsert.Tables["WRDeferral"].Rows.Count +" After Add
Update ");
}
// now do Updates
DataSet dsUpdate = dataSet.GetChanges(DataRowState.Modified);
if (dsUpdate != null)
{
Console.WriteLine(dsUpdate.Tables["WRDeferral"].Rows.Count +" Before
Modified Update ");
for (int i=0;i<cnt;i++)
{
((SqlDataAdapter)dataAdapters[i]).Update
(dsUpdate.Tables[tableNames[i].ToString()]);
}
Console.WriteLine(dsUpdate.Tables["WRDeferral"].Rows.Count +" After
Modified Update ");
}
// finally do Deletes in reverse order
DataSet dsDelete = dataSet.GetChanges(DataRowState.Deleted);
if (dsDelete != null)
{
Console.WriteLine(dsDelete.Tables["WRDeferral"].Rows.Count +" Before
Deleted Update ");
for (int i=cnt-1; i>=0; i--)
{
((SqlDataAdapter)dataAdapters[i]).Update
(dsDelete.Tables[tableNames[i].ToString()]);
}
Console.WriteLine(dsDelete.Tables["WRDeferral"].Rows.Count +" After
Deleted Update ");
}
sqlTrans.Commit();
// tell the original dataSet everything is applied
Console.WriteLine(dataSet.Tables["WRDeferral"].Rows.Count +" Before Merge
");
if (dsInsert != null) dataSet.Merge (dsInsert, false);
if (dsUpdate != null) dataSet.Merge (dsUpdate, false);
if (dsDelete != null) dataSet.Merge (dsDelete, false);
Console.WriteLine(dataSet.Tables["WRDeferral"].Rows.Count +" After Merge
");
dataSet.AcceptChanges();
Console.WriteLine(dataSet.Tables["WRDeferral"].Rows.Count +" The End ");
}
catch (SqlException e)
{
sqlTrans.Rollback();
throw new AodSqlException(e, "Database Update Failed");
}
}
This is the console output:
20 Start
1 Before Add Update
1 After Add Update
20 Before Merge
21 After Merge
21 The End
This is from SQL Profiler
exec sp_executesql N'INSERT INTO WRDeferral(WRId, SignedDt, SignedBy,
Justification, ExpirationDt) VALUES (@WRId, @SignedDt, @SignedBy,
@Justification, @ExpirationDt); SELECT def.WRId, def.SignedDt, def.SignedBy,
def.Justification, def.ExpirationDt , dbo.fPsFullName (per.first,
per.middle, per.last) as SignedByName FROM WRDeferral def left join
Personnel per on def.SignedBy = per.PersonnelKey WHERE (SignedDt =
@SignedDt) AND (WRId = @WRId)', N'@WRId uniqueidentifier,@SignedDt
datetime,@SignedBy decimal(9,0),@Justification varchar(2000),@ExpirationDt
datetime', @WRId = 'E8B8E23E-636A-4E1E-A7A6-1F80A63D41BA', @SignedDt = 'Jun
17 2005 9:40:32:593AM', @SignedBy = 363, @Justification = 'eee',
@ExpirationDt = NULL
|
Similar Threads
