一种优化的ADO.NET通用数据库操纵引擎

2018-10-16 09:19:04

福建工程学院学报 2018年4期

(福建工程学院信息科学与工程学院，福建福州 350118)

在开发一款企业级应用程序，不管是桌面应用程序、WEB应用程序还是移动端APP应用程序，对数据库的封装访问都是其底层的核心模块之一。随着C#语言的流行，越来越多的应用程序在与数据库的通信上采用ADO.NET技术。ADO.NET是一组允许和不同类型的数据源以及数据库进行交互的面向对象类库，采用XML作为通用的数据传送格式，在跨异种环境通信情况下具有极好的互操作性。其次，ADO.NET具有十分强大的可伸缩性，它采用非连接数据集，数据在本地缓存，等要更新的时候再回传数据库，这使得WEB应用程序可以为更多的用户同时提供服务。本文在ADO.NET的基础上，实现了一种通用的数据库操纵引擎。

1 ADO.NET的体系结构

ADO.NET是微软公司新一代.NET数据库的访问架构，它是数据库应用程序和数据源之间沟通的桥梁，主要提供一个面向对象的数据访问架构[1]。ADO.NET主要由Data Provider和DataSet两大组件组成[2]。.NET Data Provider针对不同类型的数据源，细分为SQL Server .NET Data Provider，用于Microsoft SQL Server数据源；OLE DB .NET Data Provider用于OLE DB公开的数据源；ODBC .NET Data Provider用于ODBC公开的数据源；Oracle .NET Data Provider用于Oracle数据源。

NET Data Provider包含4个核心对象，Connection对象用于建立与数据源的连接；Command对象用于执行数据源操纵命令；DataReader对象用于从数据源返回一个仅向前的只读数据流；DataAdapter对象用于充当DataSet对象与数据源之间的桥梁。DataSet对象是创建在内存中的集合对象，不依赖于数据库的独立数据集，即使数据库连接断开，它依然可用。ADO.NET的体系架构如图1所示。

图1 ADO.NET体系架构Fig.1 ADO.NET architecture

2 ADO.NET的改进研究

虽然ADO.NET具有互操作性、可伸缩性、性能高等优点，但是也存在以下不足：

(1)每次访问数据库(如：SQL Server数据库)都要创建SqlConnection对象，创建一个SqlCommand对象并编写具体的SQL语句,而SQL语句是程序员最容易拼错的地方，并且很多程序员会因一时疏忽把表单的内容直接拼接到SQL语句，这样会造成系统存在SQL注入式攻击漏洞，攻击者可以把恶意的SQL命令插入到表单从而让服务器执行恶意的SQL语句。

(2)系统中会到处分散着对ADO.NET相关类的调用和数据访问的代码，这对调试代码增加了许多工作量。

(3)没有提供数据库操纵日志记录功能，要记录一些数据库的重要操作，如delete或update操作，在每次调用时必须添加日志记录代码。

针对以上不足，对ADO.NET二次封装进行改进研究。如文献[3-4]提出了将ADO.NET与多层模式相融合的方法，在数据访问层构建独立的SQL引擎组件，把对ADO.NET相关类的调用集中在数据访问层，方便后期的代码维护。但是它并不能实现自动构造SQL语句，也不能有效防止SQL注入式攻击。文献[5]提出的ADO.NET通用数据库引擎，虽然可以自动构造SQL语句，但是并没有提供对事务处理的支持，而且Update方法只有一个Parameters参数，当要更新的域字段的名称和条件字段域的名称一样的时候，无法实现。

3 优化的ADO.NET通用数据库操纵引擎的设计

针对以上不足，本文提出一种优化的ADO.NET通用数据库操纵引擎OptimizedDbEngine，提供以下6个方面的功能：(1)能够自动构造SQL语句；(2)每次执行对数据库的操作，自动创建数据库的连接，执行完后自动释放连接对象；(3)以统一的方式从数据库获取检索结果，并支持分页；(4)支持数据库事务处理；(5)能有效防止SQL注入式攻击；(6)提供数据库操作日志记录。

OptimizedDbEngine主要有3个类： BaseDAO(自动构造SQL语句并向业务层提供数据库操纵接口)、DBUtil(向数据库发送操纵命令)、DataSetForPage(支持分页的数据集)。它们的关系如图2所示。

3.1 BaseDAO类

