Dynamically declare connection string

I have an app I've written (.NET VS 2010), however there are currently 2 versions of it and I'm trying to make them into one.  The version problem is dependent on the version of SQL the source machine is running.  Some on SQL Express and a some on full blown SQL.  The app I have declares the connection string in ClassCon (a class), and basically I switch declaration strings when I compile each version (EXP or FULL) as such:

' The FULL is commented out and switched when I need to compile the full version
'Public sqlFULL As New SqlConnection("Data Source=some IP;Initial Catalog=DBNAME;User Id=test;Password=test123;")
Public sqlEXP As New SqlConnection("Data Source=some IP\SQLEXPRESS;Initial Catalog=DBNAME;User Id=test;Password=test123;")

Then I declare the connection in each form as such and have free use of it throughout the entire form:

Dim objDBconn As New ClassCon

Keep in mind, I'm still learning and almost entirely self taught...  So knowing this was ridiculous, I started researching ways to verify if the system was a FULL or EXP install.  So I revised the following block of code, which utilizes a system file for another program that needs to have the same knowledge of the install (this "other program" is 3rd party):

Dim check As Boolean = False

        Dim objReader As New System.IO.StreamReader("C:\LocalDir\LocalUDL.udl")
        Dim strTextFileInfo() As String
        Dim arrCounter As Integer = 0  
        Dim strTextToSearch As String = "SQLEXPRESS"

        Do While objReader.Peek <> -1
            ReDim Preserve strTextFileInfo(arrCounter)
            strTextFileInfo(arrCounter) = objReader.ReadLine
            arrCounter = arrCounter + 1
        For i As Integer = 0 To arrCounter - 1
            If strTextFileInfo(i).ToLower.Contains(strTextToSearch.ToLower) Then
                check = True
                check = False
            End If

