国产化之 .NET Core 操作达梦数据库DM8的两种方式

  • 国产化之 .NET Core 操作达梦数据库DM8的两种方式已关闭评论
  • 20 次浏览
  • A+
所属分类:linux技术
摘要

某个项目需要实现基础软件全部国产化,其中操作系统指定银河麒麟,数据库使用达梦V8,CPU平台的范围包括x64、龙芯、飞腾、鲲鹏等。考虑到这些基础产品对.NET的支持,最终选择了.NET Core 3.1。


背景

某个项目需要实现基础软件全部国产化,其中操作系统指定银河麒麟,数据库使用达梦V8,CPU平台的范围包括x64、龙芯、飞腾、鲲鹏等。考虑到这些基础产品对.NET的支持,最终选择了.NET Core 3.1。

环境

  • CPU平台:x86-64 / Arm64
  • 操作系统:银河麒麟 v4
  • 数据库:DM8
  • .NET:.NET Core 3.1

SDK

达梦自己提供了.NET操作其数据库的SDK,可以通过NuGet安装,也可以通过安装达梦数据库获取。因为NuGet上的版本不知道是谁提供的,所以这里以安装数据库获取相关SDK为例。

在官网下载DM8的数据库安装文件:https://www.dameng.com/list_103.html

下载前需要先登录,随便注册一个帐号就好了。

这里需要选择CPU和操作系统,按照你的开发环境选择即可,下载后按照提示安装。

国产化之 .NET Core 操作达梦数据库DM8的两种方式

这里以Windows10为例,安装后SDK文件的位置在:C:dmdbmsdriversdotNet

这里边有EF的SDK,也有NHibernate的SDK,不过这篇文章只使用最基础的基于ADO.NET的SDK。

这些SDK在文件夹DmProvider下边,这里还提供了一个Nuget包,可以放到自己的Nuget仓库中,方便内部安装。

国产化之 .NET Core 操作达梦数据库DM8的两种方式

可以看到,这个SDK可以支持.NET Core2.0以上的所有.NET版本。

操作数据库

这里提供两种方式:传统的DbHelperSQL方式 和 Dapper 方式。

DbHelperSQL方式

