MCAD/MCSD Training Guide (70-310): Developing XML Web Services and Server Components with Visual Basic(R) .NET and the .NET Framework

Access and manipulate data from a Microsoft SQL Server database by creating and using ad hoc queries and stored procedures.

You might be a bit surprised to find a Microsoft SQL Server objective on a Visual Basic .NET certification exam, but this really makes perfect sense. Many Visual Basic .NET applications require a database to enable them to store data on a permanent basis, and SQL Server is one of the best databases to use with .NET.

As you'll see later in this chapter, an entire namespace (System.Data.SqlClient) is devoted to efficient communication between .NET applications and SQL Server.

The objects in System.Data.SqlClient, though, won't do you any good unless you understand the language used to communicate with SQL Server, Transact-SQL (T- SQL ) . T-SQL is Microsoft's implementation of SQL (Structured Query Language), which is defined by a standard from the American National Standards Institute (ANSI). The core of T-SQL is based on the ANSI SQL-92 standard. SQL-92 defines a query-oriented language in which you submit queries to the database and get back a resultset consisting of rows and columns of data. Other queries cause changes to the database (for example, adding, deleting, or updating a row of data) without returning any resultset.

You can submit T-SQL to a SQL Server database for processing in two ways. First, you can write ad-hoc queries , SQL statements that are executed directly. Second, you can write stored procedures , SQL statements that are stored on the server as a named object. The .NET Framework includes facilities for running both ad hoc queries and stored procedures.

Using Ad Hoc Queries

Ad hoc T-SQL queries provide an extremely flexible way to retrieve data from a SQL Server database or to make changes to that database. In this section of the chapter, I'll show several ways to send an ad hoc query to SQL Server. Then you'll learn the basics of the four main T-SQL statements that help manipulate SQL Server data:

• SELECT statements allow you to retrieve data stored in the database.

• INSERT statements allow you to add new data to the database.

• UPDATE statements allow you to modify data already in the database.

• DELETE statements allow you to delete data from the database.

EXAM TIP

SQL Statement Formatting You'll usually see SQL keywords (such as SELECT , INSERT , UPDATE , and DELETE ) formatted entirely in uppercase. I'll follow that convention in this book, but uppercase formatting isn't required by SQL Server. You might see these same keywords in mixed case or lowercase on an exam. As far as SQL Server is concerned , there's no difference between SELECT , Select , and select .

Running Queries

When learning T-SQL, it's useful to be able to send queries to a SQL Server database and to see the results (if any) that the server returns. You should be aware of the many ways to communicate with SQL Server. I'll show you four of them in this section:

• Using the Visual Studio .NET IDE

• Using Osql

• Using SQL Query Analyzer

• Using a Visual Basic .NET Application

NOTE

SQL Dialects Microsoft SQL Server isn't the only product that implements the SQL-92 standard. Other products, including Microsoft Access and Oracle, also use SQL-92based query languages. However, databases differ in their treatment of SQL in many subtle ways. Most databases contain extensions to SQL-92 (keywords that are only understood by that particular database), and most don't implement the entire SQL-92 standard. The SQL statements in this chapter are from the shared core of SQL-92 that's identical in nearly all database products, so they should work whether you're using SQL Server, Access, or Oracle (among others). But as you study the more advanced features of SQL Server, you should keep in mind that T-SQL statements will not necessarily run without changes on other database servers.

The Northwind Sample Database Whenever I've used data from a database in this book, I've used the Northwind sample database that comes as part of SQL Server 2000. Visual Studio .NET includes MSDE, a stripped-down version of SQL Server that you can use if you don't have the full version installed. See your Visual Studio CD's readme file for information on installing MSDE.

Using the Visual Studio .NET IDE

When you just need to run a query in the course of working with a project, you can run it directly from the Visual Studio .NET IDE. Step by Step 1.12 shows you how.

EXAM TIP

SQL Statement Formatting If you refer to Figure 1.21, you'll see that Visual Studio .NET made some changes to the SQL statement that you typed. The original statement was

SELECT * FROM Employees

The statement that Visual Studio .NET turns this into is

SELECT * FROM dbo.Employees

