我写的通用分页类,不用网上流行的那个通用分页存储过程,这样可以防止SQL注入,配合AspNetPager控件使用,对于数据量不是特别大的情况下使用比较方便.Imports System.Data.SqlClient Imports System.TextPublic Class MySqlPager Private m_StrFields As String '查询,添加,修改的字段 Private m_TableName As String '数据库表名,当是查询操作时候可以用Join连接多个表 Private m_StrWhere As String '查询操作时候的查询条件,包括分组字段和Having条件 Private m_StrSort As String '排序字符串 Private m_StrConn As String '数据库连接字符串 Private m_PrimaryKey As String '要分页表中的主键 Private m_PageIndex As Integer '分页的页面索引 Private m_PageSize As Integer '分页的页面大小 Private m_RecordCount As Integer '返回记录总数 Private m_PageCount As Integer '返回页面总数 Private m_strOutInf As String '输出调试信息 Private m_CmdParameters As New ArrayList '命令参数集合 Private m_Conn As SqlConnection '公用数据库连接 '选择的字段 Public WriteOnly Property StrFields() As String Set(ByVal Value As String) m_StrFields = Value End Set End Property '数据库表名 Public WriteOnly Property TableName() As String Set(ByVal Value As String) m_TableName = Value End Set End Property '筛选条件 Public WriteOnly Property StrWhere() As String Set(ByVal Value As String) m_StrWhere = Value End Set End Property '排序字段 Public WriteOnly Property StrSort() As String Set(ByVal Value As String) m_StrSort = Value End Set End Property '数据库连接字符串 Public WriteOnly Property StrConn() As String Set(ByVal Value As String) If Value = "" Then Throw New Exception("数据库连接字符串为空") Else m_StrConn = Value End If End Set End Property '表中的主键 Public WriteOnly Property PrimaryKey() As String Set(ByVal Value As String) m_PrimaryKey = Value End Set End Property '分页大小 Public WriteOnly Property PageSize() As Integer Set(ByVal Value As Integer) m_PageSize = Value End Set End Property '页面索引 Public WriteOnly Property PageIndex() As Integer Set(ByVal Value As Integer) m_PageIndex = Value End Set End Property '记录总数 Public ReadOnly Property RecordCount() As Integer Get Return m_RecordCount End Get End Property '页面总数 Public ReadOnly Property PageCount() As Integer Get Return m_PageCount End Get End Property '调试信息 Public Property OutInf() As String Get Return m_strOutInf End Get Set(ByVal Value As String) m_strOutInf = Value End Set End Property '打开数据库连接 Private Sub OpenConn() If m_Conn Is Nothing Then m_Conn = New SqlConnection(m_StrConn) m_Conn.Open() Else If m_Conn.State = ConnectionState.Closed Then m_Conn.Open() End If End If End Sub '关闭数据库连接 Public Sub CloseConn() If Not m_Conn Is Nothing Then If m_Conn.State = ConnectionState.Open Then m_Conn.Close() m_Conn = Nothing End If m_Conn = Nothing End If End Sub '添加命令参数 Public Sub AddCmdParms(ByVal ParmName As String, ByVal ParmType As SqlDbType, ByVal ParmValue As Object, ByVal ParmDirection As ParameterDirection) Dim Parm As SqlParameter Parm = New SqlParameter(ParmName, ParmType) Select Case ParmDirection Case ParameterDirection.Input Parm.Value = ParmValue m_CmdParameters.Add(Parm) Case ParameterDirection.Output Parm.Direction = ParmDirection m_CmdParameters.Add(Parm) End Select End Sub Public Sub AddCmdParms(ByVal ParmName As String, ByVal ParmValue As Object, ByVal ParmDirection As ParameterDirection) Dim Parm As SqlParameter Parm = New SqlParameter Parm.ParameterName = ParmName Select Case ParmDirection Case ParameterDirection.Input Parm.Value = ParmValue m_CmdParameters.Add(Parm) Case ParameterDirection.Output Parm.Direction = ParmDirection m_CmdParameters.Add(Parm) End Select End Sub
'//分页功能 '取得指定表和条件的记录总数和页面总数 Public Sub GetRecordCount() Dim strB As New StringBuilder Dim querycom As New SqlCommand strB.Append("Select count(*) From " & m_TableName) If m_StrWhere <> "" Then strB.Append(" Where " & m_StrWhere) End If Try PrepareCommand(querycom, False, CommandType.Text, strB.ToString) m_RecordCount = querycom.ExecuteScalar If m_RecordCount > 0 And m_PageSize > 0 Then If (m_RecordCount Mod m_PageSize) = 0 Then m_PageCount = (m_RecordCount / m_PageSize) Else m_PageCount = (m_RecordCount \ m_PageSize) + 1 End If Else m_PageCount = 0 End If Catch ex As Exception Throw Finally querycom.Parameters.Clear() querycom.Dispose() End Try End Sub Public Function GetPageTable() As DataTable Dim strB As New StringBuilder Dim querycom As New SqlCommand Dim da As New SqlDataAdapter Dim ds As New DataSet Dim i As Integer If m_PageCount <= m_PageIndex And m_PageCount > 0 Then m_PageIndex = m_PageCount ElseIf m_PageCount <= m_PageIndex And m_PageCount = 0 Then m_PageIndex = 1 End If '如果是第一页 If m_PageIndex = 1 Or m_PageCount <= 1 Then If m_StrWhere = "" Then strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Order By " & m_StrSort) Else strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort) End If '如果是最后一页 ElseIf m_PageIndex = m_PageCount Then If m_StrWhere = "" Then strB.Append("Select " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Order By " & m_StrSort & " ) Order By " & m_StrSort) Else strB.Append("Select " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort & " ) And " & m_StrWhere & " Order By " & m_StrSort) End If '如果是中间页 Else If m_StrWhere = "" Then strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Order By " & m_StrSort & " ) Order By " & m_StrSort) Else strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort & " ) And " & m_StrWhere & " Order By " & m_StrSort) End If End If 'm_strOutInf = strB.ToString Try PrepareCommand(querycom, False, CommandType.Text, strB.ToString) da.SelectCommand = querycom da.Fill(ds, "tbTemp") GetPageTable = ds.Tables("tbTemp") Catch ex As Exception m_strOutInf = "查询字符串为:" & strB.ToString & "<br>" m_strOutInf += "参数Parameters为:" & "<br>" For i = 0 To querycom.Parameters.Count - 1 m_strOutInf += querycom.Parameters(i).ParameterName & " = " & querycom.Parameters(i).Value & "<br>" Next m_strOutInf += "错误信息:" & ex.Source & ex.Message & ex.StackTrace Throw Finally CloseConn() da = Nothing ds = Nothing querycom.Parameters.Clear() querycom.Dispose() m_CmdParameters.Clear() End Try End Function '//结束分页功能 '准备命令参数 Private Sub PrepareCommand(ByVal Cmd As SqlCommand, ByVal ExecuteTrans As Boolean, ByVal CmdType As CommandType, ByVal CmdText As String) Dim Parm As SqlParameter Cmd.CommandType = CmdType Cmd.CommandText = CmdText '打开数据库连接 Me.OpenConn() Cmd.Connection = m_Conn '添加命令参数 For Each Parm In m_CmdParameters Cmd.Parameters.Add(Parm) Next End Sub End Class下面的是使用方法 Dim intStClass As Integer = Int32.Parse(Request.QueryString("id")) Dim StrWhere As String '条件筛选 Dim PageIndex As Integer '页面索引,从1开始 Dim PageSize As Integer '分页大小 Dim recordCount As Integer '记录总数 Dim pageCount As Integer '页面总数 Dim GetDataPage As New MySqlPager '分页类 PageIndex = PagerNavFree.CurrentPageIndex '页面索引为AspNetPager控件的当前页面 PageSize = PagerNavFree.PageSize '分页大小为AspNetPager控件分页大小 StrWhere = "StClass = @StClass" '添加筛选条件参数变量 GetDataPage.AddCmdParms("@StClass", SqlDbType.Int, intStClass, ParameterDirection.Input) GetDataPage.StrConn = ConfigurationSettings.AppSettings("connstr") GetDataPage.PageIndex = PageIndex GetDataPage.PageSize = PageSize GetDataPage.StrFields = "IDKey, StTitle, Regdate" GetDataPage.TableName = "Shiti" GetDataPage.PrimaryKey = "IDKey " GetDataPage.StrWhere = StrWhere GetDataPage.StrSort = "Regdate DESC" '取数据总数 GetDataPage.GetRecordCount()
方便许多
我有源码,需要参考吗?
Imports System.TextPublic Class MySqlPager
Private m_StrFields As String '查询,添加,修改的字段
Private m_TableName As String '数据库表名,当是查询操作时候可以用Join连接多个表
Private m_StrWhere As String '查询操作时候的查询条件,包括分组字段和Having条件
Private m_StrSort As String '排序字符串
Private m_StrConn As String '数据库连接字符串
Private m_PrimaryKey As String '要分页表中的主键
Private m_PageIndex As Integer '分页的页面索引
Private m_PageSize As Integer '分页的页面大小
Private m_RecordCount As Integer '返回记录总数
Private m_PageCount As Integer '返回页面总数
Private m_strOutInf As String '输出调试信息
Private m_CmdParameters As New ArrayList '命令参数集合
Private m_Conn As SqlConnection '公用数据库连接 '选择的字段
Public WriteOnly Property StrFields() As String
Set(ByVal Value As String)
m_StrFields = Value
End Set
End Property '数据库表名
Public WriteOnly Property TableName() As String
Set(ByVal Value As String)
m_TableName = Value
End Set
End Property '筛选条件
Public WriteOnly Property StrWhere() As String
Set(ByVal Value As String)
m_StrWhere = Value
End Set
End Property '排序字段
Public WriteOnly Property StrSort() As String
Set(ByVal Value As String)
m_StrSort = Value
End Set
End Property '数据库连接字符串
Public WriteOnly Property StrConn() As String
Set(ByVal Value As String)
If Value = "" Then
Throw New Exception("数据库连接字符串为空")
Else
m_StrConn = Value
End If
End Set
End Property '表中的主键
Public WriteOnly Property PrimaryKey() As String
Set(ByVal Value As String)
m_PrimaryKey = Value
End Set
End Property '分页大小
Public WriteOnly Property PageSize() As Integer
Set(ByVal Value As Integer)
m_PageSize = Value
End Set
End Property '页面索引
Public WriteOnly Property PageIndex() As Integer
Set(ByVal Value As Integer)
m_PageIndex = Value
End Set
End Property '记录总数
Public ReadOnly Property RecordCount() As Integer
Get
Return m_RecordCount
End Get
End Property '页面总数
Public ReadOnly Property PageCount() As Integer
Get
Return m_PageCount
End Get
End Property '调试信息
Public Property OutInf() As String
Get
Return m_strOutInf
End Get
Set(ByVal Value As String)
m_strOutInf = Value
End Set
End Property '打开数据库连接
Private Sub OpenConn()
If m_Conn Is Nothing Then
m_Conn = New SqlConnection(m_StrConn)
m_Conn.Open()
Else
If m_Conn.State = ConnectionState.Closed Then
m_Conn.Open()
End If
End If
End Sub '关闭数据库连接
Public Sub CloseConn()
If Not m_Conn Is Nothing Then
If m_Conn.State = ConnectionState.Open Then
m_Conn.Close()
m_Conn = Nothing
End If
m_Conn = Nothing
End If
End Sub '添加命令参数
Public Sub AddCmdParms(ByVal ParmName As String, ByVal ParmType As SqlDbType, ByVal ParmValue As Object, ByVal ParmDirection As ParameterDirection)
Dim Parm As SqlParameter Parm = New SqlParameter(ParmName, ParmType) Select Case ParmDirection
Case ParameterDirection.Input
Parm.Value = ParmValue
m_CmdParameters.Add(Parm)
Case ParameterDirection.Output
Parm.Direction = ParmDirection
m_CmdParameters.Add(Parm)
End Select
End Sub Public Sub AddCmdParms(ByVal ParmName As String, ByVal ParmValue As Object, ByVal ParmDirection As ParameterDirection)
Dim Parm As SqlParameter Parm = New SqlParameter
Parm.ParameterName = ParmName Select Case ParmDirection
Case ParameterDirection.Input
Parm.Value = ParmValue
m_CmdParameters.Add(Parm)
Case ParameterDirection.Output
Parm.Direction = ParmDirection
m_CmdParameters.Add(Parm)
End Select
End Sub
'取得指定表和条件的记录总数和页面总数
Public Sub GetRecordCount()
Dim strB As New StringBuilder
Dim querycom As New SqlCommand strB.Append("Select count(*) From " & m_TableName) If m_StrWhere <> "" Then
strB.Append(" Where " & m_StrWhere)
End If Try
PrepareCommand(querycom, False, CommandType.Text, strB.ToString)
m_RecordCount = querycom.ExecuteScalar If m_RecordCount > 0 And m_PageSize > 0 Then
If (m_RecordCount Mod m_PageSize) = 0 Then
m_PageCount = (m_RecordCount / m_PageSize)
Else
m_PageCount = (m_RecordCount \ m_PageSize) + 1
End If
Else
m_PageCount = 0
End If
Catch ex As Exception
Throw
Finally
querycom.Parameters.Clear()
querycom.Dispose()
End Try
End Sub Public Function GetPageTable() As DataTable
Dim strB As New StringBuilder
Dim querycom As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer If m_PageCount <= m_PageIndex And m_PageCount > 0 Then
m_PageIndex = m_PageCount
ElseIf m_PageCount <= m_PageIndex And m_PageCount = 0 Then
m_PageIndex = 1
End If '如果是第一页
If m_PageIndex = 1 Or m_PageCount <= 1 Then
If m_StrWhere = "" Then
strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Order By " & m_StrSort)
Else
strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort)
End If '如果是最后一页
ElseIf m_PageIndex = m_PageCount Then
If m_StrWhere = "" Then
strB.Append("Select " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Order By " & m_StrSort & " ) Order By " & m_StrSort)
Else
strB.Append("Select " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort & " ) And " & m_StrWhere & " Order By " & m_StrSort)
End If
'如果是中间页
Else
If m_StrWhere = "" Then
strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Order By " & m_StrSort & " ) Order By " & m_StrSort)
Else
strB.Append("Select Top " & m_PageSize & " " & m_StrFields & " From " & m_TableName & " Where " & m_PrimaryKey & " Not In ( Select Top " & (m_PageSize * (m_PageIndex - 1)) & " " & m_PrimaryKey & " From " & m_TableName & " Where " & m_StrWhere & " Order By " & m_StrSort & " ) And " & m_StrWhere & " Order By " & m_StrSort)
End If
End If 'm_strOutInf = strB.ToString Try
PrepareCommand(querycom, False, CommandType.Text, strB.ToString) da.SelectCommand = querycom
da.Fill(ds, "tbTemp")
GetPageTable = ds.Tables("tbTemp")
Catch ex As Exception
m_strOutInf = "查询字符串为:" & strB.ToString & "<br>"
m_strOutInf += "参数Parameters为:" & "<br>"
For i = 0 To querycom.Parameters.Count - 1
m_strOutInf += querycom.Parameters(i).ParameterName & " = " & querycom.Parameters(i).Value & "<br>"
Next
m_strOutInf += "错误信息:" & ex.Source & ex.Message & ex.StackTrace
Throw
Finally
CloseConn()
da = Nothing
ds = Nothing
querycom.Parameters.Clear()
querycom.Dispose()
m_CmdParameters.Clear()
End Try
End Function
'//结束分页功能 '准备命令参数
Private Sub PrepareCommand(ByVal Cmd As SqlCommand, ByVal ExecuteTrans As Boolean, ByVal CmdType As CommandType, ByVal CmdText As String)
Dim Parm As SqlParameter Cmd.CommandType = CmdType
Cmd.CommandText = CmdText '打开数据库连接
Me.OpenConn() Cmd.Connection = m_Conn '添加命令参数
For Each Parm In m_CmdParameters
Cmd.Parameters.Add(Parm)
Next
End Sub
End Class下面的是使用方法 Dim intStClass As Integer = Int32.Parse(Request.QueryString("id"))
Dim StrWhere As String '条件筛选
Dim PageIndex As Integer '页面索引,从1开始
Dim PageSize As Integer '分页大小
Dim recordCount As Integer '记录总数
Dim pageCount As Integer '页面总数
Dim GetDataPage As New MySqlPager '分页类 PageIndex = PagerNavFree.CurrentPageIndex '页面索引为AspNetPager控件的当前页面
PageSize = PagerNavFree.PageSize '分页大小为AspNetPager控件分页大小 StrWhere = "StClass = @StClass"
'添加筛选条件参数变量
GetDataPage.AddCmdParms("@StClass", SqlDbType.Int, intStClass, ParameterDirection.Input) GetDataPage.StrConn = ConfigurationSettings.AppSettings("connstr")
GetDataPage.PageIndex = PageIndex
GetDataPage.PageSize = PageSize
GetDataPage.StrFields = "IDKey, StTitle, Regdate"
GetDataPage.TableName = "Shiti"
GetDataPage.PrimaryKey = "IDKey "
GetDataPage.StrWhere = StrWhere
GetDataPage.StrSort = "Regdate DESC" '取数据总数
GetDataPage.GetRecordCount()
'repFree为Repeater控件
repFree.DataSource = GetDataPage.GetPageTable.DefaultView
repFree.DataBind()
recordCount = GetDataPage.RecordCount
pageCount = GetDataPage.PageCount '下面是AspNetPager控件
PagerNavFree.RecordCount = recordCount
PagerNavFree.CustomInfoText = "共<font color='blue'><b>" & recordCount.ToString() & "</b></font>条记录"
PagerNavFree.CustomInfoText += "<font color='red'><b>" & pageCount.ToString & "</b></font>页"
PagerNavFree.CustomInfoText += " 每页<font color='blue'><b>" & PageSize.ToString() & "</b></font>条"
PagerNavFree.CustomInfoText += " 当前第<font color='blue'><b>" & PagerNavFree.CurrentPageIndex.ToString() & "</b></font>页" 'PagerNavTop.CustomInfoText = PagerNavBottom.CustomInfoText
{
this.Repeater1.DataSource = Databind(0);
this.Repeater1.DataBind();
GetTotalPage();
Label1.Text = "当前页:1/" + Totalpage;
Current_.Value = 1.ToString();
}private void GetTotalPage()
{
SqlConnection Sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString()); SqlCommand Sqlcommand = new SqlCommand();
Sqlcommand.CommandText = "select count(link) from " + Session["tablename"] + " where link=" + Request.QueryString["id"];
Sqlcommand.Connection = Sqlconn;
Sqlconn.Open();
TotalCount = int.Parse(Sqlcommand.ExecuteScalar().ToString());
Sqlconn.Close();
if (TotalCount % Pagesize != 0)
Totalpage = TotalCount / Pagesize + 1;
else
Totalpage = TotalCount / Pagesize;
}private DataTable Databind(int StartIndex)
{
String Sql = "select 内容,时间,names,member,fromm,link,sig from " + Session["tablename"] + " as a join member as b on a.作者=b.names where link=" + Request.QueryString["id"] + " order by 时间";
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
SqlDataAdapter Sqlcmd = new SqlDataAdapter(Sql, con);
DataSet ds = new DataSet();
Sqlcmd.Fill(ds, StartIndex, Pagesize, Session["tablename"].ToString());
return ds.Tables[Session["tablename"].ToString()];
}protected void frist_Click(object sender, EventArgs e)
{
Int32 Current_Page = int.Parse(Current_.Value);
GetTotalPage();
this.Repeater1.DataSource = Databind(0);
this.Repeater1.DataBind();
Label1.Text = "当前页:1 / " + Totalpage;
Current_.Value = 1.ToString();
}
protected void prePage_Click(object sender, EventArgs e)
{
Int32 Current_Page = int.Parse(Current_.Value);
GetTotalPage();
if (Current_Page > 1)
{
Current_Page = Current_Page - 1;
Label1.Text = "当前页:" + Current_Page + "/" + Totalpage;
this.Repeater1.DataSource = Databind((Current_Page - 1) * Pagesize);
this.Repeater1.DataBind();
Current_.Value = Current_Page.ToString();
}
}
protected void nextPage_Click(object sender, EventArgs e)
{
Int32 Current_Page = int.Parse(Current_.Value);
GetTotalPage();
if (Current_Page < Totalpage)
{
Current_Page = Current_Page + 1;
Label1.Text = "当前页:" + Current_Page + "/" + Totalpage;
this.Repeater1.DataSource = Databind((Current_Page - 1) * Pagesize);
this.Repeater1.DataBind();
Current_.Value = Current_Page.ToString();
}
}
protected void lastPage_Click(object sender, EventArgs e)
{
Int32 Current_Page = int.Parse(Current_.Value);
GetTotalPage();
Label1.Text = "当前页:" + Totalpage + "/" + Totalpage;
this.Repeater1.DataSource = Databind(((Totalpage - 1) * Pagesize));
this.Repeater1.DataBind();
Current_.Value = Totalpage.ToString();
}
http://blog.csdn.net/frankwang2008/archive/2007/08/17/1749075.aspx