The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,497 other followers

.NET/C#: SqlClient ConnectionString keys and their equivalences

Posted by jpluimers on 2012/11/07

A while ago I needed to shorten SqlClient ConnectionStrings. One way to do that is to use the shortest Key for each property (and not use the default key names that are much longer).

I beefed up the code to show you both the shortest and all equivalent keys (a few of the Microsoft exams want you to memorize most of these).

The HTML table below (similar to the huge and therefore hard to read table on MSDN) comes directly from the C# code at the bottom of the post. The only post-editing I did was making the header row bold.

Key ShortesEquivalentKey EquivalentKeys
Application Name app Application Name,app
ApplicationIntent ApplicationIntent ApplicationIntent
Asynchronous Processing async Asynchronous Processing,async
AttachDbFilename AttachDbFilename AttachDbFilename,extended properties,initial file name
Connect Timeout timeout Connect Timeout,connection timeout,timeout
Connection Reset Connection Reset Connection Reset
Context Connection Context Connection Context Connection
Current Language language Current Language,language
Data Source addr Data Source,addr,address,network address,server
Encrypt Encrypt Encrypt
Enlist Enlist Enlist
Failover Partner Failover Partner Failover Partner
Initial Catalog database Initial Catalog,database
Integrated Security trusted_connection Integrated Security,trusted_connection
Load Balance Timeout connection lifetime Load Balance Timeout,connection lifetime
Max Pool Size Max Pool Size Max Pool Size
Min Pool Size Min Pool Size Min Pool Size
MultipleActiveResultSets MultipleActiveResultSets MultipleActiveResultSets
MultiSubnetFailover MultiSubnetFailover MultiSubnetFailover
Network Library net Network Library,net,network
Packet Size Packet Size Packet Size
Password pwd Password,pwd
Persist Security Info persistsecurityinfo Persist Security Info,persistsecurityinfo
Pooling Pooling Pooling
Replication Replication Replication
Transaction Binding Transaction Binding Transaction Binding
TrustServerCertificate TrustServerCertificate TrustServerCertificate
Type System Version Type System Version Type System Version
User ID uid User ID,uid,user
User Instance User Instance User Instance
Workstation ID wsid Workstation ID,wsid

The code below uses a few techniques referenced as StackOverflow links:

  1. Sorting enumerable strings using LINQ.
  2. Generating CSV from an enumerable strings using LINQ and string.Join.
  3. Converting a DataTable to an HTML Table using an ASP.NET DataGrid and HtmlTextWriter to do the rendering.
  4. Getting a private static field by name using reflection.

Both the main program and the SqlConnectionStringBuilderHelper class are less than 70 lines of code (each about 50 when excluding comments and empty lines).

The SqlConnectionStringBuilderHelper uses the internals of the SqlConnectionStringBuilder class (all DbConnectionStringBuilder descendants I have seen work in a similar way):

  • each DbConnectionStringBuilder instance has a public Keys property that exposes the static _validKeywords field of the descendant. It contains all possible keys that can appear in a generated ConnectionString.
  • the SqlConnectionStringBuilder class (and other DbConnectionStringBuilder descendants) has a static private property _keywords that maps all possible keyword strings (including equivalents) to an enumerator (which indexes into the Keys property).
    Mono uses the same mechanism.
  • The trick is to walk the _keywords property and search for equivalent keywords.
  • For a list of equivalent keywords, you find the shortest one.

Related:

Enjoy the code:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SqlConnectionStringBuilderKeysConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            IEnumerable<string> keys = SqlConnectionStringBuilderHelper.Keys;
            IEnumerable<string> orderedKeys = keys.OrderBy(s => s); // http://stackoverflow.com/a/3630693/29290

            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Key");
            dataTable.Columns.Add("ShortesEquivalentKey");
            dataTable.Columns.Add("EquivalentKeys");

            foreach (string key in orderedKeys)
            {
                string shortestEquivalentKey = SqlConnectionStringBuilderHelper.ShortestEquivalentKey(key);
                List<string> equivalentKeys = SqlConnectionStringBuilderHelper.EquivalentKeys(key);

                string equivalentKeysCSV = AsCSV(equivalentKeys);

                dataTable.Rows.Add(key, shortestEquivalentKey, equivalentKeysCSV);
            }

            string htmlTable = AsHtmlTable(dataTable);
            Console.WriteLine(htmlTable);
            Console.ReadLine();
        }

        // http://stackoverflow.com/a/799454/29290
        static string AsCSV(IEnumerable<string> strings)
        {
            string[] stringsArray = strings.ToArray();

            string joined = string.Join(",", stringsArray);

            return joined;
        }

        // http://stackoverflow.com/a/1018811/29290
        static string AsHtmlTable(DataTable dataTable)
        {
            // HtmlTextWriter and DataGrid are in System.Web.dll which is not part of the .NET Framework 4 Client Profile; you need the full .NET Framework 4 profile for this
            DataGrid dataGrid = new DataGrid();
            dataGrid.DataSource = dataTable;
            dataGrid.DataBind();

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

            dataGrid.RenderControl(htmlTextWriter);

            string result = stringWriter.ToString();
            return result;
        }

    }
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;

namespace SqlConnectionStringBuilderKeysConsoleApplication
{
    public class SqlConnectionStringBuilderHelper
    {

        public static IEnumerable<string> Keys
        {
            get
            {
                SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
                ICollection keys = connectionStringBuilder.Keys;
                // Reflection shows that SqlConnectionStringBuilder actually returns System.Data.Common.ReadOnlyCollection<string> which is an internal type.
                // but it implements IEnumerable<string>
                return (IEnumerable<string>)keys;
            }
        }

        public static List<string> EquivalentKeys(string key)
        {
            object keywordsObject = GetStaticPrivateProperty<SqlConnectionStringBuilder>("_keywords");
            // keywordsObject is actually Dictionary<string, Keywords>, but KeyWords is a private enum inside the SqlClient namespace.
            IDictionary keywords = (IDictionary)keywordsObject;

            object value = keywords[key];

            List<string> result = new List<string>();

            foreach (DictionaryEntry keyword in keywords)
            {
                if (value.Equals(keyword.Value))
                    result.Add(keyword.Key.ToString());
            }

            return result;
        }

        public static string ShortestEquivalentKey(string key)
        {
            List<string> equivalentKeys = EquivalentKeys(key);

            string result = key;

            foreach (string equivalentKey in equivalentKeys)
            {
                if (equivalentKey.Length < result.Length)
                    result = equivalentKey;
            }

            return result;
        }

        // http://stackoverflow.com/a/628687/29290
        protected static object GetStaticPrivateProperty<T>(string name)
        {
            Type type = typeof(T);
            FieldInfo info = type.GetField(name, BindingFlags.NonPublic | BindingFlags.Static);
            object result = info.GetValue(null);
            return result;
        }

    }
}

–jeroen

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: