How to use transaction in LINQ using C#

I installed VS 2008 beta 2 before few days and started exploring it. I looked in to the LINQ and found very interesting. LINQ generates DataContext class which provides classes and methods which is used in OR-Mapping. You can also use your own stored procedures and views with LINQ. You may require to use transaction with your own SPs for 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.

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.

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 parameter in SP.

Now, lets look in to 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 !!