使用存储过程修改数据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. 下面的示例演示 ADO.NET 如何发送和接收输入参数、输出参数及返回值。The sample below illustrates how ADO.NET sends and receives input parameters, output parameters, and return values. 该示例将一条新记录插入到一个表中,该表中的主键列为 SQL Server 数据库中的标识列。The example inserts a new record into a table where the primary key column is an identity column in a SQL Server database.

备注

如果您要通过 SQL Server 存储过程使用 SqlDataAdapter 来编辑或删除数据,请确保不要在存储过程定义中使用 SET NOCOUNT ON。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. 这将使返回的受影响的行数为零,DataAdapter 会将其解释为并发冲突。This causes the rows affected count returned to be zero, which the DataAdapter interprets as a concurrency conflict. 在这种情况下,将引发 DBConcurrencyExceptionIn this event, a DBConcurrencyException will be thrown.

示例Example

该示例使用以下存储过程将新类别插入Northwind category 表。The sample uses the following stored procedure to insert a new category into the Northwind Categories table. 该存储过程采用 "字段名称" 列中的值作为输入参数, 并使用 SCOPE_IDENTITY () 函数检索标识字段的新值 "类别 id", 并在输出参数中将其返回。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. Return 语句使用 @@ROWCOUNT函数返回插入的行数。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  

下面的代码示例使用上面显示的 InsertCategory 存储过程作为 InsertCommandSqlDataAdapter 的来源。The following code example uses the InsertCategory stored procedure shown above as the source for the InsertCommand of the SqlDataAdapter. 如果在将记录插入到数据库后调用 @IdentityDataSet 方法,Update 中将会反映出 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.

备注

使用OleDbDataAdapter时, 必须在其他参数之前指定ParameterDirection带有ReturnValue的参数。When using the OleDbDataAdapter, you must specify parameters with a ParameterDirection of ReturnValue before the other parameters.

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        ReturnIdentity(connectionString);
        // Console.ReadLine();

    }

    private static void ReturnIdentity(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a SqlDataAdapter based on a SELECT query.
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM dbo.Categories", connection);

            // Create a SqlCommand to execute the stored procedure.
            adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
            adapter.InsertCommand.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 DataTable();
            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.
            Int32 rowCount = (Int32)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]);
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        return "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
    }
}
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
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        Return "Data Source=(local);Initial Catalog=Northwind;" _
           & "Integrated Security=true;"
    End Function

End Module

请参阅See also