/// <summary> /// 生成查询数据记录数的Sql语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> protected virtual 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>(); List <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); string strSql = LeftJoinWhere(where, tables, masterTable, selectSql); return(strSql); }
/// <summary> /// 生成查询主键的SQL语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> protected virtual 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); var strSql = LeftJoinWhere(@where, tables, masterTable, selectSql); return(strSql); }
/// <summary> /// 生成查询语句 /// </summary> /// <returns></returns> protected virtual 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.ExtendedMySql ?? extended.ExtendedSql; foreach (FieldProperty item in tables) { extSql = extSql.RegexReplace(" " + item.TableName + ".", " " + item.TableAlias + ".", true) .RegexReplace("(" + item.TableName + ".", "(" + item.TableAlias + ".", true) .RegexReplace("=" + item.TableName + ".", "=" + item.TableAlias + ".", true); } 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); string strSql = LeftJoinWhere(where, tables, masterTable, selectSql); return(strSql); }