使用SqlBulkCopy将DataTable中的数据批量插入数据库中

  #region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
        /// <summary>
        /// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
        /// </summary>
        /// <param name="strTableName">数据库中对应的表名</param>
        /// <param name="dtData">数据集</param>
        public void SqlBulkCopyInsert(string strTableName, DataTable dtData)
        {
            string ConStr = connectionString;// 数据库连接字符串

            try
            {
                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(ConStr))//引用SqlBulkCopy
                {
                    sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名

                    sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据

                    sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库

                    sqlRevdBulkCopy.Close();//关闭连接
                }
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message + "数据库处理出错654行。SqlBulkCopyInsert");
                throw (ex);
            }
        }
        #endregion

 

使用事务：

//BCP copy
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS";
    conn.Open();

    SqlTransaction sqlbulkTransaction = conn.BeginTransaction();

    //请在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
    SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);


    copy.DestinationTableName = "T_SMS_SendInfo";
    foreach (DataColumn dc in dataTable.Columns)
    {
        copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

    }
    try
    {
        copy.WriteToServer(dataTable);
        sqlbulkTransaction.Commit();
    }
    catch (Exception ex)
    {
        sqlbulkTransaction.Rollback();
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        copy.Close();
        conn.Close();
    }