Creating Databases and Tables

The Data Explorer we have just looked at can be used to create a new database, and to create tables and stored procedures within a database. In this section of the chapter, we'll see how it is done. Then, later on, we will add some data to the new table we create.

The Cornflakes at Midnight example site you'll see in the latter part of this book uses a database to store several sets of information that are used to drive the site. This includes a list of discs that the band has released (though, according to our publishers, it's more like they escape rather than being released!):

Storing the Details of the Discs

Obviously, we could just write the list of discs, and all their individual details, as text and HTML content directly in the source code of the page, but it makes a lot more sense to store them in a database, and then extract the details each time we want to build the page. This way, updates to the list are easy; we just have to update the database and the page will automatically show the current details.

The database is named simply CAM (Cornflakes at Midnight), and we're using the instance of MSDE that is installed with the .NET Framework SDK samples to host it.

Note

We'll be using a fair bit of database terminology in this chapter. We'll be talking about tables, rows, and columns. Each table consists of a number of columns. Each column describes what data can be stored in a table. Tables then store data in rows, each row stores data corresponding to an item. In our Try It Out example, we saw a web page that displayed a table of data. The column headings (pub_id, pub_name, city, and so on) are the columns in the Publishers table. The rows of data correspond to the rows in the Publishers table. This example is a very direct representation of the contents of the database table.

The details we store about each disc are:

• A numeric identifier that is different for each disc, and which acts as the primary key for the rows in the table. A primary key is simply a column containing values that are unique for all the rows in the table, and so can be used to uniquely identify a specific row within the table. Our database can create the value for this column in our table automatically when new rows are added, in which case we refer to it as an IDENTITY column in MSDE or SQL Server terms (in Access and some other databases, it is called an AutoNumber column). This column in our database of type Int (integer) and is named DiscID.

• The title of the disc, which is a string of up to 50 characters. The column type in SQL Server and MSDE for this type of data is VarChar (variable length character string), and the column name is Title.

• The date that the disc was released. This is a DateTime column in SQL Server and MSDE, and is named ReleaseDate.

• The sleeve notes for the disc. This is also a string of characters, but this time we use a special type of column in SQL Server and MSDE called a Text column. This allows us to store large volumes of text in the column, without having to worry about the exceeding the maximum length for a VarChar column (2,048 characters). This column is named Notes.

• The name of the publisher (the record label) that released the disk. This is also string of characters, but we know it will be less than 50 characters in total so we use a column of type VarChar that allows for up to 50 characters. The column name is Label.

• The URL of the disc cover (packaging) image, as located on our web server. We use this to display the disc cover in our pages by inserting it as the value of the src attribute of a hyperlink element. Although the URL in our example site does not contain many characters, we allow for up to 255 in the VarChar column we use. This column in named CoverImageURL.

Creating a New Database

Before we can build the table for our list of discs, we must create a new empty database into which we can place all the tables and stored procedures we create for our site. Providing that you carried out the previous Try It Out successfully, we know that your database server is configured and working properly, and so we can get straight on and create the new database.

Try It Out—Creating a New Database

1. Click the New Connection icon at the top left of the Data Explorer window, as we did at the start of the first Try It Out in this chapter, to open the Connect To Database dialog. Type in the location of the Server you are using, in our case this is the local instance of MSDE named NetSDK that is installed by the .NET Framework samples.

2. We want to create a new database on the server this time, rather than connecting to an existing database, so click the Create a new database link at the bottom of the dialog, as shown below:

   

3. The Enter Information dialog should now open, asking for the name of the new database. Since we're working on the database for the Cornflakes at Midnight example site, name the database CAM, then click OK.

   Note

   Note that if you have already installed the database from the book samples, you won't be able to create another database with the same name, so you can either skip this example, or continue with this example and create a new database with a different name, for example CAM2 or TEST.

   

4. Web Matrix creates the database on the server we specified, and shows it in the Data Explorer window. The two lists, Tables and Stored Procedures are shown, but they are, of course, both empty, as we haven't created any in the new database yet:

   

That's all we have to do to create a new empty database. What we need to do now is to put some tables and data into it, so leave the connection to the new database open, ready for the next Try It Out.

Creating a New Table

Tables provide the structure within which our data is stored in a database. They consist, as you've seen in earlier screenshots and descriptions, of one or more columns, and each column defines the type of data that is stored in that column. Modern databases like MSDE and SQL Server provide lots of different data types that we can use, but in most cases, we're only concerned with three basic types:

• Text (character strings)

• Numbers

• Dates

Database Data Types

The basic data types can be divided into many different subtypes. Without getting too involved in the many types and their specialist uses, it's important to understand which columns to choose for different tasks. The table below summarizes the common choices for our three basic data types.

While the multitude of data types may seem confusing (and there are other more specialist types available as well), the table will make it much easier to understand the differences between them and choose the most appropriate for your needs:

Type of data	Data Type Name	Description
Text (character)data	Char NChar	Text strings up to 2,048 characters long, though the default setting in the Data Explorer is 50 characters. If the value placed in the column is less than the specified length, the remainder is padded (filled) with spaces. When extracted from the database, the returned value has these trailing spaces appended, so that the String is always the length specified for the column size. The Char data type stores the characters in ANSI code (8-bit) form. The NChar data type stores characters as Unicode (16-bit) form.
	VarChar NVarChar	Text strings up to 2,048 characters long, though the default setting in the Data Explorer is 50 characters. In this case, however, the value is not padded with spaces to fill the column. This saves space in the database, and gives faster performance when reading or writing the data. When extracted from the database, the returned value is a String that is the same length as the value stored in the column. The VarChar data type stores the characters in ASNI code (8-bit) form. The NVarChar data type stores characters as Unicode (16-bit) form.
	Text NText	The values are stored in a separate section of the database as pages, each of which can contain 8,080 characters (around 8KB). Although the number of pages for the column must be specified when the table is created, pages are only used as required, and do not take up space if the value is less than the maximum specified for the column. The Text data type stores the characters in ASNI code (8-bit) form. The NText data type stores characters as Unicode (16-bit) form.
Numbers	Int SmallInt TinyInt BigInt	An Int column stores whole numbers from around minus 2 billion to plus 2 billion (which usually proves to a large enough range for everyday purposes!). For smaller ranges of numbers, use SmallInt (-32,768 to +32,767) or TinyInt (0 to 255). For larger numbers, BigInt can store numbers one billion times larger that Int, but the requirement for this is quite rare.
	Bit	The values zero or one (0 or 1), useful for data that is effectively Boolean (things like True/False or Yes/No).
	Float Real	Numbers with fractional parts, in other words not whole numbers. The way that they are stored means that they cannot be guaranteed to be absolutely accurate, but it allows them to hold huge values (for example, with 308 trailing zeros) or very small values (up to 308 decimal places) in only a few bytes of actual database disk space.
	Decimal	Numbers with fractional parts, but stored in a way that means there is no loss of accuracy. Values with up to 38 trailing zeros can be stored this way. The trade off is the use of more database disk space.
	Money SmallMoney	Monetary values, accurate to one ten-thousandth of the monetary unit (in US dollar terms, up to one-hundredth of a cent). The SmallMoney type can hold values from around minus 200,000 to plus 200,000, while the Money type can hold values close to plus or minus ten thousand billion.
Dates and Times	DateTime	A date and time between 1st Jan 1753 and 31st Dec 9999
	SmallDateTime	A date and time between 1st Jan 1900 and 6th Jun 2079

So, with a new database in place, we can now build the table that will hold the details of our discs. We'll work through the process in this next Try It Out using the database we just created. If you named your database something other than CAM, the process is still just the same. The only difference is that the name of the database shown at the top of the Data Explorer window is different.