I'd like to elaborate on two points here: First, SQL Server doesn't care about whitespace. You can insert spaces, tabs, or new lines between any SQL keywords without changing the statement. Second, every SQL Server object (such as the Employees table) has an owner. The default owner is a user named dbo (for database owner). You can add the name of the owner of an object to the object when referring to it. In the case of SQL statements on the exam, it's likely that every object will be owned by dbo, so don't get thrown if you see the dbo prefix on a table name .

When you run the query, Visual Studio .NET sends the SQL statement to the SQL Server that was specified by the database connection you chose in step 3. The server then processes the query (this particular query tells it to return all columns in all rows of the Employees table) and sends the results back to the client (in this case, Visual Studio .NET). The IDE then displays the results formatted as a grid.

The View Designer in Visual Studio .NET displays up to four panes. From top to bottom, these are

1. The Diagram pane, which displays the tables involved in the query and the relations between these tables, as well as all the columns that the tables contain.

2. The Grid pane, which shows the columns that have been selected as part of the query, as well as additional sorting and filtering information.

3. The SQL pane, which shows the actual SQL statement that will be executed.

4. The Results pane, which shows the results (if any) after the query has been executed.

The View toolbar includes buttons used to hide or show any of these four panes. For this chapter, you'll only need the SQL Results panes.

Using Osql

A second option for executing ad hoc queries is to use one of the utilities that ships as a part of SQL Server. The MSDE version of SQL Server that's shipped with Visual Studio .NET includes one of these utilities, osql. Osql is a command-line utility that can execute SQL Server queries (see Step By Step 1.13).

I chose a slightly different query for the osql session than I used in Step By Step 1.12. The SELECT query in Step By Step 1.13 specifies two columns from the table (FirstName and LastName), telling SQL Server to return only the contents of those two columns. If you execute SELECT * FROM Employees in osql, you might get a bit of a shock because the Employees table includes a bitmap image column, and the contents of that column will fill a command session with junk characters .

EXAM TIP

Obtaining SQL Query Analyzer SQL Query Analyzer is not included in the MSDE version of SQL Server. It's a part of all the other editions of SQL Server, so if you have another edition installed, you'll have SQL Query Analyzer available. Otherwise, you can download the 120-day trial version of SQL Server 2000 from http://www.microsoft.com/sql/evaluation/trial/2000/default.asp. This version also contains SQL Query Analyzer.

Using SQL Query Analyzer

Although osql can be convenient for quick queries, it doesn't offer much in the way of tools. SQL Server also offers a full-featured query environment called SQL Query Analyzer (see Step By Step 1.14).

SQL Query Analyzer offers an extremely flexible environment for running ad hoc queries. The features of SQL Query Analyzer include

• Multiple open query windows

• An Object Browser to see the structure of SQL Server objects

• Performance analysis

• Templates for common queries

For more information on using SQL Query Analyzer, refer to SQL Server Books Online, the help file installed as part of SQL Server.

Using a Visual Basic .NET Application

As a final alternative for executing ad hoc queries, Step By Step 1.15 shows how to build your own Visual Basic .NET form to execute any query.

You can learn about the ADO.NET objects that this example uses in Appendix A, "ADO.NET Basics." For now I'll give a quick preview of the objects I just used:

• The SqlConnection object represents a connection to a database.

• The SqlCommand object represents a single query that you can send to the server.

• The DataSet object represents the results of one or more queries.

• The SqlDataAdapter object acts as a pipeline between the SqlConnection and DataSet objects.

The code uses these objects to retrieve data from the SQL Server to the DataSet and uses the SQL statement that you typed to know which data to retrieve. It then uses complex data binding to display the results on the user interface in the DataGrid control. With complex data binding, an entire DataSet is bound to a user interface control in a single operation. This contrasts with simple data binding, which binds the contents of a single field to a control.

The SELECT Statement

Now that you know a variety of ways to execute ad hoc queries, it's time to dig into the T-SQL language to see some of the possible queries, starting with the SELECT statement.

The basic SQL statement is the SELECT statement. This statement is used to create a resultset. In skeleton form, a SELECT looks like this:

