CREATE DEFINER=`root`@`%` PROCEDURE `sp_GeneralPaging`(    #输入参数    _fields VARCHAR(200), #要查询的字段，用逗号(,)分隔    _tables VARCHAR(200),  #要查询的表    _where VARCHAR(200),   #查询条件    _orderby VARCHAR(200),  #排序规则    _pageindex INT,  #查询页码    _pagesize INT,   #每页记录数    _sumfields VARCHAR(200),#求和字段    #输出参数    OUT _totalcount INT,  #总记录数    OUT _pagecount INT,    #总页数    OUT _sumResult VARCHAR(200)#求和结果)BEGIN    #140529-xxj-分页存储过程    #计算起始行号    SET @startRow = _pageSize * (_pageIndex - 1);    SET @pageSize = _pageSize;    SET @rowindex = 0; #行号     #合并字符串    SET @strsql = CONCAT(        #'select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,' #记录行号        '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;#定义预处理语句     EXECUTE strsql;                            #执行预处理语句     DEALLOCATE PREPARE strsql;    #删除定义     #通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数    SET _totalcount = FOUND_ROWS();     #计算总页数    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;     #计算求和字段    IF (IFNULL(_sumfields, '') <> '') THEN        #序列sum结果        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;#定义预处理语句         EXECUTE sumsql;            SET _sumResult = @sumResult;                        #执行预处理语句         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;        }