WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

ADO.NET, Part 5
Pages: 1, 2

Executing Stored Procedures Through a SqlCommand Object

To execute a stored procedure, set an SqlCommand object's Commandtext property to the name of the stored procedure to be executed, and set the Commandtype property to the constant Commandtype.StoredProcedure (defined in the System.Data namespace). Then call the ExecuteNonQuery method. Example 8-13 does just that.



Example 8-13: Executing a parameterless stored procedure

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a command object. (Assumes that the database contains a
' stored procedure called "PurgeOutdatedOrders".)
Dim cmd As New SqlCommand("PurgeOutdatedOrders", cn)
cmd.Commandtype = Commandtype.StoredProcedure
 
' Execute the command.
cmd.ExecuteNonQuery(  )
 
' Close the database connection.
cn.Close(  )

TIP:  Example 8-13 assumes for the sake of demonstration that the database contains a stored procedure called "PurgeOutdatedOrders". If you would like to have a simple stored procedure that works with Example 8-13, use this one:

CREATE PROCEDURE PurgeOutdatedOrders AS
DELETE FROM Orders
WHERE OrderDate < '04-Jul-1990' 
   AND Shippeddate IS NOT NULL

See your SQL Server documentation for information on how to create stored procedures.

Some stored procedures have parameters, and some have a return value. For these stored procedures, the SqlCommand class provides the Parameters property. The Parameters property contains a reference to an SqlParameterCollection object. To pass parameters to a stored procedure and/or to read the return value of a stored procedure, add SqlParameter objects to this collection.

Example 8-14 calls a stored procedure that takes a single argument.

Example 8-14: Executing a parameterized stored procedure

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a command object. (Assumes that the database contains a
' stored procedure called "PurgeOutdatedOrders2".)
Dim cmd As New SqlCommand("PurgeOutdatedOrders2", cn)
cmd.Commandtype = Commandtype.StoredProcedure
 
' Set up the @BeforeDate parameter for the stored procedure.
Dim param As New SqlParameter("@BeforeDate", SqlDBType.DateTime)
param.Direction = ParameterDirection.Input
param.Value = #7/4/1990#
cmd.Parameters.Add(param)
 
' Execute the command.
cmd.ExecuteNonQuery(  )
 
' Close the database connection.
cn.Close(  )

TIP:  Example 8-14 assumes for the sake of demonstration that the database contains a stored procedure called "PurgeOutdatedOrders2". If you would like to have a simple stored procedure that works with Example 8-14, use this one:

CREATE PROCEDURE PurgeOutdatedOrders2 
@BeforeDate datetime
AS
DELETE FROM Orders
WHERE OrderDate < @BeforeDate
   AND Shippeddate IS NOT NULL

See your SQL Server documentation for information on how to create stored procedures.

The steps taken in Example 8-14 are:

  1. Open a connection to the database.
  2. Instantiate an SqlCommand object using this constructor:
    Public Overloads Sub New( _
       ByVal cmdtext As String, _
       ByVal connection As System.Data.SqlClient.SqlConnection _
    )

    The cmdtext parameter specifies the name of the stored procedure, and the connection parameter specifies the database connection to use.

  3. Set the SqlCommand object's Commandtype property to Commandtype.StoredProcedure to indicate that the cmdtext parameter passed to the constructor is the name of a stored procedure.
  4. Create an SqlParameter object to pass a value in the PurgeOutdatedOrders2 stored procedure's @BeforeDate parameter. This is done as follows:

    1. Instantiate an SqlParameter object using this constructor:
    2. Public Overloads Sub New( _
         ByVal parameterName As String, _
         ByVal dbType As System.Data.SqlClient.SqlDbType _
      )

      Previous Excerpts

      ADO.NET, Part 1


      ADO.NET, Part 2


      ADO.NET, Part 3


      ADO.NET, Part 4

      The parameterName parameter specifies the name of the stored procedure parameter and should match the name as given in the stored procedure. The dbType parameter specifies the SQL Server data type of the parameter. This parameter can take any value from the SqlDbType enumeration.

    3. Set the SqlParameter object's Direction property to ParameterDirection.Input. This indicates that a value will be passed from the application to the stored procedure.
    4. Set the Value property of the SqlParameter object.
    5. Add the SqlParameter object to the SqlCommand object's Parameters collection by calling the SqlParameterCollection object's Add method.

  5. Execute the stored procedure.

Note the SqlParameter class's Direction property. Setting this property to the appropriate value from the ParameterDirection enumeration (declared in the System.Data namespace), can make a SqlParameter object an input parameter, an output parameter, an in/out parameter, or the stored procedure's return value. The values in the ParameterDirection enumeration are:

Input
The parameter provides a value to the stored procedure.

InputOutput
The parameter provides a value to the stored procedure and receives a new value back from the stored procedure.

Output
The parameter receives a value back from the stored procedure.

ReturnValue
The parameter receives the stored procedure's return value.

Summary

In this chapter, you learned about Microsoft's data-access technology, ADO.NET. You learned how to connect to a database, how to read data with either a DataSet object or a DataReader object, how to navigate and change data in a DataSet, how to use the DataSet's XML capabilities, how to generate typed DataSets, and how to execute stored procedures using an SqlCommand object.


View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.