The Products table stores information about the products of the fictional Northwind corporation. This information includes the product name, packaging information, price, and other relevant fields. Each product (or row) in the table is identified by a unique numeric ID. Because each ID is unique, the ProductID column is the table's primary key. This column is an Identity column: It's a numeric value, which is generated automatically by the database every time you insert a new row to the table. The rows of the Products table are referenced by invoices (the Order Details table, which is discussed later), so the product IDs appear in the Order Details table as well. The ProductID column, as well as most primary keys in any database, has a unique property: It's an Identity column. Every time you add a new product to the table, SQL Server assigns the next available value to this column. If the ID of the last row in the Products table is 72, the first product that will be added will take the primary key value of 73 automatically. SQL Server will always assign the proper value to this column, and it will always be unique.

Each product has a supplier, too. Because the same supplier can offer more than one product, the supplier information is stored in a different table, and a common field, the SupplierID field, is used to link each product to its supplier (as shown in Figure 15.3). For example, the products Chai, Chang, and Aniseed Syrup are purchased from the same supplier: Exotic Liquids. Its SupplierID fields all point to the same row in the Suppliers table.

Figure 15.3. Linking products to their suppliers and their categories

In addition to having a supplier, each product belongs to a category. Categories are not stored along with product names; they are stored separately in the Categories table. Again, each category is identified by a numeric value (field CategoryID) and has a name (field CategoryName). In addition, the Categories table has two more columns: Description, which contains text, and Picture, which stores a bitmap. The CategoryID field in the Categories table is the primary key, and the field by the same name in the Products table is the corresponding foreign key.

The Customers table stores information about the company's customers. Each customer is stored in a separate row of this table, and customers are referenced by the Orders table. Unlike product IDs, customer IDs are five-character strings and are stored in the CustomerID column. This is an unusual choice for IDs, which are usually numeric values. The CustomerID column isn't an Identity column; the user must determine the key of each customer and submit it along with the other customer data. The database has a unique constraint for this column: The customer's ID must be unique, and the database won't accept any rows with a duplicate CustomerID value.

The Orders table stores information about the orders placed by Northwind's customers. The OrderID field, which is an integer value, identifies each order. Orders are numbered sequentially, so this field is also the order's number. Each time you append a new row to the Orders table, the value of the new OrderID field is generated automatically by the database. Not only is the OrderID column the table's primary key, but it's also an Identity column.

The Orders table is linked to the Customers table through the CustomerID column. By matching rows that have identical values in their CustomerID fields in the two tables, we can recombine customers with their orders. Refer back to Figure 15.1 to see how customers are linked to their orders.

The Orders table doesn't store any details about the items ordered; this information is stored in the Order Details table (see Figure 15.4). Each order consists of one or more items, and each item has a price, a quantity, and a discount. In addition to these fields, the Order Details table contains an OrderID column, which holds the ID of the order to which the detail line belongs.

The reason why details aren't stored along with the order's header is that the Orders and Order Details tables store different entities. The order's header, which contains information about the customer who placed the order, the date of the order, and so on, is quite different from the information you must store for each item ordered. If you attempt to store the entire order into a single table, you'll end up repeating a lot of information. Notice also that the Order Details table stores the IDs of the products, not the product names.

This table holds employee information. Each employee is identified by a numeric ID, which appears in each order. When a sale is made, the ID of the employee who made the sale is recorded in the Orders table. An interesting technique was used in the design of the Employees table: Each employee has a manager, which is another employee. The employee's manager is identified by the ReportsTo field, which is set to the ID of the employee's manager. The rows of the Employees table contain references to the same table. This table contains a foreign key that points to the primary key of the same table, a relation that allows you to identify the hierarchy of employees in the corporation.

Figure 15.4. Customers, Orders, and Order Details tables and their relations

Each order is shipped with one of the three shippers stored in the Shippers table. The appropriate shipper's ID is stored in the Orders table.

