快捷導(dǎo)航

國產(chǎn)化中的?.NET?Core?操作達(dá)夢數(shù)據(jù)庫DM8的兩種方式(操作詳解)

更新時間：2022年04月19日 08:42:23 作者：波斯馬

這篇文章主要介紹了國產(chǎn)化之?.NET?Core?操作達(dá)夢數(shù)據(jù)庫DM8的兩種方式,這里提供兩種方式是傳統(tǒng)的DbHelperSQL方式和Dapper?方式，每種方式給大家介紹的非常詳細(xì)，需要的朋友可以參考下

背景

某個項(xiàng)目需要實(shí)現(xiàn)基礎(chǔ)軟件全部國產(chǎn)化，其中操作系統(tǒng)指定銀河麒麟，數(shù)據(jù)庫使用達(dá)夢V8，CPU平臺的范圍包括x64、龍芯、飛騰、鯤鵬等?？紤]到這些基礎(chǔ)產(chǎn)品對.NET的支持，最終選擇了.NET Core 3.1。

環(huán)境

CPU平臺：x86-64 / Arm64
操作系統(tǒng)：銀河麒麟 v4
數(shù)據(jù)庫：DM8
.NET：.NET Core 3.1

SDK

達(dá)夢自己提供了.NET操作其數(shù)據(jù)庫的SDK，可以通過NuGet安裝，也可以通過安裝達(dá)夢數(shù)據(jù)庫獲取。因?yàn)镹uGet上的版本不知道是誰提供的，所以這里以安裝數(shù)據(jù)庫獲取相關(guān)SDK為例。

在官網(wǎng)下載DM8的數(shù)據(jù)庫安裝文件：https://www.dameng.com/list_103.html

下載前需要先登錄，隨便注冊一個帳號就好了。

這里需要選擇CPU和操作系統(tǒng)，按照你的開發(fā)環(huán)境選擇即可，下載后按照提示安裝。

這里以Windows10為例，安裝后SDK文件的位置在：C:\dmdbms\drivers\dotNet

這里邊有EF的SDK，也有NHibernate的SDK，不過這篇文章只使用最基礎(chǔ)的基于ADO.NET的SDK。

這些SDK在文件夾DmProvider下邊，這里還提供了一個Nuget包，可以放到自己的Nuget倉庫中，方便內(nèi)部安裝。

可以看到，這個SDK可以支持.NET Core2.0以上的所有.NET版本。

操作數(shù)據(jù)庫

這里提供兩種方式：傳統(tǒng)的DbHelperSQL方式和 Dapper 方式。

DbHelperSQL方式

這種方式早年用的比較多，現(xiàn)在還有很多項(xiàng)目在使用，通過定義一組工具方法包裝對數(shù)據(jù)庫的各種增刪改查操作。下面給出代碼：

 public class DmDbClient
    {
        private string connectionString = string.Empty;

        /// <summary>
        /// 初始化DMClient的一個新實(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í)行簡單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ù)（可自定義超時時間）
        /// <param name="timeout">執(zhí)行超時時間</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（可自定義超時時間）
        /// <param name="sql"></param>
        /// <param name="timeout"></param>
        public DataSet Query(string sql, int timeout)
                    command.SelectCommand.CommandTimeout = timeout;
        #endregion 執(zhí)行簡單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（自動產(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í)行一條計算查詢結(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命令對象</param>
        /// <param name="conn">SQL連接對象</param>
        /// <param name="trans">SQL事務(wù)對象</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語句
    }

使用方法也很簡單，傳入SQL語句和參數(shù)即可。這里給出幾個增刪改查的例子：

 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á)夢數(shù)據(jù)庫的參數(shù)是用冒號作為前綴的。另外數(shù)據(jù)表和字段的名字建議全部使用大寫字母，單詞之間使用下劃線分隔，也就是蛇形命名法。此時SQL語句就不用關(guān)心大小寫了，怎么寫都行。

Dapper方式

Dapper是一個輕量級的ORM框架，現(xiàn)在使用的也很廣泛，可以簡化代碼編寫。因?yàn)镈apper擴(kuò)展的IDbConnection，這是ADO.NET中的東西，我們使用的DmProvider也是實(shí)現(xiàn)了ADO.NET相關(guān)接口，所以Dapper可以通過DmProvider操作達(dá)夢數(shù)據(jù)庫。

首先定義一個獲取數(shù)據(jù)庫連接對象的工廠類：

 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)于國產(chǎn)化之 .NET Core 操作達(dá)夢數(shù)據(jù)庫DM8的兩種方式的文章就介紹到這了,更多相關(guān).NET Core 達(dá)夢數(shù)據(jù)庫DM8內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: