Bagikan melalui

Mengonfigurasi parameter

  • Artikel

Berlaku untuk: .NET Framework .NET .NET Standard

Mengunduh ADO.NET

Objek perintah menggunakan parameter untuk meneruskan nilai ke SQL pernyataan atau prosedur yang disimpan, memberikan pemeriksaan dan validasi jenis. Tidak seperti teks perintah, input parameter diperlakukan sebagai nilai literal, bukan sebagai kode yang dapat dieksekusi. Perilaku ini membantu melindungi dari serangan "injeksi SQL", di mana penyerang menyisipkan perintah yang membahayakan keamanan di server ke dalam pernyataan SQL.

Perintah parameter juga dapat meningkatkan kinerja eksekusi kueri, karena membantu server database secara akurat mencocokkan perintah masuk dengan rencana kueri cache yang tepat. Untuk informasi selengkapnya, lihat Penembolokan Rencana Eksekusi dan Penggunaan Kembali dan Parameter dan Penggunaan Kembali Rencana Eksekusi. Selain manfaat keamanan dan kinerja, perintah parameter memberikan metode yang nyaman untuk mengatur nilai yang diteruskan ke sumber data.

Objek DbParameter dapat dibuat dengan menggunakan konstruktornya, atau dengan menambahkannya ke DbParameterCollection dengan memanggil metode Add dari koleksi DbParameterCollection. Metode Add akan menggunakan argumen konstruktor atau objek parameter yang ada sebagai masukan, bergantung pada penyedia data.

Menyediakan properti ParameterDirection

Saat menambahkan parameter, Anda harus menyediakan properti ParameterDirection untuk parameter selain parameter input. Tabel berikut ini memperlihatkan nilai ParameterDirection yang bisa Anda gunakan dengan enumerasi ParameterDirection.

Nama anggota Deskripsi
Input Parameter adalah parameter input. Ini adalah nilai default.
InputOutput Parameter dapat melakukan input dan output.
Output Parameter adalah parameter output.
ReturnValue Parameter mewakili nilai pengembalian dari operasi seperti prosedur yang disimpan, fungsi bawaan, atau fungsi yang ditentukan pengguna.

Bekerja dengan tempat penampung parameter

Sintaks untuk tempat penampung parameter bergantung pada sumber data. Penyedia Data Microsoft SqlClient untuk SQL Server menangani penamaan dan menentukan parameter dan tempat penampung parameter secara berbeda. Penyedia data SqlClient menggunakan parameter bernama dalam format @parametername.

Tentukan jenis data parameter

Jenis data parameter khusus untuk Penyedia Data Microsoft SqlClient untuk SQL Server. Menentukan jenis mengonversi nilai Parameter ke Penyedia Data Microsoft SqlClient untuk jenis SQL Server sebelum meneruskan nilai ke sumber data. Anda juga dapat menentukan jenis Parameter secara umum dengan menyetel properti DbType dari objek Parameter ke DbType tertentu.

Penyedia Data Microsoft SqlClient untuk jenis Parameter objek SQL Server disimpulkan dari jenis Value .NET Framework objek Parameter , atau dari DbType Parameter objek. Tabel berikut menunjukkan jenis Parameter yang disimpulkan berdasarkan objek yang diteruskan sebagai nilai Parameter atau DbType yang ditentukan.

