首页 > Web开发 > 详细

.NETCore批量插入数据BulkLoader

时间:2019-06-26 20:02:13      阅读:682      评论:0      收藏:0      [点我收藏+]
namespace DapperExtensions.MySql
{
    public static class MySqlBulkLoaderHelper
    {
        /// <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 if (colum.DataType == typeof(DateTime) || colum.DataType == typeof(DateTime?))
                    {
                        sb.Append(string.IsNullOrEmpty(row[colum].ToString()) ? "1970-01-01 00:00:00" : row[colum].ToString());
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }


            return sb.ToString();
        }

        /// <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();
                    File.Delete(tmpPath);
                    throw ex;
                }
            }
            File.Delete(tmpPath);
            return insertCount;
        }
    }
}

  MSSQL有SqlBulkCopy,MYSQL有MySqlBulkLoader,Oracle有OracleBulkCopy,对应各自的驱动。

  但是core没有批量插入数据的方法,所以只能创建临时CSV,导入CSV,批量插入后再删除CSV

.NETCore批量插入数据BulkLoader

原文:https://www.cnblogs.com/yinzhou/p/11093033.html

(0)
(0)
   
举报
评论 一句话评论(0
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!