SELECT field_list FROM table_list WHERE where_clause GROUP BY group_by_clause HAVING having_clause ORDER BY sort_clause

EXAM TIP

Practice Using SQL You can use any of the methods you saw in the preceding section to execute the statements you're about to learn. You should execute enough of these statements to get a good idea of how the T-SQL language works. Just reading the descriptions here is no substitute for actually practicing with T-SQL. You're sure to see some SQL statements on the exam.

Each of those lines of code is called a clause . The SELECT and FROM clauses are required, and the rest are optional. Here's an example of a SQL statement containing only the required clauses:

SELECT OrderID, CustomerID FROM Orders

The resultset for this statement contains the values of the OrderID and CustomerID fields from every record in the Orders table.

Other results besides just lists of fields are attainable by the SELECT clause. You've already seen the shortcut for all fields:

SELECT * FROM Orders

You can also perform calculations in the SELECT clause:

SELECT OrderID, CAST(ShippedDate - OrderDate AS integer) AS Delay FROM Orders

The expression ShippedDate - OrderDate calculates the number of days between the two dates. The CAST function tells SQL Server to return the result as an integer. If you try that example, you'll see that the AS clause supplies a name for the calculated column. If you omit AS Delay , the query will still work, but SQL Server will return the calculation without assigning a name to the column.

You're also not limited to fields from a single table. For instance, you might try retrieving information from both the Customers and Orders tables with this query:

SELECT OrderID, Customers.CustomerID FROM Orders, Customers

Customers.CustomerID is what's known as a fully qualified name , specifying both the table name and the field name. This is necessary because both the Customers and the Orders tables contain fields named CustomerID, and you need to tell SQL Server which one you want to display.

If you try the previous query, though, you'll get more than 75,000 records backmany more than the number of orders in the database! That's because the query as written, although it includes all the proper tables, doesn't tell SQL Server how to relate those tables.

The name for this sort of query is a cross-product query. SQL Server constructs the resultset by including one row in the output for each row in each combination of input table rows. That is, there's an output row for the first order and the first customer, for the first order and the second customer, and so on. A more useful query, of course, matches each order with the corresponding customer.

That's the job of the INNER JOIN keyword. INNER JOIN tells SQL Server how to match two tables. Here's how the syntax looks for a fixed version of the original query:

SELECT OrderID, Customers.CustomerID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

NOTE

One Keyword or Two? Even though it's two words, INNER JOIN is referred to as a single SQL keyword because you can't have INNER in T-SQL unless you immediately follow it with JOIN.

This rewrite tells SQL Server to look at each row in the Orders table and match it with all rows in the Customers table where the CustomerID of the order equals the CustomerID of the customer. Because CustomerIDs are unique in the Customers table, this is tantamount to including only a single row for each order in the resultset.

The INNER JOIN keyword can appear more than once in a query for more than two tables to join. For example, here's a query to show EmployeeIDs along with Order and CustomerIDs:

SELECT Orders.OrderID, Customers.CustomerID, Employees.EmployeeID FROM Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID

Note the use of parentheses to specify the order in which the joins should be performed.

The basic SELECT query allows you to see all the data in a table; for example,

SELECT * FROM Orders

That query returns every bit of data in the Orders table: every column, every row. You've already seen that you can use a field list to limit the number of columns returned:

SELECT OrderID, CustomerID, EmployeeID FROM Orders

But what if you only want to see some of the rows in the table? That's where the WHERE clause comes into the picture. You can think of a WHERE clause as making a simple, yes-or-no decision for each row of data in the original table, deciding whether to include that row in the resultset.

The simplest form of the WHERE clause checks for the exact contents of a field; for example,

SELECT * FROM Orders WHERE ShipCountry = 'Brazil'

This query looks at every row in the Orders table and determines whether the ShipCountry field contains the exact value Brazil . If so, the row is included in the results. If not, it's discarded. However, WHERE clauses need not be exact. This is also a valid SQL statement:

SELECT * FROM Orders WHERE Freight > 50

In this case, you'll get all the rows in which the amount in the Freight field is greater than 50.

