Pengaturan parameter dan tipe data parameter

Objek perintah menggunakan parameter untuk meneruskan nilai ke pernyataan SQL atau prosedur tersimpan, menyediakan pemeriksaan dan validasi tipe. Tidak seperti teks perintah, input parameter diperlakukan sebagai nilai literal, bukan sebagai kode yang dapat dieksekusi. Ini membantu menjaga dari serangan "injeksi SQL", di mana penyerang memasukkan 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 Penyimpanan Sementara Rencana Eksekusi dan Penggunaan Ulang dan Parameter dan Penggunaan Ulang 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 tersebut adalah parameter input. Ini adalah 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 .NET Framework menangani penamaan dan menentukan parameter dan tempat penampung parameter secara berbeda. Sintaks ini disesuaikan ke sumber data tertentu, seperti yang dijelaskan dalam tabel berikut.

Penyedia data	Sintaks penamaan untuk parameter
System.Data.SqlClient	Menggunakan parameter bernama dalam format @parametername.
System.Data.OleDb	Menggunakan penanda parameter posisi yang ditunjukkan oleh tanda tanya (?).
System.Data.Odbc	Menggunakan penanda parameter posisi yang ditunjukkan oleh tanda tanya (?).
System.Data.OracleClient	Menggunakan parameter bernama dalam format :parmname (atau parmname).

Menentukan jenis data parameter

Jenis data parameter khusus untuk penyedia data .NET Framework. Menentukan jenis mengonversi nilai Parameter ke jenis penyedia data .NET Framework sebelum meneruskan nilai ke sumber data. Anda juga dapat menentukan jenis Parameter secara umum dengan menyetel properti DbType dari objek Parameter ke DbType tertentu.

Jenis penyedia data .NET Framework dari objek Parameter disimpulkan dari jenis .NET Framework dari Value objek Parameter, atau dari DbType objek Parameter. Tabel berikut menunjukkan jenis Parameter yang disimpulkan berdasarkan objek yang diteruskan sebagai nilai Parameter atau DbType yang ditentukan.

Jenis .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Boolean (tipe data yang hanya memiliki dua nilai: true atau false)	Sedikit	Boolean (tipe data yang hanya memiliki dua nilai: true atau false)	Sedikit	Bita
Byte	Bita	TinyInt	UnsignedTinyInt	TinyInt	Bita
(byte[])	Biner	VarBinary. Konversi implisit ini akan gagal jika larik byte lebih besar dari ukuran maksimum VarBinary, yaitu 8000 byte. Untuk larik byte yang lebih besar dari 8000 byte, setel SqlDbType secara eksplisit.	VarBinary	Biner	Mentah
Char		Menyimpulkan SqlDbType dari char tidak didukung.	Karakter	Karakter	Bita
DateTime	TanggalWaktu	TanggalWaktu	DBTimeStamp (Penanda Waktu Basis Data)	TanggalWaktu	TanggalWaktu
DateTimeOffset	Pengaturan Waktu & Tanggal	DateTimeOffset pada SQL Server 2008. Menyimpulkan SqlDbType dari DateTimeOffset tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008.			TanggalWaktu
Decimal	Desimal	Desimal	Desimal	Numerik	Nomor
Double	Ganda	Mengapung	Ganda	Ganda	Ganda
Single	Tunggal	Nyata	Tunggal	Nyata	Mengapung
Guid	Panduan	UniqueIdentifier	Panduan	UniqueIdentifier	Mentah
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Integer	Integer	Integer	Int32
Int64	Int64	Bigint	Bigint	Bigint	Nomor
Object	Objek	Varian	Varian	Menarik kesimpulan OdbcType dari Object tidak didukung.	Gumpalan
String	string	NVarChar. Konversi implisit ini akan gagal jika string lebih besar dari ukuran maksimum NVarChar, yaitu 4000 karakter. Untuk string yang lebih besar dari 4000 karakter, setel secara eksplisit SqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Waktu	Waktu di SQL Server 2008. Menyimpulkan SqlDbType dari TimeSpan tidak didukung di versi SQL Server sebelum SQL Server 2008.	DBTime	Waktu	TanggalWaktu
UInt16	UInt16	Menyimpulkan sebuah SqlDbType dari UInt16 tidak didukung.	UnsignedSmallInt	Integer	UInt16
UInt32	UInt32	Menginferensi SqlDbType dari UInt32 tidak didukung.	Integer Tidak Bertanda	Bigint	UInt32
UInt64	UInt64	Menyimpulkan SqlDbType dari UInt64 tidak didukung.	UnsignedBigInt	Numerik	Nomor
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Karakter	Karakter	Karakter	Karakter
	Mata Uang	Uang	Mata Uang	Menyimpulkan OdbcType dari Currency tidak didukung.	Nomor
	Tanggal	Tanggal di SQL Server 2008. Menarik kesimpulan SqlDbType dari Tanggal tidak didukung di versi SQL Server sebelum SQL Server 2008.	DBDate	Tanggal	TanggalWaktu
	SByte	Menyimpulkan SqlDbType dari SByte tidak didukung.	TinyInt	Menyimpulkan OdbcType dari SByte tidak didukung.	SByte
	StringPanjangTetap	NChar	WChar	NChar	NChar
	Waktu	Waktu di SQL Server 2008. Menyimpulkan SqlDbType dari Waktu tidak didukung di versi SQL Server sebelum SQL Server 2008.	DBTime	Waktu	TanggalWaktu
	VarNumeric	Menyimpulkan SqlDbType dari VarNumeric tidak didukung.	VarNumeric	Menyimpulkan OdbcType dari VarNumeric tidak didukung.	Nomor
