CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Introduction
Someone asked me, “How do you figure out what to write about?” The answer is that I generally write about either what I am currently pursuing, the latest and greatest whiz bang technology, or what people are asking about.
Lately, I have written a lot of advanced stuff about LINQ. LINQ is very cool and it was a lot of fun writing my upcoming book LINQ Unleashed for C#, but I got some inquiries about calling a stored procedure. This reminded me that there are readers at all levels—easy to forget sometimes—and they need more down-to-earth samples.
The challenge then becomes: How do I help people who may be writing their first stored procedure and entice readers who have written their ten-thousandth line of ADO.NET code? Read this article and you’ll see the answer. (If you are a guru at ADO.NET code skip to the “Turning It Up a Notch” section.)
Defining a Stored Procedure to Experiment With
A stored procedure is a lot like a VB function. There are a header and parameters. Define the header and the input arguments and call the procedure. The trick is that, because the function lives in SQL Server, you have to use ADO.NET as a conduit to access the stored procedure. To use ADO.NET, you basically need a connection and a command.
For the sample, I used the Northwind database and added the stored procedure in Listing 1. You can create the stored procedure in Visual Studio or SQL Server Management Studio. (Yes, you can use SQL Server Management Studio with SQL Server Express. See my blog http://www.softconcepts.com/blogs/pkimmel SQL Server Management Studio Not Installed – Fix.)
Listing 1: A stored procedure that inserts a customer record into the Northwind Traders Customers table.
ALTER PROCEDURE dbo.InsertCustomer
(
@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
DECLARE @COUNTER AS INT
SET @COUNTER = 1
SET @CustomerID = LEFT(@CompanyName, 5)
WHILE(@COUNTER < 10)
BEGIN
IF NOT EXISTS (SELECT CustomerID
FROM CUSTOMERS WHERE CustomerID = @CustomerID)
BREAK
SET @CustomerID = LEFT(@CompanyName, 4) +
CAST(@COUNTER As NVarChar(10))
SET @COUNTER = @COUNTER + 1
END
IF(@COUNTER > 9)
BEGIN
RAISERROR('Error generating a unique customer id', 16, 1)
END
SET @CustomerID = UPPER(@CustomerID)
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
The stored procedure basically inserts a row into the Customers table and returns the Primary Key, CustomerID. The code at the beginning of the stored procedure (“sproc“) tries to generate unique CustomerID based on the first four characters of the CompanyName and the integers 1 to 9. An error is raised if there are ten or more nearly identical CustomerIDs.
Invoking the InsertCustomer Stored Procedure
To call this stored procedure, you need to complete a few tasks. Here they are at a high level:
- Create a VB Console application because they are easiest to work with for demos.
- In Module1.vb, add an Imports System.Data.SqlClient statement at the top of the file.
- Open the Server Explorer – View|Server Explorer – and click the Northwind database under the Data Connections node. (If you don’t have a connection to Northwind, you will need to add one from the Data|Add New Data Source menu.)
- Press F4 to open the Properties Window. From that, copy the Connection String property.
- Paste the Connection String in the Sub Main, assigning it to a String variable named connectionString.
- Next, you will need to create a SqlConnection and open it all in a Using statement.
- Create the SqlCommand, initialize the parameters and execute the stored procedure.
That’s it in a nutshell. The code that demonstrates is shown in its entirety in Listing 2.
Listing 2: A connection and connection string, a command, stored procedure name, and parameters are all you need to call a stored procedure.
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = _
"Data Source=.SQLExpress;Initial _
Catalog=northwind;Integrated Security=True"
Using connection As SqlConnection = _
New SqlConnection(connectionString)
connection.Open()
Dim command As SqlCommand = _
New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
Dim output As SqlParameter = _
command.Parameters.AddWithValue("@CustomerID", "")
output.SqlDbType = SqlDbType.NChar
output.Size = 5
output.Direction = ParameterDirection.InputOutput
command.Parameters.AddWithValue("@CompanyName", "TEST")
command.Parameters.AddWithValue("@ContactName", "TEST")
command.Parameters.AddWithValue("@ContactTitle", "TEST")
command.Parameters.AddWithValue("@Address", "TEST")
command.Parameters.AddWithValue("@City", "TEST")
command.Parameters.AddWithValue("@Region", "TEST")
command.Parameters.AddWithValue("@PostalCode", "TEST")
command.Parameters.AddWithValue("@Country", "TEST")
command.Parameters.AddWithValue("@Phone", "TEST")
command.Parameters.AddWithValue("@Fax", "TEST")
command.ExecuteNonQuery()
Console.WriteLine(command.Parameters("@CustomerID").SqlValue)
End Using
Console.ReadLine()
End Sub
End Module
One unmentioned aspect of this particular example is the use of an output variable. Parameters.AddWithValue essentially returns a SqlParameter. For output parameters, you can specify the ParameterDirection as shown in the listing. You also can specify the data type and size, but you don’t always have to.
What if you want to read the values from a select, for example? In that instance, you can use a SqlDataAdapter and a DataSet to obtain the data, or you can use a SqlDataReader to read the data. It depends on what you are trying to do. If you are trying to populate custom objects, the reader will be faster. If you are more comfortable using DataSets and DataTables, use the adapter.
Tip: Once you get the code working, select all of the code in the using statement and drag it to the toolbox. You will have an example every time you need one in the future.
