471,594 Members | 2,045 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Access DB transaction or rollback?

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.

Dec 17 '06 #1
4 3472
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
Dec 17 '06 #2
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" <ty*******@gmail.comwrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...
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.

Dec 18 '06 #3
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
Dec 18 '06 #4
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]
- mv*@spam.guard.caspershouse.com

"Sin Jeong-hun" <ty*******@gmail.comwrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...
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.

Dec 18 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Avanish Pandey | last post: by
4 posts views Thread by Andrew Chanter | last post: by
2 posts views Thread by Dano | last post: by
1 post views Thread by Matik | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.