MySql MySqlBulkLoader批量导入数据（批导BulkInsert）|C/S开发框架|C/S框架网

MySql MySqlBulkLoader批量导入数据（批导BulkInsert）|C/S开发框架

作者:csframework|C/S框架网发布日期:2024/11/15 15:25:33

MySql MySqlBulkLoader批量导入数据（批导BulkInsert）|C/S开发框架

MySql MySqlBulkLoader批量导入数据（批导BulkInsert）

引言

MySql批导需要将数据存储到本地csv文件，然后使用MySqlBulkLoader导入数据。

BulkInsert批导方法

C# 全选

        public override int BulkInsert<T>(List<T> entities, string tableName)
        {
            using (MySqlConnection conn = new MySqlConnection())
            {
                conn.ConnectionString = ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                //开启本地文件开关，否则报错：https://www.cscode.net/archive/newdoc/605188819611653.html
                var cmd = conn.CreateCommand();
                cmd.CommandText = "set global local_infile = 1;";
                cmd.ExecuteNonQuery();

                if (String.IsNullOrWhiteSpace(tableName))
                {
                    var tableAttribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
                    if (tableAttribute != null)
                        tableName = ((TableAttribute)tableAttribute).Name;
                    else
                        tableName = typeof(T).Name;
                }

                int insertCount = 0;
                string tmpPath = DateTime.Now.Ticks.ToString() + ".csv";

                DataTable dt = entities.ToDataTable(tableName);                
                var csv = dt.ToCsv2();

                //要与mysql的编码方式对象, 数据库要utf8, 表也一样
                using (StreamWriter sw = new StreamWriter(tmpPath, false, UTF8Encoding.UTF8))
                {
                    sw.Write(csv);
                    sw.Close();
                }

                MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    NumberOfLinesToSkip = 0,
                    FileName = tmpPath,
                    TableName = tableName,
                    ConflictOption = MySqlBulkLoaderConflictOption.Replace,
                    Priority = MySqlBulkLoaderPriority.None,
                    Timeout = 1000 * 60 * 60,//milliseconds
                    CharacterSet = "utf8",
                };
                try
                {
                    bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                    insertCount = bulk.Load();
                }
                catch (MySqlException ex)
                {
                    conn.Close();                    
                    throw;
                }
                finally
                {
                    if (File.Exists(tmpPath)) File.Delete(tmpPath);
                }

                return insertCount;
            }

        }

DataTable转csv文件

C# 全选

        /// <summary>
        /// 将DataTable转换为标准的CSV字符串（空值转换为NULL）
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="createTitle">是否添加标题</param>
        /// <returns></returns>
        public static string ToCsv2(this DataTable dataTable, bool createTitle = false)
        {
            var builder = new StringBuilder();

            // 添加列标题
            if (createTitle)
            {
                var columnTitles = string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(column => $"\"{column.ColumnName}\""));
                builder.AppendLine(columnTitles);
            }

            // 添加数据行
            foreach (DataRow row in dataTable.Rows)
            {
                var fields = row.ItemArray.Select(field => field is DBNull ? "NULL" : $"\"{field.ToString().Replace("\"", "\"\"")}\"");
                var line = string.Join(",", fields);
                builder.AppendLine(line);
            }

            return builder.ToString();
        }