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: Creating Read-Only Data Access in a Web Forms Page

Visual Studio .NET 2003

In Web applications, it is very common for a page to simply display data — that is, for the data on the page to be read-only. Typical examples include catalog listings, search results, and so on. Users might act on the data by clicking a button to add an item to a shopping cart or clicking a link on the page, but those actions do not directly affect the data on the page.

In this kind of scenario, data access performance can be optimized in a couple of ways. One is that the Web Forms page does not need to create and populate a dataset. If the page is going to simply display the data directly, there is no particular need to cache it in a dataset. Because of this, a second optimization is that the data components of the page can be reduced to a minimum: because you do not need a dataset, you also do not need a data adapter to populate it. In fact, the only data access components you need are a connection and a data command containing a SQL statement or stored procedure that can be executed to fetch the data.

The only remaining piece is a special data component, the data reader, which can fetch records from the result set and pass them to another component. The data reader is specifically designed to be a read-only, forward-only reader, so it is optimized for extremely fast data access. The data reader returns a structure to which you can bind list controls (Repeater, DataList, DataGrid Web server controls).

This walkthrough illustrates how to create a Web Forms page with an ASP.NET DataGrid server control on it that uses a data reader to display read-only data. When you are through, you will have a page that looks like the following:

Aa728891.vbreadonlydataacces(en-us,VS.71).gif

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 the DataGrid control to display the data and setting a few of its properties.
  • Adding the few lines of code needed to fetch the data and display it in the control.

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 create read-only access to the data, you will need a connection to the database you want to read from. You will also need a data command. The data command includes a SQL statement you can execute to fetch the data. (The data command can reference a stored procedure instead, but in this walkthrough you will create a simple SQL SELECT statement.)

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 an OleDbConnection object onto the page.
    Note   You could also use the SqlConnection object, which is optimized for working with SQL Server 7.0 or later. In this walkthrough, you use the OleDbConnection because it is more generic, providing 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 Pubs database, choose <New Connection...>.

    The Data Link Properties dialog box is displayed.

  4. Create a connection pointing to your SQL Server and the Pubs database, and then click OK.
    Tip   For assistance in creating a connection, press F1 in the Data Link Properties dialog box.
    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.

That is all that you need to do for the connection. Now you need to add a command with a SQL statement to fetch the data.

To create the data command and SQL statement

  1. From the Data tab of the Toolbox, drag an OleDbCommand object onto the page.
    Note   You could also use the SqlCommand, which is optimized for working with SQL Server 7.0 or later. In this walkthrough, you use the OleDbCommand because it is more generic, providing ADO.NET access to any OLE DB-compatible data source.
  2. 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.)
  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 au_id, au_fname, au_lname, phone
    From authors
    
    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.
  5. When you have finished, close the Query Builder dialog box.

    The SQL statement is filled into the CommandText property.

You have finished adding data components to the page. You can now add a control to display the data.

Adding a Control to Display Data

In this walkthrough you will use an DataGrid Web server control to display information from the authors table. You could also use a DataList or Repeater control to achieve the same results. However, the DataGrid control is the easiest control to configure (for example, you do not need to create any templates for it), which will allow you to concentrate on the data aspects of the walkthrough.

To add and configure the control

  1. From the Web Forms tab of the Toolbox, drag a DataGrid control onto the page.
  2. At the bottom of the Properties window, choose the AutoFormat link and select a predefined format for the grid.

If you have worked with a data-bound DataGrid control before, you might expect to set the control's DataSource property. In this example, however, you will not set the data source here; instead, you will set it using code to point to the data reader.

You will also note that you are not explicitly setting the grid columns. In this example, data binding is established at run time for the grid, and the grid will dynamically create and display columns based on the data it is bound to.

Note   If you are familiar with the DataGrid control, you can pre-format columns for the four columns (ID, first name, last name, and phone). Because this is not a required step (the grid will automatically create columns at run time when it is bound), the walkthrough does not guide you through this procedure.

Adding Code to Fetch and Display the Data

To create optimized read-only data access, you will execute the SQL statement you created with the data command. The command returns a data reader that you can then bind to. You can accomplish all of this with a few lines of code.

To add code to fetch data

  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. Create an instance variable of type OleDbDataReader.
    2. Open the connection.
    3. Call the data command's ExecuteReader method, which executes the SQL statement in the data command and returns its result set into the data reader object.
    4. Set the DataGrid control's data source to point to the data reader. You could not do this earlier in the Properties window because the data reader did not exist then.
    5. Bind the DataGrid control to the data reader.
    6. Close the data reader.
    7. 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 code required to perform the above steps.

      Note   For clarity, the code explicitly creates a variable to hold the data reader.
      ' Visual Basic
      Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         If Not IsPostBack Then
            oleDbConnection1.Open()
            Dim Reader As System.Data.OleDb.OleDbDataReader
            Reader = oleDbCommand1.ExecuteReader()
            DataGrid1.DataSource = Reader
            DataGrid1.DataBind()
            Reader.Close()
            oleDbConnection1.Close()
         End If
      End Sub
      
      // C#
      private void Page_Load(object sender, System.EventArgs e)
      {
         if (!IsPostBack) {
            oleDbConnection1.Open();
            System.Data.OleDb.OleDbDataReader Reader;
            Reader = oleDbCommand1.ExecuteReader();
            DataGrid1.DataSource = Reader;
            DataGrid1.DataBind();
            Reader.Close();
            oleDbConnection1.Close();
         }
      }
      

Testing

After adding the code, test data access in the Web Forms page.

To test the Web Forms page

  1. Save the page.
  2. In Solution Explorer, right-click the page and choose View in Browser.

    Confirm that a list of authors is displayed in the grid.

Next Steps

This walkthrough has illustrated the basic steps involved in using a data reader to display read-only data in the page. Some enhancements you could make to the form in this walkthrough include:

  • Format columns in the grid to display the information that will be available at run time.
  • Format the grid by changing its color, font, and so on.
  • Display data in a different control, such as a DataList or Repeater control. The process is largely the same. One difference, however, is that you need to create templates, drag controls into the templates, and bind the controls. Because the data binding of the container control is done at run time, you have to create custom binding expressions for the controls in the templates. A typical custom binding expression looks like this:
    DataBinder.Eval(Container, "DataItem.au_lname")
    

    The expression would vary only in the last part (au_lname) for each control in the template.

    For information about templates, see Web Server Controls Templates.

  • Use a parameterized query to fetch data, which is a very common real-world scenario. In that case, you would create a SQL statement that accepts parameters, and then pass those parameters by filling in the Parameters collection of a data command before executing it. For details, see Setting and Getting Data Command Parameters.
  • Add paging. This is a variation of a parameterized query. You need to maintain in the page some indication of what records you have displayed. When the user navigates to the next page, you need to pass parameter information with the query to get the next page worth of data. For details on creating paged output, see Paging Behavior in DataGrid Web Server Controls.
  • Learn about the security issues associated with Web Forms. For details, see Overview of Web Application Security Threats.

See Also

Introduction to DataCommand Objects in Visual Studio | Introduction to the DataGrid Web Server Control | OleDbDataReader Class | SqlDataReader Class

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