Database Access with Visual Basic .NET (3rd Edition)
| A data manipulation command is an SQL statement that can alter records. Such commands are written by using a subset of SQL grammar called Data Manipulation Language. Its commands don't return records; instead, they make permanent changes to data in a database. You generally use SQL DML commands when you need to make changes to large amounts of data based on a criterion. For example, if you need to initiate a 10 percent across-the-board price increase in your products, you'd use an update query (one type of DML command) to change the prices of all the items in your inventory. Note The SQL examples in this section make permanent changes to data in your Novelty database. If you hopelessly mangle the data and want to return the data to the way it was initially, you can always reinstall it by running the Novelty script described in the Preface.
Visual Studio.NET provides a capable interface for executing DML commands. In fact, the tools provided in VS.NET can actually provide you with helpful information (such as the correct connection string to use to connect to a database) or by retrieving data from a table and changing the query type; it will generate the basic DML for you in a designer window. There are two tools, from a low-level perspective (that is, not much on GUI), that you can use to issue SQL DML commands to SQL Server:
You can use whichever tool you feel most comfortable with; in this chapter we use SQL Query Analyzer because it's easier to use and more feature-rich than osql. And, in this chapter, our focus is on the actual commands rather than how to use a specific GUI. You can find SQL Query Analyzer in the SQL Server program group. (In Chapter 7 we discuss use of the database manipulation features of VS.Net in more detail.) Update Commands
An update command has the capability to alter a group of records all at once. An update command has three parts:
For example, to increase the price of all the items in your inventory, you'd use the update command: UPDATE tblItem SET Price = Price * 1.1 SELECT * FROM tblItem The SELECT statement that follows the UPDATE isn't necessary to perform the update, of course it's just a way for you to see the results of the UPDATE once it's occurred. The contents of tblItem after you run the update query are as follows.
To limit the number of records affected by the update command, simply append a WHERE clause to the command. For example, to apply the price increase only to big-ticket items say, more than $100 you'd alter the SQL as follows: UPDATE tblInventory SET Price = Price * 1.1 WHERE Retail Price > 100 This command increases the retail price of items currently priced at more than $100 by 10 percent. Delete Commands
A delete command can delete one or more records in a table. For example, to delete all the orders placed before (but not on) last Halloween, you'd use the SQL statement DELETE * FROM tblOrder WHERE OrderDate < '10/31/98' Insert Commands
An insert command is used for two purposes:
To create an append query, use the SQL INSERT clause. The exact syntax of the query depends on whether you're inserting a single record or copying multiple records. For example, a single-record append query that adds a new order to tblOrder might look like this: INSERT INTO tblOrder (CustomerID, OrderDate) VALUES (119, '6/16/2001') Executing this query creates a new order for Customer 119, dated June 16, 2001, in tblOrder. Note In this update command, you don't append anything for tblOrder's ID field because it is an identity column. Attempting to do so would generate an error. In general, only the database engine itself can alter the contents of an identity column.
To create the kind of insert command that copies records from one table to another, use an INSERT clause in conjunction with a SELECT clause. For example, say that, instead of deleting old orders, you want to archive them by periodically copying them to an archive table called tblOrderArchive, which has the same structure as the tblOrder table. For that to work, you'll first need to create tblOrderArchive with an SQL command: CREATE TABLE tblOrderArchive ( ID [int] NOT NULL , CustomerID [int] NULL , OrderDate [datetime] NULL Note SQL commands that create and otherwise manipulate the structure of a database are called SQL Data Manipulation Language commands. We cover SQL DML later in this chapter.
An SQL statement to copy old records from the tblOrder table to the tblOrderArchive table might look like this: INSERT INTO tblOrderArchive SELECT * FROM tblOrder WHERE OrderDate < #6/1/2001# Executing this statement will copy all the records with order dates before June 1, 2001, into the tblOrderArchive table. Creating Tables with SELECT INTO
A SELECT INTO query is similar to an append query, except that it can create a new table and copy records to it in one fell swoop. (If you're coming from the Microsoft Access universe, this method is known as a make-table query.) For example, in the preceding demonstration you copied records from tblOrder to tblOrderArchive, presuming that tblOrderArchive actually exists. Instead, to copy the same records into a new table with the same structure as the original, you could use SELECT INTO, as in SELECT * INTO tblOrderArchive FROM tblOrder Note Executing this query copies all the records from tblOrder into a new table, tblOrder Archive. If tblOrderArchive already exists when the query is run, this command won't work. This be havior is different from the make-table query functionality provided by Microsoft Access; in Access, the existing table is deleted and replaced by the database engine with the contents of the copied records. To wipe out a table in SQL Server, you first need to use the DROP TABLE command-an SQL DDL command.
With SELECT INTO, you can apply selection criteria (using a WHERE clause) in the same way you apply criteria to an append query, as illustrated in the earlier section on append queries. Doing so enables you to copy a subset of records from the original table into the new table you create with a make-table query. |