Hidden Gems

There are some things in life that are so wonderful they must be shared with the world.  This is one of them.  The problem is, I have only just found this particular item and I am wondering how I managed to get by without it for so long.

This little gem was released in the .NET Framework 2.0 (yes, about 2005) and it is SqlConnectionStringBuilder Class, yep, that is right.  Sounds simple, but the way I use it is simply to use an existing connection string, and create a new instance of the class and then use the components within.

Since I am working with SMO, I have come to need the components of the connection string, since I have the connection string, I was merely doing the following.

SqlConnection connection = new SqlConnection(connectionString);
Dim connection As SqlConnection = New SqlConnection(connectionString)

Yeah, just a string.  But the thing came when I started using the SMO.  I had this connection string but needed the server, database and user id and password if they were there.

SecureString securedPassword = new SecureString();
string server;
string database;
string username;
string password;

//Get the server and database information
server = txtDestinationConnectionString.Text.Split(';')[0].Split('=')[1];
database = txtDestinationConnectionString.Text.Split(';')[1].Split('=')[1];
username = txtDestinationConnectionString.Text.Split(';')[2].Split('=')[1];
if (!username.ToUpper().Contains("TRUE"))
	//A password has been supplied 
   password = txtDestinationConnectionString.Text.Split(';')[3].Split('=')[1];
   Char[] secPassword = password.ToCharArray();

   foreach(char character in secPassword)
	password = string.Empty;

So as you can see, I am splitting and pulling the elements apart.  This is fine in reality, since I am sure that behind the scenes the SqlConnectionStringBuilder Class is doing just that, possibly with a little more grace, looking for words etc…

But, using the class I can now do this.

string server;
string database;
string userId;
string password;

SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder(connectionString);
server = connection.DataSource;
database = connection.InitialCatalog;
userId = connection.UserId;
password = connection.Password;

Yes, this is it.  Sure in the previous example I used a SecureString, but you can easily change that into a character array and then assign this to a SecureString.  But you get my point.

What is the point in all of this?

Good question, it is about often using the resources you have at hand than the need to re-invent the wheel each time.  The time it took me to write the code for the extracting of the components from the string, I could have used better, more wisely.  But, it wasn’t a difficult task to perform, but there are others, which are out there which DO take a LONG time to implement and chances are someone else has already done the hard yards.

But I figured I would share this to those out there who do NOT know of this class’ existence. 


Posted on October 8, 2013, in Tips and Tricks and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: