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

Author:水如烟

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

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

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

DbConnectionType.vb

Namespace uDataNamespace uData.ConnectionUI

Friend Enum DbConnectionTypeEnum DbConnectionType

OleDb

Odbc

Sql

Other

End Enum

End Namespace

ConvertType.vb

Namespace uDataNamespace uData.ConnectionUI

Friend Enum ConvertTypeEnum ConvertType

ADO2NET

NET2ADO

End Enum

End Namespace

ConnectionStringConver.vb

Option Strict Off

Namespace uDataNamespace 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

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 uDataNamespace 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

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

mDialog = Nothing

Return mResult

End Function

End Class

End Namespace

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

ConnectionDialogTest.vb

Public Class ConnectionDialogTestClass 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

Return mResult

End Function

End Class

测试界面代码:

Public Class Form1Class 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

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