Accessing and Manipulating SQL Server Data

   


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.

STEP BY STEP

1.12 Running a Query from the Visual Studio .NET IDE

  1. Open a Visual Basic .NET Windows Application in the Visual Studio .NET IDE.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database, and then the Views node of the SQL Server.

  4. Right-click the Views node and select New View.

  5. Click Close on the Add Table dialog box.

  6. In the SQL pane of the View Designer (the area that displays the text SELECT FROM ), type this SQL statement (replacing the existing text):

     SELECT * FROM Employees 
  7. Select Query, Run from the Visual Studio menu, or click the Run Query button on the View toolbar to send the SQL statement to SQL Server and display the results, as shown in Figure 1.21.

    Figure 1.21. Running an ad hoc query directly from the Visual Studio .NET IDE.

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).

STEP BY STEP

1.13 Running a Query from Osql

  1. Open a Windows command prompt.

  2. To launch osql and log in using Windows integrated authentication, type

     osql -E 
  3. To execute a query in osql, you must first tell it which database to use. Type the following:

     use Northwind 
  4. Next you must enter the query to execute. Type the following:

     SELECT FirstName, LastName FROM Employees 
  5. Finally, you must tell osql to execute the SQL statements that you just entered. Type the following:

     GO 
  6. When you're done with osql, type

     exit 

    Here's the entire osql session, including the prompts from osql:

     C:\>osql -E 1> use Northwind 2> SELECT FirstName, LastName FROM Employees 3> GO  FirstName  LastName  ---------- --------------------  Nancy      Davolio  Max        Fuller  Janet      Leverling  Margaret   Peacock  Steven     Buchanan  Michael    Suyama  Robert     King  Laura      Callahan  Anne       Dodsworth (9 rows affected) 1> exit C:\> 

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).

STEP BY STEP

1.14 Running a Query from SQL Query Analyzer

  1. Select Start, Programs, Microsoft SQL Server, Query Analyzer.

  2. SQL Query Analyzer will launch and display the Connect to SQL Server dialog box. To choose a SQL Server to work with, you can type the name of a SQL Server, or type the special name (local) to use a SQL Server on the same computer as SQL Query Analyzer. You can also use the browse button to list all servers on the network. After selecting a server and filling in your authentication information, click OK.

  3. Select the Northwind database from the databases combo box on the SQL Query Analyzer toolbar.

  4. Type a query in the Query window:

     SELECT * FROM Employees 
  5. Select Query, Execute, click the Execute button on the toolbar, or press F5 to run the query. This will send the SQL statement to SQL Server and display the results, as shown in Figure 1.22.

    Figure 1.22. Running an ad hoc query in SQL Query Analyzer.

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.

STEP BY STEP

1.15 Running a Query from a Custom Form

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form to create a SqlConnection1 object on the form. This object represents a connection to SQL Server.

  4. Add a TextBox control named txtQuery , a Button control named btnExecute , and a DataGrid control named dgResults to the form. Set the Multiline property of the TextBox to True. Set the CaptionVisible property of the DataGrid to False.

  5. Double-click the Button control to open the form's module. Enter two statements at the top of the module to make the ADO.NET objects available:

     Imports System.Data Imports System.Data.SqlClient 
  6. Enter this code to execute the query when you click the Button control:

     Private Sub btnExecute_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnExecute.Click     ' Create a SqlCommand to represent the query     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.Text     cmd.CommandText = txtQuery.Text     ' Create a SqlDataAdapter to talk to the database     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     ' Create a DataSet to hold the results     Dim ds As DataSet = New DataSet()     ' Fill the DataSet     da.Fill(ds, "Results")     ' And bind it to the DataGrid     dgResults.DataSource = ds     dgResults.DataMember = "Results" End Sub 
  7. Set the form as the startup object for the project.

  8. Run the project. Enter a query in the text box:

     SELECT * FROM Employees 
  9. Click the button to run the code, retrieving the results to the DataGrid, as shown in Figure 1.23.

    Figure 1.23. Running an ad hoc query from a custom form.

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.

Figure 1.24. Resultset from a query that includes a GROUP BY clause.

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.

