1. 云图网首页
  2. 技术专区
  3. 数据库

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

• 数据库


这里直接列举核心代码，以后整理：

底层引用Dapper。

 

a1-实体类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using WebApplication1.DB.Extend;
using WebApplication1.Enum;

namespace WebApplication1.DB.CMS
{
    [ClassMapper(EDBConnectionType.SqlServer, "dbo", "TB_UserSendAddressOrder")]
    public class TB_UserSendAddressOrder
    {
        [PropertyMapper]
        public int OrderID { get; set; }
        [PropertyMapper]
        public string AddRessUserName { get; set; }
    }
}

View Code

a2-实体表映射类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using WebApplication1.Enum;

namespace WebApplication1.DB.Extend
{
    /// <summary>
    /// 数据库表名映射类
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Class, AllowMultiple = true)]
    public class ClassMapperAttribute : System.Attribute
    {
        /// <summary>
        /// 数据库类型
        /// </summary>
        public EDBConnectionType DBConnectionType { get; set; }
        /// <summary>
        /// 数据库表的架构
        /// </summary>
        public string SchemaName { get; set; }
        /// <summary>
        /// 数据库表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="eDBConnectionType"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        public ClassMapperAttribute(EDBConnectionType eDBConnectionType, string schemaName = null, string tableName = null)
        {
            DBConnectionType = eDBConnectionType;
            SchemaName = schemaName;
            TableName = tableName;
        }
    }
}

View Code

a3-实体属性映射类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using System;

namespace WebApplication1.DB.Extend
{
    /// <summary>
    /// 数据库属性映射类
    /// </summary>
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false)]
    public class PropertyMapperAttribute : Attribute
    {
        /// <summary>
        /// 数据库列名
        /// </summary>
        public string DBColumnName { get; set; }

        /// <summary>
        /// 是否是主键列
        /// </summary>
        public bool IsPrimaryKey { get; set; }

        /// <summary>
        /// 是否忽略该列
        /// </summary>
        public bool Ignored { get; set; }

        /// <summary>
        /// 构造
        /// </summary>
        public PropertyMapperAttribute() : this(null, false, false)
        {

        }
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="dbColumnName"></param>
        public PropertyMapperAttribute(string dbColumnName) : this(dbColumnName, false, false)
        {

        }
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="isPrimaryKey"></param>
        public PropertyMapperAttribute(bool isPrimaryKey) : this(null, false, isPrimaryKey)
        {
        }
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="dbColumnName"></param>
        /// <param name="isPrimaryKey"></param>
        public PropertyMapperAttribute(string dbColumnName, bool isPrimaryKey) : this(dbColumnName, false, isPrimaryKey)
        {
        }
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="dbColumnName"></param>
        /// <param name="ignored"></param>
        /// <param name="isPrimaryKey"></param>
        public PropertyMapperAttribute(string dbColumnName, bool ignored, bool isPrimaryKey)
        {
            DBColumnName = dbColumnName;
            Ignored = ignored;
            IsPrimaryKey = isPrimaryKey;
        }
    }
}

View Code

a4-数据库类型枚举类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

namespace WebApplication1.Enum
{
    /// <summary>
    /// DB数据库类型
    /// </summary>
    public enum EDBConnectionType
    {
        /// <summary>
        /// MySql
        /// </summary>
        MySql = 0,
        /// <summary>
        /// SqlServer
        /// </summary>
        SqlServer = 1,
        /// <summary>
        /// PostgreSQL
        /// </summary>
        PostgreSQL = 2,
    }
}

View Code

