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.

On 17 Dec 2006 07:53:53 -0800, Sin Jeong-hun wrote:

> 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.
--
Bits.Bytes
http://bytes.thinkersroom.com

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

Thank you. I'll try it.

Rad [Visual C# MVP] wrote:
> On 17 Dec 2006 07:53:53 -0800, Sin Jeong-hun wrote:
>
> > 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.
> --
> Bits.Bytes
> http://bytes.thinkersroom.com

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.


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)

"Sin Jeong-hun" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>