Walkthrough: Data Binding Web Pages with a Visual Studio Data Component

Article
10/22/2014

Many Web applications are built by using multiple tiers, with one or more components in the middle tier that combine data access together with business logic. This walkthrough shows you how to build a data-access component in a Web site and bind a Web server control (a GridView control) to the data that is managed by the component. The data component interacts with a Microsoft SQL Server database, and can both read and write data.

Tasks illustrated in this walkthrough include the following:

Creating a component that can read and write data.
Referencing the data component as a data source on a Web page.
Binding a control to the data that is returned by the data component.
Reading and writing data using the data component.

Prerequisites

In order to complete this walkthrough, you will need the following:

Access to the SQL Server Northwind database. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

Note

If you need information about how to log on to the computer running SQL Server, contact the server administrator.
Microsoft Data Access Components (MDAC) version 2.7 or later.

If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you using Microsoft Windows 2000, you might to have to upgrade the MDAC version that is already installed on the computer. To download the current MDAC version, see the Data Access and Storage Developer Center.

Creating the Web Site

If you have already created a Web site in Visual Web Developer by completing Walkthrough: Creating a Basic Web Page in Visual Web Developer), you can use that Web site and go to the next section. Otherwise, create a new Web site and page by following these steps.

To create a file system Web site

Open Visual Web Developer.
On the File menu, click New Web Site.

The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
In the left-most Location list, click File System, and then in the right-most Location textbox, enter the name of the folder where you want to keep the pages of the Web site.

For example, type the folder name C:\WebSites\ComponentSample.
In the Language list, click the programming language that you prefer to work in.
Click OK.

Visual Web Developer creates the folder and a new page named Default.aspx.

Creating a Data-Access Component

In this walkthrough, you will use a wizard to generate a component that reads data from and writes data to the Northwind database. The component includes a schema file (.xsd) describing the data that you want and the methods that will be used to read and write data. You will not have to write any code. At run time, the .xsd file is compiled into an assembly that performs the tasks that you specify in the wizard.

To create a data-access component

If the Web site does not already have an App_Code folder, do the following:
1. In Solution Explorer, right-click the name of the Web site.
2. Point to Add Folder, and then click App_Code Folder.
Right-click the App_Code folder, and then click Add New Item.

The Add New Item dialog box appears.
Under Visual Studio installed templates, click DataSet.
In the Name box, type EmployeesObject, and then click Add.

The TableAdapter Configuration wizard appears.
Click New Connection.
If the Choose Data Source dialog box appears, click Microsoft SQL Server and then click Continue.
In the Server name box, enter the name of the computer that is running SQL Server.
For the logon credentials, select the option that is appropriate for accessing the SQL Server database (integrated security or specific ID and password) and if it is required, enter a user name and password.
- If you specify explicit credentials, select the Save my password check box.
Click Select or enter a database name, and then enter Northwind.
Click Test connection, and when you are sure that the connection works, click OK.

The TableAdapter Configuration wizard appears with the connection information filled in.
Click Next.

A page where you can choose to store the connection string in the configuration file appears.
Select the Yes, save this connection as check box, and then click Next.

You can leave the default connection string name.

A page where you can choose to use SQL statements or stored procedures appears.
Click Use SQL statements, and then click Next.

Using stored procedures has some advantages, including performance and security. However, for simplicity in this walkthrough, you will use an SQL statement.

A page where you can define the SQL statement appears.
Under What data should be loaded into the table, type the following SQL statement:
```
SELECT EmployeeID, LastName, FirstName, HireDate FROM Employees
```
Note

You can click Query Builder to use a builder tool, if you prefer.
Click Next.

A page where you can define the methods that the component will expose appears.
Click to clear the Fill a DataTable check box, and then select the Return a DataTable and Create methods to send updates directly to the database check boxes.

You do not need a method to fill a data table for this walkthrough. However, you will need a method that returns the data and you also want the component to contain methods that update the database.
In the Method Name box, type GetEmployees.

You are naming the method that will be used later to obtain data.
Click Finish.

The wizard configures the component and displays it in the component designer, displaying the data that the component manages and the methods that the component exposes.
Save the data component, and then close the component designer.
On the Build menu, click Build Web Site to make sure that the component compiles correctly.

Using the Data Component on a Page

You can now use the data component as a data source in an ASP.NET Web page. To access the data component, you will use an ObjectDataSource control, configuring it to call the data-access methods that are exposed by the data component. You can then add controls to the page and bind them to the data source control.

To add a data source control to the page

Open the Default.aspx page and switch to Design view.
From the Data group in the Toolbox, drag an ObjectDataSource control onto the page.
- If the ObjectDataSource Tasks shortcut menu does not appear, right-click the ObjectDataSource control, and then click Show Smart Tag.
On the ObjectDataSource Tasks shortcut menu, click Configure Data Source.

The Configure Data Source wizard appears.
In the Choose your business object list, click EmployeesObjectTableAdapters.EmployeesTableAdapter.

This is the type name (namespace and class name) of the component that you created in the preceding section.
Click Next.
On the Select tab, in the Choose a method list, click GetEmployees(), returns EmployeesDataTable.

The GetEmployees method is a method that was defined in the component that you created in the preceding section. It returns the results of the SQL statement, available in a DataTable object that data controls can bind to.
Click Finish.

You can now add data controls to the page and bind them to the ObjectDataSource control. In this walkthrough, you will work with the GridView control.

To add a GridView control to the page and bind it to the data

From the Data group in the Toolbox, drag a GridView control onto the page.
- If the GridView Tasks shortcut menu does not appear, right-click the GridView control, and then click Show Smart Tag.
On the GridView Tasks shortcut menu, in the Choose Data Source list, click ObjectDataSource1, which is the ID of the control that you configured in the preceding section.

The GridView control reappears with a column for each data column that is returned by the SQL statement.
In Properties, verify that the DataKeyNames is set to EmployeeID.

Testing the Page

Now that all controls that you need are on the page, you can test the page.

To test the page

Press CTRL+F5 to run the page.
Confirm that the EmployeeID, LastName, FirstName, and HireDate columns from the Employees table are displayed in the grid.
Close the browser.

The GridView control requests data from the ObjectDataSource control. In turn, the ObjectDataSource control creates an instance of the data component and calls GetEmployees method for the data component. The GetEmployees method returns a DataTable object, which the ObjectDataSource control returns to the GridView control.

Adding Updates to the Page

The data component that you created includes SQL statements to update the database (update, insert, and delete records). The update facilities of the data component are exposed by methods that were generated automatically when the wizard created the component. The GridView control and ObjectDataSource control can interact to automatically start the update methods.

Note

The GridView control does not support Insert.

To enable updates and deletes

Right-click the GridView control, and then click Show Smart Tag.
Select the Enable Editing check box.
Select the Enable Deleting check box.

Note

Enabling deletion lets you permanently remove records from the database. Do not enable deletion unless you are working with expendable test data.

Testing Updates

You can now test to make sure that you can use the component to update the database.

To test updates

Press CTRL+F5 to run the page.

This time, the GridView control displays Edit and Delete links in each row.
Click the Edit link that is next to a row.
Make a change to the row, and then click Update.

The grid is redisplayed with the updated date.
Click the Delete link that is in a row.

The row is permanently deleted from the database. The grid is redisplayed without that row.
Close the browser.

Next Steps

This walkthrough illustrates how to work with a data component. You might want to experiment with additional features of navigation. For example, you might want to do the following:

Create a custom data component instead of using the wizard.

For an example, see Walkthrough: Data Binding to a Custom Business Object.
Restrict which users can change the data. A typical method is to add membership and roles to the Web site, and then establish rules that the business component can check before allowing changes to data.
For detailed information, see Walkthrough: Creating a Web Site with Membership and User Login and Walkthrough: Managing Web Site Users with Roles.