Tom’s Blog

January 6, 2009

Programmatically Discover SQL Server Instances

Filed under: C# — Tom Shelton @ 5:29 pm

I’ve been working on a little pet project of mine again lately.  I have started updating an fixing up my old simple class generator again.  It’s nothing special – just something I created for my own use one day, and have sort of been poking at here and there for the last couple of years.  But, looking through the code – I did see several areas that I thought might make a nice series of articles.  This first one will be to document the method that the entity generator uses to discover SQL Server instances on my network and local machines.

I have encapsulated this code into a reusable control library – that someday, I might actually finish!  But, in it’s current state the ServerInfoControl looks like this:

connectToServer004

Why a control?  Well, I wanted to be able to use it in a couple of different applications in different types of forms – so I decided to make it a control.  It’s unfinished, the remember password functionality doesn’t work – and for some reason the controls ability to remember previous selected connections (implemented as user settings) has decided to stop working.  Both issues, I fully intend to look into sometime :)

Here is what it looks like in action in the entity generator:

connectToServer002

Not to shabby, if I do say so myself!  Here is the code for the ServerInfoControl:

   1: using System;
   2: using System.Windows.Forms;
   3: using System.Data.SqlClient;
   4:
   5: namespace FireAnt.Controls.Database
   6: {
   7:     public partial class ServerInfoControl : UserControl
   8:     {
   9:         private const string BrowseInstanceServerName = "<Browse for More...>";
  10:
  11:         public event ConnectionValidDelegate ConnectionValid;
  12:
  13:         public ServerInfoControl ()
  14:         {
  15:             InitializeComponent ();
  16:         }
  17:
  18:         public string ConnectionString
  19:         {
  20:             get
  21:             {
  22:                 return BuildConnectionString ();
  23:             }
  24:         }
  25:
  26:         private string BuildConnectionString ()
  27:         {
  28:             string connectionString = string.Empty;
  29:             if ( this.uxServers.SelectedItem != null && ( (ServerInstance)this.uxServers.SelectedItem ).Instance != BrowseInstanceServerName )
  30:             {
  31:                 SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder ();
  32:                 connectionBuilder.DataSource = ( (ServerInstance)this.uxServers.SelectedItem ).FullName;
  33:                 ( (AuthenticationBase)this.uxAuthentication.SelectedItem ).AlterConnectionString ( connectionBuilder );
  34:                 connectionString = connectionBuilder.ToString ();
  35:             }
  36:             return connectionString;
  37:         }
  38:
  39:         private void ServerInfoControl_Load ( object sender, EventArgs e )
  40:         {
  41:             if ( !this.DesignMode )
  42:             {
  43:                 ServerInfoControlSettings.Default.Reload ();
  44:
  45:                 // add all the instances we know of to the combobox...
  46:                 foreach ( ServerInstance instance in ServerInfoControlSettings.Default.Servers )
  47:                 {
  48:                     this.uxServers.Items.Add ( instance );
  49:                 }
  50:
  51:                 // add the default instance at the end
  52:                 this.uxServers.Items.Add ( new ServerInstance ( BrowseInstanceServerName ) );
  53:
  54:                 // if there are others, set the first one in the list...
  55:                 if ( this.uxServers.Items.Count > 1 )
  56:                 {
  57:                     this.uxServers.SelectedIndex = 0;
  58:                 }
  59:
  60:                 // add the authmodes, and set the index
  61:                 this.uxAuthentication.Items.AddRange ( new AuthenticationBase[] { new WindowsAuthentication ( this ), new SqlServerAuthentication ( this ) } );
  62:                 this.uxAuthentication.SelectedIndex = 0;
  63:             }
  64:         }
  65:
  66:         private void uxAuthentication_SelectedIndexChanged ( object sender, EventArgs e )
  67:         {
  68:             this.uxUserName.Enabled =
  69:                 this.uxPassword.Enabled =
  70:                 this.uxRememberPassword.Enabled = this.uxAuthentication.SelectedItem is SqlServerAuthentication;
  71:             this.TryRaiseConnectionValidEvent ();
  72:         }
  73:
  74:         private void uxServers_SelectedIndexChanged ( object sender, EventArgs e )
  75:         {
  76:             if ( this.uxServers.SelectedItem != null )
  77:             {
  78:                 ServerInstance instance = (ServerInstance)this.uxServers.SelectedItem;
  79:                 if ( instance.Server == BrowseInstanceServerName )
  80:                 {
  81:                     using ( BrowseForServerDialog bfsd = new BrowseForServerDialog () )
  82:                     {
  83:                         if ( bfsd.ShowDialog ( this ) == DialogResult.OK )
  84:                         {
  85:
  86:                             this.AddToComboBox ( bfsd.ServerInstance );
  87:                             this.uxServers.SelectedItem = bfsd.ServerInstance;
  88:                             this.SaveServerInstance ( bfsd.ServerInstance );
  89:                             this.TryRaiseConnectionValidEvent ();
  90:                         }
  91:                         else
  92:                         {
  93:                             this.uxServers.SelectedItem = null;
  94:                             this.RaiseConnectionValidEvent ( false );
  95:                         }
  96:                     }
  97:                 }
  98:                 else
  99:                 {
 100:                     this.TryRaiseConnectionValidEvent ();
 101:                 }
 102:             }
 103:         }
 104:
 105:         private void TryRaiseConnectionValidEvent ()
 106:         {
 107:             bool valid = (this.uxServers.SelectedItem != null) ?
 108:                 (this.uxAuthentication.SelectedItem is WindowsAuthentication) ?
 109:                 true :
 110:                 (this.uxUserName.Text.Length > 0 && this.uxPassword.Text.Length > 0): false;
 111:             this.RaiseConnectionValidEvent ( valid );
 112:         }
 113:
 114:         private void RaiseConnectionValidEvent ( bool valid )
 115:         {
 116:             if ( this.ConnectionValid != null )
 117:                 this.ConnectionValid ( this, new ConnectionValidEventArgs ( valid ) );
 118:         }
 119:         private void AddToComboBox ( ServerInstance instance )
 120:         {
 121:             foreach ( ServerInstance server in this.uxServers.Items )
 122:             {
 123:                 if ( server.Equals ( instance ) )
 124:                     return;
 125:             }
 126:             this.uxServers.Items.Insert ( 0, instance );
 127:
 128:         }
 129:
 130:         private void SaveServerInstance ( ServerInstance instance )
 131:         {
 132:             foreach ( ServerInstance server in ServerInfoControlSettings.Default.Servers )
 133:             {
 134:                 if ( server.Equals ( instance ) )
 135:                     return;
 136:             }
 137:
 138:             ServerInfoControlSettings.Default.Servers.Insert ( 0, instance );
 139:             ServerInfoControlSettings.Default.Save ();
 140:         }
 141:
 142:         private abstract class AuthenticationBase
 143:         {
 144:             public AuthenticationBase ( ServerInfoControl parent )
 145:             {
 146:                 this.Parent = parent;
 147:             }
 148:
 149:             protected ServerInfoControl Parent { get; set; }
 150:
 151:             public abstract void AlterConnectionString ( SqlConnectionStringBuilder connectionBuilder );
 152:         }
 153:
 154:         private class WindowsAuthentication : AuthenticationBase
 155:         {
 156:             public WindowsAuthentication ( ServerInfoControl parent ) : base ( parent ) { }
 157:
 158:             public override void AlterConnectionString ( SqlConnectionStringBuilder connectionBuilder )
 159:             {
 160:                 connectionBuilder.IntegratedSecurity = true;
 161:             }
 162:
 163:             public override string ToString ()
 164:             {
 165:                 return "Windows Authentication";
 166:             }
 167:         }
 168:
 169:         private class SqlServerAuthentication : AuthenticationBase
 170:         {
 171:             public SqlServerAuthentication ( ServerInfoControl parent ) : base ( parent ) { }
 172:
 173:             public override void AlterConnectionString ( SqlConnectionStringBuilder connectionBuilder )
 174:             {
 175:                 connectionBuilder.UserID = this.Parent.uxUserName.Text;
 176:                 connectionBuilder.Password = this.Parent.uxPassword.Text;
 177:             }
 178:
 179:             public override string ToString ()
 180:             {
 181:                 return "SQL Server Authentication";
 182:             }
 183:         }
 184:
 185:         private void CommonTextChanged ( object sender, EventArgs e )
 186:         {
 187:             this.TryRaiseConnectionValidEvent ();
 188:         }
 189:     }
 190: }

