Programming Visual Basic .NET

Executing Stored ProceduresThrough 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(  )

Note

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 ...