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

Author: 水如烟

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

 这一节原计划说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

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