dataBase——链表查询

来源：互联网发布：杭州淘宝大学培训机构编辑：程序博客网时间：2024/06/01 08:00

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语句进行缓存（未实现）

0 0