一个帮助MSSQL数据库实现数据批量插入、修改和删除的组件DBHub

xiangcns

已于 2022-03-02 08:55:27 修改

阅读量583

点赞数

分类专栏：数据库 DBHub组件文章标签： sql 数据库 c# 批处理 mssql

于 2020-12-29 00:49:17 首次发布

本文链接：https://blog.csdn.net/xiangcns/article/details/111878107

版权

数据库同时被 2 个专栏收录

19 篇文章 1 订阅

订阅专栏

DBHub组件

4 篇文章 1 订阅

订阅专栏

特别提醒

数据无价，Bug无情，请先找测试数据确认学会正确用法，以免发生删库跑路等意外。

一、概述

1.1 背景

刚工作的时候，作为萌新，干的最多的就是增删改查，那个时候叫CRUD Boy，天天拼接什么insert，update，写得很烦躁，特别是后来遇到很多复杂的操作场景，比如某个表需要同时进行N个操作：插入X行、同时更新Y行、同时删除Z行，这么多事情需要拼接多少sql语句啊，那个时候NET的ORM也不好用，而且批处理能力弱，于是萌生了一个想法：能否写一个方法一次性完成全部处理动作，于是DBHub就这样诞生了。

1.2 介绍

DBHub是帮助SqlServer数据库实现数据批量插入、修改和删除的组件。DBHub的作用是为了方便地将数据集（以下简称dt）批量写入或更新到数据表，使用者只需要把数据集按照要求组装起来，作为参数传递给DBHub的方法，剩下的事情由DBHub全部搞定。

DBHub可以：

将所有对数据表的增删改操作通过对dt的控制一次性更新到数据表；
对dt与数据表重叠的数据可以采用略过或者更新的方式自由控制；
判断送入的dt与数据表结构是否一致，提示缺少的字段或者多出的字段；
判断主键是否一致；

1.3 开源

这是我为开源世界贡献的第一个组件，该组件已经应用到我的工程物料管理系统中。原本打算发到github上，打开github提示我所在的地区正在受到美国制裁，拉倒，改发到gitee上。

1.4 使用范围

目前只支持MSSQL。

1.5 效果演示

How DBHub work

二、安装教程

开发项目直接引用类文件即可

三、成员方法

方法1：

/// <summary>
/// 数据导入
/// </summary>
/// <param name="conn">连接对象</param>
/// <param name="dt">数据集</param>
/// <param name="schemaName">架构名</param>
/// <param name="tableName">表名</param>
/// <param name="DataKeyName">关键字名数组</param>（判断数据项的唯一性用）
/// <param name="dup">对重复数据的处理方式</param>
/// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
public string[] DataImport(SqlConnection conn, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupType)

参数名称	参数类型	说明
conn	SqlConnection	连接对象
dt	DataTable	数据集
schemaName	string	架构名
tableName	string	表名
sqlfilter	string	表的查询条件，这个查询条件的作用是从数据表中查询一个参照系ds，然后用dt与ds做比对，这样的好处是缩小了参照系的尺寸，提高了dt写入数据表的错误（试想如果数据表有百万行数据，而你此时dt打算写入10行数据，却要对一百万行进行遍历检查是否重复，这样的操作命中率太低）
KeyColumnName	string[]	主键数组（因为有的表主键可能不止一个字段）
dupType	DuplicateProcessMode	对重叠数据的处理方式：Update 更新重复数据，Ignore 忽略重复数据

方法2

/// <summary>
/// 数据更新
/// </summary>
/// <param name="conn"></param>
/// <param name="dt"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="sqlfilter"></param>
/// <param name="KeyColumnName"></param>
/// <param name="dupMode">已经存在项处理方式</param>
/// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
public string[] DataUpdate(SqlConnection conn, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupMode)

