Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#SQL-ServerLINQ
hello every body.
I defined a class with insert method for insert data to sqlserver database.
I have 2 table that one of them is child of another.
There is a relation between them.
 
i want to insert an entity(record) to parent table and one or some record to it's child table that child table's records have one forenkey of parent table's record.
I define two class:
1. First class named ‘StudentBLL’ ,for insert,update,delete students.
2. Second class named ‘TeacherBLL’ for insert,update,delete teachers.
See below :
class TeacherBLL
    {
        DataBaseDataContext _db;
        public TeacherBLL(DataBaseDataContext db)
        {
            _db = db;
        }
        public bool Insert(Teacher _teacher, Students[] _students)
        {
            using(TransactionScope ts=new TransactionScope())
            {
                _db.tbl_plaques.InsertOnSubmit(_teacher);
                try
                {
                    _db.SubmitChanges();
                    if (new StudentBLL(_db).Insert(_students))
                    {
                        //Transaction Commit
                        ts.Complete();
                        _db.Dispose();
                        return true;
                    }
                    else
                    {
                        //Transaction  Rollback
                        return false;
                    }
                }
                catch (Exception e)
                {
                    // Transaction Rollback
                    System.Windows.Forms.MessageBox.Show(e.Message);
                    return false;
                }
            }
        }
    }
 

class StudentBLL
    {
        DataBaseDataContext _db;
        public StudentBLL(DataBaseDataContext db)
        {
            _db = db;
        }
        public bool Insert(Students[] _students)
        {
            _db.tbl_plaques.InsertAllOnSubmit(_teacher);
            try
            {
                _db.SubmitChanges();
                System.Windows.Forms.MessageBox.Show("Data Inserted Successfully...");
                return true;
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
                return false;
            }
        }
    }
 

public partial class Form1 : Form
    {
        DataBase.DataClasses1DataContext _db;
        public Form1()
        {
            InitializeComponent();
            _db = new DataBase.DataClasses1DataContext();
        }
        private void ButtonSave_Click(object sender, EventArgs e)
        {
		Teacher _Teacher=new Teacher(){
			ID=txt_ID.Text,
			Gender=txt_Gender.Text,
			Name=txt_Name.Text};
		Student[] _Students=new Student[10];
		.
                .
                .
                .
                .
                if (new TeacherBLL(_db).Insert(_Teacher,_Students))
                {
                       this.Close();
                }
 
         }
}
When user click save button, I create an object of Teacher BLL class and call insert method,
In insert method ,I insert teacher entity and submit my data context’s changes and create one object of StudentBLL and call insert method for each student item.( for some reasons )
I create one transaction before insert teacher and commit transaction after students inserted successfully or rollback if one of students insert has error ( Even save not Teacher and other students).
 
Notice that my data context object created in form and is passed to teacherbll( for some reasons) in it’s constructor and to studentbll too. So my data context object not disposed if save action has error and posts to TeacherBLL and StudentBLL in future.
If user try to save data after first save without close form, occur below error :
Cannot add an entity that already exists. on line 1 of method insert in TeacherBLL :
_db.tbl_plaques.InsertOnSubmit(_teacher);
before i try to save data in second,i check database tables and no items in previous save action were saved,so in second save action i need to remove teacher item that in first save action was saved to avoid Duplicated Data error.
 
please help me :
how can i solve this problem?
how can i remove that record to solve that error.
 
thanks alot.... !
Posted 31-May-12 1:55am
Edited 1-Jun-12 1:56am
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hello
 
Use CommittableTransaction in System.Transactions. Maybe you need to add reference.
For example:
using (System.Transactions.CommittableTransaction transaction = new System.Transactions.CommittableTransaction())
{
    //...

    if(new TeacherBLL(_db).Insert(_Teacher,_Students)))
       transaction.Commit();
    else
       transaction.Rollback();       
}
 
I Don't know that your code (TracherBLL(_db),Insert) works or not but if it's ok then you can use it as I've already exampled.
  Permalink  
v2
Comments
losmac at 31-May-12 11:20am
   
Good explanations, my 5!
Shahin Khorshidnia at 31-May-12 14:19pm
   
Thank you losmac :)
sepidar_902 at 31-May-12 22:36pm
   
hi thank you for your answer i try above code,but my problem already exist and not solved.
Shahin Khorshidnia at 1-Jun-12 8:44am
   
You're welcome. The problem is in your code. Try to find it. I just wrote about Transaction.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Our purpose here, is achieved through Transaction class.
 
We create an object of TransactionScope. Any query written with in the scope of the TransactionScope object can be committed or rolled back
 
eg.
Using(TransctoinScope ts = new TransactionScope())
{
       try{
          db.SubmitChanges();
          ts.Complete();
       }
       catch
       {
          
       }
}
 
You can go through the walk through provided in MSDN. It provides you with a very clear and simple methods of using transactions.
  Permalink  
v2
Comments
sepidar_902 at 31-May-12 8:17am
   
thank you dear friend... TransctoinScope has not Commit() and Rollback() method,it has just Complete() method.
Anoop Ananthan at 12-Oct-12 16:45pm
   
Thank you for pointing it.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

With all my experience in LINQ to SQL, using TransactionScope would be better, we have done much research on Transactions while creating a Generic DAO using LINQ to SQL.
 
Implementing TransactionScope Using .NET 2.0[^]
 
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx[^]
 
The above links may help you.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 775
1 OriginalGriff 359
2 Manas Bhardwaj 295
3 Abhinav S 223
4 Sampath Lokuge 215
0 Sergey Alexandrovich Kryukov 7,967
1 OriginalGriff 4,332
2 Peter Leow 3,699
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid