Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
oleh Scott Mitchell
Dalam tutorial sebelumnya, kami telah membuat pernyataan SQL dalam kode kami dan meneruskan pernyataan ke database yang akan dijalankan. Pendekatan alternatif adalah menggunakan prosedur tersimpan, di mana pernyataan SQL telah ditentukan sebelumnya di database. Dalam tutorial ini kita mempelajari cara membuat Wizard TableAdapter menghasilkan prosedur tersimpan baru untuk kita.
Pendahuluan
Lapisan Akses Data (DAL) untuk tutorial ini menggunakan Typed DataSets. Seperti yang dibahas dalam tutorial Membuat Lapisan Akses Data, DataSet Tipe berjenis kuat yang terdiri dari DataTables dan TableAdapters. DataTables mewakili entitas logis dalam sistem, sementara TableAdapters menghubungkan dengan database yang mendasar untuk melakukan pekerjaan akses data. Ini termasuk mengisi DataTables dengan data, menjalankan kueri yang mengembalikan data skalar, dan menyisipkan, memperbarui, dan menghapus rekaman dari database.
Perintah SQL yang dijalankan oleh TableAdapters dapat berupa pernyataan SQL ad-hoc, seperti SELECT columnList FROM TableName
, atau prosedur tersimpan. TableAdapters dalam arsitektur kami menggunakan pernyataan SQL ad-hoc. Namun, banyak pengembang dan administrator database lebih memilih prosedur tersimpan daripada pernyataan SQL ad-hoc untuk alasan keamanan, keberlanjutan, dan pembaruan. Yang lain dengan jelas lebih suka pernyataan SQL ad-hoc karena fleksibilitasnya. Dalam pekerjaan saya sendiri saya lebih menyukai prosedur tersimpan daripada pernyataan SQL ad-hoc, tetapi memilih untuk menggunakan pernyataan SQL ad-hoc untuk menyederhanakan tutorial sebelumnya.
Saat menentukan TableAdapter atau menambahkan metode baru, wizard TableAdapter membuatnya sama mudahnya untuk membuat prosedur tersimpan baru atau menggunakan prosedur tersimpan yang ada seperti yang dilakukan untuk menggunakan pernyataan SQL ad-hoc. Dalam tutorial ini kita akan memeriksa cara membuat wizard TableAdapter secara otomatis menghasilkan prosedur tersimpan. Dalam tutorial berikutnya kita akan melihat cara mengonfigurasi metode TableAdapter s untuk menggunakan prosedur tersimpan yang ada atau dibuat secara manual.
Nota
Lihat entri blog Rob Howard Jangan Gunakan Prosedur Tersimpan Belum? dan entri blog Frans BoumaProsedur Tersimpan buruk, M Kay? untuk perdebatan semarak tentang pro dan kontra prosedur tersimpan dan ad-hoc SQL.
Dasar Prosedur Tersimpan
Fungsi adalah konstruksi umum untuk semua bahasa pemrograman. Fungsi adalah kumpulan pernyataan yang dijalankan ketika fungsi dipanggil. Fungsi dapat menerima parameter input dan dapat secara opsional mengembalikan nilai.
Prosedur tersimpan adalah konstruksi database yang berbagi banyak kesamaan dengan fungsi dalam bahasa pemrograman. Prosedur tersimpan terdiri dari serangkaian pernyataan T-SQL yang dijalankan saat prosedur tersimpan dipanggil. Prosedur tersimpan dapat menerima nol hingga banyak parameter input dan dapat mengembalikan nilai skalar, parameter output, atau, yang paling umum, kumpulan hasil dari SELECT
kueri.
Nota
Prosedur tersimpan sering kali disebut sebagai sprocs atau SPs.
Prosedur tersimpan dibuat menggunakan CREATE PROCEDURE
pernyataan T-SQL. Misalnya, skrip T-SQL berikut membuat prosedur tersimpan bernama GetProductsByCategoryID
yang menerima satu parameter bernama @CategoryID
dan mengembalikan bidang ProductID
, ProductName
, UnitPrice
, dan Discontinued
dari kolom dalam tabel Products
yang memiliki nilai CategoryID
cocok.
CREATE PROCEDURE GetProductsByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
Setelah prosedur tersimpan ini dibuat, prosedur dapat dipanggil menggunakan sintaks berikut:
EXEC GetProductsByCategory categoryID
Nota
Dalam tutorial berikutnya kita akan memeriksa pembuatan prosedur tersimpan melalui Visual Studio IDE. Namun, untuk tutorial ini, kita akan membiarkan wizard TableAdapter secara otomatis menghasilkan prosedur tersimpan untuk kita.
Selain hanya mengembalikan data, prosedur tersimpan sering digunakan untuk melakukan beberapa perintah database dalam cakupan satu transaksi. Prosedur tersimpan bernama DeleteCategory
, misalnya, mungkin mengambil @CategoryID
parameter dan melakukan dua DELETE
pernyataan: pertama, satu untuk menghapus produk terkait dan yang kedua menghapus kategori yang ditentukan. Beberapa pernyataan dalam prosedur tersimpan tidak secara otomatis tergabung dalam transaksi. Perintah T-SQL tambahan perlu dikeluarkan untuk memastikan prosedur tersimpan yang memiliki beberapa perintah diperlakukan sebagai operasi atom. Kita akan melihat cara membungkus perintah prosedur tersimpan dalam cakupan transaksi dalam tutorial berikutnya.
Saat menggunakan prosedur tersimpan dalam arsitektur, metode Lapisan Akses Data memanggil prosedur tersimpan tertentu daripada mengeluarkan pernyataan SQL ad-hoc. Ini memusatkan lokasi pernyataan SQL yang dijalankan (pada database) daripada menentukannya dalam arsitektur aplikasi. Sentralisasi ini bisa dibilang memudahkan untuk menemukan, menganalisis, dan menyetel kueri dan memberikan gambaran yang jauh lebih jelas tentang di mana dan bagaimana database digunakan.
Untuk informasi selengkapnya tentang dasar-dasar prosedur tersimpan, lihat sumber daya di bagian Bacaan Lebih Lanjut di akhir tutorial ini.
Langkah 1: Membuat Halaman Web Skenario Lapisan Akses Data Tingkat Lanjut
Sebelum kita memulai diskusi tentang membuat DAL menggunakan prosedur tersimpan, mari kita luangkan waktu sejenak untuk membuat halaman ASP.NET di proyek situs web kita yang akan kita butuhkan untuk ini dan beberapa tutorial berikutnya. Mulailah dengan menambahkan folder baru bernama AdvancedDAL
. Selanjutnya, tambahkan halaman ASP.NET berikut ke folder tersebut Site.master
, pastikan untuk mengaitkan setiap halaman dengan halaman master:
Default.aspx
NewSprocs.aspx
ExistingSprocs.aspx
JOINs.aspx
AddingColumns.aspx
ComputedColumns.aspx
EncryptingConfigSections.aspx
ManagedFunctionsAndSprocs.aspx
Gambar 1: Tambahkan Halaman ASP.NET untuk Tutorial Skenario Lapisan Akses Data Tingkat Lanjut
Seperti di folder lain, Default.aspx
di AdvancedDAL
folder akan mencantumkan tutorial di bagiannya. Ingat bahwa SectionLevelTutorialListing.ascx
Kontrol Pengguna menyediakan fungsionalitas ini. Oleh karena itu, tambahkan Kontrol Pengguna ini ke Default.aspx
dengan menyeretnya dari Penjelajah Solusi ke tampilan Desain halaman.
Gambar 2: Tambahkan SectionLevelTutorialListing.ascx
Kontrol Pengguna ke Default.aspx
(Klik untuk melihat gambar ukuran penuh)
Terakhir, tambahkan halaman ini sebagai entri ke Web.sitemap
file. Secara khusus, tambahkan markup berikut setelah Bekerja dengan Data Batch <siteMapNode>
:
<siteMapNode url="~/AdvancedDAL/Default.aspx"
title="Advanced DAL Scenarios"
description="Explore a number of advanced Data Access Layer scenarios.">
<siteMapNode url="~/AdvancedDAL/NewSprocs.aspx"
title="Creating New Stored Procedures for TableAdapters"
description="Learn how to have the TableAdapter wizard automatically
create and use stored procedures." />
<siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx"
title="Using Existing Stored Procedures for TableAdapters"
description="See how to plug existing stored procedures into a
TableAdapter." />
<siteMapNode url="~/AdvancedDAL/JOINs.aspx"
title="Returning Data Using JOINs"
description="Learn how to augment your DataTables to work with data
returned from multiple tables via a JOIN query." />
<siteMapNode url="~/AdvancedDAL/AddingColumns.aspx"
title="Adding DataColumns to a DataTable"
description="Master adding new columns to an existing DataTable." />
<siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx"
title="Working with Computed Columns"
description="Explore how to work with computed columns when using
Typed DataSets." />
<siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx"
title="Protected Connection Strings in Web.config"
description="Protect your connection string information in
Web.config using encryption." />
<siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx"
title="Creating Managed SQL Functions and Stored Procedures"
description="See how to create SQL functions and stored procedures
using managed code." />
</siteMapNode>
Setelah memperbarui Web.sitemap
, luangkan waktu sejenak untuk melihat situs web tutorial melalui browser. Menu di sebelah kiri sekarang menyertakan item untuk tutorial skenario DAL tingkat lanjut.
Gambar 3: Peta Situs Sekarang Menyertakan Entri untuk Tutorial Skenario DAL Tingkat Lanjut
Langkah 2: Mengonfigurasi TableAdapter untuk Membuat Prosedur Tersimpan Baru
Untuk menunjukkan pembuatan Lapisan Akses Data yang menggunakan prosedur tersimpan alih-alih pernyataan SQL ad-hoc, mari kita buat Kumpulan Data Jenis baru di ~/App_Code/DAL
folder bernama NorthwindWithSprocs.xsd
. Karena kami telah melalui proses ini secara rinci dalam tutorial sebelumnya, kami akan melanjutkan dengan cepat melalui langkah-langkah di sini. Jika Anda terjebak atau memerlukan instruksi langkah demi langkah lebih lanjut dalam membuat dan mengonfigurasi Typed DataSet, rujuk kembali ke tutorial Membuat Lapisan Akses Data.
Tambahkan Himpunan Data baru ke proyek dengan mengklik DAL
kanan folder, memilih Tambahkan Item Baru, dan pilih templat Himpunan Data seperti yang ditunjukkan pada Gambar 4.
Gambar 4: Tambahkan Himpunan Data Jenis Baru ke Proyek Bernama NorthwindWithSprocs.xsd
(Klik untuk melihat gambar ukuran penuh)
Ini akan membuat Kumpulan Data Jenis baru, membuka Perancangnya, membuat TableAdapter baru, dan meluncurkan Wizard Konfigurasi TableAdapter. Langkah pertama Panduan Konfigurasi TableAdapter meminta kami untuk memilih database yang akan dikerjakan. String koneksi ke database Northwind harus tercantum dalam daftar drop-down. Pilih ini dan klik Berikutnya.
Dari layar berikutnya ini kita dapat memilih bagaimana TableAdapter harus mengakses database. Dalam tutorial sebelumnya, kami memilih opsi pertama, Gunakan pernyataan SQL. Untuk tutorial ini, pilih opsi kedua, Buat prosedur tersimpan baru, dan klik Berikutnya.
Gambar 5: Instruksikan TableAdapter untuk Membuat Prosedur Tersimpan Baru (Klik untuk melihat gambar ukuran penuh)
Sama seperti menggunakan pernyataan SQL ad-hoc, pada langkah berikut kami diminta untuk memberikan SELECT
pernyataan untuk kueri utama TableAdapter. Tetapi alih-alih menggunakan pernyataan SELECT
yang dimasukkan di sini untuk melakukan kueri ad-hoc secara langsung, wizard TableAdapter akan membuat prosedur tersimpan yang berisi kueri ini SELECT
.
Gunakan kueri berikut SELECT
untuk TableAdapter ini:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
Gambar 6: Masukkan SELECT
Kueri (Klik untuk menampilkan gambar ukuran penuh)
Nota
Kueri di atas sedikit berbeda dari kueri utama ProductsTableAdapter
dalam Northwind
Himpunan Data Tipe. Ingat bahwa ProductsTableAdapter
dalam Typed DataSet Northwind
mencakup dua subkueri yang berkorelasi untuk mengambil kembali nama kategori dan nama perusahaan masing-masing untuk kategori produk dan supplier. Dalam tutorial Memperbarui TableAdapter untuk Menggunakan JOIN yang akan datang, kita akan melihat menambahkan data terkait ini ke TableAdapter ini.
Luangkan waktu sejenak untuk mengklik tombol Opsi Tingkat Lanjut. Dari sini kita dapat menentukan apakah wizard juga harus menghasilkan pernyataan sisipkan, perbarui, dan hapus untuk TableAdapter, apakah akan menggunakan konkurensi optimis, dan apakah tabel data harus di-refresh setelah penyisipan dan pembaruan. Opsi Buat pernyataan Sisipkan, Perbarui, dan Hapus dicentang secara default. Biarkan diperiksa. Untuk tutorial ini, biarkan opsi Gunakan konkurensi optimis tidak dicentang.
Saat prosedur tersimpan dibuat secara otomatis oleh wizard TableAdapter, tampaknya opsi Refresh tabel data diabaikan. Terlepas dari apakah kotak centang ini dicentang, prosedur sisipkan dan perbarui tersimpan yang dihasilkan mengambil rekaman yang baru saja disisipkan atau baru saja diperbarui, seperti yang akan kita lihat di Langkah 3.
Gambar 7: Biarkan Opsi Buat Pernyataan Sisipkan, Perbarui, dan Hapus Dicentang
Nota
Jika opsi Gunakan kekonkurenan optimis dicentang, wizard akan menambahkan kondisi tambahan ke WHERE
klausa yang mencegah pembaruan data jika ada perubahan di bidang lain. Lihat kembali tutorial Menerapkan Konkurensi Optimis untuk informasi selengkapnya tentang menggunakan fitur kontrol konkurensi optimis tableAdapter bawaan.
Setelah memasukkan SELECT
kueri dan mengonfirmasi bahwa opsi Buat pernyataan Sisipkan, Perbarui, dan Hapus dicentang, klik Berikutnya. Layar berikutnya, yang diperlihatkan dalam Gambar 8, meminta nama prosedur tersimpan yang akan dibuat wizard untuk memilih, menyisipkan, memperbarui, dan menghapus data. Ubah nama prosedur tersimpan ini menjadi Products_Select
, , Products_Insert
Products_Update
, dan Products_Delete
.
Gambar 8: Ganti nama Prosedur Tersimpan (Klik untuk melihat gambar ukuran penuh)
Untuk melihat T-SQL yang akan digunakan wizard TableAdapter untuk membuat empat prosedur tersimpan, klik tombol Pratinjau Skrip SQL. Dari kotak dialog Pratinjau Skrip SQL, Anda dapat menyimpan skrip ke file atau menyalinnya ke clipboard.
Gambar 9: Pratinjau Skrip SQL yang Digunakan untuk Menghasilkan Prosedur Tersimpan
Setelah menamai prosedur tersimpan, klik Berikutnya untuk menamai metode terkait TableAdapter. Sama seperti saat menggunakan pernyataan SQL ad-hoc, kita dapat membuat metode yang mengisi DataTable yang ada atau mengembalikan yang baru. Kita juga dapat menentukan apakah TableAdapter harus menyertakan pola DB-Direct untuk menyisipkan, memperbarui, dan menghapus rekaman. Biarkan ketiga kotak centang dicentang, tetapi ganti nama metode Kembalikan DataTable menjadi GetProducts
(seperti yang ditunjukkan pada Gambar 10).
Gambar 10: Beri Nama Metode Fill
dan GetProducts
(Klik untuk melihat gambar ukuran penuh)
Klik Berikutnya untuk melihat ringkasan langkah-langkah yang akan dilakukan wizard. Selesaikan wizard dengan mengklik tombol Selesai. Setelah wizard selesai, Anda akan dikembalikan ke Perancang Himpunan Data, yang sekarang harus menyertakan ProductsDataTable
.
Gambar 11: Perancang Himpunan Data Menampilkan Gambar yang Baru Ditambahkan ProductsDataTable
(Klik untuk melihat gambar berukuran penuh)
Langkah 3: Memeriksa Prosedur Tersimpan yang Baru Dibuat
Wizard TableAdapter yang digunakan di Langkah 2 secara otomatis membuat prosedur tersimpan untuk memilih, menyisipkan, memperbarui, dan menghapus data. Prosedur tersimpan ini dapat dilihat atau dimodifikasi melalui Visual Studio dengan masuk ke Server Explorer dan menelusuri paling detail ke folder Prosedur Tersimpan database. Seperti yang ditunjukkan Gambar 12, database Northwind berisi empat prosedur tersimpan baru: Products_Delete
, , Products_Insert
Products_Select
, dan Products_Update
.
Gambar 12: Empat Prosedur Tersimpan yang Dibuat di Langkah 2 Dapat Ditemukan di Folder Prosedur Tersimpan Database
Nota
Jika Anda tidak melihat Penjelajah Server, buka menu Tampilan dan pilih opsi Penjelajah Server. Jika Anda tidak melihat prosedur tersimpan terkait produk yang ditambahkan dari Langkah 2, coba klik kanan pada folder Prosedur Tersimpan dan pilih Refresh.
Untuk melihat atau mengubah prosedur tersimpan, klik dua kali namanya di Penjelajah Server atau, atau, klik kanan pada prosedur tersimpan dan pilih Buka. Gambar 13 menunjukkan prosedur tersimpan Products_Delete
, ketika dibuka.
Gambar 13: Prosedur Tersimpan Dapat Dibuka dan Dimodifikasi Dari Dalam Visual Studio (Klik untuk melihat gambar ukuran penuh)
Isi prosedur yang tersimpan Products_Delete
dan Products_Select
cukup sederhana. Prosedur tersimpan Products_Insert
dan Products_Update
, di sisi lain, memerlukan inspeksi lebih lanjut karena keduanya menjalankan pernyataan SELECT
setelah pernyataan INSERT
dan UPDATE
mereka. Misalnya, SQL berikut membentuk prosedur tersimpan Products_Insert
:
ALTER PROCEDURE dbo.Products_Insert
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],
[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = SCOPE_IDENTITY())
Prosedur tersimpan menerima kolom Products
yang dikembalikan oleh kueri SELECT
yang ditentukan dalam wizard TableAdapter sebagai parameter input, dan nilai-nilai ini digunakan dalam pernyataan INSERT
. Setelah pernyataan INSERT
, kueri SELECT
digunakan untuk mengembalikan nilai kolom Products
(termasuk ProductID
) dari catatan yang baru ditambahkan. Kemampuan refresh ini berguna saat menambah catatan baru menggunakan pola Pembaruan Batch karena secara otomatis memperbarui properti ProductRow
instans ProductID
yang baru ditambahkan dengan nilai yang ditingkatkan secara otomatis yang ditetapkan oleh database.
Kode berikut mengilustrasikan fitur ini. Ini berisi ProductsTableAdapter
dan ProductsDataTable
yang dibuat untuk Himpunan DataSet Tipe NorthwindWithSprocs
. Produk baru ditambahkan ke database dengan membuat instans ProductsRow
, menyediakan nilainya, dan memanggil metode TableAdapter Update
, dengan meneruskan ProductsDataTable
. Secara internal, metode TableAdapter s Update
menghitung ProductsRow
instans dalam DataTable yang diteruskan (dalam contoh ini hanya ada satu - yang baru saja kami tambahkan), dan melakukan perintah sisipkan, perbarui, atau hapus yang sesuai. Dalam hal ini, Products_Insert
prosedur tersimpan dijalankan, yang menambahkan rekaman baru ke Products
tabel dan mengembalikan detail rekaman yang baru ditambahkan. Nilai ProductsRow
instans ProductID
kemudian diperbarui. Setelah metode Update
selesai, kita dapat mengakses nilai dari rekaman yang baru ditambahkan ProductID
melalui properti ProductsRow
ProductID
.
' Create the ProductsTableAdapter and ProductsDataTable
Dim productsAPI As New NorthwindWithSprocsTableAdapters.ProductsTableAdapter
Dim products As New NorthwindWithSprocs.ProductsDataTable
' Create a new ProductsRow instance and set its properties
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = "New Product"
product.CategoryID = 1 ' Beverages
product.Discontinued = False
' Add the ProductsRow instance to the DataTable
products.AddProductsRow(product)
' Update the DataTable using the Batch Update pattern
productsAPI.Update(products)
' At this point, we can determine the value of the newly-added record's ProductID
Dim newlyAddedProductIDValue as Integer = product.ProductID
Prosedur tersimpan Products_Update
juga menyertakan pernyataan SELECT
setelah pernyataan UPDATE
.
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@Original_ProductID int,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products]
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @Original_ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Perhatikan bahwa prosedur tersimpan ini mencakup dua parameter input untuk ProductID
: @Original_ProductID
dan @ProductID
. Fungsionalitas ini memungkinkan skenario di mana kunci utama dapat diubah. Misalnya, dalam database karyawan, setiap catatan karyawan mungkin menggunakan nomor jaminan sosial karyawan sebagai kunci utama mereka. Untuk mengubah nomor jaminan sosial karyawan yang ada, nomor jaminan sosial baru dan yang asli harus disediakan. Untuk tabel Products
, fungsionalitas tersebut tidak diperlukan karena kolom ProductID
dan tidak boleh diubah. Bahkan, pernyataan UPDATE
dalam prosedur tersimpan Products_Update
tidak menyertakan kolom ProductID
dalam daftar kolom. Jadi, saat @Original_ProductID
digunakan dalam pernyataan UPDATE
di klausul WHERE
, itu tidak diperlukan untuk tabel Products
dan dapat saja digantikan oleh parameter @ProductID
. Saat memodifikasi parameter prosedur tersimpan, penting bahwa metode TableAdapter yang menggunakan prosedur tersimpan tersebut juga diperbarui.
Langkah 4: Memodifikasi Parameter Prosedur Tersimpan dan Memperbarui TableAdapter
Karena parameter tersebut berlebihan, sebaiknya kita hapus dari prosedur tersimpan @Original_ProductID
sepenuhnya.
Products_Update
Buka prosedur tersimpan, hapus @Original_ProductID
parameter, dan, dalam WHERE
klausul UPDATE
pernyataan, ubah nama parameter yang digunakan dari @Original_ProductID
ke @ProductID
. Setelah membuat perubahan ini, T-SQL dalam prosedur tersimpan akan terlihat seperti berikut ini:
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Untuk menyimpan perubahan ini ke database, klik ikon Simpan di toolbar atau tekan Ctrl+S. Pada titik ini, Products_Update
prosedur tersimpan tidak mengharapkan @Original_ProductID
parameter input, tetapi TableAdapter dikonfigurasi untuk meneruskan parameter seperti itu. Anda dapat melihat parameter yang akan dikirim oleh TableAdapter ke stored procedure dengan memilih TableAdapter di DataSet Designer, pergi ke jendela Properties, dan mengklik ikon elipsis dalam koleksi Products_Update
s UpdateCommand
. Ini memunculkan kotak dialog Editor Koleksi Parameter yang diperlihatkan dalam Gambar 14.
Gambar 14: Editor Koleksi Parameter Mencantumkan Parameter yang Digunakan dan Diteruskan ke Prosedur Tersimpan Products_Update
Anda dapat menghapus parameter ini dari sini hanya dengan memilih @Original_ProductID
parameter dari daftar anggota dan mengklik tombol Hapus.
Sebagai alternatif, Anda dapat memperbarui parameter yang digunakan untuk semua metode dengan mengklik kanan pada TableAdapter di Perancang dan memilih Konfigurasi. Ini akan mengaktifkan Wizard Konfigurasi TableAdapter, mencantumkan prosedur tersimpan yang digunakan untuk memilih, menyisipkan, memperbarui, dan menghapus, bersama dengan parameter yang diharapkan oleh prosedur tersimpan. Jika Anda mengeklik daftar drop-down Perbarui, Anda dapat melihat parameter input yang diharapkan dari prosedur tersimpan Products_Update
, yang saat ini sudah tidak lagi menyertakan @Original_ProductID
(lihat Gambar 15). Cukup klik Selesai untuk memperbarui koleksi parameter yang digunakan oleh TableAdapter secara otomatis.
Gambar 15: Sebagai alternatif, Anda dapat menggunakan Wizard Konfigurasi TableAdapter untuk menyegarkan Koleksi Parameter Metodenya (Klik untuk melihat gambar ukuran penuh)
Langkah 5: Menambahkan Metode TableAdapter Tambahan
Seperti yang diilustrasikan Langkah 2, saat membuat TableAdapter baru, mudah untuk membuat prosedur tersimpan yang sesuai secara otomatis. Hal yang sama berlaku saat menambahkan metode tambahan ke TableAdapter. Untuk mengilustrasikan ini, mari kita tambahkan metode GetProductByProductID(productID)
ke ProductsTableAdapter
yang dibuat di Langkah 2. Metode ini akan menerima nilai ProductID
sebagai input dan mengembalikan detail tentang produk yang ditentukan.
Mulailah dengan mengklik kanan tableAdapter dan memilih Tambahkan Kueri dari menu konteks.
Gambar 16: Tambahkan Kueri Baru ke TableAdapter
Ini akan memulai asisten Konfigurasi Kueri TableAdapter, yang pertama-tama menanyakan bagaimana TableAdapter harus mengakses ke database. Untuk membuat prosedur tersimpan baru, pilih opsi Buat prosedur tersimpan baru dan klik Berikutnya.
Gambar 17: Pilih Opsi Buat prosedur tersimpan baru (Klik untuk melihat gambar ukuran penuh)
Layar berikutnya meminta kami untuk mengidentifikasi jenis kueri yang akan dijalankan, apakah akan mengembalikan sekumpulan baris atau nilai skalar tunggal, atau melakukan UPDATE
pernyataan , INSERT
, atau DELETE
. Karena metode GetProductByProductID(productID)
akan mengembalikan baris, biarkan opsi SELECT yang mengembalikan baris tetap dipilih dan tekan Berikutnya.
Gambar 18: Pilih SELECT yang mengembalikan Opsi baris (Klik untuk melihat gambar ukuran penuh)
Layar berikutnya menampilkan kueri utama TableAdapter, yang hanya mencantumkan nama prosedur tersimpan (dbo.Products_Select
). Ganti nama prosedur tersimpan dengan pernyataan berikut SELECT
, yang mengembalikan semua bidang produk untuk produk tertentu:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Gambar 19: Ganti Nama Prosedur Tersimpan SELECT
dengan Kueri (Klik untuk melihat gambar ukuran penuh)
Layar berikutnya meminta Anda untuk memberi nama prosedur tersimpan yang akan dibuat. Masukkan nama Products_SelectByProductID
dan klik Berikutnya.
Gambar 20: Beri nama Prosedur Products_SelectByProductID
Tersimpan Baru (Klik untuk melihat gambar ukuran penuh)
Langkah terakhir dari wizard memungkinkan kami untuk mengganti nama metode yang kami hasilkan dan menunjukkan apakah akan menggunakan pola Mengisi DataTable, Mengembalikan DataTable, atau kedua-duanya. Untuk metode ini, biarkan kedua opsi dicentang, tetapi ganti nama metode menjadi FillByProductID
dan GetProductByProductID
. Klik Berikutnya untuk melihat ringkasan langkah-langkah yang akan dilakukan panduan lalu klik Selesai untuk menyelesaikan panduan.
Gambar 21: Ganti nama Metode TableAdapter s menjadi FillByProductID
dan GetProductByProductID
(Klik untuk melihat gambar ukuran penuh)
Setelah menyelesaikan wizard, TableAdapter memiliki metode baru yang tersedia, GetProductByProductID(productID)
yang, ketika dipanggil, akan menjalankan Products_SelectByProductID
prosedur tersimpan yang baru saja dibuat. Luangkan waktu sejenak untuk melihat prosedur tersimpan baru ini dari Penjelajah Server dengan menelusuri folder Prosedur Tersimpan Products_SelectByProductID
dan membuka (jika Anda tidak melihatnya, klik kanan pada folder Prosedur Tersimpan dan pilih Refresh).
Perhatikan bahwa prosedur yang disimpan menerima SelectByProductID
sebagai parameter input dan menjalankan perintah @ProductID
yang kami masukkan di wizard.
ALTER PROCEDURE dbo.Products_SelectByProductID
(
@ProductID int
)
AS
SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Langkah 6: Membuat Kelas Lapisan Logika Bisnis
Sepanjang seri tutorial kami telah berusaha untuk mempertahankan arsitektur berlapis di mana Lapisan Presentasi melakukan semua panggilannya ke Business Logic Layer (BLL). Untuk mematuhi keputusan desain ini, pertama-tama kita perlu membuat kelas BLL untuk Himpunan Data Jenis baru sebelum kita dapat mengakses data produk dari Lapisan Presentasi.
Buat file kelas baru bernama ProductsBLLWithSprocs.vb
di folder ~/App_Code/BLL
dan tambahkan ke dalamnya kode berikut:
Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class ProductsBLLWithSprocs
Private _productsAdapter As ProductsTableAdapter = Nothing
Protected ReadOnly Property Adapter() As ProductsTableAdapter
Get
If _productsAdapter Is Nothing Then
_productsAdapter = New ProductsTableAdapter()
End If
Return _productsAdapter
End Get
End Property
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, True)> _
Public Function GetProducts() As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductByProductID(ByVal productID As Integer) _
As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductByProductID(productID)
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddProduct _
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean) _
As Boolean
' Create a new ProductRow instance
Dim products As New NorthwindWithSprocs.ProductsDataTable()
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Add the new product
products.AddProductsRow(product)
Dim rowsAffected As Integer = Adapter.Update(products)
' Return true if precisely one row was inserted, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateProduct
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean, ByVal productID As Integer) _
As Boolean
Dim products As NorthwindWithSprocs.ProductsDataTable = _
Adapter.GetProductByProductID(productID)
If products.Count = 0 Then
' no matching record found, return false
Return False
End If
Dim product As NorthwindWithSprocs.ProductsRow = products(0)
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Update the product record
Dim rowsAffected As Integer = Adapter.Update(product)
' Return true if precisely one row was updated, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function DeleteProduct(ByVal productID As Integer) As Boolean
Dim rowsAffected As Integer = Adapter.Delete(productID)
' Return true if precisely one row was deleted, otherwise false
Return rowsAffected = 1
End Function
End Class
Kelas ini meniru semantik kelas ProductsBLL
dari tutorial sebelumnya, tetapi menggunakan objek ProductsTableAdapter
dan ProductsDataTable
dari DataSet NorthwindWithSprocs
. Misalnya, daripada memiliki Imports NorthwindTableAdapters
pernyataan di awal file kelas seperti halnya ProductsBLL
, kelas ProductsBLLWithSprocs
menggunakan Imports NorthwindWithSprocsTableAdapters
. Demikian juga, ProductsDataTable
objek dan ProductsRow
yang digunakan dalam kelas ini diawali dengan NorthwindWithSprocs
namespace. Kelas ini ProductsBLLWithSprocs
menyediakan dua metode akses data, GetProducts
dan GetProductByProductID
, dan metode untuk menambahkan, memperbarui, dan menghapus satu instans produk.
Langkah 7: Bekerja dengan HimpunanNorthwindWithSprocs
Data dari Lapisan Presentasi
Pada titik ini kami telah membuat DAL yang menggunakan prosedur tersimpan untuk mengakses dan memodifikasi data database yang mendasar. Kami juga telah membangun BLL dasar dengan metode untuk mengambil semua produk atau produk tertentu bersama dengan metode untuk menambahkan, memperbarui, dan menghapus produk. Untuk membulatkan tutorial ini, mari kita buat halaman ASP.NET yang menggunakan kelas BLL s ProductsBLLWithSprocs
untuk menampilkan, memperbarui, dan menghapus rekaman.
NewSprocs.aspx
Buka halaman di AdvancedDAL
folder dan seret GridView dari Kotak Alat ke Perancang, beri nama Products
. Dari tag pintar GridView pilih untuk mengikatnya ke ObjectDataSource baru bernama ProductsDataSource
. Konfigurasikan ObjectDataSource untuk menggunakan kelas ProductsBLLWithSprocs
, seperti yang ditunjukkan dalam Gambar 22.
Gambar 22: Konfigurasikan ObjectDataSource untuk Menggunakan ProductsBLLWithSprocs
Kelas (Klik untuk melihat gambar ukuran penuh)
Daftar drop-down di tab SELECT memiliki dua opsi, GetProducts
dan GetProductByProductID
. Karena kami ingin menampilkan semua produk di GridView, pilih metode .GetProducts
Daftar drop-down di tab PERBARUI, SISIPKAN, dan HAPUS masing-masing hanya memiliki satu metode. Pastikan bahwa masing-masing daftar drop-down ini memiliki metode yang sesuai dipilih lalu klik Selesai.
Setelah wizard ObjectDataSource selesai, Visual Studio akan menambahkan BoundFields dan CheckBoxField ke GridView untuk bidang data produk. Aktifkan fitur pengeditan dan penghapusan bawaan GridView dengan mencentang opsi Aktifkan Pengeditan dan Aktifkan Penghapusan yang ada di tag pintar.
Gambar 23: Halaman Berisi GridView dengan Pengeditan dan Penghapusan Dukungan Diaktifkan (Klik untuk melihat gambar ukuran penuh)
Seperti yang sudah kita bahas dalam tutorial sebelumnya, ketika wizard ObjectDataSource selesai, Visual Studio mengatur properti OldValuesParameterFormatString
ke original_{0}. Ini perlu dikembalikan ke nilai {0} defaultnya agar fitur modifikasi data berfungsi dengan baik mengingat parameter yang diharapkan oleh metode di BLL kami. Oleh karena itu, pastikan untuk mengatur properti ke OldValuesParameterFormatString
{0} atau menghapus properti sama sekali dari sintaks deklaratif.
Setelah menyelesaikan wizard Konfigurasi Sumber Data, mengaktifkan pengeditan dan penghapusan dukungan di GridView, dan mengembalikan properti ObjectDataSource ke OldValuesParameterFormatString
nilai defaultnya, markup deklaratif halaman Anda akan terlihat mirip dengan yang berikut ini:
<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID"
SortExpression="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
SortExpression="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"
SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"
SortExpression="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"
SortExpression="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"
SortExpression="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
DeleteMethod="DeleteProduct" InsertMethod="AddProduct"
SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"
UpdateMethod="UpdateProduct">
<DeleteParameters>
<asp:Parameter Name="productID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
<asp:Parameter Name="productID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
</InsertParameters>
</asp:ObjectDataSource>
Pada titik ini kita dapat merapikan GridView dengan menyesuaikan antarmuka pengeditan untuk menyertakan validasi, membuat CategoryID
kolom dan SupplierID
dirender sebagai DropDownLists, dan sebagainya. Kami juga dapat menambahkan konfirmasi sisi klien ke tombol Hapus, dan saya mendorong Anda untuk meluangkan waktu untuk menerapkan peningkatan ini. Karena topik-topik ini telah dibahas dalam tutorial sebelumnya, namun, kami tidak akan membahasnya lagi di sini.
Terlepas dari apakah Anda meningkatkan GridView atau tidak, uji fitur inti halaman di browser. Seperti yang ditunjukkan Gambar 24, halaman mencantumkan produk dalam GridView yang menyediakan kemampuan pengeditan dan penghapusan per baris.
Gambar 24: Produk Dapat Dilihat, Diedit, dan Dihapus dari GridView (Klik untuk melihat gambar ukuran penuh)
Ringkasan
TableAdapters dalam DataSet Tertipe dapat mengakses data dari database menggunakan perintah SQL ad-hoc atau melalui prosedur tersimpan. Saat bekerja dengan prosedur tersimpan, Anda dapat menggunakan prosedur tersimpan yang sudah ada atau menginstruksikan wizard TableAdapter untuk membuat prosedur tersimpan baru berdasarkan kueri SELECT
. Dalam tutorial ini kami mengeksplorasi cara membuat prosedur tersimpan secara otomatis untuk kami.
Meskipun memiliki prosedur tersimpan yang dihasilkan secara otomatis membantu menghemat waktu, ada kasus tertentu di mana prosedur tersimpan yang dibuat oleh wizard tidak selaras dengan apa yang akan kita buat sendiri. Salah satu contohnya adalah prosedur tersimpan Products_Update
, yang mengharapkan parameter input @Original_ProductID
dan @ProductID
meskipun parameter @Original_ProductID
berlebihan.
Dalam banyak skenario, prosedur tersimpan mungkin sudah dibuat, atau kita mungkin ingin membuatnya secara manual sehingga memiliki tingkat kontrol yang lebih baik atas perintah prosedur tersimpan. Dalam kedua kasus, kami ingin menginstruksikan TableAdapter untuk menggunakan prosedur tersimpan yang ada untuk metodenya. Kita akan melihat cara mencapai ini di tutorial berikutnya.
Selamat Pemrograman!
Bacaan Lebih Lanjut
Untuk informasi selengkapnya tentang topik yang dibahas dalam tutorial ini, lihat sumber daya berikut:
- Membuat dan Memelihara Prosedur Tersimpan
- Prosedur Tersimpan: Gambaran Umum
- Membuat Prosedur Tersimpan
Tentang Penulis
Scott Mitchell, penulis tujuh buku ASP/ASP.NET dan pendiri 4GuysFromRolla.com, telah bekerja sama dengan teknologi Microsoft Web sejak 1998. Scott bekerja sebagai konsultan, pelatih, dan penulis independen. Buku terbarunya adalah Sams Teach Yourself ASP.NET 2.0 dalam 24 Jam. Dia dapat dijangkau di mitchell@4GuysFromRolla.com.
Ucapan terima kasih khusus kepada
Seri tutorial ini ditinjau oleh banyak peninjau yang bermanfaat. Peninjau utama untuk tutorial ini adalah Hilton Geisenow. Tertarik untuk meninjau artikel MSDN saya yang akan datang? Jika demikian, hubungi saya di mitchell@4GuysFromRolla.com.