T-SQL and Collections and Transactions

Question

T-SQL and Collections and Transactions

meichner

SSCertifiable

Points: 5563
More actions
December 4, 2008 at 11:27 am

#127315

I am a C# program (.net 2.0) using Sql Server 2005. I would love nothing more then to do all of my transaction processing in T-SQL. I have a senerio where I have a Sales Order Header and 1 or more Sales Order Detail Objects. The Sales Order Detail objects exist in a collection.
I would like to be able to pass the fields for the Sales Order Header and the collection that contains the Sales Order Detail objects to a stored procedure so that I could do something like the following psuedo code in T-SQl:
Start a Transaction
Insert Sales Order Header
Foreach of the Sales Order Details
Insert Sales Order Detail
Next
Commit
Is there any way to do this? I read an article that unfortuanately no longer exists that stated there was a way to accomplish what I want by passing CSV values to the stored procedure. Since the article no longer exists, I have no idea how to do what the author suggested.
Any help would be much appreciated. I should also mention that I can't use ADO.NET transactions because all of my CRUD processing opens a connection, performs an insert, update or delete and then closes the connection. Changes to this pattern would be extensive. Then again if you know a way that I can use ADO.NET transactions I am more then willing to give it a shot.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

meichner (12/4/2008)
I am a C# program (.net 2.0) using Sql Server 2005. I would love nothing more then to do all of my transaction processing in T-SQL. I have a senerio where I have a Sales Order Header and 1 or more Sales Order Detail Objects. The Sales Order Detail objects exist in a collection.
I would like to be able to pass the fields for the Sales Order Header and the collection that contains the Sales Order Detail objects to a stored procedure so that I could do something like the following psuedo code in T-SQl:
Start a Transaction
Insert Sales Order Header
Foreach of the Sales Order Details
Insert Sales Order Detail
Next
Commit
Is there any way to do this? I read an article that unfortuanately no longer exists that stated there was a way to accomplish what I want by passing CSV values to the stored procedure. Since the article no longer exists, I have no idea how to do what the author suggested.
Any help would be much appreciated. I should also mention that I can't use ADO.NET transactions because all of my CRUD processing opens a connection, performs an insert, update or delete and then closes the connection. Changes to this pattern would be extensive. Then again if you know a way that I can use ADO.NET transactions I am more then willing to give it a shot.
Thanks

Based on what you defined, I'd suggest taking a look at using XML. You could pass in a single value through the XML and then use XQUERY to pull the data out with an insert for the SalesHeader and a single Insert for all the SalesDetails (set based, not looping through FOR EACH NEXT... RBAR). Two reasons for suggesting this over CSV. If you're like most of the developers I work with these days, you already have the data in XML, so no conversions required. Second, there's more flexibility inherint with XQUERY against the XML data type than trying to pivot untold number of detailed CSV values into a table so you can then do an insert.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

meichner SSCertifiable Points: 5563 More actions · Answer 2

Grant Fritchey (12/4/2008)
meichner (12/4/2008)
I am a C# program (.net 2.0) using Sql Server 2005. I would love nothing more then to do all of my transaction processing in T-SQL. I have a senerio where I have a Sales Order Header and 1 or more Sales Order Detail Objects. The Sales Order Detail objects exist in a collection.
I would like to be able to pass the fields for the Sales Order Header and the collection that contains the Sales Order Detail objects to a stored procedure so that I could do something like the following psuedo code in T-SQl:
Start a Transaction
Insert Sales Order Header
Foreach of the Sales Order Details
Insert Sales Order Detail
Next
Commit
Is there any way to do this? I read an article that unfortuanately no longer exists that stated there was a way to accomplish what I want by passing CSV values to the stored procedure. Since the article no longer exists, I have no idea how to do what the author suggested.
Any help would be much appreciated. I should also mention that I can't use ADO.NET transactions because all of my CRUD processing opens a connection, performs an insert, update or delete and then closes the connection. Changes to this pattern would be extensive. Then again if you know a way that I can use ADO.NET transactions I am more then willing to give it a shot.
Thanks
Based on what you defined, I'd suggest taking a look at using XML. You could pass in a single value through the XML and then use XQUERY to pull the data out with an insert for the SalesHeader and a single Insert for all the SalesDetails (set based, not looping through FOR EACH NEXT... RBAR). Two reasons for suggesting this over CSV. If you're like most of the developers I work with these days, you already have the data in XML, so no conversions required. Second, there's more flexibility inherint with XQUERY against the XML data type than trying to pivot untold number of detailed CSV values into a table so you can then do an insert.

That's an interesting approach. One I would not have thought off. Is this a common way of handling this problem?

Thanks

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Prior to 2005, people (including me) tried using OPENXML to perform this function. It worked reasonably well, but instantiating the XML document back then was a pretty major performance hit, so you need to be seeing, at least, hundreds of rows coming in before this worked better than simply calling insert 100 times.

Now that you can pass an XML document straight into the procedure and call into it using XQUERY rather than having to perform OPENXML, it's a lot faster, less painful, with easier to read and maintain code.

It's fairly common, but hardly an industry-wide best practice.

One other note, taking the SalesHeader followed by SalesDetail approach, I assume you have artificial keys for the SalesHeader? Look into Books Online for the OUTPUT clause. You can use that to capture the artificial key values generated as you do the inserts to SalesHeader and then use them on the SalesDetail inserts. Handy trick.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

meichner SSCertifiable Points: 5563 More actions · Answer 4

Thanks for all the input. I will look into the XML approach. I am currently using the OUTPUT parameter to obtain the identity key from the Sales Order Header so that I could pass it to the Sales Order Detail.

Thanks