这种方式早年用的比较多,现在还有很多项目在使用,通过定义一组工具方法包装对数据库的各种增删改查操作。下面给出代码:

    public class DmDbClient     {         private string connectionString = string.Empty;          /// <summary>         /// 初始化DMClient的一个新实例         /// </summary>         /// <param name="str"></param>         public DmDbClient(string str)         {             connectionString = str;         }          #region 通用快捷方法         /// <summary>         /// 执行一条SQL语句,确定记录是否存在         /// </summary>         /// <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;         }          /// <summary>         /// 执行一条SQL语句,确定记录是否存在         /// </summary>         /// <param name="sql">SQL查询语句</param>         /// <returns></returns>         public async Task<bool> ExistsAsync(string sql)         {             object obj = await GetSingleAsync(sql);              int cmdresult;             if (Equals(obj, null) || Equals(obj, DBNull.Value))             {                 cmdresult = 0;             }             else             {                 cmdresult = int.Parse(obj.ToString());             }              return cmdresult > 0;         }          /// <summary>         /// 执行一条SQL语句,确定记录是否存在         /// </summary>         /// <param name="sql">SQL查询语句</param>         /// <param name="paras">SQL参数数组</param>         /// <returns></returns>         public bool Exists(string sql, params DmParameter[] paras)         {             object obj = GetSingle(sql, paras);              int cmdresult;             if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))             {                 cmdresult = 0;             }             else             {                 cmdresult = int.Parse(obj.ToString());             }              return cmdresult > 0;         }          /// <summary>         /// 执行一条SQL语句,确定记录是否存在         /// </summary>         /// <param name="sql">SQL查询语句</param>         /// <param name="paras">SQL参数数组</param>         /// <returns></returns>         public async Task<bool> ExistsAsync(string sql, params DmParameter[] paras)         {             object obj = await GetSingleAsync(sql, paras);              int cmdresult;             if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))             {                 cmdresult = 0;             }             else             {                 cmdresult = int.Parse(obj.ToString());             }              return cmdresult > 0;         }          /// <summary>         /// 获取记录条数         /// </summary>         /// <param name="tableName">表名</param>         /// <param name="sqlCondition">查询条件</param>         /// <returns></returns>         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);             }             else             {                 return 0;             }         }          /// <summary>         /// 获取记录条数         /// </summary>         /// <param name="tableName">表名</param>         /// <param name="sqlCondition">查询条件</param>         /// <returns></returns>         public async Task<int> GetCountAsync(string tableName, string sqlCondition)         {             string sql = "select count(1) from `" + tableName + "`";              if (!string.IsNullOrWhiteSpace(sqlCondition))             {                 sql += " where " + sqlCondition;             }              object result = await GetSingleAsync(sql);              if (result != null)             {                 return Convert.ToInt32(result);             }             else             {                 return 0;             }         }          /// <summary>         /// 获取记录条数         /// </summary>         /// <param name="tableName">表名</param>         /// <param name="sqlCondition">查询条件</param>         /// <param name="paras">SQL参数数组</param>         /// <returns></returns>         public int GetCount(string tableName, string sqlCondition, DmParameter[] paras)         {             string sql = "select count(1) from `" + tableName + "`";              if (!string.IsNullOrWhiteSpace(sqlCondition))             {                 sql += " where " + sqlCondition;             }              object result = GetSingle(sql, paras);              if (result != null)             {                 return Convert.ToInt32(result);             }             else             {                 return 0;             }         }          /// <summary>         /// 获取记录条数         /// </summary>         /// <param name="tableName">表名</param>         /// <param name="sqlCondition">查询条件</param>         /// <param name="paras">SQL参数数组</param>         /// <returns></returns>         public async Task<int> GetCountAsync(string tableName, string sqlCondition, DmParameter[] paras)         {             string sql = "select count(1) from `" + tableName + "`";              if (!string.IsNullOrWhiteSpace(sqlCondition))             {                 sql += " where " + sqlCondition;             }              object result = await GetSingleAsync(sql, paras);              if (result != null)             {                 return Convert.ToInt32(result);             }             else             {                 return 0;             }         }          #endregion 通用快捷方法          #region 执行简单SQL语句          /// <summary>         /// 执行SQL语句,返回影响的记录数         /// </summary>         /// <param name="sql">SQL语句</param>         /// <returns>影响的记录数</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;                 }             }         }          /// <summary>         /// 执行SQL语句,返回影响的记录数         /// </summary>         /// <param name="sql">SQL语句</param>         /// <returns>影响的记录数</returns>         public async Task<int> ExecuteSqlAsync(string sql)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand(sql, connection))                 {                     await connection.OpenAsync();                     int rows = await cmd.ExecuteNonQueryAsync();                     return rows;                 }             }         }          /// <summary>         /// 执行SQL语句,返回影响的记录数(可自定义超时时间)         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="timeout">执行超时时间</param>         /// <returns>影响的记录数</returns>         public int ExecuteSqlByTime(string sql, int timeout)         {             using (DmConnection connection = new DmConnection(this.connectionString))             {                 using (DmCommand cmd = new DmCommand(sql, connection))                 {                     connection.Open();                     cmd.CommandTimeout = timeout;                     int rows = cmd.ExecuteNonQuery();                     return rows;                 }             }         }          /// <summary>         /// 执行SQL语句,返回影响的记录数(可自定义超时时间)         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="timeout">执行超时时间</param>         /// <returns>影响的记录数</returns>         public async Task<int> ExecuteSqlByTimeAsync(string sql, int timeout)         {             using (DmConnection connection = new DmConnection(this.connectionString))             {                 using (DmCommand cmd = new DmCommand(sql, connection))                 {                     await connection.OpenAsync();                     cmd.CommandTimeout = timeout;                     int rows = await cmd.ExecuteNonQueryAsync();                     return rows;                 }             }         }          /// <summary>         /// 执行多条SQL语句,实现数据库事务。         /// </summary>         /// <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;                         }                     }                 }             }         }          /// <summary>         /// 执行多条SQL语句,实现数据库事务。         /// </summary>         /// <param name="sqlList">多条SQL语句</param>         public async Task ExecuteSqlTransAsync(ArrayList sqlList)         {             using (DmConnection conn = new DmConnection(connectionString))             {                 await conn.OpenAsync();                 using (DbTransaction trans = await conn.BeginTransactionAsync())                 {                     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;                                     await cmd.ExecuteNonQueryAsync();                                 }                             }                              trans.Commit();                         }                         catch (DmException ex)                         {                             trans.Rollback();                             throw ex;                         }                     }                 }             }         }          /// <summary>         /// 执行一条SQL查询语句,返回查询结果。         /// </summary>         /// <param name="sql">SQL查询语句</param>         /// <returns>查询结果</returns>         public object GetSingle(string sql)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand(sql, connection))                 {                     connection.Open();                      object obj = cmd.ExecuteScalar();                      if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))                     {                         return null;                     }                     else                     {                         return obj;                     }                 }             }         }          /// <summary>         /// 执行一条SQL查询语句,返回查询结果。         /// </summary>         /// <param name="sql">SQL查询语句</param>         /// <returns>查询结果</returns>         public async Task<object> GetSingleAsync(string sql)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand(sql, connection))                 {                     await connection.OpenAsync();                      object obj = await cmd.ExecuteScalarAsync();                      if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))                     {                         return null;                     }                     else                     {                         return obj;                     }                 }             }         }          /// <summary>         /// 执行查询语句,返回DbDataReader(切记要手工关闭DbDataReader)         /// </summary>         /// <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();         }          /// <summary>         /// 执行查询语句,返回DbDataReader(切记要手工关闭DbDataReader)         /// </summary>         /// <param name="sql">查询语句</param>         /// <returns>DmDataReader</returns>         public async Task<DbDataReader> ExecuteReaderAsync(string sql)         {             DmConnection connection = new DmConnection(connectionString);             DmCommand cmd = new DmCommand(sql, connection);              await connection.OpenAsync();             return await cmd.ExecuteReaderAsync();         }          /// <summary>         /// 执行查询语句,返回DataSet         /// </summary>         /// <param name="sql">查询语句</param>         /// <returns>DataSet</returns>         public DataSet Query(string sql)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmDataAdapter command = new DmDataAdapter(sql, connection))                 {                     DataSet ds = new DataSet();                      connection.Open();                     command.Fill(ds, "ds");                      return ds;                 }             }         }          /// <summary>         /// 执行查询语句,返回DataSet(可自定义超时时间)         /// </summary>         /// <param name="sql"></param>         /// <param name="timeout"></param>         /// <returns></returns>         public DataSet Query(string sql, int timeout)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmDataAdapter command = new DmDataAdapter(sql, connection))                 {                     DataSet ds = new DataSet();                      connection.Open();                     command.SelectCommand.CommandTimeout = timeout;                     command.Fill(ds, "ds");                      return ds;                 }             }         }         #endregion 执行简单SQL语句          #region 执行带参数的SQL语句          /// <summary>         /// 执行SQL语句,返回影响的记录数         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="paras">SQL参数数组</param>         /// <returns>影响的记录数</returns>         public int ExecuteSql(string sql, params DmParameter[] paras)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     PrepareCommand(cmd, connection, null, sql, paras);                     int rows = cmd.ExecuteNonQuery();                     cmd.Parameters.Clear();                     return rows;                 }             }         }          /// <summary>         /// 执行SQL语句,返回影响的记录数         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="paras">SQL参数数组</param>         /// <returns>影响的记录数</returns>         public async Task<int> ExecuteSqlAsync(string sql, params DmParameter[] paras)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     await PrepareCommandAsync(cmd, connection, null, sql, paras);                     int rows = await cmd.ExecuteNonQueryAsync();                     cmd.Parameters.Clear();                     return rows;                 }             }         }          /// <summary>         /// 执行添加SQL语句,返回记录的ID(自动产生的自增主键)         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>记录的ID</returns>         public int ExecuteAdd(string sql, params DmParameter[] parms)         {             sql = sql + ";Select @@IDENTITY";              using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     PrepareCommand(cmd, connection, null, sql, parms);                     int recordID = Int32.Parse(cmd.ExecuteScalar().ToString());                     cmd.Parameters.Clear();                      return recordID;                 }             }         }          /// <summary>         /// 执行添加SQL语句,返回记录的ID(自动产生的自增主键)         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>记录的ID</returns>         public async Task<int> ExecuteAddAsync(string sql, params DmParameter[] parms)         {             sql = sql + ";select @@identity as newautoid";              using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     await PrepareCommandAsync(cmd, connection, null, sql, parms);                      int recordID;                     try                     {                         recordID = int.Parse((await cmd.ExecuteScalarAsync()).ToString());                     }                     catch                     {                         recordID = -1;                     }                      cmd.Parameters.Clear();                      return recordID;                 }             }         }          /// <summary>         /// 执行多条SQL语句,实现数据库事务。         /// </summary>         /// <param name="sqlList">SQL语句的哈希表(key为sql语句,value是该语句的DmParameter[])</param>         public void ExecuteSqlTrans(Hashtable sqlList)         {             using (DmConnection conn = new DmConnection(connectionString))             {                 conn.Open();                 using (DbTransaction trans = conn.BeginTransaction())                 {                     using (DmCommand cmd = new DmCommand())                     {                         try                         {                             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();                             }                              trans.Commit();                         }                         catch (DmException ex)                         {                             trans.Rollback();                             throw ex;                         }                     }                 }             }         }          /// <summary>         /// 执行多条SQL语句,实现数据库事务。         /// </summary>         /// <param name="sqlList">SQL语句的哈希表(key为sql语句,value是该语句的DmParameter[])</param>         public async Task ExecuteSqlTransAsync(Hashtable sqlList)         {             using (DmConnection conn = new DmConnection(connectionString))             {                 await conn.OpenAsync();                 using (DbTransaction trans = conn.BeginTransaction())                 {                     using (DmCommand cmd = new DmCommand())                     {                         try                         {                             foreach (DictionaryEntry entry in sqlList)                             {                                 var sql = entry.Key.ToString();                                 var paras = (DmParameter[])entry.Value;                                  await PrepareCommandAsync(cmd, conn, trans, sql, paras);                                  int val = await cmd.ExecuteNonQueryAsync();                                  cmd.Parameters.Clear();                             }                              trans.Commit();                         }                         catch (DmException ex)                         {                             trans.Rollback();                             throw ex;                         }                     }                 }             }         }          /// <summary>         /// 执行一条计算查询结果语句,返回查询结果。         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>查询结果</returns>         public object GetSingle(string sql, params DmParameter[] parms)         {             using (DmConnection conn = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     PrepareCommand(cmd, conn, null, sql, parms);                      object obj = cmd.ExecuteScalar();                     cmd.Parameters.Clear();                      if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))                     {                         return null;                     }                     else                     {                         return obj;                     }                 }             }         }          /// <summary>         /// 执行一条计算查询结果语句,返回查询结果。         /// </summary>         /// <param name="sql">SQL语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>查询结果</returns>         public async Task<object> GetSingleAsync(string sql, params DmParameter[] parms)         {             using (DmConnection conn = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     await PrepareCommandAsync(cmd, conn, null, sql, parms);                      object obj = await cmd.ExecuteScalarAsync();                     cmd.Parameters.Clear();                      if ((object.Equals(obj, null)) || (object.Equals(obj, DBNull.Value)))                     {                         return null;                     }                     else                     {                         return obj;                     }                 }             }         }          /// <summary>         /// 执行查询语句,返回DmDataReader (切记要手工关闭DmDataReader)         /// </summary>         /// <param name="sql">查询语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>DmDataReader</returns>         public DbDataReader ExecuteReader(string sql, params DmParameter[] parms)         {             DmConnection connection = new DmConnection(connectionString);             DmCommand cmd = new DmCommand();              PrepareCommand(cmd, connection, null, sql, parms);              DbDataReader myReader = cmd.ExecuteReader();             cmd.Parameters.Clear();              return myReader;         }          /// <summary>         /// 执行查询语句,返回DmDataReader (切记要手工关闭DmDataReader)         /// </summary>         /// <param name="sql">查询语句</param>         /// <param name="parms">SQL参数</param>         /// <returns>DmDataReader</returns>         public async Task<DbDataReader> ExecuteReaderAsync(string sql, params DmParameter[] parms)         {             DmConnection connection = new DmConnection(connectionString);             DmCommand cmd = new DmCommand();              await PrepareCommandAsync(cmd, connection, null, sql, parms);              var myReader = await cmd.ExecuteReaderAsync();             cmd.Parameters.Clear();             return myReader;         }          /// <summary>         /// 执行查询语句,返回DataSet         /// </summary>         /// <param name="sql">查询语句</param>         /// <param name="paras">参数数组</param>         /// <returns>DataSet</returns>         public DataSet Query(string sql, params DmParameter[] paras)         {             using (DmConnection connection = new DmConnection(connectionString))             {                 using (DmCommand cmd = new DmCommand())                 {                     PrepareCommand(cmd, connection, null, sql, paras);                     DataSet ds = new DataSet();                      using (DmDataAdapter da = new DmDataAdapter(cmd))                     {                         da.Fill(ds, "ds");                         cmd.Parameters.Clear();                          return ds;                     }                 }             }         }          /// <summary>         /// 准备SQL查询命令         /// </summary>         /// <param name="cmd">SQL命令对象</param>         /// <param name="conn">SQL连接对象</param>         /// <param name="trans">SQL事务对象</param>         /// <param name="cmdText">SQL语句</param>         /// <param name="paras">SQL参数数组</param>         private void PrepareCommand(DmCommand cmd, DmConnection conn, DbTransaction trans, string cmdText, DmParameter[] paras)         {             if (conn.State != ConnectionState.Open)             {                 conn.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);                 }             }         }          /// <summary>         /// 准备SQL查询命令         /// </summary>         /// <param name="cmd">SQL命令对象</param>         /// <param name="conn">SQL连接对象</param>         /// <param name="trans">SQL事务对象</param>         /// <param name="cmdText">SQL语句</param>         /// <param name="paras">SQL参数数组</param>         private async Task PrepareCommandAsync(DmCommand cmd, DmConnection conn, DbTransaction trans, string cmdText, DmParameter[] paras)         {             if (conn.State != ConnectionState.Open)             {                 await conn.OpenAsync();             }              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);                 }             }         }          #endregion 执行带参数的SQL语句     } 

使用方法也很简单,传入SQL语句和参数即可。这里给出几个增删改查的例子:

    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";             DmParameter[] paras = new DmParameter[] {                 new DmParameter(":Id",model.Id),                 new DmParameter(":City",model.City)             };              return _client.ExecuteSql(sql, paras) > 0 ? true : false;         }          public bool Delete(int id)         {             string sql = "delete from Person where Id=:Id";             DmParameter[] paras = new DmParameter[] {                 new DmParameter(":Id",id),             };              return _client.ExecuteSql(sql, paras) > 0 ? true : false;         }          public PersonModel Get(int id)         {             string sql = "select Id,Name,City from Person where Id=:Id";             DmParameter[] paras = new DmParameter[] {                 new DmParameter(":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"))             {                 while (reader.Read())                 {                     var model = new PersonModel();                     model.Id = reader.GetInt32(0);                     model.Name = reader.GetString(1);                     model.City = reader.GetString(2);                     list.Add(model);                 }             }              return list;         }      } 

需要注意达梦数据库的参数是用冒号作为前缀的。另外数据表和字段的名字建议全部使用大写字母,单词之间使用下划线分隔,也就是蛇形命名法。此时SQL语句就不用关心大小写了,怎么写都行。

Dapper方式

Dapper是一个轻量级的ORM框架,现在使用的也很广泛,可以简化代码编写。因为Dapper扩展的IDbConnection,这是ADO.NET中的东西,我们使用的DmProvider也是实现了ADO.NET相关接口,所以Dapper可以通过DmProvider操作达梦数据库。

首先定义一个获取数据库连接对象的工厂类:

    public class DmConnectionFactory     {         static string sqlConnString = "Server=127.0.0.1; UserId=TESTDB; PWD=123456";         public static IDbConnection GetConn()         {             return new DmConnection(sqlConnString);         }     } 

然后就可以使用它执行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定义的,可以看到能够少写很多代码。这里也不用打开连接、关闭连接,也不用写using,因为Dapper的这些方法中已经做了相关处理。


好了,以上就是本文的主要内容。如有错漏欢迎指正。

收获更多架构知识,请关注微信