HOW TO:可视化配置数据源连接字符串(VB)

HOW TO:可视化配置数据源连接字符串(一)

Author:水如烟

数据源连接字符串的配置是一大学问，这里我也只能说说怎么连接上去。初学的也是只是关心这个问题罢了，至于性能安全等等，我也是不懂的了。

各种数据源的字符串连接形式，在http://www.connectionstrings.com/上都可以方便查到。

数据源的连接因参数多，关键词不好记，所以组装这个字串有些吃力。我们需要的是有一个可视的向导窗体来简化配置的过程。就算没有NET，这个可视窗 体早已存在于Window了，那便是控制面版或管理工具里的“数据源(ODBC)”，在添加一个DSN的过程中，指导我们该如何做。而我们更乐于使用的， 还是那个“数据链接属性”的对话框。

有时觉得奇怪，既然这个向导窗体那么平常并且是重要的，为什么NET不提供出来呢？
到了NET2005，在X:/Program Files/Microsoft Visual Studio 8/Common7/IDE目录下，我们可以找到两个文件，NET真正的类文件，Microsoft.Data.ConnectionUI.dll和 Microsoft.Data.ConnectionUI.Dialog.dll，那是说，它还是给IDE专用的，还不想给Framework2.0。而 Framework2.0，程序配置文件对ConnectionString已经有一个Section了，在System.Design.Dll的 System.Data.Design命名空间下，已经有一个类ConnectionString了，在System.Data.Dll里，多了一个 System.Data.Common命名空间，其下，有关连接的有DbConnection、DbConnectionOptions、 DBConnectionString，并且为了组装字符串方便，还有个DbConnectionStringBuilder类。具体到特定连接方式时， 都继承了上面提到的类。估计呢，下一版本，应该有一个关于数据源连接的可视向导窗体了。会不呢？

其实做出数据源连接的可视向导窗体的效果，并不困难，当然，仅仅是效果了，对NET来说，不正统，所以不入流。NET的工程好宠大，宠大到重新包装 那些底层函数时，不得不摇头叹气，在这里，我就想说，它离不开这个OleDb32，甚至还离不开ADODB。以前做VB时，习惯着ADODB，有时用 DAO，到了现在，居然在引用命名空间时，在类库中看到了ADODB而不是在COM中看到它，欣喜。再仔细些看，文件是X:/Program Files/Microsoft.NET/Primary Interop Assemblies/adodb.dll，哦，原来是Interop.adodb.dll吧。

下面做出了数据源连接的可视向导窗体的效果。用到了Microsoft ActiveX Data Objects 2.8 Library(即ADODB)和Microsoft OLE DB Service Component 1.0 Type Library(即那个“数据链接属性”窗体)。为了适用不同版本，我用CreateObject间接引用，不直接引用。

 

