Using Data with Web Application Projects

Wendy Wei
Microsoft Corporation

May 2006

Applies to:
Visual Studio 2005

Summary: This white paper shows how to properly use SQL Server Express and Data Binding with Web Application Projects, an alternative Web project model that can be added to and used in Visual Studio 2005. (20 printed pages)

Contents

Introduction
   Prerequisites
Walkthrough: Using SQL Server Express Files
   Creating a Web Application Project
   Adding a SQL Server Express Database File
   Create a Page to Display Data
   Testing the SQL Server Express Database
Walkthrough: Data Binding Web Pages with a Visual Studio Dataset
   Creating a Dataset
   Using the DataSet on a Page
   Testing the DataSet
   Adding Updates to the Page
   Testing Updates
Appendix 1: Known Data Issues
   SQL Server Express Issues
   DataSet Designer Issues
   Settings Designer Issues

Introduction

Web Application Projects provide a companion Web project model that can be used as an alternative to the built-in Web Site Project in Visual Studio 2005. This new model is ideal for Web site developers who are converting a Visual Studio .NET 2003 Web project to Visual Studio 2005.

Starting with the April 2006 release, Web Application Projects supports SQL Server Express data management. You can create SQL Server Express databases, and it will facilitate your Web development for most data scenarios. However, Web Application Projects does not address all existing data issues. Our goal with the current release is to enable data scenarios so you can start using the Web Application Projects immediately, as well as to provide workarounds for all known issues. We plan to fix these data issues when Web Application Projects is integrated into the release of Visual Studio 2005, Service Pack 1.

This paper describes some differences in data scenarios between Web Application Projects and Web Site Projects in Visual Studio 2005. Although a Web Application Project enables you to create a Web project within Visual Studio, some aspects of Web Application Projects work like client projects, particularly when using data. An appendix in the paper also lists all the known issues in the current release of Web Application Projects.

In order to illustrate differences in how to work with data in Web Application Projects, the paper includes two walkthroughs for these common data scenarios:

• Creating an application using SQL Server Express. This walkthrough addresses how to add an .mdf file to a Web Application Project and highlights the differences from the same task in a Web site project.
• Data binding ASP.NET Web pages using a dataset in Visual Studio. As with the first walkthrough, this walkthrough highlights differences that you must be aware of when using Web Application Projects instead of Web Site Projects.

Prerequisites

In order to complete these walkthroughs, you need:

• Web Application Projects. This can be downloaded from https://msdn.microsoft.com/asp.net/reference/infrastructure/wap/default.aspx and installed as an add-in feature for Visual Studio 2005.
• SQL Server 2005 Express Edition.
• Access to a SQL Server 2005 Express database (.mdf file) that contains a Customers table and an Employees table. Instructions later in this walkthrough describe how to create this database, or how to use alternative tables if you prefer.

Walkthrough: Using SQL Server Express Files

The first walkthrough illustrates how to add a SQL Server 2005 Express Edition database (.mdf file) to a Web Application Project. There are some slight differences in performing this task between Web Application Projects and Web Site Projects.

Creating a Web Application Project

To begin, create a new Web Application Project.

1. Open Visual Studio 2005.

2. In the File menu, click New Project.

   Note   To create a Web Application Project, you do not choose the New Web Site command, as you do to create a Web site project. Instead, you choose the New Project command.

3. Select Visual Basic or Visual C#, then select ASP.NET Web Application, provide a name and location for the Web project in the Location box, and then click OK.

   The instructions in this walkthrough illustrate the project MyWebProject in the C:\WebProjects folder.

   

   Figure 1. Creating a new Web Application Project

   Visual Studio 2005 creates and opens a Web Application Project. By default, the project contains a single page (Default.aspx), an AssemblyInfo.vb file, and a Web.config file.

   

   Figure 2: A new Web Application Project in Solution Explorer

Adding a SQL Server Express Database File

You can now add a SQL Server Express data file to the project.

If you have an existing SQL Server Express database (an .mdf file created in SQL Server Express), you can use that database for the walkthrough. For example, you might have created an .mdf file while working in a Web site project. The walkthrough requires a table that has some data in it that you can edit as part of the walkthrough.

Note   The .mdf file should have been created with the same version of SQL Server Express that you are using in this walkthrough.

