satu. Kata Pengantar:
Prosedur Tersimpan adalah sekumpulan pernyataan SQL yang dirancang untuk menyelesaikan fungsi tertentu, yang dikompilasi dan disimpan dalam database. Pengguna menjalankan prosedur tersimpan dengan menentukan namanya dan memberikan parameter (jika prosedur tersimpan memiliki parameter). Prosedur tersimpan adalah objek penting dalam database, dan setiap aplikasi database yang dirancang dengan baik harus menggunakan prosedur tersimpan. Secara umum, prosedur tersimpan memiliki keuntungan sebagai berikut:
◆ Prosedur tersimpan memungkinkan pemrograman komponen standar
◆ Prosedur tersimpan dapat mencapai kecepatan eksekusi lebih cepat
◆ Prosedur tersimpan dapat mengurangi lalu lintas jaringan
◆ Prosedur tersimpan dapat dimanfaatkan sepenuhnya sebagai mekanisme keamanan
Penulis artikel ini akan memperkenalkan kepada Anda penerapan prosedur tersimpan dalam aplikasi database .NET, dan cara menggunakannya bersama dengan objek SqlDataAdapter, objek DataSet, dll. di ADO.NET untuk meningkatkan kinerja aplikasi database .NET secara keseluruhan.
dua. Persyaratan sistem:
Alat pengembangan: Visual Studio.NET
Sistem manajemen basis data: SQL Server 2000 (termasuk basis data Pub yang digunakan dalam program sampel)
3. Buat prosedur tersimpan sederhana:
Di sini saya akan memperkenalkan kepada Anda cara menggunakan Visual Studio.NET IDE untuk membuat prosedur tersimpan. Sangat mudah dan intuitif untuk membuat prosedur tersimpan menggunakan Visual Studio.NET IDE Selama Anda menavigasi ke database Pubs di server explorer dan memperluas node, Anda akan menemukan berbagai objek database termasuk prosedur tersimpan, seperti yang ditunjukkan pada Gambar 1. Menunjukkan.
Klik kanan pada node prosedur tersimpan untuk memunculkan menu yang berisi perintah "Prosedur Tersimpan Baru". Setelah membuat prosedur tersimpan baru, templat kode yang ditunjukkan di bawah ini akan muncul di jendela pengeditan kode di IDE:
BUAT PROSEDUR dbo.StoredProcedure1
/*
(
@parameter1 tipe data = nilai default,
@parameter2 tipe data OUTPUT )
*/
SEBAGAI
/* TETAPKAN JUMLAH HIDUP */
KEMBALI
Templat kode di atas sesuai dengan aturan sintaksis yang disederhanakan untuk membuat prosedur tersimpan. Aturan sintaksis lengkapnya adalah sebagai berikut:
CREATE PROC [ EDURE ] procedure_name [ ;
[ { @parameter tipe data }
[ BERVARIASI ] [ = default ] [ KELUARAN ]
] [ ,...N ]
[ DENGAN
{ KOMPILASI ULANG |. ENKRIPSI |. KOMPILASI ULANG , ENKRIPSI } ]
[UNTUK REPLIKASI]
SEBAGAI sql_pernyataan [ ...n ]
Karena keterbatasan ruang, arti setiap parameter tidak akan diperkenalkan di sini. Pembaca yang tertarik dapat merujuk pada informasi tentang sistem manajemen database SQL Server 2000.
Di bawah ini saya akan memperkenalkan secara singkat setiap komponen tata bahasa dalam templat kode ini. Pernyataan CREATE PROCEDURE membuat prosedur tersimpan, diikuti dengan nama prosedur tersimpan. Komponen dalam "/*...*/" adalah parameter dari prosedur tersimpan, yang dapat mencakup parameter masukan dan parameter keluaran. Konten yang mengikuti kata kunci AS adalah bagian utama dari prosedur tersimpan, yang merupakan nomor dan jenis pernyataan SQL apa pun yang terdapat dalam prosedur tersimpan. Kata kunci RETURN menunjukkan akhir dari prosedur tersimpan dan dapat mengembalikan nilai status integer ke pemanggil. Mari kita membuat prosedur tersimpan sederhana tanpa parameter dan menggunakannya:
CREATE PROCEDURE dbo.up_GetPublisherInfo
SEBAGAI
PILIH pub_id, nama_pub, kota, negara bagian, negara
DARI penerbit
KEMBALI
Setelah membuat prosedur tersimpan di atas, simpanlah. Setelah disimpan, node yang sesuai dengan prosedur tersimpan akan muncul di Server Explorer. Perlu diketahui juga bahwa kata kunci CREATE di jendela pengeditan kode telah diubah menjadi kata kunci ALTER, yang digunakan untuk mengubah prosedur tersimpan yang ada. Untuk menjalankan prosedur tersimpan di atas, cukup klik simpulnya dan pilih "Jalankan Prosedur Tersimpan" di menu pop-up klik kanan. Hasil operasinya adalah sebagai berikut:
Empat. Membuat prosedur tersimpan dengan parameter:
Di atas kami membuat prosedur tersimpan sederhana tanpa parameter, namun dalam aplikasi sebenarnya banyak prosedur tersimpan dengan parameter yang sering digunakan. Prosedur tersimpan dengan parameter umumnya digunakan untuk memperbarui data atau memasukkan data. Di bawah ini kita dapat menggunakan metode operasi yang sama untuk membuat prosedur tersimpan dengan parameter:
BUAT PROSEDUR dbo.up_UpdatePublisherInfo
(
@pub_id karakter (4),
@pub_name varchar (40),
@cityvarchar(20),
@status karakter (2),
@negara varchar (30)
)
SEBAGAI
PERBARUI penerbit
SET nama_pub = @nama_pub, kota = @kota, negara bagian = @negara bagian,
negara = @negara
DIMANA ( pub_id = @pub_id )
KEMBALI
Pada kode di atas untuk membuat prosedur tersimpan, kami mendeklarasikan variabel-parameter lokal dari prosedur tersimpan dengan menambahkan tanda "@" sebelum namanya. Kami juga mendeklarasikan tipe setiap parameter dan menentukan nilai arah setiap parameter, yaitu adalah, Menunjukkan apakah parameternya adalah tipe input atau tipe output atau tipe input-output atau tipe nilai kembalian. Pengguna dapat memanggil prosedur tersimpan melalui nama prosedur tersimpan yang sesuai dan parameter yang benar dan valid. Selain itu, Anda juga dapat menambahkan parameter keluaran ke parameter dengan menggunakan kata kunci OUTPUT. Silakan merujuk ke aturan sintaksis di atas untuk metode tertentu. Parameter keluaran dapat mengembalikan informasi yang relevan kepada pemanggil.
Prosedur tersimpan di atas dapat memperbarui informasi penerbit terkait di tabel penerbit. Anda dapat menjalankannya dengan mengklik node dari prosedur tersimpan dan memilih "Jalankan Prosedur Tersimpan" di menu pop-up klik kanan. Setelah dijalankan, kotak dialog untuk memasukkan informasi penerbit akan muncul di IDE (seperti yang ditunjukkan pada Gambar 3). Isikan informasi update yang benar dan valid pada kotak dialog ini. Perhatikan bahwa nilai pub_id harus ada pada tabel asli, lalu klik tombol "OK" untuk memperbarui data.
lima. Membuat aplikasi database dengan prosedur tersimpan sederhana:
Selanjutnya, kita akan menggunakan prosedur tersimpan di atas tanpa parameter untuk membuat aplikasi database, yang juga menggunakan objek SqlDataAdapter dan objek DataSet di ADO.NET. Objek SqlDataAdapter berfungsi sebagai jembatan antara database SQL Server dan objek DataSet untuk menghubungkan keduanya. Objek SqlDataAdapter berisi dua metode yang umum digunakan: metode Fill() dan metode Update(). Metode Fill() dapat memperoleh data terkait dari database dan mengisinya ke dalam objek DataSet, dan metode Update(), seperti namanya, memperbarui kumpulan data. Sebelum memanggil metode Fill(), kita harus menyetel properti SelectCommand dari objek SqlDataAdapter, yang sebenarnya adalah objek SqlCommand. Properti SelectCommand berisi pernyataan SQL yang valid, dan dapat memperoleh data terkait dari database dan mengisinya ke dalam objek DataSet.
Pertama kita membuat aplikasi Windows Forms, bahasa pemrogramannya C#. Setelah membuat proyek baru di Visual Studio.NET, tambahkan kelas baru ke proyek - kelas Publishers, yang merangkum logika bisnis menghubungkan ke database backend dan mendapatkan objek kumpulan data. Langkah-langkahnya adalah sebagai berikut:
1. Tambahkan referensi namespace yang diperlukan: menggunakan System.Data.SqlClient
2. Tambahkan variabel berikut yang diperlukan ke kelas ini:
private SqlConnection cnPubs;
cmdPub SqlCommand pribadi;
daPub SqlDataAdapter pribadi;
dsPub Kumpulan Data pribadi;
3． Di konstruktor kelas ini, selesaikan koneksi ke database backend dan dapatkan objek SqlDataAdapter dan logika bisnis lainnya:
public Publishers()
{
mencoba
{
//Buat objek koneksi database
cnPubs = new SqlConnection( "server=localhost;keamanan terintegrasi=true;database=pubs" );
//Buat objek SqlCommand dan tentukan jenis perintahnya sebagai prosedur tersimpan
cmdPubs = SqlCommand baru();
cmdPubs.Koneksi = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Buat objek SqlDataAdapter dan atur properti SelectCommand ke objek SqlCommand di atas
daPubs = baru SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//Buat objek DataSet
dsPubs = Kumpulan Data baru();
}
tangkapan(Pengecualian) {}
}
4. Terakhir, metode GetPublisherInfo() disediakan untuk kelas ini, yang mengisi objek DataSet dengan objek SqlDataAdapter dan mengembalikan objek DataSet yang diisi. Metodenya adalah sebagai berikut (perlu dicatat bahwa objek SqlDataAdapter akan secara implisit membuka koneksi database dan secara implisit membuka koneksi database setelah mendapatkan data. Tutup koneksi secara formal, yang berarti objek DataSet bekerja dalam mode non-koneksi dan ketika Anda secara eksplisit membuka koneksi database dan mendapatkan data, objek SqlDataAdapter tidak akan menutup koneksi):
Kumpulan Data publik GetPublisherInfo().
{
// Panggil metode Fill() dari objek SqlDataAdapter dan kembalikan objek kumpulan data
daPubs.Isi(dsPubs);
kembalikan dsPub;
}
Setelah menyelesaikan desain kelas Publishers, kami menambahkan kontrol DataGrid ke formulir utama dan menggunakannya untuk menampilkan data di objek DataSet. Pertama tambahkan variabel anggota berikut ke kelas formulir utama:
private Publishers pub;
DataSet ds pribadi;
Setelah itu, modifikasi konstruktor kelas form utama sebagai berikut:
Formulir publik1()
{
//
// Diperlukan untuk dukungan Windows Forms Designer
//
Inisialisasi Komponen();
//
// TODO: Tambahkan kode konstruktor apa pun setelah panggilan InitializeComponent
// pubs = Penerbit baru();
ds = pub.GetPublisherInfo();
dataGrid1.DataSource = ds.Tabel[0];
}
Dengan cara ini, segera setelah aplikasi dimulai, data terkait yang diperoleh dari database Pub menggunakan prosedur tersimpan di atas tanpa parameter akan ditampilkan di kontrol DataGrid pada formulir utama. Diagram yang menjalankan program adalah sebagai berikut:
6. Membuat aplikasi database dengan prosedur tersimpan dengan parameter:
Di atas kita membuat aplikasi dengan prosedur tersimpan tanpa parameter, dan sekarang kita akan membuat aplikasi database yang lebih kompleks. Dalam aplikasi database sebenarnya, kita sering kali perlu mendapatkan data dan memperbarui, menyisipkan atau menghapus data. Saat ini, kita perlu menggunakan prosedur tersimpan dengan parameter. Pada saat yang sama, saat menggunakan objek SqlDataAdapter, kita akan memanggilnya Update(). metode. Metode Update() akan secara otomatis menyelesaikan operasi terkait berdasarkan perubahan pada setiap record di objek DataTable di objek DataSet. Objek SqlDataAdapter juga berisi properti seperti UpdateCommand, InsertCommand, DeleteCommand, dll. Properti ini sebenarnya adalah objek SqlCommand. Metode Update() memilih atribut yang sesuai berdasarkan jenis operasi.
Saat menggunakan prosedur tersimpan dengan parameter untuk membangun aplikasi database, kami biasanya menggunakan kelas SqlParameter, yang merangkum berbagai properti dan metode yang terkait dengan parameter Sql. Propertinya meliputi ParameterName, SqlDBType, Direction, Size, Value, SourceColumn dan SourceVersion, dll. Diantaranya, ParameterName, SqlDBType, Direction, Size dan atribut lainnya digunakan untuk mencocokkan parameter yang ditentukan dalam prosedur tersimpan. Misalnya, objek SqlParameter yang ditentukan di bawah ini digunakan untuk mencocokkan parameter "@pub_id " dalam prosedur tersimpan up_UpdatePublisherInfo yang ditentukan sebelumnya.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
Dalam definisi di atas, meskipun atribut Direction tidak diberikan secara eksplisit, nilai defaultnya adalah Input, sehingga memenuhi kebutuhan kita. Dan jika properti Direction dari objek SqlParameter adalah InputOutput atau Output atau ReturnValue, maka properti Direction-nya harus dinyatakan dengan jelas. Misalnya, kode berikut dengan jelas mendeklarasikan bahwa properti Direction dari objek SqlParameter adalah Output.
oParam.Direction = ParameterDirection.Output;
Properti SourceColumn digunakan untuk mencocokkan objek DataColumn dalam objek DataTable. Pencocokan ini secara implisit dapat mengimpor objek SqlParameter yang diperlukan ketika metode Update() dipanggil untuk memperbarui objek DataTable. Jika properti ini tidak dideklarasikan saat mendefinisikan, maka Anda harus secara eksplisit menyatakan properti SourceColumn dari objek SqlParameter dalam kode Anda.
Nilai default properti SourceVersion adalah nilai saat ini di bidang terkait objek DataRow, yang merupakan nilai yang akan diperbarui ke database. Tentu saja, properti SourceVersion juga dapat menunjuk ke nilai asli di bidang objek DataRow yang sesuai, yaitu nilai awal yang diperoleh dari database. Dalam sistem pemrosesan transaksi database, masalah sinkronisasi data sangatlah penting. Mari kita membangun prosedur tersimpan yang dapat mendeteksi sinkronisasi data.
BUAT PROSEDUR dbo.up_UpdatePublisherName
(
@pub_id karakter(4),
@nama_pub varchar(40),
@Original_pub_name varchar(40)
)
SEBAGAI
jika ada (pilih pub_id
dari penerbit
di mana (pub_id = @pub_id) DAN (pub_name = @Original_pub_name))
Mulai
PERBARUI penerbit SET pub_name = @pub_name
DIMANA (pub_id = @pub_id)
Akhir
KEMBALI
Selanjutnya, kita memanggil prosedur tersimpan pada aplikasi di atas untuk memperbarui nama penerbit. Pertama, tingkatkan kelas logika bisnisnya-kelas Penerbit berdasarkan aplikasi asli:
1. Tambahkan objek SqlCommand baru yang dapat digunakan sebagai properti UpdateCommand dari objek SqlDataAdapter:
cmdUpdPubs SqlCommand pribadi;
2. Perbarui fungsi konstruktor Publishers() kelas ini untuk menambahkan yang berikut:
// Buat objek SqlCommand lain yang mereferensikan prosedur tersimpan yang memperbarui nama penerbit
cmdUpdPubs = SqlCommand baru();
cmdUpdPubs.Koneksi = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Tambahkan parameter yang diperlukan ke objek SqlCommand di atas
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name" );
SqlParameter updParam = SqlParameter baru
( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Asli;
cmdUpdPubs.Parameter.Tambahkan( updParam );
3． Tentukan properti UpdateCommand dari objek SqlDataAdapter sebagai objek SqlCommand yang didefinisikan di atas:
daPubs.UpdateCommand = cmdUpdPubs;
4. Tambahkan metode UpdatePublisherName():
kekosongan publik UpdatePublisherName (DataSet dsChanges)
{
// Perbarui semua perubahan
daPubs.Update(dsChanges);
}
Setelah kelas logika bisnis aplikasi selesai, tambahkan tombol bernama "Perbarui Kumpulan Data" pada formulir utama, dan tambahkan fungsi respons peristiwa dari tombol tersebut sebagai berikut:
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo();
Sejauh ini, kelas logika bisnis aplikasi dan kelas formulir utama telah diperbarui. Sekarang aplikasi dapat memperbarui konten yang relevan dalam database sesuai dengan perubahan pengguna.
tujuh. Ringkasan:
Artikel ini memperkenalkan Anda pada pengetahuan dasar tentang prosedur tersimpan dan cara menggabungkan objek SqlDataAdapter, objek DataSet, dll. untuk membangun aplikasi berbasis data dalam aplikasi database .NET. Pada artikel ini, kami telah menggunakan dua jenis prosedur tersimpan: satu adalah prosedur tersimpan sederhana tanpa parameter, yang relatif mudah digunakan; yang lain adalah prosedur tersimpan dengan parameter, dan Anda harus memanggil jenis prosedur tersimpan ini ke objek SqlParameter. Pada saat yang sama, tidak sulit untuk menemukan bahwa merangkum logika bisnis pembaruan data dalam prosedur tersimpan adalah metode desain yang baik, yang dapat meningkatkan pengelolaan, skalabilitas, dan keamanan basis data aplikasi. Demikian pula, logika bisnis untuk memasukkan dan menghapus data dapat diringkas dalam prosedur tersimpan dan digunakan dalam aplikasi dengan cara yang sama. Akhir kata, semoga artikel ini dapat memberikan manfaat yang besar bagi semuanya.