jenis yang ditentukan pengguna (objek dengan SqlUserDefinedAggregateAttribute	Objek atau String, tergantung penyedia (SqlClient selalu mengembalikan Objek, Odbc selalu mengembalikan String, dan penyedia data terkelola OleDb dapat melihat salah satu	SqlDbType.Udt jika SqlUserDefinedTypeAttribute ada; jika tidak, Varian	OleDbType.VarWChar (jika nilai adalah null) sebaliknya OleDbType.Variant.	OdbcType.NVarChar	tidak didukung

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. Untuk informasi selengkapnya tentang null database, lihat Menangani Nilai Null.

Memperoleh informasi parameter

Parameter juga dapat berasal dari prosedur tersimpan menggunakan kelas DbCommandBuilder. Baik kelas SqlCommandBuilder dan OleDbCommandBuilder menyediakan metode statis, DeriveParameters, yang secara otomatis mengisi kumpulan parameter dari objek perintah yang menggunakan informasi parameter dari prosedur tersimpan. Perhatikan bahwa DeriveParameters menggantikan semua 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 simpan

Prosedur tersimpan menawarkan banyak keuntungan dalam aplikasi berbasis data. Dengan menggunakan prosedur yang tersimpan, operasi database dapat dienkapsulasi dalam satu perintah, dioptimalkan untuk kinerja terbaik, dan ditingkatkan dengan keamanan tambahan. 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. .NET Framework Data Provider untuk SQL Server tidak mendukung karakter tanda tanya (?) sebagai penanda untuk meneruskan parameter ke pernyataan SQL atau prosedur yang disimpan. Ini memperlakukan parameter dalam prosedur tersimpan sebagai parameter bernama dan mencari penanda parameter yang sesuai. 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. Sebuah SqlDataReader mengeksekusi SqlCommand dan 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(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new()
        {
            Connection = connection,
            CommandText = "SalesByCategory",
            CommandType = CommandType.StoredProcedure
        };

        // Add the input parameter and set its properties.
        SqlParameter parameter = new()
        {
            ParameterName = "@CategoryName",
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input,
            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($"{reader[0]}: {reader[1]:C}");
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As 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 reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                Do While reader.Read()
                    Console.WriteLine("{0}: {1:C}", _
                      reader(0), reader(1))
                Loop
            Else
                Console.WriteLine("No rows returned.")
            End If
        End Using
    End Using
End Sub

Menggunakan parameter dengan OleDbCommand atau OdbcCommand

Saat menggunakan parameter dengan OleDbCommand atau OdbcCommand, urutan parameter yang ditambahkan ke koleksi Parameters harus sesuai dengan urutan parameter yang ditentukan dalam prosedur tersimpan Anda. .NET Framework Data Provider untuk OLE DB dan .NET Framework Data Provider untuk ODBC memperlakukan parameter dalam prosedur tersimpan sebagai placeholder dan menerapkan nilai parameter secara berurutan. Selain itu, parameter nilai yang dikembalikan harus menjadi parameter pertama yang ditambahkan ke koleksi Parameters.

.NET Framework Data Provider untuk OLE DB dan .NET Framework Data Provider untuk ODBC tidak mendukung parameter bernama untuk meneruskan parameter ke pernyataan SQL atau prosedur yang disimpan. Dalam hal ini, Anda harus menggunakan tempat penampung tanda tanya (?), seperti pada contoh berikut.

SELECT * FROM Customers WHERE CustomerID = ?

Akibatnya, urutan objek Parameter yang ditambahkan ke koleksi Parameters harus langsung sesuai dengan posisi ? pengganti sementara untuk parameter.

Contoh OleDb

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Contoh Odbc

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Lihat juga

• Perintah dan Parameter
• Parameter DataAdapter
• Pemetaan Jenis Data di ADO.NET
• Gambaran Umum ADO.NET