asked on

Modifying a connection string

I would like to be able to dynamically modify an application's connection string based on a new location of the database. For instance, I have an application (snippet below) that Gets an SQL Connection string from My.Settings prior to opening it. Unfortunately, when i deploy, the target computer's database location does not match the copy i have in the development environment. So basically, I need a way to recover from the connection.open statement timing out. Ideally, I would propose to do something to update the connection string in the catch block after recording the error message.

Thanks in advance for any help you may be able to provide.

Friend Class Connection
    Private Shared m_Connection As SqlConnection
    Private Shared m_LastError As String
    'Private connDialog As SqlConnectionStringBuilder
    ''' <summary>
    ''' Shared method that returns a reference to an SqlConnection object for the Travel Database.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>If this is the first time the method is called, a new SqlConnection is constructed.</remarks>
    Public Shared Function GetConnection() As SqlConnection
        If m_Connection Is Nothing Then
            Try
                m_Connection = New SqlConnection(My.Settings.TravelConnectionString)
            Catch ex As Exception
                m_LastError = ex.Message
 
            End Try
        End If
        Return m_Connection
    End Function
 
    ''' <summary>
    ''' Opens the database connection.  Checks first to see if the connection is already open.
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub Open()
        If m_Connection.State = ConnectionState.Closed Then
            m_Connection.Open()
        End If
    End Sub
 
    ''' <summary>
    ''' Closes the database connection.
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub Close()
        m_Connection.Close()
    End Sub
End Class

Open in new window

lwebber

You shouldn't have the connection string inside your app's procedural code at all. The best place is in you application settings. Visual Studio will create these for you. In the Visual Studio Help system, search for "Application Settings" for more information.

jackwebb22002

ASKER

The connection string is in my application settings, and visual studio created them. Hence the statement:
m_Connection = New SqlConnection(My.Settings.TravelConnectionString)
in fact, this User setting is actually:
Data Source=JJW-HP\sqlexpress;AttachDbFilename="F:\COP2\\Project2\Travel.mdf";Initial Catalog=Travel;Integrated Security=True

The problem arises in my customer's placement of the database, i.e on a drive/folder other than where I had it. THerefore, I'd like my application to be able to handle resetting the location of the file, within the connection string.

Kevin Robinson

A few points here

1) The class should only really return a connection string not a SqlConnection
2) The function does not need to be shared
3) If this is a SQL server you do not need to use a 'F:\COP2\\Project2\Travel.mdf' path use the free SQL Server for small enviorments and host it on a server via IP address.

Additional point
When you set the "m_LastError = ex.Message" you may want to raise an event so it can be caught by the controlling class.

jackwebb22002

ASKER

Thanks for your input. Given the code snippet below, your points:
1) A brethren class calls for the SqlConnection to build it's SqlCommand
2) I made it shared so that other classes could access it without creating an instance of the class. Was I mistaken here?
3) I am using SqlExpress. But that still does not preclude my need to dynamically alter the location of the MDF.

I only set m_LastError last night prior to submitting my question. It's not mormally there, as the brethren classes use Try...Catch to raise exceptions. This exercise is an attempt to handle the Timeout Exception, and gracefully recover

