SQL Connection Dialog

• Download SQL_Connection_Dialog.zip - 295.9 KB
• Download Compiled_DLL_SQL_Connection_Dialog.zip - 99.3 KB

Introduction

I set out to solve 2 of the most anoying 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. 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 to 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 leftest 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 MS provides.

Next Create a class to provide 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 allready the SaveChange_To_App_Config method will not add it and no changes will be saved.

Snippit 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