C# 使用MySqlBulkLoader 批量导入数据到Mysql

怼烎@ 2022-08-21 03:16 836阅读 0赞

# 前言： #

最近做了一个导入数据的功能，需求大概是这样的，有张Excel表格，将里面的数据插入到mysql已经建好的表里面，而且字段要一一对应，之前我们用的是SqlServer数据库，实现这个批量插入的工作就很简单了，我们可以用SqlBulkCopy批量更新数据，但是在Mysql的ADO操作对象中并没有这个对象，难道要去拼接Insert语句么？小编找到了这样一个有用的对象—— MySqlBulkLoader，是先将DATATable编程CSV文件， 在用MySqlBulkLoader导入MySql中，这样的操作很简单，下面是我的实现。

![SouthEast][]

# 代码实现： #

逻辑层：

#region ImportQuestionFile() 导入Excel模板题--周洲--2016年3月11日20:42:53
 
 
 /// <summary>
 /// 导入Excel模板题--周洲--2016年3月11日20:42:53
 /// </summary>
 /// <param name="filePathName"></param>
 /// <param name="strPaperId"></param>
 /// <returns></returns>
 public Dictionary<int, DataTable> ImportQuestionFile(string filePathName, string strPaperId)
 {
 //完整文件路径
 string strFilePath = _savePath1 + filePathName;
 //添加默认列（时间戳，是否删除等）
 Dictionary<string, string> defaultField = new Dictionary<string, string>();
 //defaultField.Add("CourseID", strPaperId);
 defaultField.Add("TimeSpan", DateTime.Now.ToString());
 
 //调用底层的方法
 Dictionary<int, DataTable> ta = new Dictionary<int, DataTable>();
 
 ExcelImportManager excelImportManager = new ExcelImportManager();
 ta = excelImportManager.ImportExcel(strFilePath, "Panduanti", defaultField, "itooexamEntities");
 
 return ta;
 }
 #endregion

调用的ExcelImportManager里面的ImportExcel方法：

public Dictionary<int, DataTable> ImportExcel(string strPath, string strXMLName, Dictionary<string, string> dicDefaultColumn, string strDBKey)
 {
 //得到导入目标表的DataTable
 Dictionary<int, DataTable> dicTargetTable = this.GetImportTable(strPath, strXMLName, dicDefaultColumn, strDBKey);
 //得到导入第三张表的DataTable
 Dictionary<int, DataTable> dicThirdTable = this.GetThirdTable();
 //得到过程中出现的问题表
 Dictionary<int, DataTable> dicErrorTable = this.GetErrorTable();
 //执行隐式事务
 // try
 // {
 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
 {
 for (int intTableIndex = 0; intTableIndex < dicTargetTable.Count; intTableIndex++)
 {
 if (dicTargetTable[intTableIndex].Rows.Count > 0)
 {
 DataTable dtTarget = dicTargetTable[intTableIndex];
 //TODO:从这里调用导入数据库的方法，在sqlhelper中
 // sqlHelper.InsertTable(strPath, strDBKey, dtTarget, dtTarget.TableName, dtTarget.Columns);
 //MySqlDBHelper mysqlhelper = new MySqlDBHelper();
 
 //mysqlhelper.BulkInsert(strDBKey,dtTarget);
 string strConnValue = ConfigHelper.ReadAppSetting(strDBKey);
 MySqlHelper mysqlhelper = new MySqlHelper(strConnValue);
 mysqlhelper.BulkInsert(dtTarget);
 }
 }
 
 scope.Complete();
 }
 // }
 //catch (Exception e)
 // {
 // throw new Exception(e.Message);
 // }
 Boolean bolIsExistErrorData = false;
 foreach (int intErrorTableIndex in dicErrorTable.Keys)
 {
 if (dicErrorTable[intErrorTableIndex].Rows.Count > 0)
 {
 bolIsExistErrorData = true;
 }
 }
 if (bolIsExistErrorData)
 {
 return dicErrorTable;
 }
 return null;
 }

调用的数据库操作类：mysqlhelper

/// <summary>
 ///大批量数据插入,返回成功插入行数
 /// </summary>
 /// <param name="connectionString">数据库连接字符串</param>
 /// <param name="table">数据表</param>
 /// <returns>返回成功插入行数</returns>
 public static int BulkInsert(string connectionString, 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);
 // MySqlTransaction tran = null;
 
 using (MySqlConnection conn = new MySqlConnection(connectionString))
 {
 
 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).ToArray());
 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();
         }

/// <summary>
         ///大批量数据插入,返回成功插入行数
         /// </summary>
         /// <param name="table">数据表</param>
         /// <returns>返回成功插入行数</returns>
         public int BulkInsert(DataTable table)
         {
             return BulkInsert(ConnectionString, table);
         }

Panduanti.xml文件，用来获取表信息和数据类型的验证

<?xml version="1.0" encoding="utf-8" ?>
 <Excel name="判断题导入模板">
 <Sheet name="判断题" table="t_sixiu_panduanti" primaryKey="QuestionID" pkType="int">
 <Column name="题号" field="QuestionID" isNecessary="true" >
 <DataType>int</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="章节" field="ChapterID" >
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="题型" field="QuestionTypeID" >
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="难度等级" field="Degree">
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="总分值" field="Fraction">
 <DataType>float</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="主题干" field="QuestionContent">
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="标准答案" field="CorrectAnswer">
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="有效性(T or F)" field="IsValid" >
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 <Column name="是否为听力(T or F)" field="other2">
 <DataType>string</DataType>
 <ForeignKey isExist="false"></ForeignKey>
 </Column>
 </Sheet>
 </Excel>

这样就简单的实现了一个批量上传的功能，跟拼接insert语句比起来，我还是选择这样的方法，方便省事的批量插入。

# 总结： #

1.一个功能实现，找方法的过程可能比你实现花费的时间长的很多，那也一定要耐得住性子去查，有很多不知道的东西，在等着你。

2.在发现mysql没有这个SqlBulkCopy的时候，我就知道一定有一个它的替代品，哈哈，果然找到了。

[SouthEast]: /images/20220731/51cbd79650bf4cb28b99bfc75238072e.png