Click here to Skip to main content
12,398,225 members (53,221 online)
Click here to Skip to main content
Add your own
alternative version

Stats

109.9K views
8.9K downloads
101 bookmarked
Posted

SQL Connection Dialog

, 28 Mar 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Connection dialog and saving the connection string
Screenshot - SQL_Connection_Dialog_Screen_Shot.jpg

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

TWallick
Web Developer
United States United States
Lead programmer for Document Control Systems in Louisville, Ky.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Hernán Hegykozi15-Oct-12 17:56
memberHernán Hegykozi15-Oct-12 17:56 
Suggestionno need to build form. just reference and a 6 lines of code Pin
DamienMcK1-Nov-11 3:54
memberDamienMcK1-Nov-11 3:54 
GeneralCorrection Pin
DAVNETBILL16-Jun-11 5:04
memberDAVNETBILL16-Jun-11 5:04 
GeneralA bit of Luck and many hours I reckon i got it to work? Pin
DAVNETBILL15-Jun-11 16:07
memberDAVNETBILL15-Jun-11 16:07 
Generalapp.config Pin
Lucedoriente28-Sep-10 11:45
memberLucedoriente28-Sep-10 11:45 
QuestionTest Connection Pin
DAVNETBILL14-Sep-10 3:56
memberDAVNETBILL14-Sep-10 3:56 
GeneralMy vote of 5 Pin
jslx731-Jul-10 23:47
memberjslx731-Jul-10 23:47 
GeneralMicrosoft's own version of this dialog Pin
Laughing.John14-Jun-10 4:41
memberLaughing.John14-Jun-10 4:41 
GeneralMy vote of 5 Pin
ziscoooo1-Jun-10 22:22
memberziscoooo1-Jun-10 22:22 
GeneralSqlFileConnectionProperties, SqlConnectionUIControl Pin
Jay Bienvenu4-Sep-09 3:00
memberJay Bienvenu4-Sep-09 3:00 
QuestionGood but...for Oracle? Pin
Astroman10129-Jan-09 0:39
memberAstroman10129-Jan-09 0:39 
QuestionHow to display IP Addresses instead Server Name Pin
AriMaga26-May-08 12:04
memberAriMaga26-May-08 12:04 
QuestionError in source code? Pin
Lucedoriente28-Mar-08 1:00
memberLucedoriente28-Mar-08 1:00 
AnswerRe: Error in source code? Pin
TWallick28-Mar-08 6:29
memberTWallick28-Mar-08 6:29 
Question.tlb not loadable ?? Pin
dherrmann25-Nov-07 4:52
memberdherrmann25-Nov-07 4:52 
AnswerRe: .tlb not loadable ?? Pin
TWallick29-Nov-07 9:23
memberTWallick29-Nov-07 9:23 
GeneralRe-distributable Pin
rctaubert17-Nov-07 3:11
memberrctaubert17-Nov-07 3:11 
GeneralRe: Re-distributable Pin
Laughing John4-Apr-08 14:46
memberLaughing John4-Apr-08 14:46 
GeneralAdded the compiled DLL Pin
TWallick15-Nov-07 4:39
memberTWallick15-Nov-07 4:39 
GeneralConnection strings in the IDE Pin
DumpsterJuice14-Nov-07 1:17
memberDumpsterJuice14-Nov-07 1:17 
GeneralRe: Connection strings in the IDE Pin
TWallick15-Nov-07 4:13
memberTWallick15-Nov-07 4:13 
GeneralUsing Data Connection Dialog, can not recreate the test project Pin
rabbaszadeh2007@yahoo.com3-Aug-11 4:30
memberrabbaszadeh2007@yahoo.com3-Aug-11 4:30 
GeneralErrors when I use your introduction Pin
dherrmann13-Nov-07 11:49
memberdherrmann13-Nov-07 11:49 
GeneralRe: Errors when I use your introduction Pin
TWallick15-Nov-07 4:25
memberTWallick15-Nov-07 4:25 
GeneralRe: Errors when I use your introduction Pin
dherrmann15-Nov-07 5:30
memberdherrmann15-Nov-07 5:30 
GeneralTest doesn't start Pin
dherrmann13-Nov-07 11:05
memberdherrmann13-Nov-07 11:05 
GeneralRe: Test doesn't start Pin
TWallick15-Nov-07 4:30
memberTWallick15-Nov-07 4:30 
GeneralIn context - DataLinksClass Pin
Jon-W5-Nov-07 21:49
memberJon-W5-Nov-07 21:49 
GeneralRe: In context - DataLinksClass Pin
TWallick6-Nov-07 4:38
memberTWallick6-Nov-07 4:38 
GeneralRe: In context - DataLinksClass Pin
Luke Lovegrove1-Apr-08 16:48
memberLuke Lovegrove1-Apr-08 16:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160721.1 | Last Updated 28 Mar 2008
Article Copyright 2007 by TWallick
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid