none
VB.NET 数据库连接问题。 RRS feed

  • 问题

  • 以前我用VB6.0 连接数据库都是采用 类模块 或者 ADODC1控件来连接的。

    这是我以前直接用类模块连接方法
    Public Rs As New ADODB.Recordset
    Public Conn As New ADODB.Connection

    Public Sub ConnOpen() '打开数据库
    On Error GoTo ConnOpenError
    Conn.ConnectionString = "driver={sql server};server=IP;uid=帐号;pwd=密码;database=数据"
    Conn.Open
    Exit Sub
    ConnOpenError: MsgBox "出错提示信息"
    End Sub
    Public Sub ConnClose() '关闭数据库
    On Error GoTo ConnCloseError
    If Conn.State = 1 Then
    Conn.Close '关闭数据库连接
    Set Conn = Nothing '清空内存数据
    End If
    Exit Sub
    ConnCloseError:
    End Sub
    因为用VB编译生成出来,用OD加载直接爆露数据库连接字符串,很不安全。以所自已想转学VB.NET

    我现在换了vb.net 2008  感觉什么都不会,也不知道如何下手。本VB学得也不是很好,请各位指点一下。

    有举例说明最好了,VB.NET 最常用的数库连接方法,我一般都是连接到服务器上的数据库!

    在下非常喜欢VB。  几年前就入门了,总觉得自已从没进一步的提升过。随便请各位老师多多指点我一下。万分感谢!
    2009年8月19日 11:04

