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]]

Re: Database commit

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" <pk@alpha-solutions.dk> escribió en el mensaje
news:eAResppZFH A.1368@tk2msftn gp13.phx.gbl...[color=blue]
> 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
>[/color]

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

Re: Database commit

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]
- mvp@spam.guard. caspershouse.co m

"Carlos J. Quintero [.NET MVP]" <carlosq@NOSPAM sogecable.com> wrote in
message news:eWa2NiqZFH A.3648@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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" <pk@alpha-solutions.dk> escribió en el mensaje
> news:eAResppZFH A.1368@tk2msftn gp13.phx.gbl...[color=green]
>> 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
>>[/color]
>
>[/color]

[Carlos J. Quintero [.NET MVP]]

Re: Database commit

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]" <mvp@spam.guard .caspershouse.c om> escribió
en el mensaje news:OOAfW1rZFH A.2756@tk2msftn gp13.phx.gbl...[color=blue]
> 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]
> - mvp@spam.guard. caspershouse.co m
>[/color]