C# 24-Hour Trainer (2015)

Section VII

Specialized Topics

Lesson 34

Programming Databases, Part 1

Database programming is another truly enormous topic, so there isn't room to cover it all here. However, Visual Studio provides tools that make some simple kinds of database programs so easy to write that your education won't be complete until you've written a few.

In this lesson, you learn how to make a simple database application. You learn how to connect to a database, load data, let the user navigate through records, and save any changes.

Connecting to a Database

The first step in building a database program is giving it a connection to the data. You can easily do this interactively at design time, although it requires quite a few steps:

NOTE

If you want to following along with these instructions, you may want to download the database file Contacts.mdb, which is available in this lesson's downloads.

1. First open the Project menu and select Add New Data Source to display the dialog shown in Figure 34.1. As you can see in the figure, you might want the program to get data from several different places. The data source used in this example is a database, so select Database and click Next.

2. The dialog's next screen lets you decide whether you want to use a data set or an entity data model for your data. (The entity data model option won't be there if you don't have the entity framework tools installed. You won't be using an entity data model in this lesson, so if that option doesn't appear, don't worry about it.) For this example, pick Dataset and click Next to make the dialog display the screen shown in Figure 34.2.

Figure 34.1

Figure 34.2

NOTE

A data set is an in-memory representation of a data source. A data set can include multiple tables that are related with complex database relationships, although this example's database contains only a single table.

3. If you have previously built data connections, you can pick one from the dropdown list. Otherwise, click the New Connection button to display the dialog shown in Figure 34.3.

Figure 34.3

For this example, select Microsoft Access Database File and click Continue to see the dialog shown in Figure 34.4.

Figure 34.4

Enter the name of the database in the textbox or click the Browse button and select it. When you're finished, if you like, you can click Test Connection to see if Visual Studio can connect to the database.

Click OK to create the new connection and return to the dialog shown in Figure 34.2.

TIP

If you click the plus sign at the bottom of the dialog shown in Figure 34.2, you can see the connection string Visual Studio built to connect to the database. You won't need that string now, but you may want it later if you use code to connect to a database.

Picking a Database

Picking the right database product is a tough decision. Microsoft Access databases have the advantage that a C# program can read and manipulate one even if Access isn't installed on the computer. That means you can build a database on one computer that has Access installed and then copy it to another computer that doesn't have Access and use it there.

SQL Server, Oracle, MySQL, and similar database products tend to provide more database features than Access. For example, they support bigger databases, triggers, views, and other features that Access doesn't provide.

A common choice is to start development with SQL Server Express Edition, a free version of SQL Server that has some size restrictions. Later if you decide you need the extra space provided by the full version of SQL Server, you can upgrade relatively easily. You can learn more about SQL Server Express and download it at www.microsoft.com/express/Database.

Unfortunately a C# program cannot use these more powerful databases unless you have them installed, an assumption I don't want to make, so this lesson works with Access databases. You can get the necessary databases from the lesson's code download and use them even if you don't have Access installed.

If you're planning to do more database programming, I encourage you to install one of the more powerful database products, particularly since SQL Server Express and MySQL are free.

4. When you click Next, Visual Studio asks whether you want to include the database in the project. Click Yes to copy the database file into the project so it can easily be distributed with the program.

5. The dialog's next page asks whether you want to include the database connection string in the program's configuration file so the program can use that string to connect to the database at run time. This is often convenient because it lets you change the connection string without rebuilding the application. Note, however, that you shouldn't store database passwords in the configuration file, so if the database requires a password, you may want to leave the connection string out of the configuration file.

When you click Next again, you see the page shown in Figure 34.5.

Figure 34.5

6. Expand the database object treeview and select the tables and fields that you want the program to use. In this example, the database contains only one table. In Figure 34.5 I selected the Tables entry and that selected the database's single table and all of its fields.

7. When you click Finish, Visual Studio defines a data set that can hold the data in the database. It also adds some code to make working with the data set easier.

Now that you've added a data source to the project, Visual Studio provides easy ways to make two simple kinds of database programs: one that displays data in a grid and one that displays data one record at a time.

Displaying Data in a Grid

To display data in a grid, first open the Data Sources window. If you can't find it, use the View Other Windows Data Sources command to find it. Figure 34.6 shows the Data Sources window after I connected to a Microsoft Access database named Contacts.mdb.

Figure 34.6

TIP

Often developers make the Data Sources window a tab in the same window as the Toolbox.

To display data in a grid, click a table in the Data Sources window and drag it onto the form. When you drop the table, Visual Studio adds several objects to the form to help manage the table's data. A few of these objects appear on the form itself, but most of them appear in the Component Tray below the form. When you drop the table on the form, Visual Studio adds:

