一个通用数据库操作组件DBUtil(c#)、支持SqlServer、Oracle、Mysql、postgres、SQLITE-武穆逸仙

这是一个.net 下操作数据库(结构数据库)的工具类，支持sqlserver、oracle、mysql、postgres、sqlite、access 等常见数据库。

注意：它并不是一个 orm 工具(常见的 orm 框架如：EF、Dapper 等)。

2.1 引入 DBUtil 依赖

1. 首先打开 vs(推荐vs2019)，新建控制台应用程序(.net framework)

2. 添加依赖方法 1：打开工具>NuGet 包管理器>程序包管理器控制台，输入：

Install-Package DBUtil -Version 1.0.0

3. 添加依赖方法 2：使用可视化的 nuget 管理窗口搜索“DBUtil”，选择安装即可！

2.2 准备数据库

自行准备吧。

2.3 增删改查代码

    
    DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB("Data Source=.;Initial Catalog=JACKOA;User ID=sa;Password=xx;","SQLSERVER");


    Console.WriteLine(iDb.GetFirstColumnString("select Name from SysUser"));


    Console.WriteLine(iDb.ExecuteSql("updatetest set CaseNo=CaseNo+'e'"));


    Console.WriteLine(iDb.ExecuteSql("update test set CaseNo=@case",newIDataParameter[] { iDb.CreatePara("case","123") }));


     Hashtable ht = newSystem.Collections.Hashtable();
     ht.Add("CaseNo", "456");
     ht.Add("Name", "ji");
     Console.WriteLine(iDb.AddData("test",ht));


     Hashtable ht2 = newSystem.Collections.Hashtable();
     ht2.Add("Name", "jiko");
     Console.WriteLine(iDb.UpdateData("test",ht2," and CaseNo='123'"));


     Console.WriteLine(iDb.DeleteTableRow("test"," and id=11"));


      Console.WriteLine(iDb.GetDataSet("select * from sysuser").Tables[0].Rows.Count);

在这个组件设计中，所有的数据库操作方法都被集成在了一个对象上：IDbAccess。它是一个接口，定义了公共的数据库访问方法，如：增删改查、事务控制等。具体的实现由 SqlServerIDbAccess、MySqlIDbAccess、PostgreSqlIDbAccess、OracleIDbAccess、AccessIDbAccess、SQLiteIDbAccess 等实现，它们分别对应着一种数据库。

在所有的数据库操作之前，都必须先创建 IDbAccess对象，创建的方法如下：

DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB("Data Source=.;Initial Catalog=JACKOA;User ID=sa;Password=xx;","SQLSERVER");

你所需的参数有两个：

1. 数据库的类型字符串

SQLSERVER、ORACLE、MYSQL、POSTGRESQL、ACCESS、SQLITE

2. 数据库连接字符串

参考以下示例：

SQLSERVER: Data Source=.;Initial Catalog=JACKOA;User ID=sa;Password=xx;
ORACLE: Data Source=ORCLmyvm2;Password=sys123;User ID=sys;DBA Privilege=SYSDBA;
MYSQL: Data Source=localhost;Initial Catalog=test;User ID=root;Password=xxxx;
POSTGRESQL: Server=localhost;Port=5432;UserId=postgres;Password=xxxx;Database=test
ACCESS: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:workMultiplan.mdb;
ACCESS: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersAdministratorDesktopdemo.accdb;
SQLITE: Data Source=f:demo.db;

3. sqlite 数据库

sqlite 数据库存储是单个文件存储的，所以在访问之前你需要先创建它，参照如下代码：


DBUtil.IDBFactory.CreateSQLiteDB("d:\demo.db");

string str = DBUtil.IDBFactory.GetSQLiteConnectionString("d:\demo.db");

DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB(str, "SQLITE");

iDb.ExecuteSql(@"
create table test(
id int primary key,
name varchar(50)
);
insert into test values(1,'张三');
insert into test values(2,'李四');
");

string name = iDb.GetFirstColumnString("select name from test");
Console.WriteLine(name);

四、增删改查方法

4.1 增加数据（AddData）

DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB(@"Data Source=d:demo.db;", "SQLITE");
iDb.ExecuteSql(@"
create table test2(
id int primary key,
name varchar(50),
createtime timestamp,
largefield blob
)");
Hashtable ht = new Hashtable();
 ht.Add("id", 1);
ht.Add("name", "李四");
 ht.Add("createtime", DateTime.Now);
ht.Add("largefield", System.Text.Encoding.UTF8.GetBytes("我的密码是：xxxxxx"));
iDb.AddData("test2", ht);
Console.WriteLine(iDb.GetFirstColumnString("select name from test2"));
Console.WriteLine(iDb.GetFirstColumnString("select createtime from test2"));            Console.WriteLine(System.Text.Encoding.UTF8.GetString(iDb.GetFirstColumn("select largefield from test2") as byte[]));
Console.WriteLine("ok");
Console.ReadLine();

4.2 删除数据（DeleteTableRow）

iDb.DeleteTableRow("test2", "and id=1");

4.3 更新数据（UpdateData）

Hashtable ht = new Hashtable();
ht.Add("name", "王五");
iDb.UpdateData("test2", ht, "and id=1");

4.4 更新或添加数据（UpdateOrAdd）

Hashtable ht = new Hashtable();
ht.Add("id", 1);
ht.Add("name", "王五");
iDb.UpdateOrAdd("test2", ht, "and id=1");

4.5 查询数据

4.5.1 获取第一个值

string str=iDb.GetFirstColumnString("select name from test2");
object obj= iDb.GetFirstColumnString("select largefield  from test2");

4.5.2 获取表


DataTable dt = iDb.GetDataTable("select * from test2");

DataSet ds = iDb.GetDataSet("select * from test2;select * from test2;");

4.6 参数化 sql 语句

iDbAccess 的大部分方法都是直接参数化查询的，参照如下代码：

DataTable dt = iDb.GetDataTable(string.Format("select * from test2 where name like {0}", iDb.paraPrefix + "name"), new IDbDataParameter[] {
      iDb.CreatePara("name","%小%")
 });

每个数据库的分页方法不同，比如：

sqlserver：top 分页、row_number() over()分页、fetch 分页

mysql：limit 分页

等等。。。

在这个组件中设计为：根据指定的查询语句和分页参数生成分页的查询语句，参照以下代码：

DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB(@"Data Source=localhost;Initial Catalog=imgserver2;User ID=root;Password=123456;", "MYSQL");
string selectSql = "select * from test2";
string orderSql = "order by id desc";
int pageSize = 10;
int pageIndex = 1;
string sqlFinal = iDb.GetSqlForPageSize(selectSql, orderSql, pageSize, pageIndex);
Console.WriteLine(sqlFinal);

iDb.BeginTrans();
IDbTransaction tran= iDb.tran;
iDb.Commit();
iDb.Rollback();
bool b = iDb.IsTran;

DBUtil.IDbAccess iDb = DBUtil.IDBFactory.CreateIDB(@"Data Source=localhost;Initial Catalog=imgserver2;User ID=root;Password=1234567;", "MYSQL");
DBUtil.Result res = iDb.OpenTest();
if (res.Success)
{
    Console.WriteLine("连接成功！");
}
else
{
    Console.WriteLine("连接失败：" + res.Data);
}
Console.WriteLine("ok");
Console.ReadLine();

默认情况下，每进行一次数据库的操作都会打开和关闭数据库。在密集的操作下这种方法并不推荐。

可以通过 IsKeepConnect 属性使数据的链接一直保持

iDb.IsKeepConnect = true;

string filter=iDb.GetDateFilter("end", "2016-12-1", "2017-01-01", true, false);

bool b=iDb.JudgeTableOrViewExist("test");
bool b2 = iDb.JudgeColumnExist("test", "id");

11.1 说明

数据库表的 ID 的生成是一个常见的问题，常见的有表字段设置为自增、设为序列、程序控制 ID 生成。下面分别说说这三种模式：

1) 表字段自增：在 sqlserver 中有个致命的缺陷就是一旦遇到数据迁移，那么这些 ID 都将重新生成，如果其他的表引用了这个 ID，后果可想而知。在 mysql 中有办法解决这个问题，但是这种办法最根本的缺陷在于：“不能在数据插入到表之前获取对应的 ID”，试想一下：如果你在插入数据之前还要用到这个 ID 做其他的工作该怎么办？

2) 序列法：在oracle中可以新建一个序列控制 ID 的生成（sqlserver 中好像也开始支持了），这种方法其实已经解决了绝大部分问题了，但是在每次生成 ID 之前你还是需要访问一遍数据库，如果需要批量生成的时候性能肯定受影响（不知道序列支不支持批量生成）。

3) 程序控制 ID 生成法：这个组件是用的这种方法生成 ID 的，它可以解决上述提到的问题。它的原理是：

