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 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 !!
hey… nice one, just the code snippet i needed to dive into transactions in linq.
thnx
Transactional LINQ in a nut shell.
Thanks guys.
Jeremy
[…] https://chiragrdarji.wordpress.com/2007/08/25/how-to-use-transaction-in-linq-using-c/ […]
Hi chiragrdarji ,
Thanks for the valuable information. I’m extremely new to LINQ and got some basic idea about it. My doubt is that,
suppose we have accessed a db Customer table record having ID 10 using LINQ in C# code. My intension is to modify the customer name from ‘Mark to ‘Peter’. At the same time somone also fetched this record. I’ve updated name to Peter. But, the other persons fetched record still contains ‘Mark’ which causes a data inconsistency. Can we prevent this by starting a transaction before fetching the record and ending after submitting the changes ?
Expecting your valubale advice.
Shabu
Thanks a lot…It helped.
[…] How to use transaction with linq: ——————————– https://chiragrdarji.wordpress.com/2007/08/25/how-to-use-transaction-in-linq-using-c/ […]
hey thx guys..
@Shabu, Transactions are about making a number of discrete database operations all or nothing. IE if you add a product, then add a product detail, but there is an error adding the product detail, then the product isn’t added either.
What you want information on is called concurrency.
For optimistic concurrency, you typically add a column to your table called RowVersion
( [RowVersion] [timestamp] NOT NULL )
What happens is that only the database can update this column, and it’s updated automatically when an INSERT or UPDATE occurs. Thus in code, RowVersion is checked, and if incoming data’s rowversion is not the same as the database rowversion, an exception is thrown.
Pre-linq this was a DBConcurrencyException. With linq it’s now a ChangeConflictException.
RE Concurrency,
Without RowVersion you can also just record original values and check every field, but it’s not as quick and is error prone when done manually.
I think Linq by default keeps track of the original object state and will throw ChangeConflictExceptions without the RowVersion field, but perhaps with RowVersion it doesn’t need to check every field.
You can also change things like the transaction level making it read-committed, which has the effect of blocking other readers of Customer 10, and maybe rows in the same page as customer 10. But you need to be very very careful with this because you can then introduce deadlock conditions, as well as really slowing down the system unnecessarily. Be very careful.
What happens when you get a concurrency exception is up to you. Essentially what happens is that someone has made the change between the time you retrieved the record and went to update the record. If you don’t use concurrency control the last person to update wins, which usually isn’t desired. With optimistic locking, long-running updates are the losers in these situations.
It is very important for me.
Thanks,
This seems to be a a good piece of information. Let me try this out…
Hi Chirag
I think DataContext class itself provides the transaction and every command being executed on this object of DataContext class will be in that transaction.
And if still we want to execute some SProcs etc onto two or more different DataContext objects but in the same transaction then we can make use of TransactionScope as:
try
{
using (System.Transactions.TransactionScope scope =
new System.Transactions.TransactionScope())
{
db.SubmitChanges();
testDb.SubmitChanges();
throw (new Exception(“Just to rollback the transaction.”));
// A warning will result because the next line cannot be reached.
scope.Complete();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Plz let me know if there is something else you want to convey.
Thanks
-Munish
Very good article.
I did a few tests where I created new records in several tables and then did a rollback and they all disappeared as expected.
It’s great that Linq-to-SQL can handle this so easily.
Superb boys!!! Cheers…
Hi Chirag,
I want to use Linq with store proceduare,
i want to get data in DataTable form my select StoreProcedure, pls explain
Hello Chiragrdarji,
thanks a lot for this really nice article. It gives a quick overview and was really helpful to me.
I also like the photo with the 5 guys and probably the after-work tea. Why did they tape their fingers?
elke..
All has tape their fingers becuase this photo was taken @ kite flying day.:)
I love the one with the orange shirt!
Is mine!
NICE, thanks!
Thanks guys, very useful. Added your blog to my favorites
Hi,
using LINQ to SQL i have displayed results in datagrid.
Now I am trying to update tables….but i could not get how to update tables simultaneously???
var z = from a in db.Products
join b in db.ProductSubcategories on a.ProductSubcategoryID equals b.ProductSubcategoryID
join d in db.ProductCategories on b.ProductCategoryID equals d.ProductCategoryID
select new { ProductName = a.Name, ProductCategory = d.Name, ProductSubCategory = b.Name, Cost = a.StandardCost, discontinuedDate = a.DiscontinuedDate, ProductId=a.ProductID };
Thanks, exactly what I was looking for.
Just what I was looking for, THANX !
Nice article
Hi chiragrdarji,
This topic is very useful for me.
I have used this way but it’s sucess. I am trying to do some other wrong calculator to it rollback….but db can’t rollback.
My code:
DataContext db = new DataContext();
db.Connection.Open();
DbTransaction trans = db.Connection.BeginTransaction();
try
{
account acc1= new account ();
acc.username = “demo”;
acc.password = “demo”;
db.AddToaccount(acc1);
db.SaveChanges();
// some other calculator…..
trans.Commit();
Label1.Text = “DB is saved success!”;
}
catch
{
trans.Rollback();
Label1.Text = “DB is rollback”;
}
please help me.
Hi Chiragrdarji,
this is a very useful tutorial..thanx 4 sharing wid us.
One quick question,
Why we are opening the connection again
( db.Connection.Open() ) as using the Linq queries it automatically opens the connection.
Don’t u think its an unwanted action on DB or redundant action.
Can we remove it and what are the possibility of improving the existing code.
Once again Thank u 4 sharing this content.
Regards
Sandeep
If you don’t apply SubmitChanges() to the datacontext.. Well.. There are no changes sent to the database. In other words: the sample code is not working. Following is a working/ better approach, imho:
using (FooBarDataContext _db = new FooBarDataContext())
{
System.Data.Common.DbTransaction _transaction = null;
_db.Connection.Open();
_transaction = _db.Connection.BeginTransaction();
_db.Transaction = _transaction;
bool _commitError = false;
try
{
_db.[].DeleteAllOnSubmit([LAMBDA EXPR]);
_db.SubmitChanges();
}
catch (Exception ex)
{
_commitError = true;
}
finally
{
if (_commitError)
{
_transaction.Rollback();
}
else
{
_transaction.Commit();
}
if (_db.Connection.State.Equals(ConnectionState.Open))
{
_db.Connection.Close();
}
}
}
Hi guys, Why do you all have bandages on your fingers in the picture up the top?
[…] Transazioni esplicite in LinQ Tweet Eccolo qua […]
very very good article
thanks alot
Nice post!
Very useful! It works!
Thank you!!
Hi,
Good, exactly is need to .. thanks
Terrific article!!
As Sandeep said, is ( db.Connection.Open() ) necessary?
Cheers!
Hi , I’m from Iran .
i like thank you for your webSite .
it ‘s useful
agin thank you