Connect To SQL… a utility for C# programmers– Part 2


Today we will examine the code that connects to our database and showcases the code used to determine the correct Authentication method

   1: Cursor current = Cursor.Current;
   2: Cursor.Current = Cursors.WaitCursor;
   3: // Create our connection information for use by the Results Form
   4: ServerConnection sc = new ServerConnection(ServerName);
   5: // Create a ConnectToSQLBase object to simplify management
   6: SqlConnectionInfo sci = new SqlConnectionInfo(sc, ConnectionType.Sql);

First we do some housekeeping (i.e. capturing the current cursor, and setting the wait cursor).

Then we create a ServerConnection object based on the name in the ServerName in the cbServerName combobox

   1: sci.Authentication = SqlConnectionInfo.AuthenticationMethod.NotSpecified;
   2: switch (AuthMethod)
   3: {
   4:     case 0: // Windows Auth
   5:         sci.UseIntegratedSecurity = true;
   6:         break;
   7:     case 1: // SQL Server Login
   8:         sci.UserName = txtUserName.Text;
   9:         sci.Password = txtPassword.Text;
  10:         break;
  11:     case 2: // Active Directory Password Authentication
  12:         sci.Authentication = SqlConnectionInfo.AuthenticationMethod.ActiveDirectoryPassword;
  13:         sci.UserName = txtUserName.Text;
  14:         sci.Password = txtPassword.Text;
  15:         sci.EncryptConnection = true;
  16:         break;
  17:     case 3: // Active Directory Integrated Authentication
  18:         sci.Authentication = SqlConnectionInfo.AuthenticationMethod.ActiveDirectoryIntegrated;
  19:         sci.UseIntegratedSecurity = true;
  20:         sci.UserName = Id.Name;
  21:         sci.EncryptConnection = true;
  22:         break;
  23: }

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

   1: // Use TCP connection
   2: sci.ConnectionProtocol = NetworkProtocol.TcpIp;
   3: // Set user requested timeout
   4: sci.ConnectionTimeout = ConnectionTimeout;
   5: // Finally, we can create our SqlConnection         
   6: SqlConnection con = new SqlConnection(sci.ConnectionString);
   7: try
   8: {
   9:     // Now, make sure we can open a connection (to ensure user has rights)
  10:     // if they don't have rights, it will throw an exception
  11:     DoWork(con, sci);
  12: }
  13: catch (Exception ex)
  14: {
  15:     MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
  16: }
  17: finally
  18: {
  19:     Cursor.Current = current;
  20:     con.Close();
  21: }

In the above code snippet on Line 9 is where I call the virtual method DoWork() which you need to implement in your derived class. 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.

   1: bool useLocalServers = false;
   2: DataTable dt = SmoApplication.EnumAvailableSqlServers(useLocalServers);
   3: cbServer.Items.Clear();
   4: cbServer.Items.Add("(local)");
   5: // Work item 311
   6: // Only add servers that are at the minimum version or greater
   7: foreach (DataRow r in dt.Rows)
   8: {
   9:     string[] verParts = r["Version"].ToString().Split('.');
  10:     // Only add servers that are at the minimum version or greater
  11:     if (Convert.ToInt32(verParts[0]) >= MinimumVersion)
  12:     {
  13:         cbServer.Items.Add(r[0].ToString());
  14:     }
  15: }
  16: cbServer.Items.Add("<Browse for more...>");

That about covers it… I will be creating a CodePlex project for this project which will include the dll if that is all you want as well as the complete source. I’ll post the URL once I upload it to CodePlex.


Comments (0)

Skip to main content