REVIEW BREAK

  • Transact-SQL is the Microsoft SQL Server dialect of the ANSI SQL-92 standard query language.

  • You can execute T-SQL statements from a variety of interfaces, including the Visual Studio .NET IDE, osql, SQL Query Analyzer, or custom applications.

  • SELECT statements retrieve data from tables in a database.

  • INSERT statements add new data to tables in a database.

  • UPDATE statements modify existing data in tables in a database.

  • DELETE statements remove data from tables in a database.

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).

STEP BY STEP

1.16 Creating a Stored Procedure from the Visual Studio .NET IDE

  1. Open a Visual Basic .NET Windows application in the Visual Studio .NET IDE.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database, and then the Stored Procedures node of the SQL Server.

  4. Right-click the Stored Procedures node and select New Stored Procedure.

  5. Replace the boilerplate code in the Stored Procedure designer with this code:

     CREATE PROCEDURE procFranceCustomers AS     SELECT * FROM Customers     WHERE Country = 'France' 
  6. Click the Save button to save the stored procedure to the database.

  7. Select Database, Run Stored Procedure to run the CREATE PROCEDURE statement. This creates the stored procedure in the database.

  8. Now you can execute the new procFranceCustomers stored procedure from any tool that allows you to execute SQL Statements. For example, Figure 1.25 shows the results of executing this stored procedure in the custom form you built in Step By Step 1.15.

    Figure 1.25. The results of running a stored procedure are the same as the results of running the T-SQL statements contained in the stored procedure.

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).

STEP BY STEP

1.17 Running a Stored Procedure from Visual Basic .NET

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form. This will create a SqlConnection1 object on the form.

  4. Add a DataGrid control named dgResults to the form.

  5. Double-click the form to open the form's module. Enter two statements at the top of the module to make the ADO.NET objects available:

     Imports System.Data Imports System.Data.SqlClient 
  6. Enter this code to execute the stored procedure when you load the form:

     Private Sub StepByStep1_17_Load(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles MyBase.Load     ' Create a SqlCommand to represent     ' the stored procedure     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.StoredProcedure     cmd.CommandText = "procFranceCustomers"     ' Create a SqlDataAdapter to talk to the database     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     ' Create a DataSet to hold the results     Dim ds As DataSet = New DataSet()     ' Fill the DataSet     da.Fill(ds, "Customers")     ' And bind it to the DataGrid     dgResults.DataSource = ds     dgResults.DataMember = "Customers" End Sub 
  7. Set the form as the startup object for the project.

  8. Run the project. This will run the code, retrieving the results to the DataGrid, as shown in Figure 1.26.

    Figure 1.26. Displaying results from a stored procedure.

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.

STEP BY STEP

1.18 Running a Stored Procedure That Does Not Return Results

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form. This will create a SqlConnection1 object on the form.

  4. Use a tool such as SQL Query Analyzer or the Visual Studio .NET IDE to create a stored procedure with this code:

     CREATE PROCEDURE procExpandCountry AS UPDATE Customers  SET Country = 'United States'  WHERE Country = 'USA' 
  5. Place a Button control on the form and name it btnExecute .

  6. Double-click the Button control to open the form's module. Enter this statement at the top of the module to make the ADO.NET objects available:

     Imports System.Data.SqlClient 
  7. Enter this code to execute the stored procedure when you click the button:

     Private Sub btnExecute_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnExecute.Click     ' Create a SqlCommand to represent     ' the stored procedure     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.StoredProcedure     cmd.CommandText = "procExpandCountry"     ' Open the connection and execute     ' the stored procedure     SqlConnection1.Open()     cmd.ExecuteNonQuery()     ' Close the connection     SqlConnection1.Close()     MessageBox.Show("SQL statement was executed.") End Sub 
  8. Set the form as the startup object for the project.

  9. Run the project and click the button to execute the stored procedure and display a message box when the stored procedure has completed its work.

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).

STEP BY STEP

1.19 Running a Parameterized Stored Procedure

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form to create a SqlConnection1 object on the form.

  4. Use a tool such as SQL Query Analyzer or the Visual Studio .NET IDE to create a stored procedure with this code:

     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 
  5. Place two Label controls, two TextBox controls ( txtCustomerID and txtTotalSales ) and a Button control ( btnGetTotalSales ) on the form, as shown in Figure 1.27.

    Figure 1.27. Designing a form to execute a parameterized stored procedure.

  6. Double-click the Button control to open the form's module. Enter these two statements at the top of the module to make the ADO.NET objects available:

     Imports System.Data.SqlClient Imports System.Data.SqlTypes 
  7. Enter this code to execute the stored procedure when you click the button:

     Private Sub btnGetTotalSales_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnGetTotalSales.Click     ' Create a SqlCommand to represent     ' the stored procedure     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.StoredProcedure     cmd.CommandText = "procCustomerSales"     ' Add the input parameter and set its value     cmd.Parameters.Add(New SqlParameter(_      "@CustomerID", SqlDbType.Text, 5))     cmd.Parameters("@CustomerID").Value = _      txtCustomerID.Text     ' Add the output parameter and set its direction     cmd.Parameters.Add(New SqlParameter(_      "@TotalSales", SqlDbType.Money))     cmd.Parameters("@TotalSales").Direction = _       ParameterDirection.Output     ' Execute the stored procedure and     ' display the formatted results     SqlConnection1.Open()     cmd.ExecuteNonQuery()     txtTotalSales.Text = String.Format("{0:c}", _      cmd.Parameters("@TotalSales").Value)     SqlConnection1.Close() End Sub 
  8. Set the form as the startup object for the project.

  9. Run the project and enter a CustomerID from the Customers table (such as ALFKI or BONAP) in the first text box. Click the button to execute the stored procedure and return the total sales for this customer in the second text box.

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 Retrieving a New Identity Value

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form to create a SqlConnection1 object on the form.

  4. Use a tool such as SQL Query Analyzer or the Visual Studio .NET IDE to create a stored procedure with this code:

     CREATE PROC procInsertShipper   @CompanyName nvarchar(40),   @ShipperID int OUTPUT AS   INSERT INTO Shippers (CompanyName)     VALUES (@CompanyName)   SELECT @ShipperID = @@IDENTITY 

    This stored procedure contains two SQL statements. The first inserts a row into the Shippers table, and the second retrieves the value of the identity column for the new row.

  5. Place two Label controls, two TextBox controls ( txtCompanyName and txtShipperID ) and a Button control ( btnAddShipper ) on the form.

  6. Double-click the Button control to open the form's module. Enter these two statements at the top of the module to make the ADO.NET objects available:

     Imports System.Data.SqlClient Imports System.Data.SqlTypes 
  7. Enter this code to execute the stored procedure when you click the button:

     Private Sub btnAddShipper_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnAddShipper.Click     ' Create a SqlCommand to represent     ' the stored procedure     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.StoredProcedure     cmd.CommandText = "procInsertShipper"     ' Add the input parameter and set its value     cmd.Parameters.Add(New SqlParameter(_      "@CompanyName", SqlDbType.VarChar, 40))     cmd.Parameters("@CompanyName").Value = _      txtCompanyName.Text     ' Add the output parameter and set its direction     cmd.Parameters.Add(New SqlParameter(_     "@ShipperID", SqlDbType.Int))     cmd.Parameters("@ShipperID").Direction = _      ParameterDirection.Output     ' Execute the stored procedure and     ' display the result     SqlConnection1.Open()     cmd.ExecuteNonQuery()     txtShipperID.Text = cmd.Parameters(_      "@ShipperID").Value     SqlConnection1.Close() End Sub 
  8. Set the form as the startup object for the project.

  9. Run the project and enter a company name for the new shipper in the first text box. Click the button to execute the stored procedure and return the identity value assigned to the new shipper in the second text box.

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.

GUIDED PRACTICE EXERCISE 1.1

In this exercise, you'll be designing a form to enter new products into the Northwind database. Table 1.3 shows the columns that the Products table contains.

Table 1.3. Northwind Products Table

Column Name

Data Type

Nullable?

Identity?

ProductID

int

No

Yes

ProductName

nvarchar(40)

No

No

SupplierID

int

Yes

No

CategoryID

int

Yes

No

QuantityPerUnit

nvarchar(20)

Yes

No

UnitPrice

money

Yes

No

UnitsInStock

smallint

Yes

No

UnitsOnOrder

smallint

Yes

No

ReorderLevel

smallint

Yes

No

Discontinued

bit

No

No

Allow the user to enter at least the product name and CategoryID, to add the product to the table, and to see the ProductID that's assigned to the new row in the table. You might optionally allow the user to input any other data that you like.

