0

Actually,I have used SqlBulkCopy for inserting rows into my DB and I have placed transactions to the SqlBulkCopy.
Now,My Scenario is that,I have a DataTable containing 500k rows and I'm trying to insert them through SqlBulkCopy.

My Code :

using (con)
{
   con.Open();
   using (SqlTransaction transaction = con.BeginTransaction())
   {
      using (SqlBulkCopy SBC = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction))
       {
         SBC.DestinationTableName = "SURVEY_EMAIL_BLAST";
         //SBC.BatchSize = 100;
         SBC.BulkCopyTimeout = 0;    
         SqlBulkCopyColumnMapping mapEMAILID = new SqlBulkCopyColumnMapping("EMAILID", "EMAILID");
         SBC.ColumnMappings.Add(mapEMAILID);
            try
             {
               int k = dtEmails.Rows.Count;    
               SBC.WriteToServer(dtEmails);
               transaction.Commit();
              }
              catch (System.Exception ex)
              {
                 transaction.Rollback();
              }    
              SBC.Close();
        }
    }
    con.Close();
}

It is taking very long time to insert.
When ever I stop my execution,the data is still inserting in DB with out Rolling Back

RealSteel
  • 1,871
  • 3
  • 37
  • 74
  • If by "stop my execution" you mean stopping debug then the rollback will never be reached. When you stop debugging the code exits immediately. 500 000 rows should insert fairly quickly, do you have any varbinary columns? – BossRoss Aug 22 '13 at 09:09
  • I have 2 varchar(max) columns – RealSteel Aug 22 '13 at 09:16
  • The only thing I can recommend by looking at the provider information is check your column mapping(s) and ensure all aligns correctly. – BossRoss Aug 22 '13 at 09:19

0 Answers0