Using Stored Procedures and Parameters.AddWithValue

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.

Turning It Up a Notch

As promised, some of you undoubtedly will have mastered the ADO.NET code already. If so, maybe you know about copying code to the toolbox for future use. (If not, see the Tip.)

For those of you still with me, here is something you may find useful. The master table contains all kinds of information about your databases, including the stored procedures defined and things like parameter definitions. For example, parameter definitions are found in the master table Views in System Views. The view name is INFORMATION_SCHEMA.PARAMETERS. By querying this view, you get the parameter direction, name, data type, size, and all kinds of other goodies. What can you do with these? Why, write an ADO.NET code generator, of course.

The query

select * from information_schema.parameters
   where specific_name = '{0}'

where you replace '{0}' with the stored procedure name—for instance, InsertCustomer—will return the parameters for that query. With the results from this query, you could use simple string substitution and generate a string version of the ADO.NET code to call the sproc. (Get a little fancy and you could write a code generator using the CodeDOM to generate dynamically runnable code.)

The class is Listing 3 demonstrates how you could build the Using statement in Listing 2 for the InsertCustomer stored procedure. (The code was only tested against that stored procedure, but with a little tweaking it should be generally useful.)

Listing 3: This code reads parameter information about a stored procedure and generates ADO.NET code in VB that is pretty close to runnable; the results are copied to the Clipboard.

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports System.Text.RegularExpressions

Public Class CreateSprocCall

   Private Shared template As String = _
      "using connection as sqlconnection = _
       new sqlconnection( _\r\n" + _
      "\t""{0}"")\r\n" + _
      "\tconnection.open()\r\n" + _
      "\tdim command as sqlcommand = _
       new sqlcommand(""{1}"", connection)\r\n" + _
      "\tcommand.commandtype = _
       commandtype.storedprocedure\r\n" + _
      "\t{2}\r\n" + _
      "\tcommand.executenonquery()\r\n" + _
      "end using\r\n"

   Private Shared Function _
      GetTemplate() As String
      Return Regex.Unescape(template)
   End Function

   Private Shared Function _
      GetParametersSql(ByVal sprocname As String) As String

      Const sql As String = _
         "select * from information_schema.parameters where _
          specific_name = '{0}'"

      writeline(String.Format(sql, sprocname))
      Return String.Format(sql, sprocname)

   End Function

   Public Shared Function GetCode(ByVal sprocname As String, _
   ByVal connectionstring As String) As String

      Using connection As SqlConnection = _
         New SqlConnection(connectionstring)
         connection.Open()
         Dim command As SqlCommand = _
            New SqlCommand(GetParametersSql(sprocname), connection)
         Dim reader As SqlDataReader = command.ExecuteReader

         Dim builder As StringBuilder = New StringBuilder()
         builder.AppendLine()
         While (reader.Read())

            builder.Append(GetParameter(reader))
            builder.AppendLine()


         End While

         Return GetCodeString(connectionstring, _
            sprocname, Regex.Unescape(builder.ToString()))
      End Using
   End Function

   Private Shared Function GetCodeString( _
      ByVal connectionString As String, _
      ByVal SprocName As String, _
      ByVal parameters As String)

         Dim results As String = String.Format(GetTemplate(), _
            connectionString, SprocName, parameters)
         WriteLine(results)
         My.Computer.Clipboard.SetText(results)
         MsgBox("code copied to Clipboard")
         Return results

   End Function

   Private Shared Function GetParameter( _
      ByVal reader As SqlDataReader) As String
      Const mask As String = _
         "\tcommand.parameters.addwithvalue(""{0}"", {1})"

      Dim parametername As String = reader("parameter_name")
      Dim parametertype As String = reader("data_type")
      Dim length As Integer = reader("character_maximum_length")

   Return String.Format(mask, parametername, _
      GetParameterSampleData(parametertype, length))

   End Function

   Private Shared Function GetParameterSampleData( _
      ByVal parametertype As String, _
         ByVal length As Integer) As Object

      'put in some logic to figure the sample data to return
      length = IIf(length > 10, 10, length)
      Return String.Format("""{0}""", New String("x", length))

   End Function

   Private Shared FLog As TextWriter
   Public  Shared WriteOnly Property Log() As TextWriter
      Set(ByVal value As TextWriter)
         flog = value
      End Set
   End Property

   Private Shared Sub WriteLine(ByVal str As String)
      If (FLog Is Nothing = False) Then
         FLog.WriteLine(str)
      End If
   End Sub


End Class

Note: Pardon me for any weird capitalization you might notice. Visual Studio was trying to "help me" and it lower-cased everything. I think I got everything Pascal-cased, but I might have missed something.

The code is pretty basic. Call GetCode with the stored procedure name and a connection string. The code reads INFORMATION_SCHEMA.PARAMETERS and fills in the ADO.NET AddWithValue calls in the template string. The results are returned and copied to the clipboard for convenience.

Regex.Unescape is used to take into account the \t (tab) and \r\n (carriage return newlines) for pagination purposes, and the Log property can be assigned Console.Out to display the output of the class—that is, tell you what the class is doing.

With a little extra work, you could add parameter directions, data types, and lengths and take into account elements such as readers, adapters, and DataSets. I hope you have fun playing with the code.

Summary

From Alice in Wonderland, Lewis Carroll wrote "Begin at the beginning and go on till you come to the end; then stop." Everyone is at different phases of their programming journey. I hope some basic ADO.NET code helps those new to ADO.NET and that the Tip and sample at the end gave the rest of you a reason to read, too. Thanks for playing.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#; preorder your copy today at Amazon.com. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.