Tuesday, May 20, 2008

How do you call a stored procedure?

Create a command object, set the object’s CommandText property to the name of the stored procedure, and set the CommandType property to StoredProcedure. To execute the stored procedure, use the command object’s ExecuteNonQuery, ExcecuteScalar, ExecuteReader, or ExecutelXmlReader method. For example, the following code calls the Ten Most Expensive Products stored procedure on the Northwind Traders database:

Visual Basic .NET
' Create a command object to execute.
Dim cmdTopTen As New SqlCommand(connNWind)
' Set command text.
cmdTopTen.CommandText = "Ten Most Expensive Products"
' Set the command properties.
cmdTopTen.CommandType = CommandType.StoredProcedure
' Create a data reader object to get the results.
Dim drdTopTen As SqlDataReader
' Open the connection.
connNWind.Open()
' Excecute the stored procedure.
drdTopTen = cmdTopTen.ExecuteReader()
Visual C#
// Create a connection for NorthWind Trader's database.
SqlConnection connNWind = new SqlConnection("integrated security=SSPI;" +
"data source=(local);initial catalog=Northwind");
// Create a command object to execute.
SqlCommand cmdTopTen = new SqlCommand(connNWind);
cmdTopTen.CommandText = "Ten Most Expensive Products";
// Set the command properties.
cmdTopTen.CommandType = CommandType.StoredProcedure;
// Create a data reader object to get the results.
SqlDataReader drdTopTen;
// Open the connection.
connNWind.Open();
// Excecute the stored procedure.
drdTopTen = cmdTopTen.ExecuteReader();

No comments: