Export (0) Print
Expand All

Walkthrough: Updating Data Using a Database Update Query in Web Forms

Visual Studio .NET 2003

The Web Forms data-binding architecture allows you to bind control properties to a data source, making it easy to display data on the page. However, the data-binding architecture does not support updates — that is, data binding is one-way in Web Forms pages. Because a majority of Web applications involve reading data and not updating it, one-way data binding keeps the size and complexity of the page architecture to a minimum for the most common scenarios.

However, there are times when you do want to update data. There are several ways to do so. If you are using a dataset, you can write changes to the dataset and then call the data adapter to send changes from the dataset to the database. (For an example of this scenario, see Walkthrough: Using a DataGrid Web Control to Read and Write Data.) Alternatively, you can update the database directly, using SQL statements or stored procedures, which is what this walkthrough illustrates.

Note   For details about choosing between these approaches, see Web Data Access Strategy Recommendations.

This walkthrough illustrates how to create a Web Forms page that reads and writes data to a database using SQL statements. For the walkthrough, you will work with the Categories table of the SQL Server Northwind database. In the page, you can select a record, edit it, and then save it to the database. When you are done, your page will look something like the following:

Aa728894.vbwlkwebformupdatingwithquery(en-us,VS.71).gif

The walkthrough will illustrate a number of concepts:

  • Creating command objects that incorporate SQL statements
  • Using a SQL statement and a data reader to fetch data from the database
  • Creating parameterized queries
  • Setting query parameters and executing queries

In order to complete this walkthrough, you will need:

The walkthrough is split into a number of smaller pieces:

  • Creating the Web Forms page.
  • Adding the necessary data components.
  • Adding controls to display the data.
  • Adding code to read and write data.

Creating the Project and Form

The first step is to create a Web application and a Web Forms page.

To create the project and form

  1. On the File menu, point to New, then click Project.
  2. In the New Project dialog box, do the following:
    1. In the Project Types pane, choose either Visual Basic Projects or Visual C# Projects.
    2. In the Templates pane, choose ASP.NET Web Application.
    3. In the Location box, enter the complete URL for your application, including http://, the name of the server, and the name of your project. The Web server must have IIS version 5 or later and the .NET Framework installed on it. If you have IIS on your computer, you can specify http://localhost for the server. (If you normally use a proxy server to access the Internet, you might need to configure Internet Explorer to bypass the proxy server in order to use localhost.)

      When you click OK, a new Web Forms project is created at the root of the Web server you specified. In addition, a new Web Forms page called WebForm1.aspx is displayed in the Web Forms Designer in Design view.

      Tip   If you have trouble creating a Web application project, see Web Access Failed Dialog Box.

Adding the Data Components

To access the data, you will need a connection to the database you want to read from. You will also need several command objects — two to read data from the database using SQL Select statements, and a third to update the database with a SQL Update statement. Two of these commands will require parameters, so that you can pass run-time values to the statements before executing them. (The data commands can reference a stored procedure instead of SQL statements, but in this walkthrough you will use statements.)

Creating and Configuring the Connection

To begin, add a connection that points to the database you want to read from.

To create the data connection

  1. From the Data tab of the Toolbox, drag a SqlConnection object onto the page.
    Note   Because you are using SQL Server, you use the SqlConnection object, which is optimized for working with SQL Server 7.0 or later. If you are using a different database, you use the OleDbConnection, which provides ADO.NET access to any OLE DB-compatible data source.
  2. Select the connection and in the Properties window, click the button in the ConnectionString property box.

    The list displays any existing connections and the <New Connection...> option.

  3. Unless you already have a connection to the SQL Server Northwind database, choose <New Connection...>.

    The Data Link Properties dialog box is displayed.

  4. Create a connection pointing to your SQL Server and the Northwind database, and then click OK.
    Note   You need appropriate read/write permissions on the SQL Server you are using. If SQL Server is on the same computer as IIS, it is recommended that you specify Windows integrated security when creating the connection. Otherwise, you can specify a user name and password and save that information with the connection, but doing so can compromise security. For more information, see Accessing SQL Server from a Web Application.

    The connection information you specified is saved as the connection string for your connection.

Now you need to add commands. You will need three in all, each with a different query:

  • One to get all the category ID values.
  • One to get a selected category based on an ID that you provide.
  • One to update a category with new values.

To create the data command for getting all category ids

  1. From the Data tab of the Toolbox, drag an SqlCommand object onto the page.
  2. Rename the command cmdCategoriesAll.
  3. Select the command and in the Properties window, click the button in the Connection property, and select the connection you made earlier. (You will need to open the Existing node.)
  4. In the CommandText property, click the ellipsis button to open the Query Builder dialog box.
  5. Use the Query Builder dialog box to construct the following SQL statement:
    SELECT CategoryID, CategoryName, Description FROM Categories
    
  6. When you have finished, close the Query Builder dialog box.

    The SQL statement is filled into the CommandText property.

The remaining commands are slightly more complex, in that they require parameters.

To create the data command for getting a single category record

  1. Drag a second SqlCommand object onto the page and set its Connection property to SqlConnection1.
  2. Name the command cmdCategoriesById.
  3. In the CommandText property, click the ellipsis button to open the Query Builder dialog box.
  4. Use the Query Builder dialog box to construct the following SQL statement:
    SELECT CategoryID, CategoryName, Description 
    FROM Categories 
    WHERE (CategoryID = @categoryid)
    
    Note   The variable @categoryid is a "named variable." The exact name of the variable you use in the Where clause is not important, but you will need to know it later in the walkthrough. The "@" prefix is required for SQL Server named parameters.

The final command includes an Update statement for sending changes to the database.

To create the data command for updating a category record

  1. Drag a third SqlCommand object onto the page and set its Connection property to SqlConnection1.
  2. Name the command cmdCategoriesUpdate.
  3. Set the command's CommandText property to the following statement:
    UPDATE Categories 
    SET CategoryName = @categoryname, Description = @categorydescription WHERE (CategoryID = @categoryid)
    

You have finished adding data components to the page.

Adding Controls

In the page, you will need a drop-down list to allow users to select a category ID, some text boxes to display and edit data with, and a button to save changes.

To add controls to the form

  1. From the Web Forms tab of the Toolbox, add the following controls to the form and set the specified properties as indicated:
    ControlPurposeProperties
    DropDownListAllows user to select a category list that you will provide at run time.ID: ddlCategoryID

    AutoPostBack: true

    TextBoxAllows users to edit the category name.ID: txtCategoryName
    TextBoxAllows users to edit the category description.ID: txtCategoryDescription
    ButtonUpdates the database with changes.ID: btnSave

    Text: Save

  2. Add labels in front of the controls to indicate their function.
    Tip   If you are working in grid layout mode, you can add literal text using the Flow Layout Panel (HTML Div Controls) from the HTML tab of the Toolbox.

Adding Code to Display and Update the Data

To finish the page, you will need to add code to accomplish three tasks:

  • When the page is initialized, you need to populate the drop-down list with the category IDs. Since the first category ID will be automatically selected, you can also display the first category name and description in the text boxes.
  • When users select a category ID from the drop-down list, you need to determine which ID they selected. You then use that value to execute the SQL statement that brings back a single record.
  • When users click the Save button, you need to get their changes and use them to execute an Update statement with the new values.

Populating the Drop-Down List at Page Initialization

To populate the drop-down list, you will execute a query that returns all the records in the Categories table. You only need to do this the first time the page runs; thereafter, the values in the list are preserved in the control's view state.

After executing the query, you will use a data reader (SqlDataReader object) to fetch the records. The data reader is specifically designed to be a read-only, forward-only reader, so it is optimized for extremely fast data access.

To add code to populate the drop-down list

  1. Double-click the Web Forms page to open the Code Editor and find the Page_Load event handler.
  2. Create code to perform the following functions:
    1. Test the page's IsPostBack property to determine if this is the first time the page is running. If so, perform the rest of these steps.
    2. Create an instance variable of type SqlDataReader.
    3. Open the connection.
    4. Call the ExecuteReader method of the cmdCategoriesAll object, which executes the SQL statement in the data command and returns its result set to the data reader object.
    5. Provide a way, such as a Boolean flag, to know when you are fetching the first record, because you want to display the information from the record only.
    6. In a loop, use the data reader to fetch each successive record.
    7. For each record, create a new item in the drop-down list and assign the category ID to it.
    8. For the first record only, get the category name and description and set them to the Text property of the text boxes.
    9. Close the data reader.
    10. Close the connection. This is an important step; the connection remains open while the data is being fetched from the database. Therefore, when you no longer need the connection, you want to close it and free the resources it uses.

    The following example shows the complete Page_Load method with the code required to perform the above steps:

    ' Visual Basic
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       If Not IsPostBack Then
          ' Executes only the first time the page is processed. After
          ' that, the list is already in the drop-down list and is
          ' preserved in view state.
          Dim dreader As SqlClient.SqlDataReader
          SqlConnection1.Open()
          dreader = cmdCategoriesAll.ExecuteReader()
          Dim firstrow As Boolean = True
          While dreader.Read()
             ddlCategoryID.Items.Add(New ListItem(dreader(0).ToString()))
             If firstrow Then
                txtCategoryName.Text = dreader(1).ToString()
                txtCategoryDescription.Text = dreader(2).ToString()
                firstrow = False
             End If
          End While
          dreader.Close()
          SqlConnection1.Close()
       End If
    End Sub
    
    // C#
    private void Page_Load(object sender, System.EventArgs e)
    {
       if (!IsPostBack)
       {
          // Executes only the first time the page is processed. After
          // that, the list is already in the drop-down list and is
          // preserved in view state.
          System.Data.SqlClient.SqlDataReader dreader;
          sqlConnection1.Open();
          dreader = cmdCategoriesAll.ExecuteReader();
          bool firstrow = true;
          while (dreader.Read())
          {
             ddlCategoryID.Items.Add(new ListItem(dreader[0].ToString()));
             if (firstrow)
             {
                txtCategoryName.Text = dreader[1].ToString();
                txtCategoryDescription.Text = dreader[2].ToString();
                firstrow = false;
             }
          }
          dreader.Close();
          sqlConnection1.Close();
       }
    } 
    

Displaying a Record When Users Select a Category ID

The drop-down list displays category IDs. When the user selects one — that is, when the control's SelectedIndexChanged event is raised — you want to display the corresponding record.

When you created the drop-down list, you set its AutoPostBack property to true. The result is that the page is posted to the server as soon as the user makes a selection.

To get a category record, you will execute a SQL statement that uses a Where clause to find only the category whose ID the user selected. To pass this value to the statement, you set the value of a parameter (a SqlParameter object) that is part of the data command.

To display data for the selected category

  1. Double-click the drop-down list to open the Code Editor and create the SelectedIndexChanged event handler.
  2. Create code to perform the following functions:
    1. Get the text of the user's selection from the drop-down list.
    2. Set the parameter (@categoryid) of the cmdCategoriesById command to the selected ID.
    3. Open the connection.
    4. Create a data reader instance variable and call the ExecuteReader method of the cmdCategoriesById object to execute the command. In this case, you will pass a flag to the method indicating that you want to return only a single record.
    5. Use the data reader to fetch the returned record and then display its values in the text boxes.
    6. Close the data reader.
    7. Close the connection.

    The following example shows the complete SelectedIndexChanged method with the code required to perform the above steps:

    ' Visual Basic
    Private Sub ddlCategoryID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlCategoryID.SelectedIndexChanged
       Dim categoryid As String
       categoryid = ddlCategoryID.SelectedItem.Text
       cmdCategoriesById.Parameters("@categoryid").Value = categoryid
       SqlConnection1.Open()
       Dim dreader As SqlClient.SqlDataReader
       dreader = cmdCategoriesById.ExecuteReader(CommandBehavior.SingleRow)
       If dreader.Read() Then
          txtCategoryName.Text = dreader(1)
          txtCategoryDescription.Text = dreader(2)
       End If
       dreader.Close()
       SqlConnection1.Close()
    End Sub
    
    // C#
    private void ddlCategoryID_SelectedIndexChanged(object sender, System.EventArgs e)
    {
       string categoryid;
       categoryid = ddlCategoryID.SelectedItem.Text;
       cmdCategoriesById.Parameters["@categoryid"].Value = categoryid;
       sqlConnection1.Open();
       System.Data.SqlClient.SqlDataReader dreader;
       dreader = cmdCategoriesById.ExecuteReader(CommandBehavior.SingleRow);
       if (dreader.Read())
       {
          txtCategoryName.Text = dreader[1].ToString();
          txtCategoryDescription.Text = dreader[2].ToString();
       }
       dreader.Close();
       sqlConnection1.Close();
    }
    

