Mengurutkan Data Halaman Kustom (VB)

oleh Scott Mitchell

Unduh PDF

Dalam tutorial sebelumnya, kami mempelajari cara menerapkan halaman kustom saat menyajikan data di halaman web. Dalam tutorial ini kita melihat cara memperluas contoh sebelumnya untuk menyertakan dukungan untuk mengurutkan penomoran halaman kustom.

Pendahuluan

Dibandingkan dengan pemanggilan halaman default, pemanggilan halaman kustom dapat meningkatkan performa dengan beberapa kali lipat saat memproses data, menjadikannya pilihan utama dalam implementasi paging ketika memproses sejumlah besar data. Menerapkan paging kustom lebih rumit dibandingkan dengan menerapkan paging default, terutama ketika menambahkan pengurutan ke dalamnya. Dalam tutorial ini kita akan memperluas contoh dari yang sebelumnya untuk menyertakan dukungan untuk pengurutan dan penomoran kustom.

Nota

Karena tutorial ini dibangun berdasarkan yang sebelumnya, sebelum mulai mengambil waktu sejenak untuk menyalin sintaks deklaratif dalam <asp:Content> elemen dari halaman web tutorial sebelumnya (EfficientPaging.aspx) dan menempelkannya di antara <asp:Content> elemen di SortParameter.aspx halaman. Lihat kembali ke Langkah 1 dari tutorial Menambahkan Kontrol Validasi ke Tutorial Mengedit dan Menyisipkan Antarmuka untuk diskusi yang lebih rinci tentang mereplikasi fungsionalitas satu halaman ASP.NET ke halaman lainnya.

Langkah 1: Mengeksam Ulang Teknik Penomor Kustom

Agar paging kustom berfungsi dengan baik, kita harus menerapkan beberapa teknik yang dapat secara efisien mengambil subset tertentu dari rekaman dengan parameter Indeks Baris Mulai dan Baris Maksimum. Ada beberapa teknik yang dapat digunakan untuk mencapai tujuan ini. Dalam tutorial sebelumnya, kami melihat cara mencapai hal ini menggunakan fungsi peringkat baru ROW_NUMBER() Microsoft SQL Server 2005. Singkatnya ROW_NUMBER() , fungsi peringkat menetapkan nomor baris ke setiap baris yang dikembalikan oleh kueri yang diberi peringkat berdasarkan urutan pengurutan tertentu. Subset rekaman yang sesuai kemudian diperoleh dengan mengembalikan bagian tertentu dari hasil bernomor. Kueri berikut mengilustrasikan cara menggunakan teknik ini untuk mengembalikan produk bernomor 11 hingga 20 saat memberi peringkat hasil yang diurutkan menurut abjad oleh ProductName:

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

Teknik ini berfungsi dengan baik untuk penomoran menggunakan urutan pengurutan tertentu (ProductName diurutkan menurut abjad, dalam hal ini), tetapi kueri perlu dimodifikasi untuk menampilkan hasil yang diurutkan menurut ekspresi pengurutan yang berbeda. Idealnya, kueri di atas dapat ditulis ulang untuk menggunakan parameter dalam OVER klausa, seperti:

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

Sayangnya, klausa terparameterisasi ORDER BY tidak diizinkan. Sebagai gantinya, kita harus membuat prosedur tersimpan yang menerima @sortExpression parameter input, tetapi menggunakan salah satu solusi berikut:

• Tulis kueri yang dikodekan secara permanen untuk setiap ekspresi pengurutan yang dapat digunakan; kemudian, gunakan IF/ELSE pernyataan T-SQL untuk menentukan kueri mana yang akan dijalankan.
• CASE Gunakan pernyataan untuk memberikan ekspresi dinamis ORDER BY berdasarkan @sortExpressio parameter input n; lihat bagian Digunakan untuk Mengurutkan Hasil Kueri secara Dinamis di Pernyataan T-SQL CASE untuk informasi selengkapnya.
• Buat kueri yang sesuai sebagai string dalam prosedur tersimpan lalu gunakan sp_executesql prosedur tersimpan sistem untuk menjalankan kueri dinamis.