处理连接字符串，有两种情形，一是新建一个连接字符串，二是编辑已有的连接定符串。处理的过程代码参考如下：
新建连接字符串(http://support.microsoft.com/default.aspx?scid=kb;EN-US;310083)

Dim strConn as String
Dim objDataLinks as DataLinks
Dim bolStatus as Boolean
Dim cnn as ADODB.Connection

Set cnn = New ADODB.Connecton
Set objDataLinks = New Datalinks

' Get user's first settings for the connection.

StrConn = ObjDataLink.PromptNew
MsgBox(strConn)

' Assign the settings to the connection object.

cnn.ConnectionString = strConn

' Display connection properties for editing.

bolStatus = objDataLinks.PromptEdit(cnn)
If bolStatus = False Then
   MsgBox("Changes were canceled")
Else
   MsgBox("New settings are: " & cnn.ConnectionString)
End If

Set objDataLinks = Nothing
Set cnn = Nothing

 

编辑连接字符串(http://windowssdk.msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbidatasourcelocator_promptedit.asp)

Dim bolStatus as Boolean
Dim cnn as ADODB.Connection
Dim objDataLinks as DataLinks
Set cnn = New ADODB.Connecton
Set objDataLinks = New Datalinks
cnn.Provider="Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = "Northwind.mdb"
bolStatus = objDataLinks.PromptEdit(cnn)
If bolStatus = False Then
   MsgBox("User pressed cancel button")
End If
MsgBox(cnn.ConnectionString)
Set objDataLinks = Nothing
Set cnn = Nothing

下一节主要给出VB.NET代码。

HOW TO:可视化配置数据源连接字符串(二)

Author: 水如烟 

  

文件AdoConnectionDialog.vb

Option   Strict   Off
Namespace uData Namespace uData.ConnectionUI
    Public Class AdoConnectionDialogClass AdoConnectionDialog

        Private gConnectionString As String = Nothing

        Public Property ConnectionString()Property ConnectionString() As String
            Get
                Return gConnectionString
            End Get

            Set(ByVal value As String)
                gConnectionString = value
            End Set
        End Property


        Public Function ShowDialog()Function ShowDialog() As System.Windows.Forms.DialogResult

            Return ShowDialog(Nothing)
        End Function

        Public Function ShowDialog()Function ShowDialog(ByVal owner As System.Windows.Forms.Form) As System.Windows.Forms.DialogResult
            Dim mResult As System.Windows.Forms.DialogResult = System.Windows.Forms.DialogResult.OK

            Dim AdoConnection As Object
            Dim DataLinks As Object = CreateObject("DataLinks")

            If owner Is Nothing Then
                DataLinks.hWnd = IntPtr.Zero
            Else
                DataLinks.hWnd = owner.Handle
            End If

            If gConnectionString Is Nothing OrElse gConnectionString.Trim.Equals(String.Empty) Then '新建字符串
                AdoConnection = DataLinks.PromptNew()
                mResult = IIf(Not AdoConnection Is Nothing, System.Windows.Forms.DialogResult.OK, System.Windows.Forms.DialogResult.Cancel)

            Else '编辑已有字符串
                AdoConnection = CreateObject("ADODB.Connection")
                AdoConnection.ConnectionString = gConnectionString
                mResult = IIf(DataLinks.PromptEdit(AdoConnection), System.Windows.Forms.DialogResult.OK, System.Windows.Forms.DialogResult.Cancel)
            End If

            If mResult = System.Windows.Forms.DialogResult.OK Then
                gConnectionString = AdoConnection.ConnectionString
            End If

            AdoConnection = Nothing
            DataLinks = Nothing

            Return mResult
        End Function
    End Class
End Namespace

上面的类可视化生成或编辑一个AdoConnection的数据源连接字符串.测试代码如下(各位使用时注意命名空间):

     Dim  t  As   New  LzmTW.uData.ConnectionUI.AdoConnectionDialog
     Private   Sub Button2_Click() Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If t.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Console.WriteLine(t.ConnectionString)
        Else
            Console.WriteLine("Cancel")
        End If
    End Sub

下面的处理关键是我们常用到的OleDbConnection、OdbcConnection、SqlConnection的 ConnectionString与AdoConnection的ConnectionString如何相互转换。这好象并不简单，看来还得掌握一些 AdoConnection的相关特性才行。
还得分节。下一节，AdoConnection的连接字符串有关特性

HOW TO:可视化配置数据源连接字符串(三)

Author: 水如烟

 这一节原计划说AdoConnection连接字符串有关特性的,在这省了,以下只贴代码了.有些匆忙.仅供参考.



项目除了上面提到的AdoConnectionDialog.vb还有以下四个文件:
DbConnectionType.vb

Namespace uData Namespace uData.ConnectionUI
    Friend Enum DbConnectionTypeEnum DbConnectionType
        OleDb
        Odbc
        Sql
        Other
    End Enum
End Namespace

ConvertType.vb

Namespace uData Namespace uData.ConnectionUI
    Friend Enum ConvertTypeEnum ConvertType
        ADO2NET
        NET2ADO
    End Enum
End Namespace

ConnectionStringConver.vb

Option   Strict   Off

Namespace uData Namespace uData.ConnectionUI
    Friend Class ConnectionStringConverClass ConnectionStringConver
        Private Sub New()Sub New()
        End Sub

        Private Shared Function AdoToOleDb()Function AdoToOleDb(ByVal adoConnectionString As String) As String
            Dim mBuilder As New OleDb.OleDbConnectionStringBuilder(adoConnectionString)
            Return mBuilder.ConnectionString
        End Function

        Private Shared Function AdoToOdbc()Function AdoToOdbc(ByVal adoConnectionString As String) As String
            Dim mResult As New Text.StringBuilder

            Dim AdoConnection As Object
            AdoConnection = CreateObject("ADODB.Connection")
            AdoConnection.ConnectionString = adoConnectionString
    
            Dim mDataSource As String = Nothing
            Dim mExtendedProperties As String = Nothing

            Dim Key As String = Nothing
            Dim Value As String = Nothing
            For Each p As Object In AdoConnection.Properties
                Key = p.Name.ToString
                Value = p.Value

                If Key <> "Provider" Then
                    If Key = "Data Source" Then
                        mDataSource = Value
                    ElseIf Key = "Extended Properties" Then
                        mExtendedProperties = Value
                    Else
                        If Not Value Is Nothing Then mResult.AppendFormat("{0}={1};", Key, Value)
                    End If
                End If
            Next

            If mDataSource = "" Then
                mResult.Append(mExtendedProperties)
            Else
                mResult.AppendFormat("Dsn={0};", mDataSource)
            End If

            AdoConnection = Nothing

            Return mResult.ToString
        End Function

        Private Shared Function AdoToSql()Function AdoToSql(ByVal adoConnectionString As String) As String
            adoConnectionString = System.Text.RegularExpressions.Regex.Replace(adoConnectionString, "Provider.*?;", "", Text.RegularExpressions.RegexOptions.IgnoreCase)
            Dim mBuilder As New SqlClient.SqlConnectionStringBuilder(adoConnectionString)
            Return mBuilder.ConnectionString
        End Function


        Private Shared Function OleDbToAdo()Function OleDbToAdo(ByVal oleDbConnectionString As String) As String
            Dim mBuilder As New OleDb.OleDbConnectionStringBuilder(oleDbConnectionString)
            If mBuilder.Provider = "" Then mBuilder.Provider = "SQLOLEDB.1;"
            Return mBuilder.ConnectionString
        End Function

        Private Shared Function OdbcToAdo()Function OdbcToAdo(ByVal odbcConnectionString As String) As String
            If odbcConnectionString Is Nothing Then odbcConnectionString = ""

            odbcConnectionString = odbcConnectionString.ToLower
            If odbcConnectionString.Contains("dsn=") Then
                Dim AdoConnection As Object
                AdoConnection = CreateObject("ADODB.Connection")
                AdoConnection.ConnectionString = odbcConnectionString

                Dim mExtendedProperties As String = AdoConnection.Properties("Extended Properties").Value
                If mExtendedProperties.IndexOf(";") = -1 Then
                    odbcConnectionString = odbcConnectionString.ToLower.Replace("dsn", "Data Source")
                End If

                AdoConnection = Nothing
            End If

            Dim mBuilder As New Odbc.OdbcConnectionStringBuilder(odbcConnectionString)
            Return mBuilder.ConnectionString
        End Function

        Private Shared Function SqlToAdo()Function SqlToAdo(ByVal sqlConnectionString As String) As String
            Dim mBuilder As New SqlClient.SqlConnectionStringBuilder(sqlConnectionString)
            sqlConnectionString = mBuilder.ConnectionString
            sqlConnectionString = System.Text.RegularExpressions.Regex.Replace(sqlConnectionString, "integrated security.*?true/s{0,};{0,1}", "Integrated Security=SSPI;", Text.RegularExpressions.RegexOptions.IgnoreCase)
            Return "Provider = SQLOLEDB.1;" & sqlConnectionString
        End Function



        Public Shared Function GetString()Function GetString(ByVal dbconnectiontype As DbConnectionType, ByVal convertype As ConvertType, ByVal cnString As String) As String

            Dim mResult As String = Nothing

            Select Case dbconnectiontype
                Case ConnectionUI.DbConnectionType.OleDb

                    If convertype = ConvertType.NET2ADO Then
                        mResult = OleDbToAdo(cnString)
                    Else
                        mResult = AdoToOleDb(cnString)
                    End If

                Case ConnectionUI.DbConnectionType.Odbc

                    If convertype = ConvertType.NET2ADO Then
                        mResult = OdbcToAdo(cnString)
                    Else
                        mResult = AdoToOdbc(cnString)
                    End If

                Case ConnectionUI.DbConnectionType.Sql

                    If convertype = ConvertType.NET2ADO Then
                        mResult = SqlToAdo(cnString)
                    Else
                        mResult = AdoToSql(cnString)
                    End If

                Case Else ' ConnectionUI.DbConnectionType.Other
                    Throw New NullReferenceException("不支持当前连接方式")
            End Select

            Return mResult
        End Function

    End Class
End Namespace

ConnectionDialog.vb

Namespace uData Namespace uData.ConnectionUI
    Public Class ConnectionDialogClass ConnectionDialog

        Private gDbConnection As IDbConnection = Nothing
        Private gConnectionString As String = Nothing

        '连接方式
        Private gDbConnectionType As DbConnectionType = DbConnectionType.Odbc
        Private gAdoConnectionString As String = Nothing

        Public Property DbConnection()Property DbConnection() As IDbConnection
            Get
                Return gDbConnection
            End Get

            Set(ByVal value As IDbConnection)
                gDbConnection = value
            End Set
        End Property

        Public Property ConnectionString()Property ConnectionString() As String
            Get
                Return gConnectionString
            End Get
            Set(ByVal value As String)
                gConnectionString = value
            End Set
        End Property

        '判断连接方式
        Private Sub Check()Sub Check()
            '优先判断DbConnection.若无，判断连接字符串;若字符串为空，则取默认连接方式为Odbc
            If gDbConnection Is Nothing Then
                If gConnectionString = "" Then
                    gDbConnectionType = DbConnectionType.Odbc
                Else
                    '根据连接字符串的特征字来判断是哪类连接方式
                    If gConnectionString.ToLower.Contains("provider=") Then
                        gDbConnectionType = DbConnectionType.OleDb
                    ElseIf gConnectionString.ToLower.Contains("dsn=") Then
                        gDbConnectionType = DbConnectionType.Odbc
                    ElseIf gConnectionString.ToLower.Contains("driver=") Then
                        gDbConnectionType = DbConnectionType.Odbc
                    Else
                        gDbConnectionType = DbConnectionType.Sql
                    End If
                End If
            Else

                If TypeOf gDbConnection Is OleDb.OleDbConnection Then
                    gDbConnectionType = DbConnectionType.OleDb
                ElseIf TypeOf gDbConnection Is Odbc.OdbcConnection Then
                    gDbConnectionType = DbConnectionType.Odbc
                ElseIf TypeOf gDbConnection Is SqlClient.SqlConnection Then
                    gDbConnectionType = DbConnectionType.Sql
                Else
                    gDbConnectionType = DbConnectionType.Other
                End If
                '也取连接字符串
                gConnectionString = gDbConnection.ConnectionString
            End If

            '初始化AdoConnectionString
            gAdoConnectionString = Nothing
        End Sub

        Public Function ShowDialog()Function ShowDialog() As System.Windows.Forms.DialogResult

            Return ShowDialog(Nothing)
        End Function

        Public Function ShowDialog()Function ShowDialog(ByVal owner As System.Windows.Forms.Form) As System.Windows.Forms.DialogResult
            Dim mResult As System.Windows.Forms.DialogResult = System.Windows.Forms.DialogResult.OK

            Check()

            Dim mDialog As New AdoConnectionDialog
            With mDialog
                .ConnectionString = ConnectionStringConver.GetString(gDbConnectionType, ConvertType.NET2ADO, gConnectionString)
                mResult = .ShowDialog(owner)
            End With

            If mResult = Windows.Forms.DialogResult.OK Then
                gConnectionString = ConnectionStringConver.GetString(gDbConnectionType, ConvertType.ADO2NET, mDialog.ConnectionString)
                If Not gDbConnection Is Nothing Then
                    '正处在连接状态下不可置值,这里强制关闭了不好,由用户处理好了
                    ' If gDbConnection.State = ConnectionState.Open Then gDbConnection.Close()
                    gDbConnection.ConnectionString = gConnectionString
                End If
            End If

            mDialog = Nothing

            Return mResult
        End Function

    End Class
End Namespace

以下为测试项目用到的一个文件:
ConnectionDialogTest.vb

Public   Class ConnectionDialogTest Class ConnectionDialogTest

    Private OleDbCn As New OleDb.OleDbConnection
    Private OdbcCn As New Odbc.OdbcConnection
    Private SqlCn As New SqlClient.SqlConnection

    Private gConnectionString As String
    Public Property ConnectionString()Property ConnectionString() As String
        Get
            Return gConnectionString
        End Get
        Set(ByVal value As String)
            gConnectionString = value
        End Set
    End Property

    Private gNowCn As Common.DbConnection
    Public ReadOnly Property DbConnection()Property DbConnection() As Common.DbConnection
        Get
            Return gNowCn
        End Get
    End Property

    Private gDialog As New LzmTW.uData.ConnectionUI.ConnectionDialog
    Public Function ShowDialog()Function ShowDialog(ByVal owner As Form, ByVal byConnectionString As Boolean, ByVal DbTypeOrConnectionstring As String) As DialogResult
        Dim mResult As DialogResult = DialogResult.OK

        If byConnectionString Then

            gDialog.ConnectionString = DbTypeOrConnectionstring
            gDialog.DbConnection = Nothing
            mResult = gDialog.ShowDialog(owner)

            If mResult = DialogResult.OK Then
                gConnectionString = gDialog.ConnectionString
                If gConnectionString.ToLower.Contains("provider=") Then
                    gNowCn = New OleDb.OleDbConnection
                ElseIf gConnectionString.ToLower.Contains("dsn=") Then
                    gNowCn = New Odbc.OdbcConnection
                ElseIf gConnectionString.ToLower.Contains("driver=") Then
                    gNowCn = New Odbc.OdbcConnection
                Else
                    gNowCn = New SqlClient.SqlConnection
                End If
                gNowCn.ConnectionString = gConnectionString
            End If
        Else

            Dim TmpCn As Common.DbConnection
            If DbTypeOrConnectionstring = "OleDbConnection" Then
                TmpCn = OleDbCn
            ElseIf DbTypeOrConnectionstring = "OdbcConnection" Then
                TmpCn = OdbcCn
            Else
                TmpCn = SqlCn
            End If

            gDialog.DbConnection = TmpCn
            mResult = gDialog.ShowDialog(owner)

            If mResult = DialogResult.OK Then
                gNowCn = TmpCn
                gConnectionString = gNowCn.ConnectionString
            End If

        End If

        Return mResult
    End Function


End Class

测试界面代码:

Public   Class Form1 Class Form1

    Private Demo As New ConnectionDialogTest

    Private Sub SetMessage()Sub SetMessage(ByVal text As String)
        Me.Label1.Text = String.Format("Msg:{0}", text)
    End Sub
    Private Sub OpenTest_Click()Sub OpenTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenTest.Click
        Demo.DbConnection.Open()
        SetMessage("已打开")
    End Sub

    Private Sub CloseTest_Click()Sub CloseTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseTest.Click
        Demo.DbConnection.Close()
        SetMessage("已关闭")
    End Sub

    Private Sub DbConnectionEdit_Click()Sub DbConnectionEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DbConnectionEdit.Click
        If Demo.ShowDialog(Me, False, Me.GetDbName) = Windows.Forms.DialogResult.OK Then
            Me.RichTextBox1.AppendText(System.Environment.NewLine & Demo.ConnectionString)
        End If
    End Sub

    Private Sub ConnectionStringEdit_Click()Sub ConnectionStringEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConnectionStringEdit.Click
        If Demo.ShowDialog(Me, True, Me.RichTextBox1.SelectedText) = Windows.Forms.DialogResult.OK Then
            Me.RichTextBox1.AppendText(System.Environment.NewLine & Demo.ConnectionString)
        End If
    End Sub

    Private Function GetDbName()Function GetDbName() As String
        Dim tmpRadio As RadioButton
        If Me.RadioButton1.Checked Then
            tmpRadio = Me.RadioButton1
        ElseIf Me.RadioButton2.Checked Then
            tmpRadio = Me.RadioButton2
        Else
            tmpRadio = Me.RadioButton3
        End If
        Return tmpRadio.Text
    End Function


End Class

项目代码下载,不做异常处理