Visual Web Developer™ 2005 Express Edition For Dummies - Alan Simpson

220 Part III: Personalization and Databases

Figure 10-18:

Setting a default theme in Web. config.

The beauty of this approach is that every page opens with the default theme applied, instead of with no theme applied. As you create and review your pages, you know exactly where each item’s style is coming from.

The default theme is only the default, in the sense that it’s applied only when something else doesn’t override it. Any theme that’s applied programmatically will override the default theme.

Chapter 11

SQL Server Crash Course

In This Chapter

Exploring database design

Designing a database with tables and primary keys

Creating your own SQL Server tables

Linking tables

Abig part of any dynamic data-driven Web site is the database that contains data for the site. Visual Web Developer allows you to use either

Microsoft Access (.mdb files) or SQL Server 2005 (.mdf files) for storing data. Of the two, a SQL Server database provides better scalability and supports a greater number of simultaneous users. That’s why it’s the database most people will likely want to use (or would, if they knew what we know), so that’s the one that gets the focus in this book.

As its title suggests, this chapter is a quick crash course in using SQL Server as the database for your Web site. You’ll discover what SQL Server is, and how to create tables to store the data your Web site needs. You’ll also get the lowdown on SQL, Structured Query Language, the tool you use to extract specific data from the database to display on Web pages. If you’re already familiar with a database program like Microsoft Access, the most noticeable feature of SQL Server is that it has no user interface — no program window you can open from the Start menu. There’s no table designer, no forms designer, nothing. The reason for that is simple: Microsoft Access is an application program that has to perform multiple tasks for a wide range of users — but SQL Server is a lot more specialized; it’s just a server.

As a server, SQL Server 2005 is designed to provide data storage and Access to some other program rather than to a human who is sitting at the mouse and keyboard. The working interface for SQL Server isn’t in its own separate program window. It’s right in Visual Web Developer.

222 Part III: Personalization and Databases

Crash Course in Database Design

Database design is an enormous topic that could easily fill several books the size of this one — and has — so I can’t get into all the details here. But I can get you in the ballpark and clear up some of the important buzzwords that apply to all relational databases.

Tables, rows, and columns

The data (information) in a SQL Server database is organized into tables. Each table consists of rows (also called records) and columns (also called fields). Figure 11-1 illustrates the terms, using a sample table named Items. The field names (that is, the column names) are ItemId, OurItemId, ItemName, and ItemPrice. The table contains five rows (records), which happen to be numbered 10001, 10002, 10003, and so forth.

Rows (records)

Columns (fields)

Figure 11-1:

A sample database table.

When you set up your site for Membership, VWD automatically creates several tables for you. To see those tables, first make sure your Web site is open in Visual Web Developer, then click the Database Explorer tab or choose View Database Explorer from the menu bar.

To see the tables that are already in your database, expand the Data Connections, ASPNET.MDF, and Tables categories (if they’re showing a + sign). Each item under the Tables heading represents a single table of data. The names of tables that VWD automatically creates all start with aspnet_ as shown in Figure 11-2.

Each one of those tables contains rows and columns. To see the actual data that’s stored in a table, right-click the table name and choose Show Table Data. For example, if you right-click aspnet_Users and choose Show Table Data, the aspnet_Users table opens. The table will contain one record for each user account you’ve created.

Chapter 11: SQL Server Crash Course 223

Figure 11-2:

Automatically

created

tables in a

Membership

site.

For example, by the time I got to this part of the book, I had created seven user accounts through the Web Site Administration Tool. Every account you create becomes a record in aspnet_Users. So opening my aspnet_Users table shows that it contains seven records, as shown in Figure 11-3.

Figure 11-3:

The

aspnet_

Users table data.

Don’t add, change, or delete data in any of the aspnet_ tables manually through Database Explorer. Always use the Web Site Administration Tool to manage membership data.

To close an open table, just click the Close (X) button in the upper-right corner, just as you’d close anything else that’s open in the Design surface.

One-to-many, many-to-many

SQL Server 2005 is a relational database-management system (RDBMS) that not only stores data, but can also define multiple relationships among items

224 Part III: Personalization and Databases

of data. It, and other products like it, exist mainly because in the real world, there are natural one-to-many relationships among different types of data. For example, suppose your site offers some kind of items to users. The items might be products you sell, courses you offer, Web seminars that people sign up for, or something like that.

