Mengonfigurasi parameter
Berlaku untuk:
.NET Framework .NET .NET Standard
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 oleh argumen parameter sebagai pernyataan SQL, dengan menggunakan Parameters kumpulan objek ADO.NET DbCommand memungkinkan Anda untuk secara lebih eksplisit menentukan parameter prosedur tersimpan, dan untuk mengakses parameter output 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();
}
}
}