title: How to pick recently inserted record id of the current session?
body: <p>I am using ASP.NET 4.0 with SQL Server 2008 R2 and PetaPoco ORM.</p>
  
  <p>A Web Form consists of Tab Panels, for example:</p>
  
  <p>-- Employee General Information</p>
  
  <p>-- Appointment</p>
  
  <p>-- Education</p>
  
  <p>Each Tab Panel entry goes to a specific table in SQL Server. I have a main table, employee, with Primary Key empID. Other tables, viz., appointment, education etc. are related with empID.</p>
  
  <p>I have multiple methods to Save record in respective tables:</p>
  
  <p><strong>AddGeneralInformation</strong> saves the General Information Tab Panel record.</p>
  
  <p><strong>AddAppointment</strong> saves the appointment details and so on.</p>
  
  <p>The application is being used in a concurrent environment where multiple users are either inserting or updating records.</p>
  
  <p>For the second and third methods to insert a new record, the methods must have the correct empID. As soon as the first method (General Information) saves the record, the empID is to be used in other methods.</p>
  
  <p>The problem is that if I use:</p>
  
  <pre><code>Select max(empID)
  </code></pre>
  
  <p>then it won't pick the correct empID as many users are inserting records.</p>
  
  <p>On solution I feel is to use another column containing SessionID and use this query:</p>
  
  <pre><code>Select max(empID) where sessionID = SessionID
  </code></pre>
  
  <p>Is there any more reliable way to do this?</p>
  
  <p>** Edited **</p>
  
  <pre><code>   protected void btnSave_Click(object sender, EventArgs e)
      {
          Session.Add("TaskFlag", "New");
          AddUpdateEmployee();
          AddUpdateAddress();
      }
  
  protected void AddUpdateEmployee()
  {
      var db = new PetaPoco.Database("cnWeb");   
      var emp = new employee();                  */
  
  
      using (TransactionScope scope = new TransactionScope())
      {
          db.BeginTransaction();
  
          emp.deptcode = txtEmpCode.Text.TrimEnd();
          emp.empname = txtEmpName.Text.TrimEnd();
          emp.guardianname = txtGuardian.Text.TrimEnd();
          emp.relationwithemployee = ddlRelation.Text.TrimEnd();
          emp.gender = ddlGender.SelectedItem.Text;
          emp.dateofbirth = Convert.ToDateTime(txtDOB.Text.TrimEnd());
  
          if (Session["TaskFlag"].ToString() == "New")
              db.Insert(emp);
          else if (Session["TaskFlag"].ToString() == "Update")
              db.Update&lt;employee&gt;("SELECT * FROM employee WHERE empid = @0", Session["EmployeeID"]);
  
          reuse.CustomClientMessage("Record Saved", this.Page);
          ClearFields();
          /* Commit Transaction */
          db.CompleteTransaction();
          scope.Complete();
      }
  }
  
  
  protected void AddUpdateAddress()
  {
      var db = new PetaPoco.Database("cnWeb");    
      var addr = new emp_address();
  
      using (TransactionScope scope = new TransactionScope())
      {
          db.BeginTransaction();
  
          /* Permanaent Address */
          addr.perm_houseno = txtPermHouse.Text.TrimEnd();
          addr.perm_street = txtPermStreet.Text.TrimEnd();
          addr.perm_place = txtPermCity.Text.TrimEnd();
          addr.perm_pincode = txtPermPincode.Text.TrimEnd();
          addr.perm_landlinephone = txtPermLandline.Text.TrimEnd();
          addr.perm_mobilephone = txtPermMobile.Text.TrimEnd();
  
          if (Session["TaskFlag"].ToString() == "New")
              db.Insert(addr);
          else if (Session["TaskFlag"].ToString() == "Update")
              db.Update&lt;emp_address&gt;("SELECT * FROM emp_address WHERE empid = @0", Session["EmployeeID"]);
  
          /* Commit Transaction */
          db.CompleteTransaction();
          scope.Complete();
      }
  
  }
  </code></pre>