使用存储过程修改数据Modify data with stored procedures

适用于: .NET Framework .NET Core .NET Standard

下载 ADO.NET

存储过程可以接受数据作为输入参数并可以返回数据作为输出参数、结果集或返回值。Stored procedures can accept data as input parameters and can return data as output parameters, result sets, or return values. 下面的示例演示用于 SQL Server 的 Microsoft SqlClient 数据提供程序如何发送和接收输入参数、输出参数和返回值。The sample below illustrates how Microsoft SqlClient Data Provider for SQL Server 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.

备注

如果使用存储过程来编辑或删除使用 SqlDataAdapter 的数据,请确保在存储过程定义中不使用 SET NOCOUNT ON。If you are using 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“类别”表。The sample uses the following stored procedure to insert a new category into the Northwind Categories table. 存储过程获取“CategoryName”列中的值作为输入参数,并使用 SCOPE_IDENTITY() 函数来检索标识字段“CategoryID”的新值,然后在输出参数中返回该新值。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.

using System;
using System.Data;
using Microsoft.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.
            Int 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]);
            }
        }
    }

    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";
    }
}

请参阅See also