Modifying Data with Stored Procedures
Stored procedures can accept data as input parameters and can return data as output parameters, result sets, or return values. The sample below illustrates how ADO.NET sends and receives input parameters, output parameters, and return values. The example inserts a new record into a table where the primary key column is an identity column in a SQL Server database.
Note
If you are using SQL Server stored procedures to edit or delete data using a SqlDataAdapter, make sure that you do not use SET NOCOUNT ON in the stored procedure definition. This causes the rows affected count returned to be zero, which the DataAdapter interprets as a concurrency conflict. In this event, a DBConcurrencyException will be thrown.
Example
The sample uses the following stored procedure to insert a new category into the Northwind Categories table. The stored procedure 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 dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
The following code example uses the InsertCategory stored procedure shown above as the source for the InsertCommand of the SqlDataAdapter. The @Identity output parameter will be reflected in the DataSet after the record has been inserted into the database when the Update method of the SqlDataAdapter is called. The code also retrieves the return value.
Note
When using the OleDbDataAdapter, you must specify parameters with a ParameterDirection of ReturnValue before the other parameters.
using (SqlConnection connection = new(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter = new("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
{
// Create a SqlCommand to execute the stored procedure.
InsertCommand = new SqlCommand("InsertCategory", connection)
{
CommandType = CommandType.StoredProcedure
}
};
// Create a parameter for the ReturnValue.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
// Create an input parameter for the CategoryName.
// You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");
// Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;
// Create a DataTable and fill it.
DataTable categories = new();
adapter.Fill(categories);
// Add a new row.
DataRow categoryRow = categories.NewRow();
categoryRow["CategoryName"] = "New Beverages";
categories.Rows.Add(categoryRow);
// Update the database.
adapter.Update(categories);
// Retrieve the ReturnValue.
var rowCount = (int)adapter.InsertCommand.Parameters["@RowCount"].Value;
Console.WriteLine("ReturnValue: {0}", rowCount.ToString());
Console.WriteLine("All Rows:");
foreach (DataRow row in categories.Rows)
{
Console.WriteLine(" {0}: {1}", row[0], row[1]);
}
}
Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Module Class1
Sub Main()
Dim connectionString As String = _
GetConnectionString()
ReturnIdentity(connectionString)
' Console.ReadLine()
End Sub
Private Sub ReturnIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Create a parameter for the ReturnValue.
Dim parameter As SqlParameter = _
adapter.InsertCommand.Parameters.Add( _
"@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
' Create an input parameter for the CategoryName.
' You do not need to specify direction for input parameters.
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NChar, 15, "CategoryName")
' Create an output parameter for the new identity value.
parameter = adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
' Create a DataTable and fill it.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
' Retrieve the ReturnValue.
Dim rowCount As Int32 = _
CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
Console.WriteLine("All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
Private Function GetConnectionString() As String
Throw New NotImplementedException()
End Function
End Module