dataguests.vb

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

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

NameSpace nsGuest													'nsGuest为自己定义的名称空间的名称

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

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

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

	'该过程用来所有记录，注意这里返回的是没有排序和查找的DataView对象
	Public Function GetGuest() As DataView
		Dim conn As New OleDbConnection(_strConn)						'这里使用了_strConn表示的数据库连接字符串
		Dim strSql As String="Select * From guest"
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "guest")											
		Return(ds.Tables("guest").DefaultView)							'返回函数值
	End Function

	'该过程用来插入记录
	Public Sub InsertGuest(strTitle As String,strBody As String,strName As String,strEmail As String,dtmSubmit_Date As Date)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
		'建立Command对象，注意这里使用了含有参数的SQL语句
		Dim strSql="Insert Into guest(title,body,name,email,submit_date) Values(@title,@body,@name,@email,@submit_date)"
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
		cmd.Parameters.Add(New OleDbParameter("@title",OleDbType.VarWChar,100))
		cmd.Parameters("@title").Value=strTitle
		cmd.Parameters.Add(New OleDbParameter("@body",OleDbType.VarWChar,4000))
		If strBody<>"" Then
			cmd.Parameters("@body").Value=strBody
		Else
			cmd.Parameters("@body").Value=DBNull.Value						'赋值NULL
		End If		
		cmd.Parameters.Add(New OleDbParameter("@name",OleDbType.VarWChar,30))
		cmd.Parameters("@name").Value=strName
		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("@submit_date",OleDbType.Date))
		cmd.Parameters("@submit_date").Value=dtmSubmit_Date
		'下面执行插入记录操作
		conn.open()                             
		cmd.ExecuteNonQuery()					
		conn.close()                            
	End Sub

	'该过程用来删除记录，传入参数为记录编号Id
	Public Sub DeleteGuest(intId As Integer)
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Delete from guest Where id=" & intId				
		Dim cmd As New OleDbCommand(strSql, conn)
		conn.Open()
		cmd.ExecuteNonQuery()											'删除记录
		conn.Close()
	End Sub

	'该过程用来更新记录
	Public Sub UpdateGuest(intId As Integer,strReback As String)
		'下面建立Connection对象，并利用含有参数的SQL语句更新数据库
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Update guest Set reback=@reback Where id=@id" 
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
		cmd.Parameters.Add(New OleDbParameter("@reback",OleDbType.VarWChar,1000))
		cmd.Parameters("@reback").Value=strReback
		cmd.Parameters.Add(New OleDbParameter("@id",OleDbType.Integer))
		cmd.Parameters("@id").Value=intId									
		'执行更新操作
		conn.Open()
		cmd.ExecuteNonQuery()
		conn.Close()
	End Sub

End Class

End NameSpace