· A DataGridView—This control displays the data.

· A data set—This data set can hold the table's data at run time.

· A BindingSource—This object encapsulates the data source. It provides a link between the form's controls and the data source.

· A data adapter—This object provides methods to move data between the database and the data set.

· A table adapter manager—This object helps coordinate movement of data by the data adapter.

· Binding navigator—This object provides navigation services for the controls on the form. For example, buttons that move to the next or previous record use these navigation services.

This seems like a confusing assortment of objects. Fortunately you don't need to do much with them for the simple database applications described in this lesson.

Figure 34.7 shows the program created by Visual Studio at run time. The only changes I made were to resize the form and dock the DataGridView control to make it fill the form.

Figure 34.7

The DataGridView and the BindingNavigator (which provides the buttons at the top) automatically let the user perform a lot of simple database tasks, including:

· Clicking a cell and typing to change its value

· Selecting a row and pressing Delete to delete the corresponding record

· Clicking and dragging on the left of the data to select multiple rows, which the user can then delete all at once

· Using the navigation buttons to move through the records

· Entering values in the last row to create a new record

· Resizing rows and columns

· Clicking the floppy disk button to save changes to the data

· Clicking a column header to sort the records using that column

NOTE

In this kind of program, changes are made locally to the data set and are not copied to the database until the user clicks the Save button.

NOTE

If you build a program as described so far, make a change, and click the BindingNavigator's Save button, you may be surprised to find that the changes don't seem to be saved. They actually are saved, but by default the project copies the database into the executable directory every time it runs, and the new copy of the database overwrites the saved data so it looks like the changes weren't saved.

One way to fix this is to not include the database in the project or to use Solution Explorer to set its Copy to Output Directory property to Do not copy or Copy if newer.

You should add a few things that this automatically generated program doesn't do to this simple example. The most important of these is to check for unsaved changes before allowing the form to close.

The following FormClosing event handler prevents the user from accidentally closing the form with unsaved changes:

// Check for unsaved changes.

private void Form1_FormClosing(object sender, FormClosingEventArgs e)

{

// See if there are unsaved changes.

if (this.contactsDataSet.HasChanges())

{

// Make the user confirm.

DialogResult result = MessageBox.Show(

"Do you want to save changes before closing?",

"Save Changes?",

MessageBoxButtons.YesNoCancel,

MessageBoxIcon.Question);

if (result == DialogResult.Cancel)

{

// Cancel the close.

e.Cancel = true;

}

else if (result == DialogResult.Yes)

{

// Save the changes.

contactsTableAdapter.Update(contactsDataSet);

// Make sure the save worked.

// If we still have unsaved changes, cancel.

e.Cancel = (this.contactsDataSet.HasChanges());

}

// Else the user doesn't want to save

// the changes so just keep going.

}

}

If the data set has unsaved changes, the code asks the user whether it should save the changes. If the user clicks Cancel, the code sets e.Cancel to true so the program doesn't close the form.

If the user clicks Yes, the code calls the table adapter's Update method to save the data set's changes back to the database.

If the user clicks No, the code just continues and lets the form close without saving the changes.

Displaying Data One Record at a Time

Instead of displaying a table's records in a grid, you can display the data one record at a time, as shown in Figure 34.8.

Figure 34.8

With this kind of interface, you can click the navigation buttons on the BindingNavigator to move through the records. You can use the display controls (TextBoxes in Figure 34.8) to change a record's values.

To build this interface, first create a data source as before. Then, instead of dragging a table from the Data Sources window onto the form, drag individual fields onto the form. For each field, Visual Studio adds a Label and an appropriate display control (such as aTextBox) to the form.

This version of the interface does most of the things the grid-based version does but in different ways. For example, to create a new record you can't simply type values into a new row in a grid. Instead you need to click the BindingNavigator's Add New button (which appropriately looks like a plus sign).

As in the grid-style example, the code created by Visual Studio doesn't check for unsaved changes before the form closes. You can solve this problem by adding a FormClosing event handler to check for unsaved changes as before.

This version of the program works a little differently than the previous grid-style version, however. The DataGridView control used by the previous program automatically marks the data as modified when the user starts changing a value. In contrast, the new program marks the data as modified only when the user changes a value and then moves to a new record. That means if the user changes a value and then tries to close the form without moving to a new record, the program doesn't know there is an unsaved change and closes.

To prevent that, you can add the following two lines to the beginning of the FormClosing event handler:

this.Validate();

this.contactsBindingSource.EndEdit();

These lines make the program officially finish editing any fields that the user is modifying so the data set knows that it has a pending change. After that, the FormClosing event handler works exactly as before.

