- •Table of Contents
- •Preface
- •What is ASP.NET?
- •Installing the Required Software
- •Installing the Web Server
- •Installing Internet Information Services (IIS)
- •Installing Cassini
- •Installing the .NET Framework and the SDK
- •Installing the .NET Framework
- •Installing the SDK
- •Configuring the Web Server
- •Configuring IIS
- •Configuring Cassini
- •Where do I Put my Files?
- •Using localhost
- •Virtual Directories
- •Using Cassini
- •Installing SQL Server 2005 Express Edition
- •Installing SQL Server Management Studio Express
- •Installing Visual Web Developer 2005
- •Writing your First ASP.NET Page
- •Getting Help
- •Summary
- •ASP.NET Basics
- •ASP.NET Page Structure
- •Directives
- •Code Declaration Blocks
- •Comments in VB and C# Code
- •Code Render Blocks
- •ASP.NET Server Controls
- •Server-side Comments
- •Literal Text and HTML Tags
- •View State
- •Working with Directives
- •ASP.NET Languages
- •Visual Basic
- •Summary
- •VB and C# Programming Basics
- •Programming Basics
- •Control Events and Subroutines
- •Page Events
- •Variables and Variable Declaration
- •Arrays
- •Functions
- •Operators
- •Breaking Long Lines of Code
- •Conditional Logic
- •Loops
- •Object Oriented Programming Concepts
- •Objects and Classes
- •Properties
- •Methods
- •Classes
- •Constructors
- •Scope
- •Events
- •Understanding Inheritance
- •Objects In .NET
- •Namespaces
- •Using Code-behind Files
- •Summary
- •Constructing ASP.NET Web Pages
- •Web Forms
- •HTML Server Controls
- •Using the HTML Server Controls
- •Web Server Controls
- •Standard Web Server Controls
- •Label
- •Literal
- •TextBox
- •HiddenField
- •Button
- •ImageButton
- •LinkButton
- •HyperLink
- •CheckBox
- •RadioButton
- •Image
- •ImageMap
- •PlaceHolder
- •Panel
- •List Controls
- •DropDownList
- •ListBox
- •RadioButtonList
- •CheckBoxList
- •BulletedList
- •Advanced Controls
- •Calendar
- •AdRotator
- •TreeView
- •SiteMapPath
- •Menu
- •MultiView
- •Wizard
- •FileUpload
- •Web User Controls
- •Creating a Web User Control
- •Using the Web User Control
- •Master Pages
- •Using Cascading Style Sheets (CSS)
- •Types of Styles and Style Sheets
- •Style Properties
- •The CssClass Property
- •Summary
- •Building Web Applications
- •Introducing the Dorknozzle Project
- •Using Visual Web Developer
- •Meeting the Features
- •The Solution Explorer
- •The Web Forms Designer
- •The Code Editor
- •IntelliSense
- •The Toolbox
- •The Properties Window
- •Executing your Project
- •Using Visual Web Developer’s Built-in Web Server
- •Using IIS
- •Using IIS with Visual Web Developer
- •Core Web Application Features
- •Web.config
- •Global.asax
- •Using Application State
- •Working with User Sessions
- •Using the Cache Object
- •Using Cookies
- •Starting the Dorknozzle Project
- •Preparing the Sitemap
- •Using Themes, Skins, and Styles
- •Creating a New Theme Folder
- •Creating a New Style Sheet
- •Styling Web Server Controls
- •Adding a Skin
- •Applying the Theme
- •Building the Master Page
- •Using the Master Page
- •Extending Dorknozzle
- •Debugging and Error Handling
- •Debugging with Visual Web Developer
- •Other Kinds of Errors
- •Custom Errors
- •Handling Exceptions Locally
- •Summary
- •Using the Validation Controls
- •Enforcing Validation on the Server
- •Using Validation Controls
- •RequiredFieldValidator
- •CompareValidator
- •RangeValidator
- •ValidationSummary
- •RegularExpressionValidator
- •Some Useful Regular Expressions
- •CustomValidator
- •Validation Groups
- •Updating Dorknozzle
- •Summary
- •What is a Database?
- •Creating your First Database
- •Creating a New Database Using Visual Web Developer
- •Creating Database Tables
- •Data Types
- •Column Properties
- •Primary Keys
- •Creating the Employees Table
- •Creating the Remaining Tables
- •Executing SQL Scripts
- •Populating the Data Tables
- •Relational Database Design Concepts
- •Foreign Keys
- •Using Database Diagrams
- •Diagrams and Table Relationships
- •One-to-one Relationships
- •One-to-many Relationships
- •Many-to-many Relationships
- •Summary
- •Speaking SQL
- •Reading Data from a Single Table
- •Using the SELECT Statement
- •Selecting Certain Fields
- •Selecting Unique Data with DISTINCT
- •Row Filtering with WHERE
- •Selecting Ranges of Values with BETWEEN
- •Matching Patterns with LIKE
- •Using the IN Operator
- •Sorting Results Using ORDER BY
- •Limiting the Number of Results with TOP
- •Reading Data from Multiple Tables
- •Subqueries
- •Table Joins
- •Expressions and Operators
- •Transact-SQL Functions
- •Arithmetic Functions
- •String Functions
- •Date and Time Functions
- •Working with Groups of Values
- •The COUNT Function
- •Grouping Records Using GROUP BY
- •Filtering Groups Using HAVING
- •The SUM, AVG, MIN, and MAX Functions
- •Updating Existing Data
- •The INSERT Statement
- •The UPDATE Statement
- •The DELETE Statement
- •Stored Procedures
- •Summary
- •Introducing ADO.NET
- •Importing the SqlClient Namespace
- •Defining the Database Connection
- •Preparing the Command
- •Executing the Command
- •Setting up Database Authentication
- •Reading the Data
- •Using Parameters with Queries
- •Bulletproofing Data Access Code
- •Using the Repeater Control
- •More Data Binding
- •Inserting Records
- •Updating Records
- •Deleting Records
- •Using Stored Procedures
- •Summary
- •DataList Basics
- •Handling DataList Events
- •Editing DataList Items and Using Templates
- •DataList and Visual Web Developer
- •Styling the DataList
- •Summary
- •Using the GridView Control
- •Customizing the GridView Columns
- •Styling the GridView with Templates, Skins, and CSS
- •Selecting Grid Records
- •Using the DetailsView Control
- •Styling the DetailsView
- •GridView and DetailsView Events
- •Entering Edit Mode
- •Using Templates
- •Updating DetailsView Records
- •Summary
- •Advanced Data Access
- •Using Data Source Controls
- •Binding the GridView to a SqlDataSource
- •Binding the DetailsView to a SqlDataSource
- •Displaying Lists in DetailsView
- •More on SqlDataSource
- •Working with Data Sets and Data Tables
- •What is a Data Set Made From?
- •Binding DataSets to Controls
- •Implementing Paging
- •Storing Data Sets in View State
- •Implementing Sorting
- •Filtering Data
- •Updating a Database from a Modified DataSet
- •Summary
- •Security and User Authentication
- •Basic Security Guidelines
- •Securing ASP.NET 2.0 Applications
- •Working with Forms Authentication
- •Authenticating Users
- •Working with Hard-coded User Accounts
- •Configuring Forms Authentication
- •Configuring Forms Authorization
- •Storing Users in Web.config
- •Hashing Passwords
- •Logging Users Out
- •ASP.NET 2.0 Memberships and Roles
- •Creating the Membership Data Structures
- •Using your Database to Store Membership Data
- •Using the ASP.NET Web Site Configuration Tool
- •Creating Users and Roles
- •Changing Password Strength Requirements
- •Securing your Web Application
- •Using the ASP.NET Login Controls
- •Authenticating Users
- •Customizing User Display
- •Summary
- •Working with Files and Email
- •Writing and Reading Text Files
- •Setting Up Security
- •Writing Content to a Text File
- •Reading Content from a Text File
- •Accessing Directories and Directory Information
- •Working with Directory and File Paths
- •Uploading Files
- •Sending Email with ASP.NET
- •Configuring the SMTP Server
- •Sending a Test Email
- •Creating the Company Newsletter Page
- •Summary
- •The WebControl Class
- •Properties
- •Methods
- •Standard Web Controls
- •AdRotator
- •Properties
- •Events
- •BulletedList
- •Properties
- •Events
- •Button
- •Properties
- •Events
- •Calendar
- •Properties
- •Events
- •CheckBox
- •Properties
- •Events
- •CheckBoxList
- •Properties
- •Events
- •DropDownList
- •Properties
- •Events
- •FileUpload
- •Properties
- •Methods
- •HiddenField
- •Properties
- •HyperLink
- •Properties
- •Image
- •Properties
- •ImageButton
- •Properties
- •Events
- •ImageMap
- •Properties
- •Events
- •Label
- •Properties
- •LinkButton
- •Properties
- •Events
- •ListBox
- •Properties
- •Events
- •Literal
- •Properties
- •MultiView
- •Properties
- •Methods
- •Events
- •Panel
- •Properties
- •PlaceHolder
- •Properties
- •RadioButton
- •Properties
- •Events
- •RadioButtonList
- •Properties
- •Events
- •TextBox
- •Properties
- •Events
- •Properties
- •Validation Controls
- •CompareValidator
- •Properties
- •Methods
- •CustomValidator
- •Methods
- •Events
- •RangeValidator
- •Properties
- •Methods
- •RegularExpressionValidator
- •Properties
- •Methods
- •RequiredFieldValidator
- •Properties
- •Methods
- •ValidationSummary
- •Properties
- •Navigation Web Controls
- •SiteMapPath
- •Properties
- •Methods
- •Events
- •Menu
- •Properties
- •Methods
- •Events
- •TreeView
- •Properties
- •Methods
- •Events
- •HTML Server Controls
- •HtmlAnchor Control
- •Properties
- •Events
- •HtmlButton Control
- •Properties
- •Events
- •HtmlForm Control
- •Properties
- •HtmlGeneric Control
- •Properties
- •HtmlImage Control
- •Properties
- •HtmlInputButton Control
- •Properties
- •Events
- •HtmlInputCheckBox Control
- •Properties
- •Events
- •HtmlInputFile Control
- •Properties
- •HtmlInputHidden Control
- •Properties
- •HtmlInputImage Control
- •Properties
- •Events
- •HtmlInputRadioButton Control
- •Properties
- •Events
- •HtmlInputText Control
- •Properties
- •Events
- •HtmlSelect Control
- •Properties
- •Events
- •HtmlTable Control
- •Properties
- •HtmlTableCell Control
- •Properties
- •HtmlTableRow Control
- •Properties
- •HtmlTextArea Control
- •Properties
- •Events
- •Index
Chapter 7: Database Design and Development
isting departments in the Department table. However, as with primary keys, just having the correct fields in place doesn’t mean that our data is guaranteed to be correct.
For example, try setting the DepartmentID field for one of the employees to 123. SQL Server won’t mind making the change for you, so if you tried this in practice, you’d end up storing invalid data. However, after we set the foreign keys correctly, SQL Server will be able to ensure the integrity of our data—specifically, it will forbid us to assign employees to nonexistent departments, or to delete departments with which employees are associated.
The easiest way to create foreign keys using Visual Web Developer or SQL Server Management Studio is through database diagrams, so let’s learn about them.
Using Database Diagrams
To keep the data consistent, the Dorknozzle database really should contain quite a few foreign keys. The good news is that you have access to a great feature called database diagrams, which makes it a cinch to create foreign keys. You can define the table relationships visually using the database diagrams tool in Visual Web Developer or SQL Server Management Studio, and have the foreign keys generated for you.
Database diagrams weren’t created specifically for the purpose of adding foreign keys. The primary use of diagrams is to offer a visual representation of the tables in your database and the relationships that exist between them, to help you to design the structure of your database. However, the diagrams editor included in Visual Web Developer and SQL Server Management Studio is very powerful, so you can use the diagrams to create new tables, modify the structure of existing tables, or add foreign keys.
Let’s start by creating a diagram for the Dorknozzle database. To create a database diagram in Visual Web Developer, right-click the Database Diagrams node, and select Add New Diagram, as shown in Figure 7.15.
The process is similar in SQL Server Management Studio, which, as Figure 7.16 illustrates, has a similar menu.
The first time you try to create a diagram, you’ll be asked to confirm the creation of the database structures that support diagrams. Select Yes from the dialog, which should look like the one shown in Figure 7.17.
280
Using Database Diagrams
Figure 7.15. Creating a database diagram with Visual Web Developer
Figure 7.16. Creating a database diagram with SQL Server Management Studio
Figure 7.17. Adding support for database diagrams
281
Chapter 7: Database Design and Development
Figure 7.18. Adding tables to the diagram
Next, a dialog like the one in Figure 7.18 will ask you which of your database tables you want included in the diagram. If you’re working with a database that comprises many tables, you may want to have diagrams built to represent specific pieces of functionality, but we want to create a diagram that includes all the tables in our database.
Click Add until all the tables are added to the diagram. As you click Add, the tables will be removed from the list and will appear in the diagram. Once you’ve added all the tables, click Close. You’ll see a window in which all the tables are clearly displayed—something like Figure 7.19.
You’ll probably need to tweak their positioning and dimensions so they fit nicely into the window. The zooming feature may prove useful here! Select File > Save Diagram1 (or similar) to save your new diagram. Enter Dorknozzle for the diagram’s name.
Now, if you right-click any table in the diagram, you’ll gain access to a plethora of possibilities, as Figure 7.20 reveals. This menu, along with the other diagramming features, are identical in Visual Web Developer and SQL Server Management Studio.
282
Using Database Diagrams
Figure 7.19. Visualizing data tables using a diagram
Figure 7.20. The many features of the diagram editor
283
Chapter 7: Database Design and Development
Expanding the Table View submenu gives you more options for displaying your table. If you choose Standard, you’ll see a full-blown version of the table definition; as Figure 7.21 shows, you can change the table structure directly in the diagram! The diagraming features provided for free are extremely powerful and useful.
Figure 7.21. The standard table view
Implementing Relationships in the Dorknozzle
Database
Every table in the Dorknozzle database has a relationship with another table. To create a foreign key using the diagram, click the gray square to the left-hand side of the column for which you want to create the foreign key, and drag it over the table to which you want it to relate.
Let’s give it a try. Start by dragging the DepartmentID column of the Employees table over the DepartmentID column of the Departments table, as illustrated in Figure 7.22.
Figure 7.22. Creating a link between Employees and Departments
284
Implementing Relationships in the Dorknozzle Database
The designer will open a dialog that shows the details of the new foreign key, like the one shown in Figure 7.23.
Figure 7.23. Adding a foreign key
Ensure that your data matches that shown in Figure 7.23, and click OK. A new dialog like the one shown in Figure 7.24 will appear, allowing you to tweak numerous options that relate to the new foreign key. Leave the default options as they are for now (though we’ll discuss them shortly), and click OK to finish up.
Figure 7.24. Editing the foreign key options
285
Chapter 7: Database Design and Development
After creating the foreign key, make a quick test to ensure that the relationship is indeed enforced. Try adding an employee, but set the person’s DepartmentID to 123. You should see an error like the one pictured in Figure 7.25.
Figure 7.25. The foreign key disallowing the addition of invalid data
If you tried to delete a department with which employees were associated, you’d generate a similar error.
Table 7.15 shows the foreign keys that we need to establish in the Dorknozzle database. In our project, the foreign key column has the same name as its corresponding primary key column. Go ahead and create all the foreign keys outlined in Table 7.15.
Table 7.15. The relationships in the Dorknozzle database
Primary Key Foreign Key
DepartmentID in the table Departments DepartmentID in the table Employees
EmployeeID in the table Employees |
EmployeeID in the table HelpDesk |
CategoryID in the table HelpDeskCat- |
CategoryID in the table HelpDesk |
egories |
|
SubjectID in the table HelpDeskSub- |
SubjectID in the table HelpDesk |
jects |
|
StatusID in the table HelpDeskStatus |
StatusID in the table HelpDesk |
When it’s complete, your relationship diagram should resemble Figure 7.26. After you add the relationships, save your changes by selecting File > Save Dorknozzle. When you’re asked to confirm the changes to the database tables you’re altering, click Yes.
286