.NET Building Blocks: Build a Configurable Database Credential Selector

.NET Building Blocks: Build a Configurable Database Credential Selector

isual Studio provides convenient design-time wizards to define static database connection strings that you can store in your settings files. Storing connection strings in configuration files means you can update the connection string without having to recompile or redeploy the host program, although it does require restarting the application. This article presents a dynamically configurable user control that lets you provide users with the capability to change the connection parameters themselves. You have complete control?letting them change all or only some portions of the connection string.

Developers building database applications often encounter many of the same common scenarios.

  • Arbitrary Server Name. You’re tasked with delivering an application that must install on the customers’ servers, but you can’t know the server name in advance. Your product creates a database on that server with a specific, constant name. Further, your application relies on a predefined “application user” account with specific permissions. In this case, you never want to expose the username, the password, or the database name to the user. In this scenario, a customer at company X would have a database of the same name as a customer at company Y, each on their own server (whose names are arbitrary and unknown to you). To make this work, you must either write a custom installation program, which prompts for the server name during the install, or add logic that requests the server name the first time the application runs. The username, password, and database name are all invariants in your program and, in fact, you do not want to reveal these to casual users.
  • Individual Login Support. Your application supports individual logins for every user. So at some point in your program you need to ask users for their names and passwords. Depending on the application and the requirements, you may choose to ask every time the program runs, or just the first time the application is run, or you might opt to allow users to choose whether the program should remember their credentials.
  • Configurable Development Environments. During development and testing, you want to be able to switch between your development database and a test database. (Or similarly between a development server and a test server.) Or perhaps you have multiple databases, each configured differently to test different aspects of your application.
  • Support for Privileged Users. Your application is sophisticated enough that it differentiates between ordinary users (ordinary in name only; these are the folks who actually accomplish work with it!) and privileged or administrative users. The latter group needs to have the ability to select a different database or server in addition to entering their usernames and passwords, whereas ordinary users need only to input their usernames and passwords.

Orthogonal to the scenarios above, there are some additional considerations, such as always omitting the password from a stored connection string for security reasons; testing the database connection after users provide parameters; allowing Windows authentication vs. SQL Server authentication; connecting to different database systems (such as SQL Server or Oracle), and so forth.

Creating a custom login form for each proprietary application is tedious, boring, and error prone; instead, you can build a generic control suitable for any application, and configure it with just a few lines of code to customize it according to an application’s needs. The control should be flexible enough to handle all the considerations listed in the various scenarios, and it must be easy for developers to use.

The ConnectionStringManager control described in this article fulfills those conditions. When you drop it on your Windows Form, you need only add OK and Cancel buttons and hook up their event handlers to have a complete database login panel. The output of the control is simply an updated connection string that you may then use to establish your database connections, store in the user’s settings, and so on.

First Look: The Configurator
The ConnectionStringManagerDemo sample project accompanying this article gives you a sandbox in which to explore the flexibility of the ConnectionStringManager control. Figure 1 shows the main form of the demo project.

The application includes a secondary form?the Credential Input Form (see Figure 2)?where the ConnectionStringManager control actually resides. This simple architecture mirrors a typical application; the main form does the real work, displaying the secondary login or credential form when appropriate. In this demo application, the Change button on the form in Figure 1 opens the Credential Input Form shown in Figure 2. The Current Credentials TextBox holds the connection string passed to the credential form. As you can see, the sample application is, in essence, a configurator; it lets a user interactively customize or configure the credential form.

Figure 1. ConnectionStringManager Demo: The main form provides controls so you can tailor the credential form at run time.
Figure 2. The Credential Input Form: This sub-form shows the ConnectionStringManager control with a variety of TextBox, ComboBox, RadioButton, CheckBox, Label, and Button controls.

Proceeding downward on the form in Figure 1, you’ll see the Pending Credentials area, which displays the return values from the credential form?an output pane if you will. The Copy Back to Input button provides a convenient way to copy the output back to the input for continued experimentation. The Run Test Query button uses the connection string in the Current Credentials to invoke a simple query, just to let you test whether the supplied credentials are correct.

To use the Run Test Query button?and to be able to fully exercise the ConnectionStringManager?you will need to have at least one database available (preferably more than one). The starting connection string, shown in Figure 1, is typical for a local instance of SQL Server 2005 Express Edition.

Down the left side of the main form are input controls that let you configure the visual and behavioral characteristics of the ConnectionStringManager on the credential form. The Credential Mode section presents four common configurations of the input controls. Each radio button in this section represents a group of settings in the ConnectionStringManager.

