Tuesday, July 14, 2009

No-hassle SQL connection strings

Most applications working with a database handle the connection string in one of two ways: Hard-coding the full string or doing some amount of string concatenation. A typical concatenation method looks something like this:

public string OldMethod(string server, string database,
                        string username, string password)
    string connectionString = "Data Source=" + server + ";";
    connectionString += "Initial Catalog=" + database + ";";
    connectionString += "User ID=" + username + ";";
    connectionString += "Password=" + password;
    return connectionString;

I admit this is fairly simple code. The only potential issues might be a property name typo or a misplaced (or missing) semicolon. But why hassle with even that much when the dotNet Framework has the same functionality built into the SqlConnectionStringBuilder class? With a reference to System.Data.SqlClient, the above method can be replaced with:

public string NewMethod(string server, string database,
                        string username, string password)
    SqlConnectionStringBuilder connBuilder 
        = new SqlConnectionStringBuilder();
    connBuilder.UserID = username;
    connBuilder.Password = password;
    connBuilder.InitialCatalog = database;
    connBuilder.DataSource = server;
    return connBuilder.ToString();

In either case, the output is identical:

Data Source=myServer;Initial Catalog=myDatabase;User ID=myUser;Password=myPassword

Note: If you are working with a database other than MSSQL, there are several other classes derived from the common DbConnectionStringBuilder base class, such as OdbcConnectionStringBuilder and OracleConnectionStringBuilder.

Monday, July 6, 2009

Quickly escape strings in xml

If you spend much time working with xml, you will find yourself needing to escape strings. Replacing '<' with '&lt;' for example. Usually the code written to do so looks something like this:

escapedItem = itemToEscape.Replace("&", "&amp;")
                          .Replace("<", "&lt;")
                          .Replace(">", "&gt;")
                          .Replace("'", "&apos;")
                          .Replace("\"", "&quot;");

Though this technically works, there is an easier way built right in to the .Net framework. If we reference System.Security we can replace the above code with

escapedItem = SecurityElement.Escape(itemToEscape);

In both cases, the string

If (x < 2) & (y > 3), where \"x\" isn't...

is replaced with

If (x &lt; 2) &amp; (y &gt; 3), where &quot;x&quot; isn&apos;t...