Parameter dengan Nilai Tabel

Parameter berbentuk tabel menyediakan cara mudah untuk mengalihkan beberapa baris data dari aplikasi klien ke SQL Server tanpa memerlukan beberapa kali bolak-balik atau logika sisi server khusus untuk memproses data. Anda dapat menggunakan parameter bernilai tabel untuk merangkum baris data dalam aplikasi klien dan mengirim data ke server dalam satu perintah berparameter. Baris data masuk disimpan dalam variabel tabel yang kemudian dapat dioperasikan dengan menggunakan Transact-SQL.

Nilai kolom dalam parameter bernilai tabel dapat diakses menggunakan pernyataan Transact-SQL SELECT standar. Parameter bernilai tabel di ketik dengan kuat dan strukturnya secara otomatis divalidasi. Ukuran parameter bernilai tabel hanya dibatasi oleh memori server.

Nota

Anda tidak dapat mengembalikan data dalam parameter bernilai tabel. Parameter bernilai tabel hanya untuk input; kata kunci OUTPUT tidak didukung.

Untuk informasi selengkapnya tentang parameter dengan nilai tabel, lihat sumber daya berikut.

Sumber Daya	Deskripsi
Gunakan Parameter Bernilai Tabel (Mesin Database)	Menjelaskan cara membuat dan menggunakan parameter bernilai tabel.
User-Defined Jenis Tabel	Menjelaskan jenis tabel yang ditentukan pengguna yang digunakan untuk mendeklarasikan parameter bernilai tabel.

Meneruskan Beberapa Baris di Versi SQL Server Sebelumnya

Sebelum parameter bernilai tabel diperkenalkan ke SQL Server 2008, opsi untuk meneruskan beberapa baris data ke prosedur tersimpan atau perintah SQL berparameter dibatasi. Pengembang dapat memilih dari opsi berikut untuk meneruskan beberapa baris ke server:

• Gunakan serangkaian parameter individual untuk mewakili nilai dalam beberapa kolom dan baris data. Jumlah data yang dapat diteruskan dengan menggunakan metode ini dibatasi oleh jumlah parameter yang diizinkan. Prosedur SQL Server dapat memiliki, paling banyak, 2100 parameter. Logika sisi server diperlukan untuk merakit nilai individual ini ke dalam variabel tabel atau tabel sementara untuk diproses.

• Bundelkan beberapa nilai data ke dalam string yang dibatasi atau dokumen XML lalu teruskan nilai teks tersebut ke prosedur atau pernyataan. Ini mengharuskan prosedur atau pernyataan dalam menyertakan logika yang diperlukan untuk memvalidasi struktur data dan membatalkan pengelompokan nilai.

• Buat serangkaian pernyataan SQL individual untuk modifikasi data yang memengaruhi beberapa baris, seperti yang dibuat oleh metode Update dari SqlDataAdapter. Kirimkan perubahan satu per satu atau secara massal. Bahkan dalam batch, server menjalankan setiap statement secara terpisah.

• bcp Gunakan program utilitas atau SqlBulkCopy objek untuk memuat banyak baris data ke dalam tabel. Meskipun teknik ini sangat efisien, teknik ini tidak mendukung pemrosesan sisi server kecuali data dimuat ke dalam tabel sementara atau variabel tabel.

Membuat Jenis Parameter Table-Valued

Parameter bernilai tabel didasarkan pada struktur tabel bertipe ketat yang didefinisikan menggunakan pernyataan Transact-SQL CREATE TYPE. Anda harus membuat jenis tabel dan menentukan struktur di SQL Server sebelum Anda dapat menggunakan parameter bernilai tabel di aplikasi klien Anda. Untuk informasi selengkapnya tentang membuat jenis tabel, lihat User-Defined Tipe Tabel.

Pernyataan berikut membuat tipe tabel bernama CategoryTableType yang terdiri dari kolom CategoryID dan CategoryName:

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

Setelah membuat jenis tabel, Anda dapat mendeklarasikan parameter bernilai tabel berdasarkan jenis tersebut. Fragmen Transact-SQL berikut menunjukkan cara mendeklarasikan parameter bernilai tabel dalam definisi prosedur tersimpan. Perhatikan bahwa kata kunci READONLY diperlukan untuk mendeklarasikan parameter bernilai tabel.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Memodifikasi Data dengan Parameter Table-Valued (Transact-SQL)

