How to use transaction in LINQ using C#

LINQ generates DataContext class which provides classes and methods which is used in OR-Mapping. You can also use your stored procedures and views with LINQ. You may require to use transaction with your SPs during Insert, Delete or Update operations.

System.Data.Common.DbTransaction class provides the Transaction object. I have used Northwind database in this example. Lets start with new project, you can select new project from Start -> All Programs ->  Microsoft Visual Studio 2008 Beta 2  and click on Microsoft Visual Studio 2008 Beta 2. Create new Asp.net website. Right click on website from solution explorer and select LINQ to SQL classes from Add New Item as shown below.

Fig – (1)  LINQ to SQL classes 

           This will generate dbml file in App_Code folder. Select the tables, views, stored procedures and function from server explorer and drag it on dbml file. DataContext class generates methods for each SPs, functions and views. 

           I have used Category and Product tables in this example. I have created two SPs InsertCategory and InsertProduct for inserting records in appropriate tables. You can see your SPs when you create the object of DataContext class.

Fig – (2) DataContext class shows the methods generated for SPs 

          I will first insert the category and then insert product for newly created category. If you have used some parameters as OUT parameters in your SP, you need to pass these parameters as Ref in calling method. In my SPs I have used CategoryID and ProductID as OUT parameters. 

          Now, lets move towards the transaction. I want that either category and product both will be added in database or none of them will be inserted. Below is the code for that,

System.Data.Common.DbTransaction trans = null;
DataClassesDataContext objDataClass = new DataClassesDataContext
                 (ConfigurationManager.ConnectionStrings
                                       [Constants.ConnectionString].ConnectionString);
try{

                // Nullable data type as the methods generated for SP will use Nullable
                // type                int? intCategoryID =0;
                int? intProductID =0;

                // Open the connection
                objDataClass.Connection.Open();

                // Begin the transaction
                trans = objDataClass.Connection.BeginTransaction();
               
                // Assign transaction to context class
                // All the database operation perform by this object will now use
                //transaction 
                objDataClass.Transaction = trans;

                // Insert Category
                // I have to use Ref keyword CategoryID of newly added category will
                // be assign to this variable
                objDataClass.InsertCategory
                                          (
                                            ref intCategoryID, 
                                            txtName.Text.Trim().Replace(“‘”, “””), 
                                            txtDescription.Text.Trim().Replace(“‘”, “””),
                                            new byte[0]
                                          );
                               

                // Insert Product
                // I have to use Ref keyword as ProductID of newly generated product will
                // be assign to this variable
                objDataClass.InsertProduct
                                          (
                                            ref intProductID,
                                            txtProductName.Text.Trim().Replace(“‘”,“””),
                                            null,
                                            intCategoryID,
                                            txtQuantityPerUnit.Text.Trim().Replace(“‘”, “””),
                                            Convert.ToDecimal(
                                                      txtUnitPrice.Text.Trim().Replace(“‘”, “””)
                                                                                  ),
                                             null,
                                             null,
                                             null,
                                             0);
               

                // Commit transaction
                trans.Commit();

               
            }
            catch (Exception ex)
            {                

                    // Rollback transaction
                    if (trans != null)
                                 trans.Rollback();
            }
            finally            {

                      // Close the connection
                      if (objDataClass.Connection.State == ConnectionState.Open)
                                 objDataClass.Connection.Close();
            }

     Fig – (3) Code for Transaction in LINQ using  C#

Happy Programming !!