Updates in a transaction

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a SqlDataAdapter to update my DataSet. The updates all take place
within an single transaction. If I rollback the transaction how do I reset
the DataSet such that it knows the recent updates are still outstanding?
 
You can't !! :-) (But there is a solution - keep reading)

As a matter of fact, for this very reason there is a new property on the
data adapter in .NET 2.0 called "AcceptChangesDuringUpdate"

In .NET 1.1, you can do GetChanges on the dataset first to extract a new
dataset with only the changes, and then call AcceptChanges on the original
in the event of success, and Merge to "merge with the newly refreshed data".

HTH :-)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
 
Dick,

I think that it has not to be more difficult than this written in a kind of
pseudo.

if ds.haschanges
try
da.update(ds.getchanges)
ds.acceptchanges
catch
try
rollback
catch
'handle rollback error
end try
end try
end if

Just my thought, never tried however I would not know why not.

I hope this helps,,

Cor
 
Cor:

I haven't tried it this way but if it works that's really cool.. Say that
you have 20 rows to be changed and the 19th row will cause a Problem. 18 of
the rows will be updated and acceptchanges will be called on them. The 19th
will blow up and you 'll roll back your transaction.

In this case, does passing in GetChanges make a copy of those values so the
originals aren't touched?
 
Sahil - did you take a look at Cor's answer above? I haven't tried it that
way but if it works, that's way cool b/c you don't have to create an extra
dataset so it's more concise. I wrote Cor back but due to the time
difference, he's probably not around now. I'm in the middle of installing
Beta 2 on a new USB hard drive so I can't test it, but if it works, that's
definitely a cool idea.
 
Thanks Sahil and William. And yes, as I showered this morning, I predicted
the answer. And yes I've followed your links and read (but only for a short
time) some of your musings on this subject.

However using GetChanges doesn't necessarily solve my problem.

This is because I'm doing my data validation in the RowUpdating and
RowUpdated events and calling the Row's SetColumnError method when there is a
validation failure. So, once the DataAdapter's Update is complete, the
"GetChanges" dataset contains all my error messages - which I want to get
back into the main DataSet - but it also contains all that inaccurate
information about Rows that have been updated but since rolled back - which I
don't want to get back into my main DataSet. So using DataSet.Merge is a bad
idea, yes? But have you any good ideas?

Cheers.
 
Bill,

With this one you can try it.

\\\needs a datagrid and a textbox on a form
private void Form1_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection
("Server=YourServer;DataBase=Northwind; Integrated Security=SSPI");
string sqlstr = "SELECT EmployeeID, FirstName FROM Employees";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter
(sqlstr,conn);
da.Fill(ds);
ds.Tables[0].Rows[0][1] = "Cor";
sqlstr = "Update Employees Set FirstName = 'Bill' Where EmployeeId = 1";
SqlCommand cm = new SqlCommand(sqlstr,conn);
conn.Open();
cm.ExecuteNonQuery();
SqlCommand cmUpdate = conn.CreateCommand();
cmUpdate.CommandText =
"UPDATE Employees SET FirstName = @FirstName WHERE (EmployeeID =
@Original_Employe" +
"eID) AND (FirstName = @Original_FirstName); SELECT EmployeeID, FirstName
FROM Em" +
"ployees WHERE (EmployeeID = @EmployeeID)";
cmUpdate.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar,
10, "FirstName"));
cmUpdate.Parameters.Add(new SqlParameter("@Original_EmployeeID",
SqlDbType.Int, 4, ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "EmployeeID", DataRowVersion.Original, null));
cmUpdate.Parameters.Add(new SqlParameter("@Original_FirstName",
SqlDbType.NVarChar, 10, ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "FirstName", DataRowVersion.Original, null));
cmUpdate.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4,
"EmployeeID"));
SqlTransaction Tr;
Tr = conn.BeginTransaction();
da.UpdateCommand = cmUpdate;
da.UpdateCommand.Transaction = Tr;
if (ds.HasChanges())
{
try
{
da.Update(ds.GetChanges());
ds.AcceptChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
try
{
Tr.Rollback();
}
catch (Exception ex2)
{
MessageBox.Show(ex2.ToString());
}
}
}
dataGrid1.DataSource = ds.Tables[0];
conn.Close();
conn.Open();
cm.CommandText = "Select FirstName From Employees Where EmployeeId = 1";
textBox1.Text = cm.ExecuteScalar().ToString();
cm.CommandText = "Update Employees Set FirstName = 'Nancy' Where EmployeeId
= 1";
cm.ExecuteNonQuery();
conn.Dispose();
}
///

Cor
 
Bill,

I realized me that it was not complete, you can change these rows (plus
inserts that gives a better view)

ds.Tables[0].Rows[0][1] = "Cor";
ds.Tables[0].Rows[7][1] = "Angelo";
sqlstr = "Update Employees Set FirstName = 'Bill' Where EmployeeId = 8";


cm.CommandText = "Update Employees Set FirstName = 'Laura' Where EmployeeId
= 8";

