CREATE DEFINER=`root`@`%` PROCEDURE `sp_GeneralPaging`(
    #輸入?yún)?shù)
    _fields VARCHAR(200), #要查詢的字段，用逗號(hào)(,)分隔
    _tables VARCHAR(200),  #要查詢的表
    _where VARCHAR(200),   #查詢條件
    _orderby VARCHAR(200),  #排序規(guī)則
    _pageindex INT,  #查詢頁碼
    _pagesize INT,   #每頁記錄數(shù)
    _sumfields VARCHAR(200),#求和字段
    #輸出參數(shù)
    OUT _totalcount INT,  #總記錄數(shù)
    OUT _pagecount INT,    #總頁數(shù)
    OUT _sumResult VARCHAR(200)#求和結(jié)果
)
BEGIN
    #140529-xxj-分頁存儲(chǔ)過程
    #計(jì)算起始行號(hào)
    SET @startRow = _pageSize * (_pageIndex - 1);
    SET @pageSize = _pageSize;
    SET @rowindex = 0; #行號(hào)
 
    #合并字符串
    SET @strsql = CONCAT(
        #'select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,' #記錄行號(hào)
        'select sql_calc_found_rows '
        ,_fields
        ,' from '
        ,_tables
        ,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END
        ,CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END
      ,' limit ' 
        ,@startRow
        ,',' 
        ,@pageSize
    );
 
    PREPARE strsql FROM @strsql;#定義預(yù)處理語句 
    EXECUTE strsql;                            #執(zhí)行預(yù)處理語句 
    DEALLOCATE PREPARE strsql;    #刪除定義 
    #通過 sql_calc_found_rows 記錄沒有使用 limit 語句的記錄，使用 found_rows() 獲取行數(shù)
    SET _totalcount = FOUND_ROWS();
 
    #計(jì)算總頁數(shù)
    IF (_totalcount <= _pageSize) THEN
        SET _pagecount = 1;
    ELSE IF (_totalcount % _pageSize > 0) THEN
        SET _pagecount = (_totalcount +  _pageSize - 1) / _pageSize;
    ELSE
        SET _pagecount = _totalcount / _pageSize;
    END IF;
    END IF;
 
    #計(jì)算求和字段
    IF (IFNULL(_sumfields, '') <> '') THEN
        #序列sum結(jié)果
        SET @sumCols = CONCAT (
            'CONCAT_WS(\',\','
            ,'SUM('
            ,REPLACE(_sumfields,',','),SUM(')
            ,'))');
        #拼接字符串
        SET @sumsql = CONCAT(
            'select '
            ,@sumCols
            ,' INTO @sumResult from '
            ,_tables
            ,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END
            ,';'
        );
        #select @sumsql;
        PREPARE sumsql FROM @sumsql;#定義預(yù)處理語句 
        EXECUTE sumsql;    
        SET _sumResult = @sumResult;                        #執(zhí)行預(yù)處理語句 
        DEALLOCATE PREPARE sumsql;    #刪除定義 
 
    END IF;
 
END

       protected DataTable getDataTableFromSP(int currentIndex, int pageSize, out int count)
        {
            string strConnection = ConfigurationManager.AppSettings["MySQLConnection"];
            MySqlConnection connect = new MySqlConnection(strConnection);

            MySqlParameter[] parameters = {
                    new MySqlParameter("?_fields", MySqlDbType.VarChar, 200),
                    new MySqlParameter("?_tables", MySqlDbType.VarChar, 200),
                    new MySqlParameter("?_where", MySqlDbType.VarChar, 200),
                    new MySqlParameter("?_orderby", MySqlDbType.VarChar, 200),
                    new MySqlParameter("?_pageindex", MySqlDbType.Int32),
                    new MySqlParameter("?_pagesize", MySqlDbType.Int32),
                    new MySqlParameter("?_sumfields", MySqlDbType.VarChar, 200),
                    new MySqlParameter("?_totalcount", MySqlDbType.Int32),
                    new MySqlParameter("?_pagecount", MySqlDbType.Int32),
                    new MySqlParameter("?_sumResult", MySqlDbType.VarChar, 200)
                    };
            parameters[0].Value = "GameID,GameNO,Name,EnName,Language,Description,OrderID,Platform";
            parameters[1].Value = "game";
            parameters[2].Value = "Platform=1";
            parameters[3].Value = "GameID";
            parameters[4].Value = currentIndex;
            parameters[5].Value = pageSize;
            parameters[6].Value = "OrderID";
            parameters[7].Direction = ParameterDirection.Output;
            parameters[8].Direction = ParameterDirection.Output;
            parameters[9].Direction = ParameterDirection.Output;

            connect.Open();
            MySqlCommand cmd = new MySqlCommand("sp_GeneralPaging", connect);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            MySqlDataReader sdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(sdr);
            connect.Close();

            System.Diagnostics.Debug.WriteLine(parameters[7].Value + "|" + parameters[8].Value + "|" + parameters[9].Value);

            count = Convert.ToInt32(parameters[7].Value);

            return dt;
        }