Valid values for the CategoryID column can be determined by retrieving the CategoryID values from the Categories table, which also contains a CategoryName column. You should use a ComboBox control to display valid CategoryID values.

Try this on your own first. If you get stuck or would like to see one possible solution, follow these steps:

  1. Add a new form to your Visual Basic .NET project.

  2. Open Server Explorer.

  3. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database. Drag and drop the data connection to the form to create a SqlConnection1 object on the form.

  4. Add Label controls, a ComboBox control ( cboCategoryID ), a Button control ( btnAddProduct ), and two TextBox controls ( txtProductName and txtProductID ) to the form. Figure 1.28 shows a design for the form.

    Figure 1.28. Designing a form to enter product information.

  5. Use a tool such as SQL Query Analyzer or the Visual Studio .NET IDE to create a stored procedure with this code:

     CREATE PROC procInsertProduct   @ProductName nvarchar(40),   @CategoryID int,   @ProductID int OUTPUT AS   INSERT INTO Products (ProductName, CategoryID)     VALUES (@ProductName, @CategoryID)   SELECT @ProductID = @@IDENTITY 
  6. Double-click the form to open the form's module. Enter these three statements at the top of the module to make the ADO.NET objects available:

     Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes 
  7. Enter code to fill the list in the ComboBox control when the form is opened:

     Private Sub GuidedPracticeExercise6_1_Load(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles MyBase.Load     ' Retrieve data for the combo box     Dim cmdCategories As SqlCommand = _      SqlConnection1.CreateCommand()     cmdCategories.CommandType = CommandType.Text     cmdCategories.CommandText = _      "SELECT CategoryID, CategoryName " & _      "FROM Categories ORDER BY CategoryName"     Dim ds As DataSet = New DataSet()     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmdCategories     da.Fill(ds, "Categories")     With cboCategoryID         .DataSource = ds.Tables("Categories")         .DisplayMember = "CategoryName"         .ValueMember = "CategoryID"     End With End Sub 
  8. Enter this code to execute the stored procedure when you click the button:

     Private Sub btnAddProduct_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnAddProduct.Click     ' Create a SqlCommand to represent     ' the stored procedure     Dim cmd As SqlCommand = _      SqlConnection1.CreateCommand     cmd.CommandType = CommandType.StoredProcedure     cmd.CommandText = "procInsertProduct"     ' Add the input parameters and set their values     cmd.Parameters.Add(New SqlParameter(_      "@ProductName", SqlDbType.VarChar, 40))     cmd.Parameters("@ProductName").Value = _      txtProductName.Text     cmd.Parameters.Add(New SqlParameter(_      "@CategoryID", SqlDbType.Int))     cmd.Parameters("@CategoryID").Value = _      cboCategoryID.SelectedValue     ' Add the output parameter and set its direction     cmd.Parameters.Add(New SqlParameter(_      "@ProductID", SqlDbType.Int))     cmd.Parameters("@ProductID").Direction = _      ParameterDirection.Output     ' Execute the stored procedure and     ' display the result     SqlConnection1.Open()     cmd.ExecuteNonQuery()     txtProductID.Text = cmd.Parameters(_      "@ProductID").Value     SqlConnection1.Close() End Sub 
  9. Set the form as the startup object for the project.

  10. Run the project. Select a category for the new product from the combo box. Enter a name for the new product in the first text box. Click the button. The form will execute the stored procedure and return the identity value assigned to the new product in the second text box.

REVIEW BREAK

  • Stored procedures provide a way to keep compiled SQL statements on the database server.

  • The ADO.NET SqlCommand object enables you to execute stored procedures.

  • Stored procedures can have both input and output parameters. Input parameters are variables that are used by the stored procedure. Output parameters let the stored procedure return results to the caller.

  • The @@IDENTITY variable returns the most recent identity value from the connection.


   
Top


MCAD. MCSD Training Guide (Exam 70-310. Developing XML Web Services and Server Components with Visual Basic. NET and the. NET Framework)
MCAD/MCSD Training Guide (70-310): Developing XML Web Services and Server Components with Visual Basic(R) .NET and the .NET Framework
ISBN: 0789728206
EAN: 2147483647
Year: 2002
Pages: 166

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net