1、添加属性特性
1)链表查询的特性
[AttributeUsage( AttributeTargets .Property)]
public class RefFieldAttribute : Attribute
{
public RefFieldAttribute()
{
}
/// <summary>
/// </summary>
/// <param name="masterTableField"> 主表的外键 </param>
/// <param name="refTableName"> 引用表名称 </param>
/// <param name="refTableKey"> 引用表主键 </param>
public RefFieldAttribute(string masterTableField, string refTableName, string refTableKey)
{
RefTableName = refTableName;
RefTableKey = refTableKey;
MasterTableField = masterTableField;
}
/// <summary>
/// 引用表的名称
/// </summary>
public string RefTableName { get; set ; }
/// <summary>
/// 引用表的键
/// </summary>
public string RefTableKey { get; set ; }
/// <summary>
/// 主表的外键
/// </summary>
public string MasterTableField { get; set ; }
/// <summary>
/// 引用的字段
/// </summary>
public string RefFieldName { get; set ; }
public static RefFieldAttribute GetAttribute( MemberInfo item)
{
var refFieldAttribute = (RefFieldAttribute )GetCustomAttribute(item, typeof (RefFieldAttribute ));
return refFieldAttribute;
}
}
2)实体类名与数据库表名不一致时标致特性
[AttributeUsage( AttributeTargets .Class)]
[System.Serializable]
public class TableInfoAttribute : Attribute
{
public TableInfoAttribute(string tableName)
{
TableName = tableName;
}
/// <summary>
/// 数据库中表的名称
/// </summary>
public string TableName { get; set ; }
/// <summary>
/// 获取元数据的特性
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static TableInfoAttribute GetAttribute( Type item)
{
var excludeFieldAttribute = (TableInfoAttribute )GetCustomAttribute(item, typeof( TableInfoAttribute ));
return excludeFieldAttribute;
}
}
2、为字段添加特性
[ Serializable ()]
[TableInfo( "Sys_RolePermission" )]
public class Sys_RolePermission
{
public Sys_RolePermission(){}
public int RMId{ get; set ; }
public int TenantId{ get; set ; }
public int PermissionId{ get; set ; }
public int RoleId{ get; set ; }
public string OperateIds{ get; set ; }
private int totalCount;
[ Extended ("select count(1) from Sys_RolePermission" )]
[ ExtendFiled ]
public int ToTalCount { get { return totalCount; } set { totalCount = value ; } }
private string roleName;
[ RefField (MasterTableField = "RoleId" , RefTableKey = "RoleId", RefFieldName = "RoleName" , RefTableName = "Sys_Roles")]
[ ExtendFiled ]
public string RoleName {get { return roleName; }set {roleName = value; } }
}
3、通过反射生成sql语句
1)准备链表查询的辅助类
public
class TablePrimary {
private TablePrimary()
{
}
/// <summary>
/// 引用表名
/// </summary>
public string TableName { get; private set ; }
private string _masterField = string.Empty;
/// <summary>
/// 关联字段中主表的字段
/// </summary>
public string MasterTableField
{
get { return _masterField; }
private set { _masterField = value; }
}
/// <summary>
/// 引用表和主表的相关联的字段
/// <para> 一般为主键 </para>
/// </summary>
public string RelateField { get; private set ; }
/// <summary>
/// 添加的字段名
/// </summary>
public string FieldName { get; private set ; }
/// <summary>
/// 添加的字段别名
/// </summary>
public string FieldAlias { get; set ; }
/// <summary>
/// 对应实体属性
/// </summary>
private string PropertyName { get; set ; }
internal static List< TablePrimary > GetTablePrimary(Type type){
TableInfoAttribute tableinfoAttribute = TableInfoAttribute .GetAttribute(type);
PropertyInfo [] info = type.GetProperties();
List <TablePrimary > list = new List< TablePrimary >();
foreach (PropertyInfo v in info) {
ExtendedAttribute extendedAttribute = ExtendedAttribute .GetAttribute(v);
if (extendedAttribute != null )
continue ;
RefFieldAttribute refFieldAttribute = RefFieldAttribute .GetAttribute(v);
TablePrimary tp = new TablePrimary();
if (refFieldAttribute != null )
{
tp.TableName = refFieldAttribute.RefTableName; //外键表名
tp.MasterTableField = refFieldAttribute.MasterTableField; //主键字段
tp.RelateField = refFieldAttribute.RefTableKey; //外键字段
tp.FieldName = refFieldAttribute.RefFieldName ?? v.Name; //添加外键的字段
tp.FieldAlias = v.Name; //添加字段别名
tp.PropertyName = v.Name; //实体类的属性名
list.Add(tp);
}
else {
tp.TableName = tableinfoAttribute == null ? type.Name : tableinfoAttribute.TableName;
tp.FieldName = v.Name;
tp.PropertyName = v.Name;
list.Add(tp);
}
}
return list;
}
internal static List< TablePrimary > GetDistinct(List <TablePrimary> list) {
TablePrimary [] copyList = new TablePrimary[list.Count];
list.CopyTo(copyList);
List <TablePrimary > result = copyList.ToList();
for (int i = 0; i < result.Count; i++)
{
for (int j = i+1; j < result.Count; j++)
{ //外键表名、主键字段
if (result[i].TableName == result[j].TableName &&
result[i].MasterTableField == result[j].MasterTableField
) {
result.Remove(result[j]);
j--;
}
}
}
return result;
}
}
2)生成sql语句方法
public string SelectJoinSql(Type type , string where,DbType dbType)
{
if (string .IsNullOrEmpty(where)) {
if (!where.TrimStart().StartsWith("WHERE" , StringComparison .CurrentCultureIgnoreCase)) {
where = "Where " + where;
}
}
StringBuilder sql = new StringBuilder( "SELECT " );
List <TablePrimary > tablePrimary = TablePrimary .GetTablePrimary(type);
List <TablePrimary > tables = TablePrimary .GetDistinct(tablePrimary);
foreach (TablePrimary v in tablePrimary)
{ //是否有外键的字段
if (string .IsNullOrEmpty(v.FieldName))
{
sql.AppendFormat( "{0}.{1}," , v.TableName, v.FieldName);
}
else {
sql.AppendFormat( "{0}.{1} {2}," , v.TableName, v.FieldName, v.FieldAlias);
}
}
//添加的sql语句
PropertyInfo [] infos = type.GetProperties();
foreach (PropertyInfo pro in infos) {
ExtendedAttribute extended = ExtendedAttribute .GetAttribute(pro);
if (extended != null ) {
var extSql = (dbType == DbType .MySql) ? extended.ExtendedMySql : extended.ExtendedSql;
sql.Append( "(" + extSql + ") " + pro.Name + ",");
}
}
TableInfoAttribute tableInfo = TableInfoAttribute .GetAttribute(type);
string tableName = tableInfo==null ?type.Name:tableInfo.TableName,joinOn = "";
sql = sql.Remove(sql.Length - 1, 1);
sql.AppendLine();
sql.AppendFormat( " FROM {0} " ,tableName);
//连表
foreach (TablePrimary v in tables) {
if (!string .IsNullOrEmpty(v.MasterTableField))
{
joinOn = string .Format(" {0}.{1} = {2}.{3} " , tableName, v.MasterTableField, v.TableName, v.RelateField);
sql.AppendFormat( "JOIN {0} ON ({1}) " , v.TableName,joinOn);
}
}
sql.Append(where);
return sql.ToString();
}
注:1、ExtendedAttribute(sql) 通过sql语句生成的一个属性,如求数量、综合之类
2、在为实体类的属性添加链表的特性时,填入数据要与数据库一致
3、可对sql语句进行缓存(未实现)