背景
某個(gè)項(xiàng)目需要實(shí)現(xiàn)基礎(chǔ)軟件全部國(guó)產(chǎn)化，其中操作系統(tǒng)指定銀河麒麟，數(shù)據(jù)庫(kù)使用達(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ù)庫(kù)：DM8
- .NET：.NET Core 3.1
SDK
達(dá)夢(mèng)自己提供了.NET操作其數(shù)據(jù)庫(kù)的SDK，可以通過(guò)NuGet安裝，也可以通過(guò)安裝達(dá)夢(mèng)數(shù)據(jù)庫(kù)獲取。因?yàn)镹uGet上的版本不知道是誰(shuí)提供的，所以這里以安裝數(shù)據(jù)庫(kù)獲取相關(guān)SDK為例。
在官網(wǎng)下載DM8的數(shù)據(jù)庫(kù)安裝文件：https://www.dameng.com/list_103.html
下載前需要先登錄，隨便注冊(cè)一個(gè)帳號(hào)就好了。
這里需要選擇CPU和操作系統(tǒng)，按照你的開(kāi)發(fā)環(huán)境選擇即可，下載后按照提示安裝。
img
這里以Windows10為例，安裝后SDK文件的位置在：C:\dmdbms\drivers\dotNet
這里邊有EF的SDK，也有NHibernate的SDK，不過(guò)這篇文章只使用最基礎(chǔ)的基于A(yíng)DO.NET的SDK。
這些SDK在文件夾DmProvider下邊，這里還提供了一個(gè)Nuget包，可以放到自己的Nuget倉(cāng)庫(kù)中，方便內(nèi)部安裝。
img
可以看到，這個(gè)SDK可以支持.NET Core2.0以上的所有.NET版本。
操作數(shù)據(jù)庫(kù)
這里提供兩種方式：傳統(tǒng)的DbHelperSQL方式 和 Dapper 方式。
DbHelperSQL方式
這種方式早年用的比較多，現(xiàn)在還有很多項(xiàng)目在使用，通過(guò)定義一組工具方法包裝對(duì)數(shù)據(jù)庫(kù)的各種增刪改查操作。下面給出代碼：
public class DmDbHelper
{
private string connectionString = string.Empty;
/// <summary>
/// 初始化DMClient的一個(gè)新實(shí)例
/// </summary>
/// <param name="str"></param>
public DmDbHelper(string str)
{
connectionString = str;
}
#region 通用快捷方法
/// <summary>
/// 執(zhí)行一條SQL語(yǔ)句，確定記錄是否存在
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</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>
/// 執(zhí)行一條SQL語(yǔ)句，確定記錄是否存在
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</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>
/// 執(zhí)行一條SQL語(yǔ)句，確定記錄是否存在
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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>
/// 執(zhí)行一條SQL語(yǔ)句，確定記錄是否存在
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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>
/// 獲取記錄條數(shù)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCondition">查詢(xún)條件</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>
/// 獲取記錄條數(shù)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCondition">查詢(xún)條件</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>
/// 獲取記錄條數(shù)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCondition">查詢(xún)條件</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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>
/// 獲取記錄條數(shù)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="sqlCondition">查詢(xún)條件</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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 執(zhí)行簡(jiǎn)單SQL語(yǔ)句
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="sql">SQL語(yǔ)句</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;
}
}
}
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <returns>影響的記錄數(shù)</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>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)（可自定義超時(shí)時(shí)間）
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="timeout">執(zhí)行超時(shí)時(shí)間</param>
/// <returns>影響的記錄數(shù)</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>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)（可自定義超時(shí)時(shí)間）
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="timeout">執(zhí)行超時(shí)時(shí)間</param>
/// <returns>影響的記錄數(shù)</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>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="sqlList">多條SQL語(yǔ)句</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>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="sqlList">多條SQL語(yǔ)句</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>
/// 執(zhí)行一條SQL查詢(xún)語(yǔ)句，返回查詢(xún)結(jié)果。
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</param>
/// <returns>查詢(xún)結(jié)果</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>
/// 執(zhí)行一條SQL查詢(xún)語(yǔ)句，返回查詢(xún)結(jié)果。
/// </summary>
/// <param name="sql">SQL查詢(xún)語(yǔ)句</param>
/// <returns>查詢(xún)結(jié)果</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DbDataReader（切記要手工關(guān)閉DbDataReader）
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DbDataReader（切記要手工關(guān)閉DbDataReader）
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet（可自定義超時(shí)時(shí)間）
/// </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 執(zhí)行簡(jiǎn)單SQL語(yǔ)句
#region 執(zhí)行帶參數(shù)的SQL語(yǔ)句
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</param>
/// <returns>影響的記錄數(shù)</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>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</param>
/// <returns>影響的記錄數(shù)</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>
/// 執(zhí)行添加SQL語(yǔ)句，返回記錄的ID（自動(dòng)產(chǎn)生的自增主鍵）
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</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>
/// 執(zhí)行添加SQL語(yǔ)句，返回記錄的ID（自動(dòng)產(chǎn)生的自增主鍵）
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</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>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="sqlList">SQL語(yǔ)句的哈希表（key為sql語(yǔ)句，value是該語(yǔ)句的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>
/// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
/// </summary>
/// <param name="sqlList">SQL語(yǔ)句的哈希表（key為sql語(yǔ)句，value是該語(yǔ)句的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>
/// 執(zhí)行一條計(jì)算查詢(xún)結(jié)果語(yǔ)句，返回查詢(xún)結(jié)果。
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</param>
/// <returns>查詢(xún)結(jié)果</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>
/// 執(zhí)行一條計(jì)算查詢(xún)結(jié)果語(yǔ)句，返回查詢(xún)結(jié)果。
/// </summary>
/// <param name="sql">SQL語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</param>
/// <returns>查詢(xún)結(jié)果</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DmDataReader (切記要手工關(guān)閉DmDataReader)
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DmDataReader (切記要手工關(guān)閉DmDataReader)
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</param>
/// <param name="parms">SQL參數(shù)</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>
/// 執(zhí)行查詢(xún)語(yǔ)句，返回DataSet
/// </summary>
/// <param name="sql">查詢(xún)語(yǔ)句</param>
/// <param name="paras">參數(shù)數(shù)組</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>
/// 準(zhǔn)備SQL查詢(xún)命令
/// </summary>
/// <param name="cmd">SQL命令對(duì)象</param>
/// <param name="conn">SQL連接對(duì)象</param>
/// <param name="trans">SQL事務(wù)對(duì)象</param>
/// <param name="cmdText">SQL語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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>
/// 準(zhǔn)備SQL查詢(xún)命令
/// </summary>
/// <param name="cmd">SQL命令對(duì)象</param>
/// <param name="conn">SQL連接對(duì)象</param>
/// <param name="trans">SQL事務(wù)對(duì)象</param>
/// <param name="cmdText">SQL語(yǔ)句</param>
/// <param name="paras">SQL參數(shù)數(shù)組</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 執(zhí)行帶參數(shù)的SQL語(yǔ)句
}
使用方法也很簡(jiǎn)單，傳入SQL語(yǔ)句和參數(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";
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;
}
}
需要注意達(dá)夢(mèng)數(shù)據(jù)庫(kù)的參數(shù)是用冒號(hào)作為前綴的。另外數(shù)據(jù)表和字段的名字建議全部使用大寫(xiě)字母，單詞之間使用下劃線(xiàn)分隔，也就是蛇形命名法。此時(shí)SQL語(yǔ)句就不用關(guān)心大小寫(xiě)了，怎么寫(xiě)都行。
Dapper方式
Dapper是一個(gè)輕量級(jí)的ORM框架，現(xiàn)在使用的也很廣泛，可以簡(jiǎn)化代碼編寫(xiě)。因?yàn)镈apper擴(kuò)展的IDbConnection，這是ADO.NET中的東西，我們使用的DmProvider也是實(shí)現(xiàn)了ADO.NET相關(guān)接口，所以Dapper可以通過(guò)DmProvider操作達(dá)夢(mèng)數(shù)據(jù)庫(kù)。
首先定義一個(gè)獲取數(shù)據(jù)庫(kù)連接對(duì)象的工廠(chǎng)類(lèi)：
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語(yǔ)句了：
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定義的，可以看到能夠少寫(xiě)很多代碼。這里也不用打開(kāi)連接、關(guān)閉連接，也不用寫(xiě)using，因?yàn)镈apper的這些方法中已經(jīng)做了相關(guān)處理。
好了，以上就是本文的主要內(nèi)容。如有錯(cuò)漏歡迎指正。
收獲更多架構(gòu)知識(shí)，請(qǐng)關(guān)注公眾號(hào) 螢火架構(gòu)。原創(chuàng)內(nèi)容，轉(zhuǎn)載請(qǐng)注明出處。