Parameter bernilai tabel dapat digunakan dalam modifikasi data berbasis set yang memengaruhi beberapa baris dengan menjalankan satu pernyataan. Misalnya, Anda dapat memilih semua baris dalam parameter bernilai tabel dan menyisipkannya ke dalam tabel database, atau Anda bisa membuat pernyataan pembaruan dengan menggabungkan parameter bernilai tabel ke tabel yang ingin Anda perbarui.

Pernyataan Transact-SQL UPDATE berikut menunjukkan cara menggunakan parameter bernilai tabel dengan menggabungkannya ke tabel Kategori. Saat Anda menggunakan parameter bernilai tabel dengan JOIN dalam FROM klausul, Anda juga harus alias parameter tersebut, seperti yang ditunjukkan di sini, di mana parameter bernilai tabel diberi alias sebagai "ec":

UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;

Contoh Transact-SQL ini menunjukkan cara memilih baris dari parameter bernilai tabel untuk melakukan INSERT dalam satu operasi berbasis himpunan.

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Pembatasan Parameter Table-Valued

Ada beberapa batasan pada parameter bernilai tabel:

• Anda tidak dapat meneruskan parameter bernilai tabel ke fungsi yang ditentukan pengguna CLR.

• Parameter bernilai tabel hanya dapat diindeks untuk mendukung batasan UNIQUE atau batasan PRIMARY KEY. SQL Server tidak mempertahankan statistik pada parameter bernilai tabel.

• Parameter bernilai tabel di dalam kode Transact-SQL bersifat baca-saja. Anda tidak dapat memperbarui nilai kolom dalam baris parameter bernilai tabel dan Anda tidak dapat menyisipkan atau menghapus baris. Untuk mengubah data yang diteruskan ke prosedur tersimpan atau pernyataan berparameter dalam parameter bernilai tabel, Anda harus menyisipkan data ke dalam tabel sementara atau ke dalam variabel tabel.

• Anda tidak dapat menggunakan pernyataan SQL ALTER TABLE untuk mengubah desain parameter bernilai tabel.

Pengaturan Contoh SqlParameter

Pasang paket NuGet Microsoft.Data.SqlClient dan impor Microsoft.Data.SqlClient dan System.Data. Untuk urutan record sisi klien, gunakan Microsoft.Data.SqlClient.Server.SqlDataRecord dan Microsoft.Data.SqlClient.Server.SqlMetaData. Jangan ganti tipe sisi server SQL CLR dari Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient mendukung nilai parameter table-valued dari DataTable, DbDataReader, atau IEnumerable<T> dari objek SqlDataRecord. Atur TypeName milik parameter ke nama jenis tabel yang kompatibel yang sebelumnya dibuat di server. Fragmen berikut mengonfigurasi parameter untuk menyisipkan data.

Dalam contoh berikut, sebuah addedCategories variabel berisi sebuah DataTable. Untuk melihat bagaimana variabel diisi, lihat contoh di bagian berikutnya, Meneruskan Parameter Table-Valued ke Prosedur Tersimpan.

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

' Configure the command and parameter.
Dim insertCommand As New SqlCommand(sqlInsert, connection)
Dim tvpParam As SqlParameter = _
   insertCommand.Parameters.AddWithValue( _
  "@tvpNewCategories", addedCategories)
tvpParam.SqlDbType = SqlDbType.Structured
tvpParam.TypeName = "dbo.CategoryTableType"

Anda juga dapat menggunakan objek apa pun yang berasal dari DbDataReader untuk mengalirkan baris data ke parameter bernilai tabel, seperti yang ditunjukkan dalam fragmen ini:

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;

' Configure the SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  dataReader)
tvpParam.SqlDbType = SqlDbType.Structured

Meneruskan Parameter Table-Valued ke Prosedur Tersimpan

Contoh ini meneruskan data parameter bernilai tabel ke stored procedure. Kode memanggil GetChanges untuk mengekstrak baris yang ditambahkan ke DataTable baru. Ini membuat SqlCommand dengan CommandType diatur ke StoredProcedure. Ini menambahkan parameter dengan AddWithValue dan mengatur SqlDbType ke Structured. Akhirnya, kode memanggil ExecuteNonQuery.

// Assumes connection is an open SqlConnection object.
using (connection)
{
  // Create a DataTable with the modified rows.
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

  // Configure the SqlCommand and SqlParameter.
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
  insertCommand.CommandType = CommandType.StoredProcedure;
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
  tvpParam.SqlDbType = SqlDbType.Structured;

  // Execute the command.
  insertCommand.ExecuteNonQuery();
}

