國(guó)產(chǎn)化中的?.NET?Core?操作達(dá)夢(mèng)數(shù)據(jù)庫DM8的兩種方式(操作詳解)
背景
某個(gè)項(xiàng)目需要實(shí)現(xiàn)基礎(chǔ)軟件全部國(guó)產(chǎn)化,其中操作系統(tǒng)指定銀河麒麟,數(shù)據(jù)庫使用達(dá)夢(mèng)V8,CPU平臺(tái)的范圍包括x64、龍芯、飛騰、鯤鵬等。考慮到這些基礎(chǔ)產(chǎn)品對(duì).NET的支持,最終選擇了.NET Core 3.1。
環(huán)境
- CPU平臺(tái):x86-64 / Arm64
- 操作系統(tǒng):銀河麒麟 v4
- 數(shù)據(jù)庫:DM8
- .NET:.NET Core 3.1
SDK
達(dá)夢(mèng)自己提供了.NET操作其數(shù)據(jù)庫的SDK,可以通過NuGet安裝,也可以通過安裝達(dá)夢(mèng)數(shù)據(jù)庫獲取。因?yàn)镹uGet上的版本不知道是誰提供的,所以這里以安裝數(shù)據(jù)庫獲取相關(guān)SDK為例。
在官網(wǎng)下載DM8的數(shù)據(jù)庫安裝文件:https://www.dameng.com/list_103.html
下載前需要先登錄,隨便注冊(cè)一個(gè)帳號(hào)就好了。
這里需要選擇CPU和操作系統(tǒng),按照你的開發(fā)環(huán)境選擇即可,下載后按照提示安裝。
這里以Windows10為例,安裝后SDK文件的位置在:C:\dmdbms\drivers\dotNet
這里邊有EF的SDK,也有NHibernate的SDK,不過這篇文章只使用最基礎(chǔ)的基于ADO.NET的SDK。
這些SDK在文件夾DmProvider下邊,這里還提供了一個(gè)Nuget包,可以放到自己的Nuget倉庫中,方便內(nèi)部安裝。
可以看到,這個(gè)SDK可以支持.NET Core2.0以上的所有.NET版本。
操作數(shù)據(jù)庫
這里提供兩種方式:傳統(tǒng)的DbHelperSQL方式 和 Dapper 方式。
DbHelperSQL方式
這種方式早年用的比較多,現(xiàn)在還有很多項(xiàng)目在使用,通過定義一組工具方法包裝對(duì)數(shù)據(jù)庫的各種增刪改查操作。下面給出代碼:
public class DmDbClient { private string connectionString = string.Empty; /// <summary> /// 初始化DMClient的一個(gè)新實(shí)例 /// </summary> /// <param name="str"></param> public DmDbClient(string str) { connectionString = str; } #region 通用快捷方法 /// 執(zhí)行一條SQL語句,確定記錄是否存在 /// <param name="sql">SQL查詢語句</param> /// <returns></returns> public bool Exists(string sql) object obj = GetSingle(sql); int cmdresult; if (Equals(obj, null) || Equals(obj, DBNull.Value)) { cmdresult = 0; } else cmdresult = int.Parse(obj.ToString()); return cmdresult > 0; public async Task<bool> ExistsAsync(string sql) object obj = await GetSingleAsync(sql); /// <param name="paras">SQL參數(shù)數(shù)組</param> public bool Exists(string sql, params DmParameter[] paras) object obj = GetSingle(sql, paras); if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value))) public async Task<bool> ExistsAsync(string sql, params DmParameter[] paras) object obj = await GetSingleAsync(sql, paras); /// 獲取記錄條數(shù) /// <param name="tableName">表名</param> /// <param name="sqlCondition">查詢條件</param> public int GetCount(string tableName, string sqlCondition) string sql = "select count(1) from `" + tableName + "`"; if (!string.IsNullOrWhiteSpace(sqlCondition)) sql += " where " + sqlCondition; object result = GetSingle(sql); if (result != null) return Convert.ToInt32(result); return 0; public async Task<int> GetCountAsync(string tableName, string sqlCondition) object result = await GetSingleAsync(sql); public int GetCount(string tableName, string sqlCondition, DmParameter[] paras) object result = GetSingle(sql, paras); public async Task<int> GetCountAsync(string tableName, string sqlCondition, DmParameter[] paras) object result = await GetSingleAsync(sql, paras); #endregion 通用快捷方法 #region 執(zhí)行簡(jiǎn)單SQL語句 /// 執(zhí)行SQL語句,返回影響的記錄數(shù) /// <param name="sql">SQL語句</param> /// <returns>影響的記錄數(shù)</returns> public int ExecuteSql(string sql) using (DmConnection connection = new DmConnection(connectionString)) using (DmCommand cmd = new DmCommand(sql, connection)) { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } public async Task<int> ExecuteSqlAsync(string sql) await connection.OpenAsync(); int rows = await cmd.ExecuteNonQueryAsync(); /// 執(zhí)行SQL語句,返回影響的記錄數(shù)(可自定義超時(shí)時(shí)間) /// <param name="timeout">執(zhí)行超時(shí)時(shí)間</param> public int ExecuteSqlByTime(string sql, int timeout) using (DmConnection connection = new DmConnection(this.connectionString)) cmd.CommandTimeout = timeout; public async Task<int> ExecuteSqlByTimeAsync(string sql, int timeout) /// 執(zhí)行多條SQL語句,實(shí)現(xiàn)數(shù)據(jù)庫事務(wù)。 /// <param name="sqlList">多條SQL語句</param> public void ExecuteSqlTrans(ArrayList sqlList) using (DmConnection conn = new DmConnection(connectionString)) conn.Open(); using (DbTransaction trans = conn.BeginTransaction()) using (DmCommand cmd = new DmCommand()) { cmd.Connection = conn; cmd.Transaction = trans; try { for (int n = 0; n < sqlList.Count; n++) { string sql = sqlList[n].ToString(); if (sql.Trim().Length > 1) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (DmException ex) trans.Rollback(); throw ex; } public async Task ExecuteSqlTransAsync(ArrayList sqlList) await conn.OpenAsync(); using (DbTransaction trans = await conn.BeginTransactionAsync()) await cmd.ExecuteNonQueryAsync(); /// 執(zhí)行一條SQL查詢語句,返回查詢結(jié)果。 /// <returns>查詢結(jié)果</returns> public object GetSingle(string sql) object obj = cmd.ExecuteScalar(); if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value))) return null; else return obj; public async Task<object> GetSingleAsync(string sql) object obj = await cmd.ExecuteScalarAsync(); /// 執(zhí)行查詢語句,返回DbDataReader(切記要手工關(guān)閉DbDataReader) /// <param name="sql">查詢語句</param> /// <returns>DmDataReader</returns> public DbDataReader ExecuteReader(string sql) DmConnection connection = new DmConnection(connectionString); DmCommand cmd = new DmCommand(sql, connection); connection.Open(); return cmd.ExecuteReader(); public async Task<DbDataReader> ExecuteReaderAsync(string sql) await connection.OpenAsync(); return await cmd.ExecuteReaderAsync(); /// 執(zhí)行查詢語句,返回DataSet /// <returns>DataSet</returns> public DataSet Query(string sql) using (DmDataAdapter command = new DmDataAdapter(sql, connection)) DataSet ds = new DataSet(); command.Fill(ds, "ds"); return ds; /// 執(zhí)行查詢語句,返回DataSet(可自定義超時(shí)時(shí)間) /// <param name="sql"></param> /// <param name="timeout"></param> public DataSet Query(string sql, int timeout) command.SelectCommand.CommandTimeout = timeout; #endregion 執(zhí)行簡(jiǎn)單SQL語句 #region 執(zhí)行帶參數(shù)的SQL語句 public int ExecuteSql(string sql, params DmParameter[] paras) using (DmCommand cmd = new DmCommand()) PrepareCommand(cmd, connection, null, sql, paras); cmd.Parameters.Clear(); public async Task<int> ExecuteSqlAsync(string sql, params DmParameter[] paras) await PrepareCommandAsync(cmd, connection, null, sql, paras); /// 執(zhí)行添加SQL語句,返回記錄的ID(自動(dòng)產(chǎn)生的自增主鍵) /// <param name="parms">SQL參數(shù)</param> /// <returns>記錄的ID</returns> public int ExecuteAdd(string sql, params DmParameter[] parms) sql = sql + ";Select @@IDENTITY"; PrepareCommand(cmd, connection, null, sql, parms); int recordID = Int32.Parse(cmd.ExecuteScalar().ToString()); return recordID; public async Task<int> ExecuteAddAsync(string sql, params DmParameter[] parms) sql = sql + ";select @@identity as newautoid"; await PrepareCommandAsync(cmd, connection, null, sql, parms); int recordID; try recordID = int.Parse((await cmd.ExecuteScalarAsync()).ToString()); catch recordID = -1; /// <param name="sqlList">SQL語句的哈希表(key為sql語句,value是該語句的DmParameter[])</param> public void ExecuteSqlTrans(Hashtable sqlList) foreach (DictionaryEntry entry in sqlList) var sql = entry.Key.ToString(); var paras = (DmParameter[])entry.Value; PrepareCommand(cmd, conn, trans, sql, paras); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); public async Task ExecuteSqlTransAsync(Hashtable sqlList) await PrepareCommandAsync(cmd, conn, trans, sql, paras); int val = await cmd.ExecuteNonQueryAsync(); /// 執(zhí)行一條計(jì)算查詢結(jié)果語句,返回查詢結(jié)果。 public object GetSingle(string sql, params DmParameter[] parms) PrepareCommand(cmd, conn, null, sql, parms); public async Task<object> GetSingleAsync(string sql, params DmParameter[] parms) await PrepareCommandAsync(cmd, conn, null, sql, parms); /// 執(zhí)行查詢語句,返回DmDataReader (切記要手工關(guān)閉DmDataReader) public DbDataReader ExecuteReader(string sql, params DmParameter[] parms) DmCommand cmd = new DmCommand(); PrepareCommand(cmd, connection, null, sql, parms); DbDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; public async Task<DbDataReader> ExecuteReaderAsync(string sql, params DmParameter[] parms) await PrepareCommandAsync(cmd, connection, null, sql, parms); var myReader = await cmd.ExecuteReaderAsync(); /// <param name="paras">參數(shù)數(shù)組</param> public DataSet Query(string sql, params DmParameter[] paras) using (DmDataAdapter da = new DmDataAdapter(cmd)) da.Fill(ds, "ds"); cmd.Parameters.Clear(); return ds; /// 準(zhǔn)備SQL查詢命令 /// <param name="cmd">SQL命令對(duì)象</param> /// <param name="conn">SQL連接對(duì)象</param> /// <param name="trans">SQL事務(wù)對(duì)象</param> /// <param name="cmdText">SQL語句</param> private void PrepareCommand(DmCommand cmd, DmConnection conn, DbTransaction trans, string cmdText, DmParameter[] paras) if (conn.State != ConnectionState.Open) cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (paras != null) foreach (DmParameter parameter in paras) if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) parameter.Value = DBNull.Value; cmd.Parameters.Add(parameter); private async Task PrepareCommandAsync(DmCommand cmd, DmConnection conn, DbTransaction trans, string cmdText, DmParameter[] paras) #endregion 執(zhí)行帶參數(shù)的SQL語句 }
使用方法也很簡(jiǎn)單,傳入SQL語句和參數(shù)即可。這里給出幾個(gè)增刪改查的例子:
public class PersonAdoNetDAL : IPersonDAL { static readonly DmDbClient _client = new DmDbClient("Server=127.0.0.1; UserId=TESTDB; PWD=1234567"); public int Add(PersonModel model) { string sql = "insert into Person(Name,City) Values(:Name,:City)"; DmParameter[] paras = new DmParameter[] { new DmParameter(":Name",model.Name), new DmParameter(":City",model.City) }; return _client.ExecuteAdd(sql, paras); } public bool Update(PersonModel model) string sql = "update Person set City=:City where Id=:Id"; new DmParameter(":Id",model.Id), return _client.ExecuteSql(sql, paras) > 0 ? true : false; public bool Delete(int id) string sql = "delete from Person where Id=:Id"; new DmParameter(":Id",id), public PersonModel Get(int id) string sql = "select Id,Name,City from Person where Id=:Id"; PersonModel model = null; using (var reader = (DmDataReader)_client.ExecuteReader(sql, paras)) { while (reader.Read()) { model = new PersonModel(); model.Id = reader.GetInt32(0); model.Name = reader.GetString(1); model.City = reader.GetString(2); } } return model; public List<PersonModel> GetList() var list = new List<PersonModel>(); using (var reader = (DmDataReader)_client.ExecuteReader("select Id,Name,City from Person")) var model = new PersonModel(); list.Add(model); return list; }
需要注意達(dá)夢(mèng)數(shù)據(jù)庫的參數(shù)是用冒號(hào)作為前綴的。另外數(shù)據(jù)表和字段的名字建議全部使用大寫字母,單詞之間使用下劃線分隔,也就是蛇形命名法。此時(shí)SQL語句就不用關(guān)心大小寫了,怎么寫都行。
Dapper方式
Dapper是一個(gè)輕量級(jí)的ORM框架,現(xiàn)在使用的也很廣泛,可以簡(jiǎn)化代碼編寫。因?yàn)镈apper擴(kuò)展的IDbConnection,這是ADO.NET中的東西,我們使用的DmProvider也是實(shí)現(xiàn)了ADO.NET相關(guān)接口,所以Dapper可以通過DmProvider操作達(dá)夢(mèng)數(shù)據(jù)庫。
首先定義一個(gè)獲取數(shù)據(jù)庫連接對(duì)象的工廠類:
public class DmConnectionFactory { static string sqlConnString = "Server=127.0.0.1; UserId=TESTDB; PWD=123456"; public static IDbConnection GetConn() { return new DmConnection(sqlConnString); } }
然后就可以使用它執(zhí)行SQL語句了:
public class PersonDapperDAL : IPersonDAL { public PersonDapperDAL() { } public PersonModel Get(int id) { string sql = "select Id,Name,City from Person where Id=:Id"; return DmConnectionFactory.GetConn().QueryFirstOrDefault<PersonModel>(sql, new { Id = id }); } public List<PersonModel> GetList() { string sql = "select Id,Name,City from Person"; return DmConnectionFactory.GetConn().Query<PersonModel>(sql).ToList(); } public int Add(PersonModel model) { string sql = "insert into Person(Name,City) Values(:Name,:City);Select @@IDENTITY"; return DmConnectionFactory.GetConn().QuerySingle<int>(sql, model); } public bool Update(PersonModel model) { string sql = "update Person set City=:City where Id=:Id"; int result = DmConnectionFactory.GetConn().Execute(sql, model); return result > 0; } public bool Delete(int id) { string sql = "delete from Person where Id=:Id"; int result = DmConnectionFactory.GetConn().Execute(sql, new { Id = id }); return result > 0; } }
Query、Execute這些方法都是Dapper定義的,可以看到能夠少寫很多代碼。這里也不用打開連接、關(guān)閉連接,也不用寫using,因?yàn)镈apper的這些方法中已經(jīng)做了相關(guān)處理。
到此這篇關(guān)于國(guó)產(chǎn)化之 .NET Core 操作達(dá)夢(mèng)數(shù)據(jù)庫DM8的兩種方式的文章就介紹到這了,更多相關(guān).NET Core 達(dá)夢(mèng)數(shù)據(jù)庫DM8內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- .NET?8新預(yù)覽版使用?Blazor?組件進(jìn)行服務(wù)器端呈現(xiàn)(項(xiàng)目體驗(yàn))
- 解決Win10無法安裝.Net Framework 3.5提示錯(cuò)誤代碼0x800F081F
- ASP.NET Core異常和錯(cuò)誤處理(8)
- win8/8.1系統(tǒng)安裝.net framework 3.5出現(xiàn)0x800F0906代碼錯(cuò)誤的解決方法
- ASP.NET MVC5+EF6+EasyUI 后臺(tái)管理系統(tǒng)(81)-數(shù)據(jù)篩選(萬能查詢)實(shí)例
- ASP.NET 程序員都非常有用的85個(gè)工具
- 無法啟動(dòng).NET Framework NGEN v4.0.30319_X86服務(wù)的解決方法
- .NET8 依賴注入
相關(guān)文章
asp.net 日期函數(shù) 某月的第一天和最后一天的日期
常用asp.net日期操作函數(shù)-得到某月的第一天和最后一天的日期2008-12-12asp.net下GDI+的一些常用應(yīng)用(水印,文字,圓角處理)技巧
asp.net下GDI+的一些常用應(yīng)用(水印,文字,圓角處理)技巧...2007-03-03CKEditor與dotnetcore實(shí)現(xiàn)圖片上傳功能
這篇文章主要為大家詳細(xì)介紹了CKEditor與dotnetcore實(shí)現(xiàn)圖片上傳功能,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-09-09asp.net通過HttpModule自動(dòng)在Url地址上添加參數(shù)
由于項(xiàng)目中有許多頁面需要用到cid參數(shù),所以想通過傳值cid來獲取數(shù)據(jù)。2010-01-01asp.net實(shí)現(xiàn)Gradview綁定數(shù)據(jù)庫數(shù)據(jù)并導(dǎo)出Excel的方法
這篇文章主要介紹了asp.net實(shí)現(xiàn)Gradview綁定數(shù)據(jù)庫數(shù)據(jù)并導(dǎo)出Excel的方法,涉及asp.net操作Gradview實(shí)現(xiàn)數(shù)據(jù)庫綁定及數(shù)據(jù)導(dǎo)出的相關(guān)技巧,非常簡(jiǎn)單實(shí)用,需要的朋友可以參考下2015-11-11