Jenis .NET DbType SqlDbType
Boolean Boolean Bit
Byte Byte TinyInt
byte[] Binary VarBinary. Konversi implisit ini akan gagal jika array byte lebih besar dari ukuran VarBinarymaksimum , yaitu 8000 byte. Untuk array byte yang lebih besar dari 8000 byte, secara eksplisit mengatur SqlDbType.
Char Menyimpulkan SqlDbType karakter dari tidak didukung.
DateTime DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset di SQL Server 2008. Menyimpulkan SqlDbType dari DateTimeOffset tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008.
Decimal Decimal Decimal
Double Double Float
Single Single Real
Guid Guid UniqueIdentifier
Int16 Int16 SmallInt
Int32 Int32 Int
Int64 Int64 BigInt
Object Object Variant
String String NVarChar. Konversi implisit ini akan gagal jika string lebih besar dari NVarCharukuran maksimum , yaitu 4000 karakter. Untuk string yang lebih besar dari 4000 karakter, setel secara eksplisit SqlDbType.
TimeSpan Time Time di SQL Server 2008. Menyimpulkan SqlDbType dari TimeSpan tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008.
UInt16 UInt16 Menyimpulkan SqlDbType dari UInt16 tidak didukung.
UInt32 UInt32 Menyimpulkan SqlDbType dari UInt32 tidak didukung.
UInt64 UInt64 Menyimpulkan SqlDbType dari UInt64 tidak didukung.
AnsiString VarChar
AnsiStringFixedLength Char
Currency Money
Date Date di SQL Server 2008. Menyimpulkan SqlDbType dari Date tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008.
SByte Menyimpulkan SqlDbType dari SByte tidak didukung.
StringFixedLength NChar
Time Time di SQL Server 2008. Menyimpulkan SqlDbType dari Time tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008.
VarNumeric Menyimpulkan SqlDbType dari VarNumeric tidak didukung.
jenis yang ditentukan pengguna (objek dengan SqlUserDefinedAggregateAttribute SqlClient selalu mengembalikan Objek SqlDbType.Udt jika SqlUserDefinedTypeAttribute ada, jika tidak Variant

Catatan

Konversi dari desimal ke jenis lain mempersempit konversi yang membulatkan nilai desimal ke nilai integer terdekat menuju nol. Jika hasil konversi tidak dapat diwakili dalam jenis tujuan, maka OverflowException akan dilemparkan.

Catatan

Saat Anda mengirim nilai parameter nol ke server, Anda harus menentukan DBNull, bukan null (Nothing dalam Visual Basic). Nilai nol dalam sistem adalah objek kosong yang tidak memiliki nilai. DBNull digunakan untuk mewakili nilai null.

Mendapatkan informasi parameter

Parameter juga dapat berasal dari prosedur tersimpan menggunakan kelas DbCommandBuilder. Kelas SqlCommandBuilder menyediakan metode statis, DeriveParameters, yang secara otomatis mengisi kumpulan parameter objek perintah yang menggunakan informasi parameter dari prosedur tersimpan. DeriveParameters menimpa informasi parameter yang ada untuk perintah .

Catatan

Memperoleh informasi parameter dikenakan penalti kinerja karena memerlukan perjalanan pulang pergi tambahan ke sumber data untuk mengambil informasi. Jika informasi parameter diketahui pada waktu desain, Anda dapat meningkatkan kinerja aplikasi Anda dengan mengatur parameter secara eksplisit.

Untuk mendapatkan informasi selengkapnya, lihat Menghasilkan Perintah dengan CommandBuilders.

Menggunakan parameter dengan SqlCommand dan prosedur tersimpan

Prosedur tersimpan menawarkan banyak keuntungan dalam aplikasi berbasis data. Dengan menggunakan prosedur tersimpan, operasi database dapat dienkapsulasi dalam satu perintah, dioptimalkan untuk performa terbaik, dan ditingkatkan dengan keamanan ekstra. Meskipun prosedur tersimpan dapat dipanggil dengan meneruskan nama prosedur tersimpan diikuti dengan argumen parameter sebagai pernyataan SQL, dengan menggunakan kumpulan Parameters dari objek ADO.NET DbCommand memungkinkan Anda untuk secara lebih eksplisit mendefinisikan parameter prosedur tersimpan, dan untuk mengakses parameter keluaran dan mengembalikan nilai.

Catatan

Pernyataan berparameter dijalankan di server dengan menggunakan sp_executesql, yang memungkinkan penggunaan kembali rencana kueri. Kursor atau variabel lokal dalam sp_executesql batch tidak terlihat oleh batch yang memanggil sp_executesql. Perubahan dalam konteks database hanya berlangsung hingga akhir pernyataan sp_executesql. Untuk informasi selengkapnya, lihat sp_executesql (Transact-SQL).

Saat menggunakan parameter dengan SqlCommand untuk menjalankan prosedur tersimpan SQL Server, nama parameter yang ditambahkan ke koleksi Parameters harus cocok dengan nama penanda parameter dalam prosedur tersimpan. Penyedia Data Microsoft SqlClient untuk SQL Server tidak mendukung tempat penampung tanda tanya (?) untuk meneruskan parameter ke pernyataan SQL atau prosedur tersimpan. Ini memperlakukan parameter dalam prosedur yang disimpan sebagai parameter bernama dan mencari penanda parameter yang cocok. Misalnya, prosedur tersimpan CustOrderHist ditentukan dengan menggunakan parameter bernama @CustomerID. Ketika kode Anda menjalankan prosedur tersimpan, kode juga harus menggunakan parameter bernama @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Contoh

Contoh ini menunjukkan cara memanggil prosedur tersimpan SQL Server di database sampel Northwind. Nama prosedur tersimpan adalah dbo.SalesByCategory dan memiliki parameter input bernama @CategoryName dengan jenis data nvarchar(15). Kode membuat SqlConnection baru di dalam blok penggunaan sehingga koneksi dibuang saat prosedur berakhir. Objek SqlCommand dan SqlParameter dibuat, dan propertinya diatur. menjalankan SqlDataReader dan SqlCommand mengembalikan set hasil dari prosedur tersimpan, menampilkan output di jendela konsol.

Catatan

Alih-alih membuat objek SqlCommand dan SqlParameter kemudian mengatur properti dalam pernyataan terpisah, Anda dapat memilih untuk menggunakan salah satu konstruktor yang kelebihan beban untuk mengatur beberapa properti dalam satu pernyataan.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Lihat juga