In cases such as these there are two natural one-to-many relationships between users and the items being offered:

Any one user might purchase many items.

Any one item might be purchased by many users.

Any time you have two one-to-many relationships like that, you have what’s called a many-to-many relationship. You have many users purchasing (or enrolling in, or attending) many items.

To illustrate, let’s look at simplified versions of the aspnet_Users table and a table of items that the site offers to users. At the left side of Figure 11-4 is a table showing a couple of columns and some rows from a table of users. Each record in that table represents a single user account.

Users

Items

	Figure 11-4:
	Sample	How do I
	aspnet_
		connect
	Users and
		these?
	Items
	tables.

On the right side is a simple Items table. Each record in that table represents a single item offered to users. In between the two tables is the burning question “How do I connect these?” The answer is pretty straightforward: “By creating a third table that keeps track of who purchased what.”

It might help to think of it this way. Each time a user purchases a product, that’s a transaction. You need to keep track of these transactions — specifically, who purchased what — and that requires two pieces of information per transaction: who (a UserId), and what (an ItemId).

So, at the very least, this third table (which I’ll name Transactions) must contain two fields: One field to record the user’s identification (UserId), and the other to record the item purchased (ItemId). You can imagine this as a table with two fields named UserId and ItemId between the aspnet_Users table and the Items table.

Chapter 11: SQL Server Crash Course 225

Now, imagine that user Carol (UserId 66cd...) purchases item 10003. The Transactions would need a new record with 66cd in its UserId field and the 10003 in its ItemId field. If you then trace an imaginary line from the user to the transaction to the item, you’ll see how the record in the Transactions table provides the link from a specific user to a specific item purchased, as in Figure 11-5.

Users

Transactions

Items

Figure 11-5:

Third

Transactions table links user to item purchased.

Every time a user purchases something, the Transactions table grows by one record. As time goes by, the Transactions table continues to grow, perhaps to thousands of records. Each record represents a single transaction where a specific user purchased a specific product.

Recall that the natural many-to-many relationship between users and items is actually two one-to-many relationships. Any one user might purchase many items, and any one item might be purchased by many users. The Transactions table provides the “map” that allows code to find all transactions by any

one user.

No matter how many records are in the Transactions table, that table still provides the one-to-many link from users to items. To illustrate, pick one user, such as Carol (UserId 66cd...). Each record in the Transactions table that has 66cd... is a transaction made by that user. The ItemId field in Transactions identifies exactly which item the user purchased, as shown in Figure 11-6.

Users	Transactions
	Items

Figure 11-6:

One-to- many link from a user to many products purchased.

226 Part III: Personalization and Databases

There’s also a one-to-many relationship between items and users. The Transactions table, once again, provides the map describing which users purchased an item. Pick any one item, say 10003, from the Items table. Find records in the Transactions table that have 10003 in their ItemId field, and you have a link back to each user that purchased that product, as shown in Figure 11-7.

Users	Transactions
	Items

Figure 11-7:

Trace any one ItemId back to users who bought that item.

In real life, of course, you don’t draw lines between records in tables to get information. In fact, you don’t look at the tables at all — instead, you create queries to get information.

However, while I’m on the subject of lines that connect things between tables, any time you extract data from all three of the tables (users, transactions, and items), your query must contain fields of all three tables. And the primary and foreign keys that link the tables must be connected by join lines in that query, as shown in Figure 11-8. We’ll discuss the roles of the primary and foreign keys in detail as we progress through the chapter.

Figure 11-8:

Aquery’s view of

connecting lines between tables.

So that’s how database design works, in a conceptual sense. When there is a natural many-to-many relationship between items in two separate tables, you need a third table that contains records stating who purchased what. That third table provides the many-to-many link needed to extract meaningful data from the tables.

Chapter 11: SQL Server Crash Course 227

However, if you were to try this technique right now — using only what’s covered in the book up to this point — there’s a good chance yours wouldn’t work. That’s because there are rules to follow to get this sort of thing to work — two in particular:

Every table on the “one” side of a one-to-many relation must have a primary key, a field that uniquely identifies each record in that table. In this example, both the users and items tables must have a primary key.

