Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

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

Visual Studio .NET 2003

A common scenario is to display only selected data on a form – for example, the orders for a specified customer, or the books for a specific author. In this scenario, a user enters information into a form, and then a query is executed with the user's input as a criterion — that is, the data is selected based on a parameterized query. The query returns only the data that the user needs.

Using parameterized queries helps your application be efficient by letting the database do the work it is best at — namely, filtering and sorting records. In contrast, if you request an entire database table, transfer it over the network, and then use application logic to find the records you want, your application can become slow and unwieldy.

In this walkthrough, you will create a Windows Form that shows authors from the standard SQL Server Pubs database. Users will be able to enter a U.S. state code (such as CA for California) and fill the dataset with a list of the authors who live in that state.

Aa984467.vbwalkthroughparameterizedqueryscreen(en-us,VS.71).gif

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 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 controls to the form.
  • Adding code to refresh the dataset with new parameters.
  • Adding code to navigate between authors.

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 choose Project. The New Project dialog box is displayed.
  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_Parameters.
  6. When you have assigned a name, click OK to create the project.

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

Creating and Configuring a Dataset

As with most data-access scenarios in Visual Studio, 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, you will not do so directly, by manually adding 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 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.

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.
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.

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 object, which is optimized for working with SQL Server 7.0 or later. In this walkthrough, you use the OleDbDataAdapter object 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 Pubs database of your SQL server.
    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, state 
      FROM authors 
      WHERE (state = ?)
      

      The question mark (?) is the placeholder for the parameter. (If you are using the SqlDataAdapter class to access SQL Server version 7.0, parameters are specified using named variables.)

      Tip   For assistance building the SQL statement, click Query Builder to launch the Query Builder. To enter the parameter placeholder, enter the question mark into the Criteria column for the state field.

    The wizard creates a connection (OleDbConnection1 in Visual Basic, or oleDbConnection1 in Visual C# or Visual C++) and a data adapter (OleDbDataAdapter1 in Visual Basic, or oleDbDataAdapter1 in Visual C# or Visual C++).

  3. 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.

The data adapter actually contains four SQL statements — not only the Select statement that you created, but also additional statements to update, insert, and delete records. In this walkthrough, you will use only the Select statement.

The adapter's SQL Select statement is stored as part of the data adapter's SelectCommand property. The SelectCommand property contains a data command object (an object of type OledbCommand) that in turn contains not just the SQL statement, but other information required by that statement. Most notably, the command contains a Parameters collection. This collection contains a parameter object (of type OleDbParameter) for each parameter to be passed to and from the SQL statement. In this case, of course, there is only one parameter, namely the value for the state column. By default, the Data Adapter Configuration Wizard generates the parameter with the name of the column; the parameter is therefore called "state." Later in this walkthrough you will learn how to set the parameter value before executing the SQL statement.

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. Name the dataset dsAuthors, and check Add this dataset to the designer, 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. If you choose the Solution Explorer option to Show All Files, you will see that the schema file has a dependent .vb or .cs file containing the code that defines your new dataset class.

    Finally, Visual Studio adds an instance of the new dataset class (DsAuthors1 in Visual Basic, or dsAuthors1 in Visual C# or Visual C++) 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 Controls to Display the Data

For this walkthrough, the form will need a way for users to enter a state code, a way to execute the query, and a way to display author information.

To add controls to the form

  1. Add the following controls to the form, naming them as indicated:
    ControlPurposeNameText
    TextBoxAllows users to enter the state code for authors they want to seetxtStateParameter(Empty string)
    ButtonRuns the query and fills the datasetbtnShowShow
    TextBoxDisplays the author's IDtxtAuthorID(Empty string)
    TextBoxDisplays the author's last nametxtAuthorLName(Empty string)
    TextBoxDisplays the state the author lives in. (This is displayed in the walkthrough only for illustration.)txtAuthorState(Empty string)
  2. Add labels in front of the text boxes to indicate their function.

Adding Code to Populate the Dataset

When the form is displayed, the dataset is not automatically filled in with information from the database. Instead, you must explicitly fill the dataset. In this walkthrough, each time the users clicks the Show button, the query specified in the data adapter is re-executed and the results fed into the dataset. In this case, the query requires a parameter. The parameter value is the information that the user enters into the txtStateParameter text box.

You execute the query in code by calling the data adapter's Fill method.

To write code to populate the dataset

  • Double-click the Show button to create a method for the Click event. Add code to the handler to:
    • Set the value of the single parameter required by the SQL statement you created earlier. You will set it to whatever the user has entered into the txtStateParameter text box.
    • Call the dataset's Clear method. If you do not clear it, the records returned by a query are appended onto the dataset.
    • Call the data adapter's Fill method, passing it a reference to the dataset and the parameter value to include in the query.

    The following example shows what the code for the method looks like:

    ' Visual Basic
    Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
       ' The following line passes the value the user enter for state
       ' to the SQL Select statement.
       OleDbDataAdapter1.SelectCommand.Parameters("state").Value = txtStateParameter.Text
       DsAuthors1.Clear()
       OleDbDataAdapter1.Fill(DsAuthors1)
    End Sub
    
    // C#
    private void btnShow_Click(object sender, System.EventArgs e)
    {
       oleDbDataAdapter1.SelectCommand.Parameters["state"].Value = txtStateParameter.Text;
       dsAuthors1.Clear();
       oleDbDataAdapter1.Fill(dsAuthors1);
    }
    
    // C++
    private:
       System::Void btnShow_Click(System::Object *  sender,
          System::EventArgs *  e)
       {
          oleDbDataAdapter1->SelectCommand->Parameters->
             Item[S"state"]->Value = txtStateParameter->Text;
          dsAuthors1->Clear();
          oleDbDataAdapter1->Fill(dsAuthors1);
       }
    

Binding the Text Boxes to the Dataset

The three authors text boxes are intended to display information from a single record in the dataset's Authors data table. To make this happen automatically, you bind columns in the data table to the text boxes. The form's binding mechanism ensures that the text box controls are automatically updated each time you move to another record.

In the walkthrough, you will bind the text box controls' Text property to the column you want to display. In fact, you can bind any property of the text box control to any column in the dataset, and can bind multiple properties of a control. For more information, see Windows Forms Data Architecture.

To bind the text boxes to the dataset

  1. Return to the form designer.
  2. Select the first author text box and press F4 to open the Properties window.
  3. Expand the (DataBindings) node, and for the Text property, expand DsAuthors1, expand authors, and select au_id from the drop-down list.
  4. Repeat Steps 2 and 3 for the remaining two authors textboxes, binding them to DsAuthors1.authors.au_lname and DsAuthors1.authors.state, respectively.

At this point, the controls are in place to display data from each row in the dataset. However, you still need a way to move from one row to the next.

Adding Navigation Controls

Finally, you add navigation controls to the form. In this walkthrough, you'll add Previous and Next buttons. (You could also add First and Last buttons, but those are simply variations on Previous and Next.) You'll also add a text box that displays the current record position.

In a Windows Form, information about the current position and record count in an individual data table, as well as information about the bindings between the data table and controls, is maintained in a CurrencyManager object. There can be multiple data tables (all with different bindings and run-time positions and counts) on a form, each with its own CurrencyManager object. To manage the different possible CurrencyManager objects, the form includes a BindingContext object that provides a single interface for all the list managers.

The current record position in a data table is available in the Position property exposed via the BindingContext object. To navigate, you change the value of this property. To determine how many records are in a data table, you can query the BindingContext object's Count property.

To add navigation controls

  1. Add the following controls to the form, setting the properties as indicated:
    ControlNameText
    ButtonbtnNextNext
    ButtonbtnPreviousPrevious
  2. Create an event-handling method for the Previous button's Click event. Add code to decrement the BindingContext object's Position property. The code will look like this:
    ' Visual Basic
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
       Me.BindingContext(DsAuthors1, "authors").Position -= 1
    End Sub
    
    // C#
    private void btnPrevious_Click(object sender, System.EventArgs e)
    {
       this.BindingContext[dsAuthors1, "authors"].Position -=1 ;
    }
    
    // C++
    private:
      System::Void btnPrevious_Click(System::Object *  sender,
        System::EventArgs *  e)
      {
        this->BindingContext->get_Item(dsAuthors1, S"authors")->Position = 
          this->BindingContext->get_Item(dsAuthors1,
          S"authors")->Position - 1;
      }
    
  3. Do the same for the Next button, only increment the position. Use code such as the following:
    ' Visual Basic
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
       Me.BindingContext(DsAuthors1, "authors").Position += 1
    End Sub
    
    // C#
    private void btnNext_Click(object sender, System.EventArgs e)
    {
       this.BindingContext[dsAuthors1, "authors"].Position +=1 ;
    }
    
    // C++
    private:
      System::Void btnNext_Click(System::Object *  sender,
        System::EventArgs *  e)
      {
        this->BindingContext->get_Item(dsAuthors1, S"authors")->Position =
          this->BindingContext->get_Item(dsAuthors1,
          S"authors")->Position + 1;
      }
    

Displaying the Current Record Position

Finally, you can create a ShowPosition method to display the position of the current record.

To display the current record position

  1. Add a TextBox control to the form and name it txtPosition.
    Tip   Set the Enabled property to False so that users can see the current position but can't change the value of the control.
  2. Create a method in the form called ShowPosition. In the method, get the current position from the BindingContext object and display it in the text box. The following example shows what the code will look like:
    ' Visual Basic
    Private Sub ShowPosition()
       Dim iCnt As Integer
       Dim iPos As Integer
       iCnt = Me.BindingContext(DsAuthors1, "authors").Count
       If iCnt = 0 Then
          txtPosition.Text = "(No records)"
       Else
          iPos = Me.BindingContext(DsAuthors1, "authors").Position + 1
          txtPosition.Text = iPos.ToString & " of " & iCnt.ToString
       End If
    End Sub
    
    // C#
    private void ShowPosition()
    {
       int iCnt;
       int iPos;
       iCnt = this.BindingContext[dsAuthors1, "authors"].Count;
       iPos = this.BindingContext[dsAuthors1, "authors"].Position + 1;
       if(iCnt == 0)
       {
          txtPosition.Text = "(No records)";
       }
       else 
       {
          txtPosition.Text = iPos.ToString() + " of " + iCnt.ToString() ;
       }
    }
    
    // C++
    private:
       void ShowPosition()
       {
          int iCnt;
          int iPos;
          iCnt = this->BindingContext->get_Item(dsAuthors1,
             S"authors")->Count;
          iPos = this->BindingContext->get_Item(dsAuthors1,
             S"authors")->Position + 1;
          if(iCnt == 0)
          {
             txtPosition->Text = S"(No records)";
          }
          else
          {
             txtPosition->Text = String::Concat(iPos.ToString(),
                S" of ", iCnt.ToString());
          }
       }
    
  3. Add a call to the ShowPosition method at any point in the code where the current record position might change. For this walkthrough, add it in these places:
    • After calling the Fill method in the Click event-handling method for the Show button.
    • After changing the record position in the Click methods for the Previous and Next buttons. For example, the complete event-handling method for the Previous method will look like this:
      ' Visual Basic
      Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
         Me.BindingContext(DsAuthors1, "authors").Position -= 1
         ShowPosition()
      End Sub
      
      // C#
      private void btnPrevious_Click(object sender, System.EventArgs e)
      {
         this.BindingContext[dsAuthors1, "authors"].Position -=1 ;
         ShowPosition();
      }
      
      // C++
      private:
        System::Void btnPrevious_Click(System::Object *  sender,
          System::EventArgs *  e)
        {
          this->BindingContext->get_Item(dsAuthors1, S"authors")->Position
            = this->BindingContext->get_Item(dsAuthors1,
            S"authors")->Position - 1;
          ShowPosition();
        }
      

Testing

You can now test the form to make sure it displays data correctly based on the parameter you enter.

To test the form

  1. Press F5 to run the form.
  2. When the form is displayed, enter CA into the state textbox, and then click Show.

    The first author who lives in California is displayed.

  3. Click the Next and Previous buttons to navigate through the authors.
  4. Enter a new value into the state text box (for example, try UT for Utah), and then click Show.

    Confirm that a new author is displayed and the record count has changed.

Next Steps

When you have finished with this walkthrough, you will have a simple data-bound form. There are a number of improvements you could make, including:

  • Display state codes in a drop-down list box rather than having users enter them. One way would be to add another data adapter to the form with the SQL statement SELECT DISTINCT state FROM authors, generate a second dataset, and bind a list box to that dataset.
  • Check that records have been returned by the query. To do so, you might check the Count property of the BindingContext object; if it is zero, you can display a message. The following example shows an expanded version of the handler for the Show button that incorporates this check:
    ' Visual Basic
    Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
       OleDbDataAdapter1.SelectCommand.Parameters("state").Value = txtStateParameter.Text
       DsAuthors1.Clear()
       OleDbDataAdapter1.Fill(DsAuthors1)
       If Me.BindingContext(DsAuthors1, "authors").Count = 0 Then
          MessageBox.Show("No authors found in state " & txtStateParameter.Text)
          txtStateParameter.Focus()
       Else
          ShowPosition()
       End If
    End Sub
    
    // C#
    private void btnShow_Click(object sender, System.EventArgs e)
    {
       oleDbDataAdapter1.SelectCommand.Parameters["state"].Value = txtStateParameter.Text;
       dsAuthors1.Clear();
       oleDbDataAdapter1.Fill(dsAuthors1);
       if(this.BindingContext[dsAuthors1, "authors"].Count == 0)
       {
          MessageBox.Show("No authors found in " + txtStateParameter.Text);
          txtStateParameter.Focus();
       }
       else
       {
          ShowPosition();
       }
    }
    
    // C++
    private:
       System::Void btnShow_Click(System::Object *  sender,
          System::EventArgs *  e)
       {
          oleDbDataAdapter1->SelectCommand->Parameters->
             Item[S"state"]->Value = txtStateParameter->Text;
          dsAuthors1->Clear();
          oleDbDataAdapter1->Fill(dsAuthors1);
          if(this->BindingContext->get_Item(dsAuthors1,
             "authors")->Count == 0)
          {
             MessageBox::Show(String::Concat(S"No authors found in ",
                txtStateParameter->Text));
             txtStateParameter->Focus();
          }
          else
          {
             ShowPosition();
          }
       }
    
  • Display the authors in a data-bound grid rather than in individual controls. That way, you could see all the authors at once.
  • Add buttons that let the user add, edit and delete authors.

See Also

Introduction to Datasets | Introduction to Data Adapters | Windows Forms Data Architecture

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft