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


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:

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

Get Programming Visual Basic .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.