Database commit

[Peter Kirk]

Hi there

when working with database connections in C# is there a concept of "auto
commit"?

For instance, if I obtain a database connection in java I can set the
auto-commit state to true or false. Is there something similar in C#, or how
do I handle this - do I always have to start and end transactions and commit
database updates myself?

Thanks
Peter

[Carlos J. Quintero [.NET MVP]]

Hi Peter,

By default, ODBC drivers, OLE DB Providers and .NET Data Providers set
auto-commit on, so to use a transaction you have to request it explicitly
via Connection.Begi nTransaction()

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com

"Peter Kirk" <[e-mail]> escribió en el mensaje
news:eA******** ******@tk2msftn gp13.phx.gbl...

Hi there

when working with database connections in C# is there a concept of "auto
commit"?

For instance, if I obtain a database connection in java I can set the
auto-commit state to true or false. Is there something similar in C#, or
how do I handle this - do I always have to start and end transactions and
commit database updates myself?

Thanks
Peter

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

I wouldn't recommend this. Managing a transaction yourself can be more
trouble than it is worth, especially when you start adding multiple
resources (other transactable resources, such as other databases, and
filesystems, etc, etc) and multiple objects (because you have to pass the
transaction/connection around) into the mix.

A better solution would be to use a class derived from
EnterpriseServi ces to take advantage of COM+'s support for transaction
management. At that point, you can just adorn your class with an attribute
or two (one for transactions, and one on the method to tell it to auto
commit if no exception is thrown), and the transactions will be taken care
of for you. If you change the method later to include calls to other
objects that use transactions, then it will be managed automatically for
you.

Also, in .NET 2.0, there is a new namespace, System.Transact ions which
provides the Transaction class as well as the TransactionScop e class. These
can help you specify blocks of code where you want transactions to take
place, and any resources that can, will participate in the commiting and
aborting of that transaction.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Carlos J. Quintero [.NET MVP]" <ca*****@NOSPAM sogecable.com> wrote in
message news:eW******** ******@TK2MSFTN GP14.phx.gbl...

Hi Peter,

By default, ODBC drivers, OLE DB Providers and .NET Data Providers set
auto-commit on, so to use a transaction you have to request it explicitly
via Connection.Begi nTransaction()

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com

"Peter Kirk" <[e-mail]> escribió en el mensaje
news:eA******** ******@tk2msftn gp13.phx.gbl...

Hi there

when working with database connections in C# is there a concept of "auto
commit"?

For instance, if I obtain a database connection in java I can set the
auto-commit state to true or false. Is there something similar in C#, or
how do I handle this - do I always have to start and end transactions and
commit database updates myself?

Thanks
Peter

[Carlos J. Quintero [.NET MVP]]

Hi Nicholas,

Well, it all depends, there are 2 approaches. I have managed my transactions
for years without problems with simple client/server Windows applications...

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c om> escribió
en el mensaje news:OO******** ******@tk2msftn gp13.phx.gbl...

I wouldn't recommend this. Managing a transaction yourself can be more
trouble than it is worth, especially when you start adding multiple
resources (other transactable resources, such as other databases, and
filesystems, etc, etc) and multiple objects (because you have to pass the
transaction/connection around) into the mix.

A better solution would be to use a class derived from
EnterpriseServi ces to take advantage of COM+'s support for transaction
management. At that point, you can just adorn your class with an
attribute or two (one for transactions, and one on the method to tell it
to auto commit if no exception is thrown), and the transactions will be
taken care of for you. If you change the method later to include calls to
other objects that use transactions, then it will be managed automatically
for you.

Also, in .NET 2.0, there is a new namespace, System.Transact ions which
provides the Transaction class as well as the TransactionScop e class.
These can help you specify blocks of code where you want transactions to
take place, and any resources that can, will participate in the commiting
and aborting of that transaction.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m