Session 4 - Working with Databases

You will be familiar with working with Microsoft Access to develop desktop databases with forms and reports. Underlying your database application are tables, relationships and queries. With a web based application, the user interface is provided completely in the form of web pages. Interaction will be through buttons and links. There is a need to simulate the forms and reports concept from Access in the web based environment. Fortunately, ASP.Net provides controls specifically to allow connection to and display of underlying database, whether in Microsoft Access, or some other database system. We will look at these controls and how to use them later.

Our development environment contains a cut down version of Microsoft SQL Server 2005 which provides all of the underlying database functionality, but does not include any management tools. However, VWD does provide a simple set of tools to link to SQL Server 2005 Express, so that you can create databases, tables, relationships, queries etc. It is also possible to create your underlying database using Access or mySQL. Connecting to other databases is similar (using different connection mechanisms), but the final display and manipulation of data is independent of the underlying database system. We will use the built in facilities for SQL Server 2005 Express in this tutorial.

Creating your database

Before you can develop an application you must determine the structure and organisation of your underlying data, and this usually means designing a database. You data analysis and subsequent normalisation results in a linked table structure with a defined set of fields for each table. From this design we can go through a straightforward construction process to build the database.

  1. Create a database container (.mdb file in Accesss, .mdf file in SQL server, database folder in mySQL)
  2. Connect to the database.
  3. For each table in your definition, create the table, and name it.
  4. For each table create the field definitions, with names, types, sizes. Set the key field(s).
  5. Establish the relationships between fields in tables.
  6. Pre-populate tables which have fixed (lookup) data.
  7. Populate other tables with sample data if required.

We will use this approach to build a website linked to a database with the following structure, which represents a simple stock control database:

Simple database relationships diagram

Note: the above diagram has been produced from Microsoft Access, but we will reproduce this structure in VWD as an SQL Server 2005 Express database.

Step by Step

  1. Close any open projects and create a new C# ASP.NET web site called StockControl.
  2. If you look at the Solution Explorer pane (top right) you will see to following:
    Solution explorer view
  3. Right click the App_Data item in the list and select 'Add New Item…'
  4. In the Add New Item dialog box choose Database and change the name to Stock.mdf
  5. The Solution Explorer will switch to Database Explorer mode show the following information:
    Database explorer view
  6. Right Click the Tables element in the list and Select 'Add New Table'.
  7. The Table design view should now show in the Design pane. You can type in new fields, and for each field you can set properties as you enter them. You will end up with a set of fields as follows:
    Database table editor view
  8. Select the StockCode Field, right click and choose 'Set Primary Key'.
  9. Choose menu item File | Save Table 1 and type the name tblStock.
  10. Repeat stages 6 through 9 to create and save a table to reproduce the field layout for table tblManufacturer.
  11. At this stage you have created the tables. However, they do not contain any data, and there is no relationship information stored to link the ManufacturerCode field in each table.
    If you want to create an enforced relationship between the two tables you can do this using the Database Diagrams feature in VWD. In the Database Explorer right click Database Diagrams and choose Add New Diagram ... (You may be promted to install the Database Diagram feature if you have not used it before. Simply accept the offer to install the feature.) Choose both tables from the Add Table Dialog and click Add, and then Close. A new tab opens with a plain database diagram with the two tables in it, as follows:

    Database diagram view - relationships
  12. To create the relationship between the ManufacturerCode in both tables select the grey button to the left of the ManufacturerCode field in the stock table and drag it to the ManufacturerCode field in the Manufacturer table. You will get a Tables and Columns dialog describing the elationship you have just created.

    Tables and columns view

    Click OK, and then click OK to the Foreign Key Relationship dialog. Type a name for the diagram (e.g. Relationships) and press OK. Press OK to save the changes to the tables. You now have a pair of linked tables.
  13. You can add data manually by right clicking a table in the Database Explorer view and selecting Show Table Data. The data appears in Grid view and you can simply type into the grid. If you have setup related fields with referential integrity you will have to populate the Manufacturer table first, as the Manufacturer Code is needed for each stock item.

Note: you can uses Windows Explorer to copy the App_Data folder from one website project into another. This makes it easy to experiment with different web page designs without having to create a new database each time.

Download a ZIP version of the Stock table project.

Getting your data onto a web page

Now we have a database, we can think about creating the web page based user interface to it. We will go through a series of stages demonstrating basic elements of database interaction. These will be:

  • Viewing table data
  • Pagination and sorting
  • Editing, deleting and inserting records
  • Master detail pages
  • Record filtering

Fortunately most of the construction work for the user interface elements is wizard driven.

Session 4 - Workshop

Choose a simple example scenario from the list below and use VWD to create a new Website and the database tables for the scenario. Do not over analyse the scenario as the purpose of this exercise is to become familiar with VWD, not to develop skills in database design. Use VWD to enter some sample data. We will look at creating the web page interface to the database in the next session.

  1. CD / Record collection (tables for Albums, Artist, Labels etc.)
  2. Book collection (tables for Books, Publisher, Genre etc.)
  3. Purchase orders (tables for Stock Items, Orders, Order Items etc)
  4. etc.
Valid XHTML 1.0! | Valid CSS! | Bobby WorldWide Approved AAA