Build Your Own ASP.NET 3.5 Website Using C# & VB (52 page)
Read Build Your Own ASP.NET 3.5 Website Using C# & VB Online
Authors: Cristian Darie,Zak Ruvalcaba,Wyatt Barnett
Tags: #C♯ (Computer program language), #Active server pages, #Programming Languages, #C#, #Web Page Design, #Computers, #Web site development, #internet programming, #General, #C? (Computer program language), #Internet, #Visual BASIC, #Microsoft Visual BASIC, #Application Development, #Microsoft .NET Framework
282
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
Figure 7.10. Specifying column properties
Figure 7.11. The Employees table
Now, let’s create a table called Employees by adding the columns described in
Licensed to [email protected]
Database Design and Development
283
Table 7.1. The structure of the Employees table
Column Name
SQL Data Type
Identity
Allow Nulls
Primary Key
EmployeeID
int
Yes
No
Yes
DepartmentID
int
No
No
No
Name
nvarchar(50)
No
No
No
Username
nvarchar(50)
No
No
No
Password
nvarchar(50)
No
Yes
No
Address
nvarchar(50)
No
Yes
No
City
nvarchar(50)
No
Yes
No
State
nvarchar(50)
No
Yes
No
Zip
nvarchar(50)
No
Yes
No
HomePhone
nvarchar(50)
No
Yes
No
Extension
nvarchar(50)
No
Yes
No
MobilePhone
nvarchar(50)
No
Yes
No
After you enter this information, press
Ctrl
+
S
to save the table. When you’re asked to name the table, type
Employees
and click
OK
. When you’re done, your table will resemble
Figure 7.11
.
After you create the table, you’ll see it appear under the
Tables
node in the
Object
Explorer
(or
Database Explorer
in Visual Web Developer). SQL Server Management
Studio prepends dbo. to the table’s name; dbo is the default “database owner” user.
Don’t worry about this for now—we’ll explore the topic of database users in some
detail later.
If you close the table designer window, you can open it later by right-clicking the
Employees table and selecting
Open Table Definition
in Visual Web Developer, or
Modify
in SQL Server Management Studio. You’ll be taken back to the screen that
shows the structure of the table (shown in Figure 7.11
).
Creating the Remaining Tables
Let’s create the rest of the database tables. Apply the process you used to build the
Employee table to create the new data tables, using the data presented in
Table 7.2
Licensed to [email protected]
284
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
to Table 7.6
. Later in this chapter, we’ll discuss how these tables work. For starters, though, you need to insert them into your database.
Table 7.2. The Departments table
Column Name
SQL Data Type
Identity
Allow Null
Primary Key
DepartmentID
int
Yes
No
Yes
Department
nvarchar(50)
No
No
No
Table 7.3. The HelpDesk table
Column Name
SQL Data Type
Identity
Allow Null
Primary Key
RequestID
int
Yes
No
Yes
EmployeeID
int
No
No
No
StationNumber
int
No
Yes
No
CategoryID
int
No
No
No
SubjectID
int
No
No
No
Description
nvarchar(50)
No
Yes
No
StatusID
int
No
No
No
Table 7.4. The HelpDeskCategories table
Column Name
SQL Data Type
Identity
Allow Null
Primary Key
CategoryID
int
Yes
No
Yes
Category
nvarchar(50)
No
No
No
Table 7.5. The HelpDeskSubjects table
Column Name
SQL Data Type
Identity
Allow Null
Primary Key
SubjectID
int
Yes
No
Yes
Subject
nvarchar(50)
No
No
No
Licensed to [email protected]
Database Design and Development
285
Table 7.6. The HelpDeskStatus table
Column Name
SQL Data Type
Identity
Allow Null
Primary Key
StatusID
int
Yes
No
Yes
Status
nvarchar(50)
No
No
No
Using SQL Scripts
Yes, there’s a lot of data to type in! While we recommend that you create the tables
yourself by defining the fields outlined here, you can achieve the same goal using
an SQL script that’s included in this book’s code archive. This script contains
SQL code that SQL Server understands, and contains instructions that create data
structures (you’ll learn about SQL in
Chapter 8
). If you want to use the downloadable script, we recommend you have a look over the following tables to get an idea of the structures we’ll be creating, then read
the section called “Executing
We already have a clear idea of the data we’ll store in the Employees and Departments tables. The other tables will be used to store help desk requests; we’ll discuss these in more detail in the following pages.
Executing SQL Scripts
If you prefer not to create the data tables manually, you can use the
CreateTables.sql
script included in the book’s code archive to create the tables for you. This script
is most easily used with SQL Server Management Studio. After you log in, click the
New Query
button on the toolbar (or select
File
>
New
>
Query with Current Connection
). Paste the contents of the
CreateTables.sql
script into the window that displays, and
press
F5
to execute the commands. Note that if you have already created the Employees table, you should remove the CREATE TABLE command that creates this table
before
you hit
F5
.
The SQL script included in the code archive contains all the commands required
for this chapter—it even creates the sample data and table references that we’ll
cover later.
Licensed to [email protected]
286
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
Populating the Data Tables
If tables represent drawers in a filing cabinet, rows represent individual paper records
in those drawers. Let’s imagine for a moment that our intranet web application is
a real application. As people begin to register and interact with the application,
rows are created within the various tables, and are filled up with information about
those people.
Once the data structures are in place, adding rows of data is as easy as typing information into the cells in the
Datasheet View
of a table, which looks a bit like a spreadsheet. To access it, right-click on the table and select
Show Table Data
in
Visual Web Developer, or
Open Table
in SQL Server Management Studio. You can
use the dialog that opens to start adding data. Let’s add some sample data to the
tables you’ve just created, so that we can test the Dorknozzle database as we develop
the application.
Table 7.7
to
Table 7.11
represent the tables and data you should add.
Inserting Data and Identity Columns
If you correctly set the ID column as an identity column, you won’t be allowed
to specify the values manually—the ID values will be generated for you automatically. You need to be careful, because an ID value will never be generated twice on the same table. So even if you delete all the rows in a table, the database will
not generate an ID with the value of 1; instead, it will continue creating new values
from the last value that was generated for you.
Keep in mind that a new row is saved to the database at the moment that you move
on to the next row. It’s very important that you remember this when you reach the
last row, as you’ll need to move to an empty row even if you aren’t adding any more
records.
Licensed to [email protected]
Database Design and Development
