http://www.developer.com/db/article.php/3438221/Calling-Stored-Procedures-with-ADONET.htm
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. 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 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: 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 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 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: That's it. You have written a basic but functional 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 Listing 4: Delete a Northwind Customer by CustomerID 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. 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. 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. 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). 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. 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. 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 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 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. 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. 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. 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. Listing 12: Using the SqlTransaction Object to Treat Two Insert-Stored Procedures as a Homogeneous Operation. 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 pkimmel@softconcepts.com 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.
Calling Stored Procedures with ADO.NET
November 19, 2004
A Quick Primer for Writing SQL Server Stored Procedures
Writing a stored procedure
Click here for a larger image.
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN
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
)
Testing Your Stored Procedure
Click here for a larger image.
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
CREATE PROCEDURE dbo.DeleteCustomer
(
@CustomerID nchar(5)
)
AS
DELETE FROM Customers
WHERE CustomerID = @CustomerID
RETURN @@ROWCOUNT
Generating and archiving SQL script
Creating a Connection
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
Creating a Command Object
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
Filling a DataTable
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
Invoking DeleteCustomer
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
Fetching Output Parameters
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
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
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
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
Biography