Note, by the way, that Brazil goes in quotation marks, whereas 50 doesn't. That's simply a syntax matter: Text and date data need quotation marks, but numeric columns don't.

You're free to combine multiple tests in a single WHERE clause; for example,

SELECT * FROM Orders WHERE ShipCountry = 'Brazil' AND Freight > 50 AND OrderDate <= '12/31/97'

This retrieves all orders that went to Brazil, had more than $50 of freight charges, and were shipped before the end of 1997. The key is that the entire WHERE clause must be a single logical predicate. That is, by evaluating all the pieces, the result must be a True or False value. Rows for which the WHERE clause evaluates to True are included in the results; rows for which it evaluates to False are excluded.

You can also use wildcards in a WHERE clause. Consider this simple SELECT statement:

SELECT * FROM Customers WHERE CustomerID = 'BLONP'

If you run that query, you'll find that it returns the record for Blondel pere et fils , the customer that is assigned the CustomerID BLONP. So far, that's easy. But what if you remember that the CustomerID starts with B , but not what it is exactly? That's when you'd use a wildcard:

SELECT * FROM Customers WHERE CustomerID LIKE 'B%'

The % wildcard matches zero or more characters, so the result of this query is to retrieve all the customers whose CustomerIDs begin with B . Note the switch from = to LIKE when using a wildcard (if you searched for CustomerID = ' B% ', you'd only find a customer with that exact ID).

Now suppose you almost remember the CustomerID, but not quite: Is it BLOND or BLONP? Try this query:

SELECT * FROM Customers WHERE CustomerID LIKE 'BLON_'

The _ wildcard matches precisely one characterso that would match BLONA, BLONB, and so on. If you're sure that it's either D or P , you can try the following:

SELECT * FROM Customers WHERE CustomerID LIKE 'BLON[DP]'

The [DP] is a character set wildcard. The square brackets tell SQL Server to match any one of the characters listed in the set. You can also use a dash in a character set to indicate a range:

SELECT * FROM Customers WHERE CustomerID LIKE 'BLON[D-P]'

That matches BLOND, BLONE, and so on, through BLONP. You can also invert a character set with the ^ character; for example,

SELECT * FROM Customers WHERE CustomerID LIKE 'BLON[^A-O]'

That matches BLONP, BLONQ, and so on but not BLONA, BLONB, or anything else that would match the character set without the ^ character.

SQL is a set-oriented language; by default, the database engine is free to return the set of results in any order it likes. To guarantee a sort order, include an ORDER BY clause in your SQL statement. For example, to see the customers from Venezuela in Postal Code order, you could use this statement:

SELECT * FROM Customers WHERE Country = 'Venezuela' ORDER BY PostalCode

That's the basic ORDER BY clause: a field name to sort by. You can use two keywords to modify this: ASC , for ascending sort (the default), and DESC , for descending sort. So, you could write the previous SQL statement as

SELECT * FROM Customers WHERE Country = 'Venezuela' ORDER BY PostalCode ASC

Or you could get the customers sorted in reverse postal code order with this statement:

SELECT * FROM Customers WHERE Country = 'Venezuela' ORDER BY PostalCode DESC

You're not limited to sorting by a single field. For example, you might want to see the entire Customer list, sorted first by country and then by postal code within country:

SELECT * FROM Customers ORDER BY Country, PostalCode

You can specify on a field-by-field basis the order of the sort:

SELECT * FROM Customers ORDER BY Country ASC, PostalCode DESC

That would sort by country in ascending order, and then by postal code in descending order within each country.

You can also calculate a sort. For example, you can sort the customers by the length of their company name:

SELECT * FROM Customers ORDER BY Len([CompanyName])

Here the square brackets tell the Len() function that it's being passed a column name, and to retrieve that column value for each row as the input to the function. In fact, the calculation need not have anything to do with the fields returned by the SELECT statement:

SELECT * FROM Customers ORDER BY 2+2

That's a perfectly valid SQL statement, though the effect is to put the records in whatever order the database engine decides it wants to use.

So far, all the SELECT statements you've seen in this chapter have returned results in which each row corresponds to one row in the underlying tables. However, it's possible (and indeed common) to use SQL to return aggregate, summarized information.

For example, suppose that you want to know how many customers you have in each country. This query will give you the answer:

SELECT Count(CustomerID) AS CustCount, Country FROM Customers GROUP BY Country

You can think of the GROUP BY clause as creating "buckets" in this case, one for each country. As the database engine examines each record, it tosses it in the appropriate bucket. After this process is done, it counts the number of records that ended up in each bucket and outputs a row for each one. Figure 1.24 shows the start of the resultset from this query.

You can use ORDER BY in conjunction with GROUP BY . In this case, you could sort by the number of customers in each country:

SELECT Count(CustomerID) AS CustCount, Country FROM Customers GROUP BY Country ORDER BY Count(CustomerID) DESC

Or by the country name:

SELECT Count(CustomerID) AS CustCount, Country FROM Customers GROUP BY Country ORDER BY Country

Count() in these SQL statements is an aggregate function , one that returns a result based on a number of rows. T-SQL supports a number of aggregate functions. Here are some of the most common:

• Count() Number of records

• Sum() Total value of records

• Avg() Average value of records

• Min() Smallest record

• Max() Largest record

You can also group on more than one field; for example,

SELECT Count(CustomerID) AS CustCount, Region, Country FROM Customers GROUP BY Region, Country

That statement sets up one bucket for each combination of region and country and categorizes the customers by both fields simultaneously .

So far, the GROUP BY statements you've seen have included all the records in the table. For example, consider this query:

SELECT ProductID, Sum(Quantity) AS TotalSales FROM [Order Details] GROUP BY ProductID ORDER BY Sum(Quantity) DESC

NOTE

Quoting Names This query uses square brackets to quote the name of the Order Details table because the table name has a space in it. Without the quoting, SQL Server would try to interpret it as two names.

That query returns a resultset that has one row for each product found in the Order Details table, with the ProductID and the total quantity of that ordered product.

As stated, that query uses all the rows in the Order Details table to come up with its totals. You can limit this to use only part of the table in two ways.

First, you can use a WHERE clause to limit the rows from the original query that will be included in the totals:

SELECT ProductID, Sum(Quantity) AS TotalSales FROM [Order Details] WHERE Quantity > 10 GROUP BY ProductID ORDER BY Sum(Quantity) DESC

That will have the same effect as the first query, except that it will just ignore any row in the Order Details table that has a quantity of 10 or under.

The other way to limit the results is by filtering the totals with a HAVING clause:

SELECT ProductID, Sum(Quantity) AS TotalSales FROM [Order Details] GROUP BY ProductID HAVING Sum(Quantity) > 1000 ORDER BY Sum(Quantity) DESC

A HAVING clause filters the results, rather than the input. That is, the last query will sum everything from the Order Details table and then show you rows in which the total is greater than 1000.

You can also combine the two types of filtering:

SELECT ProductID, Sum(Quantity) AS TotalSales FROM [Order Details] WHERE Quantity > 10 GROUP BY ProductID HAVING Sum(Quantity) > 1000 ORDER BY Sum(Quantity) DESC

That searches the source table for rows in which the quantity is greater than 10 and then only keeps those rows in which the total is more than 1,000.

Note that WHERE and HAVING go in two different places in the SQL statement. The order of clauses is fixed, not optional.

The INSERT Statement

The purpose of the INSERT statement is to add a row or multiple rows to a table through executing a SQL statement. In its simplest form, the insert query lists a target table and a set of values to insert. For example, this query adds a new row to the Order Details table:

INSERT INTO [Order Details] VALUES (10248, 1, 12.00, 5, 0)

This simple form of the statement has two drawbacks. First, knowing which field is getting which piece of data is difficult: The values are inserted into the table fields in the order that the fields show up in Design view, but you must remember (in this example) that the quantity is the fourth field. Second, if you use this format, you must supply a value for every field. This is a problem when you want the default value for a field or when a field can't have data inserted into it (for example, an identity field whose values are automatically generated by SQL Server). To get around these problems, a second format explicitly lists the fields for the target table:

INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10248, 2, 12.00, 5, 0)

