Memodifikasi Data dengan Prosedur tersimpan
Prosedur tersimpan bisa menerima data sebagai parameter input dan bisa mengembalikan data sebagai parameter output, tataan hasil, atau mengembalikan nilai. Sampel di bawah ini menggambarkan bagaimana ADO.NET mengirim dan menerima parameter input, parameter output, dan nilai pengembalian. Contoh menyisipkan rekaman baru ke dalam tabel di mana kolom kunci primer adalah kolom identitas dalam database SQL Server.
Catatan
Jika Anda menggunakan prosedur tersimpan SQL Server untuk mengedit atau menghapus data menggunakan SqlDataAdapter, pastikan bahwa Anda tidak menggunakan SET NOCOUNT ON dalam definisi prosedur tersimpan. Ini menyebabkan jumlah baris yang terpengaruh dikembalikan menjadi nol, yang ditafsirkan DataAdapter sebagai konflik konkurensi. Dalam hal ini, DBConcurrencyException akan dilemparkan.
Contoh
Sampel menggunakan prosedur tersimpan berikut untuk menyisipkan kategori baru ke dalam tabel Kategori Northwind. Prosedur tersimpan mengambil nilai di kolom CategoryName sebagai parameter input dan menggunakan fungsi SCOPE_IDENTITY() untuk mengambil nilai baru bidang identitas, CategoryID, dan mengembalikannya dalam parameter output. Pernyataan RETURN menggunakan fungsi @@ROWCOUNT untuk mengembalikan jumlah baris yang disisipkan.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Contoh kode berikut menggunakan prosedur tersimpan yang InsertCategory ditunjukkan di atas sebagai sumber untuk InsertCommand dari SqlDataAdapter. Parameter @Identity output akan tercermin dalam DataSet setelah rekaman dimasukkan ke dalam database ketika metode Update dari SqlDataAdapter dipanggil. Kode juga mengambil nilai yang dikembalikan.
Catatan
Saat menggunakan OleDbDataAdapter, Anda harus menentukan parameter dengan ParameterDirectionreturnValue sebelum parameter lainnya.
using (SqlConnection connection = new(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter = new("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
{
// Create a SqlCommand to execute the stored procedure.
InsertCommand = new SqlCommand("InsertCategory", connection)
{
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();
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.
var 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]);
}
}
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
Throw New NotImplementedException()
End Function
End Module