The Titles table contains information about individual books (the book's title, ID, price, and so on). Each title is identified by an ID, which is not a numeric value, that's stored in the title_id column. The IDs of the books look like this: BU2075.

The Authors table contains information about authors. Each author is identified by an ID, which is stored in the au_id field. This field is a string with a value such as 172-32-1176 (they resemble U.S. Social Security numbers).

The Titles and Authors tables are not directly related because they can't be joined via a one-to-many relationship; the relationship between the two tables is many-to-many. The relations you have seen so far are one-to-many because they relate one row in the table that has the primary key to one or more rows in the table that has the foreign key: One order contains many detail lines, one customer has many orders, one category contains many products, and so on.

The relation between titles and authors is many-to-many because each book may have multiple authors, and each author may have written multiple titles. If you stop to think about the relationship between these two tables, you'll realize that it can't be implemented with a primary key and a foreign key (like the Order-Customer relationship or the Order-Shipper relationship in the Northwind database). To establish a many-to-many relationship, you must create a join table between the other two, and this table must have a one-to-many relationship with both tables.

Figure 15.5 shows how the Titles and Authors tables of the Pubs database are related to one another. The table between them holds pairs of title IDs and author IDs. If a book was written by two authors, the TitleAuthor table contains two entries with the same title ID and different author IDs. The book with a title_id of BU1111 was written by two authors. The IDs of the authors appear in the TitleAuthor table along with the ID of the book. The IDs of these two authors are 267-41-2394 and 724-80-9391. Likewise, if an author has written more than one book, the author's ID will appear many times in the TitleAuthor table — each time paired with a different title ID.

Figure 15.5. The TitleAuthor table links titles to authors.

At times you won't be able to establish the desired relationship directly between two tables because the relationship is many-to-many. When you discover a conflict between two tables, you must create a join table between them. A many-to-many relation is actually implemented as two one-to-many relations.

The Publishers table contains information about publishers. Each title has a pub_id field, which points to the matching row of the Publishers table. Unlike the other major tables of the Pubs database, the Publishers table uses a numeric value to identify each publisher.

Maintaining the links between tables is not a trivial task. When you add an invoice line, for instance, you must make sure that the product ID that you insert in the Order Details table corresponds to a row in the Products table. An important aspect of a database is its integrity. To be specific, you must ensure that the relations are always valid, and this type of integrity is called referential integrity. There are other types of integrity (for example, setting a product's value to a negative value will compromise the integrity of the database), but this is not nearly as important as referential integrity. The wrong price can be easily fixed. But issuing an invoice to a customer who doesn't exist isn't easy (if even possible) to fix. Modern databases come with many tools to help ensure their integrity, especially referential integrity. These tools are constraints you enter when you design the database, and the DBMS makes sure that the constraints are not violated as the various programs manipulate the database.

When you relate the Products and Categories tables, for example, you must also ensure the following:

These two restrictions would be quite a burden on the programmer if the DBMS didn't protect the database against actions that could impair its integrity. The referential integrity of your database depends on the validity of the relations. Fortunately, all DBMSs can enforce rules to maintain their integrity, and you'll learn how to enforce rules that guarantee the integrity of your database later in this chapter. In fact, when you create the relationship, you can select a couple of check boxes that tell SQL Server to enforce the relationship (that is, not to accept any changes in the data that violate the relationship). If you leave these check boxes deselected, be ready to face a real disaster sooner or later.

To simplify the development of database applications, Visual Studio 2010 comes with some visual tools, the most important of which are briefly described in Table 15.1 and then discussed in the following sections.

One of the applications installed with SQL Server is SQL Server Management Studio. To start it, choose Start

Figure 15.6. SSMS provides access to all the database engine objects, including databases.

After you're connected, right-click the database you want to use, and choose New Query from the context menu. Enter the SQL statement you want to execute in the blank query that SSMS creates. The SQL statement will be executed against the selected database when you press Ctrl+E or click the Execute button (it's the button with the exclamation point icon). Alternatively, you can prefix the SQL statement with the USE statement, which specifies the database against which the statement will be executed. To retrieve all the Northwind customers located in Germany, enter this statement:

USE Northwind
SELECT CompanyName FROM Customers
WHERE  Country = 'Germany'

The USE statement isn't part of the query; it simply tells SSMS the database against which it must execute the query. I'm including the USE statement with all the queries so you know the database used for each example. If you're executing the sample code from within Visual Studio, you need not use the USE statement, because all queries are executed against the selected database. Actually, the statement isn't supported by Query Designer of Visual Studio.

The results of the query, known as the result set, will appear in a grid in the lower pane. An action query that updates a table (adds a new row, edits a row, or deletes an existing row) doesn't return any rows; it simply displays the number of rows affected on the Messages tab.

To execute another query, enter another statement in the upper pane, or edit the previous statement and press Ctrl+E again. You can also save SQL statements into files so that you won't have to type them again. To do so, open the File menu, choose Save As or Save, and enter the name of the file in which the contents of the Query pane will be stored. The statement will be stored in a text file with the extension .sql.

To execute the same queries with Visual Studio, open the Server Explorer window, and right-click the name of the database against which you want to execute the query. From the context menu, choose New Query, and a new query window will open. You will also see a dialog box prompting you to select one or more tables. For the time being, close this dialog box, because you will supply the names of the tables in the query; later in this chapter, you'll learn how to use the visual tools to build queries.

Query Designer of Visual Studio consists of four panes (Figure 15.7). The upper pane (which is the Table Diagram pane) displays the tables involved in the query, their fields, and the relationships between the tables — if any. The next pane shows the fields that will be included in the output of the query. Here you specify the output of the query, as well as the selection criteria. This pane is Query Builder, the tool that lets you design queries visually. It's discussed later in this chapter. In the next pane, the SQL pane, you see the SQL statement produced by the visual tools. If you modify the query with the visual tools, the SQL statement is updated automatically; likewise, when you edit the query, the other two panes are updated automatically to reflect the changes. The last pane, the Results pane, contains a grid with the query's output. Every time you execute the query by clicking the button with the exclamation mark in the toolbar, the bottom pane is populated with the results of the query. For the examples in this section, ignore the top two panes. Just enter the SQL statements in the SQL pane, and execute them.

Figure 15.7. Executing queries with Visual Studio

The unconditional form of the SELECT statement used in the previous section is quite trivial. You rarely retrieve data from all rows in a table. Usually you specify criteria, such as "all companies from Germany," "all customers who have placed three or more orders in the last six months," or even more-complicated expressions. To restrict the rows returned by the query, use the WHERE clause of the SELECT statement. The most common form of the SELECT statement is the following:

SELECT fields
FROM   tables
WHERE  condition

The fields and tables arguments are the same as before, and condition is an expression that limits the rows to be selected. The syntax of the WHERE clause can get quite complicated, so we'll start with the simpler forms of the selection criteria. The condition argument can be a relational expression, such as the ones you use in VB. To select all the customers from Germany, use the following condition:

WHERE Country = 'Germany'

To select customers from multiple countries, use the OR operator to combine multiple conditions:

WHERE Country = 'Germany' OR
      Country = 'Austria'

You can also combine multiple conditions with the AND operator.

It is possible to retrieve data from two or more tables by using a single statement. (This is the most common type of query, actually.) When you combine multiple tables in a query, you can use the WHERE clause to specify how the rows of the two tables will be combined. Let's say you want a list of all product names, along with their categories. For this query, you must extract the product names from the Products table and the category names from the Categories table and specify that the ProductID field in the two tables must match. The statement

USE Northwind
SELECT ProductName, CategoryName
FROM   Products, Categories
WHERE  Products.CategoryID = Categories.CategoryID

retrieves the names of all products, along with their category names. Here's how this statement is executed. For each row in the Products table, the SQL engine locates the matching row in the Categories table and then appends the ProductName and CategoryName fields to the result. In other words, it creates pairs of a product and the matching category. Products that don't belong to a category are not included in the result set.

To avoid typing long table names, you can alias them with a shorter name and use this shorthand notation in the rest of the query. The query that retrieves titles and publishers can be written as follows:

USE pubs
SELECT T.title
FROM   titles T, publishers P
WHERE  T.pub_id = P.pub_id

The table names are aliased in the FROM clause, and the alias is used in the rest of the query. There's one situation where table name aliasing is mandatory, and this is when you want to refer to the same table twice. This is a fairly advanced topic, but I'm including a typical example to demonstrate an interesting technique, because you may run into it. Some tables contain references to themselves, and the Employees table of the Northwind database belongs to this category. Each employee reports to a manager, who's also an employee. The manager is identified by the column ReportsTo, which contains the ID of an employee's manager. Here are three rows of the Employees table that demonstrate this hierarchy:

As you can see here, Suyama reports to Buchanan, and Buchanan in turn reports to Fuller. How would you retrieve each employee's name and title along with his manager's name? Here, we must make a query that involves two tables, one with the employee names and another one with the manager names. It just so happens that the two tables are in reality the same table. The trick is to treat the Employees table as two separate tables using aliases. Here's the query that retrieves employees and managers in a single result set:

SELECT  Employees.EmployeeID,
        Employees.LastName + ' ' + Employees.FirstName,
        Employees.Title,
        Managers.FirstName + ' ' + Managers.LastName,
        Managers.Title
FROM    Employees, Employees Managers
WHERE   Employees.ReportsTo = Managers.EmployeeID

If the database contained two different tables, one for employees and another one for managers, you'd have no problem coding the query. Because we want to use the same table for both managers and employees, we use aliases to create two virtual tables and associate them with the ReportsTo and EmployeeID columns. The result is a neat list of names that shows clearly the hierarchy of the Northwind corporation's employees:

1  Davolio Nancy    Sales Representative      Andrew Fuller
3  Leverling Janet  Sales Representative      Andrew Fuller
4  Peacock Margaret Sales Representative      Andrew Fuller
5  Buchanan Steven  Sales Manager             Andrew Fuller
6  Suyama Michael   Sales Representative      Steven Buchanan
7  King Robert      Sales Representative      Steven Buchanan
8  Callahan Laura   Inside Sales Coordinator  Andrew Fuller
9  Dodsworth Anne   Sales Representative      Steven Buchanan

I skipped the manager's title in the listing because it wouldn't fit on the printed page. Note that because the Last Name and First Name column names contain spaces, they're embedded in square brackets. The same is true for table names that contain spaces.

By default, each column of a query is labeled after the actual field name in the output. If a table contains two fields named CustLName and CustFName, you can display them with different labels by using the AS keyword. The following SELECT statement produces two columns labeled CustLName and CustFName:

SELECT CustLName, CustFName

The query's output looks much better if you change the labels of these two columns with a statement like the following:

SELECT CustLName AS [Last Name],
       CustFName AS [First Name]

It is also possible to concatenate two fields in the SELECT list with the concatenation operator. Concatenated fields are labeled automatically as Expr1, Expr2, and so on, so you must supply your own name for the combined field. The following statement creates a single column for the customer's name and labels it Customer Name:

SELECT CustLName + ', ' + CustFName AS [Customer Name]

The DISTINCT keyword eliminates from the cursor any duplicates retrieved by the SELECT statement. Let's say you want a list of all countries with at least one customer. If you retrieve all country names from the Customers table, you'll end up with many duplicates. To eliminate them, use the DISTINCT keyword, as shown in the following statement:

USE Northwind
SELECT DISTINCT Country
FROM   Customers

The LIKE operator uses pattern-matching characters like the ones you use to select multiple files in DOS. The LIKE operator recognizes several pattern-matching characters (or wildcard characters) to match one or more characters, numeric digits, ranges of letters, and so on. Table 15.2 describes these characters.

You can use the LIKE operator to retrieve all titles about Windows from the Pubs database, by using a statement like the following one:

USE pubs
SELECT titles.title
FROM   titles
WHERE  titles.title LIKE '%Windows%'

The percent signs mean that any character(s) may appear in front of or after the word Windows in the title.

To include a wildcard character itself in your search argument, enclose it in square brackets. The pattern %50[%]% will match any field that contains the string 50%.

A common operation for manipulating and maintaining databases is to locate null values in fields. The expressions IS NULL and IS NOT NULL find field values that are (or are not) null. To locate the rows of the Customers table that have a null value in their CompanyName column, use the following WHERE clause:

WHERE CompanyName IS NULL

You can easily locate the products without prices and edit them. The following statement locates products without prices:

USE Northwind
SELECT * FROM Products WHERE UnitPrice IS NULL

A related function, the ISNULL() function, allows you to specify the value to be returned when a specific field is null. The ISNULL() SQL function accepts two arguments: a column name and a string. The function returns the value of the specified column, unless this value is null, in which case it returns the value of the second argument. To return the string *** for customers without a company name, use the following expression:

USE Northwind
SELECT  CustomerID,
        ISNULL(CompanyName, '***') AS Company, ContactName
FROM Customers

The rows of a query are not in any particular order. To request that the rows be returned in a specific order, use the ORDER BY clause, which has this syntax:

ORDER BY col1, col2, . . .

You can specify any number of columns in the ORDER BY list. The output of the query is ordered according to the values of the first column. If two rows have identical values in this column, they are sorted according to the second column, and so on. The following statement displays the customers ordered by country and then by city within each country:

USE      Northwind
SELECT   CompanyName, ContactName, Country, City
FROM     Customers
ORDER BY Country, City

The left join displays all the records in the left table and only those records of the table on the right that match certain user-supplied criteria. This join has the following syntax:

FROM (primary table) LEFT JOIN (secondary table) ON
(primary table).(field) = (secondary table).(field)

The left outer join retrieves all rows in the primary table and the matching rows from a secondary table. The following statement retrieves all the titles from the Pubs database along with their publisher. If some titles have no publisher, they will be included in the result:

USE pubs
SELECT  title, pub_name
FROM    titles LEFT JOIN publishers
           ON titles.pub_id = publishers.pub_id

The right join is similar to the left outer join, except that it selects all rows in the table on the right and only the matching rows from the left table. This join has the following syntax:

FROM (secondary table) RIGHT JOIN (primary table)
ON (secondary table).(field) = (primary table).(field)

The following statement retrieves all the publishers from the Pubs database along with their titles. If a publisher has no titles, the publisher name will be included in the result set. Notice that this statement is almost the same as the example of the left outer join entry. I changed only LEFT to RIGHT:

USE pubs
SELECT  title, pub_name
FROM    titles RIGHT JOIN publishers
           ON titles.pub_id = publishers.pub_id

The full join returns all the rows of the two tables, regardless of whether there are matching rows. In effect, it's a combination of left and right joins. To retrieve all titles and all publishers and to match publishers to their titles, use the following join:

USE pubs
SELECT  title, pub_name
FROM    titles FULL JOIN publishers
           ON titles.pub_id = publishers.pub_id

This query will include titles without a publisher, as well as publishers without a title.

The inner join returns the matching rows of both tables, similar to the WHERE clause, and has the following syntax:

FROM (primary table) INNER JOIN (secondary table)
ON (primary table).(field) = (secondary table).(field)

The following SQL statement combines records from the Titles and Publishers tables of the Pubs database if their pub_id fields match. It returns all the titles and their publishers. Titles without publishers, or publishers without titles, will not be included in the result.

USE pubs
SELECT titles.title, publishers.pub_name FROM titles, publishers
WHERE  titles.pub_id = publishers.pub_id

You can retrieve the same rows by using an inner join, as follows:

USE pubs
SELECT titles.title, publishers.pub_name
FROM   titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id

Real World Scenario: Do Not Join Tables with the WHERE Clause

The proper method of retrieving rows from multiple tables is to use joins. It's not uncommon to write a dozen joins one after the other (if you have that many tables to join). You can also join two tables by using the WHERE clause. Here are two statements that return the total revenue for each of the customers in the Northwind database. The first one uses the INNER JOIN statement, and the second one uses the WHERE clause. The INNER JOIN is equivalent to the WHERE clause: They both return the same rows.

Query 1

SELECT
     C.CompanyName,
     SUM((OD.UnitPrice * OD.Quantity) * (1 - OD.Discount)) AS Revenue
FROM Customers AS C
     INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID
     INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID
GROUP BY C.CompanyName

Query 2

SELECT
    C.CompanyName,
    SUM((OD.UnitPrice * OD.Quantity) * (1 - OD.Discount)) AS Revenue
FROM Customers AS C, Orders AS O, [Order Details] AS OD
     WHERE C.CustomerID = O.CustomerID
     AND O.OrderID = OD.OrderID
GROUP BY C.CompanyName

Both statements assume that all customers have placed an order. If you change the INNER JOIN in the first statement to a LEFT JOIN, the result will contain two more rows: The customers FISSA and PARIS have not placed any orders, and they're not included in the output. If you know that all your customers have placed an order or you don't care about customers without orders, use the WHERE clause or an inner join. It's important to keep in mind that if you want to see all customers, regardless of whether they have placed an order, you must use joins.

An even better example is that of retrieving titles along with their authors. An inner join will return titles that have one or more authors. A left join will return all titles, even the ones without authors. A right join will return all authors, even if some of them are not associated with any titles. Finally, a full outer join will return both titles without authors and authors without titles. Here's the statement that retrieves titles and authors from the Pubs database. Change the type of joins to see how they affect the result set.

SELECT   titles.title,
         authors.au_lname + ', ' + authors.au_fname AS Author
FROM     authors
   INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
   INNER JOIN titles ON titleauthor.title_id = titles.title_id
ORDER BY titles.title

There's a shorthand notation for specifying left and right joins with the WHERE clause. When you use the operator *= in a WHERE clause, a left join will be created. Likewise, the =* operator is equivalent to a right join.

The HAVING clause limits the groups that will appear at the cursor. In a way, it is similar to the WHERE clause, but the HAVING clause is used with aggregate functions and the GROUP BY clause, and the expression used with the HAVING clause usually involves one or more aggregates. The following statement returns the IDs of the products whose sales exceed 1,000 units:

USE NORTHWIND
SELECT   ProductID, SUM(Quantity)
FROM     [Order Details]
GROUP BY ProductID
HAVING   SUM(Quantity) > 1000

You can't use the WHERE clause here, because no aggregates may appear in the WHERE clause. However, you can use the WHERE clause as usual to limit the number of rows involved in the query (for example, limit the aggregate to the products of a specific category, or the products sold to customers in Germany). To see product names instead of IDs, join the Order Details table to the Products table by matching their ProductID columns. Note that the expression in the HAVING clause need not be included in the selection list. You can change the previous statement to retrieve the total quantities sold with a discount of 10 percent or more with the following HAVING clause:

HAVING Discount >= 0.1

However, the Discount column must be included in the GROUP BY clause as well, because it's not part of an aggregate.

The command to be executed through the Command object is not always a SQL statement; it could be the name of a stored procedure, or the name of a table, in which case it retrieves all the rows of the table. You can specify the type of statement you want to execute with the CommandType property, whose value is a member of the CommandType enumeration: Text (for SQL statements), StoredProcedure (for stored procedures), and TableDirect (for a table). You don't have to specify the type of the command you want to execute, but then the Command object will have to figure it out, a process that will take a few moments, and you can avoid this unnecessary delay. The Northwind database comes with the Ten Most Expensive Products stored procedure. To execute this stored procedure, set up a Command object with the following statements:

Dim CMD As New SqlCommand
CMD.Connection = CN
CMD.CommandText = "[Ten Most Expensive Products]"
CMD.CommandType = CommandType.StoredProcedure

Finally, you can retrieve all the rows of the Customers table by setting up a Command object like the following:

Dim CMD As New SqlCommand
CMD.Connection = CN
CMD.CommandText = "Customers"
CMD.CommandType = CommandType.TableDirect

The most common SQL statements, the SELECT statements, retrieve a set of rows from one or more joined tables, the result set. These statements are executed with the ExecuteReader method, which returns a DataReader object — a SqlDataReader object for statements executed against SQL Server databases. The DataReader class provides the members for reading the results of the query in a forward-only manner. The connection remains open while you read the rows returned by the query, so it's imperative to read the rows and store them in a structure in the client computer's memory as soon as possible and then close the connection. The DataReader object is read-only (you can't use it to update the underlying rows), so there's no reason to keep it open for long periods. Let's execute the following SELECT statement to retrieve selected columns of the rows of the Employees table of the Northwind database:

SELECT LastName + ' ' + FirstName AS Name,
       Title, Extension, HomePhone
FROM   Employees

Here are the VB statements that set up the appropriate Command object and retrieve the SqlDataReader object with the result set:

Dim CMD As New SqlCommand
Dim CN As New SqlConnection("Data Source = localhost;Initial Catalog=Northwind;" &
                   "Integrated Security=True")
CMD.Connection = CN
CMD.CommandText =
     "SELECT LastName + ' ' + FirstName AS Name, " &
     "Title, Extension, HomePhone FROM Employees"
CN.Open()
Dim Reader As SqlDataReader
Reader = Command.ExecuteReader
Dim str As String = ""
Dim cont As Integer = 0
While Reader.Read
    str &= Convert.ToString(Reader.Item("Name")) & vbTab
    str &= Convert.ToString(Reader.Item("Title")) & vbTab
    str &= Convert.ToString(Reader.Item("Extension")) & vbTab
    str &= Convert.ToString(Reader.Item("HomePhone")) & vbTab
    str &= vbCrLf
    count += 1
End While
Debug.WriteLine(vbCrLf & vbCrLf & "Read " & count.ToString &
                " rows: " & vbCrLf & vbCrLf & str)
CN.Close()

The DataReader class provides the Read method, which advances the current pointer to the next row in the result set. To read the individual columns of the current row, you use the Item property, which allows you to specify the column by name and returns an object variable. It's your responsibility to cast the object returned by the Item property to the appropriate type. Initially, the DataReader is positioned in front of the first line in the result set, and you must call its Read method to advance to the first row. If the query returns no rows, the Read method will return False, and the While loop won't be executed at all. In the preceding sample code, the fields of each row are concatenated to form the str string, which is printed in the Immediate window; it looks something like this:

Davolio Nancy     Sales Representative    5467  (206) 555-9857
Fuller Andrew     Vice President, Sales   3457  (206) 555-9482
Leverling Janet   Sales Representative    3355  (206) 555-3412

Most SQL statements and stored procedures accept parameters, and you should pass values for each parameter before executing the query. Consider a simple statement that retrieves the customers from a specific country, whose name is passed as an argument:

SELECT * FROM Customers WHERE Country = @country

The @country parameter must be set to a value, or an exception will be thrown as you attempt to execute this statement. Stored procedures also accept parameters. The Sales By Year stored procedure of the Northwind database, for example, expects two Date values and returns sales between the two dates. To accommodate the passing of parameters to a parameterized query or stored procedure, the Command object exposes the Parameters property, which is a collection of Parameter objects. To pass parameter values to a command, you must set up a Parameter object for each parameter; set its name, type, and value; and then add the Parameter object to the Parameters collection of the Command object. The following statements set up a Command object with a parameter of the varchar type with a maximum size of 15 characters:

Dim Command As New SqlCommand
Command.CommandText = "SELECT * FROM Customers WHERE Country = @country"
Command.Parameters.Add("@country", SqlDbType.VarChar, 15)
Command.Parameters("@country").Value = "Italy"

At this point, you're ready to execute the SELECT statement with the ExecuteReader method and retrieve the customers from Italy. You can also configure the Parameter object in its constructor:

Dim param As New SqlParameter(paramName, paramType, paramSize)

Here's the constructor of the @country parameter of the preceding example:

Dim param As New SqlParameter("@country", SqlDbType.VarChar, 15)
param.Value = "Italy"

CMD.Parameters.Add param

Finally, you can combine all these statements into a single one:

CMD.Parameters.Add("@country", SqlDbType.VarChar, 15).Value = "Italy"

In the last statement, I initialize the parameter as I add it to the Parameters collection and then set its value to the string Italy. Oddly, there's no overloaded form of the Add method that allows you to specify the parameter's value, but there is an AddWithValue method, which adds a new parameter and sets its value. This method accepts two arguments: a string with the parameter's name and an object with the parameter's value. The actual type of the value is determined by the type of the query or stored procedure's argument, and it's resolved at runtime. The simplest method of adding a new parameter to the CMD.Parameters collection is the following:

CMD.Parameters.Add("@country", "Italy")

After the parameter has been set up, you can call the ExecuteReader method to retrieve the customers from the country specified by the argument and then read the results through an instance of the DataReader class.

Another property of the Parameter class is the Direction property, which determines whether the stored procedure can alter the value of the parameter. The Direction property's setting is a member of the ParameterDirection enumeration: Input, Output, InputOutput, and ReturnValue. A parameter that's set by the procedure should have its Direction property set to Output: The parameter's value is not going to be used by the procedure, but the procedure's code can set it to return information to the calling application. If the parameter is used to pass information to the procedure, as well as to pass information back to the calling application, its Direction property should be set to InputOutput.

Let's look at a stored procedure that returns the total of all orders, as well as the total number of items ordered by a specific customer. This stored procedure accepts as a parameter the ID of a customer, obviously, and it returns two values: the total of all orders placed by the specified customer and the number of items ordered. A procedure (be it a SQL Server stored procedure or a regular VB function) can't return two or more values. The only way to retrieve multiple results from a single stored procedure is to pass output parameters so that the stored procedure can set their value. To make the stored procedure a little more interesting, we'll add a return value, which will be the number of orders placed by the customer. Listing 15.1 shows the implementation of the CustomerTotals stored procedure.

CREATE PROCEDURE CustomerTotals
@customerID varchar(5),
@customerTotal money OUTPUT,
@customerItems int OUTPUT
AS
SELECT @customerTotal = SUM(UnitPrice * Quantity * (1 - Discount))
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID = Orders.OrderID
WHERE Orders.CustomerID = @customerID
SELECT @customerItems = SUM(Quantity)
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID = Orders.OrderID
WHERE Orders.CustomerID = @customerID

DECLARE @customerOrders int
SELECT @customerOrders = COUNT(*) FROM Orders
WHERE Orders.CustomerID = @customerID

RETURN @customerOrders

To attach the CustomerTotals stored procedure to the database, create a new stored procedure, paste the preceding statements in the code window, and press F5 to execute it. Make sure the database is Northwind (not master). The stored procedure calculates three totals for the specified customer and stores them to three local variables. The @customerTotal and @customerItems variables are output parameters, which the calling application can read after executing the stored procedure. The @customerOrders variable is the procedure's return value. We can return the number of orders for the customer through the stored procedure's return value, because this variable happens to be an integer, and the return value is always an integer. In more-complex stored procedures, we'd use output parameters for all the values we want to return to the calling application, and the procedure would return a value to indicate the execution status: 0 or 1 if the procedure completed its execution successfully and a negative value to indicate the error, should the procedure fail to execute.

Before using the CustomerTotals stored procedure with our VB application, let's test it in SQL Server Management Studio. We must declare a variable for each of the output parameters: the @Total, @Items, and @Orders variables. These three variables must be passed to the stored procedure with the OUTPUT attribute, as shown here:

DECLARE @Total money
DECLARE @Items int
DECLARE @Orders int
DECLARE @custID varchar(5)
DECLARE @CustomerTotal decimal
DECLARE @CustomerItems int
SET @custID = 'BLAUS'
EXEC @orders = CustomerTotals @custID,
          @customerTotal OUTPUT, @customerItems OUTPUT
PRINT 'Customer ' + @custId + ' has placed a total of ' +
      CAST(@orders AS varchar(8)) + ' orders ' +
      ' totaling $' + CAST(ROUND(@customerTotal, 2) AS varchar(12)) +
      ' and ' + CAST(@customerItems AS varchar(4)) + ' items.'

Open a new query window in SQL Server Management Studio, and enter the preceding statements. Press F5 to execute them, and you will see the following message printed in the Output window:

Customer BLAUS has placed a total of 8 orders totaling $10355.45 and 653 items.

The customer's ID is an INPUT parameter, and we could pass it to the procedure as a literal. You can omit the declaration of the @custID variable and call the stored procedure with the following statement:

DECLARE @CustomerTotal decimal
DECLARE @CustomerItems int
EXEC @orders = _
          CustomerTotals 'BLAUS', @customerTotal OUTPUT, @customerItems OUTPUT

Now that we've tested our stored procedure and know how to call it, we'll do the same from within our sample application. To execute the CustomerTotals stored procedure, we must set up a Command object, create the appropriate Parameter objects (one Parameter object per stored procedure parameter plus another Parameter object for the stored procedure's return value), and then call the Command.ExecuteNonQuery method. Upon return, we'll read the values of the output parameters and the stored procedure's return value. Listing 15.2 shows the code that executes the stored procedure (see the SimpleQueries sample project available for download from www.sybex.com/go/masteringvb2010).

Private Sub bttnExecSP_Click(...) Handles bttnExecSP.Click
    Dim customerID As String = InputBox("Please enter a customer ID",
          "CustomerTotals Stored Procedure", "ALFKI")
    If customerID.Trim.Length = 0 Then Exit Sub
    Dim CMD As New SqlCommand
    CMD.Connection = CN
    CMD.CommandText = "CustomerTotals"
    CMD.CommandType = CommandType.StoredProcedure
    CMD.Parameters.Add(
          "@customerID", SqlDbType.VarChar, 5).Value = customerID
    CMD.Parameters.Add("@customerTotal", SqlDbType.Money)
    CMD.Parameters("@customerTotal").Direction = ParameterDirection.Output
    CMD.Parameters.Add("@customerItems", SqlDbType.Int)
    CMD.Parameters("@customerItems").Direction = ParameterDirection.Output
    CMD.Parameters.Add("@orders", SqlDbType.Int)
    CMD.Parameters("@orders").Direction = ParameterDirection.ReturnValue
    CN.Open()
    CMD.ExecuteNonQuery()
    CN.Close()
    Dim items As Integer
    Items = Convert.ToInt32(CMD.Parameters("@customerItems").Value
    Dim orders As Integer
    Orders = Convert.ToInt32(CMD.Parameters("@orders").Value
    Dim ordersTotal As Decimal
    ordersTotal = Convert.ToDouble(
         CMD.Parameters("@customerTotal").Value
    MsgBox("Customer BLAUS has placed " &
           orders.ToString & " orders " &
           "totaling $" & Math.Round(ordersTotal, 2).ToString("#,###.00") &
           " and " & items.ToString & " items")
End Sub

In most applications, the same Command object will be reused again and again with different parameter values, so it's common to add the parameters to a Command object's Parameters collection and assign values to them every time we want to execute the command. Let's say you've designed a form with text boxes, where users can edit the values of the various fields; here's how you'd set the values of the UPDATECMD variable's parameters:

UPDATECMD.Parameters("@CustomerID").Value = txtID.Text.Trim
UPDATECMD.Parameters("@CompanyName").Value = txtCompany.Text.Trim

After setting the values of all parameters, you can call the ExecuteNonQuery method to submit the changes to the database. To update another customer, just assign different values to the existing parameters, and call the UPDATECMD object's ExecuteNonQuery method.

Real World Scenario: Security Issue: SQL Injection Attacks

You may be wondering why we have to go through the process of creating SQL statements with parameters and setting up Parameter objects, instead of generating straightforward SQL statements on the fly. A statement that picks some user-supplied values and embeds them in a SQL statement can be exploited by a malicious user as a Trojan horse to execute any SQL statement against your database. Can you guess what will happen when the user enters a SQL statement in one of the TextBox controls on the form and your code uses this string to build a SQL statement on the fly? The code will pick it up, insert it into the larger statement, and then execute it against the database. This technique is known as SQL injection, and I'll show you how it works with a simple example. It's a known issue with any DBMS that can execute multiple SQL statements in a batch mode, and SQL Server is certainly vulnerable to SQL injection attacks.

How would you validate the users of an application? You'd most likely store the usernames and passwords in a table and execute a few statements to locate the row with the specified ID and password, right? If you didn't know any better, you'd probably write some code to extract the values from two TextBox controls and build the following SQL statement:

Dim CMD As New SqlClient.SqlCommand(
"SELECT COUNT(*) FROM Customers WHERE CompanyName = " ' &
txtName.Text & "' AND CustomerID = "' & txtPsswd.Text & "'")

Then you'd execute this statement and examine the number of rows that match the specified criteria:

Dim count As Integer
count = Convert.ToInt32(CMD.ExecuteScalar)

If the value of the count variable is 1, the user can log in. (I've assumed that the CompanyName field is the username and that the password for each user is the CustomerID field of the Customers table.) If the values in the two text boxes are Frankenversan and FRANK, the following statement will be executed against the database and will return the numeric value 1:

SELECT COUNT(*) FROM Customers
WHERE CompanyName = 'Frankenversan' AND CustomerID = 'FRANK'

A malicious user might enter the following username (and no password at all):

xxx' ; DROP TABLE [Orders] --

If you examine the value of the Command.CommandText property before this statement is executed, you'll see the following SQL statement:

SELECT COUNT(*) FROM Customers
WHERE CompanyName = 'xxx' ;
DROP TABLE [Orders] --' AND CustomerID = "

This statement contains two SQL statements and a comment: the SELECT statement, followed by another statement that drops the Orders table! The comments symbol (the two dashes) is required to disable the last part of the original statement, which otherwise would cause a syntax error. The Orders table can't be dropped, because it contains related rows in the Order Details table, but nothing will stop you from dropping the Order Details table.

If you want to demonstrate to someone that their software isn't secure, you can replace the DROP TABLE statement with the SHUTDOWN statement. The following statement shuts down the server immediately (and they can restart it immediately by running SQL Server Agent from the SQL Server Configuration Manager utility):

SHUTDOWN WITH NOWAIT

Another problem you will avoid with parameterized queries and stored procedures is that of handling single quotes, which are used to delimit literals in T-SQL. Consider the following UPDATE statement, which picks up the company name from a TextBox control and updates a single row in the Customers table:

CMD.CommandText =
       "UPDATE Customers SET CompanyName = '" &
       txtCompany.Text & "'" &
       "WHERE CustomerID = "' & txtID.Text & "'"

If the user enters a company name that contains a single quote, such as B's Beverages, the command will become the following:

UPDATE Customers SET CompanyName = 'B's Beverages' WHERE CustomerID = 'BSBEV'

If you attempt to execute this statement, SQL Server will reject it because it contains a syntax error (you should be able to figure out the error easily by now). The exact error message is as follows:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ".

The single quote is used to delimit literals, and there should be an even number of single quotes in the statement. The compiler determines that there's an unclosed quotation mark in the statement and doesn't execute it. If the same statement was written as a parameterized query, such as the following, you could pass the same company name to the statement as an argument without a hitch:

CMD.CommandText =
    "UPDATE Customers SET CompanyName = @CompanyName " &
    "WHERE CustomerID = @ID"
CMD.Parameters.Add("@CompanyName",
         SqlDbType.VarChar, 40).Value = "B's Beverages"
CMD.Parameters.Add("@ID",
         SqlDbType.Char, 5).Value = "BSBEV"
CMD.ExecuteNonQuery

The same is true for other special characters, such as the percentage symbol. It's possible to escape the special symbols; you can replace the single-quote mark with two consecutive single quotes, but the most elegant method of handling special characters, such as quotation marks, percent signs, and so on, is to use parameterized queries or stored procedures. You just assign a string to the parameter and don't have to worry about escaping any characters; the Command object will take care of all necessary substitutions.

The values you assign to the arguments of a query or stored procedure usually come from controls on a Windows form and in most cases from TextBox controls. The following statement reads the text in the txtFax TextBox control and assigns it to the @FAX parameter of a Command object:

Command.Parameters("@FAX").Value = txtFax.Text

But what if the user has left the txtFax TextBox blank? Should we pass to the INSERT statement an empty string or a null value? If you collect the values from various controls on a form and use them as parameter values, you'll never send null values to the database. If you want to treat empty strings as null values, you must pass a null value to the appropriate parameter explicitly. Let's say that the txtFax TextBox control on the form corresponds to the @FAX parameter. You can use the IIf() statement of Visual Basic to assign the proper value to the corresponding parameter as follows:

UPDATECommand.Parameters("@FAX").Value =
     IIf(txtFax.Text.Trim.Length = 0,
             System.DBNull.Value, txtFax.Text)

This is a lengthy statement, but here's how it works: The IIf() function evaluates the specified expression. If the length of the text in the txtFax control is zero, it returns the value specified by its second argument, which is the null value. If not — in other words, if the TextBox control isn't empty — it returns the text on the control. This value is then assigned to the @FAX parameter of the UPDATECommand object.

Another interesting aspect of the DataReader object is that you can use it to read multiple result sets, such as the ones returned by multiple queries. You can execute a batch query such as the following with a single Command object:

Command.CommandText = "SELECT * FROM Customers; SELECT * FROM Employees"
Dim Reader As SqlDataReader = Command.ExecuteReader

We'll use the same DataReader object to read the rows of both tables, but we need to know when we're finished with the first result set (the customers) and start reading the second result set. The NextResult property of the DataReader does exactly that: After exhausting the first result set (by iterating through its rows with the Read method), we can request the NextResult property to find out whether the DataReader contains additional result sets. If so, we can start reading the next result set with the Read method. Here's the outline of the code for reading two result sets from the same DataReader:

While Reader.Read
   ' read the fields of the current row in the 1st result set
End While
If Reader.NextResult
    While Reader.Read
       ' read the fields of the current row in the 2nd result set
    End While
End If

In this section I'll put together all the information presented so far in this chapter to build a data-driven application — an application that actually talks to a database, retrieves data, and displays it on a Windows form. The application is not new to you. In Chapter 5, "Basic Windows Controls," you created an application for maintaining a list of contacts, based on a ListBox control, where you stored the names of the contacts. The same ListBox control was also used as a navigational tool, because users could select a contact and view their details, as shown in Figure 15.8.

Figure 15.8. The Contacts application's interface

The contacts were stored in a List of Contact objects, and they were persisted to a file. Now let's revise this application so that it works with a database, namely, the Customers table of the Northwind database. The columns of the Customers table are almost identical to the fields of the Contact object, so you'll use the same data type to store the data. Instead of persisting the data to a file, you'll read your contacts from the database and submit the edited rows back to the same database. Because you'll use a central data storage, the revised application can be used by multiple users at the same time.

Start by copying the Contacts project folder to a new folder with a different name. Then edit the menu, as follows:

Most of the code remains the same. You only need to replace the routines that perform the basic operations against the database. We no longer read the data from an external file. Instead, we must execute a query against the database and retrieve the company names along with their IDs. The Load command's code follows in Listing 15.4.

Private Sub LoadToolStripMenuItem_Click(...) Handles
            LoadToolStripMenuItem.Click
    CMD.CommandText = "SELECT * FROM Customers"
    CN.Open()
    Dim RDR As SqlDataReader
    RDR = CMD.ExecuteReader
    ListBox1.Items.Clear()
    While RDR.Read
        Dim C As New Contact
        C.CustomerID = RDR.Item("CustomerID")
        C.CompanyName = RDR.Item("CompanyName")
        C.ContactName = Convert.ToString(
                  IIf(RDR.IsDBNull(
                             RDR.GetOrdinal("ContactName")),
                             "", RDR.Item("ContactName")))
        C.Address1 = Convert.ToString(
                  IIf(RDR.IsDBNull(
                             RDR.GetOrdinal("Address")),
                             "", RDR.Item("Address")))
        ' Similar statements for the remaining fields
        ListBox1.Items.Add(C)

End While
    CN.Close()
    currentContact = 0
    ShowContact()
End Sub

The code executes a simple SELECT query against the database and then loads the ListBox control with Contact objects as before. The only difference is that now the data comes from the DataReader. You will also notice the lengthy expressions that assign the values read from the database to the TextBox controls on the form. The expression RDR.Item(field_name) reads the corresponding column's value from the current row. This row, however, may be null, and you can't assign a null value to the Text property. So, the code uses the IIf() function to determine whether the current field's value is null. If the method IsDBNull returns True, then the current field is null, and the IIf() function returns an empty string. If not, it returns the actual field value, which is the expression RDR.Item(field_name). But what about the call to the GetOrdinal method? Well, the IsDBNull method doesn't accept a column name as an argument, only an integer that is the order of the column in the result. Instead of hard-coding numeric values to our code (and make it impossible to maintain later), we can retrieve the ordinal of a column with the GetOrdinal method. The expression GetOrdinal("CustomerID") returns 0 because the CustomerID column is the first one, GetOrdinal("CompanyName") returns 1, and so on. Finally, because the IIf() function returns an object, we must cast it to a string before assigning it to the Text property.

Now, this is the type of code that can be streamlined, and there are tools that do it for you. Even better, there are tools that convert each row to a custom object (a Contact object, in our example), so you can handle the data you retrieve from the database with the object-oriented techniques you have learned in this book. The new data access technologies, including LINQ to SQL that was discussed in Chapter 14, "An Introduction to LINQ," bridge the gap between databases and object-oriented programming. As you have already noticed, there's a mismatch between objects and the way we access databases. Having to set up a DataReader, execute commands directly against the database, and then having to worry about null and converting the fields to the proper type takes a lot of code, and the entire approach just isn't elegant. You'll see better techniques for accessing databases in the following chapter, but I wanted to show you the basic data access mechanisms first (actually you already saw how to access databases with LINQ to SQL queries in the preceding chapter).This may not be so elegant, but they're the foundation on which more elaborate tools were built. Moreover, they're the fastest way to get data out of a database and back. That said, let's continue with our sample application.

Next, we must implement the procedures for adding a new contact (the SubmitContact subroutine), for updating an existing contact (the UpdateContact subroutine), and for deleting a contact (the RemoveContact subroutine). The first two subroutines accept as an argument a variable of the Contact type, form the appropriate SQL statement, and execute it against the database. The RemoveContact subroutine accepts the ID of the row to be deleted, forms the appropriate DELETE statement, and executes it likewise. You may wonder why I haven't implemented these routines as functions that return a True/False value indicating whether the operation completed successfully. The reason is that a simple indication about the success of an operation won't suffice; we need to display a more specific error message to the user, so I've decided to throw an exception with the error description, as shown in Listings 15.5, 15.6, and 15.7.

Private Sub SubmitContact(ByVal C As Contact)
    CMD.CommandText = "INSERT Customers " &
              "(CustomerID, CompanyName, ContactName, Address, " &
              " City, Region, PostalCode, Country) " &
              "VALUES (@CustomerID, @CompanyName, @ContactName, " &
              "@Address, @City, @Region, @PostalCode, @Country) "
    CMD.Parameters.Clear()
    CMD.Parameters.AddWithValue("@CustomerID", C.CustomerID)
    CMD.Parameters.AddWithValue("@CompanyName", C.CompanyName)
    CMD.Parameters.AddWithValue("@ContactName", C.ContactName)
    CMD.Parameters.AddWithValue("@Address", C.Address1)
    CMD.Parameters.AddWithValue("@City", C.City)
    CMD.Parameters.AddWithValue("@Region", C.State)
    CMD.Parameters.AddWithValue("@PostalCode", C.ZIP)
    CMD.Parameters.AddWithValue("@Country", C.ZIP)
    CN.Open()
    Try
        CMD.ExecuteNonQuery()
    Catch ex As Exception
        Throw New Exception(
                 "Failed to update contact in database. " &
                 vbCrLf & "ERROR MESSAGE: " & vbCrLf & ex.Message)
    Finally
        CN.Close()
    End Try
    CN.Close()
End Sub

Private Sub UpdateContact(ByVal C As Contact)
    CMD.CommandText = "UPDATE Customers " &
                "SET CompanyName = @CompanyName, " &
                "    ContactName = @ContactName, " &
                "    Address = @Address, " &
                "    City = @City, " &
                "    Region = @Region, " &
                "    PostalCode = PostalCode" &
                "    Country = @Country " &
                "WHERE CustomerID = @CustomerID"
    CMD.Parameters.Clear()
    CMD.Parameters.AddWithValue("@CustomerID", C.CustomerID)
    CMD.Parameters.AddWithValue("@CompanyName", C.CompanyName)
    CMD.Parameters.AddWithValue("@ContactName", C.ContactName)

CMD.Parameters.AddWithValue("@Address", C.Address1)
    CMD.Parameters.AddWithValue("@City", C.City)
    CMD.Parameters.AddWithValue("@Region", C.State)
    CMD.Parameters.AddWithValue("@PostalCode", C.ZIP)
    CN.Open()
    Try
        CMD.ExecuteNonQuery()
    Catch ex As Exception
        Throw New Exception(
             "Failed to update contact in database. " & vbCrLf &
             "ERROR MESSAGE: " & vbCrLf & ex.Message)
    End Try
    CN.Close()
End Sub

Private Sub RemoveContact(ByVal ContactID As String)
    CMD.CommandText = "DELETE Customers WHERE CustomerID=@contactID "
    CMD.Parameters.Clear()
    CMD.Parameters.AddWithValue("@contactID", ContactID)
    CN.Open()
    Try
        CMD.ExecuteNonQuery()
    Catch ex As Exception
        Throw New Exception(
                "Failed to delete contact in database. " & vbCrLf &
                "ERROR MESSAGE: " & vbCrLf & ex.Message)
    Finally
        CN.Close()
    End Try
End Sub

The rest of the code is basically the same. We started with an application that manipulates Contact objects in a List and converted it to an application that manipulates the rows of a database table. We changed the routines that read data and submit data to the database. The routines that read the data from the database create Contact objects that are stored to the ListBox control as with the original application. Likewise, every time the user inserts a new contact or updates an existing one, instead of modifying an item in the List control, we submit it to the database and update the underlying source (the Customers table). Finally, when a contact is removed, the application removes it directly from the Customers table. The deletion operation may fail if the customer has placed an order. As you realize, we're no longer dealing with an isolated table but with a larger system with related tables, and the DBMS maintains the integrity of our data.

This application has a serious flaw, though. What if the Customers table has thousands of customers and there are a few dozen users who may need access the database? Would it make sense to download the entire table to the client and maintain such an enormous list of customers at every client? The first improvement I'd suggest is to download only the customer names and display them on the ListBox control. Every time the user clicks a different customer's name, your code should execute a SELECT statement for the specific customer and bring the entire row to the client and display its fields.

Even so, the table may grow so large that it wouldn't make sense to display all company names in a list. In this case, you should provide some selection mechanism to force users to download the names of selected companies only. For example, you could display a list of countries and download only customers from the selected customer every time users selected another country. You could also filter by company or contact name or provide combinations of criteria. If you decide to modify the application, here are the SELECT statements that limit customers by country and company name:

SELECT CustomerID, CompanyName FROM Customers WHERE Country = @country
SELECT CustomerID, CompanyName FROM Customers WHERE CompanyName LIKE @name + '%'

(The percent symbol at the end of the string is a wildcard, indicating that the company name should start with the string stored in the @name variable, followed by any other combination of characters.)

One last hitch in this application is the following: What will happen if other users delete a contact you're editing or someone else updates the contact you're editing? Issues of concurrency are very important in designing data-driven applications, and we'll look at this in the following chapter. The Contacts project assumes that the same row won't be edited by multiple users at once. Even when this happens, the last user to save the edits overwrites the edits of the other users. It's a bit of a crude approach, but it's quite appropriate in many situations. In other situations, most notably in reservation applications, it's unacceptable.