Here, the first set of parentheses holds a column list, and the second set holds the values to insert. If a field has a default value, can be null, or is an identity field, you can leave it out of the field list:

INSERT INTO Products (ProductName, SupplierID, CategoryID) VALUES ('Turnips', 25, 7)

This works even though no value is specified for most of the fields in the Products table. Also, you can rearrange the field list as long as you rearrange the value list to match:

INSERT INTO Products (SupplierID, ProductName, CategoryID) VALUES (20, 'Lettuce', 7)

The insert query isn't limited to inserting a single record. A second format inserts the results of a SELECT statement into the target table. For example, this query will insert a product from every supplier into the Products table:

INSERT INTO Products (SupplierID, ProductName, CategoryID) SELECT SupplierID, 'Trout', 8 FROM Suppliers

This works by building the results of the SELECT statement and then putting each row returned by the SELECT into the target table. Of course, the columns still need to match up properly.

The UPDATE Statement

Another useful SQL statement is the UPDATE statement. As you can probably guess, the purpose of an UPDATE query is to update data. For example, you could update a field in a record in Northwind with this query:

UPDATE Customers SET ContactName = 'Maria Anderson' WHERE CustomerID = 'ALFKI'

In this query, the UPDATE keyword introduces an update query. The SET keyword tells SQL Server what to update. Here it's setting a field equal to a literal value. The WHERE clause tells SQL Server which row in the table to update.

