title: Delete not working in transaction with sql bulk insert
body: <p>I need to delete some records from table and then insert some records to the same table. This delete and insert process should be in transaction.</p>
  
  <p>Below is what i did.</p>
  
  <pre><code> using (SqlConnection sqlConn = new SqlConnection(connectionString))
          {
              sqlConn.Open();
              using (SqlTransaction sqlTran = sqlConn.BeginTransaction())
              {
                  string deleteQuery = "delete from dbo.MyTable where Col1 =" + colValue;
                  SqlCommand sqlComm = new SqlCommand(deleteQuery, sqlConn,sqlTran);
                  sqlComm.ExecuteNonQuery();
                  using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran))
                  {
                      sqlcopy.BatchSize = 10;
                      sqlcopy.DestinationTableName = "MyTable";
                      try
                      {
                          sqlcopy.WriteToServer(dsDataSet.Tables[0]);
                          sqlTran.Commit();
                      }
                      catch (Exception ex)
                      {
                          sqlTran.Rollback();
                      }
                  }
              }
          }
  </code></pre>
  
  <p>But, i guess as the delete operation is not getting performed before insert, i get duplicate key errors. Can some one help.?</p>