background
A project needs to realize the localization of all basic software. The operating system specifies Galaxy Kirin, and the database uses Dameng V8. The scope of CPU platform includes x64, godson, Feiteng, Kunpeng, etc. Considering that these basic products are right Net support, finally chose NET Core 3.1.
environment
- CPU platform: x86-64 / Arm64
- Operating system: Galaxy Kirin v4
- Database: DM8
- .NET: .NET Core 3.1
SDK
Da Meng provided it himself NET to operate its database, which can be installed through NuGet or obtained by installing Damon database. Because the version on NuGet is not known who provided it, here we take the installation database to obtain the relevant SDK as an example.
Download the database installation file of DM8 on the official website: https://www.dameng.com/list_103.html
You need to log in before downloading. Just register an account.
Here you need to select the CPU and operating system. You can select it according to your development environment. After downloading, install it according to the prompts.
Take Windows10 as an example. The location of SDK files after installation is C:\dmdbms\drivers\dotNet
There are both EF SDK and NHibernate SDK, but this article only uses the most basic one based on ADO Net SDK.
These SDK s are under the folder DmProvider. A Nuget package is also provided here, which can be put into your own Nuget warehouse for internal installation.
As you can see, this SDK can support NET Core2. All above 0 Net version.
Operation database
There are two ways: traditional dbhelper SQL and Dapper.
DbHelperSQL mode
This method was used a lot in the early years. Now there are many projects in use. By defining a set of tools and methods, we can package various operations of adding, deleting, modifying and querying the database. The code is given below:
public class DmDbHelper
{
private string connectionString = string.Empty;
/// <summary>
///Initialize a new instance of DMClient
/// </summary>
/// <param name="str"></param>
public DmDbHelper(string str)
{
connectionString = str;
}
#region general shortcut
/// <summary>
///Execute an SQL statement to determine whether the record exists
/// </summary>
///< param name = "SQL" > sql query statement < / 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>
///Execute an SQL statement to determine whether the record exists
/// </summary>
///< param name = "SQL" > sql query statement < / 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>
///Execute an SQL statement to determine whether the record exists
/// </summary>
///< param name = "SQL" > sql query statement < / param >
///< param name = "params" > sql parameter array < / 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>
///Execute an SQL statement to determine whether the record exists
/// </summary>
///< param name = "SQL" > sql query statement < / param >
///< param name = "params" > sql parameter array < / 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>
///Get the number of records
/// </summary>
///< param name = "tablename" > table name < / param >
///< param name = "sqlcondition" > query criteria < / 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>
///Get the number of records
/// </summary>
///< param name = "tablename" > table name < / param >
///< param name = "sqlcondition" > query criteria < / 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>
///Get the number of records
/// </summary>
///< param name = "tablename" > table name < / param >
///< param name = "sqlcondition" > query criteria < / param >
///< param name = "params" > sql parameter array < / 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>
///Get the number of records
/// </summary>
///< param name = "tablename" > table name < / param >
///< param name = "sqlcondition" > query criteria < / param >
///< param name = "params" > sql parameter array < / 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 general shortcut
#region executes simple SQL statements
/// <summary>
///Execute the SQL statement and return the number of records affected
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< returns > number of records affected < / 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>
///Execute the SQL statement and return the number of records affected
/// </summary>
///<param name= "SQL" >sql statement </param>
///< returns > number of records affected < / 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>
///Execute the SQL statement and return the number of records affected (the timeout can be customized)
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "timeout" > execution timeout < / param >
///< returns > number of records affected < / 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>
///Execute the SQL statement and return the number of records affected (the timeout can be customized)
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "timeout" > execution timeout < / param >
///< returns > number of records
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>
///Execute multiple SQL statements to realize database transactions.
/// </summary>
///< param name = "sqllist" > multiple SQL statements < / 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>
///Execute multiple SQL statements to realize database transactions.
/// </summary>
///< param name = "sqllist" > multiple SQL statements < / 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>
///Execute an SQL query statement and return the query results.
/// </summary>
///< param name = "SQL" > sql query statement < / param >
///< returns > query results < / 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>
///Execute an SQL query statement and return the query results.
/// </summary>
///< param name = "SQL" > sql query statement < / param >
///< returns > query results < / 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>
///Execute the query statement and return the DbDataReader (remember to close the DbDataReader manually)
/// </summary>
///< param name = "SQL" > query statement < / 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>
///Execute the query statement and return the DbDataReader (remember to close the DbDataReader manually)
/// </summary>
///< param name = "SQL" > query statement < / 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>
///Execute query statement and return DataSet
/// </summary>
///< param name = "SQL" > query statement < / 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>
///Execute query statement and return DataSet (customizable timeout)
/// </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 executes simple SQL statements
#region executes SQL statements with parameters
/// <summary>
///Execute the SQL statement and return the number of records affected
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "params" > sql parameter array < / param >
///< returns > number of records affected < / 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>
///Execute the SQL statement and return the number of records affected
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "params" > sql parameter array < / param >
///< returns > number of records affected < / 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>
///Execute the add SQL statement and return the ID of the record (auto generated self incrementing primary key)
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "parms" > sql parameters < / param >
///< returns > record 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>
///Execute the add SQL statement and return the ID of the record (auto generated self incrementing primary key)
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "parms" > sql parameters < / param >
///< returns > record 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>
///Execute multiple SQL statements to implement database transactions.
/// </summary>
///< param name = "sqllist" > hash table of sql statement (key is sql statement and value is 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>
///Execute multiple SQL statements to realize database transactions.
/// </summary>
///< param name = "sqllist" > hash table of sql statement (key is sql statement and value is 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>
///Execute a calculation query result statement to return the query result.
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "parms" > sql parameters < / param >
///< returns > query results < / 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>
///Execute a statement to calculate the query result and return the query result.
/// </summary>
///< param name = "SQL" > sql statement < / param >
///< param name = "parms" > sql parameters < / param >
///< returns > query results < / 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>
///Execute the query statement and return the DmDataReader (remember to close the DmDataReader manually)
/// </summary>
///< param name = "SQL" > query statement < / param >
///< param name = "parms" > sql parameters < / 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>
///Execute the query statement and return the DmDataReader (remember to close the DmDataReader manually)
/// </summary>
///< param name = "SQL" > query statement < / param >
///< param name = "parms" > sql parameters < / 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>
///Execute query statement and return DataSet
/// </summary>
///< param name = "SQL" > query statement < / param >
///< param name = "params" > parameter array < / 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>
///Prepare SQL query commands
/// </summary>
///< param name = "CMD" > SQL command object < / param >
///< param name = "conn" > sql connection object < / param >
///< param name = "trans" > sql transaction object < / param >
///< param name = "cmdtext" > sql statement < / param >
///< param name = "params" > sql parameter array < / 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>
///Prepare SQL query command
/// </summary>
///< param name = "CMD" > SQL command object < / param >
///< param name = "conn" > sql connection object < / param >
///< param name = "trans" > sql transaction object < / param >
///< param name = "cmdtext" > sql statement < / param >
///< param name = "params" > sql parameter array < / 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 executes SQL statements with parameters
}The use method is also very simple. You can pass in SQL statements and parameters. Here are some examples of adding, deleting and modifying queries:
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;
}
}It should be noted that the parameters of Dameng database are prefixed with colons. In addition, it is recommended to use uppercase letters for the names of data tables and fields, and underline between words, that is, snake naming. At this time, the SQL statement does not need to care about case, and it can be written in any way.
Dapper mode
Dapper is a lightweight ORM framework, which is widely used now and can simplify code writing. Because of the IDbConnection extended by dapper, this is ADO Net, the DmProvider we use also implements ADO Net related interfaces, so dapper can operate Dameng database through DmProvider.
First, define a factory class to obtain the database connection object:
public class DmConnectionFactory
{
static string sqlConnString = "Server=127.0.0.1; UserId=TESTDB; PWD=123456";
public static IDbConnection GetConn()
{
return new DmConnection(sqlConnString);
}
}Then you can use it to execute SQL statements:
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 and Execute methods are defined by Dapper. You can see that you can write a lot less code. There is no need to open or close the connection or write using here, because Dapper has done relevant processing in these methods.
Well, that's the main content of this article. If there are mistakes and omissions, please correct them.
Get more architecture knowledge, please pay attention to WeChat official account firefly. Original content, please indicate the source for reprint.