Option Strict On
Imports System.Data.SqlClient
Friend Class Utils
 
    ''' <summary>
    ''' Returns a DataReader that has been initialized by the query or stored procedure.  Does NOT close the Connection.  Caller must remember to do this.
    ''' As a precaution, close any connection left open.
    ''' </summary>
    ''' <param name="procnameOrQuery">query or stored procedure</param>
    ''' <param name="cmdType">CommandType enumeration</param>
    ''' <param name="param1"></param>
    ''' <param name="param2"></param>
    ''' <param name="param3"></param>
    ''' <param name="param4"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Shared Function GetReader(ByVal procnameOrQuery As String, ByVal cmdType As System.Data.CommandType, _
                              Optional ByVal param1 As SqlParameter = Nothing, _
                              Optional ByVal param2 As SqlParameter = Nothing, _
                              Optional ByVal param3 As SqlParameter = Nothing, _
                              Optional ByVal param4 As SqlParameter = Nothing) _
                              As SqlDataReader
        Dim cmd As New SqlCommand(procnameOrQuery, Connection.GetConnection())
        cmd.CommandType = cmdType
        ' If there are query parameters, add them to the command.
        With cmd.Parameters
            If param1 IsNot Nothing Then .Add(param1)
            If param2 IsNot Nothing Then .Add(param2)
            If param3 IsNot Nothing Then .Add(param3)
            If param4 IsNot Nothing Then .Add(param4)
        End With
        Try
            Connection.Open()
            Return cmd.ExecuteReader
        Finally
            cmd.Dispose()
        End Try
        Return Nothing
    End Function
 
 
    ''' <summary>
    ''' Returns a DataTable that has been initialized by an SQL query.
    ''' </summary>
    ''' <param name="procnameOrQuery">query or stored procedure</param>
    ''' <param name="cmdType">CommandType enumeration</param>
    ''' <param name="param1"></param>
    ''' <param name="param2"></param>
    ''' <param name="param3"></param>
    ''' <param name="param4"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Shared Function GetTable(ByVal procnameOrQuery As String, ByVal cmdType As System.Data.CommandType, _
                              Optional ByVal param1 As SqlParameter = Nothing, _
                              Optional ByVal param2 As SqlParameter = Nothing, _
                              Optional ByVal param3 As SqlParameter = Nothing, _
                              Optional ByVal param4 As SqlParameter = Nothing) _
                              As DataTable
        ' Load a DataTable from the database using either query text or a stored procedure
        Dim table As New DataTable
        Dim reader As SqlDataReader
        Try
            reader = GetReader(procnameOrQuery, cmdType, param1, param2, param3, param4)
            table.Load(reader)
            reader.Close()
            Return table
        Finally
            Connection.Close()
        End Try
        Return Nothing
    End Function
 
    ''' <summary>
    ''' Executes a non row returning query by calling
    ''' the SqlCommand.ExecuteNonQuery method. If no exception
    ''' is thrown, a count of the number of affected rows is 
    ''' returned. If an exception is thrown, -1 is returned and
    ''' an error message is assigned to the LastError variable.
    ''' </summary>
    ''' <param name="procnameOrQuery">The name of a stored procedure
    ''' or SQL query in text format.</param>
    ''' <param name="cmdType">Indicates whether the first argument
    ''' contains a text query or stored proceudre.</param>
    ''' <param name="param1">First optional query parameter</param>
    ''' <param name="param2">Second optional query parameter</param>
    ''' <param name="param3">Third optional query parameter</param>
    ''' <param name="param4">Fourth optional query parameter</param>
    '''
    Public Shared Function ExecuteNonQuery(ByVal procnameOrQuery As String, ByVal cmdType As System.Data.CommandType, _
                                                                    Optional ByVal param1 As SqlClient.SqlParameter = Nothing, _
                                                                    Optional ByVal param2 As SqlClient.SqlParameter = Nothing, _
                                                                    Optional ByVal param3 As SqlClient.SqlParameter = Nothing, _
                                                                    Optional ByVal param4 As SqlClient.SqlParameter = Nothing) _
                                                                As Integer
 
        Dim cmd As New SqlCommand(procnameOrQuery, Connection.GetConnection())
        cmd.CommandType = cmdType
 
        ' If there are query parameters, add them to the Command.
        With cmd.Parameters
            If param1 IsNot Nothing Then .Add(param1)
            If param2 IsNot Nothing Then .Add(param2)
            If param3 IsNot Nothing Then .Add(param3)
            If param4 IsNot Nothing Then .Add(param4)
        End With
 
        Try
            Connection.Open()
            Return cmd.ExecuteNonQuery()
        Finally
            Connection.Close()
            cmd.Dispose()
        End Try
        Return -1
    End Function
 
    ''' <summary>
    ''' Executes a Scalar query and returns the value produced
    ''' by the SqlCommand.ExecuteScalar method.
    ''' </summary>
    ''' <param name="procnameOrQuery">The name of a stored procedure
    ''' or SQL query in text format.</param>
    ''' <param name="cmdType">Indicates whether the first argument
    ''' contains a text query or stored proceudre.</param>
    ''' <param name="param1">First optional query parameter</param>
    ''' <param name="param2">Second optional query parameter</param>
    ''' <param name="param3">Third optional query parameter</param>
    ''' <param name="param4">Fourth optional query parameter</param>
    '''
    Shared Function GetScalar(ByVal procnameOrQuery As String, ByVal cmdType As System.Data.CommandType, _
                                                Optional ByVal param1 As SqlClient.SqlParameter = Nothing, _
                                                Optional ByVal param2 As SqlClient.SqlParameter = Nothing, _
                                                Optional ByVal param3 As SqlClient.SqlParameter = Nothing, _
                                                Optional ByVal param4 As SqlClient.SqlParameter = Nothing) _
                                            As Object
 
        Dim cmd As New SqlCommand(procnameOrQuery, Connection.GetConnection())
        cmd.CommandType = cmdType
 
        ' If there are query parameters, add them to the Command.
        With cmd.Parameters
            If param1 IsNot Nothing Then .Add(param1)
            If param2 IsNot Nothing Then .Add(param2)
            If param3 IsNot Nothing Then .Add(param3)
            If param4 IsNot Nothing Then .Add(param4)
        End With
 
        Try
            Connection.Open()
            Return cmd.ExecuteScalar()
        Finally
            Connection.Close()
            cmd.Dispose()
        End Try
        Return Nothing
    End Function
End Class

Open in new window

ASKER CERTIFIED SOLUTION

dublingills

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jackwebb22002

ASKER

Thank you very much. Now I just need to learn how to work with the app config file. Is there any source of info other than the one you pointed me to?

jackwebb22002

ASKER

I was just wondering if there was a 'User Friendly' method to making changes to an Application Config File in Visual Studio 2008 / VB 2008 .NET 3.5

dublingills

Hi

The file is just a straightforward xml file so any text editor would do but I have to admit that, as I just edit the file directly in the Visual Studio 2008 I don't know whether there is anything around that does what you're looking for in a similar manner to the various project property pages.

For a thorough explanation of app.config the MSDN library is the best source of information I've found. Sorry I can't be more heplful on this is aspect.