c#代码，批量导入数据代码
public class MySql_Target : ZFCommon.DataAccesser.Base.DABase{public MySql_Target(){this.InitDataAccesser(ZFCommon.DataAccesser.DatabaseType.MySql, ReadConfig.TargetConnection);}///大批量数据插入,返回成功插入行数 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> /// <returns>返回成功插入行数</returns> public 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);// MySqlTransaction tran = null; using (MySqlConnection conn = new MySqlConnection(ReadConfig.TargetConnection)){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;}///将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();} }
从sqlserver读取数据，并调用 上面导入代码
//学生课表public int SyncStu_Course(){DataTable dataTable = this.GetDataTable("select * from V_Schedule_Stud");dataTable.TableName = "V_Schedule_Stud";MySql_Target tarDb = new MySql_Target();return tarDb.BulkInsert(dataTable);}
运行速度高效： 63万条数据 只需5分钟 导出并导入完成！
