저장 프로시저로 데이터 수정

  • 아티클

적용 대상: .NET Framework .NET .NET Standard

ADO.NET 다운로드

저장 프로시저는 입력 매개 변수로 데이터를 받아들이고 출력 매개 변수, 결과 집합 또는 반환 값으로 데이터를 반환할 수 있습니다. 다음 샘플에서는 SQL Server용 Microsoft SqlClient 데이터 공급자에서 입력 매개 변수, 출력 매개 변수 및 반환 값을 보내고 받는 방법을 보여 줍니다. 이 예제에서는 기본 키 열이 ID 열인 테이블에 새 레코드를 삽입합니다.

참고 항목

SqlDataAdapter를 사용하여 데이터를 편집하거나 삭제하기 위해 저장 프로시저를 사용하는 경우 저장 프로시저 정의에서 SET NOCOUNT ON을 사용하지 않아야 합니다. 이로 인해 영향을 받은 행 수가 0으로 반환되고, DataAdapter는 이를 동시성 충돌로 해석합니다. 이 이벤트에서는 DBConcurrencyException이 throw됩니다.

예시

이 샘플에서는 다음 저장 프로시저를 사용하여 NorthwindCategories 테이블에 새 범주를 삽입합니다. 저장 프로시저에서는 CategoryName 열의 값을 입력 매개 변수로 받아 SCOPE_IDENTITY() 함수를 사용하여 ID 필드인 CategoryID의 새 값을 검색한 다음 이를 출력 매개 변수로 반환합니다. RETURN 문은 @@ROWCOUNT 함수를 사용하여 삽입된 행의 수를 반환합니다.

CREATE PROCEDURE dbo.InsertCategory  
  @CategoryName nvarchar(15),  
  @Identity int OUT  
AS  
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)  
SET @Identity = SCOPE_IDENTITY()  
RETURN @@ROWCOUNT

다음 코드 예제에서는 위의 InsertCategory 저장 프로시저를 InsertCommand의 SqlDataAdapter에 대한 소스로 사용합니다. @Identity의 DataSet 메서드를 호출하면 데이터베이스에 레코드가 삽입된 후 Update 출력 매개 변수가 SqlDataAdapter에 반영됩니다. 이 코드는 반환 값도 검색합니다.

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

참고 항목