ストアド プロシージャでのデータの変更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 データベースの ID 列であるテーブルに新しいレコードを挿入しています。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. この場合、DBConcurrencyException がスローされます。In 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. このストアドプロシージャは、 [区分名] 列の値を入力パラメーターとして受け取り、SCOPE_IDENTITY () 関数を使用して 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. @Identity 出力パラメーターは、DataSetUpdate メソッドが呼び出され、データベースにレコードが挿入された後で 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も前に戻りがあるパラメーターを指定する必要があります。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