Masing-masing solusi ini memiliki beberapa kelemahan. Opsi pertama tidak dapat dipertahankan seperti dua lainnya karena mengharuskan Anda membuat kueri untuk setiap ekspresi pengurutan yang mungkin. Oleh karena itu, jika nanti Anda memutuskan untuk menambahkan bidang baru yang dapat diurutkan ke GridView, Anda juga harus kembali dan memperbarui prosedur tersimpan. Pendekatan kedua memiliki beberapa kehalusan yang menimbulkan kekhawatiran performa saat mengurutkan berdasarkan kolom database non-string dan juga mengalami masalah pemeliharaan yang sama dengan yang pertama. Dan pilihan ketiga, yang menggunakan SQL dinamis, memperkenalkan risiko untuk serangan injeksi SQL jika penyerang dapat menjalankan prosedur tersimpan yang melewati nilai parameter input yang mereka pilih.

Meskipun tidak ada pendekatan ini yang sempurna, saya pikir opsi ketiga adalah yang terbaik dari ketiganya. Dengan penggunaan SQL dinamis, sistem ini menawarkan tingkat fleksibilitas yang tidak dimiliki oleh dua lainnya. Selain itu, serangan injeksi SQL hanya dapat dieksploitasi jika penyerang dapat menjalankan prosedur tersimpan yang melewati parameter input pilihannya. Karena DAL menggunakan kueri berparameter, ADO.NET akan melindungi parameter yang dikirim ke database melalui arsitektur, yang berarti bahwa kerentanan serangan injeksi SQL hanya ada jika penyerang dapat langsung menjalankan prosedur tersimpan.

Untuk menerapkan fungsionalitas ini, buat prosedur tersimpan baru di database Northwind bernama GetProductsPagedAndSorted. Prosedur tersimpan ini harus menerima tiga parameter input: @sortExpression, parameter input jenis nvarchar(100) yang menentukan bagaimana hasilnya harus diurutkan dan disuntikkan langsung setelah ORDER BY teks dalam OVER klausul; dan @startRowIndex@maximumRows, parameter input dua bilangan bulat yang sama dari GetProductsPaged prosedur tersimpan yang diperiksa dalam tutorial sebelumnya. Buat prosedur tersimpan GetProductsPagedAndSorted menggunakan skrip berikut:

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
                   UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
                   CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                         QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                         ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

Prosedur tersimpan dimulai dengan memastikan bahwa nilai untuk @sortExpression parameter telah ditentukan. Jika hilang, hasilnya diurutkan berdasarkan ProductID. Selanjutnya, kueri SQL dinamis dibuat. Perhatikan bahwa kueri SQL dinamis di sini sedikit berbeda dari kueri kami sebelumnya yang digunakan untuk mengambil semua baris dari tabel Produk. Dalam contoh sebelumnya, kami memperoleh setiap kategori terkait produk dan nama pemasok menggunakan subkueri. Keputusan ini dibuat kembali dalam tutorial Membuat Lapisan Akses Data dan dilakukan sebagai pengganti penggunaan JOIN s karena TableAdapter tidak dapat secara otomatis membuat metode sisipan, pembaruan, dan penghapusan terkait untuk kueri tersebut. Namun, prosedur tersimpan GetProductsPagedAndSorted harus menggunakan JOIN untuk menyusun hasil berdasarkan kategori atau nama pemasok.

Kueri dinamis ini dibangun dengan menggabungkan bagian kueri statis dan parameter @sortExpression, @startRowIndex, dan @maximumRows. Karena @startRowIndex dan @maximumRows merupakan parameter bilangan bulat, parameter tersebut harus dikonversi menjadi nvarchars agar dapat digabungkan dengan benar. Setelah kueri SQL dinamis ini dibuat, kueri dijalankan melalui sp_executesql.

Luangkan waktu sejenak untuk menguji prosedur tersimpan ini dengan nilai yang berbeda untuk @sortExpressionparameter, @startRowIndexparameter, dan @maximumRowsparameter. Dari Server Explorer, klik kanan pada nama prosedur tersimpan dan pilih Jalankan. Ini akan memunculkan kotak dialog Jalankan Prosedur Tersimpan tempat Anda dapat memasukkan parameter input (lihat Gambar 1). Untuk mengurutkan hasil menurut nama kategori, gunakan CategoryName untuk @sortExpression nilai parameter; untuk mengurutkan menurut nama perusahaan pemasok, gunakan CompanyName. Setelah memberikan nilai parameter, klik OK. Hasilnya ditampilkan di jendela Output. Gambar 2 menunjukkan hasil saat mengembalikan produk yang berada di peringkat 11 hingga 20 saat memesan berdasarkan UnitPrice urutan turun.

Gambar 1: Coba Nilai Yang Berbeda untuk Prosedur Tersimpan s Tiga Parameter Input

Gambar 2: Hasil Prosedur Tersimpan Ditampilkan di Jendela Output (Klik untuk melihat gambar ukuran penuh)

Nota

Saat memberi peringkat hasil berdasarkan kolom yang ditentukan ORDER BY dalam OVER klausa, SQL Server harus mengurutkan hasilnya. Ini adalah operasi yang cepat jika terdapat indeks berkelompok pada kolom yang menjadi dasar pengurutan hasil, atau jika ada indeks yang meliputi, tetapi bisa lebih mahal jika tidak. Untuk meningkatkan performa untuk kueri yang cukup besar, pertimbangkan untuk menambahkan indeks non-kluster untuk kolom tempat hasil diurutkan. Lihat Fungsi dan Performa Peringkat di SQL Server 2005 untuk detail selengkapnya.

Langkah 2: Menambah Akses Data dan Lapisan Logika Bisnis

GetProductsPagedAndSorted Dengan prosedur tersimpan yang dibuat, langkah kami selanjutnya adalah menyediakan sarana untuk menjalankan prosedur tersimpan tersebut melalui arsitektur aplikasi kami. Ini memerlukan penambahan metode yang sesuai ke DAL dan BLL. Mari kita mulai dengan menambahkan metode ke DAL. Buka Northwind.xsd Himpunan Data Bertipe, klik kanan pada ProductsTableAdapter, dan pilih opsi Tambah Kueri dari menu konteks. Seperti yang kami lakukan dalam tutorial sebelumnya, kami ingin mengonfigurasi metode DAL baru ini untuk menggunakan prosedur tersimpan yang ada - GetProductsPagedAndSorted, dalam hal ini. Mulailah dengan menunjukkan bahwa Anda ingin metode TableAdapter baru menggunakan prosedur tersimpan yang sudah ada.

Gambar 3: Pilih untuk Menggunakan Prosedur Tersimpan yang Ada

Untuk menentukan prosedur tersimpan yang akan digunakan, pilih GetProductsPagedAndSorted prosedur tersimpan dari daftar drop-down di layar berikutnya.

Gambar 4: Gunakan Prosedur Tersimpan GetProductsPagedAndSorted

Prosedur tersimpan ini mengembalikan sekumpulan rekaman sebagai hasilnya sehingga, di layar berikutnya, menunjukkan bahwa ia mengembalikan data tabular.

Gambar 5: Menunjukkan bahwa Prosedur Tersimpan Mengembalikan Data Tabular

Terakhir, buat metode DAL yang menggunakan pola Mengisi DataTable dan Mengembalikan DataTable, dengan menamai metode masing-masing FillPagedAndSorted dan GetProductsPagedAndSorted.

Gambar 6: Pilih Nama Metode

Sekarang setelah kita memperpanjang DAL, kita siap untuk beralih ke BLL. ProductsBLL Buka file kelas dan tambahkan metode baru, GetProductsPagedAndSorted. Metode ini perlu menerima tiga parameter masukan sortExpression, startRowIndex, dan maximumRows serta harus langsung memanggil metode GetProductsPagedAndSorted dari DAL seperti ini:

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
    ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function

Langkah 3: Mengonfigurasi ObjectDataSource untuk Meneruskan Parameter SortExpression

Setelah menambah DAL dan BLL untuk menyertakan metode yang menggunakan GetProductsPagedAndSorted prosedur tersimpan, yang tersisa adalah mengonfigurasi ObjectDataSource di SortParameter.aspx halaman untuk menggunakan metode BLL baru dan meneruskan SortExpression parameter berdasarkan kolom yang diminta pengguna untuk mengurutkan hasilnya.

Mulailah dengan mengubah ObjectDataSource dari SelectMethodGetProductsPaged ke GetProductsPagedAndSorted. Ini dapat dilakukan melalui wizard Konfigurasi Sumber Data, dari jendela Properti, atau langsung melalui sintaks deklaratif. Selanjutnya, kita perlu memberikan nilai untuk properti ObjectDataSource sSortParameterName. Jika properti ini diatur, ObjectDataSource mencoba meneruskan properti SortExpression milik GridView ke SelectMethod. Secara khusus, ObjectDataSource mencari parameter input yang namanya sama dengan nilai SortParameterName properti. Karena metode BLL s GetProductsPagedAndSorted memiliki parameter input ekspresi sortir bernama sortExpression, atur properti ObjectDataSource s SortExpression ke sortExpression .

