If you haven't installed SQL Server, you might want to do so now. The Visual Studio installation software comes with SQL Server (at least the versions I've seen) or you can download the free SQL Server Express edition at www.microsoft.com/express/Database. You should probably download the management tools, too. You will find SQL Server Management Studio Express particularly useful for building databases.

Assuming you have SQL Server installed, you need to create the database that your program will manipulate. To do that:

Figure 39.2. Figure 39-2

You can use SQL Server Management Studio to build the database interactively. See the program's help for details.

Alternatively, you can execute a SQL script that contains commands to build the database for you. The file MakeOrdersDatabase.sql, which is available in this lesson's download, builds a database called OrdersDatabase that contains Customer, PurchaseOrder, and OrderItem tables.

To run the script, download it from the book's web site. In SQL Server Management Studio, open the File menu, select Open, and pick File; or click [Ctrl]+O. Browse to the script and click Open. After you load the script, execute it by opening the Query menu and selecting Execute, or by pressing [f5].

The following code shows the parts of the script that create the database and its tables:

-- Create and use the OrdersDatabase.
CREATE DATABASE OrdersDatabase;
GO
USE OrdersDatabase;

-- Create the Customer table.
CREATE TABLE Customer
(
    CustomerId        int                PRIMARY KEY,
    FirstName         nvarchar(50)       NOT NULL,
    LastName          nvarchar(50)       NOT NULL,
)

-- Create the PurchaseOrder table.
CREATE TABLE PurchaseOrder
(
    OrderId           int                PRIMARY KEY,
    OrderDate         datetime           NOT NULL,
    CustomerId        int                NOT NULL
        FOREIGN KEY REFERENCES Customer (CustomerId),
)

-- Create the OrderItem table.
CREATE TABLE OrderItem
(
    OrderId           int                NOT NULL
        FOREIGN KEY REFERENCES PurchaseOrder (OrderId),
    ItemNumber        int                NOT NULL,
    ItemDescription   nvarchar(50)       NOT NULL,
    Quantity          int                NOT NULL,
    CONSTRAINT PK_OrderItem PRIMARY KEY (OrderId, ItemNumber)
)

You don't need to understand every detail of this script. You should be able to get the gist of it even if you're unfamiliar with the Transact-SQL database language that it uses.

The CREATE DATABASE statement creates the OrdersDatabase inside the server.

The CREATE TABLE Customer statement creates the Customer table and gives it the fields CustomerId, FirstName, and LastName. CustomerId is an integer, and the table's primary key. FirstName and LastName are strings with lengths up to 50. The NOT NULL clause means all records must have these values filled in. (Primary keys must also always have values.)

The CREATE TABLE PurchaseOrder statement similarly creates the PurchaseOrder table. The only new concept used by this statement is the FOREIGN KEY clause, which indicates that this table's CustomerId field references the Customer table's CustomerId field. That means any record in the PurchaseOrder table must have a CustomerId value that is already present in the Customer table. The idea is that a PurchaseOrder record's CustomerId indicates the ID of the customer who placed the order.

Finally, the CREATE TABLE OrderItem statement creates the OrderItem table. Its FOREIGN KEY clause requires that its OrderId field match a value in the PurchaseOrder table. The idea here is that an OrderItem record's OrderId value indicates which PurchaseOrder includes the OrderItem.

This last CREATE TABLE statement also uses a PRIMARY KEY clause to indicate that the table's primary key includes the combination of OrderId and ItemNumber. Each order may contain several items, and their ItemNumber values indicate the order in which they should appear in the purchase order.

You'll see a graphic representation of this database design in the next section.

In addition to creating the OrdersDatabase and its Customer, PurchaseOrder, and OrderItem tables, the script inserts records in the tables to give your programs some data to manipulate. That code is long and not very interesting so it isn't shown here.

To make the data model, start a new Visual Basic Windows Forms project. Then open the Project menu and select Add New Item. On the Add New Item dialog, shown in Figure 39-3, pick the Data category on the left and select ADO.NET Entity Data Model. Enter a name for the model (such as OrdersModel.edmx) and click Add to launch the Entity Data Model Wizard.

Figure 39.3. Figure 39-3

The wizard's first page, which is shown in Figure 39-4, lets you decide whether you want to generate a model from a database or create an empty model. An empty model is useful if you want to build the model from scratch and then use Entity Framework tools to build a database from it.

For this example, pick "Generate from database" and click Next to display the Choose Your Data Connection page shown in Figure 39-5. If you have previously defined database connections (which you probably haven't at this point), you can select one from the drop-down list.

Figure 39.4. Figure 39-4

Figure 39.5. Figure 39-5

If you have no previously defined connections, click New Connection to display the Choose Data Source dialog shown in Figure 39-6. Select the type of data source that you want to use and click Continue. In Figure 39-6, I selected a database server, but the documentation says that Visual Studio Express Edition can select only database files, not servers, so you might need to make a different selection.

Figure 39.6. Figure 39-6

When you click Continue, the Connection Properties dialog shown in Figure 39-5 appears. Fill in the server name and database name. Note that you must append SQLEXPRESS to the server name if you are using SQL Server Express Edition. If you don't, you'll get a confusing error message saying, "The server was not found or was not accessible."

When you finish entering the server and database names, click OK to return to the Entity Data Model Wizard page shown in Figure 39-5. Click Next to see the page shown in Figure 39-8. Select all of the tables and click Finish to close the wizard.

Figure 39.7. Figure 39-7

Figure 39.8. Figure 39-8

At this point the wizard builds the data model and displays it graphically, as shown in Figure 39-9. In addition to building a pretty picture of the model, the wizard builds classes to represent the data. If you look closely at this figure, you can learn about those classes and how they are related.

Figure 39.9. Figure 39-9

Each class is represented by a box that lists the properties built by the wizard to correspond to the database table's fields. At the bottom of each class is a list of navigation properties that your program can use to link one class to others. For example, the Customer class's PurchaseOrders property is a collection of PurchaseOrder objects.

If you click on a property in the model, the Properties window displays information about that property such as its name, whether it is nullable (allows null or missing values), and its data type.

The links connecting the classes shown in Figure 39-9 indicate the relationships among the classes. The numbers at either end of a link indicate the numbers of objects from the corresponding classes that are involved in the link.

For example, the link between the Customer and PurchaseOrder classes in Figure 39-9 has a 1 at the Customer end and a * at the PurchaseOrder end. That means one Customer object corresponds to any number of PurchaseOrder objects. Similarly, one PurchaseOrder object corresponds to any number of OrderItem objects.

If you click a link, the Properties window provides information about the link such as the names of the fields in the two tables it connects and their multiplicities.

In this lesson: