databbs.vb

《ASP.NET C#程序设计案例教程》

'********************************************************************************
'这是数据访问组件的源文件，主要用于和数据库打交道,其中定义了4个类。
'DataBBSes   类，专门用来处理有关发表文章的操作，
'DataBBS     类，主要用来返回一篇文章的信息，
'DataForums  类，专门用来处理有栏目的操作
'DataForum   类，主要用来返回一个栏目的信息
'********************************************************************************

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration										'因为用到了ConfigurationSettings类
Imports MicroSoft.VisualBasic
Imports System.Web


'----------------------------------------------------------------------------------------------------
'该类用来查询、添加、删除和更新记录
Public Class DataBBSes												'DataBBSes是自己定义的类的名称

	Private _strConn As String										'定义一个私有变量，用来设置数据库连接字符串

	'建立构造函数
	Public Sub New()
		_strConn=ConfigurationSettings.AppSettings("strConn")		'将数据库连接字符串赋值给私有变量_strConn
	End Sub

	'该过程用来返回所有记录，如intLayer为1，则返回所有第1层记录，如为2，则返回所有1层和2层。
	Public Function GetBBS(intLayer As Integer) As DataView
		Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
		Dim strSql As String
		If intLayer=1 Then
			strSql="Select * From BBS Where layer=1"				'这里只返回第一层
		Else
			strSql="Select * From BBS"
		End If
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "BBS")											
		Return(ds.Tables("BBS").DefaultView)							'返回函数值
	End Function

	'该过程用来插入记录
	Public Sub InsertBBS(intForumId As Integer,strTitle As String,strBody As String,intLayer As Integer,intFatherId As Integer,intChilds As Integer,intHits As Integer, strUserId As String, strEmail As String, strIP As String, dtmSubmitDate As Date,intSetTop As Integer,strLastUserId As String,dtmLastSubmitDate As Date)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
		'建立Command对象，注意这里使用了含有参数的SQL语句
		Dim strSql="Insert Into BBS(ForumId,Title,Body,Layer,FatherId,Childs,Hits,UserId,Email,IP,SubmitDate,SetTop,LastUserId,LastSubmitDate) Values(@ForumId,@Title,@Body,@Layer,@FatherId,@Childs,@Hits,@UserId,@Email,@IP,@SubmitDate,@SetTop,@LastUserId,@LastSubmitDate)"
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
		cmd.Parameters.Add(New OleDbParameter("@ForumId",OleDbType.Integer))
		cmd.Parameters("@ForumId").Value=intForumId
		cmd.Parameters.Add(New OleDbParameter("@Title",OleDbType.VarWChar,100))
		cmd.Parameters("@Title").Value=strTitle
		cmd.Parameters.Add(New OleDbParameter("@Body",OleDbType.VarWChar,8000))
		If strBody<>"" Then
			cmd.Parameters("@Body").Value=strBody
		Else
			cmd.Parameters("@Body").Value=DBNull.Value						'赋值NULL
		End If		
		cmd.Parameters.Add(New OleDbParameter("@Layer",OleDbType.Integer))
		cmd.Parameters("@Layer").Value=intLayer
		cmd.Parameters.Add(New OleDbParameter("@FatherId",OleDbType.Integer))
		cmd.Parameters("@FatherId").Value=intFatherId
		cmd.Parameters.Add(New OleDbParameter("@Childs",OleDbType.Integer))
		cmd.Parameters("@Childs").Value=intChilds
		cmd.Parameters.Add(New OleDbParameter("@Hits",OleDbType.Integer))
		cmd.Parameters("@Hits").Value=intHits
		cmd.Parameters.Add(New OleDbParameter("@UserId",OleDbType.VarWChar,20))
		cmd.Parameters("@UserId").Value=strUserId
		cmd.Parameters.Add(New OleDbParameter("@Email",OleDbType.VarWChar,100))
		If strEmail<>"" Then
			cmd.Parameters("@Email").Value=strEmail
		Else
			cmd.Parameters("@Email").Value=DBNull.Value						'赋值NULL
		End If
		cmd.Parameters.Add(New OleDbParameter("@IP",OleDbType.VarWChar,20))
		cmd.Parameters("@IP").Value=strIP
		cmd.Parameters.Add(New OleDbParameter("@SubmitDate",OleDbType.Date))
		cmd.Parameters("@SubmitDate").Value=dtmSubmitDate
		cmd.Parameters.Add(New OleDbParameter("@SetTop",OleDbType.Integer))
		cmd.Parameters("@SetTop").Value=intSetTop
		cmd.Parameters.Add(New OleDbParameter("@LastUserId",OleDbType.VarWChar,20))
		cmd.Parameters("@LastUserId").Value=strLastUserId
		cmd.Parameters.Add(New OleDbParameter("@LastSubmitDate",OleDbType.Date))
		cmd.Parameters("@LastSubmitDate").Value=dtmLastSubmitDate

		'下面执行插入记录操作
		conn.open()                             
		cmd.ExecuteNonQuery()
		conn.close()                            
	End Sub

	'该函数用来删除记录，并返回删除的记录数目，
	'要注意的是如果它是第1层的，会将所有回复记录都删除掉
	Public Function DeleteBBS(intBBSId As Integer) As Integer
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Delete from BBS Where BBSId=" & intBBSId & " Or FatherId=" & IntBBSId
		Dim cmd As New OleDbCommand(strSql, conn)
		conn.Open()
		Dim intDel As integer=cmd.ExecuteNonQuery()						'删除记录,并返回受影响的记录数目
		conn.Close()
		Return(intDel)
	End Function

	'下面的函数用来根据记录编号Id返回一个DataBBS对象,就是下面定义的第2个类
	Public Function Items(intBBSId As integer) As DataBBS
		'下面建立DataUser对象
		Dim db As New DataBBS(intBBSId)									'注意这里的传入参数
		Return db															'返回函数值
	End Function
