Bagikan melalui

Parameter bernilai tabel

  • Artikel

Mengunduh ADO.NET

Parameter bernilai tabel menyediakan cara mudah untuk melakukan marshal beberapa baris data dari aplikasi klien ke SQL Server. Mereka tidak memerlukan beberapa perjalanan pulang pergi 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 yang masuk disimpan dalam variabel tabel yang kemudian dapat dioperasikan dengan menggunakan Transact-SQL.

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

Catatan

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 bernilai tabel, lihat sumber daya berikut.

Sumber daya Deskripsi
Gunakan Parameter Bernilai Tabel (Mesin Database) Menjelaskan cara membuat dan menggunakan parameter bernilai tabel.
Membuat jenis tabel yang ditentukan pengguna Menjelaskan jenis tabel yang ditentukan pengguna yang digunakan untuk mendeklarasikan parameter bernilai tabel.
Jenis Tabel yang Ditentukan Pengguna 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, 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 individu 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 individu ini menjadi variabel tabel atau tabel sementara untuk diproses.

  • Bundel beberapa nilai data ke dalam string atau dokumen XML yang dibatasi, lalu berikan nilai teks tersebut ke prosedur atau pernyataan. Metode ini memerlukan prosedur atau pernyataan untuk menyertakan logika untuk memvalidasi struktur data dan membatalkanbundling nilai.

  • Buat serangkaian pernyataan SQL individu untuk modifikasi data yang memengaruhi beberapa baris, seperti yang dibuat dengan memanggil metode Update dari SqlDataAdapter. Perubahan dapat dikirimkan ke server secara individu atau dalam grup. Namun, bahkan ketika dikirimkan dalam kumpulan yang berisi banyak pernyataan, setiap pernyataan dijalankan secara terpisah di server.

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

Membuat jenis parameter bernilai tabel

Parameter bernilai tabel didasarkan pada struktur tabel yang diketik dengan kuat yang ditentukan dengan menggunakan pernyataan CREATE TYPE Transact-SQL. 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 Menggunakan Parameter Bernilai Tabel (Mesin Database).

Pernyataan berikut membuat jenis 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. Kata kunci READONLY diperlukan untuk mendeklarasikan parameter bernilai tabel.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Memodifikasi data dengan parameter bernilai tabel (Transact-SQL)

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

Pernyataan UPDATE Transact-SQL berikut menunjukkan cara menggunakan parameter bernilai tabel dengan menggabungkannya ke tabel Kategori. Saat Anda menggunakan parameter bernilai tabel dengan JOIN dalam klausa FROM, Anda juga harus membuat alias, seperti yang ditunjukkan di sini, di mana parameter bernilai tabel memiliki alias "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 set.

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

Batasan parameter bernilai tabel

Ada beberapa batasan untuk 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 PRIMARY KEY. SQL Server tidak mempertahankan statistik pada parameter bernilai tabel.

  • Parameter bernilai tabel bersifat baca-saja dalam kode Transact-SQL. 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 ALTER TABLE untuk mengubah desain parameter bernilai tabel.

Mengonfigurasi contoh SqlParameter

Microsoft.Data.SqlClient mendukung pengisian parameter bernilai tabel dari DataTable, , DbDataReaderatau IEnumerable<T> \ SqlDataRecord objek. Tentukan nama jenis untuk parameter bernilai tabel dengan menggunakan TypeName properti dari SqlParameter. TypeName harus cocok dengan nama jenis kompatibel yang dibuat sebelumnya di server. Fragmen kode berikut menunjukkan cara mengonfigurasi SqlParameter untuk menyisipkan data.

Dalam contoh berikut, variabel addedCategories berisi DataTable. Untuk melihat bagaimana variabel diisi, lihat contoh di bagian berikutnya, Meneruskan Parameter Bernilai Tabel 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";

Anda juga dapat menggunakan objek apa pun yang diturunkan 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;

Meneruskan parameter bernilai tabel ke prosedur tersimpan

Contoh ini menunjukkan cara meneruskan data parameter bernilai tabel ke prosedur tersimpan. Kode mengekstrak baris yang ditambahkan menjadi DataTable baru dengan menggunakan metode GetChanges. Kode kemudian mendefinisikan SqlCommand, mengatur properti CommandType ke StoredProcedure. SqlParameter diisi dengan menggunakan metode AddWithValue dan SqlDbType diatur ke Structured. SqlCommand kemudian dijalankan dengan menggunakan metode 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();
}

Meneruskan parameter bernilai tabel ke pernyataan SQL berparameter

Contoh berikut menunjukkan cara menyisipkan data ke dalam tabel dbo.Categories menggunakan pernyataan INSERT dengan subkueri SELECT yang memiliki parameter bernilai tabel sebagai sumber data. Saat meneruskan parameter bernilai tabel ke pernyataan SQL berparameter, Anda harus menentukan nama jenis untuk parameter bernilai tabel dengan menggunakan properti TypeName baru dari SqlParameter. TypeName ini harus cocok dengan nama jenis kompatibel yang dibuat sebelumnya di server. Kode dalam contoh ini menggunakan properti TypeName untuk mereferensikan struktur jenis yang ditentukan dalam dbo.CategoryTableType.

Catatan

Jika Anda memberikan nilai untuk kolom identitas dalam parameter bernilai tabel, Anda harus mengeluarkan pernyataan SET IDENTITY_INSERT 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();
}

Streaming baris dengan DataReader

Anda juga dapat menggunakan objek apa pun yang diturunkan dari DbDataReader untuk mengalirkan baris data ke parameter bernilai tabel. Fragmen kode berikut menunjukkan pengambilan data dari database Oracle dengan menggunakan OracleCommand dan OracleDataReader. Kode kemudian mengonfigurasi SqlCommand untuk menjalankan prosedur tersimpan dengan satu parameter input. Properti SqlDbType dari SqlParameter diatur ke Structured. AddWithValue meneruskan hasil OracleDataReader yang diatur ke prosedur tersimpan 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();

Langkah berikutnya