Chapter 3: Connecting to Your Database

Visual Studio .NET Design-Time Features

The Visual Studio .NET development environment includes features that make it easy to create Connections at design time and use those Connections at run time.

Working with Connections in Server Explorer

The ADO.NET development team built ADO.NET to help you build fast, scalable database applications. The Visual Studio .NET development team built a number of features into Visual Studio .NET to help you build those database applications faster. One such feature is Server Explorer.

Server Explorer lets you examine various operating system services and integrate them into your applications. As you can see in Figure 3-5, Server Explorer lists many of the common operating system services such as event logs, message queues, and performance counters. You can also drag items from Server Explorer onto design surfaces such as forms and components in Visual Studio .NET to easily create components at design time that you can access at run time.

Figure 3-5

Visual Studio .NET Server Explorer

For example, if you want to access or add items to the machine's event log from your application, you can drag and drop an item from the Event Logs portion of Server Explorer onto a designer and then add code to your application to call that item.

Server Explorer also lets you interact with your database from within the Visual Studio development environment. SQL Server is one of the services that Server Explorer makes available through its server-based interface. There's also a separate category toward the top of Server Explorer called Data Connections.

In Chapter 2, we looked at the user interface that the Data Form Wizard displayed to request connection information. When we entered that connection information, Visual Studio added an item to the Data Connections area in Server Explorer. You can see this in Figure 3-6 below.

The items available beneath a connection depend on the type of database you've specified. Most databases support tables, views, and stored procedures. Some databases, such as recent versions of Oracle and SQL Server, support database diagrams and functions. Oracle also supports synonyms and packages. All of this information is available in Server Explorer.

You can expand a table to see the list of columns in the table. Click on a column, and you can see properties of the column in the Properties window. If you want to view the contents of a table or view, you can right-click on the item and choose Retrieve Data From Table. Server Explorer will display the contents of the table or view in a grid. The data might be updateable if you have permission to modify the data, and Server Explorer can retrieve the key information necessary to submit changes.

Figure 3-6

Data Connections in Server Explorer

If you're using the Enterprise Edition of Visual Studio .NET, you can also manage your SQL Server and Oracle databases. You can modify, create, and delete SQL Server and Oracle tables, views, stored procedures, database diagrams, and functions.

Adding a Data Connection to Server Explorer

You can add a data connection to Server Explorer by clicking the button at the top of Server Explorer that shows a yellow cylinder with an electrical cord attached. You can see in Figure 3-7 the same Data Link dialog box that you saw when we added a connection using the Data Form Wizard.

	In diagrams, databases are represented almost universally by cylinders. I'm not entirely sure why, but my technical editor, Dail, informed me that this is —likely because a cylinder is the old flowchart symbol for mass storage—chosen because long, long ago random-access mass-storage devices used drums rather than disks. He also claimed not to be old enough to remember such drives before saying "Now go away and let me drink my Geritol in peace." He gets a little cranky when he hasn't had his afternoon nap.

Figure 3-7

Adding a new data connection to Server Explorer using the Data Link Properties dialog box

OLE DB, ODBC, and .NET Data Providers

Adding connections to Server Explorer is straightforward in most, but not all, cases. To understand why things aren't always so simple, we'll need to discuss some low-level data access technologies, both past and present. The next couple of pages might look like condensed alphabet soup as a result. This information is geared toward developers who want to use ODBC drivers or .NET data providers other than those built for OLE DB or SQL Server.

The Data Link dialog box was originally designed as an interface for building connection strings in Visual Studio 6. The central data access technology in version 6 is ADO, which is based on a lower-level data access technology called OLE DB.

The Providers tab of the Data Link dialog box displays a list of OLE DB providers built to help you connect to a particular type of database such as SQL Server, Oracle, or Microsoft Access.

Before Microsoft developed OLE DB, the most prevalent data access technology was ODBC, OLE DB's predecessor. Developers communicated with their databases through ODBC drivers. In fact, many developers still build applications that rely on ODBC drivers because some databases have ODBC drivers but not an OLE DB provider.

The first OLE DB provider that Microsoft developed acted as a bridge between the two technologies by translating OLE DB calls to ODBC calls. This allows developers to use ADO, which uses OLE DB providers, with ODBC drivers. You'll see this provider listed on the Providers tab of the Data Link dialog box as Microsoft OLE DB Provider For ODBC Drivers. If you select this provider and then click on the Connection tab, you can select an ODBC data source or enter an ODBC connection string.

So far, we've discussed how to use the Data Link dialog box to connect to a data source using OLE DB and ODBC. But what about .NET data providers? As I mentioned earlier, the Data Link dialog box was built for Visual Studio 6. As of the release of Visual Studio .NET, there is no user interface for building connection strings for .NET data providers in general. Thus, you can add Data Connections to Server Explorer only if the desired data source has an OLE DB provider or ODBC driver.

Saving Password Information

You can enter a password in the Data Link dialog box and click the Test button to verify that you can connect successfully. For security reasons, the Data Link dialog box will not save the password that you entered into the new connection string unless you select the Allow Saving Password check box. If you select this check box, you'll see the warning shown in Figure 3-8.

Figure 3-8

The warning that appears if you try to save a password into a connection string

If you choose to save the password into the connection string, the password information will be used both at design time by Server Explorer and at run time by the applications you build. Thus, you won't have to enter the password again when Server Explorer connects to your database. However, this also means that if you use this connection string in your application, the password will be built into the application.

Microsoft doesn't recommend storing the password in your application in this fashion because it is not secure. Visual Studio .NET builds managed code, which can be decompiled. This means that users can decompile your application and find the connection string. If this possibility makes you uncomfortable, you should omit the password from the connection string, and prompt the user for the password—or use integrated security, which we'll discuss next.

Integrated Security

Who says you need a password to connect to your database? Some database systems, such as SQL Server, allow you to connect using your network identity. This feature is known by a number of different names—network authentication, Windows authentication, Windows NT authentication, or integrated security. It's a fairly simple concept. Rather than relying on the user to specify a username and password, the database asks the network to identify the user. The database then checks its user list to determine whether that user has permission to connect.

For more information on using SQL Server with integrated security, look under Administering SQL Server\ Managing Security\ Security Levels\ Authentication Modes in SQL Server Books Online.

Adding Connections to Your Application

Now that you've seen how to add data connections to Server Explorer, let's talk about how you can use Server Explorer to add a Connection to your application. It's as simple as dragging and dropping. Just select the desired connection in Server Explorer, and drag-and-drop it onto the designer in your project. You'll see a new Connection in the component tray for the designer.

The Component Tray

Many Visual Studio .NET project items—such as Windows forms, component classes, Web forms, and ASP.NET Web services—allow you to interact with controls and components in a visual fashion. If you're working with a simple Windows form, you can place visual controls such as buttons and text boxes on the form. You can set the properties of these controls in the Properties window. This feature is old hat to developers who've used development tools such as Visual Basic or Microsoft Visual InterDev.

But what if you want to add a component that, unlike a control on a form, has no user interface? Previous versions of Visual Basic treated such components as controls, and those components appeared as icons on your forms, but only at design time. Visual Studio .NET takes a slightly different approach. Components that have no user interface appear in a tray below the designer—the component tray. The component tray lets you interact with these components and set their properties at design time in the Properties window.

Using Your New Connection

When you drag-and-drop a data connection from Server Explorer onto a designer, you create a Connection object in the designer's component tray. The type of Connection will depend on the type of data connection you selected in Server Explorer. Dragging and dropping a SQL Server connection creates a SqlConnection, while dragging and dropping a connection in Server Explorer that uses any other data source creates an OleDbConnection.

	As of this writing, you cannot drag-and-drop connections from Server Explorer that use ODBC drivers. You'll receive an error stating that the OLE DB provider for ODBC drivers cannot be used. Look for the ability to create an OdbcConnection object, the Connection object for the ODBC .NET Data Provider, in this fashion in a future release.

