SQL Connection Dialog

• Download source - 288.52 KB
• Download compiled DLL - 99.26 KB

Introduction

I set out to solve 2 of the most annoying things about .NET:

1. No common dialog box for making connection strings
2. Getting around the Read only my.settings.connection string

The ADDO connection dialog will not directly take connection strings from Table Adapters, nor will the Table Adapters take the string generated by ADDO. This dialog will do both.

Background

When developing data bound apps, all the wonderful auto generated code is all pointing to my network not the network it will ultimately be living in. I could set the binding sources at install time through the installer, but I like the click once publishing. Also, Data sources tend to get changed/moved on some of my customers' networks. I prefer to put it in a settings dialog, which would take another article to explain, so the strings can be changed on the fly.

Giving credit where credit is due:

• For the dialog interface - I wish I knew but I cannot find the article again.
• For the save the my.settings connection string - Jakob Lithner. Runtime Connection Wizard

Using the Code

It turns out Microsoft does have a .NET UI control for building connection strings.

The app or DLL needs to reference Microsoft.Data.ConnectionUI.dll and Microsoft.Data.ConnectionUI.Dialog.dll. Both are found in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\.

Now to make the dialog box display the dialog box:

1. Start out with an empty Class Library project
2. Add a dialog Form to the project
3. Name = SQLConnectionDialog
4. Start position = CenterScreen
5. Move the OK Cancel button to the lowest leftmost corner and anchor Bottom, Left
6. Replace the forms code with the following:

   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 = The connection properties where you can pass a connection string and get the resulting connection string via cp.ConnectionStringBuilder.ConnectionString.
   • uic = The user interface component. This is the portion Microsoft provides.

Next create a class to provide properties and methods for the dialog and the Save connection string method.

1. Add a Class Item to the project
2. Replace the code

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

In your application, turn off Visual Studio hosting process in Debugging so *.exe.config will be used instead of *.vshost.exe.config which gets overwritten by app.config everytime you compile.

The my.settings connection string should atleast have Data Source= in it so the *.exe.config file has the connection string info. If it doesn't exist already, the SaveChange_To_App_Config method will not add it and no changes will be saved.

Snippet to use the 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