| T-SQL statements are used in order to manipulate the data present within a SQL Server database. These commands may be passed in an ad-hoc manner to the server or compiled as stored procedures on the server itself. The advantage of using stored procedures is that they are faster than ad-hoc queries, which must first be translated into machine language and then executed. The disadvantage of stored procedures is that they are not as flexible as ad-hoc queries, which may be created within your application at runtime. Ad-hoc Queries Microsoft provides several methods for executing ad-hoc queries, including the following: -
Visual Studio .NET IDE Using the Server Explorer, you can create a new view within the Views folder of a Data Connection object. When you run this query, the results will be displayed. -
Osql This command-line utility ships with SQL Server. -
SQL Query Analyzer A full-featured GUI utility provided with SQL Server that includes templates for common queries, integrated performance analysis, and the Object Browser, which allows you to see the structure of SQL Server objects. -
.NET application You may use a .NET application in order to submit an ad-hoc query and then display the returned result set. You can create an application that will submit an ad-hoc SQL query and display its returned result set by following these steps: -
Open a Visual Basic .NET Windows Application project with a form and expand the Server Explorer. -
Expand the Data Connections node to show the SQL Server connection for the Northwind database. If you don't already have a connection to the Northwind sample database, right-click the Data Connections node and select Add Connection. Supply your server name and authentication information in the Add Connection dialog box and click OK to create the connection. -
Drag the data connection and drop it on your form, creating a new SqlConnection1 object. -
Add a TextBox control (txtQuery), a Button control (btnExecute), and a DataGrid control (dgResults) to your form. Set the Multiline property of the TextBox control to True and set the CaptionVisible property of the DataGrid control to False. Figure 6.1 shows this form. Figure 6.1. An ad-hoc query run within the sample form.  -
Open the form's code module and add the following code: Imports System.Data Imports System.Data.SqlClient Private Sub btnExecute_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnExecute.Click Dim cmd As SqlCommand = SqlConnection1.CreateCommand cmd.CommandType = CommandType.Text cmd.CommandText = txtQuery.Text Dim da As SqlDataAdapter = New SqlDataAdapter() da.SelectCommand = cmd Dim ds As DataSet = New DataSet() da.Fill(ds, "Results") dgResults.DataSource = ds dgResults.DataMember = "Results" End Sub -
Set the form as the startup object for the project and then run the project. -
Enter a query in the text box, such as this: SELECT * from Employees -
Click the button to execute your ad-hoc statement and display the result set using the data grid, as shown in Figure 6.1. Stored Procedures Stored procedures are precompiled SQL queries that are stored on the server, allowing them to run much more quickly than ad-hoc queries that must first be translated into machine code before execution. Stored procedures can make use of input and output parameters, designated using the @ symbol (for example, @TotalSales ), as well as special variables such as the @@IDENTITY variable, which returns the most recently assigned identity column value. You may also make use of parameters in your application code in order to prompt the user for an input value.  | A column with the IDENTITY constraint will be given a unique initial value by SQL Server; however, unless other constraints are placed on the column, this value may later be changed. In order to ensure that a primary key value remains unique, you should use the PRIMARY KEY constraint. |
Stored procedures may be created using any of the same methods used to execute ad-hoc queries, through the use of the CREATE PROCEDURE ( CREATE PROC ) statement, as shown here: CREATE PROCEDURE procGermanCustomers AS SELECT * FROM Customers WHERE Country = 'Germany' After creating the stored procedure, you must save it and then run it in order to cause the database server to compile the SQL statement and store a copy of the compiled stored procedure.  | The ExecuteNonQuery method of the SqlCommand object can be used in order to execute an ad-hoc query or stored procedure that does not return any results to the user. |
You may also specify variable parameters that provide input and output values for use with your stored procedure, as in the following example, which accepts an input parameter ( @CustomerID ) and returns an output parameter value through @TotalSales : 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 After creating this stored procedure using whichever means you prefer, you can create a sample form that uses the procedure by following these steps: -
Open a Visual Basic .NET Windows application project with a form and expand the Server Explorer. -
Expand the Data Connections node to show the SQL Server connection for the Northwind database. -
Drag the data connection and drop it on your form, creating a new SqlConnection1 object. -
Add two Label controls, two TextBox controls (txtCustomerID and txtTotalSales), and a Button control (btnGetTotalSales) on your form. Figure 6.2 shows a layout for this form. Figure 6.2. A form that calls a stored procedure.  -
Open the form's code module and add the following code: Imports System.Data.SqlClient Imports System.Data.SqlTypes Private Sub btnGetTotalSales_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnGetTotalSales.Click Dim cmd As SqlCommand = SqlConnection1.CreateCommand cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "procCustomerSales" cmd.Parameters.Add(New SqlParameter("@CustomerID",SqlDbType.Text,5)) cmd.Parameters("@CustomerID").Value = txtCustomerID.Text cmd.Parameters.Add(New SqlParameter("@TotalSales",SqlDbType.Money)) cmd.Parameters("@TotalSales").Direction = ParameterDirection.Output SqlConnection1.Open() cmd.ExecuteNonQuery() txtTotalSales.Text = String.Format("{0:c}", _ cmd.Parameters("@TotalSales").Value) SqlConnection1.Close() End Sub -
Set the form as the startup object for the project and then run the project. Enter a CustomerID value from the Customers table in the first text box. The form will execute your stored procedure and return the TotalSales value to the second text box.  |
