Using Data with Web Application Projects
Debugger Visualizations, Garbage Collection
Use the Visual Studio 2005 Bootstrapper to Kick-Start Your Installation
CLR Inside Out: Improving Application Startup Time
Unit Testing Tips: Write Maintainable Unit Tests That Will Save You Time And Tears
Program Customized Testing Environments Without Trashing Your Machine
Bridge the Gap Between Development and Operations with Whitehorse
Visual Studio 2005: Create Reusable Project And Item Templates For Your Development Team
TOC
Collapse the table of content
Expand the table of content
This documentation is archived and is not being maintained.

Using Data with Web Application Projects

Visual Studio 2005
 

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:

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.

    Aa730874.using_data_with_wap01(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap02(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap03(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap04(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap05(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap06(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap07(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap08(en-US,VS.80).jpg

    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.

    Aa730874.using_data_with_wap09(en-US,VS.80).jpg

    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|\App_Data\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.

Using the DataSet on a Page

You can now use the dataset as a data source in an ASP.NET Web page. To access the dataset, you will use an ObjectDataSource control, configuring it to call the data-access methods that are exposed by the dataset. 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

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

    The Configure Data Source wizard appears.

  4. In the Choose a business object list, click
    MyWebProject.EmployeesObjectTableAdapters.EmployeesTableAdapter.

    This is the type name (namespace and class name) of the dataset that you created in the preceding section.

  5. Click Next.
  6. 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 dataset 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.

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

  1. From the Data group in the Toolbox, drag a GridView control onto the page.
  2. In the GridView Tasks menu, in the Choose Data Source list, click ObjectDataSource1.
    Note   If the GridView Tasks menu does not automatically appear, right-click the GridView control and then click Show Smart Tag.

    The GridView control reappears with a column for each data column that is returned by the SQL statement.

  3. Select the GridView control, and then in the Properties window, verify that the DataKeyNames property is set to EmployeeID.

Testing the DataSet

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

To test the dataset

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

When the page runs, the GridView control requests data from the ObjectDataSource control. The ObjectDataSource control in turn creates an instance of the dataset and calls its GetEmployees method. The GetEmployees method returns a DataTable object, which the ObjectDataSource control returns to the GridView control.

Adding Updates to the Page

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

Note   The GridView control does not support inserting new records.

To enable updates and deletes

  1. Right-click the GridView control, and then click Show Smart Tag.
  2. Select the Enable Editing check box.
  3. 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 dataset to update the database.

To test updates

  1. Press CTRL+F5 to run the page.

    This time, the GridView control displays Edit and Delete links in each row.

  2. Click the Edit link in a row.
  3. Make a change to the row, and then click Update.

    The grid is redisplayed with the updated date.

  4. Click the Delete link that is in a row.

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

    Note   If you use the SampleDatabase.mdf from the walkthrough, the delete command will not succeed because there is a foreign key constraint from another table in the database.
  5. Close the browser.

Appendix 1: Known Data Issues

Because of the differences between the Web Application Project model and the Web site project model, their support for data scenarios is different. A Web Application Project has the same semantics as a Windows application project (a client project), which differ from those for a Web site project.

The primary areas in which differences exist are:

  • SQL Server 2005 Express databases
  • Datasets and the TableAdapter Configuration wizard
  • Settings designer

SQL Server Express Issues

This section contains a list of behavior differences and issues that arise when working with SQL Server 2005 Express databases (.mdf files). Workarounds for all issues are provided here. For more details on how these workarounds can enable the scenario completely, see Walkthrough: Using SQL Server Express Files earlier in this paper.

No prompt for adding the App_Data folder

When you add a SQL Server Express database (.mdf file) to a Web Application Project as an existing item, Visual Studio does not prompt you to place the .mdf file in the App_Data folder, because the App_Data folder does not exist by default as it does in Web Site Projects.

Similarly, if you double click an .mdf file in any location in a Web Application Project, Visual Studio creates a connection for the database and lists it in Server Explorer. However, if the .mdf file is not in the App_Data folder, the connection might not work at run time.

To work around this issue, manually create the App_Data folder and then make sure that you add any .mdf files to that folder. Note that it is recommended that all data stored in the Web site be in the App_Data folder. This folder has the correct permissions for data access and supports special syntax in connection strings to use the |DataDirectory| relative path.

DataSet Designer Issues

This section lists behavior differences, issues, and features that are not yet implemented in the dataset designer. Missing features and known issues will be addressed in future releases.

Adding .xsd file does not launch wizard

When you add a new .xsd file in a Web Application Project, Visual Studio does not automatically launch the TableAdapter Configuration wizard. (In Web Site Projects, the wizard starts automatically.)

To work around this issue, right-click the design surface of the designer, click Add, and then add a TableAdapter object. This starts the wizard.

"Sensitive data" prompt in TableAdapter Configuration wizard

In Web Application Projects, the first time you use the TableAdapter Configuration wizard, the wizard prompts you to include or exclude sensitive data in the connection string. Web sites require the information in the connection string, so this prompt does not appear when you are in a Web site project.

In Web Application Projects, make sure that you explicitly select Yes, include sensitive data in the connection string.

TableAdapter Configuration wizard does not create correct path to .mdf file

In Web Application Projects, the TableAdapter Configuration wizard does not add the correct relative path in the connection string for an .mdf file. An example of the path that the wizard creates might be the following:

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

The correct path should be:

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

To work around this issue, before you run the Web site, manually edit the connection string in the Web.config and Settings.settings file to remove "\App_Data" from the path. Note that your change fixes a run-time error, but results in a design-time error.

TableAdapter Configuration wizard does not prompt to correct .mdf file location

When you are using the TableAdapter Configuration wizard to create a new .mdf file, the wizard does not warn you that the .mdf file should be in the App_Data folder. (In Web Site Projects, the wizard prompts you correctly.) Instead, the wizard creates the .mdf file in your user directory.

To work around this issue, manually create an App_Data folder in your Web Application Project, if one does not already exist. Then move the .mdf file to the App_Data folder.

Connection strings not displayed

The TableAdapter Configuration Wizard does not enumerate connection strings that are defined in the project configuration file.

Differences in TableAdapter Configuration wizard advanced options

In the Advanced Options dialog box, the TableAdapter Configuration wizard offers the Use optimistic concurrency and Refresh the DataTable options. These options are not suitable for generating Update, Insert, and Delete methods for use with the ObjectDataSource control in Web sites.

Make sure that you clear these options when configuration data in a Web Application Project.

Data settings stored in .settings file

In a Web Application Project, connection string information is stored in a .settings file. In Web Site Projects, connection string information is stored in the Web.config file.

TableAdapter types are not available before building project

When you create TableAdapter classes based on .xsd files, the classes are not immediately available to other classes in the project. For example, the ObjectDataSource control will not see TableAdapter types. This is because the types are not dynamically compiled at design time, as in Web Site Projects.

To work around this issue, manually build the .xsd files. After the project builds successfully, the types are available at design time.

Settings Designer Issues

This section contains a list of issues that arise in Web Application Projects when working with the Visual Studio Settings designer.

Connection string settings not displayed

When you use the Settings designer to add connection string settings, the new values do not appear as options in the TableAdapter Configuration wizard or in the SqlDataSource control's connection dropdown.

Unusable "User" settings

The Settings designer exposes settings scoped to "User". However, settings in that scope are invalid in a Web project and should not be available.

Show:
© 2016 Microsoft