Walkthrough: Creating an Application by Using SQL Server Express and Visual Web Developer 2005 Express

  • Article

This scenario shows how to build a simple ASP .NET Web application that uses SQL Server 2005 Express Edition (SQL Server Express).

Prerequisites

Tasks

Install the Northwind database

  1. Download the installation scripts for the Northwind sample database from the Northwind and pubs Sample Databases Web site.

  2. Run the SQL2000SampleDb.msi file that you downloaded. It installs the installation scripts in the C:\SQL Server 2000 Sample Databases folder.

  3. When installation is complete, open a command prompt: on the Start menu, click Run, type cmd, and then click OK.

  4. At the command prompt, type:

    cd C:\SQL Server 2000 Sample Databases

    and then press ENTER.

  5. At the command prompt, type:

    sqlcmd -S .\SQLExpress -i instnwnd.sql

    And then press ENTER.

    The sqlcmd tool will report its progress as the Northwind database is installed and will then return control to the command prompt.

Verify installation by using the sqlcmd utility

  1. To verify installation of the database, type the following from the command prompt:

    sqlcmd -S .\SQLExpress

    and then press ENTER.

  2. From the sqlcmd tool prompt, type the following three commands:

    Use Northwind
Select name from sys.Tables
Go

  3. You will see the following output from the sqlcmd tool:

    Changed database context to 'Northwind'.

    name

    -----------------------------------------------

    Customers

    Shippers

    Suppliers

    Orders

    Products

    Order Details

    CustomerCustomerDemo

    CustomerDemographics

    Region

    Territories

    EmployeeTerritories

    Employees

    Categories

    (13 rows affected)

  4. From the sqlcmd tool prompt, type the following two commands:

    Select * from Customers

    Go

  5. You see a list of all of the information in the Customers table. Leave this command prompt window open so that you can use it later in this walkthrough.

Create a new Web site

  1. Open Visual Web Developer 2005 Express Edition.

  2. On the File menu, choose New Web Site.

  3. Select a development language (Visual Basic, Visual C#, or Visual J#), select ASP .NET Web Site, provide a name and location for the Web site in the Location box, and then click OK. For this example, name myWebSite, created in the C:\Websites directory, is used.

  4. Visual Web Developer 2005 Express builds a new web site and displays the Default.aspx page in source (HTML) view.

Add UI to the Web page

  1. Click Design in the lower left corner of the Default.aspx code screen.

  2. From the Toolbox, select the Data tab.

  3. Under the Data tab of the Toolbox, drag a GridView object to the designer view of Default.aspx.

Bind the Web page to the data source

  1. In the GridView Tasks dialog box, which automatically appears when you add the GridView to the Web page, under Choose Data Source, select <new data source>.

  2. In the Data Source Configuration dialog box, choose Database, type MyNwndDataSource for the ID, and then click OK.

  3. On the Choose Your Data Connection screen, click New Connection.

  4. In Choose Data Source, select Microsoft SQL Server.

    1. Under Data Provider, select .NET Framework Data Provider for SQL Server.
    2. Click Continue.

  5. In Add Connection, type the name of the server on which SQL Server Express is installed, followed by a backslash (\) and the instance name. For example, if SQL Server Express is installed in the default location, which is the SQLExpress named instance, and is installed on the same computer as Visual Studio, you should type either Servername\SQLExpress or .\SQLExpress.

    1. Select Use Windows Authentication.
    2. From the Select or enter a database name drop-down list, select Northwind.
    3. Click Test Connection. A dialog box appears confirming the connection. Click OK.
    4. Click OK to save the new connection.

  6. In Choose Your Data Connection, click Next.

  7. In the Save connection string to the application configuration file dialog box, choose to save the connection string, and name it myNwndConnection. Click Next.

  8. In Configure the Select Statement, select the data you want to use in this application. Ensure that Specify columns from a table or view is selected, choose the Customers table from the Name drop-down list, and then select the following columns:

    1. CustomerID
    2. CompanyName
    3. ContactName
    4. Country
    5. Phone.

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

  10. Click Next.

  11. In the Test Query window, click Test Query to confirm that the proper data is returned and displayed in the data grid. Click Finish.

  12. You are returned to the Web page designer view, and the GridView now shows the columns that will be used (with artificial data).

Test the Web site

  1. Click Start or press F5 to build and deploy the Web site in Debug mode. If you are prompted to add or modify the Web.config file to enable debugging, choose to do so, and then click OK.

  2. Internet Explorer opens and displays the Web page. On the Web page, you see the GridView and the data from the Northwind 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.

Add more functionality to the Web page

  1. Close Internet Explorer. Visual Web Developer 2005 Express Edition exits Debug mode and returns control for Web page development.

  2. In the designer, click the GridView once. In the upper right corner of the GridView, click the small arrow that appears. This displays the GridView Tasks dialog box.

  3. Click Auto Format, choose a format that you like, and then click OK.

  4. Click Add New Column.

  5. In the Add Field dialog box, for the field type, choose CommandField. Choose the Button button type, and then select Edit/Update. Make sure that Show Cancel Button is also enabled. Click OK.

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

  7. In GridView Tasks, select Enable Paging and Enable Sorting.

Test the Web site

  1. Rebuild the Web site by clicking Start or pressing F5.

  2. Test column sorting and paging on the Web page. By clicking on any column heading, you can sort the column in ascending or descending order.

  3. Click Edit for any row in the DataGrid. 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 typing in a new value. When finished, click Update for that row.

    The change you make is written to SQL Server Express and the GridView is updated with the new information.

Verify the database

  1. In the command prompt windows that you already have open, type the following three commands:

    Use Northwind
Select CompanyName from Customers
Go

    The sqlcmd tool lists the company names for each of the customers. You should see the CompanyName value that you changed.