BaseDAO类向业务层提供Insert和InsertWithTrans、Update和UpdateWithTrans、Delete和DeleteWithTrans、Select方法实现对数据库的增、改、删、查操作，其中InsertWithTrans、UpdateWithTrans和DeleteWithTrans方法是带事务处理。Select方法用于执行检索操作，是重载方法，第2个Select方法是带分页的检索。RunProcedure和RunProcedureWithTrans方法用于执行存储过程，后者带事务处理。BeginTransaction、CommitTransaction和RollbackTransaction方法用于控制事务。Log方法是一个保护方法，用于记录数据库操纵的日志，日志的记录主要用到开源的log4net.dll组件。

对数据库的增、改、删、查的具体SQL语句是自动构造出来，不需要程序员在业务层中编写。构造SQL语句的原理是基于htFieldValue和htParamValue这两个Hashtable类型的字段。htFieldValue用于存储要更新的字段域的名称和值的键值对；htParamValue用于存储条件字段域的名称和值的键值对。其中Update、Select方法需用到htFieldValue和htParamValue两个字段，Insert方法只需用到htFieldValue字段，Delete方法只需用到htParamValue字段。因为SQL中update的语法格式为：update tableName set field_column1=field_value1,field_column2=field_value2,... where param_column1=param_value1 and param_column2=param_value2 and ...;所以构造update语句的重点在于自动构造出set子句和where子句，这两个字符串分别由若干和键值对组成，可从htFieldValue和htParamValue自动生成。insert语句的语法格式为：insert into tableName(field_column1,field_column2,…) values (field_value1,field_value2,…);语句里的键值对可从htFieldValue自动生成。delete语句的语法格式为：delete from tableName where param_column1=param_value1 and param_column2=param_value2 and ...;语句里的键值对可从htParamValue自动生成。select语句的语法格式为：select field_column1,field_column2,… from tableName where param_column1=param_value1 and param_column2=param_value2 and ...;语句里的键值对可从htParamValue自动生成，列表可从htFieldValue自动生成。

下面给出Update方法中自动构造SQL语句的具体步骤以及核心代码，在其它方法中构造SQL语句类似。

图2 OptimizedDbEngine的类图

(1)遍历存储在htFieldValue中的键集合，构造set子句，具体代码如下。

String sqlSet = "";

foreach (DictionaryEntry de in htFieldValue)

{

if (!(String.IsNullOrEmpty(sqlSet))) sqlSet = sqlSet + ",";

sqlSet += de.Key + "=" + "@f_" + de.Key;

}

这里的参数名称加个前缀“f_”是为了区分htFieldValue和htParamValue中的参数，因为在htFieldValue和htParamValue中可能存在相同名称的参数。

(2)遍历存储在htParamValue中的键集合，构造where子句，具体代码如下。

String sqlWhere = "";

foreach (DictionaryEntry de in htParamValue)

{

if (!(String.IsNullOrEmpty(sqlWhere))) sqlWhere = sqlWhere + " and ";

sqlWhere += de.Key + "=" + "@" + de.Key;

}

(3)拼接set子句和where子句，构造出完整的update语句，具体代码如下。String sql = "update " + tableName + " set " + sqlSet + " where " + sqlWhere ;

(4)然后调用DBUtil类的ExecuteSql方法，传递的参数为(sql, htFieldValue, htParamValue)，接着调用Log方法记录该操作日志，最后调用Clear方法清空存储在htFieldValue和htParamValue的所有元素。

从上面Update方法中构造SQL语句的具体代码中可以看出，最后传递给DBUtil类的ExecuteSql方法的sql语句是采用参数的形式，并没有把参数具体的值(具体的值往往是接收用户表单提交的数据，存在SQL注入式攻击风险)直接拼接在SQL语句中，这样可以有效防止SQL注入式攻击。并且Update方法最后会调用Log方法进行日志记录，不需要程序员在业务层显式编写代码进行数据库操纵日志记录。

3.2 DBUtil类

数据库通信和操纵的具体实现是封装在DBUtil类中。DBUtil类中有一个属性：connectionString，读取存储在配置文件中的数据库连接字符串。并向BaseDAO类提供ExecuteSql、Query、RunProcedure方法，这3个方法执行时会根据属性connectionString的值自动创建用于数据库连接的SqlConnection对象，并在方法返回前会断开数据库连接。

(1)ExecuteSql方法，主要用来执行增、删、改操作，是1个重载方法。第1个ExecuteSql方法接受3个参数，第1个参数strSql是要执行数据库操纵的带参数的SQL语句；第2个参数htFieldValue是1个Hashtable类的对象，用于存储操作字段域的名称和值的键值对；第3个参数htParamValue也是1个Hashtable类的对象，用于存储条件字段域的名称和值的键值对。方法内会先创建1个SqlCommand对象；接着调用PrepareCommand私有方法，PrepareCommand方法主要根据htFieldValue和htParamValue参数设置SqlCommand对象的Parameters属性；然后执行SqlCommand对象的ExecuteNonQuery方法，完成数据库的操纵。

第2个ExecuteSql方法共接受5个参数，多接受1个SqlConnection对象的参数和1个SqlTransaction对象的参数，用于实现带事务处理的操作。

(2)Query方法，主要用来执行查询操作，是1个重载方法。第1个Query方法接受两个参数，第1个参数strSql是要执行查询操作的SQL语句；第2个参数htParamValue是1个Hashtable类的对象，用于存储查询条件字段域的名称和值的键值对。方法内会先创建1个SqlDataAdapter对象，再创建一个SqlCommand对象作为SqlDataAdapter对象的SelectCommand属性；接着调用PrepareCommand私有方法，PrepareCommand方法会根据htParamValue参数设置SqlCommand对象的Parameters属性；然后执行SqlDataAdapter对象的Fill方法，完成数据库的查询，返回1个查询结果集DataSet对象。

第2个Query方法实现分页查询，共接受5个参数，多接受了3个整型参数pageSize、pageIndex、rowCount，pageSize参数用于指定每页记录数，pageIndex参数用于指定页码，rowCount是一个输出参数，用于返回总共有多少条记录。分页检索的原理就是自动构造出如下SQL语句。

select top pageSize * from (select row_number() rowNum,field_column1,field_column2,... from tableName where param_column1=param_value1,param_column2=param_value2,...)t1 where rowNum>((pageIndex-1)*pageSize) order by rowNum。

(3)RunProcedure方法，用来执行存储过程，是1个重载方法。第1个RunProcedure方法接受3个参数，第1参数storedProcName用于指定存储过程的名称，第2个参数htParamValue是一个Hashtable类的对象，用于存储参数的名称和值的键值对，第3个参数rowsAffected是1个输出参数，用于返回受影响的行数。

第2个RunProcedure方法共接受5个参数，多接受一个SqlConnection对象的参数和一个SqlTransaction对象的参数，用于实现带事务处理的存储过程。

3.3 DataSetForPage类

DataSetForPage类是对ADO.NET体系架构中的DataSet对象的进一步封装，用于接收带分页查询的结果，有5个可以读写的属性，_ds是1个DataSet对象，用于存储数据结果集；_recordCount是1个整型对象，用于存储总记录数；_pageCount是1个整型对象，用于存储总页数；_pageIndex是1个整型对象，用于存储当前页码；_pageSize是1个整型对象，用于存储每页记录数。

3.4 在业务层使用OptimizedDbEngine

通用数据库引擎以DLL的形式发布，在业务层只要引用OptimizedDbEngine.dll即可。下面给出1个简单的应用示例。如在数据库里有一张表student，它有3个字段num(学号)，name(姓名)，age(年龄)。现要往student表插入1条记录(1001，"张三"，20)，具体的代码如下：

BaseDAO dao = new BaseDAO();

dao.TableName="student";

dao.SetFieldValue("num",1001);

dao.SetFieldValue("name","张三");

dao.SetFieldValue("age",20);

dao.Insert();

如要把学号为1001的学生记录的年龄修改为21，具体的代码如下：

dao.SetFieldValue("age",21);

dao.SetParamValue("num",1001);

dao.Update();

从以上示例代码可以看出，使用OptimizedDbEngine.dll操纵数据库，完全屏蔽了底层的数据库访问技术，大大降低了应用程序访问数据库的复杂性。

4 结语

数据库访问在绝大多数应用系统里占据着举足轻重的地位，而编写SQL语句是一项繁琐而又重复的工作。OptimizedDbEngine提供了简单易用的接口供应用程序业务层调用，大大降低了数据库访问的门槛，即使从来没用过ADO.NET的程序员，也可以快速开发出高质量的数据库应用程序。OptimizedDbEngine.dll在笔者的多个实际项目中使用并取得很好的效果。