Updating a Record

Finally, you want to add code that will update the database with changes from the record displayed in the page. As with fetching a single record, the update requires that you set some parameters and then execute a statement (in this case, an Update statement). However, because the Update statement does not return any records, the code for executing the statement is relatively simple.

Security Note   User input in a Web Forms page can include potentially malicious client script. By default, the Web Forms page validates that user input does not include script or HTML elements. For more information, see Scripting Exploits and Protecting Against Script Exploits in a Web Application.

To update records

  1. Double-click the Save button to create a Click handler.
  2. Create code to perform the following functions:
    1. Set parameters (three in all) to the values of the controls in the page.
    2. Open the connection.
    3. Call the ExecuteNonQuery method of the cmdCategoriesUpdate command.
    4. Close the connection.

    The following example shows the complete Click method with the code required to perform the above steps:

    ' Visual Basic
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
       With cmdCategoriesUpdate
          .Parameters("@categoryid").Value = ddlCategoryID.SelectedItem.Text
          .Parameters("@categoryname").Value = txtCategoryName.Text
          .Parameters("@categorydescription").Value = txtCategoryDescription.Text
       End With
       SqlConnection1.Open()
       cmdCategoriesUpdate.ExecuteNonQuery()
       SqlConnection1.Close()
    End Sub
    
    // C#
    private void btnSave_Click(object sender, System.EventArgs e)
    {
       cmdCategoriesUpdate.Parameters["@categoryid"].Value = ddlCategoryID.SelectedItem.Text;
       cmdCategoriesUpdate.Parameters["@categoryname"].Value = txtCategoryName.Text;
       cmdCategoriesUpdate.Parameters["@categorydescription"].Value = txtCategoryDescription.Text;
       sqlConnection1.Open();
       cmdCategoriesUpdate.ExecuteNonQuery();
       sqlConnection1.Close();
    }
    

Testing

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

To test the page

  1. In Solution Explorer, right-click the page and choose View in Browser.
  2. When the page is displayed, make sure that the drop-down list contains a list of category IDs and that the first category is displayed in the text boxes.
  3. Select a different category from the list.

    The new record will be displayed.

    Note   If selecting a new category ID in the drop-down list does not change the record, make sure that the control's AutoPostBack property is set to true.
  4. Make a change in one of the text boxes, and then click the Save button.
  5. Move to a different category record, and then back to the one you changed.

    The changes you made are visible when you redisplay the record.

Next Steps

This walkthrough has illustrated the basic steps involved in using a data reader to fetch records directly from the database and using a data command to make updates. To enhance the Web Forms page you have created, you might want to explore some of the following ideas:

  • Provide an alternative way for users to select categories. In this walkthrough, the drop-down list displayed category IDs, which ordinarily are not very useful to users. This was done to allow users to edit the name, if desired, and to maintain clarity for how information from database records was moving into and out of controls.

    A relatively easy change would be to display category names as the text of the drop-down list (the information that is visible in the list) and category IDs as the value of the list. (However, this would not allow users to edit the name.) You could then pass the value as a parameter value instead of passing the text.

  • Add error checking. Typically, you want to make your code robust by enclosing all data-access statements in try-catch blocks. Data access is subject to many possible problems (many of them out of the control of your application, such as network problems). For details, see Best Practices for Throwing Exceptions from Components.
  • Add concurrency control. In this walkthrough, you simply update a record in the database without determining first whether another user might have also made changes. For details, see Concurrency Control in ADO.NET.
  • Learn about the security issues associated with Web Forms. For details, see Overview of Web Application Security Threats.

See Also

Walkthrough: Creating Read-Only Data Access in a Web Forms Page | Introduction to ADO.NET Connection Design Tools | Introduction to DataCommand Objects in Visual Studio | Executing a Data Command That Returns a Result Set | Executing Updates or Database Commands using a Data Command | Setting and Getting Data Command Parameters | Concurrency Control in ADO.NET

Show:
© 2014 Microsoft