答案

  • #Region "数据读取模块"
    Public Class clsDataBase
        Dim SqlC As SqlClient.SqlConnection '定义SQL连接
        Dim sCnn As String '定义连接串
        Dim sErrMsg As String
        Dim bCnnDb As Boolean '是否连接到数据库
        Dim bRetry As Boolean '是重新连接到数据库
    #Region "获取执行错误后的消息"
        Private Sub LetRetMsg(ByRef mg As String)
            mg = "数据库连接失败"
        End Sub
    #End Region
    #Region "直接复制数据库链接"
        Public Function SetSqlConnection(ByVal tsCnn As SqlClient.SqlConnection) As Boolean
            Try
                If tsCnn.State = ConnectionState.Broken Or tsCnn.State = ConnectionState.Open Then
                    SqlC = tsCnn
                    sCnn = SqlC.ConnectionString.ToString()
                    SetSqlConnection = True
                    bCnnDb = True
                Else
                    SetSqlConnection = False
                End If
            Catch ex As Exception
                SetSqlConnection = False
            End Try

        End Function
    #End Region
    #Region "打开数据库连接"
        Public Function OpenDb(ByVal sServerName As String, ByVal sUserName As String, ByVal sUserPass As String, _
                                ByVal sDbName As String, Optional ByRef sRetMsg As String = "") As Boolean
            '打开数据库连接,参数 服务器名,用户名,用户密码,目标数据库,
            sRetMsg = ""
            Try

                sCnn = "user id=" + sUserName + ";data source=" + sServerName + ";persist security info=True;initial catalog=" + sDbName + ";password=" + sUserPass
                SqlC = New SqlClient.SqlConnection
                SqlC.ConnectionString = sCnn
                SqlC.Open()
                OpenDb = True
                bCnnDb = True
            Catch ex As Exception
                sRetMsg = ex.Message.ToString()
                letErrMsg(ex)
                OpenDb = False
                bCnnDb = False
            End Try
        End Function
    #End Region
    #Region "获取数据(断开数据库) sSql要执行的SQL语句,itiemout 超时的时间,sRetMsg执行的消息 ds 返回线呈的数据集,sTabName 存储的数据表"
        Public Function GetDataNotOnLine(ByVal sSql As String, ByRef ds As Data.DataSet, ByVal sTabName As String, Optional ByVal iTimeOut As Integer = 0, Optional ByRef sRetMSg As String = "") As Boolean
            '获取数据非在线
            If GetState() Then
                Try
                    Dim cmd As New SqlClient.SqlCommand
                    Dim dr As New System.Data.SqlClient.SqlDataAdapter
                    ds = Nothing
                    ds = New System.Data.DataSet
                    If iTimeOut > 0 Then
                        cmd.CommandTimeout = iTimeOut
                    End If
                    cmd.Connection = SqlC
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = sSql
                    dr.SelectCommand = cmd
                    dr.Fill(ds, sTabName)

                    dr.Dispose()
                    dr = Nothing
                    cmd.Dispose()
                    cmd = Nothing
                    GetDataNotOnLine = True
                Catch ex As Exception
                    letErrMsg(ex)
                    sRetMSg = ex.Message
                End Try
            Else
                GetDataNotOnLine = False
                LetRetMsg(sRetMSg)
            End If
        End Function
    #End Region
    #Region "更新数据"
        Public Function UpdateToDB(ByRef dr As System.Data.SqlClient.SqlDataAdapter, ByRef ds As System.Data.DataSet, ByVal TarTabName As String, Optional ByRef sRetMsg As String = "") As Boolean
            If GetState() Then
                Try
                    If dr Is Nothing Then
                        UpdateToDB = False
                        Exit Function
                    End If
                    Dim objCommandBuilder As New System.Data.SqlClient.SqlCommandBuilder(dr)
                    dr.Update(ds, TarTabName)

                    UpdateToDB = True
                Catch ex As Exception
                    UpdateToDB = False
                    sRetMsg = ex.Message
                End Try

            Else
                UpdateToDB = False
                LetRetMsg(sRetMsg)
            End If

        End Function
    #End Region
    #Region "测试数据库连接状态(打开以后)"
        Public Function TestConnState() As Boolean
            '简单测试数据库状态
            Dim ct As New SqlClient.SqlConnection
            Try
                bRetry = True
                ct.ConnectionString = sCnn
                ct.Open()
                bCnnDb = True
                TestConnState = True
                ct.Close()
                ct.Dispose()
                bRetry = False
            Catch ex As Exception
                TestConnState = False
                bCnnDb = False
                bRetry = False
                letErrMsg(ex)
            End Try
        End Function
    #End Region
    #Region "获取执行错误后的消息"
        Public Property ErrMSg() As String
            Get
                ErrMSg = sErrMsg.ToString()
            End Get
            Set(ByVal Value As String)

            End Set
        End Property
    #End Region
    #Region "对错误消息进行负值"
        Private Sub letErrMsg(ByRef e As Exception)
            '列出错误消息,提供给 ErrMSg
            sErrMsg = ""
            sErrMsg = e.Message.ToString()
        End Sub
    #End Region
    #Region "执行语句前判断数据库的状态"
        Private Function GetState() As Boolean
            '判断数据库连接状态
            If bCnnDb And Not (bRetry) Then
                GetState = True
            Else
                GetState = False
            End If
        End Function
    #End Region
    #Region "获取数据库连接字符串"
        Public Property GetConnecttion() As String
            Get
                GetConnecttion = SqlC.ConnectionString
            End Get
            Set(ByVal value As String)

            End Set
        End Property
    #End Region
    End Class

    #End Region

    这个是数据操作类,里面包括数据库的打开引用:
    imports System.data
    imports system.Data.Sqlclient
    你把数据库的各个参数保存在配置文件中就可以,该类必须先进行OpenDB或SetSqlConnection操作,
    祝:生活愉快

    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月20日 3:45
  • Hi JJAVEN,

    以上这些朋友的回答已经很到位了,我的建议是你最好封装一下,不要界面下直接一堆访问数据库的代码,把那些方法抽象出来做成Dll, 引用这个类,传入连接字符串和查询语句,返回一个DataTable,这种封装是很经典的设计。很多管理软件其实还要封装基层逻辑层再调用数据库操作的。

    至于最后一个问题,你可以用Settings.settings,那个功能很方便。先选中VB.NET项目然后点上面一个“显示所有文件”按钮。点开My Project,里面有Settings.settings,双击它。然后Name输入SqlconnString(举例), value就是那个字符串。

    用的时候
    WindowsApplication1.My.Settings.SqlconnString就得到了连接字符串。
    WindowsApplication1是项目名字。

    希望这些对你有用。

    Sincerely,
    Kira Qian
    Please mark the replies as answers if they help and unmark if they don't.
    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月21日 9:59
  • 一个简单的数据库连接的例子
        Dim ConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}{1}", Application.StartupPath, ACCESS数据库的相对路径,如果是sql server只改变下连接字符串就可以了,和VB一样)
        Dim ConfigSelectSQL As String = "SELECT * FROM xxx"
            Try
                Using conn As New OleDbConnection(ConnectionString), cmd As New OleDbCommand(ConfigSelectSQL, conn)
                    conn.Open()
                    Dim Reader As OleDbDataReader = cmd.ExecuteReader
    
                    Reader.Read()
                    If Reader.IsDBNull(Reader.GetOrdinal(字段名)) = False Then
                        MsgBox(Reader.GetString(Reader.GetOrdinal(字段名)))
                    Else
                        MsgBox("空")
                    End If
    
                    Reader.Close()
                End Using
            Catch ex As Exception
                MsgBox("错误")
            End Try
    

    I am Rayman Zhai
    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月22日 4:15