Figure 3-9 shows what you'll see if you drag-and-drop a SQL Server data connection from Server Explorer onto a designer such as a Windows form:

Figure 3-9

The new SqlConnection1 object appears in the Visual Studio .NET components tray.

Because we dragged a SQL Server connection onto the designer, we received a SqlConnection. Visual Studio .NET automatically names new components based on the class name. This new SqlConnection is already initialized. Its ConnectionString property is set to the connection string for the corresponding data connection in Server Explorer.

Creating Connections Using the Toolbox

You can also create Connections by dragging and dropping items from the Data tab in the Visual Studio .NET Toolbox. This lets you specify which .NET data provider's Connection you want to use. Figure 3-10 shows a new OleDbConnection created by dragging and dropping the OleDbConnection from the Toolbox.

Figure 3-10

Setting the connection string using the Properties window

Note that none of the properties are set...yet. The main property for a Connection object, regardless of the .NET data provider you're using, is the ConnectionString property. You can select the ConnectionString property in the Properties window and type a value for the property by hand. Or, you can ask Visual Studio .NET for a little help.

You can select the ConnectionString property in the Properties window and view a drop-down list of available connections from Server Explorer. (Figure 3-9 shows an example.) At the end of the list, you'll see a <New Connection...> entry. Select this entry, and you'll launch the Data Link dialog box.

Using the New Connection at Run Time

Now that we've created a new connection at design time, let's use it at run time. Drag a button object onto your Windows form, and double-click the button to launch the code editor. You'll see that Visual Studio .NET creates a procedure to handle the button's click event. Add the following code to that event:

Visual Basic .NET

SqlConnection1.Open() MessageBox.Show("Connection opened successfully!") SqlConnection1.Close() MessageBox.Show("Connection closed successfully!")

Visual C# .NET

sqlConnection1.Open(); MessageBox.Show("Connection opened successfully!"); sqlConnection1.Close(); MessageBox.Show("Connection closed successfully!");

	If you used an OleDbConnection instead of a SqlConnection in your project, change SqlConnection1 in the preceding code to OleDbConnection1.

Now launch your project and click the button. You'll see dialog boxes that tell you that you successfully opened and closed the connection to your database.

Where's the Code?

So far, we've built a fairly simplistic application. We created a SqlConnection at design time and added a button and some code to use it at run time. Figure 3-11 shows the code we added. But how did the SqlConnection get in the application?

Figure 3-11

Adding code

Visual Studio .NET translates the objects that you create at design time, such as the SqlConnection in the component tray and the button on the form, into code. It then stores this code in the component's InitializeComponent procedure. This procedure is hidden by default in the region marked Windows Form Designer Generated Code, as shown earlier in Figure 3-11.

There are two main reasons why this code is hidden by default. First, developers generally want to focus on the code they've written rather than the code the designers have generated. You can click on the plus sign to the left to expand the region if you choose. Second, and more important, Microsoft strongly cautions against changing code in this region.

Most of the Visual Studio .NET designers that generate the code in this region are reentrant. The button we drew on the form is a simple example of this reentrancy. As you can see in Figure 3-12, the Windows Forms Designer translated the button we drew at design time into run-time code.

Figure 3-12

Code generated by the Windows Forms Designer

If you close and reopen the project and display the form in the Windows Forms Designer, the designer will use this code to draw the button on your form. Microsoft strongly cautions against changing code in this region because the designers that read the code might not be able to interpret the changes you make, so you might not be able to edit your component within the designer.

Will you get a warning ahead of time? Probably not. Will you know ahead of time whether the designer will successfully interpret the changes you've made in this region? Probably not. If you're going to make changes in this fashion, you should definitely create a backup of your project ahead of time.