Connecting to SQL Server with Visual Studio

Visual Studio has many built-in tools that make working with data as simple as drag-and-drop. Well, it's not really that quick. But by answering a few questions and dragging-and-dropping one item, you can build an entire application that lets you edit data in your database. Let's try it together.

Creating a Data Source

Start up a new Windows Forms project in Visual Studiojust a plain Windows Forms project, not one of the Library-specific projects. Selecting the Data Show Data Sources menu command brings up the Data Sources panel, as shown in Figure 10-2.

Figure 10-2. Where are the data sources?

New projects don't include any data sources by default, so we need to add one. Click on the Add New Data Source link in the Data Sources panel. The Data Source Configuration Wizard guides you through the data source creation process.

1.	The first step asks, "Where will the application get data from?" Select "Database" and click the Next button.
2.	The second step asks, "Which data connection should your application use to connect to the database?" We'll have to create a new connection for the Library database we designed way back in Chapter 4, "Designing the Database." Click the New Connection button.
3.	The Add Connection dialog appears to collect the details of the new connection. In the Data source field, click the Change button to choose the type of connection. Select "Microsoft SQL Server" from the list that appears. (It's possible that this Change Data Source sub-dialog popped up automatically on your system.)
4.	Back on the Add Connection form, fill in the Server name field with the name of your SQL Server instance. Hopefully, this drop-down list already has the instances listed, but if not, you'll have to enter it yourself. The default for SQL Server Express is the name of your system with "\SQLEXPRESS" attached. If your system name is "MYSYSTEM," the instance name would be "MYSYSTEM\SQLEXPRESS."
5.	Configure your authentication settings in the Log on to the server section. I used standard Windows Authentication, but it depends on how you set up the database in Chapter 4.
6.	In the Connect to a database section, either select or type in "Library" for the database name.
7.	Click the Test Connection button to make sure it all works. When you're finished, click the OK button to create the new connection.
8.	OK, we're back on the Data Source Connection Wizard form. The connection we just created should now appear in the list, as shown in Figure 10-3. Click Next. Figure 10-3. The new database connection, ready to use
9.	The next panel asks whether this data source should become part of the configurable settings for this project. We'll get into the settings features of Visual Basic in Chapter 14, "Application Settings." For now, just accept the default and click OK.
10.	We're almost there. Only 27 more steps to go! Just kidding. This is the last step in creating the data source. The final panel shows a list of the data-generating features in the Library database. Open the Tables branch, and select Activity, as shown in Figure 10-4. Then click Finish. Figure 10-4. The final activity is selecting the Activity table

Check out the Data Sources panel shown in Figure 10-5. It includes the new "LibraryDataSet" data source with its link to the Activity table.

Figure 10-5. Finally, a real data source

Using a Data Source

So, what is this data source anyway? It is simply a link to some portion of your database, wrapped up in a typical .NET object. Now that it's part of your project, you can use it to access the data in the Activity table through your project's code, or by drag-and-drop. In the Data Sources panel, you will find that the Activity entry is actually a drop-down list. Select "Details" from the list, as shown in Figure 10-6.

Figure 10-6. Select the Details view instead of DataGrid

Finally, drag-and-drop the Activity entry onto the surface of Form1. When you let go, Visual Studio will add a set of controls to the form, plus a few more non-user-interface controls just below the form (see Figure 10-7).

Figure 10-7. A complete program without writing a single line of code

By just dragging-and-dropping, Visual Studio added all of the necessary controls and links to turn your form into a turbo-charged Activity table editor. Try it now by pressing the F5 key. In the running program, you can use the Microsoft Access-style record access "VCR" control to move between the records in the Activity table. You can also modify the values of each record, add new records, or delete existing records (but please restore things back to their original state when you are done; we'll need all of the original records later). Talk about power! Talk about simplicity! Talk about unemployment lines! Who needs highly-paid programmers like us when Visual Studio can do this for you?

Data Binding

In reality, Visual Studio isn't doing all that much. It's using a feature called "data binding" to link the on-form fields with the data source, the Library database's Activity table. Data binding is a feature built into Windows Forms controls that allow them to automatically display and edit values in an associated data source, such as a database. It's all sorted out through the properties of the control.

Select the FullNameTextBox control added to this project's form, and then examine its properties. Right at the top is a property section named "(DataBindings)." Its Text sub-property contains "ActivityBindingSource - FullName," a reference to the ActivityBindingSource non-user-interface control also added by Visual Studio. ActivityBindingSource, in turn, contains a reference to the LibraryDataSet object, the data source we created earlier. That data source links to SQL Server, to the Library database, and finally to the Activity table and its FullName field. Piece of cake!

If you count up all of the objects involved in this data-binding relationship, you come up with something like 5,283 distinct objects. It's no wonder that Visual Studio did so much of the work for you. Data binding provides a lot of convenience, but it also takes away a lot of your control as a developer. Although there are properties and events that let you manage aspects of the data binding and its update process, most of the essential code is hidden away inside of the data binding portions of .NET. You may not touch, taste, fold, spindle, or mutilate it, and that's just bad. A quick look at one of my core programming beliefs says it all: Good software includes maximum control for the developer, and minimum control for the user.

Part of your job as a developer is to provide a highly scripted environment for the user to access important data. This requires that you have control over the user's experience through your source code. Certainly you will defer much of that control to others when you use any Microsoft or tools supplied by third-parties. As long as those tools allow you to control the user experience to your level of satisfaction, that's great. But I've always been disappointed with data binding, except for the read-only display of data from the database. Similar features were in Visual Basic long before .NET arrived, and they have always made it difficult for the developer to control the various data interactions on the form.

Fortunately, if you eschew the data-binding features, Visual Basic will pass to you the responsibility of managing all interactions between the database and the user.