a5-实体映射帮助类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using WebApplication1.Enum;
namespace WebApplication1.DB.Extend
{
/// <summary>
/// 数据库映射帮助类
/// </summary>
public class DBMapperHelper
{
/// <summary>
/// 获取映射
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <returns></returns>
public static DBMapperTable GetModelMapper<T>(EDBConnectionType eDBConnectionType)
{
DBMapperTable mapperTable = new DBMapperTable
{
DBPropertyList = new List<DBMapperProperty>(0)
};
Type tType = typeof(T);
IEnumerable<ClassMapperAttribute> classMapperAttributeList = (IEnumerable<ClassMapperAttribute>)tType.GetCustomAttributes(typeof(ClassMapperAttribute));
ClassMapperAttribute classMapperAttribute = classMapperAttributeList.ToList().FirstOrDefault(m => m.DBConnectionType == eDBConnectionType);
if (classMapperAttribute != null)
{
mapperTable.SchemaName = classMapperAttribute.SchemaName;
mapperTable.TableName = classMapperAttribute.TableName;
if (string.IsNullOrEmpty(mapperTable.TableName))
{
mapperTable.TableName = tType.Name;
}
}
List<PropertyInfo> tPropertyInfoList = tType.GetProperties().ToList();
PropertyMapperAttribute propertyMapAttribute = null;
foreach (var tPropertyInfo in tPropertyInfoList)
{
propertyMapAttribute = (PropertyMapperAttribute)tPropertyInfo.GetCustomAttribute(typeof(PropertyMapperAttribute));
//属性映射特性&&不忽略
if (propertyMapAttribute != null && propertyMapAttribute.Ignored == false)
{
if (string.IsNullOrEmpty(propertyMapAttribute.DBColumnName))
{
propertyMapAttribute.DBColumnName = tPropertyInfo.Name;
}
mapperTable.DBPropertyList.Add(new DBMapperProperty
{
DBColumnName = propertyMapAttribute.DBColumnName,
IsPrimaryKey = propertyMapAttribute.IsPrimaryKey,
PropertyInfo = tPropertyInfo
});
}
}
return mapperTable;
}
/// <summary>
/// 获取插入语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tArray"></param>
/// <returns></returns>
public static string GetInsertSql<T>(EDBConnectionType eDBConnectionType, params T[] tArray)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> columnSqlList = dBMapper.DBColumnList;
List<string> valuesSqlList = new List<string>(tArray.Length);
foreach (var tModel in tArray)
{
List<string> tValueList = new List<string>(dBMapper.DBPropertyList.Count);
foreach (DBMapperProperty dbMapperProperty in dBMapper.DBPropertyList)
{
string paramValue = FormatDBValue(dbMapperProperty.PropertyInfo.GetValue(tModel));
tValueList.Add(paramValue);
}
valuesSqlList.Add($"({string.Join(',', tValueList)})");
}
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string insertSql = $"insert into {tableSql}({string.Join(',', columnSqlList)}) values {string.Join(',', valuesSqlList)};";
return insertSql;
}
/// <summary>
/// 获取更新语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public static string GetUpdateSql<T>(EDBConnectionType eDBConnectionType, T tModel)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> updatePropertySQLList = new List<string>(dBMapper.DBPropertyList.Count);
foreach (DBMapperProperty dbMapperProperty in dBMapper.DBPropertyList)
{
if (dbMapperProperty.IsPrimaryKey == false)
{
updatePropertySQLList.Add($"{dbMapperProperty.DBColumnName}={FormatDBValue(dbMapperProperty.PropertyInfo.GetValue(tModel))}");
}
}
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string updateSQL = $"update {tableSql} set {string.Join(',', updatePropertySQLList)} where {primaryProperty.DBColumnName}={FormatDBValue(primaryProperty.PropertyInfo.GetValue(tModel))}";
return updateSQL;
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="primaryPropertyValue"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
string updateSQL = null;
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
if (primaryProperty != null)
{
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
updateSQL = $"delete from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryPropertyValue)}";
}
return updateSQL ?? "";
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, T tModel)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string updateSQL = $"delete from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryProperty.PropertyInfo.GetValue(tModel))}";
return updateSQL;
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="whereExpression"></param>
/// <param name="paramObj"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> whereExpression, out Dictionary<string, object> paramObj)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string sql = $"delete from {tableSql}";
if (whereExpression != null)
{
WherePart wherePart = new WhereBuilder().ToSql(whereExpression);
sql += $"where {wherePart.Sql}";
ExpandoObject param = new ExpandoObject();
foreach (var item in wherePart.Parameters)
{
((IDictionary<string, object>)param).Add(item.Key, item.Value);
}
paramObj = wherePart.Parameters;
}
else
{
paramObj = null;
}
return sql;
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="primaryPropertyValue"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
string sql = null;
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
if (primaryProperty != null)
{
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
sql = $"select * from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryPropertyValue)}";
}
return sql ?? "";
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="sort"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, Sort<T> sort)
{
string sql = GetSelectSql(eDBConnectionType, null, sort, out Dictionary<string, object> paramObj);
return sql;
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="whereExpression"></param>
/// <param name="sort"></param>
/// <param name="paramObj"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> whereExpression, Sort<T> sort, out Dictionary<string, object> paramObj)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> columnSqlList = dBMapper.DBColumnList;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string sql = $"select {string.Join(',', columnSqlList)} from {tableSql}";
if (whereExpression != null)
{
WherePart wherePart = new WhereBuilder().ToSql(whereExpression);
sql += $" where {wherePart.Sql}";
ExpandoObject param = new ExpandoObject();
foreach (var item in wherePart.Parameters)
{
((IDictionary<string, object>)param).Add(item.Key, item.Value);
}
paramObj = wherePart.Parameters;
}
else
{
paramObj = null;
}
if (sort != null)
{
string sortSql = sort.ToSql();
if (!string.IsNullOrWhiteSpace(sortSql))
{
sql += $" order by {sortSql}";
}
}
return sql;
}
/// <summary>
/// 格式化数据库值
/// </summary>
/// <param name="paramValue"></param>
/// <returns></returns>
private static string FormatDBValue(object paramValue)
{
string param = paramValue?.ToString();
if (string.IsNullOrEmpty(param))
{
param = "NULL";
}
else
{
if (param.Contains("'"))
{
param = paramValue.ToString().Replace("'", "''");
}
if (paramValue is string)
{
param = $"'{param}'";
}
else if (paramValue is DateTime || paramValue is DateTime?)
{
param = "'" + Convert.ToDateTime(param).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
}
}
return param;
}
/// <summary>
/// 数据库映射表
/// </summary>
public class DBMapperTable
{
/// <summary>
/// 数据库表的架构
/// </summary>
public string SchemaName { get; set; }
/// <summary>
/// 数据库表名称
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 数据库列列表
/// </summary>
public List<DBMapperProperty> DBPropertyList { get; set; }
/// <summary>
/// 数据库主键Key
/// </summary>
public string DBPrimaryKey
{
get
{
return DBPropertyList.FirstOrDefault(m => m.IsPrimaryKey)?.DBColumnName ?? "";
}
}
/// <summary>
/// 数据库主键属性
/// </summary>
public DBMapperProperty DBPrimaryProperty
{
get
{
return DBPropertyList.FirstOrDefault(m => m.IsPrimaryKey);
}
}
/// <summary>
/// 数据库列名列表
/// </summary>
public List<string> DBColumnList
{
get
{
return DBPropertyList.Select(m => m.DBColumnName).ToList();
}
}
}
/// <summary>
/// 数据库映射属性
/// </summary>
public class DBMapperProperty
{
/// <summary>
/// 数据库列名
/// </summary>
public string DBColumnName { get; set; }
/// <summary>
/// 是否是主键列
/// </summary>
public bool IsPrimaryKey { get; set; }
/// <summary>
/// 属性信息
/// </summary>
public PropertyInfo PropertyInfo { get; set; }
}
}
}

