Helpful SQL Tools and Tips for Test Engineers – Part 1

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

By Cayce Chismar,

Hello everyone, this time we start a new series where we will post some more of the great content from our college in Microsoft Test Team (Principal Customer Engineer) boB 'the ToolMan' Taylor, who is an industry expert in all topics SQL. The theme of this series will be "Helpful SQL Tools and Tips to make your life as Test Engineer" (or Developer, QA, etc.), really anyone who spends time working with SQL.

As some of you may know, we have featured content from boB before, such as his "Learning Justo Query Language" video tutorials. If you are interested in that, please don't miss our post.

Today we will review a tool he created to make the life easier for anyone who spends a lot of time working on SQL Server Management Studio (SSMS). This tool will allow you a quicker and more efficient way to connect to several SQL Servers through SSMS.

When working on SSMS, connecting many different SQL Server instances can get repetitive, having to enter information, Server Name, credentials, etc. each time. If you know a little C# and SQL Management Objects then boB has constructed a Connect To SQL class to help 'connect to any SQL Server instance along with any SQL Azure Database server instance’. The Connect to SQL base class is imported and your class will be derived from this base class. This is meant to be a simpler way to connect to a database, using an UI that looks familiar rather than going through C# config files.

Let’s do a quick walk through of this tool:

Connect to SQL Base

In boB's method, the user will have access to open a connection and any Connection Information needed to perform tasks. boB created a 'Connect to SQL Base' class to be added into your project that lessens inputting data repeatedly, such as Server Name and credentials. This is also useful if a person does not know the name of which database they are needing to connect to; this class will go out and search through all available databases and allow you to pick which database you would like to use. Within boB's ConnectToSQLBase class, there are two standard resources: a default icon for use by the dialog and a default banner for use by the dialog.

Let's dive into the code that connects our database and determines the correct Authentication method!

Cursor current = Cursor.Current; Cursor.Current = Cursors.WaitCursor; // Create our connection information for use by the Results Form ServerConnection sc = newServerConnection(ServerName); // Create a ConnectToSQLBase object to simplify management SqlConnectionInfo sci = newSqlConnectionInfo(sc, ConnectionType.Sql);

First, capture the current cursor and set wait cursor. Then create a ServerConnection object based on the ServerName in the cbServerName ComboBox.

sci.Authentication = SqlConnectionInfo.AuthenticationMethod.NotSpecified; switch(AuthMethod) { case0: // Windows Auth sci.UseIntegratedSecurity = true; break; case1: // SQL Server Login sci.UserName = txtUserName.Text; sci.Password = txtPassword.Text; break; case2: // Active Directory Password Authentication sci.Authentication = SqlConnectionInfo.AuthenticationMethod.ActiveDirectoryPassword; sci.UserName = txtUserName.Text; sci.Password = txtPassword.Text; sci.EncryptConnection = true; break; case3: // Active Directory Integrated Authentication sci.Authentication = SqlConnectionInfo.AuthenticationMethod.ActiveDirectoryIntegrated; sci.UseIntegratedSecurity = true; sci.UserName = Id.Name; sci.EncryptConnection = true; break; }

Based on the AuthMethod selected in the cbAuthentication ComboBox, modify the ServerConnectionInfo object with the appropriate information.

// Use TCP connection sci.ConnectionProtocol = NetworkProtocol.TcpIp; // Set user requested timeout sci.ConnectionTimeout = ConnectionTimeout; // Finally, we can create our SqlConnection SqlConnection con = newSqlConnection(sci.ConnectionString); try { // Now, make sure we can open a connection (to ensure user has rights) // if they don't have rights, it will throw an exception DoWork(con, sci); } catch(Exception ex) { MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Cursor.Current = current; con.Close(); }

Line 11 calls the DoWork() method, which needs to be implemented in your derived class.

booluseLocalServers = false; DataTable dt = SmoApplication.EnumAvailableSqlServers(useLocalServers); cbServer.Items.Clear(); cbServer.Items.Add("(local)"); // Work item 311 // Only add servers that are at the minimum version or greater foreach(DataRow r indt.Rows) { string[] verParts = r["Version"].ToString().Split('.'); // Only add servers that are at the minimum version or greater if(Convert.ToInt32(verParts[0]) >= MinimumVersion) { cbServer.Items.Add(r[0].ToString()); } } cbServer.Items.Add("<Browse for more...>");

"Note: you must set the minimum SQL Server version number that you want prior to the following call which fills the cbServer drop-down list. You can set this value in your constructor. It currently defaults to 12."

That is all, we hope this information is useful, please follow links below for the source code and original posts and let us know of any questions in the comments section below. Thanks!

The original Archive code is on boB Taylor's GitHub

Other interesting resources in boB's blog::

Thank you all for reading, we hope this information is helpful. Please leave any questions or comments in the section below!

Leave a Reply Cancel reply