/// <summary>
/// 生成查询主键的SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
internal string GenerateSelectPkSql <T>(string where = "")
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var selectSql = new StringBuilder("SELECT ");
List <FieldProperty> fpmap = FieldProperty.GetFieldPropertys <T>();
List <FieldProperty> tables = FieldProperty.GetTables <T>();
FieldProperty masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));
var ps = typeof(T).GetProperties();
var pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (pk == null)
{
return(string.Empty);
}
selectSql.AppendFormat("{0}.{1}", masterTable.TableAlias, pk.Name);
//selectSql.Append(pk.Name);
selectSql.AppendLine();
selectSql.AppendLine(" FROM ");
selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);
foreach (FieldProperty item in tables)
{
if (!string.IsNullOrEmpty(where))
{
where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
.Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
.Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
}
if (item.TableAlias == masterTable.TableAlias)
{
continue;
}
selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
item.TableAlias, item.RelateField);
selectSql.AppendLine();
}
string strSql = selectSql + where;
return(strSql);
}
/// <summary>
/// 生成查询数据记录数的Sql语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
internal string GenerateCountSql <T>(string where = "")
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var selectSql = new StringBuilder("SELECT COUNT(1) ");
ICollection <FieldProperty> fpmap = FieldProperty.GetFieldPropertys <T>();
ICollection <FieldProperty> tables = FieldProperty.GetTables <T>();
FieldProperty masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));
selectSql.AppendLine(" FROM ");
string leftBrackets = "".PadLeft(tables.Count - 1, '(');
selectSql.Append(" " + leftBrackets);
selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);
foreach (FieldProperty item in tables)
{
if (!string.IsNullOrEmpty(where))
{
where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
.Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".");
}
if (item.TableAlias == masterTable.TableAlias)
{
continue;
}
selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
item.TableAlias, item.RelateField);
selectSql.AppendLine();
}
string strSql = selectSql + where;
return(strSql);
}
//去除重复记录，保证表和主表关联字段的组合只有一个
//得到的结果就是要关联的表
private static List <FieldProperty> Distinct(List <FieldProperty> list)
{
var result = new FieldProperty[list.Count];
list.CopyTo(result);
List <FieldProperty> resultList = result.ToList();
for (int i = 0; i < resultList.Count; i++)
{
for (int j = i + 1; j < resultList.Count; j++)
{
if (resultList[i].TableName == resultList[j].TableName &&
resultList[i].MasterTableField == resultList[j].MasterTableField)
{
resultList.RemoveAt(j);
j--;
}
}
}
return(resultList);
}
/// <summary>
/// 生成查询语句
/// </summary>
/// <returns></returns>
internal string GenerateSelectSql(Type type, string where = "")
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var selectSql = new StringBuilder("SELECT ");
List <FieldProperty> fpmap = FieldProperty.GetFieldPropertys(type);
List <FieldProperty> tables = FieldProperty.GetTables(type);
FieldProperty masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));
foreach (FieldProperty item in fpmap)
{
if (string.IsNullOrEmpty(item.FieldAlias))
{
selectSql.AppendFormat("{0}.{1},", item.TableAlias, item.FieldName);
}
else
{
selectSql.AppendFormat("{0}.{1} {2},", item.TableAlias, item.FieldName, item.FieldAlias);
}
}
//增加扩展字段
//Type type = typeof(T);
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
var extSql = extended.ExtendedSql;
foreach (FieldProperty item in tables)
{
extSql = extSql.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
.Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
.Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
}
selectSql.Append("(" + extSql + ") " + info.Name + ",");
}
}
selectSql = selectSql.Remove(selectSql.Length - 1, 1);
selectSql.AppendLine();
selectSql.AppendLine(" FROM ");
selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);
foreach (FieldProperty item in tables)
{
if (!string.IsNullOrEmpty(where))
{
where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
.Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
.Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
}
if (item.TableAlias == masterTable.TableAlias)
{
continue;
}
selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
item.TableAlias, item.RelateField);
selectSql.AppendLine();
}
string strSql = selectSql + where;
return(strSql);
}
internal static List <FieldProperty> GetFieldPropertys(Type t)
{
var fplist = new List <FieldProperty>();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t);
PropertyInfo[] ps = t.GetProperties();
#region 取出映射关系
foreach (PropertyInfo item in ps)
{
var fp = new FieldProperty();
var excludeFieldAttribute = (ExcludeFieldAttribute)Attribute.GetCustomAttribute(item, typeof(ExcludeFieldAttribute));
if (excludeFieldAttribute != null)
{
continue;
}
ExtendedAttribute extendedAttribute = ExtendedAttribute.GetAttribute(item);
if (extendedAttribute != null)
{
continue;
}
RefFieldAttribute fieldattr = RefFieldAttribute.GetAttribute(item);
if (fieldattr != null)
{
fp.TableName = fieldattr.RefTableName;
fp.FieldName = fieldattr.RefFieldName ?? item.Name;
fp.FieldAlias = item.Name;
fp.PropertyName = item.Name;
fp.MasterTableField = fieldattr.MasterTableField;
fp.RelateField = fieldattr.RefTableKey;
fplist.Add(fp);
}
else
{
fp.TableName = tableInfo == null ? t.Name : tableInfo.TableName;
fp.FieldName = item.Name;
fp.PropertyName = item.Name;
fplist.Add(fp);
}
}
#endregion
List <FieldProperty> tables = Distinct(fplist);
for (int i = 0; i < tables.Count; i++)
{
string alias = "t" + i;
foreach (FieldProperty item in fplist)
{
if (item.TableName == tables[i].TableName && item.MasterTableField == tables[i].MasterTableField)
{
item.TableAlias = alias;
//if (string.IsNullOrEmpty(item.FieldAlias))
// item.FieldAlias = alias + "_" + item.FieldName;
}
}
}
return(fplist);
}
