Calling Stored Procedures with ADO.NET, Page 2
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.