Jalankan beberapa perintah dengan SqlBatch

Gunakan SqlBatch untuk mengirim kumpulan SqlBatchCommand objek yang terurut melalui satu SqlConnection yang terbuka. Setiap perintah batch memiliki teks perintah sendiri, jenis perintah, parameter, perilaku perintah, dan hasil jumlah baris yang terpengaruh.

Gunakan SqlBatch ketika sebuah aplikasi memiliki perintah yang terpisah yang ingin dikirim bersama. Gunakan satu pernyataan berparameter SqlCommand ketika pekerjaan berupa satu pernyataan atau prosedur tersimpan. Gunakan SqlBulkCopy atau parameter bernilai tabel untuk set baris besar.

Contoh dalam artikel ini mengasumsikan bahwa sebuah SqlConnection terbuka bernama connection menargetkan database contoh AdventureWorks.

Contoh C# membutuhkan using Microsoft.Data.SqlClient;. Contoh yang menggunakan SqlDbType atau CommandBehavior juga memerlukan using System.Data;. Contoh yang menggunakan DbDataReader memerlukan using System.Data.Common;.

Dukungan versi dan platform

Contoh-contoh tersebut menargetkan Microsoft.Data.SqlClient 7.1.0. Pilih versi paket dan kerangka kerja target yang didukung oleh siklus dukungan Microsoft.Data.SqlClient untuk aplikasi Anda.

SqlBatchberasal dari DbBatch .NET modern. Microsoft.Data.SqlClient juga mengekspos SqlBatch dalam aset paketnya .NET Framework dan .NET Standard 2.0. Surface API publik dapat berbeda menurut kerangka kerja target, jadi kompilasi aplikasi untuk setiap target yang Anda distribusikan.

Buat batch

Buat batch untuk koneksi terbuka, lalu tambahkan perintah dalam urutan eksekusi:

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT TOP (3) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;
    """));
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT TOP (3) SalesOrderID, OrderDate
    FROM Sales.SalesOrderHeader
    ORDER BY SalesOrderID;
    """));

using DbDataReader reader = batch.ExecuteReader();
do
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader.GetValue(0)} | {reader.GetValue(1)}");
    }
}
while (reader.NextResult());

CommandType.Text adalah default. Atur perintah batch menjadi CommandType.StoredProcedure untuk prosedur tersimpan. Mengatur ArgumentOutOfRangeException menyebabkan CommandType.TableDirect.

Tambahkan parameter ke perintah batch

Parameter milik individu SqlBatchCommand. Nama placeholder yang sama dalam dua perintah batch tidak berbagi satu nilai:

using var batch = new SqlBatch(connection);

var products = new SqlBatchCommand("""
    SELECT ProductID, Name
    FROM Production.Product
    WHERE ProductSubcategoryID = @id
    ORDER BY ProductID;
    """);
products.Parameters.Add("@id", SqlDbType.Int).Value = 2;
batch.BatchCommands.Add(products);

var orders = new SqlBatchCommand("""
    SELECT SalesOrderID, OrderDate
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID = @id
    ORDER BY SalesOrderID;
    """);
orders.Parameters.Add("@id", SqlDbType.Int).Value = 279;
batch.BatchCommands.Add(orders);

using DbDataReader reader = batch.ExecuteReader();

Atur tipe SQL setiap parameter serta ukuran, presisi, dan skala yang berlaku dari kontrak basis data. Untuk informasi lebih lanjut, lihat Konfigurasikan parameter.

Jalankan perintah yang mengubah data

Gunakan ExecuteNonQuery atau ExecuteNonQueryAsync saat batch mengubah data dan Anda tidak perlu baris hasil. Nilai yang dikembalikan adalah total baris yang terpengaruh ketika jumlah baris tersedia. Setelah dieksekusi, setiap RecordsAffected milik perintah melaporkan hitungannya sendiri.

Contoh ini membatalkan perubahannya:

using SqlTransaction transaction = connection.BeginTransaction();
using var batch = new SqlBatch(connection, transaction);

var firstUpdate = new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """);
firstUpdate.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
firstUpdate.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43659;
batch.BatchCommands.Add(firstUpdate);

var secondUpdate = new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """);
secondUpdate.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
secondUpdate.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43660;
batch.BatchCommands.Add(secondUpdate);

int totalRows = batch.ExecuteNonQuery();
Console.WriteLine($"Total rows: {totalRows}");

foreach (SqlBatchCommand command in batch.BatchCommands)
{
    Console.WriteLine(command.RecordsAffected);
}

transaction.Rollback();

Baca beberapa hasil

