Because stored procedures are not written in Visual Basic .NET, VB.NET programmers have to learn stored procedure grammar and keywords and practice writing SQL to use them. In a perfect world, the division of labor in software development would be divided between a stored procedure programmer and a Visual Basic .NET programmer so neither would have to know the ins and outs of the other’s work. However, dividing these jobs in a team environment carries the risk of one programmer inadvertently dropping or modifying the other’s precious stored procedure, which can wreak havoc on the team’s code.
This article assumes that you are either an aspiring SQL programmer who dabbles in Visual Basic .NET or a Visual Basic .NET programmer who has to write his own stored procedures for MS SQL Server. It demonstrates some basics of writing SQL stored procedures—SQL is a whole language, so you will need to pick up a couple books, some courses, and hundreds of hours of independent study if you want to become an expert—and shows you how to use those stored procedures from Visual Basic .NET code. The reason for doing this is simple: Isolating SQL code from VB.NET code to clearly create divisions of labor and focus for your solution.
This article uses VS.NET 2003 and the desktop edition of SQL Server (called MSDE), but its code listings should work in most recent editions of SQL and all versions of VS.NET.
A Quick Primer for Writing SQL Server Stored Procedures
The language that supports stored procedures for Microsoft SQL Server is called Transact SQL or T-SQL. Getting into the differences among all of the implementations of ANSI SQL won’t help here. The important thing to know is that almost every implementation deviates from standard SQL, making it a separate language just like Visual Basic .NET is different from QuickBasic, VB6, GW-BASIC, and RomBasic. You have to master only as much SQL in your implementation as you need to get the job done.
The key to writing great SQL is mastering the language. The key to writing functional SQL is pretty straightforward: Basically, write a plain vanilla SQL statement and add a procedure header. That’s all there is to it.
Plain SQL with a procedure header is functional because you can write all of your validation rules, lookups, and constraint logic in Visual Basic .NET. You don’t need SQL to do this. On the flipside, if you write plain vanilla stored procedures, you leave your database wide open for corruption, which is the counter argument for adding constraints in your code and mastering SQL.
Writing a stored procedure
Writing a stored procedure is easy. The IDE will generate a procedure stub for you; all you need to do is provide a name, add desired parameters, and define the T-SQL.
For demonstration, use the Northwind database. If you have almost any version of MS SQL Server installed, you should have a copy of the Northwind database too. (If you don’t have the Northwind database, use the definitions in the INSERT procedure you will write and create a database with a Customers table using the same schema.)
Follow these steps to create a stored procedure:
- Run Visual Studio .NET.
- Create a new VB.NET Console Application project.
- Open the Server Explorer by selecting View|Server Explorer. (You may not have this option on academic or standard versions of VS.NET. If you’d prefer, you can use MS SQL Enterprise Manager, Query Analyzer, the command line tool osql.exe, or work with existing stored procedures, and skip to the next part.)
- In the Server Explorer, expand the server tab, expand the SQL Servers tab, and expand the server instance with which you will be working. (If you don’t see any server instance, right-click the SQL Servers node of the server explorer and select Register SQL Server Instance.) Your Server Explorer window should look something like Figure 1, although your server instance name will be different than the one the figure shows.
- Expand the Northwind node, followed by the Stored Procedures node.
- Right-click on the Stored Procedures node and select New Stored Procedure. (Alternately, you can select the Database|New Stored Procedure menu item in VS.NET.)
Figure 1: The Server Explorer After Step 4, Opening a SQL Server Instance.
Step 6 creates the stored procedure stub you’ll see in Listing 1. (The last digit of the stored procedure name will be incremented by 1 if you save the stored procedure with the default stub name and generate subsequent stored procedures.)
Listing 1: Generating a Stored Procedure Stub
CREATE PROCEDURE dbo.StoredProcedure1 /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ RETURN
The stubbed procedure also opened in the stored procedure designer, an editor with its own context menus that are primarily designed to code, run, and test T-SQL. All you need to do at this point is change the name, add parameters, and add the T-SQL after the AS keyword. For example, to write an InsertCustomers procedure, you list the parameters and types from the customers table and write an INSERT statement. The result looks like the code in Listing 2.
Listing 2: Writing an INSERT Stored Procedure in T-SQL
CREATE PROCEDURE dbo.InsertCustomer ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) VALUES ( @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax )
Insert statements don’t vary much. Generally, you write INSERT INTO tablename, list the field names in order, and follow those with the values (one for each field). You can copy the parameter names, types, and sizes for the procedure header from the table designer right into the SQL designer.
To quickly copy the Customers’ table schema, do this:
- In the Server Explorer, expand the Northwind Tables node.
- Select the Customers table.
- Select Design Table from the context or Database menu.
- Click the upper-left most cell of the Table designer to select all columns and rows—think of the Table designer as a database table itself—and press Ctrl+C.
- Switch to the stored procedure designer (or editor, if you like) and press Ctrl+V (in other words, copy and paste the schema).
- Add parentheses around the size values and delimit each parameter—except the last one—with a comma.
That’s it. You have written a basic but functional stored procedure.
Testing Your Stored Procedure
To test the stored procedure, select Run Stored Procedure from the Database or context menu. You will be presented with a simple dialog (see Figure 2). Fill out values for each parameter and click OK. If the stored procedure contains an error, the VS.NET IDE will indicate that one or more errors exist. Fix the errors and try again.
Figure 2: A Real Simple Means of Testing Store Procedures: Select Database|Run Stored Procedure.
Now that you have the hang of it, add a stored procedure for selecting a customer and one for deleting a customer. Listings 3 and 4 show these solutions, respectively.
Listing 3: Select a Specific Northwind Customer by CustomerID
CREATE PROCEDURE dbo.SelectCustomer ( @CustomerID nchar(5) ) AS SET NOCOUNT ON SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers NOLOCK WHERE CustomerID = @CustomerID
Listing 4: Delete a Northwind Customer by CustomerID
CREATE PROCEDURE dbo.DeleteCustomer ( @CustomerID nchar(5) ) AS DELETE FROM Customers WHERE CustomerID = @CustomerID RETURN @@ROWCOUNT
Notice that the select procedure introduced the SET NOCOUNT ON statement and the NOLOCK keyword. SET NOCOUNT ON instructs SQL Server not to count the rows in the result set, and NOLOCK is a SQL hint to not issue a shared lock, not honor exclusive locks, and maybe permit a dirty read. In the delete procedure, the row count is left on (by default) and the number of rows deleted is returned. The @@ROWCOUNT value is reserved. It contains the number of rows affected by the last operation.
Generating and archiving SQL script
Let’s take a quick detour for a moment.
VS.NET supports a Database project. This project is a convenient place to store connection information and, more importantly, SQL scripts. By selecting any element of your database and selecting Generate Create Script from the Database or context menu, you can create SQL scripts that can easily rebuild any particular aspect of your database (except data, for which you need backups) or write custom scripts to insert data such as dictionary information.
This feature is often overlooked because, if you generate scripts, it is very easy to archive SQL source code (like VB.NET source code) in SourceSafe. Versioning your scripts is a great way to reset data after testing or rolling back experimental changes. Versioning your scripts is also a great way to undo changes erroneously made by other developers, or worse: well-intentioned DBAs. Yes, you should have a regular schedule for backing up your database, but scripting your database is a nifty way of permitting experimentation, restoring changes after tests or between backups, and preventing a small error like a dropped stored procedure from necessitating a more complex restore or a DBA’s intervention.
Finally, scripting your database is an excellent way to support the deployment of your software. As part of your installation process, simply run the script (which includes scripts for lookup data or seed values) to create the database, permissions, procedures, schema, views, and functions. The collective result is a worry-free database working environment and a professional installation and deployment process.
To create a Database project and script your database, select File|New Project|Other Projects|Database Projects and select the Database project from the Templates section of the New project dialog. Use the name of your database (Northwind) for the project name, and use the Data Link Properties editor to specify a connection for your database project. After you’ve created the project, generate and save a script of your database each time you change it, making sure to check changes into version control between updates.
Creating a Connection
Now that you have some stored procedures to play with, you need to write some code to invoke those procedures. The first thing you need to do is create a SqlConnection object. To do that, you need a connection string. You can use the Console Application module you created when you began.
ADO.NET uses a disconnected model. Quite simply, this means you create a connection, open it, perform your SQL request, and—no matter what happens—close the connection. The no matter what happens piece means you use a Try Finally block. For example, Listing 5 shows a block of code (called a snippet) that you can copy into the Toolbox and drag and drop into the code editor each time you want to use it.
Listing 5: The Rhythm for Using Connection in ADO.NET, Safely, Includes a Try…Finally Block to Ensure the Connection Is Closed.
Dim connectionString As String = _ "Integrated Security=SSPI;Persist Security Info=False;" + _ "Initial Catalog=Northwind;Data Source=PTK800" Dim connection As SqlConnection = New SqlConnection(connectionString) connection.Open() Try Console.WriteLine(connection.State.ToString()) Finally connection.Close() End Try
As an informed reader, you might ask, “Do I repeat this code all over the place?” A reasonable response would be that you place a SqlConnection control on a Form or UserControl. Some seasoned veterans do that, but mostly in sample programs. A better response is that you download the Data Access Application Block (DAAB) from Microsoft and allow it to consolidate a lot of steps into a condensed set of instructions for you (see Using Application Blocks for Data Access, by Paul Kimmel, at www.informit.com for more information on programming with the DAAB).
Creating a Command Object
To invoke a stored procedure, you can use a SqlCommand object. The SqlCommand object takes a connection, or optionally a transaction, and represents the objectification of your T-SQL command. This is where you will stuff your stored procedure parameters, execute non-resultset commands, and read output parameters.
The basic rhythm for using a SqlCommand object is to create the command that passes the command text and the connection. The command text can be literal SQL or a stored procedure name, and the connection can be a literal connection string or a connection object. (You just gotta love polymorphism!) Next, indicate that the command text represents a stored procedure name, fill in the parameters, and put the SqlCommand object to work. Listing 6 combines the connection code with additional command code, demonstrating how to invoke the InsertCustomer procedure.
Listing 6: Invoking InsertCustomer Using SqlConnection and SqlCommand Objects.
Public Sub InsertCustomer() Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("InsertCustomer", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CustomerID", "PAULK") command.Parameters.Add("@CompanyName", "Pauly's Bar") command.Parameters.Add("@ContactName", "Paul Kimmel") command.Parameters.Add("@ContactTitle", "The Fat Man") command.Parameters.Add("@Address", "31025 La Jolla") command.Parameters.Add("@City", "Inglewood") command.Parameters.Add("@Region", "CA") command.Parameters.Add("@Country", "USA") command.Parameters.Add("@PostalCode", "90425") command.Parameters.Add("@Phone", "(415) 555-1234") command.Parameters.Add("@Fax", "(415) 555-1235") Console.WriteLine("Rows inserted: " + _ command.ExecuteNonQuery().ToString) Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub
The code listing indicates the InsertCustomer procedure and the connection object. It specifies the command type using the CommandType enumerated value StoredProcedure and passes one parameter for each of the parameters in the stored procedure header. Finally, it uses the SqlCommand object to execute the procedure. For the example, the SqlCommand.ExecuteNonQuery method was called. ExecuteNonQuery is used for a procedure or SQL that does not return a resultset. ExecuteNonQuery returns a cardinal value indicating the number of rows affected.
Can you use literal SQL instead of a stored procedure? Absolutely, but don’t. It is much better to have clear divisions between the parts of your problem and their solutions. Using stored procedures lets you focus on SQL when writing SQL and VB.NET when writing VB.NET. Using stored procedures also packs database capabilities with your database, which means other developers can use the stored procedure for other solutions, and using stored procedures keeps your VB.NET code tidier. Finally, using stored procedures promotes a division of labor where SQL experts can work on SQL code and VB.NET experts (you) can focus on VB.NET code.
Filling a DataTable
An ADO.NET DataTable represents an in-memory resultset. The DataTable contains the results of one SELECT statement, whether the resultset draws that data from a view or multiple tables.
To invoke SelectCustomer and fill a DataTable, you need to incorporate a SqlDataAdapter into your code. The SqlDataAdapter uses the SqlCommand object, representing the command when you call SqlDatAdapter.Fill. Listing 7 shows an example running against your result set.
Listing 7: Filling a DataTable Using SelectCustomer and a SqlDataAdapter
Public Sub SelectCustomer() Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("SelectCustomer", _ connection) command.Parameters.Add("@CustomerID", "PAULK") command.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(command) Dim table As DataTable = New DataTable adapter.Fill(table) Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub
Because the SelectCustomer command requires only one parameter, the CustomerID, you reduced the number of parameters, created a SqlDataAdapter by initializing it with the SqlCommand object, created a DataTable, and called SqlDataAdapter.Fill to fill the DataTable. If an external consumer wanted to use the DataTable, you would need only change your code to a function and return the DataTable right after the adapter.Fill(table) statement. The rest of the code would remain unchanged.
You can delete a customer by copying Listing 6 almost intact. All you need to do is exchange the InsertCustomer argument with DeleteCustomer and remove all of the parameters except for the first one, CustomerID.
To convert the code to a general utility function, define a method and pass in the CustomerID. I used this technique to create a general cleanup function to facilitate removing test customers. Listing 8 shows the delete capability as a general utility function.
Listing 8: Delete-a-customer Method
Public Sub DeleteCustomer(Optional ByVal commandID As String = _ "PAULK") Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("DeleteCustomer", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CustomerID", commandID) Console.WriteLine("Rows deleted: " + _ command.ExecuteNonQuery().ToString()) Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub
The Optional modifier in Visual Basic .NET supports a funny kind of polymorphism. You could write two DeleteCustomer methods. The first DeleteCustomer might take no parameters and call a second DeleteCustomer passing what is the default optional value in the method in Listing 8. The second DeleteCustomer would then take the string parameter. Behaviorally, this would feel just like a polymorphic method call. With Visual Basic .NET, you can use variations of methods with the Optional parameter and polymorphism.
Fetching Output Parameters
Suppose you elected to generate the CustomerID from the company name rather than let the user or code dictate the CustomerID. In this instance, the stored procedure would generate, as well as return, the CustomerID. To do this, you need to modify the CustomerID parameter of the InsertCustomer stored procedure to be an OUTPUT parameter and add an algorithm to generate the CustomerID. Listing 9 shows a modified stored procedure with the aforementioned adjustments. (The algorithm for CustomerID is not robust enough for practical use but could easily be replaced at a later time.)
Listing 9: Having the Stored Procedure Define the Primary Key and Return It as an Output Parameter.
ALTER PROCEDURE dbo.InsertCustomerCreateKey ( @CustomerID nchar(5) = '' OUTPUT, @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS /* Generate unique key if possible */ SET @CustomerID = LEFT(@CompanyName, 5) INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) VALUES ( @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax ) RETURN @@ROWCOUNT
Obtaining and using the output parameter requires a minor adjustment to the code that calls InsertCustomer. In short, after you create the CustomerID parameter, you need to specify the type, size, and direction. The function FetchOutputParameter (in Listing 10) demonstrates these changes. The additional or revised lines of code are in boldface.
Listing 10: FetchOutputParameter Demonstrates How to Use Output Parameters.
Public Function FetchOutputParameter() As String Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("InsertCustomerCreateKey", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CustomerID", "") command.Parameters(0).SqlDbType = SqlDbType.NChar command.Parameters(0).Size = 5 command.Parameters(0).Direction = ParameterDirection.Output command.Parameters.Add("@CompanyName", "Pauly's Bar") command.Parameters.Add("@ContactName", "Paul Kimmel") command.Parameters.Add("@ContactTitle", "The Fat Man") command.Parameters.Add("@Address", "31025 La Jolla") command.Parameters.Add("@City", "Inglewood") command.Parameters.Add("@Region", "CA") command.Parameters.Add("@Country", "USA") command.Parameters.Add("@PostalCode", "90425") command.Parameters.Add("@Phone", "(415) 555-1234") command.Parameters.Add("@Fax", "(415) 555-1235") Console.WriteLine("Rows inserted: " + _ command.ExecuteNonQuery().ToString()) Console.WriteLine("Generated key is: " + _ command.Parameters(0).Value) Return command.Parameters(0).Value Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Function
Using ADO.NET Transactions
Let’s wrap up with transactions. You can use a transaction for every interaction that changes data, but you don’t have to. However, if you are making multiple dependent changes, transactions are a must.
ADO.NET makes using transactions simple. All you need to do is declare a transaction, call SqlConnection.BeginTransaction to start the transaction, and have every command use that connection and transaction to participate in the transaction. In the try block, call commit after all commands have executed and in the except block call SqlTransaction.Rollback. The finally block still closes the connection.
By using this technique, you can make sure that multiple dependent modifications to a database all either succeed or fail. Maintaining consistency in your database is an imperative in database programming.
In this example, the code calls InsertCustomer and InsertRegion. If the RegionDescription is not found in the Region table, a row is added to the Region table as part of the transaction. Listing 11 shows the new InsertRegion method with a conditional test, and Listing 12 shows the function UseTransaction that invokes both stored procedures, showing the placement of the new transaction code.
Listing 11: The InsertRegion Stored Procedure with an IF-conditional Test.
ALTER PROCEDURE dbo.InsertRegion ( @RegionID int = NULL OUTPUT, @RegionDescription nchar(50) ) AS IF((SELECT COUNT(@RegionID) FROM Region WHERE RegionDescription = @RegionDescription) = 0) BEGIN SELECT @RegionID = MAX(RegionID) + 1 FROM Region NOLOCK INSERT INTO Region ( RegionID, RegionDescription ) VALUES ( @RegionID, @RegionDescription ) END RETURN @@ROWCOUNT
Listing 12: Using the SqlTransaction Object to Treat Two Insert-Stored Procedures as a Homogeneous Operation.
Public Sub UseTransaction() Dim connection As SqlConnection = _ New SqlConnection(connectionString) Dim transaction As SqlTransaction = connection.BeginTransaction connection.Open() Try ' Insert Customer Dim insertCustomer As SqlCommand = _ New SqlCommand("InsertCustomer", connection) insertCustomer.CommandType = CommandType.StoredProcedure insertCustomer.Transaction = transaction insertCustomer.Parameters.Add("@CustomerID", "PAULK") insertCustomer.Parameters.Add("@CompanyName", "Pauly's Bar") insertCustomer.Parameters.Add("@ContactName", "Paul Kimmel") insertCustomer.Parameters.Add("@ContactTitle", "The Fat Man") insertCustomer.Parameters.Add("@Address", "31025 La Jolla") insertCustomer.Parameters.Add("@City", "Inglewood") insertCustomer.Parameters.Add("@Region", "CA") insertCustomer.Parameters.Add("@Country", "USA") insertCustomer.Parameters.Add("@PostalCode", "90425") insertCustomer.Parameters.Add("@Phone", "(415) 555-1234") insertCustomer.Parameters.Add("@Fax", "(415) 555-1235") Console.WriteLine("Rows inserted: " + insertCustomer.ExecuteNonQuery().ToString) Dim insertRegion As SqlCommand = _ New SqlCommand("InsertRegion", _ connection) insertRegion.Parameters.Add("@RegionID", Nothing) insertRegion.Parameters(0).Direction = _ ParameterDirection.Output insertRegion.Parameters.Add("@RegionDescription", "CA") Console.WriteLine("Rows inserted: " + insertRegion.ExecuteNonQuery().ToString) transaction.Commit() Catch ex As Exception transaction.Rollback() Console.WriteLine("Rolled back transaction, " + ex.Message) Throw Finally connection.Close() End Try End Sub
An Optimal Approach
Software developers have to make many choices. Should the constraints move with the database or the code, or should constraints be repeated in both places? Should SQL be inline in your VB.NET code or in stored procedures? There are many schools of thought on both sides of the fence.
A thorough, maximal implementation means that the database is complete, stands alone, and is ready for any code to be layered on top of it. A thorough implementation also means that the code is complete, stands alone, and won’t send bad data to the database—whether the database would accept the bad data or not. This maximal approach also takes a maximal amount of time. An optimal approach means that you use the rules and techniques necessary to cover the use cases thoroughly; unfortunately, programmers are often asked to implement an optimal solution on a minimal budget and schedule.
After reading this article, you should be better prepared for the pragmatics of the world in which you live.
Paul Kimmel is the VB Today columnist, has written several books on .NET programming, and is a software architect. Look for his upcoming book UML DeMystified from McGraw-Hill/Osborne, Spring 2005. You may contact him at [email protected] if you need assistance developing software or are interested in joining the Lansing Area .NET Users Group (glugnet.org).
Copyright © 2004 by Paul Kimmel. All Rights Reserved.