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;