DELIMITER $$USE `tsb_asksys`$$DROP PROCEDURE IF EXISTS `P_viewPage`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `P_viewPage`(         $TableName VARCHAR(200),/*表名*/       $FieldList VARCHAR(2000),  /*顯示列名*/       $PrimaryKey VARCHAR(100),  /*單一主鍵或唯一值鍵*/       $WhereStr VARCHAR(1000),  /*查詢條件 不含'where'字符*/       $OrderStr VARCHAR(1000),  /*排序 不含'order by'字符，如id asc,userid desc，當(dāng)@SortType=3時(shí)生效*/       $SortType INT,  /*排序規(guī)則 1:正序asc 2:倒序desc 3:多列排序*/       $RecorderCount INT,  /*記錄總數(shù) 0:會(huì)返回總記錄*/       $PageSize INT,  /*每頁(yè)輸出的記錄數(shù)*/       $PageIndex INT,  /*當(dāng)前頁(yè)數(shù)*/OUT    $TotalCount INTEGER,  /*返回記錄總數(shù)*/OUT    $TotalPageCount INTEGER  /*返回總頁(yè)數(shù)*/)BEGIN  IF !(($TableName IS NULL OR $TableName='') OR ($FieldList IS NULL OR $FieldList='') OR ($PrimaryKey IS NULL OR $PrimaryKey='') OR $SortType < 1 OR $SortType >3 OR $RecorderCount < 0 OR $PageSize < 0 OR $PageIndex < 0) THEN  IF ($WhereStr IS NULL OR $WhereStr='') THEN    SET @new_where1 = ' ' ;    SET @new_where2 = ' WHERE ' ;  ELSE    SET @new_where1 =CONCAT(' WHERE ',$WhereStr);    SET @new_where2 =CONCAT(' WHERE ',$WhereStr,' AND ');  END IF;  IF $OrderStr='' OR $SortType = 1 OR $SortType = 2 THEN    IF $SortType = 1 THEN         SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );      END IF;    IF $SortType = 2 THEN         SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' DESC');      END IF;  ELSE    SET @new_order =CONCAT(' ORDER BY ',$OrderStr);  END IF;  SET @SqlCount = CONCAT('SELECT COUNT(*) into @TotalCount FROM ',$TableName,@new_where1);  SET @SqlCount1 = CONCAT('SELECT CEILING((COUNT(*)+0.0)/',$PageSize,') into @TotalPageCount FROM ',$TableName,@new_where1);  IF $RecorderCount = 0 THEN       PREPARE stmt1 FROM @SqlCount;       EXECUTE stmt1;       SET $TotalCount=@TotalCount;       PREPARE stmt1 FROM @SqlCount1;       EXECUTE stmt1;       SET $TotalPageCount=@TotalPageCount;  ELSE    SET $TotalCount = $RecorderCount;  END IF;  IF $PageIndex > CEILING(($TotalCount+0.0)/$PageSize) THEN    SET $PageIndex = CEILING(($TotalCount+0.0)/$PageSize);  END IF;  IF $PageIndex = 0 OR $PageIndex = 1 THEN     SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize);  ELSE    IF $SortType = 1 THEN         SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);    END IF;    IF $SortType = 2 THEN         SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' < (SELECT MIN(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);      END IF;    IF $SortType = 3 THEN        /*IF INSTR($OrderStr,',') > 0 THEN              SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' NOT IN (SELECT ',$PrimaryKey,' FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) a)',@new_order,' limit ',$PageSize);        ELSE              SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );              SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);        END IF;*/      SET @Sql=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' NOT IN (SELECT ',$PrimaryKey,' FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) a)',@new_order,' limit ',$PageSize);  END IF;  END IF;  PREPARE stmt2 FROM @Sql;  EXECUTE stmt2;  END IF;  END$$DELIMITER ;

