Mengonfigurasi parameter dan jenis data parameter

  • Artikel

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. 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 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.

Memasok 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 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 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 Bit Boolean Bit Byte
Byte Byte TinyInt UnsignedTinyInt TinyInt Byte
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. Char Char Byte
DateTime DateTime DateTime DBTimeStamp DateTime DateTime
DateTimeOffset DateTimeOffset DateTimeOffset pada SQL Server 2008. Menyimpulkan SqlDbType dari DateTimeOffset tidak didukung dalam versi SQL Server yang lebih lama dari SQL Server 2008. DateTime
Decimal Decimal Decimal Decimal Numerik Angka
Double Laju Float Laju Laju Laju
Single Tunggal Riil Tunggal Riil Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Mentah
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 Bigint Bigint Bigint Angka
Object Objek Varian Varian Menyimpulkan OdbcType dari Object tidak didukung. Blob
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 yang lebih lama dari SQL Server 2008. DBTime Waktu DateTime
UInt16 UInt16 Menyimpulkan SqlDbType dari UInt16 tidak didukung. UnsignedSmallInt Int UInt16
UInt32 UInt32 Menyimpulkan SqlDbType dari UInt32 tidak didukung. UnsignedInt Bigint UInt32
UInt64 UInt64 Menyimpulkan SqlDbType dari UInt64 tidak didukung. UnsignedBigInt Numerik Angka
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Mata Uang Uang Mata Uang Menyimpulkan OdbcType dari Currency tidak didukung. Angka
Tanggal Tanggal di SQL Server 2008. Menyimpulkan SqlDbType dari Tanggal tidak didukung di versi SQL Server yang lebih lama dari SQL Server 2008. DBDate Tanggal DateTime
SByte Menyimpulkan SqlDbType dari SByte tidak didukung. TinyInt Menyimpulkan OdbcType dari SByte tidak didukung. SByte
StringFixedLength NChar WChar NChar NChar
Waktu Waktu di SQL Server 2008. Menyimpulkan SqlDbType dari Waktu tidak didukung di versi SQL Server yang lebih lama dari SQL Server 2008. DBTime Waktu DateTime
VarNumeric Menyimpulkan SqlDbType dari VarNumeric tidak didukung. VarNumeric Menyimpulkan OdbcType dari VarNumeric tidak didukung. Angka
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 null) jika tidak, 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 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 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 tanda tanya (?) tempat penampung untuk meneruskan parameter ke pernyataan SQL atau prosedur yang disimpan. 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(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("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            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 yang disimpan sebagai tempat penampung 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 ? tempat penampung 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