The transaction table must contain at least two fields whose names and data types match the names and data types of the two tables on the “one” side of the relationships.

To understand and apply those rules, the first order of business is to understand data types and primary keys. Let’s start with data types.

SQL Server Tables

Every column in every table has a data type that defines the type of data stored in the column. To view each column’s data type, open the table definition, rather than the table data. When you open a table definition, you don’t see any of the actual data that’s stored in the table. Instead, you see the structure of the table. The table’s structure shows the name, data type, and whether the field allows nulls.

To see an existing table’s structure, right-click the table’s name in Database

Explorer and choose Open Table Definition, as shown in Figure 11-9.

Figure 11-9:

How to view a table’s structure (definition).

For example, if you right-click the aspnet_Users table in Database Explorer and choose Open Table Definition, you see field names listed down the left side of the grid that opens. Each of those names represents a column that appears at the top of the aspnet_Users table when you’re viewing the table’s data. To the right of each field name is the Data Type of that field, as shown in Figure 11-10.

228 Part III: Personalization and Databases

Figure 11-10:

Table definition (structure) of the

aspnet_

Users

table.

Here’s what the table definition for the built-in aspnet_Users table tells you about columns in that table:

There are seven fields (columns) in this table, their names are listed in the Column Name column (ApplicationId, UserId, UserName, and so forth).

The data type of each field is visible just to the right of the field name. For example, both the ApplicationId and UserId columns are of the uniqueidentifier data type.

The UserId field is the primary key for this table, as indicated by the key symbol to the left of its name.

When entering new records into this table, it’s okay to leave the MobileAlias field empty (because its Allow Nulls check box is selected). But no other fields in the record can be left blank.

Never change or remove anything in any aspnet_ table’s structure. Those tables are created and used by the membership system and any changes you make could cause the whole membership system to stop working.

When you’re creating your own tables, it’s important to choose data types wisely. A field’s data type defines what you can put into the field — and what you can do with the information stored in that field. The main data types available to you in SQL Server 2005 Express are categorized as follows:

Text: Character data like the text you’re reading right now, people’s names, product names, and so forth.

Number: Also called scalar values, these are real numbers like quantities and dollar amounts on which you can do math.

Date/Time: Dates and time of day.

Boolean: A value that can be only True or False.

Binary: Pictures and other kinds of data that aren’t text, numbers, or dates.

Other: Specialized data types such as uniqueidentifier, xml, timestamp, and sql_variant.

Chapter 11: SQL Server Crash Course 229

The most common type of information stored in tables is text, so we’ll look at the text data types first.

Data types for storing text

In the computer biz, a chunk of text is referred to as a string, shorthand for “a string of characters.” The following are all examples of strings:

Banana

Andy Adams

123 Oak Tree Lane

Hello world, how ya doin’ today?

[phone] (phone number)

123-45-6789 (Social Security number)

00453-4321 (ZIP Code)

Phone numbers, social security numbers, and ZIP codes are not true numbers (scalar values) that you’d ever add, subtract, multiply, or divide. So they must be stored as strings (usually the char data type) rather than as numbers. If you use a number data type for any of those fields, you won’t be able to use leading zeroes in Zip codes (like 01234), or use parentheses and hyphens in phone numbers, because such things are not allowed in scalar values.

Text comes in two basic flavors:

Unicode text: Requires two bytes (16 bits) per character and can include characters from virtually any human language.

Non-Unicode text: Uses only one byte (eight bits) per character, but is limited to characters in the English alphabet.

You always want to be efficient in how you store data. So if a field should contain only English characters (A–Z) and such, a non-Unicode data type would be most efficient. But, given that we live in a Web-connected world, you may often need characters from other languages, in which case you’d use a Unicode data type — and storing the data would cost you twice as much disk space.

When defining a field to store either type of text, you can choose between fixed-length and varying-length. Fixed-length is more efficient when all the values stored in the field are the same length, or very similar in length. For example, if you were to create a field to store product identifiers, and all the product identifiers follow a similar pattern — say, ABC-123 — then a nonUnicode fixed-length text field would be ideal. In the ABC-123 example, a fixed length of 7 characters would do the trick (the hyphen counts as a character).

Often, there’s no way to predict how much text a field will store. For example, if the field is storing text that people have typed into a form, there’s no telling