Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL .NET T-SQL
I got two Tables 'Purchase Order' abd 'Purchase Order Products'.
 
I am using .NET c# program to call store procedure.
 
I would like to generate id and insert record for 'Purchase Order' first. Then insert records for 'Purchase Order Products' since it includes the ids generated.
 
If just calling a single store procedure, I can use TRY CATCH and ROLLBACK to make sure updates of two tables in sync. However, I do not know how to pass a list of information (i.e. all records for Purchase Order Product) into a single store procedure.
 
e.g. pass the following list as parameters
each Product include name, qty, amount
ProductA, 1, $20
ProductB, 3, $28
...
 
So I assume calling insert product procedure each time for one product, but then it cannot rollback the inserted row of 'Purchase Order' if any errors occur in inserting 'Products'.
 
Is there any practice that can solve my situation so I could make sure all records (Order and Products) will be added or no records will be added and error is known?
Posted 3-Mar-13 17:27pm
Edited 3-Mar-13 17:28pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
For your solution you need to study Using Transactions in ADO.NET[^] and read about SqlTransaction Class[^]. After study this you are able to integrate such given example's code into your system. Please review below links.
 
http://code.rumeryenterprises.com/post/2010/03/14/C-Sql-Transaction-Example.aspx[^]
http://www.aspnettutorials.com/tutorials/database/sql-transaction-csharp.aspx[^]
http://www.dotnetspider.com/resources/31532-Sql-Transaction.aspx[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hello ,
 
Create one stored procedure as mentioned below
 
CREATE PROCEDURE spSavePurchase_Order_Products
(
 @OrderInfo AS XML
)
AS 
BEGIN
DECLARE @tblOrderInfo AS TABLE(Product NVARCHAR(MAX),Qty INT , Amount INT)
--DECLARE @tblOrderInfo AS TABLE(ProductId NVARCHAR(MAX),Qty INT , Amount INT)

INSERT INTO @tblOrderInfo 
SELECT 
   Orders.Orderinfo.value('@Product', 'NVARCHAR(MAX)') as Product,
   Orders.Orderinfo.value('@Qty', 'NVARCHAR(MAX)') as Qty,
   Orders.Orderinfo.value('@Amount', 'NVARCHAR(MAX)') as Amount
   --Y.ID.value('(Items/Item/@Name)[1]', 'varchar(max)') as "Name",
   --Y.ID.value('(Attributes/Attribute/@ID)[1]', 'int') as AttributeID
FROM @OrderInfo.nodes('/Orders/Order') as Orders(Orderinfo)
 
BEGIN TRAN
 BEGIN TRY
  
   
   INSERT INTO [Purchase Order](Name)
   SELECT Product FROM @tblOrderInfo WHERE Product NOT IN (SELECT Name FROM [Purchase Order]) 
   
   INSERT INTO [Purchase Order Products] (ProductId  ,Qty  , Amount )
   SELECT PO.Id,tblO.Qty,tblO.Amount FROM @tblOrderInfo tblO inner join [Purchase Order] PO on tblO.Product=PO.Name
   
   
   COMMIT TRAN
  END TRY
 BEGIN CATCH 
    ROLLBACK TRAN
  END CATCH
 
END
 
Create single XML string for all your orders and execute above procedure and pass that XML String
 
see below
 
DECLARE @X AS XML
 

SET @X='<Orders>
           <Order Product="ProductA1" Qty="10" Amount="10"></Order>
           <Order Product="ProductB1" Qty="20" Amount="100"></Order>  
	</Orders>'
 

EXEC spSavePurchase_Order_Products @X
 
Hope this helps if yes then accept and vote this solution
--RDBurmon
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi swissivan,
 
(I Assume you're using MS SQL Server for db)
You can have a SQLCommand object, A connection to db and list of all queries to be executed whether it is call a procedure or it's a text command. Add all the queries to the list, create a SQLTransaction object for you SQLConnection and assign it to SQLCommand. Start the Transactin, Iterate through & execute each command in queries-list and then Commit the transaction. Put the all above in a try catch block and in Carch put the Rollback in case if anything goes wrong.
 
Do write back for any clarification.
 
Hope this helps!
 
-SK
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 275
1 DamithSL 265
2 CPallini 235
3 Maciej Los 185
4 George Jonsson 170
0 OriginalGriff 5,305
1 DamithSL 4,382
2 Maciej Los 3,760
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,901


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 4 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100