【数据库】MySqlBulkLoader——批量导入

来源：互联网发布：originlab mac 破解编辑：程序博客网时间：2024/06/03 06:44

#region 批量操作        /// <summary>        ///使用MySqlDataAdapter批量更新数据        /// </summary>        /// <param name="connectionString">数据库连接字符串</param>        /// <param name="table">数据表</param>        public static void BatchUpdate( DataTable table)        {            MySqlConnection connection = GetConnection;            MySqlCommand command = connection.CreateCommand();            command.CommandTimeout = CommandTimeOut;            command.CommandType = CommandType.Text;            MySqlDataAdapter adapter = new MySqlDataAdapter(command);            MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter);            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;            MySqlTransaction transaction = null;            try            {                connection.Open();                transaction = connection.BeginTransaction();                //设置批量更新的每次处理条数                adapter.UpdateBatchSize = BatchSize;                //设置事物                adapter.SelectCommand.Transaction = transaction;                if (table.ExtendedProperties["SQL"] != null)                {                    adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();                }                adapter.Update(table);                transaction.Commit();/////提交事务            }            catch (MySqlException ex)            {                if (transaction != null) transaction.Rollback();                throw ex;            }            finally            {                connection.Close();                connection.Dispose();            }        }        /// <summary>        ///大批量数据插入,返回成功插入行数        /// </summary>        /// <param name="connectionString">数据库连接字符串</param>        /// <param name="table">数据表</param>        /// <returns>返回成功插入行数</returns>        public static int BulkInsert( DataTable table)        {            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");            if (table.Rows.Count == 0) return 0;            int insertCount = 0;            string tmpPath = Path.GetTempFileName();            string csv = DataTableToCsv(table);            File.WriteAllText(tmpPath, csv);            using (MySqlConnection conn = GetConnection)            {                MySqlTransaction tran = null;                try                {                    conn.Open();                    tran = conn.BeginTransaction();                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)                    {                        FieldTerminator = ",",                        FieldQuotationCharacter = '"',                        EscapeCharacter = '"',                        LineTerminator = "\r\n",                        FileName = tmpPath,                        NumberOfLinesToSkip = 0,                        TableName = table.TableName,                    };                    bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());                    insertCount = bulk.Load();                    tran.Commit();                }                catch (MySqlException ex)                {                    if (tran != null) tran.Rollback();                    throw ex;                }            }            File.Delete(tmpPath);            return insertCount;        }        /// <summary>        ///将DataTable转换为标准的CSV        /// </summary>        /// <param name="table">数据表</param>        /// <returns>返回标准的CSV</returns>        private static string DataTableToCsv(DataTable table)        {            //以半角逗号（即,）作分隔符，列为空也要表达其存在。            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。            StringBuilder sb = new StringBuilder();            DataColumn colum;            foreach (DataRow row in table.Rows)            {                for (int i = 0; i < table.Columns.Count; i++)                {                    colum = table.Columns[i];                    if (i != 0) sb.Append(",");                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))                    {                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");                    }                    else sb.Append(row[colum].ToString());                }                sb.AppendLine();            }            return sb.ToString();        }        #endregion 批量操作

导入自增型数据：
自增列重新生成：SqlBulkCopy bc = new SqlBulkCopy(conn)

自增列保留原值：SqlBulkCopy bc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity)

using(SqlBulkCopy sbc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity))  {      sbc.DestinationTableName = tableName;      foreach (string col in colList)      {          sbc.ColumnMappings.Add(col, col);      }     sbc.BulkCopyTimeout = 0;     sbc.WriteToServer(dt); }

0 0