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.