title: implementing transactions in c# ASP.NET Visual Studio 2013
body: <p>I'm writing a program that takes a webservice that gives many records.
  I wanted to implement a transaction so that if ever there will be an error, the "inserted" records will be rolled back.
  Here is my code but it is not working. 
  Please help me. Thank you in advance. </p>
  
  <pre><code>string findID = "SELECT e.ID, e.SchoolYearTermsID, e.StudentID, e.DegreeProgramID, e.YearLevelID, syt.SchoolYear, syt.Term FROM ENROLLEES e INNER JOIN SCHOOLYEARTERMS syt ON syt.SchoolYearTermsID = e.SchoolYearTermsID INNER JOIN STUDENTS s ON e.StudentID=s.StudentID WHERE syt.TERM = @term";
              string strcom = "SELECT * FROM STUDENTS WHERE StudentID = @id";
              string addEnrollee = "INSERT INTO ENROLLEES VALUES(@sytid, @studid, @degprogid, @yrlvlid)";
  
  
          SqlConnection connection = new SqlConnection(GlobalData.strcon2);
          connection.Open();
  
          SqlTransaction transaction;
          transaction = connection.BeginTransaction();
  
          try
          {
                  int insert = 0;
                  for (int i = 0; i &lt; dtb.Rows.Count; i++)
                  {
                      int id = int.Parse(dtb.Rows[i]["StudentID"].ToString());
                      string term = GlobalData.term;
                      SqlCommand cmd = new SqlCommand(strcom, connection);
                      cmd.Parameters.AddWithValue("@id", id);
  
                      SqlDataReader dr = cmd.ExecuteReader();
                      bool ean = dr.HasRows;
                      if (dr.HasRows == true)
                      {
                          //add
                          dr.Close();
                          SqlCommand com = new SqlCommand(findID, connection);
                          com.Parameters.AddWithValue("@id", id);
                          com.Parameters.AddWithValue("@term", term);
                          SqlDataReader d = com.ExecuteReader();
  
                          if (d.HasRows == false)
                          {
                              d.Close();
                              dr.Close();
                              SqlCommand add = new SqlCommand(addEnrollee, connection, transaction);
  
                              add.Parameters.AddWithValue("@sytid", dtb.Rows[i]["SchoolYearTermsID"].ToString());
                              add.Parameters.AddWithValue("@studid", dtb.Rows[i]["StudentID"].ToString());
                              add.Parameters.AddWithValue("@degprogid", dtb.Rows[i]["DegreeProgramID"].ToString());
                              add.Parameters.AddWithValue("@yrlvlid", dtb.Rows[i]["YearLevelID"].ToString());
                              insert++;
                              GlobalData.insertRec = insert;
                              d.Close();
                              dr.Close();
  
                              add.Transaction = transaction;
                              add.ExecuteNonQuery();
                          }
                          dr.Close();
                          d.Close();
                      }
                      dr.Close();
                  }
                  transaction.Commit();
          }
          catch
          {
                  transaction.Rollback();
  
          }
  
          finally
          {
                  connection.Close();
          }
  </code></pre>