The two choices in the Rendering section work in conjunction with the Credential Mode choice. Choosing a Credential Mode directs the ConnectionStringManager to allow users to interact with some fields and not with others. The ones in the latter category may either be hidden from view or disabled; the ConnectionStringManager employs three levels of access (see Table 1) that let you decide.

Table 1. Three Access Levels: You can display input controls as either enabled or disabled, or hide the controls.
Level Description Result
Edit Display the component and enable it Visible and Enabled
Display Display the component and disable it Visible and Disabled
Hide Do not display the component Not Visible

The rendering adjustment on the main form, therefore, selects between a disabled control (Display) and a hidden control (Hide). You’ll find this feature particularly handy with nested access, discussed later.

The Accessories section of the form in Figure 1 contains several check boxes, which function as follows:

  • Expose Pwd Choice displays or hides a field on the form that lets users decide whether an application should remember their passwords. It is up to you to act on each user’s choice; this option merely provides the user interface component.
  • Expose Status Text displays or hides a field that produces diagnostic output when the user tests the connection on the credential form.
  • Expose Test Button displays or hides a combination test button/status indicator. This button lets users check their entered credentials before leaving the credential form (making it quicker to correct a typographic error, for example). The button changes color depending on the state of the connection. When a connection error occurs, the button turns red and an ErrorProvider component becomes visible. Users may hover over the ErrorProvider with the mouse to see the error details. Those error details also appear in the result text box exposed by the previous check box.

The final configuration control on the left side of the main form is the Server Choice list. This field accepts a list of one or more servers that the ConnectionStringManager control will present to users in a ComboBox on the credential form, letting you limit users to a preloaded list of servers from which they can choose. If you do not provide a list of servers, then the control instead opts for a simple TextBox that allows users to enter an arbitrary server name.

The Credential Input Form
Now, take a look at the credential form. Open it in its default state by launching the demo application and selecting the Change button on the main form (see Figure 2).

Figure 3. The ConnectionStringManager Control: This exploded view describes each embedded sub-control. Exposed properties let you set the access level of each of these sub-controls individually.

Contrary to what you might expect, there are only three controls on the credential form?a ConnectionStringManager, an OK button, and a Cancel button. The paucity of controls means you need very little custom code to implement a similar form in your own projects; it’s quick and easy to instrument, because the ConnectionStringManager control handles displaying, arranging, and populating all the sub-controls that users interact with. Before discussing how to create and connect such a form though, consider the exploded view of the ConnectionStringManager control shown in Figure 3, which individually delineates each sub-control within the ConnectionStringManager. You have the ability to adjust the access level of each sub-control.

By default, all sub-controls have the access level set to Edit; this corresponds to the Credential Mode of Full Access on the main form (Figure 1). The other Credential Mode options on the main form selectively adjust the access level of all the input fields in various combinations. Besides the input fields, there are several accessory sub-controls (shown in blue in Figure 3). The Accessories section on the main form provides individual checkboxes that display or hide three of these sub-controls. You can hide the fourth?the descriptive label?by setting the label to an empty string.

Choosing to make the test button/status indicator visible gives users a convenient way to check their credentials without leaving the form. The test button’s color provides immediate visual feedback. It starts out gray (status disabled), and turns orange (status unknown) only after users have entered data in all the appropriate fields. Of course, the list of “appropriate fields” varies based on what a user has selected. For example, if users select Oracle as the database type then they must enter a username, password, and server, but because Oracle does not have the concept of database names, the database selector is disabled. After the button turns orange, users may click it to attempt to connect to the specified system.

When the connection succeeds, the button changes to green; otherwise, it turns red and opens a .NET ErrorProvider, shown in the exploded view of Figure 3 (the red exclamation symbol). The ConnectionStringManager uses that same error message to populate the separate result text box near the bottom of the control. Figure 3 shows several typical status messages in the result text box. You may hide the result text box if you want a more compact form, but it does offer an easy way for users to copy error messages. If you hide the test button, users have no way to test the connection or get error messages.

Both the ErrorProvider and the result text box fulfill one other purpose. In a SQL Server context, you may select a database using the ComboBox dropdown sub-control on the form. This ComboBox remains empty unless and until the user attempts to open the dropdown. At that point, the ConnectionStringManager initiates a quick dialog with the database and, if successful, populates the ComboBox with the list of available databases. If that process fails, both the ErrorProvider and the result text box display an error message.