Not sure there is anything here that is interesting – except for maybe the TryRaiseConnectionValidEvent, which is in serious need of refactoring (multiple nested ternary operators!  What was I thinking!).

So, lets move to the more interesting part of the code.  That is found in the BrowseServerDialog form.  This form is internal to the control library, and is shown when you select the “<Browse For More…>” option in the drop down of the control.   Here it is in action:

connectToServer003

And here is the code for this dialog box:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.ComponentModel;
   4: using System.Data;
   5: using System.Drawing;
   6: using System.Text;
   7: using System.Windows.Forms;
   8: using Microsoft.Win32;
   9:
  10: namespace FireAnt.Controls.Database
  11: {
  12:     internal partial class BrowseForServerDialog : Form
  13:     {
  14:         private ServerInstance selectedInstance = null;
  15:
  16:         public BrowseForServerDialog ()
  17:         {
  18:             InitializeComponent ();
  19:         }
  20:
  21:         private void BrowseForServerDialog_Load ( object sender, EventArgs e )
  22:         {
  23:             if ( !this.DesignMode )
  24:             {
  25:                 this.FillTreeView ( this.uxLocalServers, ServerInstance.GetLocalServerList () );
  26:                 this.uxLoadNetworkServers.RunWorkerAsync ();
  27:             }
  28:         }
  29:
  30:         public ServerInstance ServerInstance
  31:         {
  32:             get { return this.selectedInstance; }
  33:         }
  34:
  35:
  36:         private void TreeView_AfterSelect ( object sender, TreeViewEventArgs e )
  37:         {
  38:             this.selectedInstance = (ServerInstance)e.Node.Tag;
  39:             this.uxOK.Enabled = ( this.selectedInstance != null );
  40:         }
  41:
  42:         private void uxLoadNetworkServers_DoWork ( object sender, DoWorkEventArgs e )
  43:         {
  44:             e.Result = ServerInstance.GetNetworkServerList ();
  45:             Console.WriteLine ( "Exiting" );
  46:         }
  47:
  48:         private void uxLoadNetworkServers_RunWorkerCompleted ( object sender, RunWorkerCompletedEventArgs e )
  49:         {
  50:             Console.WriteLine ( "Fired!" );
  51:             if ( e.Error != null )
  52:             {
  53:                 MessageBox.Show ( e.Error.Message );
  54:             }
  55:             else
  56:             {
  57:                 this.uxNetworkServers.ImageList = this.uxImages;
  58:                 this.uxNetworkServers.Nodes[0].ImageIndex = 0;
  59:                 this.uxNetworkServers.Nodes[0].SelectedImageIndex = 0;
  60:                 this.uxNetworkServers.Nodes[0].Text = "Database Engine";
  61:                 this.FillTreeView ( this.uxNetworkServers, (List<ServerInstance>)e.Result );
  62:                 this.uxNetworkServers.Enabled = true;
  63:             }
  64:         }
  65:
  66:         private void FillTreeView ( TreeView treeView, List<ServerInstance> servers )
  67:         {
  68:             foreach ( ServerInstance server in servers )
  69:             {
  70:                 TreeNode newNode = new TreeNode ( server, 1, 1 );
  71:                 newNode.Tag = server;
  72:                 treeView.Nodes[0].Nodes.Add ( newNode );
  73:             }
  74:         }
  75:
  76:         private void BrowseForServerDialog_FormClosing ( object sender, FormClosingEventArgs e )
  77:         {
  78:             if ( this.uxLoadNetworkServers.IsBusy )
  79:                 this.uxLoadNetworkServers.CancelAsync ();
  80:         }
  81:     }
  82: }