参数名称	参数类型	说明
conn	SqlConnection	连接对象
dt	DataTable	数据集
schemaName	string	架构名
tableName	string	表名
sqlfilter	string	表的查询条件，这个查询条件的作用是从数据表中查询一个参照系ds，然后用dt与ds做比对，这样的好处是缩小了参照系的尺寸，提高了dt写入数据表的错误（试想如果数据表有百万行数据，而你此时dt打算写入10行数据，却要对一百万行进行遍历检查是否重复，这样的操作命中率太低）。特别注意在使用DataUpdate方法时，dt没有的数据会从参照系中删除，如果你想批量删除数据，切记要用sqlfilter参数查询出参照系，而不是以整个数据表作为参照系，否则数据表会只剩下dt的数据。
KeyColumnName	string[]	主键数组（因为有的表主键可能不止一个字段）
dupType	DuplicateProcessMode	对重叠数据的处理方式：Update 更新重复数据，Ignore 忽略重复数据

严重警告:
特别注意在使用DataUpdate方法时，dt没有的数据会从参照系中删除，如果你想批量删除数据，切记要用sqlfilter参数查询出参照系，而不是以整个数据表作为参照系，否则数据表会只剩下dt的数据。如果你没有搞懂这段话的意思，切记不可使用DataUpdate方法。

方法3

/// <summary>
/// 数据导入
/// </summary>
/// <param name="conn"></param>
/// <param name=" sqltrans"></param>
/// <param name="dt"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="sqlfilter"></param>
/// <param name="KeyColumnName"></param>
/// <param name="dupMode">已经存在项处理方式</param>
/// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
public string[] DataImport(SqlConnection conn, SqlTransaction sqltrans, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupType)

参数名称	参数类型	说明
conn	SqlConnection	连接对象
sqltrans	SqlTransaction	事务对象
dt	DataTable	数据集
schemaName	string	架构名
tableName	string	表名
sqlfilter	string	表的查询条件，这个查询条件的作用是从数据表中查询一个参照系ds，然后用dt与ds做比对，这样的好处是缩小了参照系的尺寸，提高了dt写入数据表的错误（试想如果数据表有百万行数据，而你此时dt打算写入10行数据，却要对一百万行进行遍历检查是否重复，这样的操作命中率太低）
KeyColumnName	string[]	主键数组（因为有的表主键可能不止一个字段）
dupType	DuplicateProcessMode	对重叠数据的处理方式：Update 更新重复数据，Ignore 忽略重复数据

方法4

/// <summary>
/// 数据更新
/// </summary>
/// <param name="conn"></param>
/// <param name=" sqltrans"></param>
/// <param name="dt"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="sqlfilter"></param>
/// <param name="KeyColumnName"></param>
/// <param name="dupMode">已经存在项处理方式</param>
/// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
public string[] DataUpdate(SqlConnection conn, SqlTransaction sqltrans, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupMode)

参数名称	参数类型	说明
conn	SqlConnection	连接对象
sqltrans	SqlTransaction	事务对象
dt	DataTable	数据集
schemaName	string	架构名
tableName	string	表名
sqlfilter	string	表的查询条件，这个查询条件的作用是从数据表中查询一个参照系ds，然后用dt与ds做比对，这样的好处是缩小了参照系的尺寸，提高了dt写入数据表的错误（试想如果数据表有百万行数据，而你此时dt打算写入10行数据，却要对一百万行进行遍历检查是否重复，这样的操作命中率太低）。特别注意在使用DataUpdate方法时，dt没有的数据会从参照系中删除，如果你想批量删除数据，切记要用sqlfilter参数查询出参照系，而不是以整个数据表作为参照系，否则数据表会只剩下dt的数据。
KeyColumnName	string[]	主键数组（因为有的表主键可能不止一个字段）
dupType	DuplicateProcessMode	对重叠数据的处理方式：Update 更新重复数据，Ignore 忽略重复数据

严重警告：
同方法2

四、使用方法

4.1 dt的构造方法

//构造dt的方式1
DataTable dt= new DataTable();  //询价单材料量表写入格式
bulkDataTable.Columns.AddRange(new DataColumn[]{ 
new DataColumn("PJGUID",typeof(System.Guid)), 
new DataColumn("PSGUID",typeof(System.Guid)), 
new DataColumn("KSGUID",typeof(System.Guid)), 
new DataColumn("采购包编号",typeof(System.String)), 
new DataColumn("修改者",typeof(string)), 
new DataColumn("修改时间",typeof(DateTime))});

//构造dt的方式2
DataTable dt = new DataTable();
dt.Columns.Add("PLGUID");
dt.Columns.Add("LOCKey");
dt.Columns.Add("项目ID");
dt.Columns.Add("PSGUID");
dt.Columns.Add("XDGUID"); 
dt.Columns.Add("箱单号");
dt.Columns.Add("放行单");

4.2、不带事务的用法

//数据库连接对象（用于dbpc.GetConnectionString()获取连接字符串）
DatabaseMaterialControl dbpc = new DatabaseMaterialControl();
//声明conn对象
SqlConnection conn = new SqlConnection(dbpc.GetConnectionString());
//声明DBHub对象
DBHub.DBHub imp = new DBHub.DBHub();
string[] keyColumnName = { "LOCKey" }; //数据表的主键
string[] answerStr = new string[2];    //返回值（返回值是一个数组）
//调用DataImport，将dt写入表：合同箱单信息表
answerStr = imp.DataImport(conn, dt, "Material", "合同箱单信息表", "WHERE 项目ID='" + ProjectID.ToString() + "'", keyColumnName, DBHub.DuplicateProcessMode.Update);

4.3、带事务的用法

项目数据库连接类 dbpc = new 项目数据库连接类();
SqlConnection conn = new SqlConnection(dbpc.GetConnectionString());
using (conn)
{
   //事务开始
   if (conn.State != ConnectionState.Open)
   {
       conn.Open(); //打开连接
   }
SqlTransaction sqltrans = conn.BeginTransaction(); //事务对象
try 
{
      DBHub.DBHub impSummary = new DBHub.DBHub();
      string[] keyColumnNameSummary = { "ISGUID", "领料单号" };
      string[] answerStrSummary = new string[2];
      string messageBULKSummary = string.Empty;
      answerStrSummary = impSummary.DataImport(conn, sqltrans, dtSummary, "Material", "出库概要信息表", "WHERE 项目ID='" + ProjectID + "'", keyColumnNameSummary, DBHub.DuplicateProcessMode.Ignore);

      DBHub.DBHub impDetails = new DBHub.DBHub();
      string[] keyColumnNameDetails = { "LOCKey" };
      string[] answerStrDetails = new string[2];
      string messageBULKDetails = string.Empty;
      answerStrDetails = impDetails.DataImport(conn, sqltrans, dtDetails, "Material", "出库明细信息表", "WHERE 项目ID='" + ProjectID + "'", keyColumnNameDetails, DBHub.DuplicateProcessMode.Update);
if (answerStrSummary[0] == "[1]" && answerStrDetails[0] == "[1]")
     {
        iRel = "Done";
        messageBULK = "success";
     }
     else if (answerStrSummary[0] != "[1]")
     {
        iRel = "L7001";
        messageBULK = "出库单概要信息写入错误：" + answerStrSummary[0] + answerStrSummary[1];
     }
     else if (answerStrDetails[0] != "[1]")
     {
     iRel = "L7002";
     messageBULK = "出库单明细信息写入错误：" + answerStrDetails[0] + answerStrDetails[1];
     }

      sqltrans.Commit();
   }
   catch (Exception e)
   {
       iRel = "L7003";
       sqltrans.Rollback();
       messageBULK = e.Message;
   }
   finally
   {
      if (conn.State != ConnectionState.Closed)
      {
            conn.Close(); //关闭连接
      }
   }
}

五、错误代码

 *【错误代码】
 * 0    参数dt没有数据(0行)
 * 1    执行成功
 * 31   KeyColumnName参数中的主键列名在数据库目标表中不存在
 * 32   参数dt表结构与数据库表结构不一致，dt缺少字段[value] 
 * 33   参数dt表结构与数据库表结构不一致，dt多出字段[value] 
 * 90   来自vs debug 的try/cacth报错
 * 98   KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常
 * 99   不存在的错误代码，看到这个错误代码等于见到鬼