Relational databases such as SQL Server and Oracle rely on stored procedures to do repetitive tasks, so a programmer's main jobs when creating database applications are creating, debugging, and testing stored procedures (often called stored procs). Another database object it behooves programmers to learn is the view—or virtual table. In this chapter, you'll learn how to take advantage of Visual Studio .NET (VS .NET) tools to work with both stored procedures and views.
User-defined stored procedures are handy for a variety of reasons. User-defined stored procs, as opposed to the built-in ones called system stored procedures, distribute load between a client and a database server because they reside on a database server but are executed by a client's call. Stored procedures not only provide modularity in an application design, they also provide better control, management, load distribution, and performance. In the following sections, we discuss how to create and execute stored procedures using VS .NET.
You have a variety of ways to create stored procedures. As a database administrator, you can use the database server's administrative tools (SQL Server Enterprise Manager or Oracle Enterprise Manager, for example) to create and manage stored procedures. As a programmer, you can create stored procedures programmatically by using the CREATE PROCEDURE SQL statement. In this chapter, you'll learn how to create and manage stored procedures using VS .NET.
VS .NET's Server Explorer, discussed in Chapter 2, enables you to create, update, and delete You can launch the Server Explorer by selecting View Server Explorer. After opening it, you'll see the available database servers in the list. If you expand a server tree node, you'll find the available databases and database objects on the server. You can use the database's Stored Procedures node to managed stored procedures. As you can see from Figure 11-1, the Stored Procedures node lists all the available stored procedures in the Northwind database. You can view stored procedures by double-clicking the stored procedure's name.
Figure 11-1: Viewing available stored procedures in the Northwind database
The right-click menu option allows you to create a new stored procedure and edit, delete, and execute existing stored procedures (see Figure 11-2).
Figure 11-2: Create, edit, delete, and run stored procedure options
The Edit Stored Procedure option lets you edit a stored procedure. To edit an existing stored procedure of the Northwind database, right-click the CustOrderHist stored procedure, and select the Edit Stored Procedure option. The stored procedure editor opens (see Figure 11-3).
Figure 11-3: Stored procedure editor
In SQL Server, the login that creates or has the ownership of a database or the administrator of the server is known as the database owner. In the database and its objects, the username dbo is always associated with the login name of the database owner.
Now that you know how to edit an existing stored procedure, let's create a one. You can create a stored procedure by right-clicking the Stored Procedures node and selecting the New Stored Procedure option. This launches the stored procedure editor, which you use to write stored procedures. In SQL Server, dbo stands for the database owner. In Figure 11-4, the syntax CREATE PROCEDURE dbo.StoredProcedure2 creates a stored procedure where StoredProcedure1 is the name of the stored procedure and dbo is the owner of the stored procedure.
Figure 11-4: Creating a stored procedure
A stored procedure can return data as a result of SELECT statement, a return code (an integer value), or an output parameter. As you can see from Figure 11-4, the section after CREATE PROCEDURE is the parameters section, which is closed with comments (/* and */). If you have no requirement for using parameters in a stored procedure, you can skip this area. The section after AS is used for the actual SQL statement, and the section after RETURN is used for returning a value when you execute a stored procedure.
Now, you can write a simple SELECT statement and save it as a stored procedure. Use this SQL statement to select three columns values for customers from the United States:
SELECT CustomerId, CompanyName, ContactName FROM Customers WHERE Country ='USA'
You can change the stored procedure name to whatever you want; this example uses mySP (my stored procedure). The final stored procedure looks like Figure 11-5.
Figure 11-5: The mySP stored procedure in the editor
You can save a stored procedure by using File Save mySP or the Save All menu option or toolbar button. The Save option is also available after right-clicking on the stored procedure editor.
The Save option not only creates a stored procedure, but it also changes the CREATE PROCEDURE statement to ALTER PROCEDURE because the stored procedure has already been created.
Now, if you go to the Server Explorer and look at all the stored procedures for the Northwind database, your stored procedure should be listed. As you can see from Figure 11-6, the stored procedure mySP has three fields listed under it that appear in the SELECT statement.
Figure 11-6: The mySP stored procedure listed for the Northwind database
Executing a stored procedure in the Server Explorer is simple. You run (or execute) a stored procedure by right-clicking on the stored procedure editor and selecting Run Stored Procedure.
When you run the stored procedure mySP, the output looks like Figure 11-7.
Figure 11-7: The output of the mySP stored procedure in VS .NET
Like your SELECT SQL statement, you can use almost any SQL statement, such as UPDATE and DELETE, in the stored procedure. (Exceptions include CREATE TRIGGER, CREATE VIEW, CREATE DEFAULT, CREATE PROCEDURE, and CREATE RULE.)
A stored procedure can also accept input parameters. For example, the CustOrdersDetail stored procedure takes a parameter value of OrderID. Based on the OrderID value, it returns ProductName and calculates Discount and ExtendedPrice (see Figure 11-8).
Figure 11-8: Stored procedure with input parameter
When you run the stored procedure, it asks you for the value of the OrderID parameter, as shown in Figure 11-9.
Figure 11-9: Registering a stored procedure parameter
The output of the stored procedure CustOrdersDetail looks like Figure 11-10.
Figure 11-10: The output of stored procedure CustOrdersDetail
As an application developer, most of the time you'll be creating and testing stored procedures programmatically. You can execute a stored procedure programmatically using the Command object. Instead of passing a SQL statement, you pass the stored procedure name as the SQL statement to execute a stored procedure. As you saw in Chapter 4, each data provider provides a Command object to execute SQL statements. The Command classes for the OleDb, ODBC, and Sql data providers are OleDbCommand, OdbcCommand, and SqlCommand (respectively). Listing 11-1 shows how to use SqlCommand to execute a stored procedure programmatically against a SQL Server database.
Listing 11-1: Executing the mySP Stored Procedure Using the Sql Data Provider
Sub Main() ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;" & _ "Data Source=localhost;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection conn.Open() ' Create a SqlCommand object and pass mySP as the ' SQL command and set CommandType property to ' CommandType.StoredProcedure Dim cmd As SqlCommand = New SqlCommand("mySP", conn) cmd.CommandType = CommandType.StoredProcedure ' Call ExecuteReader of SqlCommand Dim reader As SqlDataReader = cmd.ExecuteReader() ' Read all data and display on the console While reader.Read() Console.Write(reader(0).ToString()) Console.Write(reader(1).ToString()) Console.WriteLine(reader(2).ToString()) End While Console.Read() ' Close reader and connection reader.Close() conn.Close() End Sub
There are two steps involved in executing a stored procedure from your program. First, you set the Command object property CommandText to the stored procedure name; second, you set the CommandType property to CommandType.StoredProcedure. Listing 11-1 executes the mySP stored procedure you created in the previous section. To test code in Listing 11-1, create a console application and type Listing 11-1 on the Main method. Don't forget to import the following two namespaces to the project before using Sql data provider classes:
Imports System.Data Imports System.Data.SqlClient
Listing 11-1, creates a SqlCommand object by passing the stored procedure as the first parameter of the SqlCommand constructor and then sets the CommandType property CommandType.StoredProcedure. The result of Listing 11-1 looks like Figure 11-11.
Figure 11-11: The output of stored procedure mySP
A stored procedure can also accept input, output, or both types of parameters. Next, let's modify the mySP stored procedure a little bit. This time give the user an option to select the customers based on their country. The modified stored procedure looks like Figure 11-12.
Figure 11-12: A stored procedure with parameters
As you can see from Figure 11-12, the stored procedure selects customers based on the country entered by the user. You can use the SqlParameter class to create a parameter. The SqlParameter class has properties such as Direction and Value. The Direction property defines whether the stored procedure is an input, output, or both. It also defines whether it has a return value. The ParameterDirection enumeration defines values of Direction (see Table 11-1).
Both input and output parameter
Returns a value from the stored procedure
The Value property sets the value of the parameter. The following code adds a parameter with the value UK. After you execute the mySP stored procedure, it will return customers from the United Kingdom only:
'Set the SqlParameter Dim param As SqlParameter = New SqlParameter() param = cmd.Parameters.Add("@country", SqlDbType.VarChar, 50) param.Direction = ParameterDirection.Input param.Value = "UK"
The updated source code looks like Listing 11-2, and the output of Listing 11-2 looks like Figure 11-13. Listing 11-2 creates SqlParameter as the country and sets its value to UK. ExecuteReader only returns rows where Country = "UK".
Figure 11-13: Output of Listing 11-2
Listing 11-2: Using Parameters in a Stored Procedure
' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;" & _ "Data Source=localhost;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection conn.Open() ' Create a SqlCommand object and pass mySP as the ' SQL command and set CommandType property to ' CommandType.StoredProcedure Dim cmd As SqlCommand = New SqlCommand("mySP", conn) cmd.CommandType = CommandType.StoredProcedure 'Set the SqlParameter Dim param As SqlParameter = New SqlParameter() param = cmd.Parameters.Add("@country", SqlDbType.VarChar, 50) param.Direction = ParameterDirection.Input param.Value = "UK" ' Call ExecuteReader of SqlCommand Dim reader As SqlDataReader = cmd.ExecuteReader() ' Read all data and display on the console While reader.Read() Console.Write(reader(0).ToString()) Console.Write(reader(1).ToString()) Console.WriteLine(reader(2).ToString()) End While Console.Read() ' Close reader and connection reader.Close() conn.Close()
To return a value from a stored procedure, the only thing you need to do is change the stored procedure, which will store and return a value as a parameter and set the parameter's Direction property as follows:
' Create a SqlParameter Dim param As SqlParameter = new SqlParameter() param.Direction = ParameterDirection.ReturnValue
Also, store the command's execution results in a number variable like this:
param = cmd.Parameters.Add("@counter", SqlDbType.Int)
See Listing 11-3 for the complete source code.
Listing 11-3: AddCat1 Stored Procedure
CREATE PROCEDURE AddCat1 @CategoryName nchar(15), @Description char(16), @Identity int OUT AS INSERT INTO Categories (CategoryName, Description) VALUES(@CategoryName, @Description) SET @Identity = @@Identity RETURN @@ROWCOUNT
Now you'll see an example of using ParameterDirection.Output. To test this source code, create a console application and these namespace references:
Imports System.Data Imports System.Data.Common Imports System.Data.SqlClient
Next, create a stored procedure called AddCat1 that adds a row to the Categories table and returns the row count. Listing 11-3 defines the stored procedure.
Listing 11-4 shows how to use output parameters. Everything is similar to the previous samples except that you need to use the parameter direction Parameter-Direction.Output.
Listing 11-4: Executing a Stored Procedure with Output Parameter
' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;" & _ "Data Source=localhost;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection conn.Open() Dim sql As String = _ "SELECT CategoryID, CategoryName, Description FROM Categories" Dim da As SqlDataAdapter = New SqlDataAdapter(sql, conn) da.InsertCommand = New SqlCommand("AddCat1", conn) da.InsertCommand.CommandType = CommandType.StoredProcedure ' Create a SqlParameter Dim param As SqlParameter = _ da.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int) param.Direction = ParameterDirection.ReturnValue da.InsertCommand.Parameters.Add _ ("@CategoryName", SqlDbType.NChar, 15, "CategoryName") da.InsertCommand.Parameters.Add _ ("@Description", SqlDbType.Char, 16, "Description") param = da.InsertCommand.Parameters.Add _ ("@Identity", SqlDbType.Int, 0, "CategoryID") param.Direction = ParameterDirection.Output ' Creat a DataSet and fill it Dim ds As DataSet = New DataSet() da.Fill(ds, "Categories") ' Creat a DataRow and add it to the DataSet's DataTable Dim row As DataRow = ds.Tables("Categories").NewRow() row("CategoryName") = "Beverages" row("Description") = "Chai" ds.Tables("Categories").Rows.Add(row) ' Save changes to the database da.Update(ds, "Categories") ' Output Console.WriteLine(da.InsertCommand.Parameters _ ("@RowCount").Value.ToString())