批量数据插入表(SQL和代码)(DataTabel到表)
一、代码批量插入
实现1:
代码批量(DataTable)插入表,注意:DataTable 列名必须和数据库表列名大小写一致,表名和DataTable表名相同;
public bool SqlBulkCopyByDatatable(DataTable dt,ref string emsg) { try { SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(SqlHelper.connStr, SqlBulkCopyOptions.UseInternalTransaction); sqlbulkcopy.DestinationTableName = dt.TableName; foreach (DataColumn dc in dt.Columns) { sqlbulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } sqlbulkcopy.WriteToServer(dt); return true; } catch (Exception ex) { emsg = ex.ToString(); return false; } }
实现2
public void InsertRecord(DataTable dt) { SqlBulkCopyColumnMapping CopyColumnMapping1 = new SqlBulkCopyColumnMapping("code", "code"); SqlBulkCopyColumnMapping CopyColumnMapping2 = new SqlBulkCopyColumnMapping("type", "type"); SqlBulkCopyColumnMapping CopyColumnMapping3 = new SqlBulkCopyColumnMapping("palletno", "palletno"); SqlBulkCopyColumnMapping CopyColumnMapping4 = new SqlBulkCopyColumnMapping("set_label", "set_label"); SqlBulkCopyColumnMapping CopyColumnMapping5 = new SqlBulkCopyColumnMapping("paedl_ship_limit", "paedl_ship_limit"); SqlBulkCopyColumnMapping CopyColumnMapping6 = new SqlBulkCopyColumnMapping("flag", "flag"); SqlBulkCopyColumnMapping CopyColumnMapping7 = new SqlBulkCopyColumnMapping("qty", "qty"); SqlBulkCopyColumnMapping CopyColumnMapping8 = new SqlBulkCopyColumnMapping("custid", "custid"); SqlBulkCopyColumnMapping CopyColumnMapping9 = new SqlBulkCopyColumnMapping("compid", "compid"); SqlBulkCopyColumnMapping CopyColumnMapping10 = new SqlBulkCopyColumnMapping("stockid", "stockid"); SqlBulkCopyColumnMapping CopyColumnMapping11 = new SqlBulkCopyColumnMapping("planid", "planid"); SqlBulkCopyColumnMapping CopyColumnMapping12 = new SqlBulkCopyColumnMapping("houseid", "houseid"); SqlBulkCopyColumnMapping CopyColumnMapping13 = new SqlBulkCopyColumnMapping("JIT_SHIP_LIMIT", "JIT_SHIP_LIMIT"); SqlBulkCopyColumnMapping CopyColumnMapping14 = new SqlBulkCopyColumnMapping("outinflag", "outinflag"); SqlBulkCopyColumnMapping CopyColumnMapping15 = new SqlBulkCopyColumnMapping("WH_ENTER_TIME", "WH_ENTER_TIME"); SqlBulkCopyColumnMapping CopyColumnMapping16 = new SqlBulkCopyColumnMapping("isInternal", "isInternal"); using (SqlBulkCopy sbc = new SqlBulkCopy(sqlSerH.connStr, SqlBulkCopyOptions.Default)) { sbc.ColumnMappings.Add(CopyColumnMapping1); sbc.ColumnMappings.Add(CopyColumnMapping2); sbc.ColumnMappings.Add(CopyColumnMapping3); sbc.ColumnMappings.Add(CopyColumnMapping4); sbc.ColumnMappings.Add(CopyColumnMapping5); sbc.ColumnMappings.Add(CopyColumnMapping6); sbc.ColumnMappings.Add(CopyColumnMapping7); sbc.ColumnMappings.Add(CopyColumnMapping8); sbc.ColumnMappings.Add(CopyColumnMapping9); sbc.ColumnMappings.Add(CopyColumnMapping10); sbc.ColumnMappings.Add(CopyColumnMapping11); sbc.ColumnMappings.Add(CopyColumnMapping12); sbc.ColumnMappings.Add(CopyColumnMapping13); sbc.ColumnMappings.Add(CopyColumnMapping14); sbc.ColumnMappings.Add(CopyColumnMapping15); sbc.ColumnMappings.Add(CopyColumnMapping16); //数据库表名 sbc.DestinationTableName = "lms_paedl_interface"; sbc.WriteToServer(dt); }
二、数据库SQL批量插入
Oracle
//Oracle批量插入 dual为系统参数
//Oracle批量插入 dual为系统参数 insert into Test(name,Value) select '1','1' from dual union all select '2','2' from dual union all select '3','3' from dual union all select '3','3'from dual
SqlServer 批量插入
insert into ys (Name,Value) values ('1','1'), ('2','2'), ('2','2'), ('3','3'), ('4','4')
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET开发智能桌面机器人:用.NET IoT库编写驱动控制两个屏幕
· 用纯.NET开发并制作一个智能桌面机器人:从.NET IoT入门开始
· 一个超经典 WinForm,WPF 卡死问题的终极反思
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
· 开箱你的 AI 语音女友「GitHub 热点速览」
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(二):用.NET IoT库
· 几个自学项目的通病,别因为它们浪费了时间!
· C#钩子(Hook) 捕获键盘鼠标所有事件 - 5分钟没有操作,自动关闭 Form 窗体
· 单点认证(SSO)方案调研总结