No programming language or tool would be considered complete without support for stored procedures, and of course you will find excellent support for stored procedures in the .NET Framework. As you probably know, your database provider will need to support stored procedures, and you will need to know how to write them (or know someone who does). All we have to do is transfer the knowledge from the old way to the new way.
Since stored procedures are generally an advanced topic for many developers, I will review the basic idea here. A stored procedure is analogous to a function. The difference between .NET stored procedures and functions is that stored procedures are written in a version of SQL and stored in the database and .NET functions are written in VB .NET and stored in a module. A general benefit of stored procedures is that you can treat one or more SQL operations as a single, homogeneous operation. An additional benefit is that the stored procedure is on the database, and in production systems this is generally some of your big iron. (By "big iron" I mean more computing power.) Real performance differences will vary, but using stored procedures does allow you to centralize SQL. Using stored procedures also provides for a nice division of labor, allowing database people to focus on writing SQL and VB .NET people to focus on writing Visual Basic code.
SQL Server, Oracle, and UDB all seem to have flavors of stored procedure languages. Microsoft calls its SQL Server stored procedure language T-SQL; Oracle calls its stored procedure language PL-SQL. I am not sure if UDB has a name for its stored procedure language, but until recently it was the C programming language.
Writing stored procedures varies by database provider, but from the VB .NET perspective the process is roughly the same. We need a procedure, a connection, an adapter, and a command object. Command objects serve double-duty, permitting you to pass raw SQL or the name of a stored procedure and parameters. Let's take a look at how we invoke a stored procedure with input and output parameters, as well as how to receive an open cursor (a result set).
Invoking a Stored Procedure
The direct way to invoke a stored procedure is to create a connection and a command and then initialize an adapter with the command. The adapter is used to fill a DataSet , and the command object contains the information that helps invoke the stored procedure. These basic steps are the same for OleDb or SqlClient providers; you will need to create and add parameters only if your stored procedure needs them.
The example code in StoredProcedureDemo.sln contains these basic steps with a twist. I wrote the code as one monolithic procedure so you could see a single linear progression of steps, but I used interfaces. ADO.NET is supported by common interfaces that the various provider classes must implement. For example, instead of declaring an OleDbCommand instance I can declare an IDbCommand instance. With this approach I can easily switch between OleDbCommand and SqlCommand , depending on the environment. Interface programming in general is a powerful strategy, and in this case, it supports switching between working disconnected on my laptop and back to the network development environment without changing my basic code base. (I will talk more about this in the Programming with ADO.NET Interfaces section later in this chapter.) Listing 12.2 contains a simple stored procedure call written using interface declarations.
Listing 12.2 Invoking a Stored Procedure without Parameters
1: Imports System.Data.SqlClient 2: Imports System.Data.OleDb 3: Imports System.Configuration 4: 5: Public Class Form1 6: Inherits System.Windows.Forms.Form 7: 8: [ Windows Form Designer generated code ] 9: Private Sub Form1_Load(ByVal sender As Object, _ 10: ByVal e As System.EventArgs) Handles MyBase.Load 11: 12: Dim ConnectionString As String = _ 13: ConfigurationSettings.AppSettings("ConnectionString1") 14: 15: Dim Connection As IDbConnection = _ 16: New OleDbConnection(ConnectionString) 17: 18: Dim Command As IDbCommand = _ 19: New OleDbCommand() 20: 21: Command.CommandType = CommandType.StoredProcedure 22: Command.Connection = Connection 23: Command.CommandText = "[Catalog]" 24: 25: Dim Adapter As IDataAdapter = _ 26: New OleDbDataAdapter(Command) 27: 28: Dim DataSet As DataSet = New DataSet(Command.CommandText) 29: Adapter.Fill(DataSet) 30: 31: DataGrid1.DataSource = DataSet 32: End Sub 33: End Class
Line 15 declares an IDbConnection object, and line 16 creates an instance of OleDbConnection . By implication OleDbConnection must implement the IDbConnection interface. ( SqlConnection does too.)
Line 18 declares an IDbCommand object and line 19 creates an instance of OleDbCommand . The command is central to stored procedure invocation. The CommandType property is CommandType.Text by default. When calling a stored procedure, change the CommandType property to CommandType.StoredProcedure (line 21). The command is assigned a reference to the connection object, and the stored procedure name is set as the value of CommandText . (The provider used in the example is Microsoft Access, which seems to prefer the square brackets, as in [Catalog] . Microsoft Access began supporting stored procedures in ADO 2.5 or 2.6.)
To wrap up we initialize the adapter with the command object and use the adapter to fill a DataSet object.
If a particular stored procedure does not return a cursor (think "result set"), you can call a command object's ExecuteNonQuery method. If you need only the first column of the first rowas you might need for an insert where a key is generated in the databaseyou can invoke ExecuteScalar .
You will want to perform several other tasks as well. During the course of a project you will likely need to pass parameters to a stored procedure, for example, when you insert a row. You may also need to get individual columns of data back from the stored procedure (in addition to getting a cursor). The next subsections demonstrate how to define SQL Server connection strings and how to use input and output parameters. (The examples use both the Microsoft Access and Microsoft SQL Server versions of the Northwind database.)
Defining an SQL Server Connection String
Because I am introducing a Microsoft SQL Server provider at this point, I have included this brief interlude to show you an example of an SQL Server connection string. I have used the same technique to store the SQL Server connection string in the App.config file. (The connection string shown in Listing 12.3 appears in the App.config file for StoredProcedureDemo.sln .)
Listing 12.3 contains a sample connection string for an SQL Server instance. You can copy these values from the Properties window (Figure 12.1). Select the database instance from the Server Explorer in Visual Studio .NET and press F4 to open the Properties window for that database instance.
Listing 12.3 A Sample SQL Server Connection String
<add key="ConnectionString" value="data source=SCI\EREWHON;initial catalog=Northwind;in- tegrated security=SSPI;persist security info=True;workstation id=PTK800;packet size=4096" />
Figure 12.1. The Properties window for a database instance selected in the Server Explorer.
If you define the connection string from scratch, you will need to substitute information like workstation id with your workstation's name (instead of mine, used in Listing 12.3). As previously mentioned, it is easiest to add a connection to the Server Explorer and copy the connection string from the Properties window.
Using Input Parameters
When you perform most practical queries, you will be selectively returning a subset of data, performing filters, or ordering data. To get the parameters to the stored procedure, use the Parameters collection of a command object.
Parameters are provider-specific. For OleDb providers create instances of the OleDbParameter class, and for SqlClient providers create instances of the SqlParameter class. You also have the option of declaring parameters as IDataParameter types and instantiating either OleDb or SqlClient parameters. Listing 12.4 contains the CustOrderHist stored procedure, and Listing 12.5 shows the SqlClient code that invokes this stored procedure. CustomerID is a required input parameter.
Listing 12.4 A Stored Procedure Written in T-SQL
ALTER PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName
If you are unfamiliar with SQL and stored procedures, you will need to get a good reference on SQL for whatever vendor you are using. (The flavors vary a bit but are based to a greater or lesser degree on ANSI SQL.) The procedure is a function that accepts a five-character string representing CustomerID and returns the names of products and quantities purchased by the customer. The detail information is assembled from the Customers , Products , and Orders Details tables. Here is Listing 12.5, demonstrating how to invoke the stored procedure defined in Listing 12.4.
Listing 12.5 Passing an Input Parameter to a Stored Procedure
1: Private Sub InputParameterProcedure() 2: Dim ConnectionString As String = _ 3: ConfigurationSettings.AppSettings("ConnectionString") 4: 5: Dim Connection As IDbConnection = _ 6: New SqlConnection(ConnectionString) 7: 8: Dim Command As IDbCommand = _ 9: New SqlCommand() 10: 11: Command.CommandType = CommandType.StoredProcedure 12: Command.Connection = Connection 13: Command.CommandText = "CustOrderHist" 14: 15: Dim Parameter As IDataParameter = _ 16: New SqlParameter() 17: 'New SqlParameter("@CustomerID", SqlDbType.NChar, 5) 18: 19: Parameter.Direction = ParameterDirection.Input 20: Parameter.ParameterName = "@CustomerID" 21: Parameter.DbType = DbType.String 22: Parameter.Value = "ALFKI" 23: Command.Parameters.Add(Parameter) 24: 25: Dim Adapter As IDataAdapter = _ 26: New SqlDataAdapter(Command) 27: 28: Dim DataSet As DataSet = New DataSet(Command.CommandText) 29: Adapter.Fill(DataSet) 30: 31: DataGrid2.DataSource = DataSet 32: End Sub
Listing 12.5 is an excerpt from StoredProcedureDemo.sln . The example invokes a stored procedure against the SQL Server instance of the Northwind database. Much of the code is similar to that in Listing 12.2. Let's focus on the parameter aspect of this code.
The CustOrderHist stored procedure requires a single parameter, CustomerID . This parameter is a five-character string used to join the Customers , Orders , and Products tables and return the order history for that customer (as depicted in Listing 12.4). The single parameter is defined in lines 15 through 23 in Listing 12.5. Lines 15 through 17 demonstrate that the constructor is overloaded for the SqlParameter class. You can call the default constructor and initialize the parameter values as properties or call one of the overloaded constructors and pass in all the parameters when you construct the SqlParameter instance.
The constructor that takes three arguments is commented out (line 17). I used the default constructor and assigned Direction (line 19), ParameterName (line 20), DbType (line 21), and Value (line 22). The parameter is added to the command's Parameters collection in line 23. If more than one parameter is required, you simply repeat this process.
There are a couple of further points of interest here. Using the @ prefix, as in @CustomerID , is a required convention in SQL Server (and UDB); unfortunately , the @ prefix is a required factoid with ambiguous origins. A second piece of information is the use of the DbType property. Different database providers use different type enumerations. If you are using the IDataParameter interface, you need to find a type that closely matches one of the DbType enumerated values. SqlClient providers use SqlType enumerated values, and OleDb providers use OleDbType enumerated values. To a degree they are interchangeable. Check the help documentation for specific data types supported by the enumerations.
Based on the code written in Listing 12.4, when the code in Listing 12.5 runs, the order history for customer ALFKI (Alfreds Futterkiste) will be returned by the store procedure.
Using Output Parameters
Output parameters are initialized pretty much the same way that input parameters are (see Listing 12.5). The biggest difference for output parameters is that you have to express ParameterDirection as an output parameter and retrieve the value after the stored procedure has run.
An output parameter is commonly needed when a column value is generated at the time a row is inserted. For example, if CustomerID were a generated field, its value would not be known until a row was inserted into the database. If you need to hold onto an inserted record, you will need to get the identifier back after the insert. You can use output parameters to get the necessary data.
To demonstrate I borrowed a table from the Web database implemented with SQL Server at http://www.softconcepts.com. For earlier examples I used sample databases I thought you might have or could access. For this example I used a table you could easily recreate. Toward this end I included script to generate the table, the custom stored procedure, and the VB. NET code to insert a row and return an output parameter.
Defining a Table in SQL Server
There are several database vendors to choose from. I have worked on projects with Oracle, SQL Server, UDB, Informix, and a host of other enterprise servers and desktop databases. I chose SQL Server for this example because it was handy when writing this chapter. SQL Server integrates nicely with Visual Studio .NET, and it shouldn't be too difficult for you to get a copy of Microsoft Data Engine (MSDE). (There should be a copy of MSDE on your Visual Studio .NET discs, if you purchased the enterprise edition, and a copy is included in an MSDN subscription.)
You can create a new database in the Server Explorer if you have a registered SQL Server instance. When you create the new database, you can right-click on the Tables node for that database, select New Table, and define a new table right in the Visual Studio .NET IDE.
As an alternative, you can open an instance of the SQL Query Analyzerwhich installs with SQL Serverand run the script in Listing 12.6 to create the table. To create the table, follow these steps.
When you perform the last step, the Books table will be created in the selected database. You can use the same database and the Books table to experiment with the exercises in the remaining two subsections.
Listing 12.6 The Script for Generating the Books Table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Books] GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[Books] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Author] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Title] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ISBN] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [URL] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PublishedDate] [datetime] NULL ) ON [PRIMARY] END GO
Implementing a Stored Procedure
You can continue to work in the SQL Query Analyzer or create the stored procedure in Visual Studio .NET. I implemented the stored procedure shown in Listing 12.7 in Visual Studio .NET by selecting the database containing the Books table from the Server Explorer and selecting New Stored Procedure from the Stored Procedure node for that database instance. Listing 12.7, written in T-SQL, inserts a row into the Books table and returns the identity value.
Listing 12.7 A Stored Procedure for Inserting a Row into the Books Table
ALTER PROCEDURE AddBook ( @Author char(25), @Title char(50), @ISBN char(50), @URL char(128), @Description char(128), @PublishedDate DateTime, @ID int OUTPUT ) AS INSERT INTO Books ( Author, Title, ISBN, URL, Description, PublishedDate ) VALUES ( @Author, @Title, @ISBN, @URL, @Description, @PublishedDate ) SELECT @ID = @@IDENTITY
In Listing 12.7 the ALTER PROCEDURE statement creates the stored procedure. The procedure accepts seven parameters. Six parameters are input parameters; the seventh is an output parameter. From the script in Listing 12.6 we can determine that the ID column is an identity column. (This is an autogenerated column in SQL Server.) When we insert the row, the identity column is generated, and the client will need that information for future operations. (In our example, we simply display the ID column value to the user .)
The INSERT INTO statement inserts all our input parameter columns. Because the ID column is generated, we don't need to insert this value. Finally we invoke a SELECT @ID = @@IDENTITY command to read the identity for the row we just inserted into the ID parameter. The client application can read this value from the object representing the ID parameter.
In Listing 12.7, the first statement set in bold font demonstrates how to declare an output parameter, and the second bold statement demonstrates how to select the generated identity for this row into the output parameter.
Obtaining an Output Parameter from a Stored Procedure
You can experiment with output parameters with any stored procedure that defines an output parameter. We'll continue with the Books table and the AddBook stored procedure defined in the preceding subsections. I included the code to run the stored procedure in a simple input form (Figure 12.3) to demonstrate one way to tie all the elements of a database application together. Listing 12.8 (available in OutputPutParameterDemo.sln ) accepts input values and runs the AddBook stored procedure. The only change we might make in a production application is to separate the database code into a separate class, so it isn't so closely tied to a single form.
Listing 12.8 A Windows Form Application for Running the AddBook Procedure
1: Imports System.Data.SqlClient 2: Imports System.Configuration 3: 4: Public Class Form1 5: Inherits System.Windows.Forms.Form 6: 7: [ Windows Form Designer generated code ] 8: 9: Private ReadOnly Property Author() As String 10: Get 11: Return TextBoxAuthor.Text 12: End Get 13: End Property 14: 15: Private ReadOnly Property Title() As String 16: Get 17: Return TextBoxTitle.Text 18: End Get 19: End Property 20: 21: Private ReadOnly Property ISBN() As String 22: Get 23: Return TextBoxISBN.Text 24: End Get 25: End Property 26: 27: Private ReadOnly Property URL() As String 28: Get 29: Return TextBoxURL.Text 30: End Get 31: End Property 32: 33: Private ReadOnly Property Description() As String 34: Get 35: Return TextBoxDescription.Text 36: End Get 37: End Property 38: 39: Private ReadOnly Property PublishedDate() As DateTime 40: Get 41: Return DateTimePickerPublishedDate.Value 42: End Get 43: End Property 44: 45: Private Sub Button1_Click(ByVal sender As System.Object, _ 46: ByVal e As System.EventArgs) Handles Button1.Click 47: 48: AddBook(Author, Title, ISBN, URL, _ 49: Description, PublishedDate) 50: 51: End Sub 52: 53: Private ReadOnly Property ConnectionString() As String 54: Get 55: Return ConfigurationSettings.AppSettings( _ 56: "ConnectionString") 57: End Get 58: End Property 59: 60: 61: Private Sub AddBook(ByVal Author As String, _ 62: ByVal Title As String, ByVal ISBN As String, _ 63: ByVal URL As String, ByVal Description As String, _ 64: ByVal PublishedDate As DateTime) 65: 66: Dim Connection As SqlConnection = _ 67: New SqlConnection(ConnectionString) 68: 69: Dim Command As SqlCommand = _ 70: New SqlCommand() 71: 72: Command.Connection = Connection 73: Command.CommandType = CommandType.StoredProcedure 74: Command.CommandText = "AddBook" 75: 76: 77: Dim parameter As SqlParameter = _ 78: New SqlParameter("@Author", SqlDbType.Char, 25) 79: parameter.Direction = ParameterDirection.Input 80: parameter.Value = Author 81: Command.Parameters.Add(parameter) 82: 83: parameter = New SqlParameter("@Title", SqlDbType.Char, 50) 84: parameter.Direction = ParameterDirection.Input 85: parameter.Value = Title 86: Command.Parameters.Add(parameter) 87: 88: parameter = _ 89: New SqlParameter("@ISBN", SqlDbType.Char, 50) 90: parameter.Direction = ParameterDirection.Input 91: parameter.Value = ISBN 92: Command.Parameters.Add(parameter) 93: 94: parameter = _ 95: New SqlParameter("@URL", SqlDbType.Char, 128) 96: parameter.Direction = ParameterDirection.Input 97: parameter.Value = URL 98: Command.Parameters.Add(parameter) 99: 100: parameter = _ 101: New SqlParameter("@Description", SqlDbType.Char, 128) 102: parameter.Direction = ParameterDirection.Input 103: parameter.Value = Description 104: Command.Parameters.Add(parameter) 105: 106: parameter = _ 107: New SqlParameter("@PublishedDate", SqlDbType.DateTime, 8) 108: parameter.Direction = ParameterDirection.Input 109: parameter.Value = PublishedDate 110: Command.Parameters.Add(parameter) 111: 112: parameter = _ 113: New SqlParameter("@ID", SqlDbType.Int, 4) 114: parameter.Direction = ParameterDirection.Output 115: Command.Parameters.Add(parameter) 116: 117: Connection.Open() 118: Try 119: Command.ExecuteNonQuery() 120: UpdateStatusBar("Added Book ID:" + _ 121: parameter.Value.ToString()) 122: Catch e As Exception 123: MessageBox.Show(e.Message, "Failed Insert", _ 124: MessageBoxButtons.OK, MessageBoxIcon.Error) 125: Finally 126: Connection.Close() 127: End Try 128: 129: 130: End Sub 131: 132: Private Sub UpdateStatusBar(ByVal Message As String) 133: StatusBar1.Text = Message 134: End Sub 135: 136: End Class
Figure 12.3. A simple input Windows Form application that culminates in running the AddBook stored procedure.
The first 58 lines include property statements to read values for the form's controls. I prefer this approach since it permits me to change the underlying control, add validation, and use named properties disrupting the rest of the form's code. For example, if the PublishedDate property starts as a TextBox control and then validates input and returns a DateTime value, I need to write this code only in the property getter (lines 39 through 43). Then, if I elect to reimplement the date and time using a DateTimePicker control, I can do so without disrupting any code that is dependent on the value since that code will be relying on the property and not the literal control.
Lines 61 through 130 demonstrate code that invokes the AddBook stored procedure. This code is very similar to the code in Listing 12.5; there are just many more parameters in Listing 12.8. The significant difference is that Listing 12.8 has to manage the output parameter. There are a couple of ways to do this. One way is to declare the output parameter as a separate parameter instance and read the value out of that instance. I simulated this approach in line 121. Although I reused the parameter variable for every parameter, creating a new parameter and assigning it to this variable, I assigned the output parameter last. In line 121 I simply read the value of the parameter object. A second alternative is to read the value from the Command.Parameters collection. In our code this could be accomplished with the statement Command.Parameters("@ID").Value .
Finally, because the INSERT INTO statement does not return a cursor, the stored procedure is invoked with the ExecuteNonQuery method in line 119. The stored procedure is run between the statements that open and close the connection, which are protected by a Try . . . Finally block (lines 118 through 127). Try . . . Finally blocks march to this rhythm: create resource, try, use resource, and finally clean up the resource. Because opening the connection may fail, I want the connection variable to be defined outside of the Try block that ensures an open connection is closed. If I had resolved what to do in the event that the connection could not open, I could wrap that part of the code in an outer Try . . . Catch block. In the example, if the connection cannot be opened, the default behavior of raising the error is satisfactory.
One great benefit of stored procedures is that multiple operations can be treated as a single, homogeneous operation, reducing network traffic and taking advantage of powerful database servers. This homogeneity is demonstrated in Listing 12.7: Both the INSERT INTO and SELECT IDENTITY queries are run as one transaction to the database.