View Code

 

b1-仓储类基类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using WebApplication1.DB.Extend;
using WebApplication1.Enum;
using WebApplication1.Helper;
using WebApplication1.Model;
namespace WebApplication1.DB.Base
{
/// <summary>
/// 仓储基类
/// </summary>
public class BaseRepository
{
/// <summary>
/// 配置执行超时时间120（秒）
/// </summary>
private int commandTimeout = 120;
private ConfigHelper _configHelper { get; set; }
/// <summary>
/// 构造
/// </summary>
/// <param name="configHelper"></param>
public BaseRepository(ConfigHelper configHelper)
{
this._configHelper = configHelper;
}
/// <summary>
/// 获取连接串
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <returns></returns>
private string getDbConnectionStr(EDBConnectionType eDBConnectionType)
{
Dictionary<EDBConnectionType, ConfigDBConnection> connectionDic = this._configHelper.GetDBConnectionDic();
ConfigDBConnection configDBConnection = connectionDic[eDBConnectionType];
return configDBConnection.ConnectionStr;
}
/// <summary>
/// 获取连接串
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <returns></returns>
private IDbConnection getDbConnection(EDBConnectionType eDBConnectionType)
{
IDbConnection dbConnection = null;
Dictionary<EDBConnectionType, ConfigDBConnection> connectionDic = this._configHelper.GetDBConnectionDic();
ConfigDBConnection configDBConnection = connectionDic[eDBConnectionType];
if (eDBConnectionType == EDBConnectionType.SqlServer)
{
dbConnection = SqlClientFactory.Instance.CreateConnection();
}
else if (eDBConnectionType == EDBConnectionType.MySql)
{
dbConnection = MySqlConnector.MySqlConnectorFactory.Instance.CreateConnection();
}
else if (eDBConnectionType == EDBConnectionType.PostgreSQL)
{
dbConnection = Npgsql.NpgsqlFactory.Instance.CreateConnection();
}
dbConnection.ConnectionString = configDBConnection.ConnectionStr;
return dbConnection;
}
/// <summary>
/// 批量数据插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tList"></param>
/// <returns></returns>
public async Task<int> InsertListAsync<T>(EDBConnectionType eDBConnectionType, List<T> tList)
{
int result = 0;
if (tList != null && tList.Count > 0)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string insertSql = DBMapperHelper.GetInsertSql(eDBConnectionType, tList.ToArray());
result = await dbConnection.ExecuteAsync(insertSql, commandTimeout: this.commandTimeout);
}
return result;
}
/// <summary>
/// 高级批量插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="insertList"></param>
public async Task BulkCopyAsync<T>(EDBConnectionType eDBConnectionType, List<T> insertList)
{
if (insertList != null && insertList.Count > 0)
{
//数据量大于100使用SqlBulkCopy，小于100使用批量sql插入
if (insertList.Count > 100)
{
DataTable table = new DataTable();
DBMapperHelper.DBMapperTable mapperTable = DBMapperHelper.GetModelMapper<T>(eDBConnectionType);
string conn = this.getDbConnectionStr(eDBConnectionType);
// read the table structure from the database
string tableName = $"{mapperTable.SchemaName}.{mapperTable.TableName}";
DbDataAdapter dbDataAdapter = null;
if (eDBConnectionType == EDBConnectionType.SqlServer)
{
dbDataAdapter = new SqlDataAdapter($"SELECT TOP 0 * FROM {tableName}", conn);
}
else if (eDBConnectionType == EDBConnectionType.MySql)
{
dbDataAdapter = new MySqlConnector.MySqlDataAdapter($"SELECT * FROM {tableName} LIMIT 0", conn);
}
else if (eDBConnectionType == EDBConnectionType.PostgreSQL)
{
dbDataAdapter = new Npgsql.NpgsqlDataAdapter($"SELECT TOP 0 * FROM {tableName}", conn);
}
using (dbDataAdapter)
{
dbDataAdapter.Fill(table);
}
int count = insertList.Count;
for (var i = 0; i < count; i++)
{
var row = table.NewRow();
foreach (DBMapperHelper.DBMapperProperty item in mapperTable.DBPropertyList)
{
row[item.DBColumnName] = item.PropertyInfo.GetValue(insertList[i]) ?? DBNull.Value;
}
table.Rows.Add(row);
}
using (IDbConnection dbConnection = this.getDbConnection(eDBConnectionType))
{
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
if (eDBConnectionType == EDBConnectionType.SqlServer)
{
using (var bulk = new SqlBulkCopy(dbConnection as SqlConnection))
{
//设置超时时间=1000秒
bulk.BulkCopyTimeout = 1000;
bulk.DestinationTableName = tableName;
await bulk.WriteToServerAsync(table);
}
}
else if (eDBConnectionType == EDBConnectionType.MySql)
{
var bulk = new MySqlConnector.MySqlBulkCopy(dbConnection as MySqlConnector.MySqlConnection);
//设置超时时间=1000秒
bulk.BulkCopyTimeout = 1000;
bulk.DestinationTableName = tableName;
await bulk.WriteToServerAsync(table);
}
else if (eDBConnectionType == EDBConnectionType.PostgreSQL)
{
//NpgSql暂未找到合适的大批量插入方法
await this.InsertListAsync(eDBConnectionType, insertList);
}
};
}
else
{
await this.InsertListAsync(eDBConnectionType, insertList);
}
}
}
/// <summary>
/// 查出多条记录的实体泛型集合
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <returns></returns>
public async Task<List<T>> SelectAsync<T>(EDBConnectionType eDBConnectionType, Sort<T> sort = null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string sql = DBMapperHelper.GetSelectSql<T>(eDBConnectionType, sort);
List<T> tList = (await dbConnection.QueryAsync<T>(sql, null, commandTimeout: this.commandTimeout)).ToList();
return tList ?? new List<T>(0);
}
/// <summary>
/// 查出单条数据
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <param name="eDBConnectionType">数据库类型</param>
/// <param name="primaryPropertyValue">主键值</param>
/// <returns></returns>
public async Task<T> FirstAsync<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
T t = default(T);
if (primaryPropertyValue != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
Dictionary<string, object> paramDic = null;
string sql = DBMapperHelper.GetSelectSql<T>(eDBConnectionType, primaryPropertyValue);
t = await dbConnection.QueryFirstOrDefaultAsync<T>(sql, paramDic, commandTimeout: commandTimeout);
}
return t;
}
/// <summary>
/// 查出单条数据
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <param name="eDBConnectionType">数据库类型</param>
/// <param name="expression">查询表达式</param>
/// <returns></returns>
public async Task<T> FirstAsync<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> expression)
{
T t = default(T);
if (expression != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
Dictionary<string, object> paramDic = null;
string sql = DBMapperHelper.GetSelectSql<T>(eDBConnectionType, expression, null, out paramDic);
t = await dbConnection.QueryFirstOrDefaultAsync<T>(sql, paramDic, commandTimeout: commandTimeout);
}
return t;
}
/// <summary>
/// 查出多条记录的实体泛型集合
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <returns></returns>
public async Task<List<T>> SelectAsync<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> expression, Sort<T> sort = null)
{
List<T> tList = null;
if (expression != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
Dictionary<string, object> paramDic = null;
string sql = DBMapperHelper.GetSelectSql<T>(eDBConnectionType, expression, sort, out paramDic);
tList = (await dbConnection.QueryAsync<T>(sql, paramDic, commandTimeout: this.commandTimeout)).ToList();
}
return tList ?? new List<T>(0);
}
/// <summary>
/// Query
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="sql"></param>
/// <returns></returns>
public async Task<List<T>> QueryAsync<T>(EDBConnectionType eDBConnectionType, string sql)
{
List<T> tList = null;
if (!string.IsNullOrEmpty(sql))
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
tList = (await dbConnection.QueryAsync<T>(sql, commandTimeout: this.commandTimeout)).ToList();
}
return tList ?? new List<T>(0);
}
/// <summary>
/// Execute
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public async Task<int> ExecuteAsync(EDBConnectionType eDBConnectionType, string sql, object param)
{
int executeNum = 0;
if (!string.IsNullOrWhiteSpace(sql))
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
executeNum = await dbConnection.ExecuteAsync(sql, param, commandTimeout: this.commandTimeout);
}
return executeNum;
}
/// <summary>
/// ExecuteScalar
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public async Task<object> ExecuteScalar(EDBConnectionType eDBConnectionType, string sql, object param)
{
object firstData = null;
if (!string.IsNullOrWhiteSpace(sql))
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
firstData = await dbConnection.ExecuteScalarAsync(sql, param, commandTimeout: this.commandTimeout);
}
return firstData;
}
/// <summary>
/// 更新对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public async Task<int> Update<T>(EDBConnectionType eDBConnectionType, T tModel)
{
int result = 0;
if (tModel != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string updateSQL = DBMapperHelper.GetUpdateSql(eDBConnectionType, tModel);
result = await dbConnection.ExecuteAsync(updateSQL, commandTimeout: this.commandTimeout);
}
return result;
}
/// <summary>
/// 删除对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="primaryPropertyValue"></param>
/// <returns></returns>
public async Task<int> Delete<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
int result = 0;
if (primaryPropertyValue != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string sql = DBMapperHelper.GetDeleteSql(eDBConnectionType, primaryPropertyValue);
if (sql.IsNotNullOrEmpty())
{
result = await dbConnection.ExecuteAsync(sql, commandTimeout: this.commandTimeout);
}
}
return result;
}
/// <summary>
/// 删除对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public async Task<int> Delete<T>(EDBConnectionType eDBConnectionType, T tModel)
{
int result = 0;
if (tModel != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string sql = DBMapperHelper.GetDeleteSql(eDBConnectionType, tModel);
if (sql.IsNotNullOrEmpty())
{
result = await dbConnection.ExecuteAsync(sql, commandTimeout: this.commandTimeout);
}
}
return result;
}
/// <summary>
/// 删除对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="expression"></param>
/// <returns></returns>
public async Task<int> Delete<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> expression)
{
int result = 0;
if (expression != null)
{
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
string sql = DBMapperHelper.GetDeleteSql(eDBConnectionType, expression);
result = await dbConnection.ExecuteAsync(sql, commandTimeout: this.commandTimeout);
}
return result;
}
/// <summary>
/// Count
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="where"></param>
/// <param name="param"></param>
/// <returns></returns>
public async Task<int> Count<T>(EDBConnectionType eDBConnectionType, string where, object param)
{
DBMapperHelper.DBMapperTable dBMapper = DBMapperHelper.GetModelMapper<T>(eDBConnectionType);
string whereSql = "";
if (!string.IsNullOrWhiteSpace(where))
{
whereSql = $"where {where}";
}
string countSql = $"select count(1) from {dBMapper.SchemaName}.{dBMapper.TableName} {whereSql}";
int count = Convert.ToInt32(await this.ExecuteScalar(eDBConnectionType, countSql, param));
return count;
}
/// <summary>
/// GetList
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="where"></param>
/// <param name="sortFiled"></param>
/// <param name="param"></param>
/// <returns></returns>
public async Task<(List<T>, int)> GetList<T>(EDBConnectionType eDBConnectionType, int pageIndex, int pageSize, string where, string sortFiled, object param = null)
{
if (pageIndex <= 0 || pageSize <= 0)
{
throw new ArgumentException("pageIndex、pageSize参数错误");
}
DBMapperHelper.DBMapperTable dBMapper = DBMapperHelper.GetModelMapper<T>(eDBConnectionType);
List<string> fieldList = dBMapper.DBColumnList;
int begin = pageSize * (pageIndex - 1);
string sql = string.Empty;
string tableName = dBMapper.TableName;
string orderSql = string.Empty;
int recordCount = await this.Count<T>(eDBConnectionType, where, param);
if (!string.IsNullOrWhiteSpace(sortFiled))
{
orderSql = "ORDER BY " + sortFiled;
}
if (!string.IsNullOrWhiteSpace(where))
{
where = " WHERE " + where;
}
else
{
where = " WHERE 1=1";
}
//传统查询
//sql = $"SELECT {string.Join(',', fieldList)} FROM {tableName} {where} {orderSql} LIMIT {begin},{pageSize}";
//高性能查询
//优化查询性能、利用单列(优先主键)查询速度，快速过滤不需要的数据
string primaryKey = dBMapper.DBPrimaryKey;
if (primaryKey.IsNullOrEmpty())
{
primaryKey = dBMapper.DBColumnList.FirstOrDefault();
}
if (eDBConnectionType == EDBConnectionType.MySql)
{
sql = $"SELECT {string.Join(',', fieldList)} FROM {tableName} {where} {orderSql} LIMIT {begin},{pageSize}";
}
else if (eDBConnectionType == EDBConnectionType.SqlServer)
{
sql = [email protected]"SELECT TOP({pageSize}) {string.Join(',', fieldList)} FROM {tableName} {where} AND {primaryKey} NOT IN (SELECT TOP({begin}) {primaryKey} FROM {tableName} {where} {orderSql}) {orderSql} ";
}
IDbConnection dbConnection = this.getDbConnection(eDBConnectionType);
List<T> list = (await dbConnection.QueryAsync<T>(sql, param)).ToList();
return (list, recordCount);
}
}
/// <summary>
/// 仓储基类
/// </summary>
public class BaseRepository<T, TKey> : BaseRepository
{
/// <summary>
/// 构造
/// </summary>
/// <param name="configHelper"></param>
public BaseRepository(ConfigHelper configHelper) : base(configHelper)
{
}
/// <summary>
/// 查出多条记录的实体泛型集合
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="sort"></param>
/// <returns></returns>
public Task<List<T>> Select(EDBConnectionType eDBConnectionType, Sort<T> sort = null)
{
return base.SelectAsync(eDBConnectionType, sort);
}
/// <summary>
/// 查出单条数据
/// </summary>
/// <param name="eDBConnectionType">数据库类型</param>
/// <param name="primaryPropertyValue">主键值</param>
/// <returns></returns>
public async Task<T> First(EDBConnectionType eDBConnectionType, TKey primaryPropertyValue)
{
return await base.FirstAsync<T>(eDBConnectionType, primaryPropertyValue);
}
/// <summary>
/// 查出单条数据
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="expression"></param>
/// <returns></returns>
public Task<T> First(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> expression)
{
return base.FirstAsync(eDBConnectionType, expression);
}
/// <summary>
/// 查出多条记录的实体泛型集合
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="expression"></param>
/// <param name="sort"></param>
/// <returns></returns>
public Task<List<T>> Select(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> expression, Sort<T> sort = null)
{
return base.SelectAsync(eDBConnectionType, expression, sort);
}
/// <summary>
/// 插入实体记录
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<int> Insert(EDBConnectionType eDBConnectionType, T entity)
{
return await base.InsertListAsync(eDBConnectionType, new List<T> { entity });
}
/// <summary>
/// 批量数据插入
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="tList"></param>
/// <returns></returns>
public Task<int> InsertList(EDBConnectionType eDBConnectionType, List<T> tList)
{
return base.InsertListAsync(eDBConnectionType, tList);
}
/// <summary>
/// 更新实体记录
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<int> Update(EDBConnectionType eDBConnectionType, T entity)
{
return await base.Update(eDBConnectionType, entity);
}
/// <summary>
/// 删除指定键的记录
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="ID"></param>
/// <returns></returns>
public async Task<int> Delete(EDBConnectionType eDBConnectionType, TKey ID)
{
return await base.Delete(eDBConnectionType, ID);
}
/// <summary>
/// 删除实体记录
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<int> Delete(EDBConnectionType eDBConnectionType, T entity)
{
return await base.Delete(eDBConnectionType, entity);
}
/// <summary>
/// 删除所有符合特定表达式的数据
/// </summary>
/// <param name="eDBConnectionType"></param>
/// <param name="whereExpress"></param>
/// <returns></returns>
public async Task<int> Delete(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> whereExpress)
{
return await base.Delete(eDBConnectionType, whereExpress);
}
}
}

