SqlBulkCopy 批量操作数据

　　　　　public bool SaveToDatabase(string sourceDataFilesDirectory, string errorFilesDirectory, string fileName)
        {
            string strConnString = String.Empty;
            string sql_select;
            OdbcConnection conn = null;
            strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" +
                                     "DEFAULTDIR=" + sourceDataFilesDirectory + ";" +
                                     "Extensions=asc,csv,tab,txt;" +
                                     "Persist Security Info=False";
            try
            {
                // Creates and opens an ODBC connection
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                // Creates the ODBC command
                sql_select = string.Format("select * from [{0}]", fileName);
                OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);

                // Makes on OdbcDataReader for reading data from CSV
                OdbcDataReader dataReader = commandSourceData.ExecuteReader();

                // Creates schema table. 
                // It gives column names for create table command.
                DataTable dt = dataReader.GetSchemaTable();
                SqlConnection sqlConn = new SqlConnection(config.ConnectionString);
                sqlConn.Open();

                using (SqlTransaction tran = sqlConn.BeginTransaction())
                {
                    // 批量保存数据，只能用于Sql
                    SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock, tran);
                    // 设置源表名称
                    sqlbulkCopy.DestinationTableName = entity.Name;
                    // 设置超时限制
                    sqlbulkCopy.BulkCopyTimeout = 120;
                    
                    
                    //refPropertyList
                    string fieldName = string.Empty;
                    foreach (DataRow dtRow in dt.Rows)
                    {

                        fieldName = dtRow["ColumnName"].ToString();
                        if (entity.HeaderTexts.Contains(fieldName))
                        {
                            sqlbulkCopy.ColumnMappings.Add(fieldName, entity.GetColumnNameByHeaderText(fieldName));
                        }
                        else
                        {
                            conn.Close();
                            throw new Exception("文件格式错误！");
                        }
                    }

                    try
                    {
                        // 写入
                        sqlbulkCopy.WriteToServer(dataReader);      
                        // 提交事务
                        tran.Commit();
  　　　　　　　　　　　}
                    catch (Exception e)
                    {
                        tran.Rollback();
                        sqlbulkCopy.Close();
                        throw e;
                    }
                    finally
                    {
                        sqlbulkCopy.Close();
                        sqlConn.Close();
                        dataReader.Close();
                        conn.Close();
                    }
                }
            }
            catch (Exception e)
            {
                //写入日志
                string errMsg = e.Message;
                if (e.Message.Contains("\r\n"))
                {
                    errMsg = e.Message.Substring(0, e.Message.IndexOf("\r\n"));
                }
                errMsg = "文件：" + fileName + " 导入失败，失败原因：" + errMsg;
                LogHelper.WriteError(errMsg);
                LogHelper.SendEmail(errMsg);
                return false;
            }
            return true;
        }

SqlBulkCopy 说明：

　　public SqlBulkCopy(string connectionString, SqlBulkCopyOptions copyOptions);
     public SqlBulkCopy(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction);

　　SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock, tran);
　　或 SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.UseInternalTransaction);

生成SqlBulkCopy 实例，构造函数指定了目标数据库，使用SqlBulkCopyOptions.UseInternalTransaction是指迁移动作指定在一个Transaction当中，如果数据迁移中产生错误或异常将发生回滚。