SQL ConnectionInfo Class

A class for building SQL connection strings from specified properties. Class can also parse SQLConnection.ConnectionString() into properties using the parse method. Code is fully commented and handles all SQLConnection.ConnectionString properties.

Download source files - 154 Kb

Sample Image - demobuilder.gif

Sample Image2 - demoparser.gif

Introduction

I wrote this class to help developers with building and updating SQL Connection strings. The ConnectionInfo class can parse and build strings through properties and methods of the ConnectionInfo class. The ConnectionInfo class has two methods. The first method ConnectionInfo.Parse(string), parses SQL Connection strings into ConnectionInfo() properties.

i.e

//Parsing Code----------------------------------- ConnectionInfo Info = new ConnectionInfo(); Info.Parse("Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"); MessageBox.Show(Info.DataSource); //-----------------------------------------------

The second method ConnectionInfo.ToString(), builds a string out of all the available ConnectionInfo() properties.

i.e

//Building Code---------------------------------- ConnectionInfo Info = new ConnectionInfo(); Info.DataSource = "localhost"; Info.InitialCatalog = "Northwind"; MessageBox.Show(Info.ToString()); //-----------------------------------------------

Terms & Conditions

THIS CODE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS CODE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

References

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassTopic.asp

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

VectorX

Software Developer (Senior)
Codevendor

United States

Member

Please visit my personal website http://www.codevendor.com for my latest codes and updates.

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

any update ??

alhambra-eidos

0:04 25 Sep '08

misters, thanks... UDL is better option , but if I want include SQL Connection Parser, it's great !!! Thanks. any updates about this code ? AE
Sign In·View Thread·Permalink

Why not just create a UDL file?

Christopher Scholten

18:40 17 Jul '05

Windows already contains functionality to do a very similar thing to this. Follow these steps:
1) Create a text file, eg. "myConn.txt"
2) Rename the "txt" extension to "UDL" (eg. the empty text file is now called "myConn.udl"
3) Open the file (double-click)
4) The Data Link Properties dialog appears. Modify the parameters as necessary to create a connection that works. This can be tested by clicking "Test COnnection".
5) Click OK to save the file. If the file is subsequently opened in a text editor such as Notepad, a connection string is there ready to go. This file can be re-opened by the Data Link Properties editor.

Some fine points:
- To use the connection string generated within .NET by a specific providor (eg. SQL Server providor), remove the "Providor=..." key/value pair before using the connection string
- For security purposes, clear-text passwords are not saved in the file unless the "Allow Saving of Password" checkbox is checked

A small bug and some suggestions

desynch

14:53 9 Jul '05

Firstly thank you for bringing this class together, it has been very useful to me.

I have found out a minor bug. The class can parse the connection string in a wrong way because of the culture settings. For example, I saw that the class interprets the values of the properties "Integrated Security" and "Persist Security Info" as NONE with the following connection string on my machine:

Server=(local);Database=Bordro;Integrated Security=sspi;Persist Security Info=false;

I realized this occured because these two properties contain the letter "i" whose upper case is "İ" and not "I" in turkish culture which is the default culture on my machine. String.ToUpper function used in the parsing method of the class, is culture dependent and the property "Persist Security Info" is converted as "PERSİST SECURİTY INFO", thus its value can't be evaluated.

As connection string properties should be in english only, I thought it's a good idea to force using the culture "en-US" with ToUpper functions independent from the current culture of the application/system:

public void Parse(string src)

{

  System.Globalization.CultureInfo enCulture = new System.Globalization.CultureInfo("en-US", false);

 

  ...

 

  switch(BreakDownProperties[0].ToUpper(enCulture).Trim())

 

  ...

}

And some suggestions...

In my case, I wanted to use only one instance of the class on multiple connection strings so I think it would be good to have a public reset method to get rid of the old values of the properties when needed:

public void Reset() 

{

  //Reset properties

  Declare();

}

Also, Parse method should rather reset the properties before parsing new connection strings.

public void Parse(string src)

{

 //Reset properties

 Declare();

 

  ...

}

Thanks.

Re: A small bug and some suggestions

alhambra-eidos

1:07 25 Sep '08

Please, any sample code about it ?? Thanks. AE
Sign In·View Thread·Permalink

What is the standard connection ?

Trance Junkie

23:00 31 May '05

1) What is the standard connection string format used in sql. 2) Should i put this connection in a web.config file when dev an asp.net app? Thanks in advance !;)
Sign In·View Thread·Permalink

Re: What is the standard connection ?

VectorX

6:54 1 Jun '05

There is no standard connection string format other than [property=value;]. Properties can be found in any order in the connection string. The minimum string to connect is below.

Prefered:
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;";
conn.Open();

UnPrefered:
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "database=northwind;server=mySQLServer;UID=Username;PWD=Password;Persist Security Info=True;";
conn.Open();

The prefered method above is a little safer when it comes to any type of security of protecting your username and password from being viewed as text in your web.config file.

I made my class SQL ConnectionInfo Class for the purpose of connection pooling, so I could change the pooling properties easy and not have to worry about correct spelling or wording. To answer your question about where to put your connection string; The web.config file is ok for storing a global connection string, but if you want to take proper advantage of connection pooling the string should be in each method set to proper pooling conn amount.

My Prefered Method:

//Const outside your method that never change----------
public const string DatabaseName = @"northwind";
public const string ServerName = @"mySQLServer";
//-----------------------------------------------------

public void MyPreferedMethod(){

ConnectionInfo ConnInfo = new ConnectionInfo();
ConnInfo.Server = ServerName;
ConnInfo.Database = DatabaseName;
ConnInfo.PersistSecurityInfo = ConnectionInfo.PersistSecurityInfoArguments.False;
ConnInfo.IntegratedSecurity = ConnectionInfo.IntegratedSecurityArguments.SSPI;
ConnInfo.ConnectTimeout = 30;
ConnInfo.Pooling = ConnectionInfo.PoolingArguments.True;
ConnInfo.MinPoolSize = 3;
ConnInfo.MaxPoolSize = 10;

SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConnInfo.ToString();
conn.Open();

//This is where your code for your method goes.

}

As you can see above, I keep const unchanging variables outside my method, so I can use them in more than one method. The other often changing variables i use inside my method, managed by my ConnectionInfo class.

I hope that helps,

Latez,
VectorX