Solved

Dynamically declare connection string

Posted on 2013-10-22
13
201 Views
Last Modified: 2014-07-22
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
        Loop
        For i As Integer = 0 To arrCounter - 1
            If strTextFileInfo(i).ToLower.Contains(strTextToSearch.ToLower) Then
                check = True
            Else
                check = False
            End If
        Next

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.
0
Comment
Question by:fizzlefry
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 16

Expert Comment

by:Stephan
Comment Utility
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:
<configuration>
  <connectionStrings>
    <add name="Database" connectionString="Data Source=some IP;Initial Catalog=DBNAME;User Id=test;Password=test123;" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

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:

ConfigurationManager.ConnectionStrings("Database").ConnectionString

Open in new window


and you will receive the connectionstring specified in the configuration file.
0
 

Author Comment

by:fizzlefry
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:Stephan
Comment Utility
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.
0
 

Author Comment

by:fizzlefry
Comment Utility
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?
0
 
LVL 16

Expert Comment

by:Stephan
Comment Utility
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.

<configuration>
  <connectionStrings>
    <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" />
  </connectionStrings>
</configuration>

Open in new window


Here is an article that will explain how to get information from the registry.
http://www.codeproject.com/Articles/3389/Read-write-and-delete-from-registry-with-C
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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....
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:fizzlefry
Comment Utility
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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

My.Settings.SettingName

wherever you want to access it.
0
 
LVL 28

Accepted Solution

by:
sybe earned 400 total points
Comment Utility
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.
0
 

Author Comment

by:fizzlefry
Comment Utility
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!
0
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:Ark
Comment Utility
Make connString shared property of your class
Public Class ClassCon
    Private Shared _connString As String=""
    Public Shared ReadOnly Property ConnString As String
        Get
            If _connString = "" Then
                If IsExpress() Then
                    _connString = "Data Source=some IP\SQLEXPRESS ....."
                Else
                    _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
0
 

Author Closing Comment

by:fizzlefry
Comment Utility
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now