datausers.vb

来自「《ASP.NET C#程序设计案例教程》」· VB 代码 · 共 141 行

Imports System
Imports System.Data
Imports System.Data.OleDb

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

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

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

	Public Sub New(strConn As String)
		_strConn=strConn											'将传入字符串赋值给私有变量_strConn
	End Sub

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

	'该过程用来插入记录
	Public Sub Insert(strName As String,strTel As String,strEmail As String,strIntro As String,dtmSubmit_Date As Date,strUserPWD As String)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有变量的值
		'建立Command对象，注意这里使用了含有参数的SQL语句
		Dim strSql="Insert Into users(name,tel,email,intro,submit_date,UserPWD) Values(@name,@tel,@email,@intro,@submit_date,@UserPWD)"
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
		cmd.Parameters.Add(New OleDbParameter("@name",OleDbType.VarWChar,50))
		cmd.Parameters("@name").Value=strName
		cmd.Parameters.Add(New OleDbParameter("@tel",OleDbType.VarWChar,50))
		cmd.Parameters("@tel").Value=strTel
		cmd.Parameters.Add(New OleDbParameter("@email",OleDbType.VarWChar,50))
		If strEmail<>"" Then
			cmd.Parameters("@email").Value=strEmail
		Else
			cmd.Parameters("@email").Value=DBNull.Value						'赋值NULL
		End If
		cmd.Parameters.Add(New OleDbParameter("@intro",OleDbType.VarWChar,1000))
		If strIntro<>"" Then
			cmd.Parameters("@intro").Value=strIntro
		Else
			cmd.Parameters("@intro").Value=DBNull.Value						'赋值NULL
		End If		
		cmd.Parameters.Add(New OleDbParameter("@submit_date",OleDbType.Date))
		cmd.Parameters("@submit_date").Value=dtmSubmit_Date
		cmd.Parameters.Add(New OleDbParameter("@UserPWD",OleDbType.VarWChar,50))
		cmd.Parameters("@UserPWD").Value=strUserPWD
		'下面执行插入记录操作
		conn.open()                             
		cmd.ExecuteNonQuery()					
		conn.close()                            
	End Sub

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

	'该过程用来更新记录
	Public Sub Update(intId As Integer,strName As String,strTel As String,strEmail As String,strIntro As String)
		'下面建立Connection对象，并利用含有参数的SQL语句更新数据库
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Update users Set name=@name,tel=@tel,email=@email,intro=@intro Where id=@id" 
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面给参数赋值
		cmd.Parameters.Add(New OleDbParameter("@name",OleDbType.VarWChar,5))
		cmd.Parameters("@name").Value=strName
		cmd.Parameters.Add(New OleDbParameter("@tel",OleDbType.VarWChar,13))
		cmd.Parameters("@tel").Value=strTel
		'下面给@email赋值，如果客户没有输入，则赋NULL值
		cmd.Parameters.Add(New OleDbParameter("@email",OleDbType.VarWChar,50))
		If strEmail<>"" Then
			cmd.Parameters("@email").Value=strEmail
		Else
			cmd.Parameters("@email").Value=DBNull.Value						'赋值NULL
		End If
		'下面给@intro赋值，如果客户没有输入，则赋NULL值
		cmd.Parameters.Add(New OleDbParameter("@intro",OleDbType.VarWChar))
		If strIntro<>"" Then
			cmd.Parameters("@intro").Value=strIntro
		Else
			cmd.Parameters("@intro").Value=DBNull.Value						'赋值NULL
		End If		
		cmd.Parameters.Add(New OleDbParameter("@id",OleDbType.Integer))
		cmd.Parameters("@id").Value=intId									
		'执行更新操作
		conn.Open()
		cmd.ExecuteNonQuery()
		conn.Close()
	End Sub

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

'第2个类仅仅用来返回一条记录的各个字段值
Public Class DataUser														'DataUser是自己定义的类的名称
	'下面定义了几个属性，用来返回对应字段值
	Public Id As Integer
	Public name As string
	Public tel As String
	Public email As String
	Public intro As String
	Public submit_date As Date
	Public UserPWD As String
	
	'这是构造函数，在其中给每一个属性赋值
	Public Sub New(strConn As String,intId As Integer)
		Dim conn As New OleDbConnection(strConn)							'建立Connection对象
		Dim strSql As String="Select * From users Where id=" & intId		'建立SQL语句
		Dim cmd As New OleDbCommand(strSql, conn)							'建立Command对象
		conn.Open()                                            
		Dim dr As OleDbDataReader = cmd.ExecuteReader()						'建立DataReader对象
		'下面读取数据，并给属性赋值
		dr.Read()															
		Id=dr.Item("Id")													'它和intId实际上是一样的
		name=dr.Item("name").ToString
		tel=dr.Item("tel").ToString
		email=dr.Item("email").ToString										
		intro=dr.Item("intro").ToString										
		submit_date=dr.Item("submit_date")
		UserPWD=dr.Item("UserPWD").ToString
		conn.Close()                                              
	End Sub
End Class

End NameSpace