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

420 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

In order to tell ADO.NET where the database is, what it’s called, and so on, you need a connection string. By this point the wizard has created the connection string and is advising you that the connection string will be stored in the application’s configuration file. This is a file in the project called app.config. The database classes in the .NET Framework are able to retrieve the connection string from this file at runtime. Click the Next button.

The final stage of the wizard appears now, as shown in Figure 16-6.

Figure 16-6. The final stage of the wizard lets you specify what will be included in your DataSet.

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

421

A DataSet is a container that can mirror the structure of an entire database if necessary. At this stage of the wizard you simply need to choose which objects you want to work with. For example, if you wanted to call a stored procedure in the database and display the results in the application, you would find the stored procedure in this stage of the wizard and select it. The wizard then builds what’s known as a typed DataSet, a class derived from the standard ADO.NET classes that provides you with easy type-safe access to tables and columns in the data, as well as methods to select that data to repopulate the DataSet on demand.

Expand the tables part of the tree and select the Contact, EmployeeAddress, and Employee tables from the list. Then click Finish.

After a brief delay, you’ll find yourself returned to the designer. Take a look at the Solution Explorer in Figure 16-7.

Figure 16-7. The Solution Explorer now has a new DataSet and the database you referenced.

Notice that you now have a DataSet, the database, and the app.config file that holds the connection string included in the solution.

422 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

Double-click on the DataSet to open up the DataSet designer shown in Figure 16-8.

Figure 16-8. The DataSet designer

This shows you a graphical view of the tables, stored procedures, and views that you’ve selected to be in the DataSet. Notice how the relationships between the tables are drawn for you.

Each box represents both a DataTable object and a DataAdapter object. The DataAdapter objects are what you will use in code to grab information from the database, hence the Fill() and GetData() methods shown at the bottom of each.

Let’s go ahead now and actually use the DataSet and the data adapters to grab information and display it on a form. Double-click on Form1 in the Solution Explorer to bring up the form in the designer. Take a look at the Toolbox and find the DataGridView control. Double-click it to drop it onto the form, just as in Figure 16-9.

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

423

Figure 16-9. Drop a DataGridView control onto the form.

As soon as you drop the grid onto a form, the smart tag pops up, enabling you to configure it. All you need to do for now is point the grid at the right data source. Use the Choose Data Source drop-down list and expand the Other Data Sources Tree, then Project Data Sources, then Adventureworks, and choose the Contact table.

Still using the smart tip, select Dock in Parent Container to resize and lock the grid to the form’s position. Then run the application. The form appears with the data inside it, as shown in Figure 16-10.

Figure 16-10. Run the program, and it will grab the data from the database and show it in

the grid.

424 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

So, with just a few mouse clicks you’ve added a data source to the application, dropped a control onto a form, and then got the control to “bind” to the database. DataGridView controls aren’t the only ones that can bind to data. You can pretty much bind any control you want to a database. It’s just that for the purposes of this example you get more bang for your buck with DataGridView. You’ll also notice that you get a lot of functionality right out of the box with DataGridView. You can sort columns by clicking on the headings, change items you see, even add new ones by moving to the blank line at the bottom of the grid.

Stop the application that is running and then take a look at the code behind the form. The only code that was added lives in the form’s Load event:

Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'AdventureWorks_DataDataSet.Contact' table. You 'can move, or remove it, as needed. Me.ContactTableAdapter.Fill( _

Me.AdventureWorks_DataDataSet.Contact)

End Sub

When the form loads, the Fill() method is called on employeeTableAdapter, passing in the Employee table as the DataTable to load information into. These types were created on the form when you bound the grid. That’s really all there is to it. Because you bound the grid view in design time, it already knows where it’s expecting to retrieve data, so the only code necessary is the preceding line to make sure that data is loaded.

Let’s move on now and look at all the tools available in the IDE for actually creating and working with your own databases.

Exploring the Database Creation Tools

You saw in the preceding “Try It Out” that there’s a great deal of support built right into the Visual Basic 2005 Express IDE for working with an existing database. However, there are also tools in there to let you create and build your own databases from scratch.

You can add a blank database to any project in the same way that you would add any other item, such as a class or a form. Just right-click on the project in the Solution Explorer and choose Add New Item. When the Add New Item dialog box appears, select Database, give it a name, and then click Add. Doing this will also automatically generate a typed DataSet in your project and pop open the last page of the Data Source Configuration

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

425

Wizard again for you to choose which items you want to add. Obviously, with a new database you can’t choose specific tables to add, so most people just click Finish when the wizard appears and leave the DataSet blank until they have their database fleshed out a bit more.

After you have a database in your project, whether it’s a new one or a reference to an existing one, double-click it to open the Database Explorer on the left side of the IDE, as in Figure 16-11.

Figure 16-11. Double-clicking the database in a project opens up the Database Explorer window.