你在程序中通过指定表名和字段名来获取 ID，程序中进行判断当前内存中是否缓存了这个 ID，如果缓存了 ID 的话就直接自增并返回，如果没有缓存 ID 的话就通过表名和字段名去数据库里面去查找最大的 ID，然后自增并返回。

优点：

1. ID 可以随时生成，不用非得向表中插入数据。

2. 在批量生成 ID 时，性能很高（完全碾压从数据库生成的）

3. 可以针对一个表的多个字段进行生成（一般用不到）

4. 兼容各种数据库，你不用再为 ID 配置各种生成策略

缺点：

1. 因为 ID 的生成缓存到了程序中，所以针对一张表必须保证只有一个服务器会进行 ID 的生成，否则会出现重复的 ID

注意：

有人可能已经注意到问题，分布式的 ID 是怎么生成（1 个数据库，多个服务器）？这里我提供一个思路，但是这个组件中并没有实现：针对不同的服务器预先设置 ID 的前缀，比如服务器 A 的 ID 生成从 1 开始，服务器 B 的 ID 生成从 100 0000 开始，这样就不会重叠了。如果担心数据量太大，那么你就不能在用 int 类型的 ID 了，你应该使用字符串唯一编号。这个组件中也实现了唯一编号的生成控制，原理和 ID 一样的都是在程序中缓存，只不过使用的时候要配置编号生成的规则…

