分页功能存储过程，Delphi 和 .NET应用

来源：互联网发布：淘宝怎么推爆款编辑：程序博客网时间：2024/05/16 12:18

=============== SQL 中的存储过程 ====================

CREATE procedure splitPage@sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as pageCount,@rowcount as recCount,@currentpage as curPageset @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount offGO

=============== Delphi 中应用存储过程 ===================

var myDataSet: _Recordset; beginADODataSet1.Close ;ADOStoredProc1.Close;ADOStoredProc1.Parameters.ParamByName('@sqlstr').Value :='select * from History49'; //SQLADOStoredProc1.Parameters.ParamByName('@currentpage').Value := 2; //取得第2页ADOStoredProc1.Parameters.ParamByName('@pagesize').Value := 20; //每页行数ADOStoredProc1.ExecProc;ADOStoredProc1.Open;// 第 一个记录集myDataSet := lhgDM.asProcLS.NextRecordset(0);if myDataSet = nil then exit;// 第 2 个记录集 (页数 ，记录数信息)myDataSet := lhgDM.asProcLS.NextRecordset(1);if myDataSet = nil then exit;ADODataSet1.Recordset := myDataSet ;ADODataSet1.First ;pageCount := ADODataSet1.FieldByName('pageCount').AsInteger; // 总页数recCount := ADODataSet1.FieldByName('recCount').AsInteger; //总记录数// 第 3 个记录集 当前SQL请求第N页的记录myDataSet := lhgDM.asProcLS.NextRecordset(2);if myDataSet = nil then exit;ADODataSet1.Recordset := myDataSet ;ADODataSet1.first;while not ADODataSet1.EOF do begin...end;end;

=============== .NET(c#) 中应用存储过程 ===================

public DataView createDataViewWithPage(string strSQL,string dsName,int currentPage,int pageRows,ref int pageCount,ref int RowCount){if (currentPage<=0) return null;if (SQLConn==null) SQLConn = setConn(SQLConnStr);if (SQLConn==null) return null;SqlCommand myCmd = new SqlCommand();myCmd.CommandText = "splitPage" ; //存储过程名称myCmd.CommandType = CommandType.StoredProcedure ;myCmd.Parameters.Add("@sqlstr", SqlDbType.VarChar,4000).Value = strSQL;myCmd.Parameters.Add("@currentpage", SqlDbType.Int).Value = currentPage;myCmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = pageRows;myCmd.Connection = SQLConn;SqlDataAdapter sqlDA = new SqlDataAdapter();sqlDA.SelectCommand = myCmd;DataSet ds = new DataSet();sqlDA.Fill(ds,dsName);pageCount = Convert.ToInt32(ds.Tables[1].DefaultView[0]["pageCount"].ToString()); //总有页数RowCount = Convert.ToInt32(ds.Tables[1].DefaultView[0]["recCount"].ToString()); //总记录数//curPage = Convert.ToInt32(ds.Tables[1].DefaultView[0]["curPage"].ToString()); //当前页return ds.Tables[2].DefaultView;// ds.Tables[dsName].DefaultView ;}

0 0