csqlclient.vb

这是一个关于图书仓库管理系统的程序源代码。是我的毕业设计的作品

Imports System.Data
Imports System.Data.SqlClient

Public Class CSqlClient
    Private strConnect As String        '连接数据库字符串
    Private myConnection As SqlConnection = Nothing 'SqlConnection对象
    Private myConnectFlag As Boolean = False    '是否连接成功标识
    Private strSQL As String    'SQL语句字符串

    Public Sub New()

    End Sub

    '连接数据库(Sql Server)
    Public Function connect(ByRef m_strConnect As String) As Boolean
        Try
            strConnect = m_strConnect
            myConnection = New SqlConnection(strConnect)
            myConnection.Open()
            myConnectFlag = True
        Catch e As Exception
            Console.WriteLine(e.ToString())
        End Try
        Return myConnectFlag
    End Function

    '关闭数据库
    Public Sub close()
        If (myConnectFlag) Then
            myConnection.Close()
        End If
    End Sub

    '获取SqlServer对象
    Public Function getConnectSqlServer() As SqlConnection
        If Not (myConnectFlag) Then
            Console.WriteLine("ERROR:sqlserver didn't open")
        Else
            getConnectSqlServer = myConnection
        End If
    End Function

    '创建一个command对象
    Public Function CreateCommand(ByRef m_strSQL As String, ByRef m_Param() As SqlParameter, _
                                        ByRef m_SQLCmdType As CommandType) As SqlCommand
        Dim myCommand As New SqlCommand(m_strSQL, myConnection)
        myCommand.CommandType = m_SQLCmdType
        Dim Param As SqlParameter
        If Not m_Param Is Nothing Then
            For Each Param In m_Param
                myCommand.Parameters.Add(Param)
            Next
        End If

        Try
            'myConnection.Close()
            'myConnection.Open()
            Return myCommand
        Catch err As SqlException
            Console.WriteLine(err.ToString())
            Return Nothing
        End Try
    End Function

    '执行SQL语句
    Public Overloads Function ExecuteSQL(ByRef m_strSQL As String, ByRef m_DataSet As DataSet) As Boolean
        ' Dim myLocalDataSet As New DataSet()
        Dim myLocalError As String = ""
        Dim mySuccessFlag As Boolean = False
        Try
            If (myConnectFlag) Then
                'Dim myCommand As New SqlCommand(m_strSQL, myConnection)
                Dim myDataAdapter As New SqlDataAdapter(m_strSQL, myConnection)
                'myDataAdapter.SelectCommand = myCommand
                myDataAdapter.Fill(m_DataSet)

                mySuccessFlag = True
            Else
                myLocalError = "[Connection Failed]: Invalid Connection parameters."
            End If
        Catch e As SqlException
            Dim myError As SqlError
            For Each myError In e.Errors
                myLocalError += "[Error Server]:" + myError.Server + "[Source]: " + myError.Source + "[Message]: " + myError.Message
            Next
            Console.WriteLine(myLocalError)
            'Finally
            'm_strSQLError = myLocalError
            'm_DataSet = myLocalDataSet
        End Try
        Return mySuccessFlag
    End Function

    Public Overloads Function ExecuteSQL(ByVal m_strSQL As String, ByRef m_DataReader As SqlDataReader) As Boolean

        Dim myCommand As New SqlCommand(m_strSQL, myConnection)

        Try
            m_DataReader = myCommand.ExecuteReader()
            Return True
        Catch err As SqlException
            Console.WriteLine(err.ToString())
            Return False
        End Try

    End Function


    '执行存储过程
    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter, ByRef m_DataSet As DataSet) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)
        Dim myDataAdapter As New SqlDataAdapter(myCommand)

        Try
            myDataAdapter.Fill(m_DataSet)
            Return True
        Catch err As SqlException
            Console.WriteLine(err.ToString())
            Return False
        End Try
    End Function

    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)

        Try
            myCommand.ExecuteNonQuery()
            Return True
        Catch err As SqlException
            Console.WriteLine(err.ToString())
            Return False
        End Try
    End Function

    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter, ByRef m_DataReader As SqlDataReader) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)

        Try
            m_DataReader = myCommand.ExecuteReader
            Return True
        Catch err As SqlException
            Console.WriteLine(err.ToString())
            Return False
        End Try
    End Function

    Public Function UpdateDBFromDatagrid(ByVal strSQL As String, ByVal dsChanges As DataSet)
        Dim myDataAdapter As New SqlDataAdapter(strSQL, myConnection)
        Dim myCB As New SqlCommandBuilder(myDataAdapter)

        myDataAdapter.Update(dsChanges)

    End Function

    '############################################################################################

    '获取数据库的表名
    Public Function getTables(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myTables(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myTables(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT Name FROM SysObjects WHERE Type='U'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function


    '获取数据库的视图名
    Public Function getViews(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myViews(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myViews(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT * FROM SysObjects WHERE Type='V'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function

    '获取数据库的存储过程名
    Public Function getStoredProcs(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myStoredProcs(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myStoredProcs(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT * FROM SysObjects WHERE Type='P'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function

End Class