| Structured Query Language (SQL) is a specialized set of database programming commands that enable the developer or other database users to perform tasks in a database management system. Some examples of the types of tasks you can perform with SQL are Retrieve data from one or more tables in one or more databases. Change data in tables by inserting, deleting, and updating records. Obtain summary information about the data in tables such as total and average values. Create, modify, or delete database objects such as tables, indexes, and queries.
A SQL statement contains one or more lines of text, much like a code statement in Visual Basic. As you can see from the preceding list, SQL statements fall into two basic categories: The first three items in the list are examples of how SQL is used to manipulate data in the database, the last item demonstrates how SQL can be used to define the database itself. In this chapter we will be mostly concerned with data manipulation statements. Specifically, we will be covering the data manipulation statements listed in Table 20.1. Table 20.1. Data Manipulation Statements| Statement | Function |
|---|
| SELECT | Retrieves a set of records from the database. | | INSERT INTO | Inserts a set of records into a table. | | UPDATE | Assigns new field values for a set of records. | | DELETE | Removes a set of records from a table. |
Note Data-manipulation language (DML), and data-definition language (DDL) are terms used to categorize SQL statements. Note SQL, the language, is different from SQL Server, the database management system created by Microsoft. The Structured Query Language is an ANSI standard that can be used on a variety of database management systems. Note The abbreviation SQL is pronounced like "sequel" in conversations, as in movie sequel. However, don't forget SQL is an acronym. Nothing screams beginner quite like someone who types "sequel server" in e-mail! Following Along with the Examples In later chapters, you will learn how to send SQL statements to a database and retrieve the results using a Visual Basic program. However, regardless of what interface you use to process a SQL statement, you still will need to learn to write SQL statements correctly. In addition, a working knowledge of SQL will be necessary to maintain and manage most databases. If you want to follow along with the SQL examples in this chapter, we suggest you use the interactive query tool provided by your database management system. To run the sample SQL statements presented in this section, you'll need to create some database tables and populate them with data. The file SAMPLEDB.ZIP, available for download from the Web site for this book, contains both a sample Access database and the scripts necessary to create the sample tables in a SQL server database. There are many different ways you can execute the sample queries. In this section we will review three of them: SQL Query Analyzer, the interactive query tool shipped with Microsoft SQL Server. Microsoft Access' SQL Query View Visual Studio .NET's Database Project
All the previous tools allow you to type a SQL statement on the keyboard, click a button, and view the results in a window. This is very helpful for those who are learning SQL. Visual Studio .NET even includes a graphical view of your queries, which is updated when you change your SQL statement. Note The examples in this section are compatible with Microsoft SQL Server 2000 and Microsoft Access. (Access is based on the Jet database engine, so there may be some small differences in the SQL syntax as noted in the chapter.) If you use a different database system, the supported features and syntax of some of the statements may vary. Using the SQL Server Query Analyzer SQL Server is Microsoft's enterprise-wide database management system. It generally is installed on a server machine and clients connect to it over a network. The Microsoft Database Engine is a stripped-down version of SQL Server. It does not ship with any client tools, but can be accessed using the SQL Server Client tools. MSDE can be used to distribute the SQL database engine with your application. Note The Microsoft Database Engine represents an exciting step forward for Visual Basic developers, because it provides the advanced features of SQL Server at no cost. At the time of this writing, you can download MSDE from Microsoft's Web site for free. (It is also included on the Office 2000 CD and with the .NET framework.) Microsoft's Web site has many articles that explain how to automatically install and configure the database engine with your applications. Note Microsoft's Web site recommends developing your application with SQL Server Personal Edition (included with some editions of Visual Studio) and then using MSDE to distribute it. You can connect to Microsoft SQL Server or the Microsoft Database Engine using the SQL Server Client tools installed on your PC. You will, of course, need a login and password with sufficient access. To enter SQL statements using SQL Server, perform the following steps: -
Find the MicrosoftSQL Server program group in your Start menu. -
Open the SQL Query Analyzer program. -
Enter your server name, user name, and password. -
A screen should appear that looks similar to Figure 20.9. Figure 20.9. The SQL Query Analyzer provides a way to interactively execute SQL statements. Note If you receive an error message attempting to connect to SQL Server, verify that you have entered the password correctly. If you are still having difficulties, read the troubleshooting section at the end of this chapter. -
Using the database drop-down box, select the correct database if it is not already selected. -
To execute a SQL statement, just type it in the query window and click the Execute button (or press the Alt+X shortcut keys). The results will be displayed in the results window. -
You can enter multiple SQL statements in the query window and highlight the one you want to execute with the mouse. If you do not highlight anything, SQL Server will attempt to execute all statements present in the query window.
Executing SQL Queries in Access Microsoft Access was not designed to be an enterprise-wide database management system like SQL Server, but it is still a very widely used database and quite capable of executing SQL queries. To use Microsoft Access with the samples in this chapter, follow these steps: -
Start Access and open the MDB file included with this chapter. -
From the database window, select Queries. -
Double-click Create query in design view. -
By default, Microsoft Access will display the graphical design view for the new query, which includes the Show Table dialog box. Click Close to hide the Show Table dialog box. -
From the View button on the toolbar, select SQL view. The query window will change to a blank white box where you can enter SQL statements. -
After entering your query, click the Run button on the toolbar, which appears as an exclamation mark. -
After your query is executed, you will need to use the View button again to return to the SQL statement.
Note You also can link to a SQL Server database using Access. However, in the author's opinion, the interface provided by Visual Studio .NET is superior. Figure 20.10 shows a SQL statement entered using Microsoft Access. Figure 20.10. To enter SQL in Microsoft Access, you need to switch to the SQL view using the toolbar or View menu. Using Visual Studio .NET Visual Studio .NET includes a new project type called Database Project, which is similar to the Database Tools program included with earlier versions. It allows you to manage your database queries and scripts within the Solution Explorer. You'll find the Database Project option under Other Projects in the New Project dialog box, shown in Figure 20.11. Figure 20.11. A Database Project allows you to create and test database queries from within the Visual Studio environment. Follow the steps provided in the next few sections to learn how to use Visual Studio .NET to execute SQL queries. Connecting to Your Database To simplify the process of maintaining database connections, Visual Studio .NET introduces the concept of a Database Reference. A database reference is just connection information for a particular database that can be stored with a Visual Studio project. If you have not yet created any Database References in Visual Studio, the dialog box, shown in Figure 20.12, will automatically be displayed. Figure 20.12. The Data Link window is where you specify the server name, database type, user name, and password. Note If you have already created previous database connections in Visual Studio, another dialog box will appear. This dialog box allows you to reuse one of the previous database connections or add a new one using the screen shown in Figure 20.12. Notice the four tabs pictured in Figure 20.12. These tabs allow you to specify various types of connection information: Provider Used to specify what type of database (for example, Jet or SQL Server). SQL Server is the default setting. Connection This is the most important part of the Data Link dialog box. Here you specify the password and user name to gain access to your database. Advanced This area controls advanced connection settings, such as network timeouts. All Lists all the configuration settings in a tabular format.
By default, the Connection tab is activated. Under normal circumstances, this screen is all you will need to fill out in order to connect to a SQL Server or MSDE database. To test your connection, perform the following steps: -
Enter the name of the SQL server containing the sample database. -
Enter your user name and password. -
Select the database name where you created the sample database. -
Click the Test Connection button.
If everything goes smoothly, you will see a message indicating the connection test was successful. If you receive an error message, skip ahead to the troubleshooting section at the end of this chapter and return after resolving your connection problem. Click OK to close the Data Link Properties dialog box. If you get an error message, please see the troubleshooting section at the end of this chapter before continuing. Note If you have problems connecting to a SQL server, please see "Troubleshooting Hints" at the end of this chapter. When you create a database project you may notice it looks slightly different than other Visual Studio projects. The Server Explorer window, pictured in Figure 20.13, allows you to explore the database objects on your server in a tree-like fashion. For example, you can see a list all of the tables in a particular database and view or even edit the data contained within them. Figure 20.13. One of the many features of the Server Explorer window is its ability to view objects in a database. If you will be following along with the sample SQL statements in this chapter, verify that you have Employee and Person in the table list. Adding a New Query to the Project Another difference in the look of the Database Project is the Solution Explorer; instead of forms and classes it contains database-related items, as pictured in Figure 20.14. Figure 20.14. The Solution Explorer can be used to organize database connections and objects together in a Visual Studio project. Right-click the project name in the Solution Explorer and choose Add Query. This will display the Add New Item dialog box, shown in Figure 20.15. Figure 20.15. The process of adding a new item to a Database Project is very similar to adding a form to a Windows Application. Enter a query name and click OK. Like Access, Visual Studio assumes you want to design queries graphically and displays a dialog box to select the tables in your query. However, this step is optional. Close the dialog box and you will see the Query Designer appear, as shown in Figure 20.16. The Query Designer is divided into several sections, or panes, each of which can be hidden or shown with the toolbar buttons.The following steps can be used to execute a SQL query: Figure 20.16. Each pane in the Query Designer can be shown or hidden as desired; modifications to any of the design panes affect the underlying query. -
Type your query into the SQL pane. -
(Optional) Click the Verify SQL Syntax button to make sure the statement does not contain errors. -
Click the Run button. The results of the query are displayed in the results pane.
|