O'Reilly logo

Programming Visual Basic .NET by Dave Grundgeiger

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required