I've been asked a number of times about connection strings. I have a way of connecting to databases that supports code reuse and allows me to save some keystrokes so I thought I'd share this bit of information with you.
Imagine the benefits if you contained your connection strings not in web.config but in a compiled DLL instead. You could build a library of connection strings, pass them around to others and use them in all of your projects without ever giving out your password. When the time comes to change your password, you change it in the DLL and recompile.
Here is how it works. With the .NET 2.0 runtime environment and a reference to your favorite data provider (SQL Server in this example) you can create an instance of the SqlConnectionStringBuilder. In Oracle, this would be the DbConnectionStringBuilder and in mySql, mySqlConnectionStringBuilder.
I define a public string property and initiate the connection string builder (Csb)
I then add each property that needs to be set and return it to the calling function.
public static string GetConnString
{
get
{
SqlConnectionStringBuilder Csb = new SqlConnectionStringBuilder();
Csb.Add("Server", @"server\instance");
Csb.Add("uid", @"username");
Csb.Add("pwd", @"password");
Csb.Add("Initial Catalog", @"database");
return Csb.ToString();
}
}
The first thing you may notice is that I'm not just building a string and returning it. We are building a new Sql connection string and returning the value.
The second thing new developers might notice is the @ in front of my values. The purpose of that is to avoid messing with escape characters. If you look at my server string, it has a \ in it. That seperates the host name from the server instance name in a multi-database server host. Without the @ you would need to escape out the \. Easy enough to do but if you aren't sure what characters need to be escaped out, the @ works really well.
In your web pages code behind or your data class, however you choose to do it, you can create a connection to your database and reference the class property in your DLL like so.
public static StringCollection GetWorkspaces()
{
string sSql = "Select project_id,project_name from projects";
StringCollection scProjects = new StringCollection();
SqlCommand oCmd;
SqlConnection oCn;
SqlDataReader
oDr;
//Here is the GetConnString property from our example above!
oCn = new SqlConnection(clsConnections.GetConnString);
oCmd = new SqlCommand(sSql, oCn);
oCn.Open();
oDr = oCmd.ExecuteReader();
try
{
while (oDr.Read())
{
scProjects.Add(oDr["project_id"].ToString());
scProjects.Add(oDr["project_name"].ToString());
}
}
catch (Exception err)
{
scProjects.Add(err.Message);
}
finally
{
oCn.Close();
}
return scProjects;
}