Gunakan ExecuteReader atau ExecuteReaderAsync ketika perintah apa pun mengembalikan baris. reader menyediakan hasil dalam urutan perintah batch. Hubungi NextResult atau NextResultAsync sampai mengembalikan false.

Sebuah perintah dapat tidak menghasilkan set baris apa pun, satu set baris, atau beberapa set baris. Jangan berasumsi bahwa satu NextResult panggilan dipetakan ke satu SqlBatchCommand.

Gunakan fungsi perintah

Teruskan CommandBehavior tingkat batch ke ExecuteReader atau ExecuteReaderAsync ketika pembaca membutuhkan akses berurutan atau harus menutup koneksi:

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT DocumentSummary
    FROM Production.Document
    WHERE DocumentSummary IS NOT NULL;
    """));

using DbDataReader reader =
    batch.ExecuteReader(CommandBehavior.SequentialAccess);

char[] buffer = new char[4096];
while (reader.Read())
{
    using TextReader summary = reader.GetTextReader(0);
    int count;
    while ((count = summary.Read(buffer, 0, buffer.Length)) > 0)
    {
        Console.Out.Write(buffer, 0, count);
    }
    Console.WriteLine();
}

Kelebihan beban fungsi tingkat batch hanya menerima CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection, dan kombinasi dari nilai-nilai tersebut. Nilai valid lainnya, seperti SingleRow, KeyInfo, dan ArgumentOutOfRangeException, melempar SchemaOnly saat diteruskan ke overload tingkat batch. Overload perilaku tingkat batch tersedia mulai Microsoft.Data.SqlClient 7.1.

Atur CommandBehavior pada perintah individual untuk opsi seperti SingleRow, SchemaOnly, dan KeyInfo. Gunakan overload tingkat batch untuk SequentialAccess dan CloseConnection.

Gunakan sebuah transaksi

SqlBatch tidak menjalankan perintahnya secara atomik. Gunakan transaksi eksplisit ketika semua perintah harus melakukan commit atau roll back bersama-sama. Contoh ini melakukan rollback sehingga AdventureWorks tetap tidak berubah:

using SqlTransaction transaction = connection.BeginTransaction();
using var batch = new SqlBatch(connection, transaction);

batch.BatchCommands.Add(new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = 2
    WHERE SalesOrderID = 43659;
    """));
batch.BatchCommands.Add(new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = 2
    WHERE SalesOrderID = 43660;
    """));

batch.ExecuteNonQuery();
transaction.Rollback();

Gunakan Commit saja, bukan Rollback hanya ketika aplikasi menerima batch lengkap. Tugaskan transaksi sebelum menjalankannya. Transaksi dan batch harus menggunakan koneksi yang sama. SqlClient tidak mengambil alih metode transaksi asinkron, jadi BeginTransactionAsync, CommitAsync, dan RollbackAsync selesai secara sinkron. Untuk informasi lebih lanjut, lihat Gunakan metode transaksi SQL berbasis tugas.

Tangani timeout, pembatalan, dan kesalahan

Atur Timeout dalam detik untuk batch tersebut. Batch menggunakan pengaturan Command Timeout milik koneksi saat Anda mengonfigurasi koneksi. Jika tidak, default-nya adalah 30 detik. Nilai dari 0 tidak memiliki batas waktu, dan nilai negatif menghasilkan ArgumentException. Teruskan token pembatalan ke metode eksekusi asinkron ketika pemanggil perlu berhenti menunggu, atau memanggil Cancel. Pembatalan diupayakan semaksimal mungkin. Operasi mungkin selesai sebelum pembatalan berlaku.

Ketika eksekusi gagal, tangkap SqlException dan periksa setiap item dalam koleksi Errors-nya. BatchCommand mengidentifikasi SqlBatchCommand yang terkait ketika informasi tersebut tersedia:

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand(
    "SELECT TOP (1) ProductID FROM Production.Product;"));
batch.BatchCommands.Add(new SqlBatchCommand("SELECT 1 / 0;"));

try
{
    using DbDataReader reader = batch.ExecuteReader();
    do
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetValue(0));
        }
    }
    while (reader.NextResult());
}
catch (SqlException exception)
{
    Console.WriteLine(
        exception.BatchCommand is null
            ? "The batch failed."
            : $"Failed command: {exception.BatchCommand.CommandText}");
}

Eksekusi pembaca data bersifat bertahap. Kesalahan dari perintah batch berikutnya mungkin muncul saat Read atau NextResult maju ke perintah tersebut, jadi proseslah pembaca di dalam try blok.

Jangan mencatat nilai parameter yang berisi kredensial, token, atau data sensitif. Untuk kolom kesalahan dan pesan informasi, lihat Menangani kesalahan dan pesan.

Konten terkait

  • Terakhir diperbarui pada