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