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:
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:
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:
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.
Hello,
do you have an update of the clsses and controls. can you mail a sample project for VS2008.
Thanks, nice code and purpose.
Holger
Comment by Holger Rousselle — August 26, 2009 @ 7:30 am