dataset.Merge

[Guest]

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).InsertCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters).DeleteCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters).UpdateCommand.Connection = SqlConn;
((SqlDataAdapter)dataAdapters).InsertCommand.Transaction = sqlTrans;
((SqlDataAdapter)dataAdapters).DeleteCommand.Transaction = sqlTrans;
((SqlDataAdapter)dataAdapters).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).Update
(dsInsert.Tables[tableNames.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).Update
(dsUpdate.Tables[tableNames.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).Update
(dsDelete.Tables[tableNames.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

 

[Jim Rand]

Try the following modification and see if the problem persists:

DataSet dsInsert = dataSet.GetChanges(DataRowState.Added);
DataSet dsUpdate = dataSet.GetChanges(DataRowState.Modified);
DataSet dsDelete = dataSet.GetChanges(DataRowState.Deleted);

/* Now do the processing */

 

[Cor Ligthert]

Aurin,

Where what you describe always happens is when the datatable has no primary
key.

Cor

 

[Kevin Yu [MSFT]]

Hi Aurin,

I agree with Cor that you might not have Primary Key set in the table.
Adding the PK will resolved the problem I think.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Sorry, but the datatable does have a primary key. It is a concatenated key
(wrid + signeddt)

 

[Kevin Yu [MSFT]]

Hi Aurin,

Is the primary key column WRId an auto increment column? If so, the WRId of
the newly added row might be different from the value in the database,
because after the record is inserted into the database, it will get a new
WRId from the database table and then refresh the row in dsInsert. So when
dsInsert is merged back to the original DataSet, the new row is considered
as different row in the original DataSet. So duplicated rows are generated.

In this case, if you would like to refresh the original DataSet, I suggest
you create a new DataSet and re-fill it.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

No, WRId is not auto-increment, it is a guid. Both wrid and signed dt are
set by the application before the insert is sent to the db.

 

[Kevin Yu [MSFT]]

Hi Aurin,

Could you build a simple example with your own update method, so that I can
try to debug on it? If so, please send it to my email box. Remove 'online'
from the nospam alias is my real email.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

As I'm sure you hoped, in building a mini-app to send you I found the problem.

The problem is that part of my concatenated primary key is a datetime which
I fill using DateTime.Now. DateTime.Now carries as many as 5 decimal places
in the seconds fraction. When SQL server gets the datetime it rounds it to 3
positions. Thus, the merge method does not recognize the original and the
updated row as being the same. For Example
DateTime.Now = 06/22/05 12:36:50.3125000
In the database this becomes 06/22/05 12:36:50.3130000

Since I do not care about milliseconds I can code (note the last parameter=0):
DateTime now = DateTime.Now;
DateTime dt2 = new DateTime
(now.Year,now.Month,now.Day,now.Hour,now.Minute,now.Second,0);