When you interact with a data
source through ADO.NET, you use SQL to retrieve, modify, and update
information. In some cases, ADO.NET will hide some of the details for
you or even generate required SQL statements automatically. However, to
design an efficient database application with a minimal amount of
frustration, you need to understand the basic concepts of SQL.
SQL (Structured Query
Language) is a standard data access language used to interact with
relational databases. Different databases differ in their support of SQL
or add other features, but the core commands used to select, add, and
modify data are common. In a database product such as SQL Server, it's
possible to use SQL to create fairly sophisticated SQL scripts for
stored procedures and triggers (although they have little of the power
of a full object-oriented programming language). When working with
ADO.NET, however, you'll probably use only the following standard types
of SQL statements:
A Select statement retrieves records.
An Update statement modifies existing records.
An Insert statement adds a new record.
A Delete statement deletes existing records.
If you already have a good
understanding of SQL, you can skip the next few sections. Otherwise,
read on for a quick tour of SQL fundamentals.
3.1. Running Queries in Visual Studio
If you've never used SQL
before, you may want to play around with it and create some sample
queries before you start using it in an ASP.NET site. Most database
products provide some sort of tool for testing queries. If you're using a
full version of SQL Server, you can try SQL Server Management Studio.
If you don't want to use an extra tool, you can run your queries using
the Server Explorer window described earlier. Just follow these steps in
Visual Studio:
Right-click your connection, and choose New Query.
Choose the table (or tables) you want to use in your query from the Add Table dialog box (as shown in Figure 5), click Add, and then click Close.
You'll
now see a handy query-building window. You can create your query by
adding check marks next to the fields you want, or you can edit the SQL
by hand in the lower portion of the window. Best of all, if you edit the
SQL directly, you can type in anything—you don't need to stick to the
tables you selected in step 2, and you don't need to restrict yourself
to Select statements.
When you're ready to run the query, select Query Designer =>
Execute SQL from the menu. Assuming your query doesn't have any errors,
you'll get one of two results. If you're selecting records, the results
will appear at the bottom of the window. If you're deleting or updating
records, a message box will appear informing you how many records were
affected (see Figure 6).
|
When programming with
ADO.NET, it always helps to know your database. If you have information
on hand about the data types it uses, the stored procedures it provides,
and the user account you need to use, you'll be able to work more
quickly and with less chance of error.
|
|
3.2. The Select Statement
To retrieve one or more rows of data, you use a Select statement. A basic Select statement has the following structure:
SELECT [columns]
FROM [tables]
WHERE [search_condition]
ORDER BY [order_expression ASC | DESC]
This format really just
scratches the surface of SQL. If you want, you can create more
sophisticated queries that use subgrouping, averaging and totaling, and
other options (such as setting a maximum number of returned rows). By
performing this work in a query (instead of in your application), you
can often create far more efficient applications.
The next few sections
present sample Select statements. After each example, a series of
bulleted points breaks the SQL down to explain how each part of it
works.
3.2.1. A Sample Select Statement
The following is a
typical (and rather inefficient) Select statement for the pubs database.
It works with the Authors table, which contains a list of authors:
SELECT * FROM Authors
The asterisk (*)
retrieves all the columns in the table. This isn't the best approach for
a large table if you don't need all the information. It increases the
amount of data that has to be transferred and can slow down your server.
The From clause identifies that the Authors table is being used for this statement.
The
statement doesn't have a Where clause. This means all the records will
be retrieved from the database, regardless of whether it has 10 or 10
million records. This is a poor design practice, because it often leads
to applications that appear to work fine when they're first deployed but
gradually slow down as the database grows. In general, you should
always include a Where clause to limit the possible number of rows
(unless you absolutely need them all). Often, queries are limited by a
date field (for example, including all orders that were placed in the
last three months).
The
statement doesn't have an Order By clause. This is a perfectly
acceptable approach, especially if order doesn't matter or you plan to
sort the data on your own using the tools provided in ADO.NET.
3.2.2. Improving the Select Statement
Here's another example that retrieves a list of author names:
SELECT au_lname, au_fname FROM Authors WHERE State='CA' ORDER BY au_lname ASC
Only two columns are retrieved (au_lname and au_fname). They correspond to the first and last names of the author.
A
Where clause restricts results to those authors who live in the
specified state (California). Note that the Where clause requires
apostrophes around the value you want to match, because it's a text
value.
An
Order By clause sorts the information alphabetically by the author's
last name. The ASC part (for ascending) is optional, because that's the
default sort order.
3.2.3. An Alternative Select Statement
Here's one last example:
SELECT TOP 100 * FROM Sales ORDER BY ord_date DESC
This example uses the Top
clause instead of a Where statement. The database rows will be sorted in
descending order by order date, and the first 100 matching results will
be retrieved. In this case, it's the 100 most recent orders. You could
also use this type of statement to find the most expensive items you
sell or the best-performing employees.
3.2.4. The Where Clause
In many respects, the
Where clause is the most important part of the Select statement. You can
find records that match several conditions using the And keyword, and
you can find records that match any one of a series of conditions using
the Or keyword. You can also specify greater-than and less-than
comparisons by using the greater-than (>) and less-than (<)
operators.
The following is an example with a different table and a more sophisticated Where statement:
SELECT * FROM Sales WHERE ord_date < '2000/01/01' AND ord_date > '1987/01/01'
This example uses the
international date format to compare date values. Although SQL Server
supports many date formats, yyyy/mm/dd is recommended to prevent
ambiguity.
If you were using
Microsoft Access, you would need to use the U.S. date format,
mm/dd/yyyy, and replace the apostrophes around the date with the number
(#) symbol.
3.2.5. String Matching with the Like Operator
The Like operator allows you to
perform partial string matching to filter records where a particular
field starts with, ends with, or contains a certain set of characters.
For example, if you want to see all store names that start with B, you could use the following statement:
SELECT * FROM Stores WHERE stor_name LIKE 'B%'
To see a list of all stores ending with S, you would put the percent sign before the S, like this:
SELECT * FROM Stores WHERE stor_name LIKE '%S'
The third way to use the Like
operator is to return any records that contain a certain character or
sequence of characters. For example, suppose you want to see all stores
that have the word book somewhere in the name. In this case, you could use a SQL statement like this:
SELECT * FROM Stores WHERE stor_name LIKE '%book%'
By default, SQL is not case sensitive, so this syntax finds instances of BOOK, book, or any variation of mixed case.
Finally, you can indicate
one of a set of characters, rather than just any character, by listing
the allowed characters within square brackets. Here's an example:
SELECT * FROM Stores WHERE stor_name LIKE '[abcd]%'
This SQL statement will return stores with names starting with A, B, C, or D.
3.2.6. Aggregate Queries
The SQL language also defines special aggregate functions.
Aggregate functions work with a set of values but return only a single
value. For example, you can use an aggregate function to count the
number of records in a table or to calculate the average price of a
product. Table 1 lists the most commonly used aggregate functions.
Table 1. SQL Aggregate Functions
| Function | Description |
|---|
| Avg(fieldname) | Calculates the average of all values in a given numeric field |
| Sum(fieldname) | Calculates the sum of all values in a given numeric field |
| Min(fieldname) and Max(fieldname) | Finds the minimum or maximum value in a number field |
| Count(*) | Returns the number of rows in the result set |
| Count(DISTINCT fieldname) | Returns the number of unique (and non-null) rows in the result set for the specified field |
For example, here's a query that returns a single value—the number of records in the Authors table:
SELECT COUNT(*) FROM Authors
And here's how you could calculate the total quantity of all sales by adding together the qty field in each record:
SELECT SUM(qty) FROM Sales
3.3. The SQL Update Statement
The SQL Update
statement selects all the records that match a specified search
expression and then modifies them all according to an update expression.
At its simplest, the Update statement has the following format:
UPDATE [table] SET [update_expression] WHERE [search_condition]
Typically, you'll use an
Update statement to modify a single record. The following example
adjusts the phone column in a single author record. It uses the unique
author ID to find the correct row.
UPDATE Authors SET phone='408 496-2222' WHERE au_id='172-32-1176'
This statement returns the number of affected rows. (See Figure 7
for an example in Visual Studio.) However, it won't display the change.
To do that, you need to request the row by performing another Select
statement:
SELECT phone FROM Authors WHERE au_id='172-32-1176'
As with a Select statement, you can use an Update statement with several criteria:
UPDATE Authors SET au_lname='Whiteson', au_fname='John'
WHERE au_lname='White' AND au_fname='Johnson'
You can even use the
Update statement to update an entire range of matching records. The
following example increases the price of every book in the Titles table
that was published in 1991 by one dollar:
UPDATE Titles SET price=price+1
WHERE pubdate >= '1991/01/01' AND pubdate < '1992/01/01'
3.4. The SQL Insert Statement
The SQL Insert statement adds a new record to a table with the information you specify. It takes the following form:
INSERT INTO [table] ([column_list]) VALUES ([value_list])
You can provide the
information in any order you want, as long as you make sure the list of
column names and the list of values correspond exactly:
INSERT INTO Authors (au_id, au_lname, au_fname, zip, contract)
VALUES ('998-72-3566', 'Khan', 'John', 84152, 0)
This example leaves out some
information, such as the city and address, in order to provide a simple
example. However, it provides the minimum information that's required to
create a new record in the Authors table.
Remember, database
tables often have requirements that can prevent you from adding a record
unless you fill in all the fields with valid information.
Alternatively, some fields may be configured to use a default value if
left blank. In the Authors table, some fields are required, and a
special format is defined for the ZIP code and author ID.
One feature the Authors
table doesn't use is an automatically incrementing identity field. This
feature, which is supported in most relational database products,
assigns a unique value to a specified field when you perform an insert
operation. When you insert a record into a table that has a unique
incrementing ID, you shouldn't specify a value for the ID. Instead,
allow the database to choose one automatically.
|
If you're designing a database,
it's a good design to add an auto-incrementing identity field to every
table. This is the fastest, easiest, and least error-prone way to assign
a unique identification number to every record. Without an
automatically generated identity field, you'll need to go to
considerable effort to create and maintain your own unique field. Often
programmers fall into the trap of using a data field for a unique
identifier, such as a Social Security number (SSN) or a name. This
almost always leads to trouble at some inconvenient time far in the
future, when you need to add a person who doesn't have an SSN (for
example, a foreign national) or you need to account for an SSN or name
change (which will cause problems for other related tables, such as a
purchase order table that identifies the purchaser by the name or SSN
field). A much better approach is to use a unique identifier and have
the database engine assign an arbitrary unique number to every row
automatically.
If you create a table
without a unique identification column, you'll have trouble when you
need to select that specific row for deletion or updates. Selecting
records based on a text field can also lead to problems if the field
contains special embedded characters (such as apostrophes). You'll also
find it extremely awkward to create table relationships.
|
3.5. The SQL Delete Statement
The Delete statement is
even easier to use. It specifies criteria for one or more rows that you
want to remove. Be careful: once you delete a row, it's gone for good!
DELETE FROM [table] WHERE [search_condition]
The following example removes a single matching row from the Authors table:
DELETE FROM Authors WHERE au_id='172-32-1176'
NOTE
If you attempt to
run this specific Delete statement, you'll run into a database error.
The problem is that this author record is linked to one or more records
in the TitleAuthor table. The author record can't be removed unless the
linked records are deleted first. (After all, it wouldn't make sense to
have a book linked to an author that doesn't exist.)
The Delete and Update
commands return a single piece of information: the number of affected
records. You can examine this value and use it to determine whether the
operation is successful or executed as expected.
