Introduction
This article can be treated as an extension of my earlier post Generic Data Layer
http://www.csharpcorner.com/UploadFile/vmsanthosh.chn/209062007040459AM/2.aspx?ArticleID=96a34245-427e-4e57-adb0-f75ce28d5b1d
This aims to explain how we can execute different stored procedures in ADO.Net with different parameter values with in a single Transaction context, so that if anything gone wrong in any of the stored procedure, all others should be roll backed or else committed.
Why do I need this? "Practical approach"
Very often, we might have encountered various scenarios where we need to insert records in a batch. This can go in case of a finance application where the data is tightly coupled and inter depended as well as inserting records of a grid-view (let us keep aside datasets for a while, because there is no transactions involved) in click of a button. The handling of data in case of finance application is more complex where the dependency is high. For example General Ledgers has to be available only when the journal entries are successful.
Of course we can handle this very well, with in the stored procedure itself as we have transactions in any RDBMS you name. But what if we need to combine multiple stored procedures together and run it with in a single transaction boundary, especially when I am forced to do it only through ADO.Net? (Again please keep aside DTC for a while because I feel it is bit pain for initial configurations)
Solution
I tried to get a work around fix and my objective was to reduce the burden of developer as much as possible. Something needs to be done where things can be handled very effectively with minimum lines of code. And here is what I tried.
To get this code up, you need to have Microsoft Applications Data block that can be downloaded either from Microsoft or from my earlier blog http://www.c-sharpcorner.com/UploadFile/vmsanthosh.chn/209062007040459AM/2.aspx?ArticleID=96a34245-427e-4e57-adb0-f75ce28d5b1d
Fine, But how do I use it?
Usage of this wrapper class is quite simple.
DAC DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrder";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@CustomerName", SqlDbType.VarChar, "test");
DAC.Commands.Add(DC);
DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrderLineItems";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A1");
DAC.Commands.Add(DC);
DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrderLineItems";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A21erLineId");
DAC.Commands.Add(DC);
DC = new DAC();
DC.StoredProcedure = "nProc_CreateBill";
DC.Params.Add("@BillDate", SqlDbType.DateTime, DateTime.Now);
DC.Params.Add("@BillId", SqlDbType.VarChar, "Bill1");
DAC.Commands.Add(DC);
DAC.ExecuteBatch();
If the order insertion is failed, the bill should not be created. Similarly, if the line items are failed, then the order should not be created. We are achieving this in just a few lines of code through ADO.Net.
In this example, till we call ExecuteBatch, we are not actually inserting the records but preparing the object for making batch updations.
Hope this would be of helpful for you for your projects. Mark your comments and suggestions.
Happy Coding.