Ejemplo n.º 1
0
        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));
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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());
        }
Ejemplo n.º 4
0
 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;
     }
 }
Ejemplo n.º 5
0
        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]);
        }
Ejemplo n.º 6
0
        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;
            }
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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;
        }
Ejemplo n.º 11
0
        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();
            }
        }
Ejemplo n.º 12
0
        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;
        }
Ejemplo n.º 13
0
        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;
        }
Ejemplo n.º 14
0
        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);
            }
        }
Ejemplo n.º 15
0
        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;
        }
Ejemplo n.º 16
0
        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;
            }
        }