想来学习 .net 也2个月了吧,最初遇到的一个问题就是分页程序获取当前页的数据并支持字段排序而且支持搜索时的 Where 语句 在网上搜了 很久也没找到,也在 Asp.net 的一些论坛上发过帖子也许是那些高手懒得理我吧(做菜鸟就是难)也许是我找东西的能力有问题,但我却找到了以下SQL 的 存储过程。 SET @page = (@page-1) * @pagesize + 1 EXEC sp_cursoropen @P1 output, @strSQL EXEC sp_cursorfetch @P1, 16, @page, @pagesize EXEC sp_cursorclose @P1 后来经过修改就成下面这样了(后面有个调用的例子 VB.net 版本我只会VB 不知微软何时出 masm.net 我想这辈子是没希望了) /* 通用存储过程分页----- 江建 只读取当前页 支持分类排序 */ CREATE PROCEDURE GetPage ( @strTableName nvarchar(50), --表名 @fldName nvarchar (200), --要返回的字段 @strWhere nvarchar(200)="", --Where 语句 @fldOrderby nvarchar(200), --要排序的字段 @OrderType int=0, --排序类型升序还是降序 @page int = 1, --要获取的页码 @pageSize int = 5 --页大小 ) AS SET NOCOUNT ON DECLARE @P1 int DECLARE @strSQL nvarchar(1000) DECLARE @strOrderby nvarchar(200) IF @OrderType != 0 SET @strOrderby = '''' Order By ['''' + @fldOrderby +''''] DESC'''' ELSE SET @strOrderby = '''' Order By ['''' + @fldOrderby +''''] ASC'''' IF @strWhere !='''''''' SET @strSQL=''''SELECT '''' + @fldName + '''' FROM ['''' + @strTableName + ''''] WHERE '''' + @strWhere + @strOrderby ELSE SET @strSQL=''''SELECT '''' + @fldName + '''' FROM ['''' + @strTableName + '''']'''' + @strOrderby
SET @page = (@page-1) * @pagesize + 1 EXEC sp_cursoropen @P1 output, @strSQL EXEC sp_cursorfetch @P1, 16, @page, @pagesize EXEC sp_cursorclose @P1 GO /*这个是获取记录总数----- 江建*/ CREATE PROCEDURE CountRow ( @strTableName nvarchar(50), @fldNameCount nvarchar(50), @strWhere nvarchar(200)="" ) AS DECLARE @strSQL nvarchar(1000) IF @strWhere !='''''''' SET @strSQL=''''SELECT Count(['''' + @fldNameCount + '''']) As CountRow FROM ['''' + @strTableName + ''''] WHERE '''' + @strWhere ELSE SET @strSQL=''''SELECT Count(['''' + @fldNameCount + '''']) As CountRow FROM ['''' + @strTableName + '''']'''' EXEC sp_executesql @strSQL GO (1)下面是类模块 DataPager.vb ''''=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= '''' Data Pager '''' Programme by Jiang Jian '''' Date:2005-06-23 '''' Corpright(C) 2005 Jiang Jian. ''''=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= '''' http://vbcc.126.com '''' Email:jiangjian@Ejet.com.cn '''' This is class for sql database pager ''''=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Imports System Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Data Imports System.Data.SqlClient
Namespace DataLayer Public Class GetData Inherits WebControl Private myConn As New SqlConnection Private intRowCount As Integer Private strTableName As String Private strfldNameCount As String Private strfldName As String Private intPagesize As Integer Private strWhere As String Private strfldOrderby As String Public Function ConnectionDatabase() Dim strConn As String If Not (myConn.State) Then myConn.ConnectionString = "Server=(local);database=Northwind;uid=sa" myConn.Open() End If End Function ''''set or get tablename Public Property TableName() As String Get Return strTableName End Get Set(ByVal Value As String) strTableName = Value End Set End Property ''''set or get count(xxxx) Public Property fldNameCount() As String Get Return strfldNameCount End Get Set(ByVal Value As String) strfldNameCount = Value End Set End Property ''''set or get (xxxx) FROM Public Property fldName() As String Get Return strfldName End Get Set(ByVal Value As String) strfldName = Value End Set End Property ''''set or get pagesize Public Property Pagesize() As Integer Get Return intPagesize End Get Set(ByVal Value As Integer) intPagesize = Value End Set End Property ''''set or get sql where Public Property Where() As String Get Return strWhere End Get Set(ByVal Value As String) s [1] [2] [3] 下一页 [C语言系列]NET 中C#的switch语句的语法 [系统软件]托拽Explore中的文件到VB.net的窗口 [系统软件]Boost库在XP+Visual C++.net中的安装 [常用软件]新配色面板:Paint.Net3.0RC1官方下载 [常用软件]用内建的“Net Meeting”聊天 [VB.NET程序]Henry的VB.NET之旅(三)—共享成员 [VB.NET程序]Henry的VB.NET之旅(二)—构造与析构 [VB.NET程序]Henry的VB.NET之旅(一)—失踪的窗体 [VB.NET程序]在托盘上显示Balloon Tooltip(VB.NET) [VB.NET程序]Henry手记-VB.NET中动态加载Treeview节点(二)
|