Beginning Visual Basic 2005 Express Edition - From Novice To Professional (2006)

440 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

You can right-click in the upper pane of the graphical designer to add new tables, and then just click on columns to select which ones should be included in the query, and use the middle pane to specify the filter, sort, and search conditions. Of course you can also type code directly into the bottom pane if you so desire. When you’re finished, to test the query just click the Execute Query button and you’ll get a preview of the data that the query will pull back.

Back on the main wizard page, clicking the Advanced Options button brings up the dialog box that governs how the adapters that connect to the database work behind the scenes. You can see this in Figure 16-23.

Figure 16-23. The Advanced Options dialog box lets you control what happens behind the scenes.

You can change whether or not the designer creates the Insert, Update, and Delete SQL statements, how the adapter handles concurrency (preventing one user of the application from overwriting another’s work—not a big deal with SQL Express because it’s designed for single users only, not multiple user systems). You can also force the adapter to refresh the data in the DataSet after an update. Neat, huh.

The one thing you can’t do is set the DataSet to use stored procedures in the database. For that you need to drop back into the main designer view and right-click the table in question again.

The pop-up menu that appears when you right-click on a table lets you configure the data adapter, as I just showed you. It also enables you to add additional queries to the adapter. By default the designer creates two methods for you: one to Fill() a data table with data, and the other to run the same query but return a brand new data table for use. If you need to hit additional stored procedures in the database, or run additional queries, just right-click on the table adapter and choose Add Query.

The Query Configuration Wizard shown in Figure 16-24 lets you choose to access the database with SQL code, write a new stored procedure, or select an existing one.

C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

441

Figure 16-24. The Query Configuration Wizard lets you add new queries into a table adapter and expose them as new methods that can be called in code.

For example, choosing to reference an existing stored procedure takes you to a page to let you choose the stored procedure you want to call. Just select it from the drop-down list shown in Figure 16-25.

Figure 16-25. You can easily reference existing stored procedures from the DataSet designer.

442 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

When you choose a stored procedure, any parameters it expects will be shown, and these will ultimately become method parameters on a new method generated inside the data adapter. Just click Next to get to the next page, tell the wizard what kind of stored procedure this is (one that returns a bunch of a data, a stored procedure that returns a single record, or a stored procedure that updates the database and returns nothing), and finally specify a name for the methods to generate as shown in Figure 16-26.

Figure 16-26. When you reference an existing stored procedure in the designer, you get to set a name for the new method that will be added to the adapter.

As with the GetData() and Fill() methods that are generated by default, the wizard will generate a method to run the stored procedure and populate an existing data table, as well as a method (the Get...() method) that will return a brand new DataTable to you.

An Overview of Data Binding

So far you’ve seen how easy it is to drop a grid onto a form “bound” to a database. You’ve also seen how to use the incredibly powerful DataSet designer to make it easy to grab and work with data in code.

C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

443

Visual Basic 2005 Express lets you put those two concepts together (the DataSet and data binding) in a stunningly versatile way. Pretty much every single visual control in the Windows user interface controls toolbox can be data bound, making it easy to create applications that display data and let users edit it and work with it in whatever way they please. Best of all—it’s really easy.

Rather than talk you through all the nitty-gritty details, let’s dive straight into a nice “Try It Out” to see this stuff in action.

Try It Out: Data Binding

Start up a new Windows application and as before add the Adventure Works data source to the project. Also as before, add in at least the Employee table to the DataSet.

The first thing you need to do to bind controls on a form to the database is drop a special control onto the form called the BindingSource control. All the other controls you drop on will use this to get at the database. It’s a nonvisual control, so when you add it to the form it appears underneath it, as shown in Figure 16-27.

Figure 16-27. Adding a BindingSource control to the form is the first step in setting up bound controls.

To make the BindingSource control work, you’ll need to set two properties: DataSource and DataMember. DataSource just points the data source at our project’s database. Click in it and then click the drop-down arrow to choose our project’s data source, as shown in Figure 16-28.

444 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

Figure 16-28. Use the BindingSource’s DataSource property to reference the database you want to bind to.

The DataMember property identifies the individual table that you want to work with. Click in it and then use the drop-down list to select the Employee table.

Notice that as soon as you do this, the AllowAddNew property changes to True. Because the DataSet by default is configured to handle inserting new records into the database, the BindingSource control picks up on this and sets the property to True to indicate that at runtime it can be used to feed values from controls on the form into new records in the database.

After you have a BindingSource control set up, the next step is to drop a DataNavigator control onto the form. Go ahead and do this now and your form will look like mine in Figure 16-29.

Figure 16-29. Add a binding navigator to the form.

C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

445

