歡迎光臨
每天分享高質量文章

一個通用資料庫操作組件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 [email protected]",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:\work\Multiplan.mdb;
ACCESS: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\demo.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(){
           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 li = new List();
    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(){
    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 li = new List();
    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);
赞(0)

分享創造快樂