Share via

Using Stored Procedures with a Command 

  • Article

Stored procedures offer many advantages in data-driven applications. Using stored procedures, database operations can be encapsulated in a single command, optimized for best performance, and enhanced with additional security. Although a stored procedure can be called by simply passing the stored procedure name followed by parameter arguments as an SQL statement, using the Parameters collection of the ADO.NET DbCommand object enables you to more explicitly define stored procedure parameters, as well as to access output parameters and return values.

To call a stored procedure, set the CommandType of the Command object to StoredProcedure. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters, as in the following example.

Note

The OdbcCommand requires that you supply the full ODBC CALL syntax when calling a stored procedure.

Example

' Assumes that connection is a valid SqlConnection object.

Dim salesCommand As SqlCommand = New SqlCommand( _
  "SalesByCategory", connection)
salesCommand.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = salesCommand.Parameters.Add( _
  "@CategoryName", SqlDbType.NVarChar, 15)
parameter.Value = "Beverages"

connection.Open()

Dim reader As SqlDataReader = salesCommand.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, ${1}", reader.GetString(0), reader.GetDecimal(1))
Loop

reader.Close()
connection.Close()

// Assumes that connection is a valid SqlConnection object.

SqlCommand salesCommand = new SqlCommand("SalesByCategory",
  connection);
salesCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = salesCommand.Parameters.Add(
  "@CategoryName", SqlDbType.NVarChar, 15);
parameter.Value = "Beverages";

connection.Open();

SqlDataReader reader = salesCommand.ExecuteReader();

Console.WriteLine(
  "{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine("{0}, ${1}", reader.GetString(0), 
    reader.GetDecimal(1));
}

reader.Close();
connection.Close();

A Parameter object can be created using the Parameter constructor, or by calling the Add method of the Parameters collection of a Command. Parameters.Add will take as input either constructor arguments or an existing Parameter object. When setting the Value of a Parameter to a null reference, use DBNull.Value.

For parameters other than Input parameters, you must set the ParameterDirection property to specify whether the parameter type is InputOutput, Output, or ReturnValue. The following examples demonstrate the difference between creating Input, Output, and ReturnValue parameters for the various providers.

SqlClient Example

' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand("SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = command.Parameters.Add( _
  "RETURN_VALUE", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", SqlDbType.NVarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", SqlDbType.NVarChar, 28)
parameter.Direction = ParameterDirection.Output

connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

Console.WriteLine( _
  "{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine( _
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine( _
  " @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine( _
  "RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", SqlDbType.NVarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", SqlDbType.NVarChar, 28);
parameter.Direction = ParameterDirection.Output;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

Console.WriteLine(
  "{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine(
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

OleDb Example

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

connection.Open();

OleDbDataReader reader = command.ExecuteReader();

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

Odbc Example

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

connection.Open()

Dim reader As OdbcDataReader = command.ExecuteReader()

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))

Do While reader.Read()
  Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop

reader.Close()
connection.Close()

Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

connection.Open();

OdbcDataReader reader = command.ExecuteReader();

Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));

while (reader.Read())
{
  Console.WriteLine( _
    "{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}

reader.Close();
connection.Close();

Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);

Using Parameters with a SqlCommand

When using parameters with a SqlCommand, the names of the parameters added to the Parameters collection must match the names of the parameter markers in your stored procedure. The .NET Framework Data Provider for SQL Server treats parameters in the stored procedure as named parameters and searches for the matching parameter markers.

The .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to an SQL statement or a stored procedure. In this case, you must use named parameters, as in the following example where @CustomerID is the named parameter.

SELECT * FROM Customers WHERE CustomerID = @CustomerID

Using Parameters with an OleDbCommand or OdbcCommand

When using parameters with an OleDbCommand or OdbcCommand, the order of the parameters added to the Parameters collection must match the order of the parameters defined in your stored procedure. The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC treat parameters in a stored procedure as placeholders and apply parameter values in order. In addition, return value parameters must be the first parameters added to the Parameters collection.

The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement or a stored procedure. In this case, you must use the question mark (?) placeholder, as in the following example.

SELECT * FROM Customers WHERE CustomerID = ?

As a result, the order in which Parameter objects are added to the Parameters collection must directly correspond to the position of the ? placeholder for the parameter.

Deriving Parameter Information

Parameters can also be derived from a stored procedure using the CommandBuilder class. Both the SqlCommandBuilder and OleDbCommandBuilder classes provide a static method, DeriveParameters, which automatically populates the Parameters collection of a Command object with parameter information from a stored procedure. Note that DeriveParameters overwrites any existing parameter information for the Command.

Deriving parameter information does require an added trip to the data source for the information. If parameter information is known at design time, you can improve the performance of your application by setting the parameters explicitly.

The following code example shows how to populate the Parameters collection of a Command object using CommandBuilder.DeriveParameters.

' Assumes that connection is a valid SqlConnection object.
Dim salesCommand As SqlCommand = New SqlCommand( _
  "Sales By Year", connection)
salesCommand.CommandType = CommandType.StoredProcedure

connection.Open()
SqlCommandBuilder.DeriveParameters(salesCommand)
connection.Close()

// Assumes that connection is a valid SqlConnection object.
SqlCommand salesCommand = new SqlCommand("Sales By Year", connection);
salesCommand.CommandType = CommandType.StoredProcedure;

connection.Open();
SqlCommandBuilder.DeriveParameters(salesCommand);
connection.Close();

See Also

Concepts

Executing a Command

Other Resources

Working with Commands
Using the .NET Framework Data Provider for SQL Server