SQL Azure: Building a Shard (part 2) - Managing Database Connections

2. Managing Database Connections

This section walks through a few coding decisions that are necessary when creating this shard. Because the shard library needs to be able to connect to multiple databases, the client has two options to provide this list: it can provide the list of connections to use whenever it makes a call to the library, or it can preload the library with a list of connection objects that are then kept in memory for all future calls.

The shard library declares the following property to hold the list of preloaded connection objects. The ShardConnections property is declared as static so it can be used across multiple calls easily; the client application only needs to set this property once:

public static List<SqlConnection> ShardConnections {get;set;}

In addition, an extension method is added to SqlConnection to provide a GUID value that uniquely identifies a connection string. The connection GUID is critical for the shard; it provides a breadcrumb for every record returned by the shard. This breadcrumb is later used by the shard to determine, for example, which database to use when performing an update statement.

The following code shows how a connection GUID is calculated. It uses the SqlConnectionStringBuilder helper class and another extension method on strings called GetHash() (on line 8). This extension method returns a SHA-256 hash value. Note that if the connection string doesn't specify a default database (Initial Catalog), you assume the user is connected to the master database. This assumption is correct for SQL Azure, but it may not hold true for SQL Server:

1) public static string ConnectionGuid(this SqlConnection connection)
2) {
3)    SqlConnectionStringBuilder cb = new
   SqlConnectionStringBuilder(connection.ConnectionString);
4)    string connUID =
5)    ((cb.UserID != null) ? cb.UserID : "SSPI") + "#" +
6)    cb.DataSource + "#" +
7)     ((cb.InitialCatalog != null) ? cb.InitialCatalog : "master");
8)    string connHash = connUID.GetHash().ToString();
9)    return connHash;
10) }

For reference, here is the extension method that returns a hash value for a string. Technically, you could use the string's native GetHashCode() method. However, the built-in GetHashCode method varies based on the operating system used (32-bit versus 64-bit) and the version of .NET. In this case, you create a simple GetHash() method that consistently return sthe same value for a given input. The string value is first turned into an array of bytes using UTF-8 (on line 3). The hash value is then computed on line 4. Line 5 returns the hash as a string value:

1) public static string GetHash(this string val)
2) {
3)    byte[] buf = System.Text.UTF8Encoding.UTF8.GetBytes(val);
4)    byte[] res =
   System.Security.Cryptography.SHA256.Create().ComputeHash(buf);
5)    return BitConverter.ToString(res).Replace("-", "");
6) }

By default, the application code loads the initial set of connections using the application configuration file. In the current design, it's the application's responsibility to load the connections. This sample application reads the configuration file on startup and adds every entry in the ConfigurationStrings of the configuration file that contains the word SHARD:

1) foreach (System.Configuration.ConnectionStringSettings connStr in System.Configuration.ConfigurationManager.ConnectionStrings)
2) if (connStr.Name.ToUpper().StartsWith("SHARD"))
3)    Shard.ShardConnections.Add(
   new SqlConnection(connStr.ConnectionString));

					  

The application can also add connections based on user input. The following code shows how the application adds a new connection to the shard:

Shard.ShardConnections.Add(new SqlConnection("connection string here"));

When running the test application, you can add a connection to the shard by clicking Add Connection on the Shard Connections tab. The GUID value for this connection is calculated automatically and displayed. Figure 2 shows the screen that allows you to add a connection manually, and Figure 10-3 displays all the connections defined in the shard.

Figure 2. Adding a custom connection to the shard

Figure 3. Viewing shard connections