.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:
- Sorting enumerable strings using LINQ.
- Generating CSV from an enumerable strings using LINQ and string.Join.
- Converting a DataTable to an HTML Table using an ASP.NET DataGrid and HtmlTextWriter to do the rendering.
- 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:
Persist Security Info: [WayBack] c# – Persist Security Info Property=true and Persist Security Info Property=false – Stack OverflowNetwork Library:OLE DB Services:
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
The Wiert Corner - irregular stream of stuff 
Leave a comment