How to: Execute a Stored Procedure that Returns Rows

To execute a stored procedure that returns row, you can run a TableAdapter query that is configured to run a stored procedure (for example, CustomersTableAdapter.Fill(CustomersDataTable)).

If your application does not use TableAdapters, call the ExecuteReader method on a command object, setting its CommandType property to StoredProcedure. ("Command object" refers to the specific command for the .NET Framework Data Provider that your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)

The following examples show how to execute stored procedures that return rows from a database using either TableAdapters or command objects. For more information on querying with TableAdapters and commands, see Filling Datasets with Data.

Executing Stored Procedures that Return Rows Using a TableAdapter

This example shows how to create a TableAdapter query using the TableAdapter Query Configuration Wizard, and then it provides information on how to declare an instance of the TableAdapter and execute the query.

To create a stored procedure returning rows using a TableAdapter

1. Open a dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.

2. If you do not already have one, create a TableAdapter. For more information, see How to: Create TableAdapters.

3. If you already have a query on your TableAdapter that calls a stored procedure that returns rows, skip to the next procedure, "To declare an instance of the TableAdapter and execute the query." Otherwise, continue with step 4 to create a new query that calls a stored procedure that returns rows.

4. Right-click the TableAdapter that you wan, and use the shortcut menu to add a query.

   The TableAdapter Query Configuration Wizard opens.

5. Click Next, and then choose Use existing stored procedure, and then click Next.

6. Select a stored procedure from the drop-down list, and then click Next.

7. Leave the default value of Tabular data, and then click Next.

8. Provide names for each method you want to create (Fill a DataTable and/or Return a DataTable methods).

9. Complete the wizard; the query is added to the TableAdapter.

10. Build your project.

To declare an instance of the TableAdapter and execute the query

1. Declare an instance of the TableAdapter that contains the query you want to execute.

   • To create an instance using design-time tools, drag the TableAdapter that you want from the Toolbox. (Components in your project now appear in the Toolbox under a heading that matches your project name.) If the TableAdapter does not appear in the Toolbox, then you may need to build your project.

     -or-

   • To create an instance in code, replace the following code with the names of your DataSet and TableAdapter.

     Dim tableAdapter As New DataSetTableAdapters.TableAdapter

     Note

     TableAdapters are not actually located inside their associated dataset classes. Each dataset has a corresponding collection of TableAdapters in its own namespace. For example, if you have a dataset named SalesDataSet, there would be a SalesDataSetTableAdapters namespace that contains its TableAdapters.

2. Call your query as you would call any other method in code. Your query is a method on the TableAdapter. Replace the following code with the names of your TableAdapter and query. You also need to pass in any parameters required by your query. If you are not sure if your query requires parameters, or what parameters it requires, then check IntelliSense for the required signature of the query. Depending on whether your query takes parameters or not, the code would look similar to one of the following examples:

   TableAdapter.Query()

   TableAdapter.Query(Parameters)

   TableAdapter.Query(DataTable, Parameters)

   The complete code to declare an instance of the TableAdapter and execute the query should look similar to the following:

   Dim tableAdapter As New NorthwindDataSetTableAdapters.CustomersTableAdapter()
   tableAdapter.FillByCity(NorthwindDataSet.Customers, "Seattle")
   

   NorthwindDataSetTableAdapters.CustomersTableAdapter tableAdapter = 
       new NorthwindDataSetTableAdapters.CustomersTableAdapter();
   
   tableAdapter.FillByCity(northwindDataSet.Customers, "Seattle");
   

Executing Stored Procedures that Return Rows Using a Command Object

The following example shows how to create a command and execute a stored procedure that returns rows. For information on setting and getting parameter values for a command, see How to: Set and Get Parameters for Command Objects.

This example uses the SqlCommand object and requires:

• References to the System, System.Data, and System.Xml namespaces.

• A data connection named SqlConnection1.

• A table named Customers in the data source that SqlConnection1 connects to. (Otherwise, you need a valid SQL statement for your data source).

To execute a stored procedure returning rows programmatically using a command object

• Add the following code to a method that you want to execute the code from. You return rows by calling the ExecuteReader method of the command (for example, ExecuteReader). The data is returned in a DataReader. For more information on accessing the data in a DataReader, see Retrieving Data Using a DataReader (ADO.NET).

  Dim sqlConnection1 As New SqlConnection("Your Connection String")
  Dim cmd As New SqlCommand
  Dim reader As SqlDataReader
  
  cmd.CommandText = "StoredProcedureName"
  cmd.CommandType = CommandType.StoredProcedure
  cmd.Connection = sqlConnection1
  
  sqlConnection1.Open()
  
  reader = cmd.ExecuteReader()
  ' Data is accessible through the DataReader object here.
  
  sqlConnection1.Close()
  

  SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
  SqlCommand cmd = new SqlCommand();
  SqlDataReader reader;
  
  cmd.CommandText = "StoredProcedureName";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = sqlConnection1;
  
  sqlConnection1.Open();
  
  reader = cmd.ExecuteReader();
  // Data is accessible through the DataReader object here.
  
  sqlConnection1.Close();
  

Security

The application requires permission to access the database and execute the stored procedure.

See Also

Tasks

How to: Create TableAdapter Queries

How to: Fill a Dataset with Data

How to: Set and Get Parameters for Command Objects

Reference

SqlCommand.ExecuteReader

OleDbCommand.ExecuteReader

OdbcCommand.ExecuteReader

OracleCommand.ExecuteReader

Concepts

Filling Datasets with Data