SQL 连接对话框

• 下载源代码 - 288.52 KB
• 下载编译后的DLL - 99.26 KB

引言

我着手解决.NET中最烦人的两件事

1. 没有用于创建连接字符串的通用对话框
2. 绕过只读的my.settings连接字符串

ADO连接对话框不会直接从表适配器获取连接字符串，表适配器也不会使用ADO生成的字符串。此对话框将同时执行这两项操作。

背景

在开发数据绑定应用程序时，所有生成的代码都指向我的网络，而不是它最终所在的网络。我可以在安装时通过安装程序设置绑定源，但我更喜欢ClickOnce发布。此外，我的某些客户的网络上的数据源往往会发生更改/移动。我更倾向于将其放在设置对话框中（这需要另一篇文章来解释），以便可以动态更改字符串。

致谢

• 对于对话框界面 - 我希望我知道，但我找不到那篇文章了。
• 对于保存my.settings连接字符串 - Jakob Lithner。运行时连接向导

Using the Code

事实证明，Microsoft确实有一个用于构建连接字符串的.NET UI控件。

应用程序或DLL需要引用Microsoft.Data.ConnectionUI.dll和Microsoft.Data.ConnectionUI.Dialog.dll。这两个文件都位于C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\中。

现在让对话框显示对话框

1. 从一个空的类库项目开始
2. 向项目添加一个对话框窗体
3. 名称 = SQLConnectionDialog
4. 起始位置 = CenterScreen
5. 将“确定”和“取消”按钮移动到左下角，并将其锚定到底部和左侧
6. 用以下代码替换窗体的代码

   Imports System
   Imports System.Drawing
   Imports System.Windows.Forms
   Imports System.ComponentModel
   Imports Microsoft.Data.ConnectionUI
   Imports System.Data.SqlClient
   
   Friend Class SQLConnectionDialog
       Private cp As SqlFileConnectionProperties
       Private uic As SqlConnectionUIControl
   
       Public Sub New()
           ' This call is required by the Windows Form Designer.
           InitializeComponent()
           ' Add any initialization after the InitializeComponent() call.
           cp = New Microsoft.Data.ConnectionUI.SqlFileConnectionProperties
           uic = New Microsoft.Data.ConnectionUI.SqlConnectionUIControl
           uic.Initialize(cp)
       End Sub
   
       'Allows the user to change the title of the dialog
       Public Overrides Property Text() As String
           Get
               Return MyBase.Text
           End Get
           Set(ByVal value As String)
               MyBase.Text = value
           End Set
       End Property
   
       'Pass the original connection string or get the 
       'resulting connection string
       Public Property ConnectionString() As String
           Get
               Return cp.ConnectionStringBuilder.ConnectionString
           End Get
           Set(ByVal value As String)
               cp.ConnectionStringBuilder.ConnectionString = value
           End Set
       End Property
   
   
       Private Sub OK_Button_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles OK_Button.Click
           Me.DialogResult = System.Windows.Forms.DialogResult.OK
           Me.Close()
       End Sub
   
       Private Sub Cancel_Button_Click(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles Cancel_Button.Click
           Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
           Me.Close()
       End Sub
   
       Private Sub Dialog1_Load(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles MyBase.Load
           Me.Padding = New Padding(5)
           Dim adv As Button = New Button
           Dim Tst As Button = New Button
   
           'Size the form and place the uic, Test connection button, 
           'and advanced button
           uic.LoadProperties()
           uic.Dock = DockStyle.Top
           uic.Parent = Me
           Me.ClientSize = Size.Add(uic.MinimumSize, New Size(10, _
               (adv.Height + 25)))
           Me.MinimumSize = Me.Size
           With adv
               .Text = "Advanced"
               .Dock = DockStyle.None
               .Location = New Point((uic.Width - .Width), (uic.Bottom + 10))
               .Anchor = (AnchorStyles.Right Or AnchorStyles.Top)
               AddHandler .Click, AddressOf Me.Advanced_Click
               .Parent = Me
           End With
   
           With Tst
               .Text = "Test Connection"
               .Width = 100
               .Dock = DockStyle.None
               .Location = _
                   New Point((uic.Width - .Width) - adv.Width - 10,_
                   (uic.Bottom + 10))
               .Anchor = (AnchorStyles.Right Or AnchorStyles.Top)
               AddHandler .Click, AddressOf Me.Test_Click
               .Parent = Me
           End With
       End Sub
   
       Private Sub Advanced_Click(ByVal sender As Object, ByVal e As EventArgs)
           'Set up a form to display the advanced connection properties
           Dim frm As Form = New Form
           Dim pg As PropertyGrid = New PropertyGrid
           pg.SelectedObject = cp
           pg.Dock = DockStyle.Fill
           pg.Parent = frm
           frm.ShowDialog()
       End Sub
   
       Private Sub Test_Click(ByVal sender As Object, ByVal e As EventArgs)
           'Test the connection
           Dim conn As New SqlConnection()
           conn.ConnectionString = cp.ConnectionStringBuilder.ConnectionString
           Try
               conn.Open()
               MsgBox("Test Connection Succeeded.", MsgBoxStyle.Exclamation)
           Catch ex As Exception
               MsgBox("Test Connection Failed.", MsgBoxStyle.Critical)
           Finally
               Try
                   conn.Close()
               Catch ex As Exception
                   
               End Try
           End Try
   
       End Sub
   End Class

   • CP = 连接属性，您可以在其中传递连接字符串并通过cp.ConnectionStringBuilder.ConnectionString获取生成的连接字符串。
   • uic = 用户界面组件。这是Microsoft提供的部分。

接下来创建一个类，为对话框和保存连接字符串方法提供属性和方法。

1. 向项目添加一个类项
2. 替换代码

Imports System.Configuration
Public Class SQL_Connection_Dialog

    Private _Frm_SQLConnectionDialog As SQLConnectionDialog

    Public Sub New()
        MyBase.New()
        _Frm_SQLConnectionDialog = New SQLConnectionDialog
    End Sub

    Public Property Title() As String
        Get
            Return Me._Frm_SQLConnectionDialog.Text
        End Get
        Set(ByVal value As String)
            Me._Frm_SQLConnectionDialog.Text = value
        End Set
    End Property

    Public Property ConnectionString() As String
        Get
            Return Me._Frm_SQLConnectionDialog.ConnectionString
        End Get
        Set(ByVal value As String)
            Me._Frm_SQLConnectionDialog.ConnectionString = value
        End Set
    End Property

    Public Sub SaveChange_To_App_Config(ByVal connectionName As String)
        Dim Config As Configuration
        Dim Section As ConnectionStringsSection
        Dim Setting As ConnectionStringSettings
        Dim ConnectionFullName As String

        'There is no inbuilt way to change application 
        'setting values in the config file.
        'So that needs to be done manually by calling config section object.

        Try
            'Concatenate the full settings name
            'This differs from Jakob Lithner. Runtime Connection Wizard
            'The ConnectionFullName needs to 
            'refer to the Assembly calling this DLL
            ConnectionFullName = String.Format("{0}.MySettings.{1}", _
                System.Reflection.Assembly.GetCallingAssembly._
                EntryPoint.DeclaringType.Namespace,_
                connectionName)

            'Point out the objects to manipulate
            Config = ConfigurationManager.OpenExeConfiguration(_
                ConfigurationUserLevel.None)
            Section = CType(Config.GetSection("connectionStrings"), _
                ConnectionStringsSection)
            Setting = Section.ConnectionStrings(ConnectionFullName)

            'Ensure connection setting is defined 
            '(Note: A default value must be set to save the
            'connection setting!)
            If IsNothing(Setting) Then Throw New Exception(_
                "There is no connection with this name" + _
                " defined in the config file.")

            'Set value and save it to the config file
            'This differs from Jakob Lithner. Runtime Connection Wizard
            'We only want to save the modified portion of the config file 
            Setting.ConnectionString = Me.ConnectionString
            Config.Save(ConfigurationSaveMode.Modified, True)

        Catch ex As Exception

        End Try
    End Sub

    Public Function ShowDialog() As System.Windows.Forms.DialogResult
        Return Me._Frm_SQLConnectionDialog.ShowDialog

    End Function

End Class

在您的应用程序中，在调试时关闭Visual Studio托管进程，以便使用*.exe.config而不是*.vshost.exe.config（每次编译时都会被app.config覆盖）。

my.settings连接字符串至少应包含Data Source=，以便*.exe.config文件包含连接字符串信息。如果它不存在，则SaveChange_To_App_Config方法不会添加它，并且不会保存任何更改。

使用DLL的代码片段

'TestConn = the name in my.settings to the connection string
Dim DLG As New SQL_Connection_Dialog.SQL_Connection_Dialog
DLG.ConnectionString = My.Settings.TestConn

DLG.Title = "Test Connection"
If DLG.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Sub

Dim CS As String = DLG.ConnectionString
DLG.SaveChange_To_App_Config("TestConn")
'Update the settings
My.MySettings.Default.Item("TestConn") = DLG.ConnectionString