' Assumes connection is an open SqlConnection object.
Using connection
   '  Create a DataTable with the modified rows.
   Dim addedCategories As DataTable = _
     CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Configure the SqlCommand and SqlParameter.
   Dim insertCommand As New SqlCommand( _
     "usp_InsertCategories", connection)
   insertCommand.CommandType = CommandType.StoredProcedure
   Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
     "@tvpNewCategories", addedCategories)
   tvpParam.SqlDbType = SqlDbType.Structured

   '  Execute the command.
   insertCommand.ExecuteNonQuery()
End Using

Meneruskan Parameter Table-Valued ke Pernyataan SQL Berparameter

Contoh berikut menyisipkan data ke dalam dbo.Categories dengan pernyataan INSERT dan subkueri SELECT yang membaca parameter bernilai tabel. Untuk pernyataan SQL berparameter, atur TypeName ke nama jenis tabel yang kompatibel yang sebelumnya dibuat di server. Contoh ini menggunakan dbo.CategoryTableType.

Nota

Jika Anda memberikan nilai untuk kolom identitas dalam parameter bernilai tabel, Anda harus mengeluarkan SET IDENTITY_INSERT pernyataan untuk sesi tersebut.

// Assumes connection is an open SqlConnection.
using (connection)
{
  // Create a DataTable with the modified rows.
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

  // Define the INSERT-SELECT statement.
  string sqlInsert =
      "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
      + " SELECT nc.CategoryID, nc.CategoryName"
      + " FROM @tvpNewCategories AS nc;";

  // Configure the command and parameter.
  SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
  tvpParam.SqlDbType = SqlDbType.Structured;
  tvpParam.TypeName = "dbo.CategoryTableType";

  // Execute the command.
  insertCommand.ExecuteNonQuery();
}

' Assumes connection is an open SqlConnection.
Using connection
  ' Create a DataTable with the modified rows.
  Dim addedCategories As DataTable = _
    CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Define the INSERT-SELECT statement.
  Dim sqlInsert As String = _
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _
  & " SELECT nc.CategoryID, nc.CategoryName" _
  & " FROM @tvpNewCategories AS nc;"

  ' Configure the command and parameter.
  Dim insertCommand As New SqlCommand(sqlInsert, connection)
  Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
    "@tvpNewCategories", addedCategories)
  tvpParam.SqlDbType = SqlDbType.Structured
  tvpParam.TypeName = "dbo.CategoryTableType"

  ' Execute the query
  insertCommand.ExecuteNonQuery()
End Using

Mengalirkan Baris dengan DataReader

Anda dapat menggunakan objek apa pun yang berasal dari DbDataReader untuk mengirimkan baris data ke parameter bernilai tabel. Fragmen berikut mengambil data dari database Oracle dengan OracleCommand dan OracleDataReader. Kode mengonfigurasi SqlCommand untuk memanggil prosedur tersimpan dengan satu parameter input. Ini mengatur SqlDbType ke Structured dan meneruskan kumpulan hasil OracleDataReader melalui AddWithValue sebagai parameter bernilai tabel.

// Assumes connection is an open SqlConnection.
// Retrieve data from Oracle.
OracleCommand selectCommand = new OracleCommand(
   "Select CategoryID, CategoryName FROM Categories;",
   oracleConnection);
OracleDataReader oracleReader = selectCommand.ExecuteReader(
   CommandBehavior.CloseConnection);

 // Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand(
   "usp_InsertCategories", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam =
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", oracleReader);
 tvpParam.SqlDbType = SqlDbType.Structured;

 // Execute the command.
 insertCommand.ExecuteNonQuery();

' Assumes connection is an open SqlConnection.
' Retrieve data from Oracle.
Dim selectCommand As New OracleCommand( _
  "Select CategoryID, CategoryName FROM Categories;", _
  oracleConnection)
Dim oracleReader As OracleDataReader = _
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Configure SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  oracleReader)
tvpParam.SqlDbType = SqlDbType.Structured

' Execute the command.
insertCommand.ExecuteNonQuery()

Lihat juga

• Mengonfigurasi Parameter dan Jenis Data Parameter
• Perintah dan Parameter
• Parameter DataAdapter
• Operasi Data SQL Server di ADO.NET
• Gambaran Umum ADO.NET