Note that to retrieve the database list, the control must have all the requisite credentials available; it will remain disabled until that condition is satisfied. In other words, the ConnectionStringManager enables the test button and the database ComboBox only after users have filled out enough of the form to be able to attempt a database connection. (The criteria are almost identical: the test button needs everything the ComboBox needs, plus a value in the ComboBox itself.) The code below shows the logic used to enable both controls:

   dbComboBox.Enabled = (      (serverTextBox.TextLength > 0)  &&         (           (               dbType == DBTypes.Oracle               && usernameTextBox.TextLength > 0               && passwordTextBox.TextLength > 0            )         ||            (               dbType == DBTypes.SqlServer               && (authWindowsButton.Checked                  || (usernameTextBox.TextLength > 0                  && passwordTextBox.TextLength > 0)            )         )      )   );      testButton.Enabled = (dbComboBox.Enabled &&       dbComboBox.SelectedIndex >= 0);

The preceding code works whether or not sub-controls are enabled or even visible. So for example, if you do not display or enable the username field, but it’s empty, and you are using SQL Server authentication, the control will never enable the database dropdown and the test button. The point is that it’s your responsibility to ensure that any fields not exposed are nevertheless still set correctly.

Variations on a Theme
The Credential Mode choices in Figure 1, as stated earlier, manipulate the access level of the set of input fields via properties exposed by the ConnectionStringManager control. Table 2 shows how each credential mode renders.

Table 2. Setting Credential Modes: The table shows the effect of the various Credential Mode settings on the sub-controls in the ConnectionStringManager control.
Credential Mode Description Rendered Result
Full Control Here, the access level of all six input fields (username, password, server, DB type, database, and authentication) is set to Edit, allowing users to manipulate all the values.
Name and Password Here, the server, DB type, and database are hidden from users?either because they’re hard-coded and never need to be changed, or perhaps because the users don’t have the requisite privileges to grant exposure.
Server and DB You might use this scenario when your application uses a hard-coded “database user” account (where your application always uses a specific name and password) and you don’t want to reveal those to users for security reasons.
Server Only This scenario allows only the selection of a server by setting the access level of all other input fields to Hide.

When building your applications, you would typically decide on just one such configuration and set the properties of the ConnectionStringManager accordingly. Note how (by judicious use of the AutoSize and AutoSizeMode properties of both the control and the form) the credential form resizes intelligently depending on what is visible.

Figure 4. The Server Choice List: Providing a set of values in the Server Choice List causes the server input field to render as a ComboBox; with no supplied values it renders as a TextBox.

The ServerChoice list on the main form of the demo application provides one last setting that influences how the ConnectionStringManager renders. To restrict the available servers to a small, finite set (by far the most common case), supply the list of servers in the ServerChoice list (see Figure 4, left side). On the credential form the list renders as a non-editable ComboBox, limiting users to selecting only one of your supplied choices. If you do not provide a list of servers, the control will instead be rendered as a TextBox that allows users to enter an arbitrary server name (see Figure 4, right side).

As the demo application shows, designers can configure the ConnectionStringManager control to alter the control’s visual representation. But in some situations, the control’s configuration can also change based on users’ selections. The next sections illustrate this.

Windows Authentication and SQL Server Authentication
With SQL Server 2000 or 2005, you have the choice of allowing either Windows authentication or SQL Server authentication. Microsoft recommends using Windows authentication whenever possible . Windows authentication validates the user’s currently logged on Windows account to determine whether the user has access to the given database. SQL Server authentication, on the other hand, uses the account registry contained within SQL Server itself. So if a user selects Windows authentication, they don’t need to provide a name and password. As Figure 5 illustrates, the username and password fields become disabled when a user selects Windows authentication.

Figure 5. User-Level Configuration Based on Authentication Mode: Switching between Windows and SQL Server (SqlServer) authentication modes disables or enables the username and password fields.
Figure 6. User-Level Configuration Based on Database System: Switching between Oracle and SQL Server database types disables or enables the database field.

SQL Server vs. Oracle Database Types
The ConnectionStringManager can handle credentials for both SQL Server and Oracle, but the connection strings accepted by the two database systems each require different components. SQL Server has the concept of one server exposing many separate databases, while Oracle does not. Therefore, selecting Oracle disables the database ComboBox while selecting SQL Server enables it (see Figure 6).

Nested Access
You can set the access level of the input field sub-controls, as described previously, to Edit, Display, or Hide; however, some sub-controls will override your settings as user selections warrant. That is, suppose you set the username and password access to Edit with SQL Server authentication. But if a user switches to Windows authentication, the username and password fields are no longer applicable, so the control will disable them, effectively changing them from Edit to Display. But the control remembers your initial settings, so that if the user switches back to SQL Server authentication, the override goes away and the ConnectionStringManager reasserts your original setting, as illustrated in Figure 7. In that figure, the username and password fields start with access level Edit, with SQL Server authentication. When the user switches to Windows authentication, the control disables the username and password fields (access level Display) as shown in frame 2. Jumping to frame 5, the user switches back to SQL Server authentication so the username and password re-enable, returning to the original access level of Edit.

Figure 7. Nested Access: Your design-time settings may change because of user actions.

Frames 3 and 4 in Figure 7 show another interesting facet. In frame 3, the user has simply clicked on the dropdown in the database field. That click initiates a dialog with the server to obtain the list of databases to populate the dropdown. When that process completes, the control automatically selects the first server in the dropdown, which completes the set of components needed to be able to make a complete connection to the database. At that point the test button changes from disabled to unknown (orange). The user then clicks the test button and the control validates the database connection, turning the test button to green.

Note that the username field has a value of “user1.” But there is no such user in the database and yet the connection was successful. How is that possible? You have probably already guessed it: Windows authentication ignores the username and password. The ConnectionStringManager is aware of this condition, so when the user switches the authentication mode back to SQL Server, the test button changes to orange, indicating that with the current credentials?which now include “user1” and its associated password?the connection has not yet been checked. If the user activates the test button at this point, it will turn red, because the nonexistent “user1” could not log in.

Figure 8. One-Way Actions: User actions may change the design-time settings in one direction only?from less restrictive to more restrictive.

Figure 8 starts with the same fields set to Display rather than Edit. To see this, be sure you select Disable in the Rendering choices rather than Hide on the main form, as shown. Now, when you open the credential form, the username and password fields start out disabled, because of the access level. It so happens that Windows authentication also wants those fields disabled, so everything is in harmony.

But what happens when you switch to SQL Server authentication? Nothing?the username and password fields remain disabled. But what happened, you protest, to the user actions overriding the design-time settings? The control’s design-settings override works in only one direction; in other words, a user’s actions may cause the control’s fields to become more restrictive, but never less, restrictive. Otherwise, the change would violate the security of the model you designed. If you, at design time, chose to not allow users to change the application-wide username, then user actions on the credential form should not be able to countermand that decision.

Architecture of the Demo Project
To use the ConnectionStringManager in your own applications study Figure 9, which shows the demo project architecture. With a good grasp of this, you will find it very easy to use the control. For the demo project the user in the figure represents you, the designer. Your various selections on the main form adjust exposed properties of the credential form, also a class in the demo project. In turn, the credential form sets properties of the ConnectionStringManager control. Solid arrows indicate properties that are directly determined, whereas dotted arrows are set by the program indirectly. Also note that some arrows point right (indicating data flows left to right), some point left, and some are bidirectional, illustrating that the DbType and ConnectionString properties are the only ones read from the ConnectionStringManager. In the demo program, those are passed through the credential form back to the main form.

Figure 9. Demo Project Architecture: The main form, credential form, and ConnetionStringManager control exhibit this flow of settings and properties.

The ConnectionString is, of course, the whole focus of the ConnectionStringManager control. You start with either a complete, default connection string, or a partial, base version of a connection string. In either case you pass it from your main application to the credential form. The credential form hands it off internally to the ConnectionStringManager for manipulation. After a user has interacted with it, the credential form retrieves it and sends it back to your main application. The demo project also needs the DbType in the main application so that it knows whether to formulate a SQL Server or an Oracle test query when you press the Run Test Query button.

The code for the credential form (see Listing 1) is not complicated; most of the methods simply pass property values to the ConnectionStringManager. The last method (RenderingByHiding) is different only because it must convert between a Boolean and an access level.

The Mode property requires more code, as illustrated in Figure 9. Based on the radio button selected on the main form, this property sets the exposure level of the six input fields on the ConnectionStringManager control as shown in Listing 2.

The only remaining bits of code in the credential form are the event handlers for the OK and Cancel buttons:

   private void cancelButton_Click(object sender, EventArgs e)   {      Close();   }      private void okButton_Click(object sender, EventArgs e)   {      connStrMgr.Accept();      Close();   }

The Cancel button event handler does nothing but close the form. The OK event handler performs one crucial method call, connStrMgr.Accept(), which tells the ConnectionStringManager to commit the changes the user has made. Those changes are then available in any of the ConnectionStringManager properties (ConnectionString, SecureConnectionString, or ConditionalConnectionString).

