2023年6月21日发(作者：)
使⽤SqlBulkCopy批量插⼊更新数据 在开发中遇到了⼀张表的数据因为只做了同步插⼊⽽没有做同步更新的操作，导致了百万数据不准确。⾯对⼤量数据需要更新，传统的循环逐条插⼊以及拼接1000条数据插⼊都⽐较耗时，⽹上有博主做出了相关测试。 根据以上场景，新建控制台程序。config添加数据库连接配置，sqlHelper连接更新数据源，sqlBulkCopyHelper连接更新⽬标库。 创建sqlHelper类using System;using c;using ;using ent;using ;using ;using ;namespace SqlBulkCopyHelper{ public class sqlHelper { /// /// 数据库操作帮助类 /// 此段基础代码为SQLServer数据库帮助类 /// 如需操作MySQL /// 1.将代码中Sql改为MySql /// 2.添加引⽤(可通过官⽹或NuGet) /// ent;替换为using lient; /// /// /// 数据库连接字符串 /// private static string connectionStr = tionStrings["ConnectionName"].ConnectionString; public sqlHelper() { } public sqlHelper(string connectionStr) { tionStr = connectionStr; } /// /// 得到连接对象 /// /// public static SqlConnection GetConn() { SqlConnection sqlconn = null; sqlconn = new SqlConnection(connectionStr); return sqlconn; } /// /// 查询操作 /// /// /// public static DataTable GetDataTable(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn)) { ge(sp); DataTable dt = new DataTable(); (dt); return dt; } } } /// /// 增删改操作 /// /// sql语句 /// 执⾏后的条数 public static int ExecuteNonQuery(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlCommand cmd = new SqlCommand(sql, conn)) { ge(sp); int i = eNonQuery(); return i; } } } /// /// 执⾏⼀条SQL语句,返回⾸⾏⾸列 /// /// sql语句 /// ⾸⾏⾸列 public static object ExecuteScalar(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlCommand cmd = new SqlCommand(sql, conn)) { ge(sp); return eScalar(); } } } }} 创建sqlBulkCopyHelper/// /// SqlBulkCopy 批量更新数据 /// /// 数据集 /// 临时表创建字段 /// 更新语句 public static void BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql) { eConfiguration(rRoamingAndLocal); using (var conn = new SqlConnection(tionStrings["DefaultConnection"].ConnectionString)) { using (var command = new SqlCommand("", conn)) { try { (); //数据库并创建⼀个临时表来保存数据表的数据 dText = (" CREATE TABLE #TmpTable ({0})", crateTemplateSql); eNonQuery(); //使⽤SqlBulkCopy 加载数据到临时表中 using (var bulkCopy = new SqlBulkCopy(conn)) { foreach (DataColumn dcPrepped in s) { (Name, Name); } pyTimeout = 660; ationTableName = "#TmpTable"; oServer(dataTable); (); } // 执⾏Command命令 使⽤临时表的数据去更新⽬标表中的数据 然后删除临时表 dTimeout = 300; dText = updateSql; eNonQuery(); } finally { (); } } } } Program代码/// /// 更新表数据 /// public static void update() { String sqlstring = @""; tch stopwatch = new Stopwatch(); (); // 开始监视代码运⾏时间 DataTable dt = aTable(sqlstring); (); // 停⽌监视 ine("执⾏查询sql⽤时：" + econds + "秒,共查询到：" + + "⾏"); String updateSql = @"Merge into Table AS T
Using #TmpTable AS S
ON (_no = _no and _code = _code )WHEN MATCHED
THEN UPDATE SET T.[qty]=S.[qty],T.[total_amount]=S.[total_amount];"; String crateTemplateSql = @" [order_no] [varchar](32) NULL, [qty] [int] NULL, [total_amount] [decimal](18, 2) NULL, [item_code] [varchar](32) NULL,"; for (int i = 0; i < ( + 10000 - 1) / 10000; i++) { tch stopwatch2 = new Stopwatch(); (); dateData(erable().Skip(i * 10000).Take(10000).CopyToDataTable(), crateTemplateSql, updateSql); (); ine("更新第" + (i + 1) + "次耗时：" + econds + "秒,剩余" + (( + 10000 - 1) / 10000 - i - 1) + "次"); } ne(); } 1.更新的时候，datatable数据量过⼤内存不够⽤，这⾥是分了⼀下页。 2.还需要注意的就是sqlBulkCopy在使⽤的时候，视图或者是源表的字段⼤⼩写、类型必须与⽬标表⼀致。 3. Merge，使⽤merger语句可以将插⼊、更新、删除合并成⼀句，完成存在就更新不存在就插⼊的需求。
2023年6月21日发(作者：)
使⽤SqlBulkCopy批量插⼊更新数据 在开发中遇到了⼀张表的数据因为只做了同步插⼊⽽没有做同步更新的操作，导致了百万数据不准确。⾯对⼤量数据需要更新，传统的循环逐条插⼊以及拼接1000条数据插⼊都⽐较耗时，⽹上有博主做出了相关测试。 根据以上场景，新建控制台程序。config添加数据库连接配置，sqlHelper连接更新数据源，sqlBulkCopyHelper连接更新⽬标库。 创建sqlHelper类using System;using c;using ;using ent;using ;using ;using ;namespace SqlBulkCopyHelper{ public class sqlHelper { /// /// 数据库操作帮助类 /// 此段基础代码为SQLServer数据库帮助类 /// 如需操作MySQL /// 1.将代码中Sql改为MySql /// 2.添加引⽤(可通过官⽹或NuGet) /// ent;替换为using lient; /// /// /// 数据库连接字符串 /// private static string connectionStr = tionStrings["ConnectionName"].ConnectionString; public sqlHelper() { } public sqlHelper(string connectionStr) { tionStr = connectionStr; } /// /// 得到连接对象 /// /// public static SqlConnection GetConn() { SqlConnection sqlconn = null; sqlconn = new SqlConnection(connectionStr); return sqlconn; } /// /// 查询操作 /// /// /// public static DataTable GetDataTable(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn)) { ge(sp); DataTable dt = new DataTable(); (dt); return dt; } } } /// /// 增删改操作 /// /// sql语句 /// 执⾏后的条数 public static int ExecuteNonQuery(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlCommand cmd = new SqlCommand(sql, conn)) { ge(sp); int i = eNonQuery(); return i; } } } /// /// 执⾏⼀条SQL语句,返回⾸⾏⾸列 /// /// sql语句 /// ⾸⾏⾸列 public static object ExecuteScalar(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { (); using (SqlCommand cmd = new SqlCommand(sql, conn)) { ge(sp); return eScalar(); } } } }} 创建sqlBulkCopyHelper/// /// SqlBulkCopy 批量更新数据 /// /// 数据集 /// 临时表创建字段 /// 更新语句 public static void BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql) { eConfiguration(rRoamingAndLocal); using (var conn = new SqlConnection(tionStrings["DefaultConnection"].ConnectionString)) { using (var command = new SqlCommand("", conn)) { try { (); //数据库并创建⼀个临时表来保存数据表的数据 dText = (" CREATE TABLE #TmpTable ({0})", crateTemplateSql); eNonQuery(); //使⽤SqlBulkCopy 加载数据到临时表中 using (var bulkCopy = new SqlBulkCopy(conn)) { foreach (DataColumn dcPrepped in s) { (Name, Name); } pyTimeout = 660; ationTableName = "#TmpTable"; oServer(dataTable); (); } // 执⾏Command命令 使⽤临时表的数据去更新⽬标表中的数据 然后删除临时表 dTimeout = 300; dText = updateSql; eNonQuery(); } finally { (); } } } } Program代码/// /// 更新表数据 /// public static void update() { String sqlstring = @""; tch stopwatch = new Stopwatch(); (); // 开始监视代码运⾏时间 DataTable dt = aTable(sqlstring); (); // 停⽌监视 ine("执⾏查询sql⽤时：" + econds + "秒,共查询到：" + + "⾏"); String updateSql = @"Merge into Table AS T
Using #TmpTable AS S
ON (_no = _no and _code = _code )WHEN MATCHED
THEN UPDATE SET T.[qty]=S.[qty],T.[total_amount]=S.[total_amount];"; String crateTemplateSql = @" [order_no] [varchar](32) NULL, [qty] [int] NULL, [total_amount] [decimal](18, 2) NULL, [item_code] [varchar](32) NULL,"; for (int i = 0; i < ( + 10000 - 1) / 10000; i++) { tch stopwatch2 = new Stopwatch(); (); dateData(erable().Skip(i * 10000).Take(10000).CopyToDataTable(), crateTemplateSql, updateSql); (); ine("更新第" + (i + 1) + "次耗时：" + econds + "秒,剩余" + (( + 10000 - 1) / 10000 - i - 1) + "次"); } ne(); } 1.更新的时候，datatable数据量过⼤内存不够⽤，这⾥是分了⼀下页。 2.还需要注意的就是sqlBulkCopy在使⽤的时候，视图或者是源表的字段⼤⼩写、类型必须与⽬标表⼀致。 3. Merge，使⽤merger语句可以将插⼊、更新、删除合并成⼀句，完成存在就更新不存在就插⼊的需求。
发布评论