Executing a Stored Procedure

for RuBoard

There are a few different ways to execute stored procedures within ADO.NET. The first section following uses methods you've already seen. Then, you'll see the generally accepted standard way of executing the stored procedure.

Using `Exec ()`

In Hour 6, "Retrieving Data from the Data Source," you saw how to create Connection , Command , and DataReader objects and use them to retrieve data from your database. As you recall, the ExecuteReader() method of the DataReader object returns a forward-only, read-only resultset, which you can then use to bind to a list control, or step through manually.

Almost precisely the same code can be reused to call a stored procedure. Rather than placing a query into the Command object, you can call the stored procedure directly, as you did with the Query Analyzer. Listing 15.3 shows a Web form that calls the Customers_Get stored procedure using this method.

Listing 15.3 A Simple Stored Procedure

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)           'Create and Open Connection           Dim conn as SqlConnection = new SqlConnection("Data Source=" + _                          "localhost;Initial Catalog=northwind;UID=sa;PWD=;")           conn.Open()           'Create Command object           Dim cmd as SqlCommand = new SqlCommand("Exec Customers_Get", conn)           Dim nwReader as SqlDataReader = cmd.ExecuteReader()           customers.DataSource = nwReader           customers.DataBind()         End Sub     </script> </HEAD> <BODY> <h1>Creating a DataSet</h1> <hr> <form runat="server" id=form1 name=form1>    <asp:DataGrid id="customers" runat="server"></asp:DataGrid> </form> <hr> </BODY> </HTML>

This method works quite well for stored procedures that only return data. However, calling stored procedures using this method makes it impossible to access any output parameters that the stored procedure may have. In the next section, you'll see another way to call a stored procedure that gives you full access to these values.

Specifying `CommandType`

The Command object has a property named CommandType . Normally, you do not need to worry about this property because, by default, ADO.NET assumes you will be sending a query directly to the database, instead of calling a stored procedure. To tell ADO.NET that you're calling a stored procedure, you set the CommandType property as in the following line of code:

 cmd.CommandType = CommandType.StoredProcedure

Then you only need to place the name of the stored procedure in the Command object. The ADO.NET code in Listing 15.4 can be placed into the preceding example in Listing 15.3, and will function identically.

Listing 15.4 Another Way to Call a Stored Procedure

 <script language="VB" runat="server" >     Sub Page_Load(Source as Object, E as EventArgs)         'Create and Open Connection         Dim conn as SqlConnection = new SqlConnection( _                "Data Source=localhost;" + _                "Initial Catalog=northwind;UID=sa;PWD=;")         'Create Command         Dim cmd as SqlCommand = new SqlCommand("Customers_Get", conn)         cmd.CommandType = CommandType.StoredProcedure         conn.Open()         Dim nwReader as SqlDataReader = cmd.ExecuteReader()         customers.DataSource = nwReader         customers.DataBind()         conn.Close()         nwReader.Close()     End Sub </script>

Now that you've seen how to call simple stored procedures from ADO.NET, it's time to see how to call some stored procedures that have input and output parameters.

for RuBoard

Using Exec ()

Listing 15.3 A Simple Stored Procedure

Specifying CommandType

Listing 15.4 Another Way to Call a Stored Procedure

Using `Exec ()`

Specifying `CommandType`