Specifying Parameters and Return Values 

Stored procedures can have return values in addition to input and output parameters. The sample below illustrates how ADO.NET sends and receives input parameters, output parameters, and return values by inserting a new record into a table where the primary key column is an identity column in a SQL Server database.

Example

The sample uses the following stored procedure to insert a new category into the Northwind Categories table, which takes the value in the CategoryName column as an input parameter and uses the SCOPE_IDENTITY() function to retrieve the new value of the identity field, CategoryID, and return it in an output parameter. The RETURN statement uses the @@ROWCOUNT function to return the number of rows inserted.

CREATE PROCEDURE InsertCategory
   @CategoryName nchar(15),
   @Identity int OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT

The following example uses the InsertCategory stored procedure shown above as the source for the InsertCommand of the SqlDataAdapter. The @Identity output parameter and the return value will be reflected in the DataSet after the record has been inserted into the database when the Update method of the SqlDataAdapter is called.

Note

When using the OleDbDataAdapter, you must specify parameters with a ParameterDirection of ReturnValue before the other parameters.

' Assumes that connection represents a SqlConnection object.

Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT CategoryID, CategoryName FROM dbo.Categories", connection)

adapter.InsertCommand = New SqlCommand( _
  "InsertCategory" , connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = adapter.InsertCommand.Parameters.Add( _
  "@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue

adapter.InsertCommand.Parameters.Add( _
  "@CategoryName", SqlDbType.NChar, 15, "CategoryName")

parameter = adapter.InsertCommand.Parameters.Add( _
  "@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output

Dim categoriesDS As DataSet = New DataSet()
adapter.Fill(categoriesDS, "Categories")

Dim newRow As DataRow = categoriesDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
categoriesDS.Tables("Categories").Rows.Add(newRow)

adapter.Update(categoriesDS, "Categories")

Dim rowCount As Int32 = _
  CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
// Assumes that connection represents a SqlConnection object.

SqlDataAdapter adapter = new SqlDataAdapter(
  "SELECT CategoryID, CategoryName FROM dbo.Categories", connection);

adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = adapter.InsertCommand.Parameters.Add(
  "@RowCount", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

adapter.InsertCommand.Parameters.Add(
  "@CategoryName", SqlDbType.NChar, 15, "CategoryName");

parameter = adapter.InsertCommand.Parameters.Add(
  "@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;

DataSet categoriesDS = new DataSet();
adapter.Fill(categoriesDS, "Categories");

DataRow newRow = categoriesDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
categoriesDS.Tables["Categories"].Rows.Add(newRow);

adapter.Update(categoriesDS, "Categories");

Int32 rowCount = (Int32)adapter.InsertCommand.Parameters["@RowCount"].Value;

See Also

Concepts

Executing a Command

Other Resources

Working with DataAdapters