dataguests.vb

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

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


'该类用来查询、添加、删除和更新记录
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(ByVal strTitle As String, ByVal strBody As String, ByVal strName As String, ByVal strEmail As String, ByVal 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(ByVal 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(ByVal intId As Integer, ByVal 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