Figure 10. Your Project Architecture: You customize ConnectionStringManager properties at design time on the credential form.

Architecture of a Real Project
In a real application, you’ll determine most if not all the run-time actions of the ConnectionStringManager control at design time, so the architecture looks much simpler (see Figure 10). The user in Figure 10 this time represents your application’s users. Notice that there is much less coupling between the main form and the credential form, as good design practice dictates. Also, although it appears at first glance as if there’s just as much coupling between the credential form and the ConnectionStringManager, most are dotted arrows, which signify property settings you can set at design time, as you lay out your form.

If your application requires a dual login?say a connection to both a SQL Server and an Oracle database?your main form can use this method to interact with the credential form (cf in the following code):

   private string SetCredentials(      string connString, string title)   {      cf.AllowServerSelection =         ((Control.ModifierKeys & Keys.Shift) == Keys.Shift);      cf.SetConnection(connString, title);      cf.ShowDialog();      return cf.ConnectionString;   }

Line one of the SetCredentials method above lets advanced users change servers by simply holding down the Shift key. The AllowServerSelection property shown above sets the familiar ExposeServer property of the ConnectionStringManager control. In this dual-login scenario, you maintain two separate connection strings, passing the appropriate one along with an appropriate title for the credential form. Here’s the AllowServerSelection property code:

   public bool AllowServerSelection   {      set       {          connStrManager.ExposeServer = value?            ConnectionStringManager.AccessLevel.Edit            : ConnectionStringManager.AccessLevel.Hide; }   }

Line two of the SetCredentials method invokes SetConnection, which prepares the credential form for display. This method sets four entities?marked by the numbers in square brackets in the comments below:

   public void SetConnection(string connString, string title)   {      key = title;         // [1] set title      connStrManager.ControlTitle = title + " login";         // [2] set connection string      AttributeDictionary connStrDict =          new AttributeDictionary(connString);         userDict = new AttributeDictionary(          Properties.Settings.Default.Usernames);      if (userDict.ContainsKey(key))       {           connStrDict["User ID"] = userDict[key];       }         pwdDict = new AttributeDictionary(          Properties.Settings.Default.Passwords);      if (pwdDict.ContainsKey(key))       {           connStrDict["Password"] = pwdDict[key];       }         connStrManager.ConnectionString =           connStrDict.ToString();         // [3] set remember pwd checkbox      connStrManager.RememberPassword =         Properties.Settings.Default.StorePasswords;         // [4] set db type      connStrManager.DbType = title.Equals("sqlserver") ?         ConnectionStringManager.DBTypes.SqlServer         : ConnectionStringManager.DBTypes.Oracle;   }

The items marked with [1], [3], and [4] in the preceding code should look familiar by now. Item [2] illustrates a technique for maintaining username and password dictionaries separate from the connection string. So, just before setting the ConnectionStringManager’s ConnectionString property the code manipulates the connection string to add the appropriate username and password.

Line three of the SetCredentials method opens the credential form. When the user closes the form, control returns to line four of SetCredentials, which simply returns the connection string via a pass-through property:

   public string ConnectionString   {      get { return connStrManager.ConnectionString; }   }

For completeness, here’s the rest of the credential form class definition, not counting the separate, designer-generated portion:

   public partial class CredentialForm : Form   {      private AttributeDictionary userDict;      private AttributeDictionary pwdDict;      private string key;         public CredentialForm()      {         InitializeComponent();      }         /* add other code fragments shown earlier… */         private void acceptButton_Click(object sender, EventArgs e)      {         connStrManager.Accept();         AttributeDictionary connStrDict =            new AttributeDictionary(            connStrManager.ConnectionString);         userDict[key] = connStrDict["User ID"];         pwdDict[key] = connStrDict["Password"];         Properties.Settings.Default.Usernames =             userDict.ToString();         Properties.Settings.Default.Passwords =              pwdDict.ToString();         Properties.Settings.Default.StorePasswords =            connStrManager.RememberPassword;         Close();      }         private void cancelButton_Click(object sender, EventArgs e)      {         Close();      }   }

The preceding code contains two event handlers, one for the OK button, and one for the Cancel button, just as in the demo project. The code in the acceptButton_Click routine assists with maintaining the dictionary of usernames and corresponding dictionary of passwords introduced earlier in the SetConnection method.

Finding a .NET application today that does not use a back-end database is rare, so you should easily find a use for the concepts and tools presented in this article. The ConnectionStringManager typifies the well-known “lazy axiom” of software development, to wit: create something only once and use it as a building block. Let me know if you find it useful.

Share the Post:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular