- •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
32 |
Friday Evening |
That’s it. You have now created a database using T-SQL and Query Analyzer.
Creating SQL Server Tables
Now that you have a database, Music, you can add tables to it. If you recall from the previous session, the Music database contains several tables including t_bands, t_band_members, t_albums, and so on. Figure 4-3 shows the schema for the Music database.
Figure 4-3 Schema for the Music database
You are not going to create every table in the Music database, but hopefully, based on the tables you do create, you will be able to build the remaining tables. So, go create the t_bands table:
1.In Enterprise Manager, right-click on the Music database node and select New Table.
2.You see the Choose Name dialog box as shown in Figure 4-4. Enter t_bands in the “Enter a name for the table:” textbox and click OK. The table design grid is now ready for you to enter column information.
Figure 4-4 Specifying a table’s name
Session 4—Building a Database |
33 |
3.In the design grid, enter band_id in the Column Name field of the first row as shown in Figure 4-5. In the Datatype column, select int to signify that the band_id field will contain integer type data. On the same row, deselect the Allow Nulls checkbox and select the Identity checkbox. Click the Set Primary Key button (it looks like a key) on the SQL Server toolbar to make the band_id column the primary key for the t_bands table.
4.Create the band_title, music_type_id, and record_company_id columns, using Figure 4-5 as a guide.
5.Right-click the t_bands table design grid as shown in Figure 4-5. You see the Properties dialog box.
Figure 4-5 Creating table columns
6.Select the Indexes/Keys tab and click the New button to create a new index on the band_title column.
7.Select band_title from the Column name drop-down box and enter IX_band_title in the Index name text box.
8.Select the Create UNIQUE checkbox and the Index option button, and click the Close button (as shown in Figure 4-6).
9.Save and close the t_bands design grid.
34 |
Friday Evening |
Figure 4-6 Creating a unique constraint
To create the t_bands table using T-SQL, execute the following commands in Query Analyzer:
USE Music GO
CREATE TABLE [dbo].[t_bands] (
[band_id] [int] IDENTITY (1, 1) NOT NULL , [band_title] [varchar] (100) NOT NULL , [music_type_id] [int] NOT NULL , [record_company_id] [int] NOT NULL
) ON [PRIMARY] GO
ALTER TABLE [dbo].[t_bands] WITH NOCHECK ADD CONSTRAINT [PK_t_bands] PRIMARY KEY NONCLUSTERED
(
[band_id]
)ON [PRIMARY] ,
CONSTRAINT [IX_bands_title] UNIQUE NONCLUSTERED
(
[band_title]
)ON [PRIMARY]
GO
The second command, or the first command after USE Music, creates the t_bands table using the CREATE TABLE statement. The third command, ALTER TABLE, creates two constraints on the t_bands table. The first constraint, named PK_t_bands, is placed on
the band_id field. The PK_t_bands constraint is the primary key for the t_bands table. The second constraint, named IX_bands_title, is placed on the band_title column and ensures that the band title is unique.
Session 4—Building a Database |
35 |
Now create t_albums as shown in Figure 4-7.
Figure 4-7 t_albums table
Next you need to create a few constraints on the t_albums table by following these steps:
1.Open the Properties dialog box for the t_albums table and select the Indexes/ Keys tab.
2.Create a constraint named IX_band_albums based on two columns, album_title and band_id. Make this constraint unique by selecting the “Create UNIQUE” checkbox. This constraint ensures that a band doesn’t have albums duplicated in the table. In this example, of course, you could assume that a band will never release two albums with the same name. At this point, you should start to realize that constraints are basically used to enforce business rules on our tables.
3.Close the Properties dialog box by selecting the “Close” button.
4.Expand the Music database node so you can see a complete listing of all database objects (that is, Diagrams, Tables, View, Stored Procedures, and so on).
5.Right-click the Diagrams node and select “New Database Diagram.”
6.Work your way through the Create Database Diagram Wizard. Make sure that you add the t_albums and t_bands tables to the diagram. It is through this database diagram that you are going to create a foreign key. Specifically, you are going to create a foreign key to the t_bands table to ensure that all entries in the band_id column of the t_albums table have a corresponding band_id in the t_bands table. (This is simply a business rule. You can’t have an album pop out of thin air. It has to be recorded by a band.)
7.Once the diagram has been created and the two tables mentioned in step 6 are on the diagram, drag the band_id entry in the t_albums table and drop it on t_bands.
At this point, you see the Create Relationship dialog box shown in Figure 4-8. On the Create Relationship dialog box, ensure that the primary key table is t_bands and the primary key column is band_id. Also ensure that the foreign key table is t_albums and the foreign key column is band_id. Click OK.
That’s it. You have created the t_albums table using Enterprise Manager. Listing 4-1 shows the T-SQL script you could execute to create the t_albums table and its associated constraints.
36 |
Friday Evening |
Figure 4-8 Creating a relation between t_bands and t_albums
Listing 4-1 An example of a T-SQL script
CREATE TABLE [dbo].[t_albums] (
[album_id] [int] IDENTITY (1, 1) NOT NULL , [album_title] [varchar] (255) NOT NULL , [album_publish_date] [datetime] NOT NULL , [band_id] [int] NOT NULL ,
[album_price] [smallmoney] NOT NULL ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_albums] WITH NOCHECK ADD
CONSTRAINT [DF_t_albums_album_publish_date] DEFAULT (getdate()) FOR [album_publish_date],
CONSTRAINT [DF_t_albums_album_price] DEFAULT (0.00) FOR [album_price], CONSTRAINT [PK_t_albums] PRIMARY KEY NONCLUSTERED
(
[album_id]
)ON [PRIMARY] ,
CONSTRAINT [IX_band_albums] UNIQUE NONCLUSTERED
(
[album_title], [band_id]
)ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_albums] ADD
CONSTRAINT [FK_t_albums_t_bands] FOREIGN KEY
(
[band_id]
) REFERENCES [dbo].[t_bands] (