public void TestEntityQueryBasicUsage()
{
WhereClip where = (!!(UserGroup._.ID > 0)) & (!(UserGroup._.Name == "teddy"));
OrderByClip orderBy = UserGroup._.ID.Desc & UserGroup._.Name.Asc;
Console.WriteLine(where.ToString());
Console.WriteLine(orderBy.ToString());
Console.WriteLine(PropertyItem.ParseExpressionByMetaData(where.ToString(), new PropertyToColumnMapHandler(SimplePropertyToColumn), "[", "]", "@"));
Console.WriteLine(PropertyItem.ParseExpressionByMetaData(orderBy.ToString(), new PropertyToColumnMapHandler(SimplePropertyToColumn), "[", "]", "@"));
Console.WriteLine(UserGroup._.ID.In(1, 2, 3));
Console.WriteLine(UserGroup._.ID.Between(2, 6));
}
protected virtual DbCommand CreateSelectTopCommand(WhereClip where, string[] columns, int topCount)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT TOP ");
sb.Append(topCount);
sb.Append(' ');
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
sb.Append(where.ToString());
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public void TestWhereClipWithAtCharInStringParam()
{
WhereClip where = new WhereClip("{Name} = @Name and {ID} = @ID and {a} = @a", "*****@*****.**", "*****@*****.**", 1);
WhereClip flat = gateway.ToFlatWhereClip(where, new Order().GetEntityConfiguration());
Console.WriteLine(flat.ToString());
}
public void Filter(WhereClip where)
{
if (where == null || where == WhereClip.All)
{
FilterExpression = null;
}
else
{
string whereStr = where.ToString();
if (where.ParamValues != null)
{
foreach (object p in where.ParamValues)
{
System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex("(" + "@" + @"[\w\d_]+)");
if (p != null && p is string)
{
whereStr = r.Replace(whereStr, Util.FormatParamVal(p.ToString().Replace("@", "\007")), 1);
}
else
{
whereStr = r.Replace(whereStr, Util.FormatParamVal(p), 1);
}
}
}
whereStr = whereStr.Replace("\007", "@");
FilterExpression = whereStr;
}
}
public void TestSerializable()
{
PropertyItem pi = new PropertyItem("Teddy");
Assert.AreEqual(pi.PropertyName, ((PropertyItem)SerializationManager.Deserialize(typeof(PropertyItem), SerializationManager.Serialize(pi))).PropertyName);
OrderByClip orderBy = new OrderByClip("{ID} DESC");
Assert.AreEqual(orderBy.OrderBy, ((OrderByClip)SerializationManager.Deserialize(typeof(OrderByClip), SerializationManager.Serialize(orderBy))).OrderBy);
StoredProcedureParamItem spi = new StoredProcedureParamItem("Test");
Assert.AreEqual(spi.Name, ((StoredProcedureParamItem)SerializationManager.Deserialize(typeof(StoredProcedureParamItem), SerializationManager.Serialize(spi))).Name);
WhereClip where1 = WhereClip.All;
Assert.AreEqual(where1.ToString(), ((WhereClip)SerializationManager.Deserialize(typeof(WhereClip), SerializationManager.Serialize(where1))).ToString());
WhereClip where2 = new WhereClip("{ID} > @id and {Name} = @Name", 1, "test");
WhereClip s_where2 = (WhereClip)SerializationManager.Deserialize(typeof(WhereClip), SerializationManager.Serialize(where2));
Assert.AreEqual(where2.ToString(), s_where2.ToString());
Assert.AreEqual(where2.ParamValues[0], s_where2.ParamValues[0]);
Assert.AreEqual(where2.ParamValues[1], s_where2.ParamValues[1]);
}
protected void Page_Load(object sender, EventArgs e)
{
if (IntParam == 0)
{
return;
}
SeoSetting(SeoConfig.Config.DefaultSeo);
Web_UserAlbumColumn album = DbSession.Default.Get <Web_UserAlbumColumn>(Web_UserAlbumColumn._.ID == IntParam);
if (album == null || album.IsPublic == Library.PublicState.隐藏.ToString())
{
return;
}
TUsers user = DbSession.Default.Get <TUsers>(TUsers._.UserID == album.UserID);
if (user == null)
{
return;
}
AlbumName = album.AlbumName;
UserName = user.NickName; //user.UserName;
uid = user.UserID.ToString();
WhereClip where = VUsersAlbum._.ColumnID == IntParam && VUsersAlbum._.Enable == Library.EnableState.通过.ToString();
anpPageIndex.RecordCount = DbSession.Default.Count <VUsersAlbum>(where);
anpPageIndex.CurrentPageIndex = PageIndex;
//anpPageIndex.PageSize = 4;
DataTable dt = DbSession.Default.FromProc("Bzw_GetPager")
.AddInputParameter("@QueryStr", DbType.String, "select * from VUsersAlbum where " + string.Format(where.ToString(), "", ""))
.AddInputParameter("@PageSize", DbType.Int32, anpPageIndex.PageSize)
.AddInputParameter("@PageCurrent", DbType.Int32, PageIndex)
.AddInputParameter("@FdShow", DbType.String, "*")
.AddInputParameter("@FdOrder", DbType.String, "ShortNum desc,InputTime desc")
.ToDataTable();
if (dt.Rows.Count > 0)
{
rptAlbumColumnList.Visible = true;
rptAlbumColumnList.DataSource = dt;
rptAlbumColumnList.DataBind();
ltNonData.Visible = false;
}
else
{
rptAlbumColumnList.Visible = false;
ltNonData.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(un))
{
return;
}
SeoSetting(SeoConfig.Config.DefaultSeo);
//TUsers user = DbSession.Default.Get<TUsers>(TUsers._.UserName == un);
TUsers user = DbSession.Default.Get <TUsers>(TUsers._.UserID == un);
if (user == null)
{
return;
}
//绑定用户公开的相册列表
//string where = " where UserID=" + user.UserID;
usernickname = user.NickName;
WhereClip where = Web_vUserAlbumColumn._.UserID == user.UserID && Web_vUserAlbumColumn._.IsPublic == Library.PublicState.公开.ToString();
anpPageIndex.RecordCount = DbSession.Default.Count <Web_vUserAlbumColumn>(where);
anpPageIndex.CurrentPageIndex = PageIndex;
//anpPageIndex.PageSize = 4;
DataTable dt = DbSession.Default.FromProc("Bzw_GetPager")
.AddInputParameter("@QueryStr", DbType.String, "select * from Web_vUserAlbumColumn where " + string.Format(where.ToString(), "", ""))
.AddInputParameter("@PageSize", DbType.Int32, anpPageIndex.PageSize)
.AddInputParameter("@PageCurrent", DbType.Int32, PageIndex)
.AddInputParameter("@FdShow", DbType.String, "*")
.AddInputParameter("@FdOrder", DbType.String, "OrderID desc,InputTime desc")
.ToDataTable();
if (dt.Rows.Count > 0)
{
rptAlbumColumnList.Visible = true;
rptAlbumColumnList.DataSource = dt;
rptAlbumColumnList.DataBind();
ltNonData.Visible = false;
}
else
{
rptAlbumColumnList.Visible = false;
ltNonData.Visible = true;
}
}
protected override System.Data.Common.DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn)
{
//page split algorithm using ROW_NUMBER() in SqlServer 2005
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("WITH [__T] AS (SELECT ");
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append("TOP ");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROW_NUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
protected virtual DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn)
{
//SELECT TOP 10 *
//FROM TestTable
//WHERE (ID NOT IN
// (SELECT TOP 20 id
// FROM TestTable
// ORDER BY id))
//ORDER BY ID
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT ");
if (topCount < int.MaxValue)
{
sb.Append("TOP ");
sb.Append(topCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
WhereClip cloneWhere = (WhereClip) where.Clone();
#region Construct & extend CloneWhere
StringBuilder sbInside = new StringBuilder();
sbInside.Append(identyColumn);
sbInside.Append(" NOT IN (SELECT TOP ");
sbInside.Append(skipCount);
sbInside.Append(' ');
sbInside.Append(identyColumn);
sbInside.Append(" FROM ");
sbInside.Append(where.ToString());
sbInside.Append(")");
if (cloneWhere.Sql.Length == 0)
{
cloneWhere.Sql = sbInside.ToString();
}
else
{
cloneWhere.Sql = "(" + cloneWhere.Sql.ToString() + ") AND " + sbInside.ToString();
}
#endregion
sb.Append(cloneWhere.ToString());
AddExpressionParameters(where, cmd);
AddExpressionParameters(cloneWhere, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
protected override System.Data.Common.DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn)
{
//page split algorithm using ROW_NUMBER() in SqlServer 2005
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("WITH [__T] AS (SELECT ");
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append("TOP ");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROW_NUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!UserLoginInfo.IsLogin)
{
Response.Redirect("/");
}
TUsers model = DbSession.Default.Get <TUsers>(TUsers._.UserName == UserLoginInfo.UserName);
if (model == null)
{
Response.Redirect("/");
}
WhereClip where = VUsersAlbum._.UserID == model.UserID;
if (ColumnID != 0)
{
Web_UserAlbumColumn column = DbSession.Default.Get <Web_UserAlbumColumn>(Web_UserAlbumColumn._.ID == ColumnID);
if (column != null)
{
where &= VUsersAlbum._.ColumnID == ColumnID;
ColumnName = column.AlbumName;
}
}
anpPageIndex.RecordCount = DbSession.Default.Count <VUsersAlbum>(where);
anpPageIndex.CurrentPageIndex = PageIndex;
anpPageIndex.PageSize = anpPageIndex.PageSize;
DataTable dt = DbSession.Default.FromProc("Bzw_GetPager")
.AddInputParameter("@QueryStr", DbType.String, "select * from VUsersAlbum where " + string.Format(where.ToString(), "", ""))
.AddInputParameter("@PageSize", DbType.Int32, anpPageIndex.PageSize)
.AddInputParameter("@PageCurrent", DbType.Int32, PageIndex)
.AddInputParameter("@FdShow", DbType.String, "*")
.AddInputParameter("@FdOrder", DbType.String, "InputTime desc")
.ToDataTable();
//DataTable dt = DbSession.Default.From<Web_UserAlbumColumn>().Where(Web_UserAlbumColumn._.UserID == model.UserID).OrderBy(Web_UserAlbumColumn._.InputTime.Desc).ToDataTable();
if (dt.Rows.Count > 0)
{
rptAlbumColumnList.DataSource = dt;
rptAlbumColumnList.DataBind();
}
}
protected virtual DbCommand CreateSelectRangeCommandForSortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool isIdentyColumnDesc)
{
//SELECT TOP 10 *
//FROM TestTable
//WHERE (ID >
// (SELECT MAX(id)/MIN(id)
// FROM (SELECT TOP 20 id
// FROM TestTable
// ORDER BY id) AS T))
//ORDER BY ID
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT ");
if (topCount < int.MaxValue)
{
sb.Append("TOP ");
sb.Append(topCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
WhereClip cloneWhere = (WhereClip)where.Clone();
#region Construct & extend CloneWhere
StringBuilder sbInside = new StringBuilder();
sbInside.Append(identyColumn);
sbInside.Append(isIdentyColumnDesc ? '<' : '>');
sbInside.Append('(');
sbInside.Append("SELECT ");
sbInside.Append(isIdentyColumnDesc ? "MIN(" : "MAX(");
sbInside.Append("[__T].");
string[] splittedIdentyColumn = identyColumn.Split('.');
sbInside.Append(splittedIdentyColumn[splittedIdentyColumn.Length - 1]);
sbInside.Append(") FROM (SELECT TOP ");
sbInside.Append(skipCount);
sbInside.Append(' ');
sbInside.Append(identyColumn);
sbInside.Append(" AS ");
sbInside.Append(splittedIdentyColumn[splittedIdentyColumn.Length - 1]);
sbInside.Append(" FROM ");
sbInside.Append(where.ToString());
sbInside.Append(") [__T])");
if (cloneWhere.Sql.Length == 0)
{
cloneWhere.Sql = sbInside.ToString();
}
else
{
cloneWhere.Sql = "(" + cloneWhere.Sql.ToString() + ") AND " + sbInside.ToString();
}
#endregion
sb.Append(cloneWhere.ToString());
AddExpressionParameters(where, cmd);
AddExpressionParameters(cloneWhere, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
protected virtual DbCommand CreateSelectTopCommand(WhereClip where, string[] columns, int topCount)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT TOP ");
sb.Append(topCount);
sb.Append(' ');
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
sb.Append(where.ToString());
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
//Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
//Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
//Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair <string, bool>[] { new KeyValuePair <string, bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return(CreateSelectCommand(where, columns));
}
else
{
//Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
//page split algorithm using ROW_NUMBER() in Oracle9+
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM (");
sb.Append("SELECT ");
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROW_NUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
}
protected virtual DbCommand CreateSelectRangeCommandForSortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool isIdentyColumnDesc)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT ");
if (topCount < int.MaxValue)
{
sb.Append("TOP ");
sb.Append(topCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
WhereClip cloneWhere = (WhereClip)where.Clone();
#region Construct & extend CloneWhere
StringBuilder sbInside = new StringBuilder();
sbInside.Append(identyColumn);
sbInside.Append(" NOT IN (SELECT TOP ");
sbInside.Append(skipCount);
sbInside.Append(' ');
sbInside.Append(identyColumn);
sbInside.Append(" FROM ");
sbInside.Append(where.ToString());
sbInside.Append(")");
if (cloneWhere.Sql.Length == 0)
{
cloneWhere.Sql = sbInside.ToString();
}
else
{
cloneWhere.Sql = "(" + cloneWhere.Sql.ToString() + ") AND " + sbInside.ToString();
}
#endregion
sb.Append(cloneWhere.ToString());
AddExpressionParameters(where, cmd);
AddExpressionParameters(cloneWhere, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair<string,bool>[] { new KeyValuePair<string,bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return CreateSelectCommand(where, columns);
}
else
{
Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
//page split algorithm using ROWNUMBER()
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM (");
sb.Append("SELECT ");
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROWNUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
}