全部回复

  • 给你个简单的步骤

    1 新vb winform 程序

    2 菜单 -》 数据库  数据源视图

    3 空白窗口里面的link  建立数据源

    4 选择sql 数据库 身份密码

    5  把这个窗体里面新出现的表  拖到窗体里

    6 产生的控件  设置 dock=fill

    结束
    答案破千 马上就要到5星用户了 嘿嘿~~
    2009年8月19日 11:28
  • 这个方法我会,我想使用 类来控制,或者ADODC控制之类的来控制。
    自由扩展性大点。
    谢谢!

    2009年8月19日 12:48
  • 连接字符串可以存放在配置文件中,然后加密就可以了!
    周雪峰
    2009年8月19日 14:08
    版主
  • #Region "数据读取模块"
    Public Class clsDataBase
        Dim SqlC As SqlClient.SqlConnection '定义SQL连接
        Dim sCnn As String '定义连接串
        Dim sErrMsg As String
        Dim bCnnDb As Boolean '是否连接到数据库
        Dim bRetry As Boolean '是重新连接到数据库
    #Region "获取执行错误后的消息"
        Private Sub LetRetMsg(ByRef mg As String)
            mg = "数据库连接失败"
        End Sub
    #End Region
    #Region "直接复制数据库链接"
        Public Function SetSqlConnection(ByVal tsCnn As SqlClient.SqlConnection) As Boolean
            Try
                If tsCnn.State = ConnectionState.Broken Or tsCnn.State = ConnectionState.Open Then
                    SqlC = tsCnn
                    sCnn = SqlC.ConnectionString.ToString()
                    SetSqlConnection = True
                    bCnnDb = True
                Else
                    SetSqlConnection = False
                End If
            Catch ex As Exception
                SetSqlConnection = False
            End Try

        End Function
    #End Region
    #Region "打开数据库连接"
        Public Function OpenDb(ByVal sServerName As String, ByVal sUserName As String, ByVal sUserPass As String, _
                                ByVal sDbName As String, Optional ByRef sRetMsg As String = "") As Boolean
            '打开数据库连接,参数 服务器名,用户名,用户密码,目标数据库,
            sRetMsg = ""
            Try

                sCnn = "user id=" + sUserName + ";data source=" + sServerName + ";persist security info=True;initial catalog=" + sDbName + ";password=" + sUserPass
                SqlC = New SqlClient.SqlConnection
                SqlC.ConnectionString = sCnn
                SqlC.Open()
                OpenDb = True
                bCnnDb = True
            Catch ex As Exception
                sRetMsg = ex.Message.ToString()
                letErrMsg(ex)
                OpenDb = False
                bCnnDb = False
            End Try
        End Function
    #End Region
    #Region "获取数据(断开数据库) sSql要执行的SQL语句,itiemout 超时的时间,sRetMsg执行的消息 ds 返回线呈的数据集,sTabName 存储的数据表"
        Public Function GetDataNotOnLine(ByVal sSql As String, ByRef ds As Data.DataSet, ByVal sTabName As String, Optional ByVal iTimeOut As Integer = 0, Optional ByRef sRetMSg As String = "") As Boolean
            '获取数据非在线
            If GetState() Then
                Try
                    Dim cmd As New SqlClient.SqlCommand
                    Dim dr As New System.Data.SqlClient.SqlDataAdapter
                    ds = Nothing
                    ds = New System.Data.DataSet
                    If iTimeOut > 0 Then
                        cmd.CommandTimeout = iTimeOut
                    End If
                    cmd.Connection = SqlC
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = sSql
                    dr.SelectCommand = cmd
                    dr.Fill(ds, sTabName)

                    dr.Dispose()
                    dr = Nothing
                    cmd.Dispose()
                    cmd = Nothing
                    GetDataNotOnLine = True
                Catch ex As Exception
                    letErrMsg(ex)
                    sRetMSg = ex.Message
                End Try
            Else
                GetDataNotOnLine = False
                LetRetMsg(sRetMSg)
            End If
        End Function
    #End Region
    #Region "更新数据"
        Public Function UpdateToDB(ByRef dr As System.Data.SqlClient.SqlDataAdapter, ByRef ds As System.Data.DataSet, ByVal TarTabName As String, Optional ByRef sRetMsg As String = "") As Boolean
            If GetState() Then
                Try
                    If dr Is Nothing Then
                        UpdateToDB = False
                        Exit Function
                    End If
                    Dim objCommandBuilder As New System.Data.SqlClient.SqlCommandBuilder(dr)
                    dr.Update(ds, TarTabName)

                    UpdateToDB = True
                Catch ex As Exception
                    UpdateToDB = False
                    sRetMsg = ex.Message
                End Try

            Else
                UpdateToDB = False
                LetRetMsg(sRetMsg)
            End If

        End Function
    #End Region
    #Region "测试数据库连接状态(打开以后)"
        Public Function TestConnState() As Boolean
            '简单测试数据库状态
            Dim ct As New SqlClient.SqlConnection
            Try
                bRetry = True
                ct.ConnectionString = sCnn
                ct.Open()
                bCnnDb = True
                TestConnState = True
                ct.Close()
                ct.Dispose()
                bRetry = False
            Catch ex As Exception
                TestConnState = False
                bCnnDb = False
                bRetry = False
                letErrMsg(ex)
            End Try
        End Function
    #End Region
    #Region "获取执行错误后的消息"
        Public Property ErrMSg() As String
            Get
                ErrMSg = sErrMsg.ToString()
            End Get
            Set(ByVal Value As String)

            End Set
        End Property
    #End Region
    #Region "对错误消息进行负值"
        Private Sub letErrMsg(ByRef e As Exception)
            '列出错误消息,提供给 ErrMSg
            sErrMsg = ""
            sErrMsg = e.Message.ToString()
        End Sub
    #End Region
    #Region "执行语句前判断数据库的状态"
        Private Function GetState() As Boolean
            '判断数据库连接状态
            If bCnnDb And Not (bRetry) Then
                GetState = True
            Else
                GetState = False
            End If
        End Function
    #End Region
    #Region "获取数据库连接字符串"
        Public Property GetConnecttion() As String
            Get
                GetConnecttion = SqlC.ConnectionString
            End Get
            Set(ByVal value As String)

            End Set
        End Property
    #End Region
    End Class

    #End Region

    这个是数据操作类,里面包括数据库的打开引用:
    imports System.data
    imports system.Data.Sqlclient
    你把数据库的各个参数保存在配置文件中就可以,该类必须先进行OpenDB或SetSqlConnection操作,
    祝:生活愉快

    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月20日 3:45
  • visual basic.net的知识,和vb的知识有很大不同,建议重新系统学习之。
    http://hi.baidu.com/2009ajun
    2009年8月20日 4:04
  • 建议看一下微软的一本<ADO.NET 2.0 技术内幕>,这本就足够了。
    2009年8月20日 6:08
  • 您好,上次也是你回答我的问题,我一个不小心就把标记为答案给取消了。真是抱歉!
    请问配置文件应该如何搞。呵呵
    2009年8月21日 9:23
  • Hi JJAVEN,

    以上这些朋友的回答已经很到位了,我的建议是你最好封装一下,不要界面下直接一堆访问数据库的代码,把那些方法抽象出来做成Dll, 引用这个类,传入连接字符串和查询语句,返回一个DataTable,这种封装是很经典的设计。很多管理软件其实还要封装基层逻辑层再调用数据库操作的。

    至于最后一个问题,你可以用Settings.settings,那个功能很方便。先选中VB.NET项目然后点上面一个“显示所有文件”按钮。点开My Project,里面有Settings.settings,双击它。然后Name输入SqlconnString(举例), value就是那个字符串。

    用的时候
    WindowsApplication1.My.Settings.SqlconnString就得到了连接字符串。
    WindowsApplication1是项目名字。

    希望这些对你有用。

    Sincerely,
    Kira Qian
    Please mark the replies as answers if they help and unmark if they don't.
    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月21日 9:59
  • 一个简单的数据库连接的例子
        Dim ConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}{1}", Application.StartupPath, ACCESS数据库的相对路径,如果是sql server只改变下连接字符串就可以了,和VB一样)
        Dim ConfigSelectSQL As String = "SELECT * FROM xxx"
            Try
                Using conn As New OleDbConnection(ConnectionString), cmd As New OleDbCommand(ConfigSelectSQL, conn)
                    conn.Open()
                    Dim Reader As OleDbDataReader = cmd.ExecuteReader
    
                    Reader.Read()
                    If Reader.IsDBNull(Reader.GetOrdinal(字段名)) = False Then
                        MsgBox(Reader.GetString(Reader.GetOrdinal(字段名)))
                    Else
                        MsgBox("空")
                    End If
    
                    Reader.Close()
                End Using
            Catch ex As Exception
                MsgBox("错误")
            End Try
    

    I am Rayman Zhai
    • 已标记为答案 Kira Qian 2009年8月25日 9:11
    2009年8月22日 4:15