Input and Output 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 using a common scenario of inserting a new record into a table where the primary key column is an autonumber field. The sample uses an output parameter to return the @@Identity of the autonumber field and the DataAdapter binds it to the column of the DataTable so that the DataSet will reflect the resulting primary key value.
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, returns the value of the autonumber identity field, CategoryID, from @@Identity as an output parameter, and has a return value of the rows affected.
CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNT
The following example uses the InsertCategory stored procedure as the source for the InsertCommand of the DataAdapter. By specifying the CategoryID column as the SourceColumn for the @Identity output parameter, the resulting autonumber value will be reflected in the DataSet after the record has been inserted into the database when the Update method of the DataAdapter is called.
For the OleDbDataAdapter, parameters with a ParameterDirection of ReturnValue must be specified before the other parameters.
SqlClient
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=northwind")
Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)
catDA.InsertCommand = New SqlCommand("InsertCategory" , nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure
Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue
catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")
myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output
Dim catDS As DataSet = New DataSet()
catDA.Fill(catDS, "Categories")
Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)
catDA.Update(catDS, "Categories")
Dim rowCount As Int32 = CInt(catDA.InsertCommand.Parameters("@RowCount").Value)
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;" +
"Initial Catalog=northwind");
SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);
catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue;
catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");
myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;
DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");
DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);
catDA.Update(catDS, "Categories");
Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;
OleDb
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", _
nwindConn)
catDA.InsertCommand = New OleDbCommand("InsertCategory" , nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure
Dim myParm As OleDbParameter = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer)
myParm.Direction = ParameterDirection.ReturnValue
catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName")
myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output
Dim catDS As DataSet = New DataSet()
catDA.Fill(catDS, "Categories")
Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)
catDA.Update(catDS, "Categories")
Dim rowCount As Int32 = CInt(catDA.InsertCommand.Parameters("@RowCount").Value)
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=northwind");
OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);
catDA.InsertCommand = new OleDbCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
OleDbParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", OleDbType.Integer);
myParm.Direction = ParameterDirection.ReturnValue;
catDA.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15, "CategoryName");
myParm = catDA.InsertCommand.Parameters.Add("@Identity", OleDbType.Integer, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;
DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");
DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);
catDA.Update(catDS, "Categories");
Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;