CODEWITHSUNDEEP
c#.netsqllinqlinq-to-sql
user962206
user962206

Reputation: 16117

Inserting data into different tables at the same time with Linq-to-SQL

I have Linq-to-SQL code here which submits data into the database in their respective table, PatientInformation and ResponsibleParty:

public void addPatientInformation() { 
   using(DbClassesDataContext myDb = new DbClassesDataContext(dbPath)){
      PatientInfo patientInfo = new PatientInfo();

      patientInfo.Phy_ID = physcianID;
      patientInfo.Pat_First_Name = txtFirstName.Text;
      patientInfo.Pat_Middle_Name = txtMiddleName.Text;
      patientInfo.Pat_Last_Name = txtLastName.Text;
      patientInfo.Pat_Gender = cmbGender.Text;
      patientInfo.Pat_Marital_Status = cmbMaritalStatus.Text;
      patientInfo.Pat_Date_Of_Birth = dtpDOB.Value;
      patientInfo.Pat_Home_Add = txtHomeAdd.Text;
      patientInfo.Pat_Home_Num = txtPhone.Text;
      patientInfo.Pat_Work_Add = txtWorkAdd.Text;
      patientInfo.Pat_Work_Num = txtWorkPhone.Text;
      patientInfo.Pat_Prim_Physician = txtPrimPhysician.Text;
      patientInfo.Pat_Ref_Physician = txtRefePhysician.Text;

      myDb.PatientInfos.InsertOnSubmit(patientInfo);
      myDb.SubmitChanges();
   }
}

public void addResponsiblePartyInformation() { 
   using(DbClassesDataContext myDb = new DbClassesDataContext(dbPath)){
      ResponsibleParty responsibleParty = new ResponsibleParty();

      responsibleParty.Res_First_Name = txtResFirstName.Text;
      responsibleParty.Res_Middle_Init = txtResMiddleName.Text;
      responsibleParty.Res_Last_Name = txtResLName.Text;
      responsibleParty.Res_Gender = cmbResGender.Text;
      responsibleParty.Res_Marital_Status = cmbResMaritalStatus.Text;
      responsibleParty.Res_Date_Of_Birth = dtpResDOB.Value;
      responsibleParty.Res_Home_Add = txtResHomeAdd.Text;
      responsibleParty.Res_Home_Num = txtResPhone.Text;
      responsibleParty.Res_Work_Add = txtResWorkAdd.Text;
      responsibleParty.Res_Work_Num = txtResWorkPhone.Text;

      myDb.ResponsibleParties.InsertOnSubmit(responsibleParty);
      myDb.SubmitChanges();
   }

And a method named

public void submitInformationToDatabase() {
            addPatientInformation();
            addResponsiblePartyInformation();
            MessageBox.Show("Patient Demographics Has Been added.");
        }

Is there a way I could submit them at once?

Upvotes: 2

Views: 2142

Answers (3)

Amen Ayach
Amen Ayach

Reputation: 4348

Pass connection as parameter with both method and use System.Transaction:

public void submitInformationToDatabase() {
         using (System.Transactions.TransactionScope tr = new System.Transactions.TransactionScope())
           {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(dbPath);
            con.Open();
            addPatientInformation(con);
            addResponsiblePartyInformation(con);
            tr.Complete();
           }
            MessageBox.Show("Patient Demographics Has Been added.");
        }

Upvotes: 2

qxn
qxn

Reputation: 17584

If you can refactor your code to something resembling the example below, then both records will be inserted in the same submit.

using(DbClassesDataContext myDb = new DbClassesDataContext(dbPath)){

      myDb.PatientInfos.InsertOnSubmit(patientInfo);
      myDb.ResponsibleParties.InsertOnSubmit(responsibleParty);
      myDb.SubmitChanges();
}

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062820

Two options:

  • pass the data-context in and don't call SubmitChanges() every time (maybe make it optional)
  • use a transaction

For the second, TransactionScope could be used to do this without having to change the two methods:

using(var tran = new TransactionScope()) {
    method1(...);
    method2(...);
    tran.Complete();
}

Or with the other approach (after adding an optional parameter):

using(var ctx = new SomeDataContext(...)) {
    method1(ctx, ..., submitChanges: false);
    method2(ctx, ..., submitChanges: false);
    ctx.SubmitChanges();
}

Upvotes: 6

Related Questions