Using Stored Procedures and Parameters.AddWithValue
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.
Page 2 of 3
This article was originally published on July 25, 2008