有没有什么不好的地方请大家给更改一下,给我多提点意见,另外为什么我把“TOP 10” 的“10“改成SQL参数以后不能创建存储过程,在VS中的"SQLCommand也不能定义这样的SQL命令?
分页的原理
-----------------------------------
SELECT TOP 10 * FROM (SELECT TOP 10 ProductID, ProductName, QuantityPerUnit, UnitsInStock FROM Products WHERE ProductName IN (SELECT TOP 60 ProductName FROM Products ORDER BY ProductName) ORDER BY ProductName DESC) AS tmp ORDER BY ProductName
WebForm.aspx.VB
--------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
DataGrid1.VirtualItemCount = myComponent.SetVirtualItemCount()
ViewState("SortExpression") = "ProductID"
End If
End Sub Private Sub GetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetData.Click
Dim nPageIndex As Integer = 0
Dim nRowsToDisplay As Integer = 10
DataGrid1.DataSource = MyComponent.DataGridSource(nPageIndex, nRowsToDisplay, ViewState("SortExpression").ToString())
DataGrid1.DataBind() End Sub Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged DataGrid1.CurrentPageIndex = e.NewPageIndex Dim nRowsToDisplay As Integer = DataGrid1.PageSize Dim nMod As Integer = DataGrid1.VirtualItemCount Mod DataGrid1.PageSize If e.NewPageIndex + 1 = DataGrid1.PageCount And nMod > 0 Then
nRowsToDisplay = nMod
End If DataGrid1.DataSource = MyComponent.DataGridSource(e.NewPageIndex, nRowsToDisplay, ViewState("SortExpression").ToString())
DataGrid1.DataBind() End Sub
End Class
--------------------------------------
Public Function SetVirtualItemCount()
SqlConnection1.Open()
Dim ItemCount As Integer
ItemCount = CType(SqlCommand1.ExecuteScalar(), Integer)
SqlConnection1.Close()
Return ItemCount
End Function
component1.vb数据访问层
------------------------------------
Public Function DataGridSource(ByVal nPageIndex As Integer, ByVal nRowsToDisplay As Integer, ByVal SortExpression As String) As Data.DataSet
nPageIndex += 1 Dim SortName As String = SortExpression
'这一部分我想要用StringBuilder类,可是语法没搞明白?
'是不是用StringBuilder类的效律会高一点?
Dim strCmd As String
strCmd = "SELECT TOP "
strCmd += nRowsToDisplay.ToString()
strCmd += " * FROM(SELECT TOP "
strCmd += nRowsToDisplay.ToString()
strCmd += " ProductID, ProductName, QuantityPerUnit, UnitsInStock FROM Products WHERE "
strCmd += SortName
strCmd += " IN (SELECT TOP "
strCmd += (10 * nPageIndex).ToString()
strCmd += " "
strCmd += SortName
strCmd += " FROM Products ORDER BY "
strCmd += SortName
strCmd += ") ORDER BY "
strCmd += SortName
strCmd += " DESC) AS tmp ORDER BY "
strCmd += SortName Dim DataSet1 As New System.Data.DataSet
SqlDataAdapter1.SelectCommand = New SqlClient.SqlCommand(strCmd, SqlConnection1)
SqlDataAdapter1.Fill(DataSet1, "Products")
Return DataSet1 End Function
分页的原理
-----------------------------------
SELECT TOP 10 * FROM (SELECT TOP 10 ProductID, ProductName, QuantityPerUnit, UnitsInStock FROM Products WHERE ProductName IN (SELECT TOP 60 ProductName FROM Products ORDER BY ProductName) ORDER BY ProductName DESC) AS tmp ORDER BY ProductName
WebForm.aspx.VB
--------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
DataGrid1.VirtualItemCount = myComponent.SetVirtualItemCount()
ViewState("SortExpression") = "ProductID"
End If
End Sub Private Sub GetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetData.Click
Dim nPageIndex As Integer = 0
Dim nRowsToDisplay As Integer = 10
DataGrid1.DataSource = MyComponent.DataGridSource(nPageIndex, nRowsToDisplay, ViewState("SortExpression").ToString())
DataGrid1.DataBind() End Sub Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged DataGrid1.CurrentPageIndex = e.NewPageIndex Dim nRowsToDisplay As Integer = DataGrid1.PageSize Dim nMod As Integer = DataGrid1.VirtualItemCount Mod DataGrid1.PageSize If e.NewPageIndex + 1 = DataGrid1.PageCount And nMod > 0 Then
nRowsToDisplay = nMod
End If DataGrid1.DataSource = MyComponent.DataGridSource(e.NewPageIndex, nRowsToDisplay, ViewState("SortExpression").ToString())
DataGrid1.DataBind() End Sub
End Class
--------------------------------------
Public Function SetVirtualItemCount()
SqlConnection1.Open()
Dim ItemCount As Integer
ItemCount = CType(SqlCommand1.ExecuteScalar(), Integer)
SqlConnection1.Close()
Return ItemCount
End Function
component1.vb数据访问层
------------------------------------
Public Function DataGridSource(ByVal nPageIndex As Integer, ByVal nRowsToDisplay As Integer, ByVal SortExpression As String) As Data.DataSet
nPageIndex += 1 Dim SortName As String = SortExpression
'这一部分我想要用StringBuilder类,可是语法没搞明白?
'是不是用StringBuilder类的效律会高一点?
Dim strCmd As String
strCmd = "SELECT TOP "
strCmd += nRowsToDisplay.ToString()
strCmd += " * FROM(SELECT TOP "
strCmd += nRowsToDisplay.ToString()
strCmd += " ProductID, ProductName, QuantityPerUnit, UnitsInStock FROM Products WHERE "
strCmd += SortName
strCmd += " IN (SELECT TOP "
strCmd += (10 * nPageIndex).ToString()
strCmd += " "
strCmd += SortName
strCmd += " FROM Products ORDER BY "
strCmd += SortName
strCmd += ") ORDER BY "
strCmd += SortName
strCmd += " DESC) AS tmp ORDER BY "
strCmd += SortName Dim DataSet1 As New System.Data.DataSet
SqlDataAdapter1.SelectCommand = New SqlClient.SqlCommand(strCmd, SqlConnection1)
SqlDataAdapter1.Fill(DataSet1, "Products")
Return DataSet1 End Function
解决方案 »
- 调用分页存储过程返回的是Total是什么意思? 大虾帮我看看
- 头疼的问题!在线等!
- 多表多行的Dataset一次(可能有更新,插入,删除)更新有主外键的两张表的解决方案?
- ☆★请教关于动态生成控件的问题!急等回复!!救命啊!!
- DropDownList的问题,下拉框中显示内容和树型结构差不多?
- 100分求教怎样收取邮件!!!!!!!
- 如何将图片导入到excel
- 提示:Access to the path : C:\pbmis\cfg.xml is denied.
- .net连接oracle查询问题
- 如何在asp.netweb应用程序项目里使用web窗体控件---Table?
- 关于HttpContext.Current.Response 命名空间的问题
- 求:<<aspnet数据库管理系统开发实例导航>>一书的 源代码光盘的内容 谢谢
在进行字符串连接时: string str = str1 + str2 + ....;
一般超过三项连接,最好用StringBuilder来代替String类. StringBuilder可以避免重新创建String 对象造成的性能损失.一般用于组装Sql语句时用到: StringBulider.