public virtual ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.Map.FieldMap.PrimaryState.Key == null, "不指定排序字段时,需要设置主键ID"); strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name) : strOrderBySql); var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC"); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("SELECT {0}TOP {2} {1} FROM (SELECT TOP {3} * FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse); return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (string.IsNullOrWhiteSpace(strSelectSql)) { strSelectSql = "*"; } if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT {0}{1} FROM {2} {3} {4} LIMIT {5},{6}", strDistinctSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, pageSize * (pageIndex - 1), pageSize); return(this); }
public virtual ISqlParam InsertIdentity() { var strinsertAssemble = InsertVisitor.Visit(ExpBuilder.ExpAssign); Sql.AppendFormat("INSERT INTO {0} {1}", Name, strinsertAssemble); return(this); }
public virtual ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false) { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strTopSql = top > 0 ? string.Format("TOP {0} ", top) : string.Empty; var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } if (!isRand) { Sql.AppendFormat("SELECT {0}{1}{2} FROM {3} {4} {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); } else if (string.IsNullOrWhiteSpace(strOrderBySql)) { Sql.AppendFormat("SELECT {0}{1}{2}{5} FROM {3} {4} ORDER BY NEWID()", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",NEWID() as newid" : ""); } else { Sql.AppendFormat("SELECT {2} FROM (SELECT {0} {1} *{6} FROM {3} {4} ORDER BY NEWID()) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",NEWID() as newid" : ""); } return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct" : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && (ExpBuilder.Map.FieldMap.PrimaryState.Value == null || string.IsNullOrWhiteSpace(ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name)), "不指定排序字段时,需要设置主键ID"); strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name) : strOrderBySql); Sql.AppendFormat("SELECT {1} FROM (SELECT {0} {1},ROW_NUMBER() OVER({2}) as Row FROM {3} {4}) a WHERE Row BETWEEN {5} AND {6};", strDistinctSql, strSelectSql, strOrderBySql, Name, strWhereSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); return(this); }
public virtual ISqlParam Delete() { var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("DELETE FROM {0} {1}", DbProvider.KeywordAegis(Name), strWhereSql); return(this); }
public virtual ISqlParam Count(bool isDistinct = false) { var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("SELECT {0}Count(0) FROM {1} {2}", strDistinctSql, DbProvider.KeywordAegis(Name), strWhereSql); return(this); }
public virtual ISqlParam Update() { var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strAssemble = AssignVisitor.Visit(ExpBuilder.ExpAssign); // 主键如果有值、或者设置成只读条件,则自动转成条件 if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("UPDATE {0} SET {1} {2}", DbProvider.KeywordAegis(Name), strAssemble, strWhereSql); return(this); }
public virtual ISqlParam AddUp() { Check.IsTure(ExpBuilder.ExpAssign == null, "赋值的参数不能为空!"); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strAssemble = AssignVisitor.Visit(ExpBuilder.ExpAssign); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("UPDATE {0} SET {1} {2}", DbProvider.KeywordAegis(Name), strAssemble, strWhereSql); return(this); }
public override ISqlParam Insert() { base.Insert(); // 主键如果有值,则需要 SET IDENTITY_INSERT ON if (ExpBuilder.Map.FieldMap.PrimaryState.Key != null && !string.IsNullOrWhiteSpace(ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name)) { var indexHaveValue = new GetMemberVisitor().Visit(ExpBuilder.ExpAssign).Any(o => o.Member as PropertyInfo == ExpBuilder.Map.FieldMap.PrimaryState.Key); if (indexHaveValue) { Sql.AppendFormat("SET IDENTITY_INSERT {0} ON ; {1} ; SET IDENTITY_INSERT {0} OFF;", Name, Sql); } } return(this); }
public virtual ISqlParam Min() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); if (string.IsNullOrWhiteSpace(strSelectSql)) { strSelectSql = "0"; } if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("SELECT MIN({0}) FROM {1} {2}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql); return(this); }
public virtual ISqlParam ToEntity() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT TOP 1 {0} FROM {1} {2} {3}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); return(this); }
public override ISqlParam GetValue() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT {0} FROM {1} {2} {3} rownum <=1", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); return(this); }
public override ISqlParam InsertIdentity() { base.InsertIdentity(); Sql.AppendFormat(";SELECT last_insert_rowid();"); return(this); }
public override ISqlParam InsertIdentity() { base.InsertIdentity(); Sql.AppendFormat("SELECT @@IDENTITY;"); return(this); }