ADO.NET Transaction

  • Thread starter Thread starter Lit
  • Start date Start date
L

Lit

Hello,

using ADO.NET 2.0, VS.NET 2005, SQL2005
I need to execute several Stored Procedures under one transaction. Can I
use just one transaction or have to use multiple ( then commit or rollback
all )
How does it work with ADO.NET

Ado.Tran
SP1 Called
loop
SP2 Called
SP3 Called
loop
SP4 Called
loop
Sp5 Called
Etc... etc...

if any error
rollback transaction ( or all transactions )
else
committe Transaction ( or all Transactions )

Thanks for any help or other Ideas

Lit
 
If it were me I would probably call a stored procedure that started the
transaction ran all of the SPs, handled the exceptions, channeled the logic
and returned success or failure via RETURN... but that's just me. I'm not a
fan of client-side transactions...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Lit said:
Hello,

using ADO.NET 2.0, VS.NET 2005, SQL2005
I need to execute several Stored Procedures under one transaction.
Can I use just one transaction or have to use multiple ( then commit
or rollback all ) How does it work with ADO.NET

Ado.Tran
SP1 Called
loop
SP2 Called
SP3 Called
loop
SP4 Called
loop
Sp5 Called
Etc... etc...

if any error
rollback transaction ( or all transactions )
else
committe Transaction ( or all Transactions )

Thanks for any help or other Ideas
Click to expand...

sql2005 doesn't support nested local transactions anyway, so you will
run 1 transaction no matter what.

first start a connection, then create a transaction from that
connection, then call all procs, then commit the transaction and if an
error occured, rollback the transaction, then close the connection and
dispose the transaction.

If you want to roll back parts of the transaction, for example if an
error in SP2 occurs, you want to rollback all work done by SP2, use
Save points of the transaction (SqlTransaction.Save ), which you can
use to rollback to (use a nested try/catch)

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
William,

What about when you have to process and send lists?
I know I can send XML to SQL
Is it always a bad reason to do client transactions?
Can you tell me more about your "bad" experience of client transactions I am
all ears.
I would love to hear some good or bad stories so I can decide what to do.

Thank you,

Lit
 
Ah, I don't have the time and you don't have the patience to wait until I
do... ;)
Nothing is ever "always bad" just as nothing is "always good". XML has its
place. It's just been misused by too many that should know better.
Client transactions put too much schema-dependent, business-rule-dependant
code on the client. I think more logic should be run from a common platform.
I discuss these issues in most of my books--even the latest. See Chapter 1
that discusses architectures...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
William,

You are right about "always", What is the name of your latest book?

Thank you,

Lit
 
"Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)" (Addison
Wesley).
I hope it helps.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
William,

This is NOT going to help me right now, it may in the future?
I am under time constraint to get things going.
I was only looking for some good pattern or guidelines to what would be best
to send large lists to SQL2005 via ADO.NET 2.0 ( Max of 3000 rows )

Thank You,

Lit
 
Okay, there is no good way to use ADO.NET query classes to do bulk uploads.
There are other ways however. The SqlBulkCopy class can take anything you
can expose with a DataReader (DataTable to DataReader is supported in 2.0)
and send it to SQL Server in a single high-speed operation.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
William,

I am hoping this is it.
I found an example of importing to an entire table.
I am hoping to be able to insert into a exiting table that has an identity
field.

Thanks for taking the time I appreciate it.

Lit
 
Generally you don't insert into a production (base) table but into a #temp
table first. Then you run a SP that runs the local validation rules and
inserts the values into the base table(s) as appropriate.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top