- •Preface
- •Who Should Read This Book
- •Organization and Presentation
- •Contacting the Authors
- •Acknowledgments
- •Contents
- •Introduction
- •Why Microsoft .NET?
- •The Microsoft .NET Architecture
- •Internet Standards
- •The Evolution of ASP
- •The Benefits of ASP.NET
- •What Is .NET?
- •.NET Experiences
- •.NET Clients
- •.NET Services
- •.NET Servers
- •Review
- •Quiz Yourself
- •Installation Requirements
- •Installing ASP.NET and ADO.NET
- •Installing the .NET Framework SDK
- •Testing Your Installation
- •Support for .NET
- •Review
- •Quiz Yourself
- •Designing a Database
- •Normalization of Data
- •Security Considerations
- •Review
- •Quiz Yourself
- •Creating a Database
- •Creating SQL Server Tables
- •Creating a View
- •Creating a Stored Procedure
- •Creating a Trigger
- •Review
- •Quiz Yourself
- •INSERT Statements
- •DELETE Statements
- •UPDATE Statements
- •SELECT Statements
- •Review
- •Quiz Yourself
- •The XML Design Specs
- •The Structure of XML Documents
- •XML Syntax
- •XML and the .NET Framework
- •Review
- •Quiz Yourself
- •ASP.NET Events
- •Page Directives
- •Namespaces
- •Choosing a Language
- •Review
- •Quiz Yourself
- •Introducing HTML Controls
- •Using HTML controls
- •How HTML controls work
- •Intrinsic HTML controls
- •HTML Control Events
- •The Page_OnLoad event
- •Custom event handlers
- •Review
- •Quiz Yourself
- •Intrinsic Controls
- •Using intrinsic controls
- •Handling intrinsic Web control events
- •List Controls
- •Rich Controls
- •Review
- •Quiz Yourself
- •Creating a User Control
- •Adding User Control Properties
- •Writing Custom Control Methods
- •Implementing User Control Events
- •Review
- •Quiz Yourself
- •Common Aspects of Validation Controls
- •Display property
- •Type Property
- •Operator Property
- •Using Validation Controls
- •RequiredFieldValidator
- •RegularExpressionValidator
- •CompareValidator
- •RangeValidator
- •CustomValidator
- •ValidationSummaryx
- •Review
- •Quiz Yourself
- •Maintaining State Out of Process for Scalability
- •No More Cookies but Plenty of Milk!
- •Out of Process State Management
- •Review
- •Quiz Yourself
- •Introducing the Key Security Mechanisms
- •Web.config and Security
- •Special identities
- •Using request types to limit access
- •New Tricks for Forms-based Authentication
- •Using the Passport Authentication Provider
- •Review
- •Quiz Yourself
- •ASP.NET Updates to the ASP Response Model
- •Caching with ASP.NET
- •Page Output Caching
- •Absolute cache expiration
- •Sliding cache expiration
- •Fragment Caching
- •Page Data Caching
- •Expiration
- •File and Key Dependency and Scavenging
- •Review
- •Quiz Yourself
- •A Brief History of Microsoft Data Access
- •Differences between ADO and ADO.NET
- •Transmission formats
- •Connected versus disconnected datasets
- •COM marshaling versus text-based data transmission
- •Variant versus strongly typed data
- •Data schema
- •ADO.NET Managed Provider Versus SQL Managed Provider
- •Review
- •Quiz Yourself
- •Review
- •Quiz Yourself
- •Creating a Connection
- •Opening a Connection
- •Using Transactions
- •Review
- •Quiz Yourself
- •Building a Command
- •Connection property
- •CommandText property
- •CommandType property
- •CommandTimeout property
- •Appending parameters
- •Executing a Command
- •ExecuteNonQuery method
- •Prepare method
- •ExecuteReader method
- •Review
- •Quiz Yourself
- •Introducing DataReaders
- •Using DataReader Properties
- •Item property
- •FieldCount property
- •IsClosed property
- •RecordsAffected property
- •Using DataReader Methods
- •Read method
- •GetValue method
- •Get[Data Type] methods
- •GetOrdinal method
- •GetName method
- •Close method
- •Review
- •Quiz Yourself
- •Constructing a DataAdapter Object
- •SelectCommand property
- •UpdateCommand, DeleteCommand, and InsertCommand properties
- •Fill method
- •Update method
- •Dispose method
- •Using DataSet Objects
- •DataSetName property
- •CaseSensitive property
- •Review
- •Quiz Yourself
- •Constructing a DataSet
- •Tables property
- •TablesCollection Object
- •Count property
- •Item property
- •Contains method
- •CanRemove method
- •Remove method
- •Add method
- •DataTable Objects
- •CaseSensitive property
- •ChildRelations property
- •Columns property
- •Constraints property
- •DataSet property
- •DefaultView property
- •ParentRelations property
- •PrimaryKey property
- •Rows property
- •Dispose method
- •NewRow method
- •Review
- •Quiz Yourself
- •What Is Data Binding?
- •Binding to Arrays and Extended Object Types
- •Binding to Database Data
- •Binding to XML
- •TreeView Control
- •Implement the TreeView server control
- •Review
- •Quiz Yourself
- •DataGrid Control Basics
- •Binding a set of data to a DataGrid control
- •Formatting the output of a DataGrid control
- •Master/Detail Relationships with the DataGrid Control
- •Populating the Master control
- •Filtering the detail listing
- •Review
- •QUIZ YOURSELF
- •Updating Your Data
- •Handling the OnEditCommand Event
- •Handling the OnCancelCommand Event
- •Handling the OnUpdateCommand Event
- •Checking that the user input has been validated
- •Executing the update process
- •Deleting Data with the OnDeleteCommand Event
- •Sorting Columns with the DataGrid Control
- •Review
- •Quiz Yourself
- •What Is Data Shaping?
- •Why Shape Your Data?
- •DataSet Object
- •Shaping Data with the Relations Method
- •Review
- •Quiz Yourself
- •OLEDBError Object Description
- •OLEDBError Object Properties
- •OLEDBError Object Methods
- •OLEDBException Properties
- •Writing Errors to the Event Log
- •Review
- •Quiz Yourself
- •Introducing SOAP
- •Accessing Remote Data with SOAP
- •SOAP Discovery (DISCO)
- •Web Service Description Language (WSDL)
- •Using SOAP with ASP.NET
- •Review
- •Quiz Yourself
- •Developing a Web Service
- •Consuming a Web Service
- •Review
- •Quiz Yourself
- •ASP and ASP.NET Compatibility
- •Scripting language limitations
- •Rendering HTML page elements
- •Using script blocks
- •Syntax differences and language modifications
- •Running ASP Pages under Microsoft.NET
- •Using VB6 Components with ASP.NET
- •Review
- •Quiz Yourself
- •Preparing a Migration Path
- •ADO and ADO.NET Compatibility
- •Running ADO under ASP.NET
- •Early Binding ADO COM Objects in ASP.NET
- •Review
- •Quiz Yourself
- •Answers to Part Reviews
- •Friday Evening Review Answers
- •Saturday Morning Review Answers
- •Saturday Afternoon Review Answers
- •Saturday Evening Review Answers
- •Sunday Morning Review Answers
- •Sunday Afternoon Review Answers
- •What’s on the CD-ROM
- •System Requirements
- •Using the CD with Windows
- •What’s on the CD
- •The Software Directory
- •Troubleshooting
- •ADO.NET Class Descriptions
- •Coding Differences in ASP and ASP.NET
- •Retrieving a Table from a Database
- •Displaying a Table from a Database
- •Variable Declarations
- •Statements
- •Comments
- •Indexed Property Access
- •Using Arrays
- •Initializing Variables
- •If Statements
- •Case Statements
- •For Loops
- •While Loops
- •String Concatenation
- •Error Handling
- •Conversion of Variable Types
- •Index
26 |
Friday Evening |
You could certainly modify your table design further. But at some point you need to start considering performance. Performance can be adversely impacted if, on a regular basis, you need to join multiple tables with a lot of data. We recommend that you keep the number of tables in your database to a minimum while following the normalization rules as closely as possible. You will soon learn that database design is as much art as it is science.
Security Considerations
Probably the most overlooked aspect of database design is security, when it should be a major consideration. By not securing your database and thereby your data, you are asking for trouble. Not all data is intended for everyone’s eyes, and not everyone should have the ability to manipulate your data and definitely not your database’s structure. The majority of your database users will only need and should only be granted read (or select) access. When designing your database, you should establish a list of policies and users for your database. A database user is anyone who needs access to any part of your database. The highest level of user is the database administrator who will have access to all database objects and data. Other users will only have access to certain objects and data. The average end user will only have access to certain objects, but should never have the ability to alter your database structure. It never ceases to amaze us how many organizations have one “global” user that has complete control of the database. This is typically a bad scenario, not because people are intentionally malicious, but because they are people and no one is perfect. The impact of a simple mistake can take hours and even days to reverse, if reversal is possible at all. Policies are basically rules that define which actions a user can perform on your database. Most RDMSs enable you to assign a separate set of policies, or rights, for each object in your database. User rights generally fall into one of six different categories:
SELECT enables the user to view data.
INSERT enables the user to create new data.
UPDATE enables the user to modify existing data.
DELETE enables the user to delete data.
EXECUTE enables the user to execute a stored procedure.
ALTER enables the user to alter database structure.
We will discuss stored procedures in Session 4, “Building a Database.”
Note
Each user in a database should have a unique user name and password combination. This will enable your RDMS to enforce the security policies you have established for the user.
Session 3—Designing a Database |
27 |
REVIEW
In order to have a truly active Web site, you need to have some sort of data store from which to retrieve personalized information. In most cases, this “data store” is a relational database management system (RDBMS) such as SQL Server, Oracle, or Microsoft Access. A database can consist of many types of objects such as tables and constraints. Designing the structure of your tables (and other objects) and their interactions is just as much art as it is science. However, the database normalization rules provide good guidelines to help you along your way.
QUIZ YOURSELF
1.What is the importance of the primary key in table design? (See “Designing a Database.”)
2.What is the difference between a primary key and a foreign key? (See “Designing a Database.”)
3.What is the purpose of normalization? (See “Normalization of Data.”)
S E S S I O N
4
Building a Database
Session Checklist
Building a database using SQL Server
Using database objects
In the previous session, we explained how to design and build a database. In this session, you’ll build the Music database using SQL Server. (If you’re going to build the database as you go through this session, use either SQL Server 7.0 or 2000.)
When working with SQL Server, you can create a database and its related objects in one of two ways. Probably the easiest method is to use Enterprise Manager. Enterprise Manager
provides a user interface that enables you to graphically create a database, tables, constraints, and so on. If you installed SQL Server on your local machine, Enterprise Manager should be located in the SQL Server program group on the Start menu.
The second method of creating a database with SQL Server is to execute Transact-SQL (T-SQL) commands against your SQL Server. Although writing T-SQL commands is a little more difficult than using Enterprise Manager, you have greater control of the objects you create and can save time.
Which method you use is a matter of personal preference. Throughout this session, we demonstrate creating database objects with both methods.
Creating a Database
The first step in building a database with SQL Server is to actually create the database. That’s right. SQL Server is a piece of software that runs on a computer, or server. Once the SQL Server software is installed you can create a database (or databases) with the SQL Server software
30 |
Friday Evening |
that is then managed by that SQL Server software. Many people refer to SQL Server as a database, which it is, sort of. SQL Server is actually an application, a Relational Database Management System (RDBMS), which can contain multiple databases.
We will be using SQL Server 7.0 to create the database in this session. If you are using SQL Server 2000, the steps will be slightly different.
Note
OK, let’s create the Music database. You’ll start by creating the database using Enterprise Manager and perform the following steps:
1.Expand the SQL Server Group item, if it isn’t already expanded, in the Enterprise Manager tree. Once expanded you should see a list of SQL Servers that are registered with Enterprise Manager.
2.Right-click the SQL Server in which you want to create the Music database.
3.Select New Database. Figure 4-1 illustrates steps 1, 2, and 3.
4.You see the Database Properties dialog box, shown in Figure 4-1. On the General tab, enter Music in the Name field. The Database Properties dialog box allows you to control other features of your database such as file growth, maximum database size, transaction log files, and so on. For the sake of brevity, accept the defaults.
Figure 4-1 Specifying database properties with Enterprise Manager
That’s it. You have created a SQL Server database using Enterprise Manager. If you want to create a database with T-SQL, follow these steps:
Session 4—Building a Database |
31 |
1.Select Start Programs Microsoft SQL Server Query Analyzer to open SQL Server’s Query Analyzer.
2.You see the Connect to SQL Server dialog box. Select the SQL Server on which you would like to create the Music database from the SQL Server drop-down box. Select the Use SQL Server authentication radio button. Now enter the appropriate authentication information in the Login Name and Password fields as shown in Figure 4-2.
Figure 4-2 Query Analyzer logon
3.In the Query Analyzer window, enter the following T-SQL statement:
USE master GO
CREATE DATABASE Music ON PRIMARY ( NAME = MusicData,
FILENAME = ‘C:\MSSQL7\data\MusicData.mdf’
)
In the previous script, you may need to alter the FILENAME string so that it reflects a valid path on your computer.
Note
In step 3, you essentially created a database named Music and specified that the data should be stored in the MusicData.mdf file located, in this example, in the C:\MSSQL7\data directory. The CREATE DATABASE statement accepts many other parameters, such as MAXSIZE, FILEGROWTH, SIZE, and so on. However, again, for the sake of brevity, you used the SQL Server defaults.
Once you have entered the previous SQL statement in the Query Analyzer window, hit the F5 button, which will execute the SQL script.
Note