Walkthrough: Basic Data Access in Web Pages

Walkthrough: Basic Data Access in W...

Switch on low bandwidth view

Collapse All

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Visual Web Developer

Walkthrough: Basic Data Access in Web Pages

This walkthrough shows you how to create a simple data-bound page by using controls that are specially designed for data access.

During this walkthrough, you will learn how to do the following:

Connect to a Microsoft SQL Server database in the Microsoft Visual Web Developer Web development tool.
Use drag-and-drop editing to create data-access elements that can be used in the page without code.
Use the SqlDataSource control to manage data access and binding.
Display data with the GridView control.
Configure the GridView control to allow for sorting and paging.
Create a filtered query that displays only selected records.

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 have to upgrade the MDAC version that is already installed on the computer. To download the current MDAC version, see Data Access and Storage Developer Center.

Note:
If you need information about how to log on to the computer running SQL Server, contact the server administrator.

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 right-most Location box, 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.
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.

Adding a GridView Control to Display Data

To display data on an ASP.NET Web page, you need the following:

A connection to a data source (such as a database).
In the following procedure, you will create a connection to the SQL Server Northwind database.
A data source control on the page, which executes queries and manages the results of the queries.
A control on the page to actually display the data.
In the following procedure, you will display data in a GridView control. The GridView control will get its data from the SqlDataSource control.

You can add these elements to the Web site separately. However, it is easiest to start by visualizing the data display using GridView control, and then using wizards to create the connection and data source control. The following procedure explains how to create all three of the elements that you must have to display data on the page.

To add and configure a GridView control for displaying data

In Visual Web Developer, switch to Design view.
From the Data folder 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 menu, in the Choose Data Source list, click <New data source>.
The Data Source Configuration dialog box appears.
Click Database.
This specifies that you want to obtain data from a database that supports SQL statements. This includes SQL Server and other OLE-DB–compatible databases.
In the Specify an ID for the data source box, a default data source control name appears (SqlDataSource1). You can leave this name.
Click OK.
The Configure Data Source Wizard appears, displaying a page on which you can choose a connection.
Click New Connection.
In the Choose Data Source dialog box, under Data source, click Microsoft SQL Server, and then click Continue.
The Add Connection dialog box appears.
In the Server name box, enter the name of the SQL Server that you want to use.
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.
Click Select or enter a database name, and then enter Northwind.
Click Test connection, and when you are sure that it works, click OK.
The Configure Data Source - <DataSourceName> Wizard appears and the connection information is filled in.
Click Next.
The wizard appears, displaying a page on which you can choose to store the connection string in the configuration file. Storing the connection string in the configuration file has two advantages:
1. It is more secure than storing the connection string in the page.
2. You can reuse the same connection string in multiple pages.
Make sure that the Yes, save this connection as check box is selected, and then click Next. (You can leave the default connection string name of NorthwindConnectionString.)
The wizard appears, displaying a page on which you can specify the data that you want to fetch from the database.
Under Specify columns from a table or view, in the Name list, click Customers.

Under Columns, select the CustomerID, CompanyName, and City check boxes.

The wizard appears, displaying the SQL statement that you are creating in a box at the bottom of the page.

Note:
The wizard lets you specify selection criteria (a WHERE clause) and other SQL query options. For this part of the walkthrough, you will create a simple statement without selection or sort options.

Click Next.
Click Test Query to make sure that you are fetching the data you want.

Click Finish.

The wizard closes and you are returned to the page. Running the wizard has accomplished two tasks:

The wizard created and configured a SqlDataSource control (named SqlDataSource1), which incorporates the connection and query information that you specified.
The wizard bound the GridView control to the SqlDataSource. Therefore, the GridView control will display data that is returned by the SqlDataSource control.

If you view the properties for the SqlDataSource control, you can see that the wizard has created values for the ConnectionString and SelectQuery properties.

Note:
You can easily change the look of the GridView control. In Design view, right-click the GridView control, and then click Show Smart Tag. On the GridView Tasks menu, click Auto Format, and then apply a scheme.

Testing the Page

You can now run the page.

To test the page

Press CTRL+F5 to run the page.
The page appears in the browser. The GridView control displays all data rows from the Customers table.
Close the browser.

Adding Sorting and Paging

You can add sorting and paging to the GridView control without writing any code.

To add sorting and paging

In Design view, right-click the GridView control, and then click Show Smart Tag.
On the GridView Tasks shortcut menu, select the Enable Sorting check box.
The column headings in the GridView control change to links.
On the GridView Tasks menu, select the Enable Paging check box.
A footer is added to the GridView control with page number links.
Optionally, use Properties to change the value of the PageSize property from 10 to a smaller page size.
Press CTRL+F5 to run the page.
You will be able to click a column heading to sort by the contents of that column. If there are more records in the data source than the page size of the GridView control, you will be able to use the page navigation links at the bottom of the GridView control to move between pages.
Close the browser.

Adding Filtering

Frequently you want to display only selected data in the page. In this part of the walkthrough, you will modify the query for the SqlDataSource control so that users can select customer records for a particular city.

First, you will use a TextBox control to create a text box in which users can type the name of a city. Then, you will change the query to include a parameterized filter (WHERE clause). As part of that process, you will create a parameter element for the SqlDataSource control. The parameter element establishes how the SqlDataSource control will get the value for its parameterized query—namely, from the text box.

When you are finished with this part of the walkthrough, the page might look similar to the following in Design view.

To add the text box for specifying a city