This step isn’t strictly required. You can set up bound controls on a form without this control, but the BindingNavigator control adds so much functionality for free that it’s worth using. The control is basically a toolbar that controls the BindingSource, letting you move from record to record in the DataSet’s table, add new records, cancel changes, and so on. If you come from a Visual Basic 6 background, you’ll have seen something similar for navigating through data. What’s different is that the navigator is an extensible toolbar. You can easily add new buttons to it specific to your application. You can even select the elements that are already displayed inside it and hide them if necessary. It’s a versatile control that you can customize to your heart’s content.

After the navigator is on the form, you need to connect it to the BindingSource so that it knows what it’s working with. You can do this with the BindingSource property. Simply click into the property and click the drop-down arrow to choose the BindingSource control we just set up.

Now add a text box, date time picker, and check box to the form, along with some labels, so that your form looks like the one in Figure 16-30.

Figure 16-30. Set up some controls on the form like this.

Don’t worry about setting up the control names.

Select the first text box on the form and look through its properties. Right at the top of the list (if you have the properties sorted alphabetically), you’ll find one called DataBindings. Expand this property and you’ll see the properties shown in Figure 16-31.

Figure 16-31. The DataBindings property collection is your key to setting up bound controls.

446 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

Click the Advanced property and then click the ellipsis button to bring up the Formatting and Advanced Binding dialog box in Figure 16-32.

Figure 16-32. The Formatting and Advanced Binding dialog is where you actually bind a control.

Click in the Binding drop-down list and choose our BindingSource control and the LoginID field as shown in Figure 16-33.

You’ll see instantly that you can now also specify events to fire on the control to validate the data inside it if the user makes changes. For now, just click the OK button to close the dialog and finish the binding.

Now walk through the same process to bind the other two controls. Bind the DateTimePicker control to the HireDate field, and bind the CheckBox to the SalariedFlag field.

C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

447

Figure 16-33. Use the drop-down list to choose the field in the binding source that you want to bind a control to.

That’s all there is to it. Run the application now and you’ll see your form appear like the one in Figure 16-34.

Figure 16-34. When you’re finished, you can run the application. It will have a vast amount of functionality, and you didn’t even have to write a single line of code.

448 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G

Notice that the navigator automatically shows you the number of records available to you, and you can move through them by clicking on the VCR-like buttons on the control. You can create new records by clicking the New Record button (database constraints permitting, of course). You can change anything, and cancel changes with the Cancel button.

There’s more to this, though. You’ll find that if you explore the Formatting and Advanced Binding dialog that you can bind almost any property of any control to any field on the database. Have fun.

Summary

ADO.NET and database work in .NET is a massive subject, and I’ve had to make compromises in this chapter as to what I could cover. What I did cover are the basics of setting up a database, adding it to your project, and then of course using it both in code and through data binding of controls. There are whole books written on this subject, so if databases are something that fascinates you, you should definitely spend some time poring through the online help to find out more about the controls covered in this chapter. The Movie Collection starter kit application that ships with Visual Basic 2005 Express is also a great place to look for some good example code demonstrating the range of things you can do with databases and Visual Basic 2005.

It’s also worthwhile reading up on the SQL Server Express online help to find all that that product can do. You can also find a lot of information on SQL Server Express online at the SQL Server Express site (http://msdn.microsoft.com/vstudio/express/sql/ default.aspx).

For most of you, the ground we covered here should be more than enough to get you on the way to developing your own databases and database-driven applications.

C H A P T E R 1 7

■ ■ ■

The Internet and Visual Basic

Call me weird, but I find talking to databases pretty cool. When I first came across them, the idea of being able to connect to some massive data source somewhere, extract volumes of information, and do with it as I wished seemed really quite neat. These days we all have access to a much bigger database than anyone could possibly have imagined just two decades ago: the Internet.

Visual Basic 2005 Express (and of course version 2 of the .NET Framework) ships with a bunch of tools that let you write programs that can actually go out on the Internet and do things. Perhaps you need a small application that will go out and get the next dates and times of your favorite TV shows. Perhaps you need to embed a web browser in your app and let your users view a mix of either local or remote web pages at runtime. Maybe you even need to have your program automatically exchange files with some remote server when it runs. All these things and more are possible with Visual Basic 2005 Express, and with very little effort. That’s a key point, in fact. Doing the kind of Internet-enabled work that .NET makes simple was a huge nightmare for developers just a couple of years ago. Viva Microsoft.

Introducing the WebBrowser Control

So, to demonstrate a point, let’s write our own custom web browser application. This would have taken a small team years of man effort in the past, but we’ll have one knocked out in about 10 minutes. Now how’s that for progress?

Try It Out: Building Your Own Web Browser

We’re going to cheat of course. Visual Basic 2005 Express has a handy control called a WebBrowser control that handles everything to do with web pages without any effort on our part. Go ahead and start up a new Windows application project.

When the default form appears, drop a StatusStrip control onto the form, just as in Figure 17-1.

449