Try It

In this Try It, you have a chance to practice the techniques described in this lesson. You create an application that displays contact information in a grid.

Lesson Requirements

In this lesson, you:

· Start a new project. Download the Contacts.mdb database from the book's website and place it in the project directory.

· Add a new data source for this database.

· Open the Data Sources window and drag the Contacts table onto the form.

· Add code to the FormClosing event handler to check for unsaved changes.

NOTE

You can download the code and resources for this lesson from the website at www.wrox.com/go/csharp24hourtrainer2e.

Hints

· Dock the DataGridView control so it fills the form.

· Resize the form so all fields are visible. Add a little extra width for a vertical scrollbar on the right.

· Don't forget to set the database file's Copy to Output Directory property to Copy if newer.

Step-by-Step

· Start a new project. Download the Contacts.mdb database from the book's website and place it in the project directory.

1. This is straightforward.

· Add a new data source for this database.

1. Follow the steps described earlier in this lesson.

· Open the Data Sources window and drag the Contacts table onto the form.

1. This is straightforward.

· Add code to the FormClosing event handler to check for unsaved changes.

1. Use the code shown earlier in this lesson.

Exercises

1. Make a program similar to the one you built for the Try It except make it display one record at a time instead of use a grid. Anchor the TextBoxes so they widen if the form widens. Don't forget to add the FormClosing event handler.

2. Copy the program you built for this lesson's Try It. That program's grid lets the user navigate through the records, add records, and delete records, so you don't really need all of those buttons on the BindingNavigator. Select the BindingNavigator. In the Properties window, click the Items property and click the ellipsis to the right. Set the Visible property to false for every item except the Position, Count, and Save items.

3. Copy the program you built for Exercise 1. Add a MenuStrip with a Data menu that has items First, Previous, Next, Last, Add New, Delete, and Save. Set the Visible property on the corresponding BindingNavigator buttons to false.

To make the menu items work, use the BindingSource's CurrencyManager. That object's properties and methods let you manipulate the current record (hence the name CurrencyManager). For example, the following code sets the current position to the first record:

this.contactsBindingSource.CurrencyManager.Position = 0;

Add or subtract one from Position to move to the next or previous record. Set Position to the CurrencyManager's List.Count - 1 value to move to the end of the list.

Use the RemoveAt method to delete the current record.

Finally, enter the necessary code for the Save menu item.

4. [WPF] You can use the techniques described in this lesson to make simple database applications in WPF, too. The results are similar, but some differences exist. In particular Visual Studio doesn't place a BindingNavigator on the WPF Window. The program also includes data set and table adapter objects, but they're hidden inside the code.

For this exercise, repeat the Try It with a WPF application. After you create the database connection, run the program to let it build some data structures that it needs. Then drag the Contacts table onto the Window and arrange it as before.

Because Visual Studio doesn't create a BindingNavigator, add a File menu with a Save item that uses the following code to save changes to the data:

private void saveMenuItem_Click(object sender, RoutedEventArgs e)

{

// Save the changes.

ContactsDataSet contactsDataSet =

(ContactsDataSet)this.FindResource("contactsDataSet");

ContactsDataSetTableAdapters.ContactsTableAdapter

contactsDataSetContactsTableAdapter =

new ContactsDataSetTableAdapters.ContactsTableAdapter();

contactsDataSetContactsTableAdapter.Update(contactsDataSet);

}

Use the following Window Closing event handler to protect the user from losing changes when the program closes:

private void Window_Closing(object sender,

System.ComponentModel.CancelEventArgs e)

{

ContactsDataSet contactsDataSet =

(ContactsDataSet)this.FindResource("contactsDataSet");

// See if there are unsaved changes.

if (contactsDataSet.HasChanges())

{

// Make the user confirm.

MessageBoxResult result = MessageBox.Show(

"Do you want to save changes before closing?",

"Save Changes?", MessageBoxButton.YesNoCancel,

MessageBoxImage.Question);

if (result == MessageBoxResult.Cancel)

{

// Cancel the close.

e.Cancel = true;

}

else if (result == MessageBoxResult.Yes)

{

// Save the changes.

ContactsDataSetTableAdapters.ContactsTableAdapter

contactsDataSetContactsTableAdapter =

new ContactsDataSetTableAdapters.ContactsTableAdapter();

contactsDataSetContactsTableAdapter.Update(contactsDataSet);

// Make sure the save worked.

// If we still have unsaved changes, cancel.

e.Cancel = (contactsDataSet.HasChanges());

}

// Else the user doesn't want to save

// the changes so just keep going.

}

}