Now this works perfectly, however I have to call it each time I need to declare the string.  But I was wondering if there was a way to have this declared from the start and accessible throughout the program?  I tried integrating some of the logic into a Class but I really think my blind ignorance is keeping me from understanding it (not as simple as just dropping the code in a Class).  I'm more than open to suggestions on my current code, but ultimately, I'm trying to find a way to declare the string (aka verify the version) one time, without having repeatedly reference that file.  I'm sure it doesn't eat much resource to keep checking it, but I'm looking for a more elegant solution.  Thanks again and sorry for the ignorance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephanLead Software EngineerCommented:
You can add a configuration file and inside the <connectionStrings> node you can add your own connectionstring for your sql connection. (For non-web it is called "Application Configuration File", and for web it's called "Web Configuration File".)

The configuration file looks something like this:
    <add name="Database" connectionString="Data Source=some IP;Initial Catalog=DBNAME;User Id=test;Password=test123;" providerName="System.Data.SqlClient" />

Open in new window

Then you can access this connectionstring through the ConfigurationManager (which is found in the System.Configuration library/namespace.

Simply do something like this:


Open in new window

and you will receive the connectionstring specified in the configuration file.
fizzlefryAuthor Commented:
Stephanonline...  first, thank you for your reply.  I see where you're going with this.  Rather than have the program detect it's version, you're using a config file to specify.  That is a similar direction to where I'm going, although still requires some degree of configuration, rather than being self-sufficient.  However, I don't understand how you declare and use the string from the config file.  I see your code example for accessing the string, but how is that used?  You're not declaring it as a specific type of connection.  You can't change the connection state.  Could you please give me a more direct example.  From my previous post, you'll see that is the standard I typically employ for declaring connection strings.  I'm not entirely familiar with what you're saying.
StephanLead Software EngineerCommented:
I'm trying to figure out what you are trying to archieve here.

Do you want to detect versions? Is it more than just SQLExpress and the standard? If so, check what are the differences in your database that uses standard options explicitly.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

fizzlefryAuthor Commented:
All I'm trying to determine is which version of SQL is installed on the PC, which will dictate which connection string to use.  My original concept was to have some sort of event on startup that would determine what version of SQL is installed, based on that 3rd party config file.  However, in talking with you and others, it seems I'm trying to over-complicate the process.  Let's say hypothetically, I was to use a config file of sorts, which would require initial configuration do be done by the installer:  How would I declare and use that connection string within that file?  Would I have to keep repeatedly referencing the file each time I needed the string?  Would it be declared at the top of my form with my other global variables and if so, how?
StephanLead Software EngineerCommented:
If you want it to be part of the installation, you can simply create a key in the registry which contains the connectionstring "name" for the sql connection. and the example I've shown on my first post, you can have 2 connection strings with a different name.

    <add name="Standard" connectionString="Data Source=some IP;Initial 
Catalog=DBNAME;User Id=test;Password=test123;" providerName="System.Data.SqlClient" />
<add name="SQLExpress" connectionString="Data Source=some IP;Initial Catalog=DBNAME;User Id=test;Password=test123;" providerName="System.Data.SqlClient" />

Open in new window

Here is an article that will explain how to get information from the registry.
You can not find out which version of SQL server is running without making a connection first (from the connection you can tell the version).

It is possible to install multiple versions of SQL Server on the same machine, so finding out by peeking in some installation file or in the registry or whatever you could try, is not going to tell you with 100% certainty where the database for your application is.

The only solution is to hardcode the connection string somewhere. You can make it domain-dependent (i do that often), for domain = "http://localhost", for domain = "http://stagingserver" and for domain="liveserver.com". That gives me a single codebase for different installations.

The other slution is to use the web.config file. That is basically the easiest and also what most people would do. Each installation would require its own web.config file. That is not very problematic....
fizzlefryAuthor Commented:
Sybe, let's say hypothetically I had an XML file containing the connection string.  How would I reference that string once in the application, or would I need to reference the XML each time I tried to make a connection?  At it's basest sense, I would want the program to launch, read the connection string and declare it so it's accessible throughout the form.  I can't see a way to do this, without calling to the XML file each time I need the connection.  That's my biggest problem.  Not so much where the string lives.  But how do I call it and declare it only once?
I have not read all the thread but to answer your question in the last post, you can use My.Settings namespace. You can declare settings on project properties dialog and then it lives in app.config file. You just have to use following


wherever you want to access it.
You can also declare a global variable in your application. In VB.Net it is easy using a Public Module (in App_Code as a .vb file)

Public Module Constants
    Public MyConnectionString As String
End Module

If you do this, then the variable MyConnectionString is global for the application (for all users and until the application restarts)

You only need to set it once and every page can use it after that. Of course when the application restarts, it has to be set again. You check if it has a value, and if not, get it from the xml file. It means you need to read the xml file only once.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fizzlefryAuthor Commented:
Sybe, that definitely sounds like we're going in the right direction.  And I hate to keep asking, but could you show me a little more specifically?  I'm not one to usually just ask for code, but I did post to show I'm not freeloading.  I'm just really struggling to understand what it is you're saying.  Thanks for your patience!
I am not sure what you do not understand.

There is a lot of things to explain about .Net in general and I am not sure if this is the right place for that. Let me try a little bit.

First of all, you need to understand that .Net really creates an application, which runs in the applicationpool. Compare it with starting up a desktop application. Once it is running, it keeps running until something shuts it down. While it is running it has its own variables. These variables can be used on every http request (every time an .aspx is opened by someone).

It is important that you also understand how database connections are used in .Net. Basically what you should do is create a new connection everytime you need one. There is connection pooling which is done by .Net itself (http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx), so you do not have to worry about that.

Do *not* reuse a connection unless you are very sure what you are doing (and why you are doing that). So a global connection object is a very bad thing, because it will not be able to handle all queries that the application needs.  But you can have a global connection string, which can be used to create a new connection every time you need one.

About this global connection string: it needs a global variable and luckily it is quite easy to create one. Of course you could also use an application variable (http://msdn.microsoft.com/en-us/library/94xkskdf%28v=vs.100%29.aspx), but it is easier to simply have a global variable.

But after all this, I would still recommend that you store your connectionstring in the web.config, as suggested by stephanonline above. It is widely used, and I would not worry about performance issues with that. The web.config is not just another xml-file, but essential in .Net and it is in memory permanently. Everything in it is accessible instantly. If the web.config is edited, the application restarts.
Make connString shared property of your class
Public Class ClassCon
    Private Shared _connString As String=""
    Public Shared ReadOnly Property ConnString As String
            If _connString = "" Then
                If IsExpress() Then
                    _connString = "Data Source=some IP\SQLEXPRESS ....."
                    _connString = "Data Source=some IP ....."
                End If
            End If
            Return _connString
        End Get
    End Property

    Private Shared Function IsExpress() As Boolean
'Your check if SQLExpress is using
    End Function
End Class

Open in new window

Now from any form you can call
Using conn As New SqlConnection(ClassCon.ConnString)
         'Queries here
End Using
fizzlefryAuthor Commented:
I'm very sorry for the delay in closing this question.  I took Sybe's advice and went back to the basics to really get an understanding.  The App.config file was the way to go.  Sorry to ask such a remedial question.  Sometimes a little tough love and a push in the right direction is all you need.  Thanks again for your patience.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.