11.2 扩展说明

这个组件默认使用的生成控制器是 SimpleIDSNOManager,如果需要扩展(比如使用 redis 控制生成等)可以自定义实现 IDSNOManager,并在应用程序启动时书写：IDBFactory. IDSNOManage= new 自定义实现();

11.3 使用方法

11.3.1 ID 操作

     
int id = iDb.IDSNOManager.NewID(iDb, "test", "id");

int id2 = iDb.IDSNOManager.NewIDForce(iDb, "test", "id");

     iDb.IDSNOManager.ResetID("test", "id", 1);

     iDb.IDSNOManager.ShowCurrentIDs(null,null);

11.3.2 自动编号操作

   
      iDb.IDSNOManager.NewSNO(iDb, "test", "caseno", new List<SerialChunk>(){
           new SerialChunk("prefix_GWFW","Text[GWFW][4]"),
           new SerialChunk("RiQiCtr","DateTime[yyyyMMdd][8][incycle]"),
           new SerialChunk("SerialNo","SerialNo[1,1,5,,day]")
      });
   
    List<string[]> li = iDb.IDSNOManager.ShowCurrentSNOs(null, null, null);
   
    List<SerialChunk> li = new List<SerialChunk>();
    li.Add(new SerialChunk(“prefix_GWFW”,null));
    li.Add(new SerialChunk(“RiQiCtr”,null));
    li.Add(new SerialChunk(“SerialNo”,null));
    iDb.IDSNOManager.ResetSNO(tableName, colName, li, null);
    iDb.IDSNOManager.NewSNO(iDb, "test", "caseno", new List<SerialChunk>(){
    new SerialChunk("prefix_GWFW","Text[GWFW][4]"),
    new SerialChunk("RiQiCtr","DateTime[yyyyMMdd][8][incycle]"),
    new SerialChunk("SerialNo","SerialNo[1,1,5,,day]")
    });
   
    List<string[]> li = iDb.IDSNOManager.ShowCurrentSNOs(null, null, null);
   
    List<SerialChunk> li = new List<SerialChunk>();
    li.Add(new SerialChunk(“prefix_GWFW”,null));
    li.Add(new SerialChunk(“RiQiCtr”,null));
    li.Add(new SerialChunk(“SerialNo”,null));
    iDb.IDSNOManager.ResetSNO(tableName, colName, li, null);