From the Standard group in the Toolbox, drag a TextBox control and a Button control onto the page.
The Button control is used only to post the page to the server. You will not need to write any code for it.
In Properties, for the TextBox control, set ID to textCity.
If you want, type City or similar text before the text box to act as a caption.
In Properties for the Button control, set Text to Submit.

You can now modify the query to include a filter.

To modify the query with a parameterized filter

Right-click the SqlDataSource control, and then click Show Smart Tag.
On the SqlDataSource Tasks menu, click Configure Data Source.
The Configure Data Source - <Datasourcename> wizard appears.
Click Next.
The wizard displays the SQL command that is currently configured for the SqlDataSource control.
Click WHERE.
The Add WHERE Clause page appears.
In the Column list, click City.
In the Operator list, click =.
In the Source list, click Control.
Under Parameter properties, in the Control ID list, click textCity.
The previous five steps specify that the query will get the search value for City from the TextBox control that you added in the preceding procedure.
Click Add.
The WHERE clause that you have created appears in a box at the bottom of the page.
Click OK to close the Add WHERE Clause page.
In the Configure Data Source - <DataSourceName> wizard, click Next.
On the Test Query page, click Test Query.
The wizard appears, displaying the Parameter Values Editor page, which prompts you for a value to use in the WHERE clause.
In the Value box, type London, and then click OK.
The customer records for London appear.
Click Finish to close the wizard.

You can now test filtering.

To test filtering

Press CTRL+F5 to run the page.
In the text box, type London, and then click Submit.
A list of customers from the city of London appears in the GridView control.
Try other cities, such as Buenos Aires and Berlin.

Next Steps

Data access is an important part of many Web applications, and this walkthrough has provided only a glimpse of what you can do with data in the Web pages. You might want to experiment with additional features of data access. For example, you might want to do the following:

Work with Microsoft Access instead of SQL Server. (You cannot use SQL cache dependency with Access.)
For detailed information, see Walkthrough: Creating a Web Page to Display Access Database Data.
Edit and insert records.
For detailed information, see Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control.
Work with records that have a master-detail relationship.
For detailed information, see Walkthrough: Creating Master/Detail Web Pages in Visual Studio.
Create a middle-tier (business) component that performs data access, and then use that as the data source in a page.
For detailed information, see Walkthrough: Data Binding to a Custom Business Object.

Concepts

Data Source Controls Overview

Tags

: Add a tag

Community Content

Add new content

Annotations

TIP FOR MICROSOFT SQL 2005 Express USERS!

Maui Mensch | Edit | Show History

It took me a few hours to piece together this valuable information online. Hopefully, I can spare you some headache. If you're using Microsoft SQL 2005 express and Visual Web Developer and are having trouble with steps 9-11 in the "Adding a GridView Control to Display Data" section above, follow these steps.

PART I: SET UP SQL SERVER EXPRESS
Click: Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/SQL Server Surface Area Configuration.
Next to "Configure Local Area for localhost", click "Change Computer". Make sure "Local Computer" is selected and click OK
Click "Surface Area Configuration For Services and Connections".
You'll have basically three items in the left window, whose properties you can edit in the right window. Make sure you set the following options:
Database Engine/Service: "Startup Type" set to "Automatic". If "Service Status" does not indicate that it is "running", click "Start".
Database Engine/Remote Connections: choose "Using Named Pipes Only" or "Using both TCP/IP and named pipes"
SQL Server Browser/Service: "Startup Type" set to "Automatic". If "Service Status" does not indicate that it is "running", click "Start".

Now go back to Database Engine/Service and click "Stop" and then "Start" again so that your settings are incorporated (or restart your computer...)

PART II: MAKE SURE YOU HAVE THE NORTHWIND DATABASE
Follow the instructions at the top of this tutorial to install the Northwind database

PART III: CONNECTING TO THE NORTHWIND DATABASE
For steps 9-11 in the "Adding a GridView Control to Display Data" section above, substitute these steps:

1. For Server Name, type .\SQLEXPRESS (the period in front of the \
is vital). Click Test Connection to verify the status. It should return a
success message.
2. From Connect to a Database, click Attach a database, click Browse and point to
the C:\SQL Server 2000 Sample Databases folder.
3. From Select SQL Server Database Files, select NORTHWND.MDF

PART IV: SAY THANKS
If you found this helpful, please let me know. I spent 4 hours probably figuring this thing out and another 30 minutes typing the instructions above. A little recognition would be appreciated. Cheers and good luck.

Tags

: thanxs (x) to (x) you (x) Add a tag

Flag as ContentBug

Thanks, Maui Mensch

Vichai007 | Edit | Show History

I followed your instruction, and it worked like a charm.

I was spending over an hour yesterday trying to follow another walktrough with SQL Express with no avail.

Thanks for your posting!!!!

-Vichai007

Tags

: 2005 (x) 2008 (x) didn't (x) i (x) like (x) Add a tag

Flag as ContentBug

More learning with the GridView and SqlDataSource

CSharpUniversity.com | Edit | Show History

To supplement your learning here with free additional tutorials, source code examples and videos go to: http://www.csharpuniversity.com/category/mid-level-aspnet/

Tags

: Add a tag

Flag as ContentBug

Thank you very much, Maui

Luvem | Edit | Show History

Great instructions!

Everything works fine following your steps untill the end of step 11. An error message saying "Could not load file or assembly Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral,..." pops up. After a quick search, I installed the Microsoft SQL Server 2008 Management Objects from the Microsoft SQL Server 2008 Feature Pack download page. Then, it works perfectly from there.

: )

Tags

: Add a tag

Flag as ContentBug