A Crash Course
- Hour 3: Using T-SQL: A Crash Course
- Adding New Data with INSERT
- Modifying Data with UPDATE and DELETE
- Using the Built-in SQL Functions
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Hour 3: Using T-SQL: A Crash Course
ADO.NET enables you to connect to a data source and retrieve and manipulate data. However, ADO.NET doesn't actually gather the data itself. It simply sends a string to the data source with data processing instructions. The language used to communicate with the data source is known as T-SQL (Transact-SQL), which is a dialect of Structured Query Language (SQL).
Because you must provide ADO.NET with the proper T-SQL statements for data retrieval and manipulation, knowledge of T-SQL is an essential skill for any well-rounded developer. Hundreds of different kinds of T-SQL statements are available in a product such as Microsoft SQL Server. You can modify many aspects of the server itself, such as managing jobs, creating and maintaining databases, and other administrative tasks. This chapter provides a primer; you'll learn just enough about T-SQL to understand all the examples in this book.
In this chapter, you will learn how to do the following tasks:
Retrieving data with the SELECT statement
Adding data with the INSERT statement
Modifying data with the UPDATE and DELETE statements
Using some T-SQL built-in functions
Microsoft SQL Server and Microsoft Access both ship with a sample database called Northwind. This database will be used for the examples in this chapter. The Northwind access database is freely distributed. You can download it at http://www.intensitysoftware.com/ADO.NET/nwind.mdb. If you are using a default installation of Microsoft SQL Server, you'll see an entry in your program group for the Query Analyzer. You can launch this application, select Northwind as your database, and follow along with the examples in this chapter directly.
If you are using another data source, such as Oracle, you still should be able to follow along. Your database server probably ships with an application like Query Analyzer that you can use to enter database queries. Use that to enter the queries in the following sections.
Retrieving Data with SELECT
The SELECT statement is used to retrieve and filter data from your data source. Listing 3.1 shows the simplified syntax of the SELECT statement. Read from top to bottom, this statement says "select these columns from these tables where these search criteria are true." You can retrieve several column names from several tables, so long as you separate the column names by commas.
Listing 3.1 The Syntax of the SELECT SQL Statement
SELECT column_names FROM table_names WHERE search_conditions
For instance, to retrieve all records from the Employees table, enter the following code in the query manager and press F5 or click the green Play button to execute the query:
SELECT * FROM Employees
This will return every single row and column in the Employees table. The results of your query will look much like Figure 3.1.
){kind=link}
Figure 3.1 The Query Analyzer has many uses, one of which is to see the results of your queries.
Note
T-SQL is not case sensitive. SELECT * FROM Employees is syntactically identical to select * from employees. However, there is a convention to capitalize T-SQL keywords such as SELECT and FROM to distinguish them from table and column text.
Suppose you only want to return a single record; you want to return one employee based on his or her last name, for example. As you can see in Listing 3.1, the WHERE keyword enables you to filter the data based on any number of search criteria. The content of the search criteria itself is broad. However, most often, the values of various columns are checked. For instance, to return all employees from the database with the last name "King," you would use the following query:
SELECT * FROM Employees WHERE LastName = 'King'
Similarly, if you want to be even more specific and filter by the employee's first name as well, just add another condition to your query, as in the following SQL statement.
SELECT * FROM Employees WHERE LastName = 'King' and FirstName = 'Robert'
Note
Strings in T-SQL are delimited by single quotation marks. If you attempt to use double quotation marks, an error will be returned by your data source. If you are filtering by a numerical field, there's no need for quotation marks at all.
Filtering by date is another common need. Let's say you want to return all employees hired after May 3, 1993. The query you build looks like this:
SELECT * FROM Employees WHERE HireDate between '5/3/1993' and getdate()
Notice that, like strings, dates in T-SQL are also delimited by single quotation marks. Getdate() is a built-in function that returns the current date and time in DateTime format.
Until now, we've used the wildcard "*" to select all columns for the table. This is fine for testing purposes, but not when building an application. Unless you are planning on using all the columns in the table, return only those columns that you plan to use in your application. You can do this by specifying the exact columns you need, separated by commas as shown in Listing 3.2.
Listing 3.2 Specifying Columns in SQL Statements
SELECT FirstName, Lastname, Title FROM Employees WHERE HireDate between '5/3/1993' and getdate()
This greatly reduces the amount of data returned by the data source to your application. Because the bottleneck in many applications is the database server, any way to make your queries perform more efficiently is likely to make your application perform better.
Note
In Microsoft SQL Server, all extra "white space" is ignored and does not affect processing. "White space" is defined as any character that does not generate a character on the screen. For instance, spaces, tabs, and newline characters are considered "white space." This enables you to format the appearance of queries however you want. Listing 3.2 separates the T-SQL commands from the actual table objects they use. Though the code takes up several more lines, it is easier to understand quickly.
This section only scratches the surface of what is possible with the SELECT statement. Microsoft SQL Server version 7.0 and higher ships with a terrific reference named SQL Server Books Online. This can be found in your SQL Server program group.
The online books are used on a daily basis by professionals everywhere (some might not admit to it), but new users might find it too terse to be very useful. In that case, there's certainly no lack of great books and Web sites devoted to the topic.