分页存储过程
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; }