Again, nothing special.  The real magic happens in the ServerInstance class:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Data;
   6: using System.Data.Sql;
   7: using System.Net;
   8: using Microsoft.Win32;
   9:
  10: namespace FireAnt.Controls.Database
  11: {
  12:     [Serializable ()]
  13:     internal class ServerInstance : IComparable, IComparable<ServerInstance>, IComparable<string>
  14:     {
  15:         private static readonly string LocalServer = Dns.GetHostName ().ToUpper ();
  16:         private const string SqlServerRegistryKey = "SOFTWARE\\MICROSOFT\\MICROSOFT SQL SERVER";
  17:         private const string ServerInstanceRegistryKey = "SOFTWARE\\MICROSOFT\\MICROSOFT SQL SERVER\\INSTANCE NAMES\\SQL";
  18:
  19:         #region Constructors
  20:         public ServerInstance () : this ( string.Empty ) { }
  21:         public ServerInstance ( string server ) : this ( server, string.Empty ) { }
  22:         public ServerInstance ( string server, string instance ) : this ( server, instance, "0.0" ) { }
  23:         public ServerInstance ( string server, string instance, string version ) : this ( server, instance, new Version ( version ) ) { }
  24:         public ServerInstance ( string server, string instance, Version version )
  25:         {
  26:             this.Server = server;
  27:             this.Instance = instance;
  28:             this.Version = version;
  29:         }
  30:         #endregion
  31:
  32:         #region Properties
  33:         public string Server { get; private set; }
  34:         public string Instance { get; private set; }
  35:         public Version Version { get; private set; }
  36:
  37:         public string FullName
  38:         {
  39:             get
  40:             {
  41:                 return this.Server +
  42:                     ( string.IsNullOrEmpty ( this.Instance ) ? string.Empty : "\\" + this.Instance );
  43:             }
  44:         }
  45:         #endregion
  46:
  47:         #region Overrides
  48:         public override string ToString ()
  49:         {
  50:             StringBuilder displayString = new StringBuilder ( this.FullName );
  51:
  52:             // add the version if it is a valid one
  53:             if ( this.Version.Major != 0 )
  54:             {
  55:                 displayString.AppendFormat ( "    ({0}.{1})", this.Version.Major, this.Version.Minor );
  56:             }
  57:
  58:             return displayString.ToString ();
  59:         }
  60:
  61:         public override bool Equals ( object obj )
  62:         {
  63:             if ( obj == null || !( obj is ServerInstance ) )
  64:                 return false;
  65:             return ( this.ToString () == obj.ToString () );
  66:         }
  67:
  68:         public override int GetHashCode ()
  69:         {
  70:             return this.ToString ().GetHashCode ();
  71:         }
  72:         #endregion
  73:
  74:         #region Operators
  75:         public static implicit operator string ( ServerInstance instance )
  76:         {
  77:             return instance.ToString ();
  78:         }
  79:         #endregion
  80:
  81:         public static List<ServerInstance> GetLocalServerList ()
  82:         {
  83:             List<ServerInstance> localServers = new List<ServerInstance> ();
  84:
  85:             RegistryKey instancesKey = Registry.LocalMachine.OpenSubKey ( ServerInstanceRegistryKey );
  86:             if ( instancesKey != null )
  87:             {
  88:                 using ( instancesKey )
  89:                 {
  90:                     foreach ( string instanceName in instancesKey.GetValueNames () )
  91:                     {
  92:                         string instanceSetupKeyName = string.Format ( "{0}\\{1}\\Setup", SqlServerRegistryKey, (string)instancesKey.GetValue ( instanceName ) );
  93:                         RegistryKey instanceSetupKey = Registry.LocalMachine.OpenSubKey ( instanceSetupKeyName );
  94:                         if ( instanceSetupKey != null )
  95:                         {
  96:                             using ( instanceSetupKey )
  97:                             {
  98:                                 string version = (string)instanceSetupKey.GetValue ( "Version" );
  99:                                 string edition = (string)instanceSetupKey.GetValue ( "Edition" );
 100:
 101:                                 localServers.Add ( new ServerInstance ( LocalServer, ( edition == "Express Edition" ) ? instanceName : string.Empty, version ) );
 102:                             }
 103:                         }
 104:                     }
 105:                 }
 106:             }
 107:
 108:             return localServers;
 109:         }
 110:
 111:         public static List<ServerInstance> GetNetworkServerList ()
 112:         {
 113:             List<ServerInstance> networkServers = new List<ServerInstance> ();
 114:
 115:             using ( DataTable dataSources = SqlDataSourceEnumerator.Instance.GetDataSources () )
 116:             {
 117:                 foreach ( DataRow dataSource in dataSources.Rows )
 118:                 {
 119:                     networkServers.Add ( new ServerInstance (
 120:                         (string)dataSource["ServerName"],
 121:                         Convert.IsDBNull ( dataSource["InstanceName"] ) ? string.Empty : (string)dataSource["InstanceName"],
 122:                         Convert.IsDBNull ( dataSource["Version"] ) ? "0.0" : (string)dataSource["Version"] ) );
 123:                 }
 124:             }
 125:
 126:             return networkServers;
 127:         }
 128:
 129:
 130:         #region IComparable Members
 131:
 132:         public int CompareTo ( object obj )
 133:         {
 134:             if ( obj == null || !( obj is ServerInstance ) )
 135:                 return -1;
 136:             else
 137:                 return this.ToString ().CompareTo ( obj.ToString () );
 138:
 139:         }
 140:
 141:         #endregion
 142:
 143:         #region IComparable<ServerInstance> Members
 144:
 145:         public int CompareTo ( ServerInstance other )
 146:         {
 147:             return this.ToString ().CompareTo ( other.ToString () );
 148:         }
 149:
 150:         #endregion
 151:
 152:         #region IComparable<string> Members
 153:
 154:         public int CompareTo ( string other )
 155:         {
 156:             return this.ToString ().CompareTo ( other );
 157:         }
 158:
 159:         #endregion
 160:     }
 161: }

The two methods of interest are the two static methods, ServerInstance.GetLocalServerList and ServerInstance.GetNetworkServerList.  The first method – GetLocalServerList – looks in the Windows registry to locate SQL Server instances installed on the local machine, and attempts to differentiate between Express and other editions of Sql Server.  The second method – GetNetworkServerList – uses the SqlDataSourceEnumerator found in System.Data.Sql to enumerate the network servers.  At one time, I had written the code to use the Sql Server Management Objects (SMO) – but, I found that it was way slower.  I also tried the trick of using ODBC – but that didn’t really give me the information I wanted.  So, I settled on just using the SqlDataSourceEnumerator.  Still slower then I would like, but not nearly as slow as using SMO.

Powered by WordPress