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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.