Try It Out—Creating a New Table

1. In the Data Explorer window, select the entry Tables in the database you created in the previous Try It Out and click the New Item icon on the toolbar:

   

2. The Create New Table dialog opens. Type the name of the table, Discs, in the Table Name textbox near the top of the dialog. The left-hand list shows the columns defined for the table. When the dialog first opens it is empty, so click the New button below it, and you'll see a new column created with the name Column1:

   

3. We talked about the columns we want in this table earlier in the chapter. The first column, the one we just created, needs to be our DiscID column. It is the primary key for the table, and will be an Integer value that is automatically incremented by the database as we add new rows to the table. So, the first step is to change the column name in the Name textbox from the default of Column1 to DiscID, and choose the correct data type for the column from the Data Type drop-down list – it should be Int (which gives us an Integer-type column):

   

   Note

   Don't be tempted to click the OK button when creating columns — there is no need to. The OK button closes the dialog and updates the definition of the table in the database. While we keep it open, we can define all the columns we need and then have the table created in one go in the database itself. Of course, you can always open the table for editing again if you do close the dialog, which we'll show you how to do later. The only thing that changes if you do this is that you can no longer change the name of the table in this dialog

   The Column Properties section of the dialog, where we have just set the Data Type, contains several other controls for the specific settings of each column. Some are disabled, depending on the data type chosen and a few other factors, but for our new DiscID column, you will see that several of these controls become available after we set the data type to Int.

4. We want to specify that this column is the primary key of our table, which means that we must check at least the first two checkboxes: Required (it must always contain a value), and Primary Key (which is only available after checking Required because the primary key column in a row cannot be empty). As we want our DiscID column to be populated with values automatically, we also check Auto-increment. The default First Value and Increment are fine, and can be left as they are:

   

5. Now we can add the second column. Click the New button again, and change the name of the new column to Title. In the Data Type drop-down list select VarChar. You'll see that the Field Size is then set to 50 by default, which is just what we want for our Title column:

   

6. The next column we need is the release date for the disc. Click the New button, change the column name to ReleaseDate, and select the data type DateTime. We’ll demonstrate the use of a default value for this column, so enter (getdate()) in the Default textbox:

   

   Note

   The Default value we specify will be used to set the value of this column when a new row is inserted into the table, if no value is specified for that column. We can still set the value when we create the new row if we want to, or change it later (after inserting the new row). The value we used in the dialog shown above, getdate(), is the name of a function built into the SQL Server and MSDE that returns the current date, so new rows will have this value for the ReleaseDate column. Enclosing the name of the function within brackets indicates that we are using the return value of a function as the default value, rather than entering a specific value to be used every time as a default.

7. The next column we need is the one for the sleeve notes for the disc. This is of type Text. Click the New button, change the column name to Notes, and select the data type Text in the drop-down list. You'll see that the Field Size is set to 16 automatically, and cannot be changed. Remember that because this is a Text column, the value means "16 pages", not "16 characters".

8. After the Notes column comes another VarChar column, this time named Label. Create this as before by clicking the New button, changing the column name to Label, and selecting the data type VarChar in the drop-down list. The default value of 50 (characters) for the Field Size is fine for this column.

9. The final column is the one that holds the URL of the disk cover image. This is also a VarChar column, but this time of size 255 characters. Click the New button again, change the column name to CoverImageURL, and select the data type VarChar in the drop-down list. Change the default value 50 in the Field Size text box to 255:

   

   Note

   If you decide to change the order of the columns after creating them, or at a later date, you can do so using the up and down arrows next to the list of column names in the left-hand side of the Edit Table Design dialog.

10. Now that we've defined all the columns we want for our new Discs table, click the OK button at the bottom of the dialog to create the new table within the database. The Create New Table dialog closes, and the new table can be seen in the Tables list in the Data Explorer window:

    

How It Works

Let's take a quick look at the structure we've created: