批量数据插入表(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')

 



posted @   博客YS  阅读(250)  评论(0编辑  收藏  举报
编辑推荐:
· .NET开发智能桌面机器人:用.NET IoT库编写驱动控制两个屏幕
· 用纯.NET开发并制作一个智能桌面机器人:从.NET IoT入门开始
· 一个超经典 WinForm,WPF 卡死问题的终极反思
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
阅读排行:
· 开箱你的 AI 语音女友「GitHub 热点速览」
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(二):用.NET IoT库
· 几个自学项目的通病,别因为它们浪费了时间!
· C#钩子(Hook) 捕获键盘鼠标所有事件 - 5分钟没有操作,自动关闭 Form 窗体
· 单点认证(SSO)方案调研总结
点击右上角即可分享
微信分享提示