麻烦给我一份谢谢!
解决方案 »
- textbox只能输入数字
- 急。用过EXT或懂得JsonReader用的进来帮帮小弟
- 在CS服务器端,我收到了一个table的html,请问我该怎么将这个表中,单元格中的数据取出来,成为一个新的【M行N列】的数组
- 我写的DataGrid分页 却分不了页
- 关于oracle存储过程参数问题,求高手指点
- 几个面试问题,大家一起来做做
- 怎么把数据库中的数据在DataList中以行显示!
- Session 的问题
- 请问那里有asp.net的chm文件 ?谢谢
- js多重判断。。
- 在EditCommand事件中,怎么设定某些列可编辑某些不可编辑?
- 急!!如何在客户端实现对datagrid的行进行位置调整,比如说上移或下移!!!
http://blog.csdn.net/iuhxq/archive/2005/06/30/408758.aspx
http://www.knowsky.com/302044.html
SqlConnection conn;
SqlCommand cmd;
void Page_Load(object src,EventArgs e)
{
conn=new SqlConnection(ConfigurationSettings.AppSettings["Co
if(!Page.IsPostBack)
{
cmd=new SqlCommand("GetNews",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@pageindex",1);
cmd.Parameters.Add("@pagesize",1);
cmd.Parameters.Add("@docount",true);
conn.Open();
pager.RecordCount=(int)cmd.ExecuteScalar();
conn.Close();
BindData();
}
}
void BindData()
{
cmd=new SqlCommand("GetNews",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@pageindex",pager.CurrentPageIndex);
cmd.Parameters.Add("@pagesize",pager.PageSize);
cmd.Parameters.Add("@docount",false);
conn.Open();
dataGrid1.DataSource=cmd.ExecuteReader();
dataGrid1.DataBind();
conn.Close();
pager.CustomInfoText="记录总数:<font color=\"blue\"><b>"+pa
pager.CustomInfoText+=" 总页数:<font color=\"blue\"><b>"+pa
pager.CustomInfoText+=" 当前页:<font color=\"red\"><b>"+pag
}
void ChangePage(object src,PageChangedEventArgs e)
{
pager.CurrentPageIndex=e.NewPageIndex;
BindData();
}
</script>
CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO这是一个不错的分页存储过程,由于每次在页面中调用比较麻烦,就写了个针对此调用存储过程的Module :
Module MdlCommon
Public DBSet As DataSet '查询得到的记录集
Public ErrorMsg As String '存放错误信息
Public Pages As Integer Public Function GetResult(ByVal tb As String, ByVal col As String, ByVal collist As String, ByVal condition As String, ByVal coltype As Integer, ByVal orderby As Integer, ByVal Page As Integer, ByVal Pagesize As Integer, ByRef errMsg As String) As DataSet
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet
errMsg = ""
Try
cnn = New SqlClient.SqlConnection("data source=(local);initial catalog=Northwind;user id=sa;pwd='hyaocuk!'")
cmd = New SqlClient.SqlCommand("sp_page", cnn)
cmd.CommandType = CommandType.StoredProcedure
'给存储过程的参数赋值
Dim sp_temp As SqlClient.SqlParameter
sp_temp = cmd.Parameters.Add("@tb", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = tb sp_temp = cmd.Parameters.Add("@col", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = col sp_temp = cmd.Parameters.Add("@collist", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = collist sp_temp = cmd.Parameters.Add("@condition ", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = condition sp_temp = cmd.Parameters.Add("@pagesize", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Pagesize sp_temp = cmd.Parameters.Add("@page", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Page sp_temp = cmd.Parameters.Add("@orderby", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = orderby sp_temp = cmd.Parameters.Add("@coltype", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = coltype sp_temp = cmd.Parameters.Add("@pages", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Output adpt = New SqlClient.SqlDataAdapter
adpt.SelectCommand = cmd
adpt.Fill(rst)
GetResult = rst
Pages = cmd.Parameters("@pages").Value
Catch ex As Exception
errMsg = ex.Message
Finally
rst = Nothing
cnn = Nothing
End Try
End Function
End Module
调用时候:
Private Sub dgSortReport_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgSortReport.ItemCreated
If e.Item.ItemType = ListItemType.Pager Then
Dim pager As TableCell = CType(e.Item.Controls(0), TableCell)
Dim i As Integer
For i = 0 To pager.Controls.Count Step 2
Dim o As Object = CType(pager.Controls(i), Object)
If TypeOf (o) Is LinkButton Then
Dim h As LinkButton = CType(o, LinkButton)
h.Text = "[ " + h.Text + " ]" Else
Dim l As Label = CType(o, Label)
l.Text = "第" + l.Text + "页"
End If
Next i
End If
End Sub
'绑定datagrid的函数
Protected Sub CreateDatasource(ByVal CurrentPage As Integer) Dim tb, col, collist, condition As String
'查询的表名
tb = "Orders"
'排序列的列名
col = "OrderID"
'返回的列名列表
collist = "EmployeeID,RequiredDate"
'查询的表件
condition = "OrderID>0" Dim coltype, orderby, pagesize, page As Integer
coltype = 0
orderby = 0
pagesize = 10
page = CurrentPage
DBSet = GetResult(tb, col, collist, condition, coltype, orderby, page, pagesize, ErrorMsg)
dgSortReport.VirtualItemCount = Pages
dgSortReport.DataSource = DBSet.Tables(0).DefaultView
dgSortReport.DataBind()
End Sub
Private Sub dgSortReport_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgSortReport.PageIndexChanged
dgSortReport.CurrentPageIndex = e.NewPageIndex
' 页码值是从零开始的( 所以要加一)
CreateDatasource(e.NewPageIndex + 1)
End Sub