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