当前位置:网站首页>BulkInsert方法实现批量导入
BulkInsert方法实现批量导入
2022-08-09 21:43:00 【全栈程序员站长】
大家好,又见面了,我是你们的朋友全栈君。
最近在做新生系统,其中有一个导入功能就是把保存在Excel中的多条数据导入到mysql数据库中。最初一点思路都没有,通过查阅资料,研究出了一种导入的方法,首先要把导入的Excel文件转换成Datatable,然后在底层将Datatable 转换成csv格式的文件,最终通过MySqlBulkLoader导入到数据库中。
底层导入的方法如下
/// <summary>
///大批量数据插入,返回成功插入行数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="table">数据表</param>
/// <returns>返回成功插入行数</returns>
public static 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);
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).ToList());
insertCount = bulk.Load();
}
catch (MySqlException ex)
{
throw ex;
}
}
File.Delete(tmpPath);
return insertCount;
}
将DataTable转换为标准的CSV的方法
<span style="white-space:pre"> </span>/// <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();
}
这是底层的方法,在逻辑层只实现了简单的从Excel转换为Datatable,对于重复的数据判断后还没有提示,还有一些判断和处理需要优化,完善好了再来写。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/105727.html原文链接:https://javaforall.cn
边栏推荐
- How to Make Your Company Content GDPR Compliant
- 自监督学习 —— MoCo v2
- 6 rules to sanitize your code
- 蔚来杯2022牛客暑期多校训练营7 CFGJ
- Simple questions peek into mathematics
- PMP每日一练 | 考试不迷路-8.8(包含敏捷+多选)
- LeetCode26: remove duplicates in sorted array
- supervisor 命令操作大全「建议收藏」
- In programming languages, the difference between remainder and modulo
- Converting angles to radians
猜你喜欢
PMP每日一练 | 考试不迷路-8.9(包含敏捷+多选)
ACM MM 2022 | Cloud2Sketch: 长空云作画,AI笔生花
POWER SOURCE ETA埃塔电源维修FHG24SX-U概述
4D Summary: 38 Knowledge Points of Distributed Systems
Simple questions peek into mathematics
Shanghai Konan SmartRocket series product introduction (3): SmartRocket iVerifier computer interlocking system verification tool
hdu 1503 Advanced Fruits(最长公共子序列的应用)
Word文档怎么输入无穷大符号∞
Word怎么设置图片衬于文字下方?两种方法教你设置Word图片衬于文字下方
Xiaohei leetcode's refreshing rainy day trip, just finished eating Yufei Beef Noodles, Mala Tang and Beer: 112. Path Sum
随机推荐
Word文档怎么输入无穷大符号∞
Solution: Edu Codeforces 109 (div2)
SecureCRT背景配色
Tensorflow中placeholder函数的用法
STC8H Development (15): GPIO Drives Ci24R1 Wireless Module
APP自动化测试框架-UiAutomator2基础入门
Optimization of SQL Statements and Indexes
AI+Medical: Using Neural Networks for Medical Image Recognition and Analysis
[Generic Programming] Full Detailed Explanation of Templates
《强化学习周刊》第57期:DL-DRL、FedDRL & Deep VULMAN
2022 首期线下 Workshop!面向应用开发者们的数据应用体验日来了 | TiDB Workshop Day
windos安装Mysql8.0,及解决重新登录异常问题 ERROR 1045 (28000)
蔚来杯2022牛客暑期多校训练营7 CFGJ
【双链表增删查改接口的实现】
PMP每日一练 | 考试不迷路-8.9(包含敏捷+多选)
Technology Sharing | How to use the JSON Schema mode of interface automation testing?
LeetCode26: remove duplicates in sorted array
POWER SOURCE ETA埃塔电源维修FHG24SX-U概述
简单问题窥见数学
Usage of placeholder function in Tensorflow