Access DB transaction or rollback?

[Sin Jeong-hun]

Hi.
I wrote a C# program that uses System.Data.OleDb (MS Access). For a
certain operation, it calls bunch of ExecuteScalar(). The problem is,
if any of those ExecuteScalar failes, then the whole DB becomes
unstable data. So can I set a rollback point or group them in a one
transaction?
Some pseudo-code like,
try
{
MyCommand.SetRollbackPoint();
MyCommand.ExecuteScalar();
MyCommand.ExecuteScalar();
......
MyCommand.ExecuteScalar();
}
catch
{
MyCommand.Rollback();
}

-------OR----------
MyCommand.StartTransaction();
MyCommand.ExecuteScalar();
MyCommand.ExecuteScalar();
......
MyCommand.ExecuteScalar();
MyCommand.EndTransaction();

Any suggestions will be welcome! Thank you.

 

[Rad [Visual C# MVP]]

Hi.
I wrote a C# program that uses System.Data.OleDb (MS Access). For a
certain operation, it calls bunch of ExecuteScalar(). The problem is,
if any of those ExecuteScalar failes, then the whole DB becomes
unstable data. So can I set a rollback point or group them in a one
transaction?
Some pseudo-code like,
try
{
MyCommand.SetRollbackPoint();
MyCommand.ExecuteScalar();
MyCommand.ExecuteScalar();
.....
MyCommand.ExecuteScalar();
}
catch
{
MyCommand.Rollback();
}

-------OR----------
MyCommand.StartTransaction();
MyCommand.ExecuteScalar();
MyCommand.ExecuteScalar();
.....
MyCommand.ExecuteScalar();
MyCommand.EndTransaction();

Any suggestions will be welcome! Thank you.

Most, if not all database providers provide a Transaction object e.g.
OleDbTransaction, SqlTransaction, OdbcTransaction that you can use
precisely for this sort of thing.

This object provides commit and rollback methods that do what you're
looking for.

 

[sloan]

I remembered this article:
http://www.codeproject.com/dotnet/ELAB.asp

i never tested it / used it, but you might check it out.

 

[Sin Jeong-hun]

Thank you. I'll try it.

Most, if not all database providers provide a Transaction object e.g.
OleDbTransaction, SqlTransaction, OdbcTransaction that you can use
precisely for this sort of thing.

This object provides commit and rollback methods that do what you're
looking for.

 

[Nicholas Paldino [.NET/C# MVP]]

Sin Jeong-hun,

I think you really should look into the TransactionScope class in the
System.Transactions namespace. It will allow you to create a scope of code
where there is a transaction, and you can call Commit at the end of the code
to commit all the changes. If you don't, then everything is automatically
rolled back.

Hope this helps.