一个数据库分页的例子

来源：互联网发布：wifi定位软件编辑：程序博客网时间：2024/05/01 21:14

private const string SQL_SEARCH_COUNT = "SELECT COUNT(*) AS COUNT FROM [WSE_CompanyCertificate] WHERE ( 1 = 1 ) ";
private const string SQL_SEARCH_IN = @"
SELECT [ID]
,( ROW_NUMBER() OVER (ORDER BY [UserID] , [Type] , [OrderNumber] ) -1 ) AS RowNumber
FROM [WSE_CompanyCertificate]
WHERE (1=1)";

private const string SQL_SEARCH_OUT = @"
WITH OrderedTable AS ({0})
SELECT n.*
FROM OrderedTable o LEFT OUTER JOIN
[WSE_CompanyCertificate] n ON o.[ID] = n.[ID]
WHERE o.[RowNumber] BETWEEN @PageLower AND @PageUpper
ORDER BY o.RowNumber
";

        private const string SQL_SEARCH_USER_ID = " AND ( [UserID] = @UserID ) ";
        private const string SQL_SEARCH_Type = " AND ( [Type] = @Type ) ";
        private const string SQL_SEARCH_VALIDATE = " AND ( [validate] = @validate) ";

        [Microsoft.SqlServer.Server.SqlProcedure(Name = "WSE_COMPANY_CERTIFICATE_SEARCH_3")]
        public static void Search(out int AllCount, int PageIndex, int PageSize,
            int UserID, int Type, int validate)
        {
            DateTime dtStart = DateTime.Now;

            using (SqlConnection cn = new SqlConnection(SqlConn.SQL_CON))
            {
                cn.Open();
                SqlCommand cmdCount = new SqlCommand();
                SqlCommand cmdSearch = new SqlCommand();
                cmdCount.Connection = cn;
                cmdSearch.Connection = cn;

                string strWhere = "";
                if (UserID > 0)
                {
                    strWhere += SQL_SEARCH_USER_ID;

                    cmdSearch.Parameters.AddWithValue(PARM_USER_ID, UserID);
                    cmdCount.Parameters.AddWithValue(PARM_USER_ID, UserID);
                }
                if (Type == 0 || Type == 1)
                {
                    strWhere += SQL_SEARCH_Type;

                    cmdSearch.Parameters.AddWithValue(PARM_TYPE,Type);
                    cmdCount.Parameters.AddWithValue(PARM_TYPE,Type);
                }
                if (validate == -1 || validate == 0 || validate == 1)
                {
                    strWhere += SQL_SEARCH_VALIDATE;

                    cmdSearch.Parameters.AddWithValue(PARM_VALIDATE, validate);
                    cmdCount.Parameters.AddWithValue(PARM_VALIDATE, validate);
                }

                string strSQL = SQL_SEARCH_COUNT + strWhere;

                cmdCount.CommandText = strSQL;
                DBUtility.WriteSQL(cmdCount);
                AllCount = Convert.ToInt32(cmdCount.ExecuteScalar());

                strSQL = SQL_SEARCH_IN + strWhere;
                strSQL = string.Format(SQL_SEARCH_OUT, strSQL);

                cmdSearch.CommandText = strSQL;

                int intPageLower = PageSize * PageIndex;
                int intPageUpper = intPageLower + PageSize - 1;
                cmdSearch.Parameters.AddWithValue(Constant.PARM_PAGE_UPPER, intPageUpper);
                cmdSearch.Parameters.AddWithValue(Constant.PARM_PAGE_LOWER, intPageLower);
                DBUtility.WriteSQL(cmdSearch);
                SqlContext.Pipe.ExecuteAndSend(cmdSearch);
            }
            TimeSpan ts = DateTime.Now - dtStart;
            SqlContext.Pipe.Send(string.Format("{0}:{1}", ts.Seconds, ts.Milliseconds));
        }