dboperation.vb

管理毕业设计的管理系统 VB+SQL2000....

Imports System.Data.SqlClient

Public Class DBOperation
    Shared strConn As String = "Server=localhost; DataBase=abu; Integrated Security=SSPI; User ID=sa; Password=123456"

    Shared Function Insert(ByVal strSQL As String) As Boolean
        '创建SqlConnection实例
        Dim conn As SqlConnection = New SqlConnection(strConn)
        '创建SqlCommand实例
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)

        'count表示受影响的行数，初始化为0
        Dim count As Integer = 0

        Try
            '连接数据库
            conn.Open()
            '执行SQL命令
            count = comm.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

        If count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Shared Function Insert(ByVal strSQL As String, ByVal strRow As String, ByVal fileByte As Byte()) As Boolean
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)
        '执行的数据库命令中含有image类型
        comm.Parameters.Add(strRow, SqlDbType.Image, fileByte.Length).Value = fileByte

        Dim count As Integer = 0

        Try
            conn.Open()
            count = comm.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

        If count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Shared Function Delete(ByVal strSQL As String) As Boolean
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)

        Dim count As Integer = 0

        Try
            conn.Open()
            count = comm.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

        If count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Shared Function Update(ByVal strSQL As String) As Boolean
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)

        Dim count As Integer = 0

        Try
            conn.Open()
            count = comm.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

        If count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Shared Function Update(ByVal strSQL As String, ByVal strRow As String, ByVal fileByte As Byte()) As Boolean
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)
        '执行的数据库命令中含有image类型
        comm.Parameters.Add(strRow, SqlDbType.Image, fileByte.Length).Value = fileByte

        Dim count As Integer = 0

        Try
            conn.Open()
            count = comm.ExecuteNonQuery()
        Catch ex As SqlException
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

        If count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function


    Shared Function Search(ByVal strSQL As String) As DataTable
        Dim conn As SqlConnection = New SqlConnection(strConn)
        Dim comm As SqlCommand = New SqlCommand(strSQL, conn)

        conn.Open()

        '设置适配器
        Dim adapter As New SqlDataAdapter
        adapter.TableMappings.Add("Table", "TEMP")
        adapter.SelectCommand = comm

        '填充数据集
        Dim ObjectdsDataSet As New DataSet()
        adapter.Fill(ObjectdsDataSet)

        '关闭数据库连接
        conn.Close()

        '返回查询的表
        Return ObjectdsDataSet.Tables("TEMP")
    End Function
End Class