This code is similar to the version used by the Windows Forms application except it's more work getting the data set and table adapter.

5. [WPF] Repeat Exercise 1 for a WPF application. As in Exercise 4, after you create the database connection, run the program to let it build some data structures that it needs. Then drag the Contacts table fields onto the Window and align the Labels and TextBoxes.

Add the Window's Closing event handler as in Exercise 4 but don't worry about adding Previous, Next, Save, and other commands. You'll do that in later exercises.

Run the program and verify that you can see the first record in the data set and that you can save changes to it. (Hint: Don't forget to set the database's Copy to Output Directory property.)

6. [WPF, Hard] In Exercise 5, Visual Studio put the Label and TextBox for each database field inside a separate Grid control. Those Grids sit inside the main Grid control. That works, but it makes it hard to rearrange the controls. For example, each TextBox's width is explicitly set to 120.

To make the program more flexible, copy the program you built for Exercise 5 and give the main Grid control nine rows with heights Auto and two columns with widths Auto and *. Add the following property to the Grid:

DataContext="{StaticResource contactsViewSource}"

The DataContext property tells the controls inside the Grid where they should look for data.

Next give the main Grid a resource dictionary containing two Styles that set the properties for Labels and TextBoxes. Make the Styles set all of the property values shared by the automatically created controls except set the TextBox HorizontalAlignment property toStretch and omit the TextBox Width property.

Now when you run the program, the TextBoxes should resize to use the available width.

7. [WPF, Hard] Copy the program you wrote for Exercise 6 and add navigation buttons. To do that, make the Window's main control be a DockPanel. Dock a ToolBar to the top and dock the previous Grid control below that.

Give the ToolBar the buttons First, Previous, Next, Last, Add, Delete, and Save.

To make managing the data easier, use the following code to make class-level variables to hold the data set, the table adapter, and the view source:

private ContactsDataSet DataSet;

private ContactsDataSetTableAdapters.ContactsTableAdapter TableAdapter;

private CollectionViewSource ViewSource;

Modify the Window_Loaded event handler so it initializes and uses the class-level variables. Also modify the Window_Closing event handler so it uses the variables.

Next give the buttons the following code:

private void firstButton_Click(object sender, RoutedEventArgs e)

{

ViewSource.View.MoveCurrentToFirst();

}

private void previousButton_Click(object sender, RoutedEventArgs e)

{

ViewSource.View.MoveCurrentToPrevious();

}

private void nextButton_Click(object sender, RoutedEventArgs e)

{

ViewSource.View.MoveCurrentToNext();

}

private void lastButton_Click(object sender, RoutedEventArgs e)

{

ViewSource.View.MoveCurrentToLast();

}

private void addButton_Click(object sender, RoutedEventArgs e)

{

ContactsDataSet.ContactsRow row =

DataSet.Contacts.NewContactsRow();

row.FirstName = "<missing>";

row.LastName = "<missing>";

DataSet.Contacts.AddContactsRow(row);

ViewSource.View.MoveCurrentToLast();

}

private void deleteButton_Click(object sender, RoutedEventArgs e)

{

int rownum = ViewSource.View.CurrentPosition;

DataSet.Contacts.Rows[rownum].Delete();

}

private void saveButton_Click(object sender, RoutedEventArgs e)

{

TableAdapter.Update(DataSet);

}

8. [WPF] Copy the program you wrote for Exercise 7 and add a Label at the bottom of the form that displays the current record's number as in “Record 7 of 12.” Update the position when the program starts and when the ViewSource.View object receives aCurrentChanged event.

9. [WPF] The MoveCurrentToPrevious and MoveCurrentToNext methods can move the current record beyond the beginning or end of the data set. In that case, the bound TextBoxes are blank and the user is probably confused. Fortunately those methods return true if they successfully move to a new record and false if they fall off the data set.

Copy the program you wrote for Exercise 8 and modify the code to check the values returned by MoveCurrentToPrevious and MoveCurrentToNext. If they return false, move to the first or last record.

10.[WPF] Copy the program you wrote for Exercise 9 and make the ToolBar Buttons display appropriate images.

11.[WPF] Copy the program you wrote for Exercise 10 and enable and disable the ToolBar Buttons when appropriate. Hints:

· To enable a Button, set IsEnabled = true and Opacity = 1.

· To disable a Button, set IsEnabled = false and Opacity = 0.5.

· Make the existing CurrentChanged event handler enable and disable the movement Buttons as appropriate.

· Catch the DataSet.Contacts.RowChanged event and enable the Save Button when a record is modified by the user.

NOTE

Please select the videos for Lesson 34 online at www.wrox.com/go/csharp24hourtrainer2evideos.