View Code

b2-仓储类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.DB.Base;
using WebApplication1.DB.CMS;
using WebApplication1.Enum;
using WebApplication1.Helper;
namespace WebApplication1.DB.Repository
{
public class TB_UserSendAddressOrderRepository : BaseRepository<TB_UserSendAddressOrder, string>
{
public TB_UserSendAddressOrderRepository(ConfigHelper configHelper) : base(configHelper)
{
}
public async Task<List<TB_UserSendAddressOrder>> GetModelByOrderID(int siteID, int orderID)
{
List<TB_UserSendAddressOrder> dataList = await base.Select(EDBConnectionType.SqlServer, m => m.SiteID == siteID && m.OrderID == orderID);
return dataList;
}
}
}

View Code

b3-数据库映射帮助类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using WebApplication1.Enum;
namespace WebApplication1.DB.Extend
{
/// <summary>
/// 数据库映射帮助类
/// </summary>
public class DBMapperHelper
{
/// <summary>
/// 获取映射
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <returns></returns>
public static DBMapperTable GetModelMapper<T>(EDBConnectionType eDBConnectionType)
{
DBMapperTable mapperTable = new DBMapperTable
{
DBPropertyList = new List<DBMapperProperty>(0)
};
Type tType = typeof(T);
IEnumerable<ClassMapperAttribute> classMapperAttributeList = (IEnumerable<ClassMapperAttribute>)tType.GetCustomAttributes(typeof(ClassMapperAttribute));
ClassMapperAttribute classMapperAttribute = classMapperAttributeList.ToList().FirstOrDefault(m => m.DBConnectionType == eDBConnectionType);
if (classMapperAttribute != null)
{
mapperTable.SchemaName = classMapperAttribute.SchemaName;
mapperTable.TableName = classMapperAttribute.TableName;
if (string.IsNullOrEmpty(mapperTable.TableName))
{
mapperTable.TableName = tType.Name;
}
}
List<PropertyInfo> tPropertyInfoList = tType.GetProperties().ToList();
PropertyMapperAttribute propertyMapAttribute = null;
foreach (var tPropertyInfo in tPropertyInfoList)
{
propertyMapAttribute = (PropertyMapperAttribute)tPropertyInfo.GetCustomAttribute(typeof(PropertyMapperAttribute));
//属性映射特性&&不忽略
if (propertyMapAttribute != null && propertyMapAttribute.Ignored == false)
{
if (string.IsNullOrEmpty(propertyMapAttribute.DBColumnName))
{
propertyMapAttribute.DBColumnName = tPropertyInfo.Name;
}
mapperTable.DBPropertyList.Add(new DBMapperProperty
{
DBColumnName = propertyMapAttribute.DBColumnName,
IsPrimaryKey = propertyMapAttribute.IsPrimaryKey,
PropertyInfo = tPropertyInfo
});
}
}
return mapperTable;
}
/// <summary>
/// 获取插入语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tArray"></param>
/// <returns></returns>
public static string GetInsertSql<T>(EDBConnectionType eDBConnectionType, params T[] tArray)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> columnSqlList = dBMapper.DBColumnList;
List<string> valuesSqlList = new List<string>(tArray.Length);
foreach (var tModel in tArray)
{
List<string> tValueList = new List<string>(dBMapper.DBPropertyList.Count);
foreach (DBMapperProperty dbMapperProperty in dBMapper.DBPropertyList)
{
string paramValue = FormatDBValue(dbMapperProperty.PropertyInfo.GetValue(tModel));
tValueList.Add(paramValue);
}
valuesSqlList.Add($"({string.Join(',', tValueList)})");
}
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string insertSql = $"insert into {tableSql}({string.Join(',', columnSqlList)}) values {string.Join(',', valuesSqlList)};";
return insertSql;
}
/// <summary>
/// 获取更新语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public static string GetUpdateSql<T>(EDBConnectionType eDBConnectionType, T tModel)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> updatePropertySQLList = new List<string>(dBMapper.DBPropertyList.Count);
foreach (DBMapperProperty dbMapperProperty in dBMapper.DBPropertyList)
{
if (dbMapperProperty.IsPrimaryKey == false)
{
updatePropertySQLList.Add($"{dbMapperProperty.DBColumnName}={FormatDBValue(dbMapperProperty.PropertyInfo.GetValue(tModel))}");
}
}
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string updateSQL = $"update {tableSql} set {string.Join(',', updatePropertySQLList)} where {primaryProperty.DBColumnName}={FormatDBValue(primaryProperty.PropertyInfo.GetValue(tModel))}";
return updateSQL;
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="primaryPropertyValue"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
string updateSQL = null;
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
if (primaryProperty != null)
{
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
updateSQL = $"delete from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryPropertyValue)}";
}
return updateSQL ?? "";
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="tModel"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, T tModel)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string updateSQL = $"delete from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryProperty.PropertyInfo.GetValue(tModel))}";
return updateSQL;
}
/// <summary>
/// 获取删除语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="whereExpression"></param>
/// <param name="paramObj"></param>
/// <returns></returns>
public static string GetDeleteSql<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> whereExpression, out Dictionary<string, object> paramObj)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string sql = $"delete from {tableSql}";
if (whereExpression != null)
{
WherePart wherePart = new WhereBuilder().ToSql(whereExpression);
sql += $"where {wherePart.Sql}";
ExpandoObject param = new ExpandoObject();
foreach (var item in wherePart.Parameters)
{
((IDictionary<string, object>)param).Add(item.Key, item.Value);
}
paramObj = wherePart.Parameters;
}
else
{
paramObj = null;
}
return sql;
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="primaryPropertyValue"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, object primaryPropertyValue)
{
string sql = null;
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
DBMapperProperty primaryProperty = dBMapper.DBPrimaryProperty;
if (primaryProperty != null)
{
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
sql = $"select * from {tableSql} where {primaryProperty.DBColumnName}={FormatDBValue(primaryPropertyValue)}";
}
return sql ?? "";
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="sort"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, Sort<T> sort)
{
string sql = GetSelectSql(eDBConnectionType, null, sort, out Dictionary<string, object> paramObj);
return sql;
}
/// <summary>
/// 获取查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="eDBConnectionType"></param>
/// <param name="whereExpression"></param>
/// <param name="sort"></param>
/// <param name="paramObj"></param>
/// <returns></returns>
public static string GetSelectSql<T>(EDBConnectionType eDBConnectionType, Expression<Func<T, bool>> whereExpression, Sort<T> sort, out Dictionary<string, object> paramObj)
{
DBMapperTable dBMapper = GetModelMapper<T>(eDBConnectionType);
List<string> columnSqlList = dBMapper.DBColumnList;
string tableSql = (dBMapper.SchemaName.IsNotNullOrEmpty() ? dBMapper.SchemaName + "." : "") + dBMapper.TableName;
string sql = $"select {string.Join(',', columnSqlList)} from {tableSql}";
if (whereExpression != null)
{
WherePart wherePart = new WhereBuilder().ToSql(whereExpression);
sql += $" where {wherePart.Sql}";
ExpandoObject param = new ExpandoObject();
foreach (var item in wherePart.Parameters)
{
((IDictionary<string, object>)param).Add(item.Key, item.Value);
}
paramObj = wherePart.Parameters;
}
else
{
paramObj = null;
}
if (sort != null)
{
string sortSql = sort.ToSql();
if (!string.IsNullOrWhiteSpace(sortSql))
{
sql += $" order by {sortSql}";
}
}
return sql;
}
/// <summary>
/// 格式化数据库值
/// </summary>
/// <param name="paramValue"></param>
/// <returns></returns>
private static string FormatDBValue(object paramValue)
{
string param = paramValue?.ToString();
if (string.IsNullOrEmpty(param))
{
param = "NULL";
}
else
{
if (param.Contains("'"))
{
param = paramValue.ToString().Replace("'", "''");
}
if (paramValue is string)
{
param = $"'{param}'";
}
else if (paramValue is DateTime || paramValue is DateTime?)
{
param = "'" + Convert.ToDateTime(param).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
}
}
return param;
}
/// <summary>
/// 数据库映射表
/// </summary>
public class DBMapperTable
{
/// <summary>
/// 数据库表的架构
/// </summary>
public string SchemaName { get; set; }
/// <summary>
/// 数据库表名称
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 数据库列列表
/// </summary>
public List<DBMapperProperty> DBPropertyList { get; set; }
/// <summary>
/// 数据库主键Key
/// </summary>
public string DBPrimaryKey
{
get
{
return DBPropertyList.FirstOrDefault(m => m.IsPrimaryKey)?.DBColumnName ?? "";
}
}
/// <summary>
/// 数据库主键属性
/// </summary>
public DBMapperProperty DBPrimaryProperty
{
get
{
return DBPropertyList.FirstOrDefault(m => m.IsPrimaryKey);
}
}
/// <summary>
/// 数据库列名列表
/// </summary>
public List<string> DBColumnList
{
get
{
return DBPropertyList.Select(m => m.DBColumnName).ToList();
}
}
}
/// <summary>
/// 数据库映射属性
/// </summary>
public class DBMapperProperty
{
/// <summary>
/// 数据库列名
/// </summary>
public string DBColumnName { get; set; }
/// <summary>
/// 是否是主键列
/// </summary>
public bool IsPrimaryKey { get; set; }
/// <summary>
/// 属性信息
/// </summary>
public PropertyInfo PropertyInfo { get; set; }
}
}
}