End Class

'----------------------------------------------------------------------------------------------------
'第2个类仅仅用来返回一条记录的各个字段值
Public Class DataBBS														'DataBBS是自己定义的类的名称
	Private _strConn As String										'定义一个私有变量，用来设置数据库连接字符串

	'下面定义了几个属性，用来返回对应字段值
	Public BBSId As Integer
	Public ForumId As Integer
	Public Title As String
	Public Body As String
	Public Layer As Integer
	Public Childs As Integer
	Public Hits As Integer
	Public FatherId As Integer
	Public SubmitDate As Date
	Public UserId As String
	Public Email As String
	Public SetTop As Integer
	Public LastUserId As String
	Public LastSubmitDate As Date
	
	'这是构造函数，在其中给每一个属性赋值
	Public Sub New(intBBSId As Integer)
		_strConn=ConfigurationSettings.AppSettings("strConn")							'将数据库连接字符串赋值给私有变量_strConn
		Dim conn As New OleDbConnection(_strConn)										'建立Connection对象
		Dim strSql As String="Select * From BBS Where BBSId=" & intBBSId				'建立SQL语句
		Dim cmd As New OleDbCommand(strSql, conn)										'建立Command对象
		conn.Open()                                            
		Dim dr As OleDbDataReader = cmd.ExecuteReader()									'建立DataReader对象
		'下面读取数据，并给属性赋值
		dr.Read()															
		BBSId=dr.Item("BBSId")													
		ForumId=dr.Item("ForumId")
		Title=dr.Item("Title")
		Body=dr.Item("Body").ToString()									'因为可能是空值，所以加一个ToString
		Layer=dr.Item("Layer")
		Childs=dr.Item("Childs")										
		Hits=dr.Item("Hits")
		FatherId=dr.Item("FatherId")
		SubmitDate=dr.Item("SubmitDate")
		UserId=dr.Item("UserId")
		Email=dr.Item("Email").ToString()
		SetTop=dr.Item("SetTop")
		LastUserId=dr.Item("LastUserId")
		LastSubmitDate=dr.Item("LastSubmitDate")
		conn.Close()                                              
	End Sub


	'该过程用来返回该记录及其子记录
	Public Function GetChilds() As DataView
		Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
		Dim strSql As String="Select * From BBS Where BBSId=" & BBSId & " Or FatherId=" & BBSId & " Order By BBSId Asc"		'这里只返回本记录和对应的子记录
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "BBS")											
		Return(ds.Tables("BBS").DefaultView	)						'返回函数值
	End Function

	'该过程用来将该记录设为置顶
	Public Sub AddSetTop()
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Update BBS Set SetTop=1 Where BBSId=" & BBSId 			
		Dim cmd As New OleDbCommand(strSql, conn)
		conn.Open()
		cmd.ExecuteNonQuery()										
		conn.Close()