As the name suggests, this window lets you explore the items within your database and of course add new ones. The Database Explorer shows you a tree view of the selected database with tables, views, stored procedures, and so on. To create a new item, just right-click on the category of item you want to add into the database and select New from the popup menu that appears. Let’s take a look.

Try It Out: Creating a Table

Create a new Windows application project and then add a new blank database to that project as outlined previously. After the database appears in the Solution Explorer, double click-it to open the Database Explorer window.

426 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

In the Database Explorer, right-click the Tables folder and select Add New Table. The main area of the IDE changes after a brief pause to show you the table designer in Figure 16-12.

Figure 16-12. The table designer

You’re going to create a simple blog table. The first thing you need is a unique ID for each blog entry. In the Column Name part of the grid, type id and then hit Tab. The view changes to show you more properties for the column as the cursor moves into the Data Type column (see Figure 16-13).

Figure 16-13. After you enter a name for a field, additional properties for the field come

into view.

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

427

Let’s make this ID field be an auto-incrementing number, so that as new blog entries are added to the table, they each get their own ID number, unique within this table. Select bigint as the data type and then in the Column Properties area of the designer find the Identity Specification section. Change the Is Identity property underneath Identity Specification to Yes, just as in Figure 16-14.

Figure 16-14. You can set a field to be an auto-incrementing field in the properties area.

The two properties beneath (Identity Increment and Identity Seed) will enable you to set their values. The default values here, though, are fine.

Let’s add two more columns—one for the title of a blog entry and another for the blog entry’s main content.

Set the name of the next column to title and go ahead and set the type to varchar(100). That makes it a variable-length text field up to 100 characters long. Click the Allow Nulls check box to deselect it.

Now add in the final column, postContent with a type of text, and again turn off the Allow Nulls check box for the column. When you’re finished, the table will look like Figure 16-15.

Figure 16-15. Your final table design should look like this.

Now you need to set the primary key on this table to the ID field. Just click in the ID field and then click the Set Primary Key icon on the toolbar, as in Figure 16-16.

428 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-16. The last stage of creating the table is to set its primary key.

This puts a small key symbol next to the ID field. If you wanted to use more than one field to make up the primary key, you’d just Shift+click beside each field and then click the key icon.

Close the designer and you’ll be asked if you wish to save the table design. Click the Yes button and then enter BlogPosts for the name of the table.

Your new table is now designed and ready to use. Let’s put some data in it.

Find the BlogPosts table in the Database Explorer, right-click it, and select Show Table Data from the pop-up menu that appears. The table’s data, which of course is empty at the moment, appears in the designer, as in Figure 16-17.

Figure 16-17. You can view and edit data in a table right within the VB Express IDE.

Enter a couple of rows of data. You don’t need to enter anything in the ID column because this is an autonumbering column and will generate its own value as you enter each row of data.

We’re now in good shape to add this table to our DataSet, ready to write code with it.

Double-click on the DataSet in the Solution Explorer to open the DataSet designer, and then simply drag the table from the Database Explorer into the DataSet designer.

The “Try It Out” did of course assume that you know roughly what you are doing when it comes to selecting data types for database columns. I don’t have enough room to go into that in any detail here, but at the end of the chapter I’ll provide you with pointers to online help to find out more about defining columns and other elements of SQL Server databases.

Designing tables is all well and good, as is the ability to add data to them. At some point, though, you are going to want to get data out. That’s where stored procedures come in. You could create DataSets and use the designer to generate SQL code for you to grab data from the database, but it’s good practice to store this SQL code in stored procedures;

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

429

they are faster to execute and more secure than embedding database code inside your application.

To create a stored procedure, just right-click on the stored procedures folder in the Database Explorer and select Add New Stored Procedure. The code editor will appear to let you key in the SQL for your stored procedure, as in Figure 16-18.

Figure 16-18. The code editor can be used to create stored procedures.

As you can see, the code editor pops up with the template for a new stored procedure. You will need to know how to write SQL, though, to fill in the blanks.

You can use exactly the same process to create anything else you might need in a database, such as views and functions. Again, refer to the SQL Server Express online help referenced at the end of this chapter for more information on how to write the SQL statements for a stored procedure, as well as more info on just what views and functions are.

Writing Database Code

Let’s move away from the visual tools for a moment and take a look at writing code to work with a database. Let’s kick off with exploring how to move through rows from a database in code.

Try It Out: Navigating Through Rows in a Table

Start up a new console application. When the project is ready, add the Adventure Works database, just as you did in the first “Try It Out” earlier in this chapter. After you have the database in the project and you’ve selected the Contact, Employee, and EmployeeAddress tables, you’ll start writing code.

Double-click on Module1.vb to open the code editor, ready to start writing code. When you added the data source to the project and created the DataSet, the VB Express IDE added in a few new classes for you. In fact, every box you see in the DataSet designer is a new class. These classes subclass standard ADO.NET classes