You call stored procedures in basically the same manner as executing other SQL commands. When creating the SqlCommand, set the query string to be the name of the stored procedure, and then set the CommandType to be CommandType.StoredProcedure.

SqlCommand command = new SqlCommand( "CustOrderHist", connection );
command.CommandType = CommandType.StoredProcedure;

After you setup the command type, you need to specify the parameters' name, type, value and direction (i.e., input or output).

SqlParameter cidParam =
    command.Parameters.Add( "@CustomerID", SqlDbType.VarChar, 5 );
cidParam.Direction = ParameterDirection.Input;
cidParam.Value = "ALFKI";

The method of executing the stored procedure varies depending on whether it returns a table or not. If the stored procedure doesn't return a table, you invoke it using

command.ExecuteNonQuery();

If the stored procedure returns a table, you can use a DataAdapter to fill a DataSet. Here's a sample that invokes a stored procedure that returns a table and then binds the result to a DataGrid.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

private static string connectionString =
  "Integrated Security=SSPI;Data Source=localhost;Initial Catalog=Northwind;";

private DataSet CustomerOrderHistory( string customerID )
{
  DataSet dataset = null;
  using( SqlConnection connection = new SqlConnection( connectionString ) )
  {
    using ( SqlCommand command =
      new SqlCommand( "CustOrderHist", connection ) )
    {
      command.CommandType = CommandType.StoredProcedure;

      SqlParameter cidParam =
        command.Parameters.Add( "@CustomerID", SqlDbType.VarChar, 5 );
      cidParam.Direction = ParameterDirection.Input;
      cidParam.Value = customerID;

      using ( SqlDataAdapter adapter = new SqlDataAdapter( command ) )
      {
        dataset = new DataSet();
        adapter.Fill( dataset, "Orders" );
      }
    }
  }
  return dataset;
}

private void Form1_Load(object sender, System.EventArgs e)
{
  DataSet dataset = CustomerOrderHistory( "ALFKI" );
  dataGrid1.DataSource = dataset.Tables[ "Orders" ];
}

George Shepherd, Syncfusion, and Stuart Celarier, Fern Creek