Export (0) Print
Expand All

Walkthrough: Simple Data Access in a Windows Form

Visual Studio .NET 2003

One of the most common scenarios in application development is to display data on a form. This walkthrough illustrates a simple Windows Form that displays data from a single table in a data grid. The grid is editable, and you will be able to make changes to data and update the database. Although the result is not complex, the walkthrough illustrates many of the basic procedures you will use when accessing data with forms.

In order to complete this walkthrough, you will need:

  • Access to a server with the Pubs SQL Server sample database.

The walkthrough is split into a number of smaller pieces:

  • Creating the Windows Form.
  • Creating and configuring the dataset you will bind the form against. This includes creating a query that populates the dataset from the database.
  • Adding the DataGrid control to the form and binding it to data.
  • Add code to fill the dataset.
  • Add code that sends dataset changes back to the database.

When you have finished the walkthrough, you will have a form that looks similar to the following.

Aa984466.vbwalkthroughsimplewinformdataaccess(en-us,VS.71).gif

Creating the Project and Form

The first step is to create a Windows Form.

To create the project and form

  1. If you already have a solution open, select Close Solution from the File menu.
    Note   In production projects, you will very often have multiple projects in the same solution. In this walkthrough, however, you will close any open solution and create a new one along with the project so that there is no interference between what you will do here and any existing forms, datasets, and so on.
  2. From the File menu, point to New, and then click Project.
  3. In the Project Types pane, choose Visual Basic Projects, Visual C# Projects, or Visual C++ Projects.
  4. In the Templates pane, choose Windows Application for Visual Basic and Visual C# projects, or choose Windows Forms Application (.NET) for Visual C++ projects.
  5. Assign a name to the project that will be unique and will conform to the naming conventions you use. For example, you might name this project Walkthrough_Simple1.
  6. When you have assigned a name and specified a new solution, click OK.

    Visual Studio creates a new project and displays a new form in the Windows Form Designer.

Creating and Configuring a Dataset

As with most data-access scenarios in Windows Forms applications, you'll be working with a dataset. A dataset is a container — a cache — that holds the records you are interested in working with.

Note   Using a dataset is only one option for data access, and is not the optimal choice in some scenarios. Nonetheless, datasets are usually the right choice in Windows Forms applications, and you will use one in this walkthrough. For more information, see Recommendations for Data Access Strategies.

In this walkthrough, you will add a dataset to the form. However, because the dataset does not already exist, you will not manually add it to the form. Instead, you will perform the following set of steps:

  • Create a data adapter using a wizard. The adapter contains SQL statements used to read and write database information. The wizard helps you define the SQL statements you need. If necessary, the wizard also creates a connection to the database.
  • Generate the dataset schema. In this process, you will have Visual Studio create a new typed dataset class based on the tables and columns you are accessing. When you generate the dataset class, you will also add an instance of it to the form.

It is important that you follow all the procedures in this section. Otherwise your form will not have the dataset that you will be using in subsequent parts of the walkthrough.

For more information on data adapters, see Introduction to Data Adapters. For more information about datasets, see Introduction to Datasets.

Configuring a Data Connection and Data Adapter

To begin, you create a data adapter that contains the SQL statement used to populate the dataset later. As part of this process, you define a connection to access a database. You configure the data adapter using a wizard, which makes it easy to create the SQL statements you need for data access.

Note   When the wizard is done, you must continue to the next section in order to generate a dataset and complete the data access portion of your form.
Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.

To create the data connection and data adapter

  1. From the Data tab of the Toolbox, drag an OleDbDataAdapter object onto the form.
    Note   You could also use the SqlDataAdapter, which is optimized for working with SQL Server 7.0 or later. In this walkthrough, you use the OleDbDataAdapter because it is more generic, providing ADO.NET access to any OLE DB-compatible data source.

    The Data Adapter Configuration Wizard starts, which will help you create both the connection and the adapter.

  2. In the wizard, do the following:
    1. In the second pane, create or choose a connection pointing to the SQL Server Pubs database.
    2. In the third pane, specify that you want to use an SQL statement to access the database.
    3. In the fourth pane, create the following SQL statement:
      SELECT au_id, au_lname, au_fname, city, state, phone, contract
      FROM authors
      

      For assistance building the SQL statement, click Query Builder to launch the Query Builder.

      Note   In this walkthrough, you will populate the dataset with all the rows from the authors table. In production applications, you typically optimize data access by creating a query that returns only the columns and rows you need. For an example, see Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.
  3. Click Finish to complete the wizard.

    When the wizard is complete, you will have a connection (OleDbConnection1 in Visual Basic, or oleDbConnection1 in Visual C# or Visual C++) containing information about how to access your database. You will also have a data adapter (OleDbDataAdapter1 in Visual Basic, or oleDbDataAdapter1 in Visual C# or Visual C++) that contains a query defining which table and columns in the database you want to access.

After the wizard is complete, generate the dataset based on the SQL query that you created during this procedure. For more information, see the next section.

Creating the Dataset

After you have established the means to connect to the database and specified the information you want (via the SQL command in the data adapter), you can have Visual Studio create a dataset. Visual Studio can generate the dataset automatically based on the query you specified for the data adapter. The dataset is an instance of the DataSet class based on a corresponding XML Schema (.xsd file) that describes the class's elements (table, columns, and constraints). For more information about the relationship between datasets and schemas, see Introduction to Data Access with ADO.NET.

To generate a dataset

  1. From the Data menu, choose Generate DataSet.
    Tip   If you do not see the Data menu, click in the form; the form must have focus for the menu to appear.

    The Generate Dataset dialog box is displayed.

  2. Select the New option and name the dataset dsAuthors.

    In the list under Choose which table(s) to add to the dataset, the authors table should be selected.

  3. Make sure Add this dataset to the designer is checked, and then click OK.

    Visual Studio generates a typed dataset class (dsAuthors) and a schema that defines the dataset. You will see the new schema (dsAuthors.xsd) in Solution Explorer.

    Tip   In Solution Explorer, click Show All Files to see that the schema file's dependent .vb or .cs file, which contains the code the defines your new dataset class.

    Finally, Visual Studio adds an instance of the new dataset class (DsAuthors1 or dsAuthors1) to the form.

At this point you have set up everything you need in order to get information out of the database and into a dataset. You are ready to create a form that will display the data.

Adding a DataGrid Control to Display the Data

In this walkthrough, you will add a single control — a DataGrid control — that can display all the records from the dataset at the same time. An alternative would be to use individual controls such as text boxes to display one record at a time. That strategy then requires you to add navigation to the form. For simplicity, therefore, you will use a data grid.

Note   For an example of how to use individual text boxes to display records from a dataset, see Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.

The data grid must be bound to the dataset in order to display the data.

To add a bound DataGrid control to the form

  1. If you have not already done so, switch to the form designer by clicking the tab at the top of the current window.
  2. From the Windows Forms tab of the Toolbox, drag a DataGrid control onto the form.
  3. Press F4 to display the Properties window.
  4. In the DataSource property, select DsAuthors1 (or dsAuthors1) as the data source. Do not choose DsAuthors1.Authors (or dsAuthors1.Authors).
  5. In the DataMember property, select authors.

    Setting these two properties binds the authors data table in the DsAuthors1 dataset to the grid.

  6. Resize the grid so you can see all the columns. Change its height so you will be able to see several author records.

Populating the DataGrid Control

Although the data grid is bound to the dataset you created, the dataset itself is not automatically filled in. Instead, you must fill the dataset yourself by calling a data adapter method. For more information about filling datasets, see Introduction to Datasets.

To populate the DataGrid control

  1. From the Windows Forms tab of the Toolbox, drag a Button control onto the form.
  2. Name the button btnLoad and change the caption by setting its Text property to Load.
  3. Double-click the button to create an event-handling method for its Click event.
  4. In the method, clear the dataset you created, and then call the data adapter's Fill method, passing it the dataset you want to fill.

    The following example shows what the complete method will look like:

    ' Visual Basic
    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
       DsAuthors1.Clear()
       OleDbDataAdapter1.Fill(DsAuthors1)
    End Sub
    
    // C#
    private void btnLoad_Click(object sender, System.EventArgs e)
    {
       dsAuthors1.Clear();
       oleDbDataAdapter1.Fill(dsAuthors1);
    }
    
    // C++
    private:
       System::Void btnLoad_Click(System::Object *  sender,
          System::EventArgs *  e)
          {
             dsAuthors1::Clear();
             oleDbDataAdapter1::Fill(dsAuthors1);
          }
    

Updating the Database

When users make a change in the grid, the control automatically saves the updated record in the dataset. In Windows Forms, the data-binding architecture writes the values of data-bound controls to the data rows they are bound to.

Note   In Web Form pages, data binding works somewhat differently. For a step-by-step example of data binding in Web Forms pages, see Walkthrough: Displaying Data in a Web Forms Page and Walkthrough: Updating Data Using a Database Update Query in Web Forms.

However, when you work with a dataset, updates require two stages. After the data is in the dataset, you still have to send it from the dataset to the database. The data adapter can do this with its Update method, which examines every record in the specified data table in the dataset and, if a record has changed, sends the appropriate Update, Insert, or Delete command to the database. For more information, see Introduction to Dataset Updates.

In this walkthrough, you will add a button to the form that users can press when they want to send their updates to the database.

To update the database

  1. From the Windows Forms tab of the Toolbox, drag a Button control onto the form.
  2. Name the button btnUpdate and change the caption by setting its Text property to Save Changes in Database.
  3. Double-click the button to create an event-handling method for its Click event.
  4. In the method, call the data adapter's Update method, passing it the dataset containing the updates you want to send to the database. Use the MessageBox object to display confirmation text.

    The following example shows what the complete method will look like:

    ' Visual Basic
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
       OleDbDataAdapter1.Update(DsAuthors1)
       MessageBox.Show("Database updated!")
    End Sub
    
    // C#
    private void btnUpdate_Click(object sender, System.EventArgs e)
    {
       oleDbDataAdapter1.Update(dsAuthors1);
       MessageBox.Show("Database updated!");
    }
    
    // C++
    private:
       System::Void btnUpdate_Click(System::Object *  sender,
          System::EventArgs *  e)
          {
             oleDbDataAdapter1::Update(dsAuthors1);
             MessageBox::Show("Database updated!");
          }
    

Testing

You can now test the form to make sure it displays authors data in the grid and that users can make updates.

To test the form

  1. Press F5 to run the form.
    Note   It is not uncommon to get an exception of type System.Data.SqlClient.SqlException at this point, most likely due to the way your database login credentials were saved. For information on remedying this issue, see Cannot Access a Data Base at Run Time that I Can Access at Design Time.
  2. When the form is displayed, click the Load button.

    A list of authors is displayed in the grid.

  3. Make a change in a record in the grid.

    When you move to another record in the grid, the change is preserved. Make a mental note of your change.

  4. Click the Load button again.

    This reloads the dataset from the database and refreshes the grid. Notice that the change you made in Step 3 was not preserved — because you did not save changes from the dataset to the database.

  5. Make a change in a record in the grid again.
  6. Click the Save Changes in Database button.

    You will see the message box displayed, but there is no change in the grid.

  7. Click the Load button once more to re-load data from the database.

    This time, the change you made in Step 5 is preserved, because the data was saved in the database.

Next Steps

This walkthrough has illustrated the basic steps involved in displaying data in a form. Some enhancements you could make to the form in this walkthrough include:

  • Format the grid by changing its color, font, and so on.
  • Make the grid display data without being explicitly loaded by the user. To do this, you add a Fill method to the form's New method (in Visual Basic) or constructor (in C#) and remove the Load button.
  • Display only selected information in the grid. In many instances, you will base the display on information that the user supplies at run time (for example, you might display authors only from a particular city). To do this, you create a parameterized query. For more information, see Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.
  • Separate data access from the user interface. In this walkthrough, you have created a form that accesses the data more-or-less directly (via the dataset). A more flexible and maintainable design is to create a data-access component that handles data access. The form (that is, the user interface) could then interact with the component as needed. The same component could be used by multiple forms (and by other components), which eliminates the overhead and redundancy of redesigning data access for every form you create. For more information on creating component-based data access, see Walkthrough: Creating a Distributed Application.

See Also

Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query

Show:
© 2014 Microsoft