If you do not already have a SQL Server Express .mdf file, you can create a simple database for this walkthrough. For instructions on how to create a SQL Server Express database, see Walkthrough: Creating a SQL Server Express Database File in the MSDN Library. (This walkthrough requires a Customers table and an Employees table; the Employees table must have a primary key typed as an integer.)

The walkthrough assumes that you are working with the Customers table from the SampleDatabase.mdf file described in the topic on the MSDN site. (You will also need the Employees table in the next walkthrough.) If you are not using the Customers table, you can still follow the steps in the walkthrough, but must substitute the names of the table and database that you are using.

For this part of the walkthrough, you will create a folder for the database and then copy the SampleDatabase.mdf file to the folder.

To add a SQL Server Express database file to the project:

1. In Solution Explorer, right-click the name of your Web Application Project, click Add, click Add ASP.NET Folder, and then click App_Data.

   In Web Site Projects, the App_Data folder is created automatically. In Web Application Projects, you must create the folder manually.

   Note   Be sure you add the SQL Server database file to the App_Data folder. The App_Data folder has permissions set on it that will allow the Web page to read and write data to the database file. The folder also supports special syntax to enable using the DataDirectory relative path in connection strings.

2. Select the App_Data folder, and then in the Project menu, click Add Existing Item.

3. Browse to the SampleDatabase.mdf file and then click Add.

   The Data Source Configuration wizard starts. However, in Web Application Projects you do not need to use this wizard.

4. Click Cancel to close the Data Source Configuration wizard.

   The SampleDatabase.mdf database file is added to the App_Data folder and a connection to it is created in Server Explorer.

   

   Figure 3. Adding the SampleDatabase.mdf file to the App_Data folder

Create a Page to Display Data

You will now create a Web page with a GridView control on it that enables you to test that the .mdf file was installed correctly. You can use any data controls to work with SQL Server Express databases. In this walkthrough, you use a GridView control because the control makes it easy to test both reading and updating the database.

To create a Web page to display data:

1. In Solution Explorer, double-click the Default.aspx page to open it.

2. Switch to Design view.

3. From the Data tab in the Toolbox, drag a GridView control onto the page.

4. In the GridView Tasks menu, in the Choose Data Source list, click select <New data source>.

   Note   If the GridView Tasks menu does not automatically appear, right-click the GridView control and then click Show Smart Tag.

   The Data Source Configuration wizard starts.

5. Under Where will the application get the data from?, select Database.

6. For the data source ID, enter "MySampleDataSource", and then click OK.

7. On the Choose Your Data Connection page, make sure that the selected connection is to the SampleDatabase.mdf file, and then click Next.

8. In the Save connection string to the application configuration file page, select the check box to save the connection string, name the connection "mySampleConnection", and then click Next.

   

   Figure 4: Saving the connection string.

9. In the Configure the Select Statement page, make sure Specify columns from a table or view is selected.

10. In the Name list, click Customers, and then select the following columns:

    • CustomerID
    • CompanyName
    • ContactName
    • Country
    • Phone

11. Click the Advanced button.

12. In the Advanced SQL Generation Options dialog box, select Generate Insert, Update, and Delete statements, and then click OK.

    Note   In Web Site Projects, this option is enabled automatically. In Web Application Projects, you must manually choose this option.

13. Click Next.

14. In the Test Query page, click Test Query to confirm that the connection and query are working.

15. Click Finish.

    The GridView control displays the columns that will be used, using placeholder data.

16. In the GridView Tasks menu, click Add New Column.

    The Add Field dialog box is displayed.

17. In the Choose a field type list, select CommandField.

18. In the Button type list, select Button.

19. Select the Edit/Update check box.

20. Make sure the Show cancel button check box is selected, and then click OK.

    The GridView control now displays a column that contains an Edit button.

Testing the SQL Server Express Database

When you test the page, you will make sure that the .mdf file has been installed correctly.

To test the Web site:

1. Press F5 to run the Web site in Debug mode.

2. If you are prompted to remove the Enable the exception assistant debugger option, choose Yes.

   Note   This prompt does not appear in Web Site Projects, only in Web Application Projects.

   The page is displayed in the browser. On the Web page, you see the GridView control and data from the Customers table that you selected.

   Note   If the Web page does not load properly, check your Web browser's proxy settings. They should be set to bypass the proxy server for local addresses.

3. Click Edit in any row in the GridView control.

   The row is redisplayed with editable fields. Notice that you can change the values for all of the columns except the CustomerID, which is a primary key and cannot be changed.

4. Change the CompanyName value in the row you have selected by entering a new value, and then click Update.

   The change is written to the SQL Server Express database and the GridView control is refreshed with the new information.

5. Close the browser.

Walkthrough: Data Binding Web Pages with a Visual Studio Dataset

Many Web applications are built using multiple tiers, with one or more components in the middle tier that combine data access with business logic. This walkthrough shows you how to build a dataset in a Web Application Project and bind a GridView control to the data represented by the dataset. The dataset interacts with the SQL Server Express database to read and write data.

Note   This walkthrough uses the Web Application Project and database that you created in the preceding walkthrough.

Tasks illustrated in this walkthrough include:

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

For more information on designing datasets in Visual Studio, see Dataset Designer in the MSDN Library.

Creating a Dataset

In this walkthrough, you will use a wizard to generate a dataset that reads data from and writes data to the sample database. The dataset 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 dataset

1. Open MyWebProject if it is not already open.

2. Right-click the project root folder, click Add, and then click New Item.

3. In the Add New Item dialog box, select DataSet.

4. In the Name box, type EmployeesObject.xsd.

   

   Figure 5. Creating a DataSet

5. Click Add.

   The Dataset Designer opens.

6. Right-click the design surface of the Dataset Designer, click Add, and then click TableAdapter.

   The TableAdapter Configuration Wizard starts.

   Note   In Web Site Projects, the wizard starts automatically In Web Application Projects, you must start the wizard manually.

7. Select the SQL Server Express connection to the .mdf file that you created or copied earlier. The TableAdapter Configuration wizard appears with the connection information filled in.

8. Select Yes, include sensitive data in the connection string.

   Note   This option does not appear in Web Site Projects, only in Web Application Projects. You must select this option for the Web site to run property.

9. Click Next.

   Page Save the Connection String to the Application Configuration File where you can choose to store the connection string in the configuration file appears.

10. Select the Yes, save this connection as check box, and then click Next.

    You can leave the default connection string name.

    

    Figure 6. Saving the connection string

11. Click Next.

    The Enter a SQL Statement page is displayed.

12. Click Use SQL statements, and then click Next.

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

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

    

    Figure 7. Creating a SQL Select statement

    Note   If you are not using the Employees table from the sample database, make sure that you are working with a table that has a primary key typed as an integer.

14. Click the Advanced Options button.

15. In the Advanced Options dialog box, clear the Use optimistic concurrency and Refresh the data table check boxes, and then click OK.

    These options are not offered in Web Site Projects, because they would generate SQL that is not suitable for use with an ObjectDataSource control.

    

    Figure 8: Clearing advanced options not required for Web-site data objects

16. Click Next.

    The Choose Methods to Generate page is displayed, where you can define the methods that the dataset will expose.

17. Clear the Fill a DataTable check box, and then select the Return a DataTable and Update or change the database check boxes.

    You do not need a method to fill a data table for this walkthrough. However, you need a method that returns the data and you also want the dataset to contain methods that update the database.

18. In the Method Name box, enter "GetEmployees".

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

    

    Figure 9. Creating a method to return a data table

19. Click Finish.

    The wizard configures the dataset and displays it in the Dataset Designer, displaying the data that the dataset manages and the methods that it exposes.

20. Click the Save icon on the toolbar to save the dataset, and then close the Dataset Designer.

21. Expand "My Project" node and double-click on the Settings.settings file to open Settings designer.

22. In order to fix the connection string for the Web scenario. We need to fix it in the settings designer because it translates the relative path according to the client project convention, which adds an extra "App_Data" in the path to the database. Click in the Value column and remove \App_Data in the path. Here is the data before and after:

    Before: "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|<code>App_Data</strong>SampleDat
    abase.mdf;Integrated Security=True;User Instance=True"
    After: " Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDatabase.mdf
    ;Integrated Security=True;User Instance=True"

23. On the Build menu, click Build Project to make sure that the dataset compiles correctly, and so that it can be referenced by other components at design time.

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Contacts.mdf";
Integrated Security=True;Connect Timeout=30;User Instance=True

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Contacts.mdf;Integrated 
Security=True;Connect Timeout=30;User Instance=True