利用MySqlBulkLoader生成csv文件，批量添加数据

最新推荐文章于 2024-09-01 23:12:24 发布

weixin_30532759

最新推荐文章于 2024-09-01 23:12:24 发布

阅读量333

点赞数 1

文章标签：数据库

原文链接：http://www.cnblogs.com/-xyl/p/11103375.html

版权

DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("YwQuaId", typeof(string));
dt.Columns.Add("CertiType", typeof(string));
dt.Columns.Add("CertiName", typeof(string));
dt.Columns.Add("CertiRemark", typeof(string));
dt.Columns.Add("Link", typeof(string));
dt.Columns.Add("FileId", typeof(string));
dt.TableName = "filedetail";
for (int i = 0; i < 50000; i++)
    {
      dt.Rows.Add(new object[] { 0, Guid.NewGuid().ToString(), "testCertType", "testCertiName", "testCertiRemark", "testLink", Guid.NewGuid().ToString() });
    }

开始调用

DapperHelper.AddByBulk(dt);

将DataTable数据写入excel文件,再从excel文件里导入数据到mysql数据库

        public static int AddByBulk(DataTable dt)
        {
            int insertCount = 0;
            using (var conn = ConnectionFactory.MySqlConnection())
            {
                    try
                    {
                        var path = @"E:/Attdence/";  
                        string filename = string.Format("{0}{1}.csv", path, dt.TableName);
                        Stopwatch sw = new Stopwatch();
                        sw.Start();
                        DataTableToCsv(dt, filename);
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = filename,
                            NumberOfLinesToSkip = 0,
                            TableName = dt.TableName,
                        };
                        bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                        insertCount = bulk.Load();
                        sw.Stop();
                        TimeSpan ts = sw.Elapsed;
                        Console.WriteLine(ts.TotalSeconds);
                    }
                    catch (Exception ex)
                    {
                       //异常处理
                    }
               
            }
            return insertCount;
        }

        private static void DataTableToCsv(DataTable table,string fileName)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
            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();
            }
            File.WriteAllText(fileName, sb.ToString());
        }

转载于:https://www.cnblogs.com/-xyl/p/11103375.html