View Code

b4-数据库连接配置帮助类：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using WebApplication1.Enum;
using WebApplication1.Model;
namespace WebApplication1.Helper
{
/// <summary>
/// 配置帮助类
/// </summary>
public class ConfigHelper
{
private IConfiguration _Configuration = null;
private IMemoryCache _MemoryCache = null;
/// <summary>
/// 构造
/// </summary>
/// <param name="configuration"></param>
/// <param name="memoryCache"></param>
public ConfigHelper(IConfiguration configuration, IMemoryCache memoryCache)
{
this._Configuration = configuration;
this._MemoryCache = memoryCache;
}
private Dictionary<EDBConnectionType, ConfigDBConnection> _DBConnectionDic = null;
/// <summary>
/// 获取所有数据库连接字符串
/// </summary>
/// <returns></returns>
public Dictionary<EDBConnectionType, ConfigDBConnection> GetDBConnectionDic()
{
if (this._DBConnectionDic == null || this._DBConnectionDic.Count == 0)
{
this._DBConnectionDic = this._MemoryCache.GetOrCreate("DBConnection", cacheEntry =>
{
cacheEntry.SetAbsoluteExpiration(DateTimeOffset.Now.AddDays(1));
Dictionary<EDBConnectionType, ConfigDBConnection> connectionDic = new Dictionary<EDBConnectionType, ConfigDBConnection>(0);
IConfigurationSection dbSection = this._Configuration.GetSection("DBConnection");
string providerName = null;
EDBConnectionType connectionType = default;
foreach (IConfigurationSection item in dbSection.GetChildren())
{
providerName = item.GetValue<string>("ProviderName");
if (providerName.Equals(EDBConnectionType.SqlServer.ToString(), StringComparison.OrdinalIgnoreCase))
{
connectionType = EDBConnectionType.SqlServer;
}
else if (providerName.Equals(EDBConnectionType.MySql.ToString(), StringComparison.OrdinalIgnoreCase))
{
connectionType = EDBConnectionType.MySql;
}
else if (providerName.Equals(EDBConnectionType.PostgreSQL.ToString(), StringComparison.OrdinalIgnoreCase))
{
connectionType = EDBConnectionType.PostgreSQL;
}
connectionDic.Add(connectionType, new ConfigDBConnection
{
ProviderName = providerName,
ConnectionStr = item.GetValue<string>("ConnectionStr")
});
}
cacheEntry.SetValue(connectionDic);
return connectionDic;
});
}
return this._DBConnectionDic ?? new Dictionary<EDBConnectionType, ConfigDBConnection>(0);
}
}
}

View Code

b5-.net core-appsetting.json数据库配置示例：

借用Linq To SQL，Dapper和自定义实体属性映射构建数据库底层实体和仓储层代码

{
"DBConnection": [
{
"ProviderName": "SqlServer",
"ConnectionStr": "数据库连接串"
}
]
}

View Code

b6-Linq To SQL帮助类：

　　请参考我的文章：使用LINQ生成Where的SQL语句

 

使用示例：可查看仓储类。

 

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/281146.html

赞 (0)
0
上一篇 2022年8月20日
下一篇 2022年8月20日

相关推荐

发表回复

登录后才能评论