static public DataTable getAllUser(int PageSize, int CurrPageIndex, out int AllRecordCount, String _SearchWhere)        {            using (MySQLCommand cmd = new MySqlCommand("P_viewPage"))            {                cmd.CommandType = CommandType.StoredProcedure;                //設(shè)置表名稱：                System.Text.StringBuilder TableName = new System.Text.StringBuilder();                TableName.Append(" GQShowV_UserInfoForAdmin t1 ");                cmd.Parameters.AddWithValue("@$TableName", TableName.ToString());//對(duì)表名參數(shù)賦值                //設(shè)置字段名稱：                System.Text.StringBuilder myfld_str = new System.Text.StringBuilder();                myfld_str.Append("  t1.RecordID, t1.UserID, t1.LoginID, t1.Nickname,t1.SexID_Exp,t1.UserEmail_Exp,t1.CreateDate,t1.UserName,t1.Phone, t1.Tel,t1.IsAdmin_Exp ");                cmd.Parameters.AddWithValue("@$FieldList", myfld_str.ToString());//對(duì)字段參數(shù)賦值                //設(shè)置按哪個(gè)字段進(jìn)行排序                cmd.Parameters.AddWithValue("@$OrderStr", "t1.createDate DESC");// --排序 不含'order by'字符，如id asc,userid desc，當(dāng)@SortType=3時(shí)生效                /*設(shè)置排序方式，規(guī)則說明：1是按照PrimaryKey正序asc；2按照PrimaryKey倒序desc；                3按照非關(guān)鍵字或多關(guān)鍵字排序，比如日期，排序字段后可跟asc或desc，如t1.createDate DESC或t1.createDate DESC,t1.other asc                另外請(qǐng)注意：排序方式只有1,2,3，其他數(shù)字不會(huì)從數(shù)據(jù)庫(kù)中讀出數(shù)據(jù)*/                cmd.Parameters.AddWithValue("@$SortType", 3);//--排序規(guī)則 1:正序asc 2:倒序desc 3:多列排序                //設(shè)置主表的關(guān)鍵字，切記：主鍵前不要加前綴，否則第二頁(yè)及以后頁(yè)內(nèi)容無法讀出                cmd.Parameters.AddWithValue("@$PrimaryKey", "UserID");//t1.UserID 第二頁(yè)不會(huì)顯示出來，因?yàn)橛星熬Y                //設(shè)置總查詢記錄數(shù)：為0時(shí)，查詢實(shí)際記錄，主要解決表記錄非常大，那么可以限制只查詢10萬條記錄這樣的問題                cmd.Parameters.AddWithValue("@$RecorderCount", 0);                //設(shè)置查詢條件                System.Text.StringBuilder query_where = new StringBuilder();//保存查詢條件                 query_where.Append(" (1 = 1) ");                if (!String.IsNullOrEmpty(_SearchWhere))                {                    query_where.AppendFormat("and (t1.UserName LIKE N'%{0}%')", _SearchWhere);                }                cmd.Parameters.AddWithValue("@$WhereStr", query_where.ToString());//設(shè)置查詢條件                //設(shè)置每頁(yè)要顯示的記錄數(shù)                cmd.Parameters.AddWithValue("@$PageSize", PageSize);                //設(shè)置要顯示的頁(yè)碼：                cmd.Parameters.AddWithValue("@$PageIndex", CurrPageIndex);                //保存查詢到的總記錄數(shù)                MySqlParameter record_num = new MySqlParameter();                record_num = cmd.Parameters.Add(new MySqlParameter("@$TotalCount", SqlDbType.Int));                record_num.Direction = ParameterDirection.Output;                //                int _TotalPageCount = 0;                MySqlParameter TotalPageCount = new MySqlParameter();                TotalPageCount = cmd.Parameters.Add(new MySqlParameter("@$TotalPageCount", SqlDbType.Int));                TotalPageCount.Direction = ParameterDirection.Output;                using (DataTable dt = ObjectMindDBForMysql.MySqlHelper.GetData(cmd))                {                    AllRecordCount = int.Parse(cmd.Parameters["@$TotalCount"].Value.ToString());                    _TotalPageCount = int.Parse(cmd.Parameters["@$TotalPageCount"].Value.ToString());                    return dt;                }            }        }