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.
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:
For this example, select Microsoft Access Database File and click Continue to see the dialog shown in 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.
When you click Next again, you see the page shown in Figure 34.5.
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.
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.
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:
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.
The DataGridView
and the BindingNavigator
(which provides the buttons at the top) automatically let the user perform a lot of simple database tasks, including:
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.
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.
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 (TextBox
es 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 a TextBox
) 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.
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.
In this lesson, you:
Contacts.mdb
database from the book's website and place it in the project directory.FormClosing
event handler to check for unsaved changes.DataGridView
control so it fills the form.Copy to Output Directory
property to Copy if newer
.Contacts.mdb
database from the book's website and place it in the project directory.
FormClosing
event handler to check for unsaved changes.
TextBox
es so they widen if the form widens. Don't forget to add the FormClosing
event handler.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.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.
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.
Window
and align the Label
s and TextBox
es.
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.)
Label
and TextBox
for each database field inside a separate Grid
control. Those Grid
s 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 Style
s that set the properties for Label
s and TextBox
es. Make the Style
s set all of the property values shared by the automatically created controls except set the TextBox HorizontalAlignment
property to Stretch
and omit the TextBox Width
property.
Now when you run the program, the TextBox
es should resize to use the available width.
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);
}
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 a CurrentChanged
event.MoveCurrentToPrevious
and MoveCurrentToNext
methods can move the current record beyond the beginning or end of the data set. In that case, the bound TextBox
es 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.
ToolBar Button
s display appropriate images.ToolBar Button
s when appropriate. Hints:
Button
, set IsEnabled = true
and Opacity = 1
.Button
, set IsEnabled = false
and Opacity = 0.5
.CurrentChanged
event handler enable and disable the movement Button
s as appropriate.DataSet.Contacts.RowChanged
event and enable the Save Button
when a record is modified by the user.3.144.9.169