dbconn.vb

vb中数据库联接

Public Class DBConn
    Private StrServerName As String    '存放服务器名称
    Private StrDataBaseName As String  '存放数据库名称
    Private StrUserName As String       '存放登录数据库用户名称 
    Private StrUserPsw As String        '存放登录数据库用户密码

    Private MyConnection As System.Data.SqlClient.SqlConnection = Nothing
    Private MyConnectString As String

    Private MyCommand As System.Data.SqlClient.SqlCommand
    Private MyReader As System.Data.SqlClient.SqlDataReader
    Private MyAdapter As System.Data.SqlClient.SqlDataAdapter
    Public MyDataSet As DataSet
    Private Result(,) As String
    Private MyTable As DataTable

    Public Sub New(ByVal ServerName As String, ByVal DataBaseName As String, ByVal UserName As String, ByVal UserPsw As String)
        MyDataSet = New DataSet()
        StrServerName = ServerName      '设置服务器名称
        StrDataBaseName = DataBaseName  '设置数据库名称
        StrUserName = UserName          '设置登录数据库的用户名称
        StrUserPsw = UserPsw            '设置登录数据库的用户密码
        '生成连接字符串
        MyConnectString = "Persist Security Info=False;User ID=" & StrUserName & ";password=" & StrUserPsw & ";Initial Catalog=" & StrDataBaseName & ";Data Source=" & StrServerName & ";connection timeout=1"
        MyConnection = New System.Data.SqlClient.SqlConnection(MyConnectString)
        Try
            MyConnection.Open()             '打开数据库连接
        Catch
            Debug.WriteLine("系统中的 ConnectDatabase 发生异常。" & vbCrLf & "具体描述：[" & Err.Description & "]")           '调试：报告错误
        Finally
            MyConnection.Close()
        End Try
    End Sub
    '**********************************************************************************************
    '目的：调用存储过程并返回需要的结果
    '假设：存在对应的存储过程；参数具有一一对应的关系；同时连接字符串为固定的。
    '输入：SpName:存储过程名称
    '      Parameter:参数列表
    '输出：将对数据库产生影响
    '返回：根据调用目的的不同传回不同的结果
    '**********************************************************************************************
    Public Function ExecuteSp(ByVal SpName As String, ByVal Parameter As String) As String
        Dim StrSql As String
        Try
            MyConnection.Open()
            StrSql = "exec " & SpName & " " & Parameter     '设置SQL串
            MyCommand = New System.Data.SqlClient.SqlCommand()
            MyCommand.CommandText = StrSql
            MyCommand.Connection = MyConnection
            MyReader = MyCommand.ExecuteReader
            While MyReader.Read()
                ExecuteSp = CStr(MyReader.GetValue(0))  '取出返回值
            End While
        Catch ex As Exception
            Debug.WriteLine("系统中的 ConnectDatabase 发生异常。" & vbCrLf & "具体描述：[" & Err.Description & "]")           '调试：报告错误
        Finally
            MyConnection.Close()
            MyReader.Close()  '释放内存
            MyCommand = Nothing
        End Try
    End Function
    '**********************************************************************************************
    '目的：查询数据库，并以数据集的形式返回查询结果 
    '假设：参数存在且一一对应
    '输入：View:与已修改表对应的本地数据集中的视图
    '      StrSql:查询SQL串
    '输出：无
    '返回：以数据集的形式返回查询结果   
    '**********************************************************************************************
    Public Function DstQuery(ByVal StrSql As String, ByVal View As String) As DataSet
        Try
            MyCommand = New System.Data.SqlClient.SqlCommand(StrSql, MyConnection)
            MyAdapter = New System.Data.SqlClient.SqlDataAdapter()
            MyAdapter.SelectCommand = MyCommand
            MyAdapter.Fill(MyDataSet, View)                     '填充数据集
            DstQuery = MyDataSet
        Catch ex As Exception
            Debug.WriteLine("系统中的 ConnectDatabase 发生异常。" & vbCrLf & "具体描述：[" & Err.Description & "]")           '调试：报告错误
        Finally
            MyCommand = Nothing                                 '释放内存
            MyAdapter.Dispose()
        End Try
    End Function

    Public Function ArrayResult(ByVal strSQL As String) As String(,)
        Dim i As Integer = 0
        Dim j As Integer = 0
        Dim array(,) As String
        Try
            MyConnection.Open()
            MyCommand = New System.Data.SqlClient.SqlCommand(strSQL, MyConnection)
            MyAdapter = New System.Data.SqlClient.SqlDataAdapter()
            MyAdapter.SelectCommand = MyCommand
            MyDataSet = New DataSet()
            MyAdapter.Fill(MyDataSet, "MyTable")

            Dim RowCount As Integer = MyDataSet.Tables("MyTable").Rows.Count
            Dim CollumnCount As Integer = MyDataSet.Tables("MyTable").Columns.Count
            ReDim array(RowCount, CollumnCount)

            For i = 0 To RowCount - 1
                For j = 0 To CollumnCount - 1
                    array(i, j) = CStr(MyDataSet.Tables("MyTable").Rows(i).Item(j) & "")
                Next
            Next

        Catch ex As Exception
            Debug.WriteLine("系统中的 ConnectDatabase 发生异常。" & vbCrLf & "具体描述：[" & Err.Description & "]")           '调试：报告错误
        Finally
            MyConnection.Close()

        End Try
        Return array
    End Function

    Public Sub ExecuteNonQuery(ByVal strSQL As String)
        Try
            MyConnection.Open()

            MyCommand = New System.Data.SqlClient.SqlCommand(strSQL, MyConnection)
            MyCommand.ExecuteNonQuery()

        Catch ex As Exception
            Debug.WriteLine("系统中的 ConnectDatabase 发生异常。" & vbCrLf & "具体描述：[" & Err.Description & "]")           '调试：报告错误
        Finally
            MyConnection.Close()
        End Try
    End Sub
End Class