Setelah membuat dua perubahan ini, sintaks deklaratif ObjectDataSource akan terlihat mirip dengan yang berikut ini:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

Nota

Seperti tutorial sebelumnya, pastikan bahwa ObjectDataSource tidak menyertakan parameter input sortExpression, startRowIndex, atau maximumRows dalam koleksi SelectParameters-nya.

Untuk mengaktifkan pengurutan di GridView, cukup centang kotak centang Aktifkan Pengurutan di tag pintar GridView, yang menetapkan properti GridView ke AllowSortingtrue dan yang menyebabkan teks header untuk setiap kolom ditampilkan sebagai LinkButton. Saat pengguna akhir mengklik salah satu tombol tautan di header, postback akan terjadi dan langkah-langkah berikut berlangsung:

1. GridView memperbarui propertinyaSortExpression ke nilai SortExpression bidang yang tautan headernya diklik
2. ObjectDataSource memanggil metode BLL s GetProductsPagedAndSorted , meneruskan properti GridView s SortExpression sebagai nilai untuk parameter input metode s sortExpression (bersama dengan nilai parameter yang sesuai startRowIndex dan maximumRows input)
3. BLL memanggil metode DAL s GetProductsPagedAndSorted
4. DAL mengeksekusi prosedur tersimpan GetProductsPagedAndSorted, meneruskan parameter @sortExpression (bersama dengan nilai-nilai parameter input @startRowIndex dan @maximumRows)
5. Prosedur tersimpan mengembalikan subset data yang sesuai ke BLL, yang mengembalikannya ke ObjectDataSource; data ini kemudian terikat ke GridView, dirender ke HTML, dan dikirimkan ke pengguna akhir

Gambar 7 memperlihatkan halaman pertama hasil saat diurutkan menurut UnitPrice urutan naik.

Gambar 7: Hasilnya Diurutkan menurut UnitPrice (Klik untuk melihat gambar ukuran penuh)

Meskipun implementasi saat ini dapat mengurutkan hasil dengan benar berdasarkan nama produk, nama kategori, kuantitas per unit, dan harga satuan, mencoba memesan hasil dengan nama pemasok menghasilkan pengecualian runtime (lihat Gambar 8).

Gambar 8: Mencoba Mengurutkan Hasil berdasarkan Hasil Pemasok dalam Pengecualian Runtime Berikut

Pengecualian ini terjadi karena SortExpression dari BoundField SupplierName GridView diatur ke SupplierName. Namun, nama pemasok dalam Suppliers tabel sebenarnya disebut CompanyName kami telah diberi nama kolom ini sebagai SupplierName. Namun, klausa yang digunakan oleh fungsi OVERROW_NUMBER() tidak dapat menggunakan alias dan harus menggunakan nama kolom aktual. Oleh karena itu, ubah SupplierName BoundField dari SortExpression SupplierName menjadi CompanyName (lihat Gambar 9). Seperti yang ditunjukkan Gambar 10, setelah perubahan ini, hasilnya dapat diurutkan oleh pemasok.

Gambar 9: Ubah SupplierName BoundField s SortExpression menjadi CompanyName

Gambar 10: Hasil Sekarang Dapat Diurutkan menurut Pemasok (Klik untuk melihat gambar ukuran penuh)

Ringkasan

Implementasi penomoran kustom yang kami periksa dalam tutorial sebelumnya mengharuskan urutan di mana hasilnya harus diurutkan ditentukan pada waktu desain. Singkatnya, ini berarti bahwa implementasi penomoran kustom yang kami terapkan tidak dapat, pada saat yang sama, memberikan kemampuan pengurutan. Dalam tutorial ini kita mengatasi batasan ini dengan memperluas prosedur tersimpan dari yang pertama untuk menyertakan @sortExpression parameter input yang hasilnya dapat diurutkan.

Setelah membuat prosedur tersimpan ini dan membuat metode baru di DAL dan BLL, kami dapat menerapkan GridView yang menawarkan pengurutan dan penomoran kustom dengan mengonfigurasi ObjectDataSource untuk meneruskan properti GridView saat ini SortExpression ke BLL SelectMethod.

Selamat Pemrograman!

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 Carlos Santos. Tertarik untuk meninjau artikel MSDN saya yang akan datang? Jika demikian, hubungi saya di mitchell@4GuysFromRolla.com.

SebelumnyaBerikutnya