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"  wrote in message 
news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...

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

Lit wrote:


> 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


	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



"William Vaughn"  wrote in message 
news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...

> 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"  wrote in message 
> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>> 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
>>
>>
>>
>>
> 

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

"Lit"  wrote in message 
news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...

> 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
>
>
>
> "William Vaughn"  wrote in message 
> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>> 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"  wrote in message 
>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>> 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
>>>
>>>
>>>
>>>
>>
>
> 

William,

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

Thank you,

Lit

"William Vaughn"  wrote in message 
news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...

> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Lit"  wrote in message 
> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>>
>> "William Vaughn"  wrote in message 
>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>> 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"  wrote in message 
>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
> 

"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)
-----------------------------------------------------------------------------------------------------------------------

"Lit"  wrote in message 
news:Oqz%23Y2q2HHA.2752@TK2MSFTNGP06.phx.gbl...

> William,
>
> You are right about "always",  What is the name of your latest book?
>
> Thank you,
>
> Lit
>
> "William Vaughn"  wrote in message 
> news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> "Lit"  wrote in message 
>> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>>> 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
>>>
>>>
>>>
>>> "William Vaughn"  wrote in message 
>>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>>> 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"  wrote in message 
>>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
> 

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

"William Vaughn"  wrote in message 
news:ufDr8Ar2HHA.5832@TK2MSFTNGP02.phx.gbl...

> "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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Lit"  wrote in message 
> news:Oqz%23Y2q2HHA.2752@TK2MSFTNGP06.phx.gbl...
>> William,
>>
>> You are right about "always",  What is the name of your latest book?
>>
>> Thank you,
>>
>> Lit
>>
>> "William Vaughn"  wrote in message 
>> news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...
>>> 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)
>>> -----------------------------------------------------------------------------------------------------------------------
>>>
>>> "Lit"  wrote in message 
>>> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>>>> 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
>>>>
>>>>
>>>>
>>>> "William Vaughn"  wrote in message 
>>>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>>>> 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"  wrote in message 
>>>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 

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

"Lit"  wrote in message 
news:Oaencbr2HHA.5980@TK2MSFTNGP04.phx.gbl...

> 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
>
> "William Vaughn"  wrote in message 
> news:ufDr8Ar2HHA.5832@TK2MSFTNGP02.phx.gbl...
>> "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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> "Lit"  wrote in message 
>> news:Oqz%23Y2q2HHA.2752@TK2MSFTNGP06.phx.gbl...
>>> William,
>>>
>>> You are right about "always",  What is the name of your latest book?
>>>
>>> Thank you,
>>>
>>> Lit
>>>
>>> "William Vaughn"  wrote in message 
>>> news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...
>>>> 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)
>>>> -----------------------------------------------------------------------------------------------------------------------
>>>>
>>>> "Lit"  wrote in message 
>>>> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>> "William Vaughn"  wrote in message 
>>>>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>>>>> 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"  wrote in message 
>>>>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
> 

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


"William Vaughn"  wrote in message 
news:eYG9Z4r2HHA.5696@TK2MSFTNGP02.phx.gbl...

> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Lit"  wrote in message 
> news:Oaencbr2HHA.5980@TK2MSFTNGP04.phx.gbl...
>> 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
>>
>> "William Vaughn"  wrote in message 
>> news:ufDr8Ar2HHA.5832@TK2MSFTNGP02.phx.gbl...
>>> "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)
>>> -----------------------------------------------------------------------------------------------------------------------
>>>
>>> "Lit"  wrote in message 
>>> news:Oqz%23Y2q2HHA.2752@TK2MSFTNGP06.phx.gbl...
>>>> William,
>>>>
>>>> You are right about "always",  What is the name of your latest book?
>>>>
>>>> Thank you,
>>>>
>>>> Lit
>>>>
>>>> "William Vaughn"  wrote in message 
>>>> news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...
>>>>> 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)
>>>>> -----------------------------------------------------------------------------------------------------------------------
>>>>>
>>>>> "Lit"  wrote in message 
>>>>> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>> "William Vaughn"  wrote in message 
>>>>>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>>>>>> 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"  wrote in message 
>>>>>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 

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

"Lit"  wrote in message 
news:u1L6Hxs2HHA.5360@TK2MSFTNGP03.phx.gbl...

> 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
>
>
> "William Vaughn"  wrote in message 
> news:eYG9Z4r2HHA.5696@TK2MSFTNGP02.phx.gbl...
>> 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)
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> "Lit"  wrote in message 
>> news:Oaencbr2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>> 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
>>>
>>> "William Vaughn"  wrote in message 
>>> news:ufDr8Ar2HHA.5832@TK2MSFTNGP02.phx.gbl...
>>>> "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)
>>>> -----------------------------------------------------------------------------------------------------------------------
>>>>
>>>> "Lit"  wrote in message 
>>>> news:Oqz%23Y2q2HHA.2752@TK2MSFTNGP06.phx.gbl...
>>>>> William,
>>>>>
>>>>> You are right about "always",  What is the name of your latest book?
>>>>>
>>>>> Thank you,
>>>>>
>>>>> Lit
>>>>>
>>>>> "William Vaughn"  wrote in message 
>>>>> news:efidUrq2HHA.3400@TK2MSFTNGP03.phx.gbl...
>>>>>> 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)
>>>>>> -----------------------------------------------------------------------------------------------------------------------
>>>>>>
>>>>>> "Lit"  wrote in message 
>>>>>> news:eDMn4Fq2HHA.728@TK2MSFTNGP05.phx.gbl...
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "William Vaughn"  wrote in message 
>>>>>>> news:O%23fHn8h2HHA.5164@TK2MSFTNGP05.phx.gbl...
>>>>>>>> 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"  wrote in message 
>>>>>>>> news:e0$W7bg2HHA.6072@TK2MSFTNGP03.phx.gbl...
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>