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

柔光的暖阳◎ 2022-10-01 04:55 212阅读 0赞

#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);
     }