저장 프로시저로 데이터 수정(ADO.NET)
저장 프로시저는 입력 매개 변수로 데이터를 받아들이고 출력 매개 변수, 결과 집합 또는 반환 값으로 데이터를 반환할 수 있습니다. 다음 샘플에서는 ADO.NET에서 입력 매개 변수, 출력 매개 변수 및 반환 값을 보내고 받는 방법을 보여 줍니다. 이 예제에서는 기본 키 열이 SQL Server 데이터베이스의 ID 열인 테이블에 새 레코드를 삽입합니다.
.gif "참고") 참고 |
|---|
SQL Server 저장 프로시저를 통해 SqlDataAdapter로 데이터를 편집하거나 삭제하는 경우에는 저장 프로시저 정의에 SET NOCOUNT ON을 사용하면 안 됩니다.SET NOCOUNT ON을 사용하는 경우 반환되는 행 개수가 0이 되어 DataAdapter가 이를 동시성 충돌로 인식합니다.그 결과 DBConcurrencyException이 throw됩니다. |
예제
이 샘플에서는 다음 저장 프로시저를 사용하여 Northwind Categories 테이블에 새 범주를 삽입합니다. 저장 프로시저에서는 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 저장 프로시저를 SqlDataAdapter의 InsertCommand에 대한 소스로 사용합니다. SqlDataAdapter의 Update 메서드를 호출하면 데이터베이스에 레코드가 삽입된 후 @Identity 출력 매개 변수가 DataSet에 반영됩니다. 이 코드는 반환 값도 검색합니다.
| 참고 |
|---|
OleDbDataAdapter를 사용하는 경우 ReturnValue의 ParameterDirection을 사용하는 매개 변수를 다른 매개 변수 앞에 지정해야 합니다. |
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 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]);
}
}
}
}