/// <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);
}
/// <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);
}