You're not limited to updating a single record. If the WHERE clause selects multiple records, they'll all be updated:

UPDATE Customers SET Country = 'United States' WHERE Country = 'USA'

You can even update every row in a table by leaving out the WHERE clause:

UPDATE Products SET Discontinued = False

This will update every row in the Products table, even those in which the Discontinued field already has the value False.

You can also update more than one field at a time with an UPDATE query:

UPDATE Customers SET ContactName = 'Maria Anders', City = 'Berlin' WHERE CustomerID = 'ALFKI'

And you can update with the result of an expression:

UPDATE Products SET UnitPrice = UnitPrice * 1.1

If only it were so simple to raise prices in real life! Finally, you can update based on joined tables:

UPDATE Products SET Discontinued = 1 FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.Country = 'Italy'

That has the effect of discontinuing all the products imported from Italy.

The DELETE Statement

The DELETE statement removes data from a table. The rule for constructing a delete query is simple: Construct a select query to select the records you want to delete, and change the SELECT keyword to DELETE . Remove any * identifier from the SELECT clause as well. That's it!

To avoid destroying existing data, I'll use another query to set the stage. The SELECT INTO statement is used to create a new table. For example, this statement creates a table named BadCustomers with all the data from the existing Customers table:

SELECT * INTO BadCustomers FROM Customers

Here's a select query to select a single row from the new table:

SELECT * FROM BadCustomers WHERE CustomerID = 'GODOS'

Now change the SELECT * clause to DELETE :

DELETE FROM BadCustomers WHERE CustomerID = 'GODOS'

If you run this query, it will delete the specified row.

There's no need for a WHERE clause if you want to get really extreme:

DELETE FROM BadCustomers

That statement deletes all the rows from the BadCustomers table.

Using Stored Procedures

When you use an ad hoc query to interact with SQL Server, the SQL statements in the query are completely transient. They vanish as soon as you close whatever tool you've used to execute the query. By contrast, stored procedures are queries stored permanently on the SQL Server itself. Stored procedures have two main benefits. First, you can save complex SQL statements for future execution so that you don't have to re-create them from scratch. Second, SQL Server compiles stored procedures so that they run faster than ad hoc queries.

EXAM TIP

When to Use Stored Procedures In almost every case, stored procedures are preferable to ad hoc queries in production applications. The only time you should consider using ad hoc queries is when you're writing an application that must allow completely free-form querying by the end user. Otherwise, the additional development time required to implement stored procedures will be worth it in the end.

In this section, you'll see how to create and run stored procedures. I'll also discuss parameters, which make stored procedures more flexible, and the @@IDENTITY variable, which can supply useful information any time you use a stored procedure to insert data into a table with an identity column.

Creating a Stored Procedure

T-SQL includes a CREATE PROCEDURE keyword to create stored procedures. You can run CREATE PROCEDURE statements from any interface that allows you to enter and execute T-SQL (see Step By Step 1.16).

You can see two separate executing steps in this process. Executing the CREATE PROCEDURE statement (which is itself an ad hoc query) is necessary to create the stored procedure. After that has been done, you can execute the stored procedure itself to return results.

Running Stored Procedures from .NET

Executing a stored procedure from .NET is very similar to executing an ad hoc query. The difference is that you supply the name of the stored procedure instead of the actual SQL as the CommandText property of a SqlCommand object (see Step By Step 1.17).

Stored procedures are not limited to containing SELECT statements. You can place any SQL statement inside of a stored procedure.

For example, you might use this SQL statement to create a stored procedure to update the Customers table:

CREATE PROCEDURE procExpandCountry AS UPDATE Customers SET Country = 'United States' WHERE Country = 'USA'

When your stored procedure doesn't return a resultset, you need to use a slightly different code structure to execute it (see Step By Step 1.18). Guided Practice Exercise 1.1 offers additional practice in this technique.

EXAM TIP

Opening and Closing Connections When you call the methods of the SqlDataAdapter object, the .NET Framework will automatically open and close the associated SqlConnection object as necessary. For any other operation (such as using the SqlCommand. ExecuteNonQuery method), you must explicitly call the SqlConnection.Open and SqlConnection.Close methods in your code.

The ExecuteNonQuery method of the SqlCommand object can be used to execute any ad hoc query or stored procedure that doesn't return any results.

Using Parameters in Stored Procedures

The examples that you've seen so far don't begin to tap the real power of stored procedures. SQL Server supports parameterized stored procedures , which allow you to pass information to the stored procedure at runtime. (You can think of these as the T-SQL analog of Visual Basic .NET functions.) For example, this SQL statement defines a stored procedure that returns the total sales for a particular customer, with the CustomerID specified at runtime:

CREATE PROC procCustomerSales @CustomerID char(5), @TotalSales money OUTPUT AS SELECT @TotalSales = SUM(Quantity * UnitPrice) FROM ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) WHERE Customers.CustomerID = @CustomerID

In this SQL statement, both @CustomerID and @TotalSales are variables (called parameters in T-SQL). To use the stored procedure, you must supply a value for the @CustomerID parameter. The @TotalSales parameter is marked as an OUTPUT parameter; it returns a value from the stored procedure to the calling code.

In the .NET Framework, the SqlCommand object has a collection of Parameters to let you manage parameterized stored procedures (see Step By Step 1.19).

In ADO.NET, parameters are represented by SqlParameter objects. This code uses two different forms of the constructor for SqlParameters. The first takes the parameter name, the parameter data type, and the size of the parameter; the second omits the parameter size (because the money type has a fixed size ). The code works by setting the Value property of the @CustomerID parameter, executing the SqlCommand object, and then retrieving the Value property of the @TotalSales parameter.

The @@IDENTITY Variable

A SQL Server table can have a single identity column. An identity column is a column whose value is assigned by SQL Server itself whenever you add a new row to the table. The purpose of the identity column is to guarantee that each row in the table has a unique primary key.

If you're working with a table that contains an identity column, you'll often want to add a new row to the table and then immediately retrieve the value of the identity column for the new row. SQL Server provides a variable named @@IDENTITY for just this purpose. The @@IDENTITY variable returns the most recently assigned identity column value.

Step By Step 1.20 shows how to use a stored procedure to insert a new row in a table and return the value of the identity column so that your code can continue to work with the new row.

Step By Step 1.20 uses the same code pattern as Step By Step 1.19. The variable names and control names are different, but the two Step By Steps show a common pattern for using stored procedures in your code:

1. Create a SqlCommand object to represent the stored procedure.

2. Create SqlParameter objects to represent the parameters of the stored procedure.

3. Supply values for any input parameters.

4. Open the SqlConnection for this stored procedure.

5. Execute the stored procedure using the ExecuteNonQuery method of the SqlCommand object.

6. Retrieve values of any output parameters.

7. Close the SqlConnection.