It may happen to everyone; sometimes you may want to execute your LINQ to SQL commands in one transaction like you do it in SQL Server as following:
BEGIN TRANSACTION
IF ([something happend])
begin
-- Your code is here
end
ELSE
begin
-- Restore to original
ROLLBACK TRANSACTON
end
COMMIT TRANSACTION
In this post I want to show how you can do it using LINQ to SQL:
Create your LINQ to SQL project and write some code in order to do something ex.: Add a new record to a database. You should pass the following steps in order to manage transactions in LINQ to SQL:
First, You must add a new reference named System.Transactions to your project:
Then, you must create a new variable from System.Transactions.TransactionScope like this:
using (TransactionScope ts = new TransactionScope())
{
// Your code comes here.
}
After that you must add your insert code between using tags:
using (TransactionScope ts = new TransactionScope())
{
try
{
Post p = new Post();
p.title = title;
p.keywords = keywords;
p.shortDesc = shortDesc;
p.content = content;
p.startRate = Convert.ToByte(startRate);
p.enableComments = enableComments;
p.usersCanRate = usersCanRate;
p.createDate = DateTime.Now;
p.lastModifyDate = DateTime.Now;
c.Posts.InsertOnSubmit(p);
c.SubmitChanges();
Guid postId = p.postId;
foreach (var author in authors)
{
Posts_AddAuthorRelation(postId, author.AuthorID);
}
foreach (var category in categories)
{
Posts_AddCategoryRelation(postId, category.CategoryID);
}
foreach (var source in sources)
{
Posts_AddSourceRelation(postId, source.SourceID);
}
foreach (var media in medias)
{
Posts_AddMediaRelation(postId, media.MediaID);
}
}
catch
{
Transaction.Current.Rollback();
}
}
As you can see I put a try...catch statements to my code. In try section I just wrote my insert code. If there is any error with my database or something. It will goes to my catch block.
Because I insert to more than one table, I should delete all inserted tables if there is any exception(s).
So you must use System.Transactions.Transaction.Rollback() method in order roll it back like SQL Server's ROLLBACK TRANSACTION command that we talked about it above.
With System.Transactions, you can manage your LINQ to SQL commands in transactions.