Cor
 
Dick,

If I have not seen it wrong than is my sample working fine, therefore what
is the question.

And I thought that it was exactly what you were asking.

Cor
 
My problem with your solution is that the original dataset isn't updated when
the update fails.

For instance, I'm doing some validation in the RowUpdating event. If there
is a failure then I call the Row's SetColumnError method and set the Row's
Status to SkipCurrentRow. I do the same in the RowUpdated event if a
SqlServer error occurs (actually I set the Row's RowError property and call
its RejectChanges method).

If I don't update the original dataset (using DataSet.Merge for instance)
then it (the original dataset) won't know about the error messages. But if I
call DataSet.Merge (to solve the error message problem) then the original
dataset will think that some records have been successfully updated and will
not know that the transaction has been rolled back.

e.g.

There are three rows in the table with changes. The table gets passed to the
adapter's update method. The RowUpdating and RowUpdated events get fired for
each Row. Assume the first record gets updated successfully, the second fails
in RowUpdating (because of a business rule validation falure) and the second
fails in RowUpdated (because of a SqlServer error).

Now I want to tell the original DataSet about the errors, but I also need to
leave all its row states unchanged so that, once the user has fixed the data
problems, the outstanding changes get actioned (including those that were
made successfully but were rolled back).

Sorry if I've repeated myself here. And I hope you can point me in the right
direction. Thanks.
 
Just a note here:
Merge of tables with autoincrement primary keys won't work.
You'll have to merge such tables manually.
 
Dick,

My solution is because of this question from you.
If I rollback the transaction how do I reset
the DataSet such that it knows the recent updates are still outstanding?
Click to expand...

I understood from this that the question was that the dataset had to be back
as it was.

However beside that I don't see the problem. You use a rollback, so you need
the dataset back as it was and when everything went well. Therefore you have
to do an acceptchanges. When you have errors than you can register those in
the catchpart what where they (by instance with a clone from the datast) and
take the steps to correct them.

I hope that you are aware that the ds.getchanges is a real copy dataset?

And therefore nothing happens to the original during the update.

The only problem for you in the way you now describe is is that you cannot
do the rollback and the acceptchanges in the try block itself. You have to
set a switch in the catch and at the end of the try block do conditional if
that is set the rollback and if not the acceptchanges.

However I never did it again this way, and will not test it, because than it
becomes to much, but I see no problems (beside how you handle those errors).

I hope this helps a little bit.

Cor
 
doh
However beside that I don't see the problem. You use a rollback, so you
need the dataset back as it was and when everything went well. Therefore
you have to do an acceptchanges.
Click to expand...

However beside that I don't see the problem. You use a rollback, so you need
the dataset back as it was and when everything went well than you have
to do an acceptchanges because you have used a copy of the changed records
to update.
 
I'm sorry Cor but you miss my point. Let me try to explain it another way...

The dataset passed to the dataadapter.update method gets changed in two
ways. Firstly the rowstate of each successfully updated row gets changed to
reflect the successful update. Secondly the ColumnError and RowError
properties get changed (in the RowUpdating and RowUpdated events) for each
row that fails the update. So now the dataset maintains state for both the
rows that have been successfully updated and those that have not. So if I go
back to the pre-dataadapter.update version of the dataset I'll loose the
error messages. But if I go forward with the post-dataadapter.update version
(and rollback the database transaction) I'll be left with inacurate rowstate
information. So I have a problem either way!
 
Dick,

The dataset passed to the dataadapter.update method gets changed in two
ways. Firstly the rowstate of each successfully updated row gets changed
to
reflect the successful update. Secondly the ColumnError and RowError
properties get changed (in the RowUpdating and RowUpdated events) for each
row that fails the update.
Click to expand...
I deleted a part of your message.First:
I try to tell you all the time that what is passed to the dataadapter in the
method I show is a deepcopy of the original dataset. Are you aware of that?

Second:
I have the idea that you are mixing up in this messages two different
methods that you want to try. One with a rollback and one withouth. Can you
clear this up, because now it is very confusing for me about what you
talking. Not that your message is confusing however I cannot place that
rollback situation is some parts that you tell.

Cor
 
I don't understand what a "deep" copy is but I do understand that the dataset
passed to the dataadapter is a copy of the original and what you're saying is
"We'll have two copies of our dataset. And we'll keep one safe while we make
all our changes via the copy. And at the end of the process, if we're not
happy, we'll go back to the UNCHANGED original but if we are happy we'll call
acceptchanges on the original."

A problem with this approach is that getting any data returned from the
database back into the original dataset is very difficult - identity values
for instance.

However, my real issue is that the original dataset, the one we'll go back
to if we have some problems, won't include the error messages applied during
RowUpdating and RowUpdated - because these error messages have been applied
to the copied dataset, the one we passed to the dataadapter.

So if there are some failures with the update, and I roll back the
transaction and I go back to my unchanged original datastet, I'll also have
to lose all my error messages - the very things that will tell the user what
needs to be fixed.

I hope this makes my problem clearer. :)
 
Back
Top