Enumerating MS SQL Server Instances and Creating a Simple Connection Form
Have you ever wanted an SQL Server selection wizard that works similar to the ones found throughout windows? I had that desire for a little code-generation application that I wrote so I’ll go ahead and share how I accomplished it.
What I ended up doing was creating a very simple Windows Form in a Windows Control Library which basically does the following:
- The user can either manually type in a server/instance name, or drop-down the ComboBox to retrieve and display a list of available SQL Server instances.
- The user can choose to user either Windows Authentication or SQL Server Authentication.
- The user can either manually type in a database name, or drop-down the “Database” ComboBox to pull the list of available databases from the selected SQL Server.
- The user can test the connection to make sure settings are correct using the “Test Connection” button.
The final result looks like this:
Enumerating Available SQL Server Instances
The “MSSQL Server Name” ComboBox is populated using the following code. As you can see in the following code snippet, the actual enumerating of available SQL Server instances is quite painless. Note that if the instance is a named instance, we format the name as “SERVERINSTANCE”.
DataTable instances = SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (DataRow row in instances.Rows)
{
string name = row["ServerName"].ToString();
if (row["InstanceName"] != null && row["InstanceName"].ToString() != string.Empty)
name += string.Format(@"{0}", row["InstanceName"]);
this.sqlServerComboBox.Items.Add(name);
}
The Connection String
Depending on the authentication method, we need to format the connection strings differently. If the user selects Windows Authentication then we can simply set the Integrated Security property to SSPI, but if the user wants to use SQL Server Authentication then we need to set the User ID and Password properties. Have a look at connectionstrings.com for more connection string possibilities. Anyways, the following code does this:
string connectionString;
if (this.windowsAuthentication.Checked)
connectionString = string.Format("Server={0}; Integrated Security=SSPI;", sqlServerComboBox.Text);
else
connectionString = string.Format("Server={0}; User ID={1}; Password={2};", sqlServerComboBox.Text, usernameTextBox.Text, passwordTextBox.Text);
Enumerating Databases for the Selected Instance
Once the user has selected an SQL Server instance and selected/entered valid authentication information, they may drop-down the “Database” ComboBox to automatically retrieve a list of available databases to select from. The databases are retrieved from the instance using the following code:
using (SqlConnection sqlConnection = new SqlConnection(this.GetConnectionString(false)))
{
sqlConnection.Open();
DataTable databaseList = sqlConnection.GetSchema("Databases");
sqlConnection.Close();
this.databaseComboBox.Items.Clear();
foreach (DataRow row in databaseList.Rows)
this.databaseComboBox.Items.Add(row["database_name"]);
}
Testing the Connection & Credentials
When a user clicks the “Test Connection” button, the logic is also very simple. We just open a connection to the SQL Server instance, specifying the selected database, and if it doesn’t throw an SqlException the connection succeeded, otherwise it failed. See below:
try
{
using (SqlConnection sqlConnection = new SqlConnection(this.GetConnectionString(true)))
{
sqlConnection.Open();
sqlConnection.Close();
MessageBox.Show("Connection successful!");
}
}
catch (SqlException ex)
{
MessageBox.Show("Connection failed: " + ex.Message);
}
Wrap-Up and Source Code
That’s pretty much it! For more details, have a look at the example project file which you can download here: CcwTechnologies.Windows.Forms.SqlServer VS2010 Project.
Use the code at your own will, I cannot be held responsible for any damages resulting from the misuse of the code, etc., etc… Thanks for reading!
Related Posts
Wonderful post. It looks like the download link for the source code is broken – it seems like it should be http://blog.cylewitruk.com/wp-content/uploads/2010/09/CcwTechnologies.Windows.Forms_.SqlServer.zip
Wonderful blog. Cyle, you probably already know this, but all of the links to your images are broken because they are pointing to blog2 instead of just blog
Thanks! I apparently forgot to update older posts when I moved the site over