- •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
S E S S I O N
5
Using SQL: A Primer
Session Checklist
Understanding the usefulness of SQL
Writing SELECT, INSERT, UPDATE, and DELETE SQL commands
After you have built a database, whether it be SQL Server or Oracle or Access, a time will come when you need to do something with it, more than likely retrieve and modify data. When dealing with data in a database, it turns out that there are four
actions you will most frequently perform: create, retrieve, update, and delete. Collectively these activities are referred to as CRUD. If someone, probably a manager, asks you for a CRUD diagram they are simply asking for a diagram representing what commands or actions you execute against the data store.
In order to execute CRUD commands against a relational database, you need to use Structured Query Language or SQL (pronounced sequel). SQL, as a querying language, is composed of a series of statements and clauses, which, when combined, perform different actions. In this session, we will address the most common SQL commands, INSERT, DELETE, UPDATE, and SELECT, and their related clauses. In order to demonstrate the use of SQL, you will execute commands against the Music database discussed in the previous session. So, if you haven’t already done so, please create the Music database.
INSERT Statements
Now that you’ve designed and constructed a database, it’s time to use it or have someone else use it. To make a database useful, it needs to contain some data. The SQL command to add data to a database is INSERT. The basic INSERT statement adds one row at a time to a table. Variations of the basic INSERT statement enable you to add multiple rows by selecting data from another table or by executing a stored procedure. In all of these cases, you must
48 |
Saturday Morning |
Session 5—Using SQL: A Primer |
49 |
of the columns into which you are inserting data. If the column into which you are inserting is of a numeric data type, you do not enclose the value in single quotes, however if you are inserting character data, you need to enclose the value in single quotes. Try running this statement:
INSERT INTO t_bands (band_title, music_type_id, record_company_id) VALUES (‘Toad The Wet Sprocket’,’1’,’1’)
You should get an error when executing this command because you are attempting to insert character data into columns that expect numeric data. Here’s the correct INSERT statement:
INSERT INTO t_bands (band_title, music_type_id, record_company_id) VALUES (‘Toad The Wet Sprocket’,1,1)
DELETE Statements
The DELETE command removes a row or multiple rows from a table. Following is the syntax for a basic DELETE statement:
DELETE FROM tablename [WHERE where expression]
Executing a DELETE statement that does not contain a WHERE clause removes all the records from a table. This is generally not what you want to do, so be careful when executing DELETE statements. Here’s an example:
DELETE FROM t_albums
This previous statement will delete all records from the t_albums table.
The WHERE clause is used to narrow the scope of our DELETE statement by specifying criteria that identify the records to delete. Here’s an example:
DELETE FROM t_albums WHERE band_id = 1
Assuming Hootie & The Blowfish have a band_id of 1 in the t_bands table, all of Hootie’s albums will be removed from the t_albums table.
The WHERE clause can consist of one expression as demonstrated with the previous DELETE statement or a series of expressions separated by Boolean operators. The Boolean operators most commonly used are AND, OR, and NOT. When using these operators together, precedence rules determine the order in which they’re evaluated. When the WHERE clause consists of statements enclosed in parentheses, the expressions in parentheses are examine first. After the expressions in parentheses are evaluated, the following rules apply:
NOT is evaluated before AND. NOT can only occur after AND. OR NOT isn’t allowed.
AND is evaluated before OR.
Let’s try it out . . .
DELETE FROM t_bands WHERE band_title = ‘Hootie & The Blowfish’ AND record_company_id = 100
50 |
Saturday Morning |
The previous statement will delete all rows from the t_bands table where the value in the band_title column is equal to Hootie & The Blowfish and the value in the record_ company_id field is 100. Based on the data we inserted earlier, no record should be deleted from the t_bands table. There is a record that where band_title equals Hootie & The Blowfish, but that record has record_company_id value of 1. Let’s try an OR . . .
DELETE FROM t_bands WHERE band_title = ‘Toad The Wet Sprocket OR record_company_id = 100
This statement will delete all rows from t_bands table where the value in the band_title column is equal to Toad The Wet Sprocket or the value in the record_company_id field is
100. So based on our sample data, one row should be deleted from the t_bands table because there is one row that contains Toad The Wet Sprocket in the band_title column, but no rows contain a record_company_id value of 100.
A WHERE clause can also contain something called a predicate. A predicate is a expression that makes a factual assertion about a column value. Some common examples of predicates are CONTAINS, LIKE, and NULL. CONTAINS returns true if the value in the specified table contains a specified value. LIKE returns true if the specified column’s data matches a specified string pattern.
Note
A string pattern can contain wildcard characters such as the percent sign (%), which matches one or more characters, and the underscore (_), which matches one character.
NULL determines whether a column contains data. Let’s try it out:
DELETE FROM t_bands WHERE CONTAINS (band_title,’Toad’)
This statement means, “delete all rows from t_bands where band_title contains Toad.” Let’s try another:
DELETE FROM t_bands WHERE band_title LIKE ‘Toad%’
Note
In SQL Server, the % is referred to as a wildcard character. The % wildcard character matches any string of zero or more characters. So placing % in our previous delete statement instructed SQL Server to delete all records in the t_bands table where the value in the band_title column begins with “Toad.”
UPDATE Statements
The UPDATE statement enables you to change the data within existing rows. Following is the syntax for a simple UPDATE statement:
UPDATE tablename SET columnname = contstant [AND columnname = constant ...] [WHERE where-expression]