Membuat Prosedur Tersimpan dan Fungsi yang Ditentukan Pengguna dengan Kode Terkelola (C#)

oleh Scott Mitchell

Unduh PDF

Microsoft SQL Server 2005 terintegrasi dengan .NET Common Language Runtime untuk memungkinkan pengembang membuat objek database melalui kode terkelola. Tutorial ini menunjukkan cara membuat prosedur tersimpan terkelola dan fungsi yang ditentukan pengguna terkelola dengan kode Visual Basic atau C# Anda. Kami juga melihat bagaimana edisi Visual Studio ini memungkinkan Anda untuk men-debug objek database terkelola tersebut.

Pendahuluan

Database seperti Microsoft s SQL Server 2005 menggunakan Transact-Structured Query Language (T-SQL) untuk menyisipkan, memodifikasi, dan mengambil data. Sebagian besar sistem database termasuk konstruksi untuk mengelompokkan serangkaian pernyataan SQL yang kemudian dapat dijalankan sebagai unit tunggal yang dapat digunakan kembali. Prosedur tersimpan adalah salah satu contohnya. Yang lain adalah User-Defined Functions (UDF), konstruksi yang akan kita periksa secara lebih rinci di Langkah 9.

Pada intinya, SQL dirancang untuk bekerja dengan sekumpulan data. Pernyataan SELECT, UPDATE, dan DELETE secara inheren berlaku untuk semua rekaman dalam tabel yang sesuai dan hanya dibatasi oleh klausulnya WHERE . Namun ada banyak fitur bahasa yang dirancang untuk bekerja dengan satu catatan sekaligus dan untuk memanipulasi data skalar. CURSOR s memungkinkan sekumpulan rekaman diulang satu per satu. Fungsi manipulasi string seperti LEFT, CHARINDEX, dan PATINDEX bekerja dengan data skalar. SQL juga menyertakan pernyataan alur kontrol seperti IF dan WHILE.

Sebelum Microsoft SQL Server 2005, prosedur tersimpan dan UDF hanya dapat didefinisikan sebagai kumpulan pernyataan T-SQL. Namun, SQL Server 2005 dirancang untuk menyediakan integrasi dengan Common Language Runtime (CLR), yang merupakan runtime yang digunakan oleh semua rakitan .NET. Akibatnya, prosedur tersimpan dan UDF dalam database SQL Server 2005 dapat dibuat menggunakan kode terkelola. Artinya, Anda dapat membuat prosedur tersimpan atau UDF sebagai metode di kelas C#. Ini memungkinkan prosedur tersimpan dan UDF ini untuk menggunakan fungsionalitas dalam .NET Framework dan dari kelas kustom Anda sendiri.

Dalam tutorial ini kami akan memeriksa cara membuat prosedur tersimpan terkelola dan Fungsi yang Ditentukan Pengguna dan cara mengintegrasikannya ke dalam database Northwind kami. Mari kita mulai!

Catatan

Objek database terkelola menawarkan beberapa keuntungan daripada rekan-rekan SQL mereka. Kekayaan bahasa dan keakraban serta kemampuan untuk menggunakan kembali kode dan logika yang ada adalah keuntungan utama. Tetapi objek database terkelola cenderung kurang efisien saat bekerja dengan set data yang tidak melibatkan banyak logika prosedural. Untuk diskusi yang lebih menyeluruh tentang keuntungan menggunakan kode terkelola versus T-SQL, lihat Keuntungan Menggunakan Kode Terkelola untuk Membuat Objek Database.

Langkah 1: Memindahkan Database Northwind Keluar dari App_Data

Semua tutorial kami sejauh ini telah menggunakan file database Microsoft SQL Server 2005 Express Edition di folder aplikasi App_Data web. Menempatkan database dalam mendistribusikan yang disederhanakan App_Data dan menjalankan tutorial ini karena semua file terletak dalam satu direktori dan tidak memerlukan langkah-langkah konfigurasi tambahan untuk menguji tutorial.

Namun, untuk tutorial ini, mari kita pindahkan database Northwind dari App_Data dan secara eksplisit mendaftarkannya dengan instans database SQL Server 2005 Express Edition. Meskipun kita dapat melakukan langkah-langkah untuk tutorial ini dengan database di App_Data folder, sejumlah langkah dibuat jauh lebih sederhana dengan secara eksplisit mendaftarkan database dengan instans database SQL Server 2005 Express Edition.

Unduhan untuk tutorial ini memiliki dua file database - NORTHWND.MDF dan NORTHWND_log.LDF - ditempatkan dalam folder bernama DataFiles. Jika Anda mengikuti implementasi tutorial Anda sendiri, tutup Visual Studio dan pindahkan NORTHWND.MDF file dan NORTHWND_log.LDF dari folder situs web App_Data ke folder di luar situs web. Setelah file database dipindahkan ke folder lain, kita perlu mendaftarkan database Northwind dengan instans database SQL Server 2005 Express Edition. Ini dapat dilakukan dari SQL Server Management Studio. Jika Anda memiliki SQL Server 2005 Edisi non-Ekspres yang terinstal di komputer Anda, kemungkinan Anda sudah menginstal Management Studio. Jika Anda hanya memiliki SQL Server 2005 Express Edition di komputer Anda, luangkan waktu sejenak untuk mengunduh dan menginstal Microsoft SQL Server Management Studio.

Luncurkan SQL Server Management Studio. Seperti yang ditunjukkan Gambar 1, Management Studio dimulai dengan menanyakan server apa yang akan disambungkan. Masukkan localhost\SQLExpress untuk nama server, pilih Autentikasi Windows di daftar drop-down Autentikasi, dan klik Sambungkan.

Gambar 1: Sambungkan ke Instans Database yang Sesuai

Setelah Anda tersambung, jendela Object Explorer akan mencantumkan informasi tentang instans database SQL Server 2005 Express Edition, termasuk database, informasi keamanan, opsi manajemen, dan sebagainya.

Kita perlu melampirkan database Northwind di DataFiles folder (atau di mana pun Anda mungkin telah memindahkannya) ke instans database SQL Server 2005 Express Edition. Klik kanan pada folder Database dan pilih opsi Lampirkan dari menu konteks. Ini akan memunculkan kotak dialog Lampirkan Database. Klik tombol Tambahkan, telusuri paling detail ke file yang sesuai NORTHWND.MDF , dan klik OK. Pada titik ini layar Anda akan terlihat mirip dengan Gambar 2.

Gambar 2: Sambungkan ke Instans Database yang Sesuai (Klik untuk melihat gambar ukuran penuh)

Catatan

Saat menyambungkan ke instans SQL Server 2005 Express Edition melalui Management Studio, kotak dialog Lampirkan Database tidak memungkinkan Anda menelusuri paling detail ke direktori profil pengguna, seperti Dokumen Saya. Oleh karena itu, pastikan untuk menempatkan NORTHWND.MDF file dan NORTHWND_log.LDF di direktori profil non-pengguna.

Klik tombol OK untuk melampirkan database. Kotak dialog Lampirkan Database akan ditutup dan Object Explorer sekarang harus mencantumkan database yang baru saja dilampirkan. Kemungkinan database Northwind memiliki nama seperti 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Ganti nama database menjadi Northwind dengan mengklik kanan database dan memilih Ganti Nama.

Gambar 3: Ganti Nama Database menjadi Northwind

Langkah 2: Membuat Solusi Baru dan Proyek SQL Server di Visual Studio

Untuk membuat prosedur tersimpan terkelola atau UDF di SQL Server 2005, kami akan menulis prosedur tersimpan dan logika UDF sebagai kode C# di kelas. Setelah kode ditulis, kita perlu mengkompilasi kelas ini ke dalam rakitan ( .dll file), mendaftarkan rakitan dengan database SQL Server, lalu membuat prosedur tersimpan atau objek UDF dalam database yang menunjuk ke metode yang sesuai dalam perakitan. Semua langkah ini dapat dilakukan secara manual. Kita dapat membuat kode di editor teks apa pun, mengkompilasinya dari baris perintah menggunakan pengkompilasi C# (csc.exe), mendaftarkannya dengan database menggunakan CREATE ASSEMBLY perintah atau dari Management Studio, dan menambahkan prosedur tersimpan atau objek UDF melalui cara yang sama. Untungnya, versi Visual Studio Versi Professional dan Team Systems menyertakan jenis Proyek SQL Server yang mengotomatiskan tugas-tugas ini. Dalam tutorial ini kita akan berjalan menggunakan jenis Proyek SQL Server untuk membuat prosedur tersimpan terkelola dan UDF.

Catatan

Jika Anda menggunakan Visual Web Developer atau Visual Studio edisi Standar, maka Anda harus menggunakan pendekatan manual sebagai gantinya. Langkah 13 menyediakan instruksi terperinci untuk melakukan langkah-langkah ini secara manual. Saya mendorong Anda untuk membaca Langkah 2 hingga 12 sebelum membaca Langkah 13 karena langkah-langkah ini mencakup instruksi konfigurasi SQL Server penting yang harus diterapkan terlepas dari versi Visual Studio apa yang Anda gunakan.

Mulailah dengan membuka Visual Studio. Dari menu File, pilih Proyek Baru untuk menampilkan kotak dialog Proyek Baru (lihat Gambar 4). Telusuri paling detail ke jenis proyek Database lalu, dari Templat yang tercantum di sebelah kanan, pilih untuk membuat Proyek SQL Server baru. Saya telah memilih untuk memberi nama proyek ManagedDatabaseConstructs ini dan menempatkannya dalam Solusi bernama Tutorial75.

Gambar 4: Buat Proyek SQL Server Baru (Klik untuk melihat gambar ukuran penuh)

Klik tombol OK dalam kotak dialog Proyek Baru untuk membuat Solusi dan Proyek SQL Server.

Proyek SQL Server terkait dengan database tertentu. Akibatnya, setelah membuat Proyek SQL Server baru, kami segera diminta untuk menentukan informasi ini. Gambar 5 memperlihatkan kotak dialog Referensi Database Baru yang telah diisi untuk menunjuk ke database Northwind yang kami daftarkan di instans database Edisi Ekspres SQL Server 2005 kembali di Langkah 1.

Gambar 5: Kaitkan Proyek SQL Server dengan Database Northwind

Untuk men-debug prosedur tersimpan terkelola dan UDF yang akan kita buat dalam proyek ini, kita perlu mengaktifkan dukungan debugging SQL/CLR untuk koneksi. Setiap kali mengaitkan Proyek SQL Server dengan database baru (seperti yang kami lakukan di Gambar 5), Visual Studio bertanya kepada kami apakah kami ingin mengaktifkan penelusuran kesalahan SQL/CLR pada koneksi (lihat Gambar 6). Klik Ya.

Gambar 6: Aktifkan Penelusuran Kesalahan SQL/CLR

Pada titik ini Proyek SQL Server baru telah ditambahkan ke Solusi. Ini berisi folder bernama Test Scripts dengan file bernama Test.sql, yang digunakan untuk men-debug objek database terkelola yang dibuat dalam proyek. Kita akan melihat debugging di Langkah 12.

Kita sekarang dapat menambahkan prosedur tersimpan terkelola baru dan UDF ke proyek ini, tetapi sebelum kita membiarkan terlebih dahulu menyertakan aplikasi web kita yang ada dalam Solusi. Dari menu File pilih opsi Tambahkan dan pilih Situs Web yang Sudah Ada. Telusuri ke folder situs web yang sesuai dan klik OK. Seperti yang ditunjukkan Gambar 7, ini akan memperbarui Solusi untuk menyertakan dua proyek: situs web dan ManagedDatabaseConstructs Proyek SQL Server.

Gambar 7: Penjelajah Solusi Sekarang Mencakup Dua Proyek

Nilai NORTHWNDConnectionString dalam Web.config saat ini mereferensikan NORTHWND.MDF file dalam App_Data folder. Karena kami menghapus database ini dari App_Data dan secara eksplisit mendaftarkannya di instans database SQL Server 2005 Express Edition, kita perlu memperbarui NORTHWNDConnectionString nilai secara sesuai. Web.config Buka file di situs web dan ubah NORTHWNDConnectionString nilainya sehingga string koneksi berbunyi: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Setelah perubahan ini, bagian Anda <connectionStrings> di Web.config akan terlihat mirip dengan yang berikut ini:

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Catatan

Seperti yang dibahas dalam tutorial sebelumnya, saat men-debug objek SQL Server dari aplikasi klien, seperti situs web ASP.NET, kita perlu menonaktifkan pengumpulan koneksi. string koneksi yang ditunjukkan di atas menonaktifkan pengumpulan koneksi ( Pooling=false ). Jika Anda tidak berencana untuk men-debug prosedur tersimpan terkelola dan UDF dari situs web ASP.NET, aktifkan pengumpulan koneksi.

Langkah 3: Membuat Prosedur Tersimpan Terkelola

Untuk menambahkan prosedur tersimpan terkelola ke database Northwind, pertama-tama kita perlu membuat prosedur tersimpan sebagai metode dalam Proyek SQL Server. Dari Penjelajah Solusi, klik kanan pada ManagedDatabaseConstructs nama proyek dan pilih untuk menambahkan item baru. Ini akan menampilkan kotak dialog Tambahkan Item Baru, yang mencantumkan tipe objek database terkelola yang dapat ditambahkan ke proyek. Seperti yang ditunjukkan Gambar 8, ini termasuk prosedur tersimpan dan Fungsi yang Ditentukan Pengguna, antara lain.

Mari kita mulai dengan menambahkan prosedur tersimpan yang hanya mengembalikan semua produk yang telah dihentikan. Beri nama file GetDiscontinuedProducts.csprosedur tersimpan baru .

Gambar 8: Tambahkan Prosedur Tersimpan Baru Bernama GetDiscontinuedProducts.cs (Klik untuk melihat gambar ukuran penuh)

Ini akan membuat file kelas C# baru dengan konten berikut:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

Perhatikan bahwa prosedur tersimpan diimplementasikan sebagai static metode dalam partial file kelas bernama StoredProcedures. Selain itu, GetDiscontinuedProducts metode ini dihiasi dengan SqlProcedure attribute, yang menandai metode sebagai prosedur tersimpan.

Kode berikut membuat SqlCommand objek dan mengaturnya CommandText ke SELECT kueri yang mengembalikan semua kolom dari Products tabel untuk produk yang bidangnya Discontinued sama dengan 1. Kemudian menjalankan perintah dan mengirim hasilnya kembali ke aplikasi klien. Tambahkan kode ini ke GetDiscontinuedProducts metode .

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = 
      @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
               QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
               ReorderLevel, Discontinued
        FROM Products 
        WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

Semua objek database terkelola memiliki akses ke SqlContext objek yang mewakili konteks pemanggil. SqlContext memberikan akses ke objek SqlPipe melalui properti Pipe. Objek ini SqlPipe digunakan untuk feri informasi antara database SQL Server dan aplikasi panggilan. Seperti namanya, ExecuteAndSend metode menjalankan objek yang diteruskan SqlCommand dan mengirim hasilnya kembali ke aplikasi klien.

Catatan

Objek database terkelola paling cocok untuk prosedur tersimpan dan UDF yang menggunakan logika prosedural daripada logika berbasis set. Logika prosedural melibatkan bekerja dengan sekumpulan data berdasarkan baris demi baris atau bekerja dengan data skalar. Metode yang GetDiscontinuedProducts baru saja kita buat, bagaimanapun, tidak melibatkan logika prosedural. Oleh karena itu, idealnya akan diimplementasikan sebagai prosedur tersimpan T-SQL. Ini diterapkan sebagai prosedur tersimpan terkelola untuk menunjukkan langkah-langkah yang diperlukan untuk membuat dan menyebarkan prosedur tersimpan terkelola.

Langkah 4: Menyebarkan Prosedur Tersimpan Terkelola

Dengan kode ini selesai, kami siap untuk menyebarkannya ke database Northwind. Menyebarkan Proyek SQL Server mengkompilasi kode ke dalam rakitan, mendaftarkan rakitan dengan database, dan membuat objek yang sesuai dalam database, menautkannya ke metode yang sesuai dalam perakitan. Kumpulan tugas yang tepat yang dilakukan oleh opsi Sebarkan dieja dengan lebih tepat di Langkah 13. Klik kanan pada ManagedDatabaseConstructs nama proyek di Penjelajah Solusi dan pilih opsi Sebarkan. Namun, penyebaran gagal dengan kesalahan berikut: Sintaks yang salah di dekat 'EXTERNAL'. Anda mungkin perlu mengatur tingkat kompatibilitas database saat ini ke nilai yang lebih tinggi untuk mengaktifkan fitur ini. Lihat bantuan untuk prosedur sp_dbcmptleveltersimpan .

Pesan kesalahan ini terjadi saat mencoba mendaftarkan rakitan dengan database Northwind. Untuk mendaftarkan rakitan dengan database SQL Server 2005, tingkat kompatibilitas database harus diatur ke 90. Secara default, database SQL Server 2005 baru memiliki tingkat kompatibilitas 90. Namun, database yang dibuat menggunakan Microsoft SQL Server 2000 memiliki tingkat kompatibilitas default 80. Karena database Northwind awalnya adalah database Microsoft SQL Server 2000, tingkat kompatibilitasnya saat ini diatur ke 80 dan oleh karena itu perlu ditingkatkan menjadi 90 untuk mendaftarkan objek database terkelola.

Untuk memperbarui tingkat kompatibilitas database, buka jendela Kueri Baru di Management Studio dan masukkan:

exec sp_dbcmptlevel 'Northwind', 90

Klik ikon Jalankan di Toolbar untuk menjalankan kueri di atas.

Gambar 9: Perbarui Tingkat Kompatibilitas Database Northwind (Klik untuk melihat gambar ukuran penuh)

Setelah memperbarui tingkat kompatibilitas, sebarkan ulang Proyek SQL Server. Kali ini penyebaran harus selesai tanpa kesalahan.

Kembali ke SQL Server Management Studio, klik kanan pada database Northwind di Object Explorer, dan pilih Refresh. Selanjutnya, telusuri paling detail folder Programmability lalu perluas folder Assemblies. Seperti yang ditunjukkan Gambar 10, database Northwind sekarang menyertakan rakitan yang dihasilkan oleh ManagedDatabaseConstructs proyek.

Gambar 10: ManagedDatabaseConstructs Rakitan Sekarang Terdaftar di Database Northwind

Perluas juga folder Prosedur Tersimpan. Di sana Anda akan melihat prosedur tersimpan bernama GetDiscontinuedProducts. Prosedur tersimpan ini dibuat oleh proses penyebaran dan menunjuk ke GetDiscontinuedProducts metode dalam perakitan ManagedDatabaseConstructs . Ketika prosedur tersimpan GetDiscontinuedProducts dijalankan, pada gilirannya, menjalankan GetDiscontinuedProducts metode . Karena ini adalah prosedur tersimpan terkelola yang tidak dapat diedit melalui Management Studio (karenanya ikon kunci di samping nama prosedur tersimpan).

Gambar 11: GetDiscontinuedProducts Prosedur Tersimpan Tercantum di Foldernya

Masih ada satu rintangan lagi yang harus kita atasi sebelum kita dapat memanggil prosedur tersimpan terkelola: database dikonfigurasi untuk mencegah eksekusi kode terkelola. Verifikasi ini dengan membuka jendela kueri baru dan menjalankan prosedur tersimpan GetDiscontinuedProducts . Anda akan menerima pesan kesalahan berikut: Eksekusi kode pengguna di .NET Framework dinonaktifkan. Aktifkan opsi konfigurasi yang diaktifkan clr.

Untuk memeriksa informasi konfigurasi database Northwind, masukkan dan jalankan perintah exec sp_configure di jendela kueri. Ini menunjukkan bahwa pengaturan yang diaktifkan clr saat ini diatur ke 0.

Gambar 12: Pengaturan yang diaktifkan clr Saat ini Diatur ke 0 (Klik untuk melihat gambar ukuran penuh)

Perhatikan bahwa setiap pengaturan konfigurasi di Gambar 12 memiliki empat nilai yang tercantum dengannya: nilai minimum dan maksimum serta nilai konfigurasi dan eksekusi. Untuk memperbarui nilai konfigurasi untuk pengaturan yang diaktifkan clr, jalankan perintah berikut:

exec sp_configure 'clr enabled', 1

Jika Anda menjalankan exec sp_configure kembali, Anda akan melihat bahwa pernyataan di atas memperbarui nilai konfigurasi pengaturan yang diaktifkan clr menjadi 1, tetapi nilai eksekusi masih diatur ke 0. Agar perubahan konfigurasi ini mempengaruhi kita perlu menjalankan RECONFIGURE perintah, yang akan mengatur nilai eksekusi ke nilai konfigurasi saat ini. Cukup masukkan RECONFIGURE di jendela kueri dan klik ikon Jalankan di Toolbar. Jika Anda menjalankan exec sp_configure sekarang, Anda akan melihat nilai 1 untuk konfigurasi pengaturan yang diaktifkan clr dan menjalankan nilai.

Dengan konfigurasi yang diaktifkan clr selesai, kami siap untuk menjalankan prosedur tersimpan terkelola GetDiscontinuedProducts . Di jendela kueri masukkan dan jalankan perintah execGetDiscontinuedProducts. Memanggil prosedur tersimpan menyebabkan kode terkelola yang sesuai dalam metode dijalankan GetDiscontinuedProducts . Kode ini mengeluarkan SELECT kueri untuk mengembalikan semua produk yang dihentikan dan mengembalikan data ini ke aplikasi panggilan, yaitu SQL Server Management Studio dalam instans ini. Management Studio menerima hasil ini dan menampilkannya di jendela Hasil.

Gambar 13: GetDiscontinuedProducts Prosedur Tersimpan Mengembalikan Semua Produk yang Dihentikan (Klik untuk melihat gambar ukuran penuh)

Langkah 5: Membuat Prosedur Tersimpan Terkelola yang Menerima Parameter Input

Banyak kueri dan prosedur tersimpan yang telah kami buat di seluruh tutorial ini telah menggunakan parameter. Misalnya, dalam tutorial Membuat Prosedur Tersimpan Baru untuk TableAdapters s TableAdapters Typed DataSet , kami membuat prosedur tersimpan bernama GetProductsByCategoryID yang menerima parameter input bernama @CategoryID. Prosedur tersimpan kemudian mengembalikan semua produk yang bidangnya CategoryID cocok dengan nilai parameter yang disediakan @CategoryID .

Untuk membuat prosedur tersimpan terkelola yang menerima parameter input, cukup tentukan parameter tersebut dalam definisi metode. Untuk mengilustrasikan ini, mari kita tambahkan prosedur tersimpan terkelola lain ke ManagedDatabaseConstructs proyek bernama GetProductsWithPriceLessThan. Prosedur tersimpan terkelola ini akan menerima parameter input yang menentukan harga dan akan mengembalikan semua produk yang bidangnya UnitPrice kurang dari nilai parameter.

Untuk menambahkan prosedur tersimpan baru ke proyek, klik kanan pada ManagedDatabaseConstructs nama proyek dan pilih untuk menambahkan prosedur tersimpan baru. Beri nama file GetProductsWithPriceLessThan.cs. Seperti yang kita lihat di Langkah 3, ini akan membuat file kelas C# baru dengan metode bernama GetProductsWithPriceLessThan ditempatkan dalam partial kelas StoredProcedures.

GetProductsWithPriceLessThan Perbarui definisi metode sehingga menerima SqlMoney parameter input bernama price dan menulis kode untuk menjalankan dan mengembalikan hasil kueri:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText =
          @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                   QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                   ReorderLevel, Discontinued
            FROM Products
            WHERE UnitPrice < @MaxPrice";
    myCommand.Parameters.AddWithValue("@MaxPrice", price);
    // Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand);
}

Definisi GetProductsWithPriceLessThan metode dan kode sangat menyerupai definisi dan kode metode yang GetDiscontinuedProducts dibuat di Langkah 3. Satu-satunya perbedaan adalah bahwa GetProductsWithPriceLessThan metode menerima sebagai parameter input (price), SqlCommand kueri s menyertakan parameter (@MaxPrice), dan parameter ditambahkan ke SqlCommand koleksi s Parameters adalah dan menetapkan nilai price variabel.

Setelah menambahkan kode ini, sebarkan ulang Proyek SQL Server. Selanjutnya, kembali ke SQL Server Management Studio dan Refresh folder Prosedur Tersimpan. Anda akan melihat entri baru, GetProductsWithPriceLessThan. Dari jendela kueri, masukkan dan jalankan perintah exec GetProductsWithPriceLessThan 25, yang akan mencantumkan semua produk kurang dari $ 25, seperti yang ditunjukkan Gambar 14.

Gambar 14: Produk di bawah $25 ditampilkan (Klik untuk melihat gambar ukuran penuh)

Langkah 6: Memanggil Prosedur Tersimpan Terkelola dari Lapisan Akses Data

Pada titik ini kami telah menambahkan GetDiscontinuedProducts prosedur tersimpan dan GetProductsWithPriceLessThan dikelola ke ManagedDatabaseConstructs proyek dan telah mendaftarkannya dengan database Northwind SQL Server. Kami juga memanggil prosedur tersimpan terkelola ini dari SQL Server Management Studio (lihat Gambar s 13 dan 14). Agar aplikasi ASP.NET kami dapat menggunakan prosedur tersimpan terkelola ini, namun, kita perlu menambahkannya ke Lapisan Logika Akses Data dan Bisnis dalam arsitektur. Dalam langkah ini kita akan menambahkan dua metode baru ke ProductsTableAdapter dalam Himpunan Data Yang Dijenis, yang awalnya dibuat dalam tutorial NorthwindWithSprocs. Pada Langkah 7 kita akan menambahkan metode yang sesuai ke BLL.

NorthwindWithSprocs Buka Typed DataSet di Visual Studio dan mulailah dengan menambahkan metode baru ke ProductsTableAdapter yang bernama GetDiscontinuedProducts. Untuk menambahkan metode baru ke TableAdapter, klik kanan pada nama TableAdapter di Perancang dan pilih opsi Tambahkan Kueri dari menu konteks.

Catatan

Karena kami memindahkan database Northwind dari App_Data folder ke instans database SQL Server 2005 Express Edition, sangat penting bahwa string koneksi yang sesuai di Web.config diperbarui untuk mencerminkan perubahan ini. Pada Langkah 2 kita membahas pembaruan NORTHWNDConnectionString nilai di Web.config. Jika Anda lupa membuat pembaruan ini, maka Anda akan melihat pesan kesalahan Gagal menambahkan kueri. Tidak dapat menemukan koneksi NORTHWNDConnectionString untuk objek Web.config dalam kotak dialog saat mencoba menambahkan metode baru ke TableAdapter. Untuk mengatasi kesalahan ini, klik OK lalu buka Web.config dan perbarui nilai seperti yang NORTHWNDConnectionString dibahas di Langkah 2. Kemudian coba tambahkan kembali metode ke TableAdapter. Kali ini seharusnya berfungsi tanpa kesalahan.

Menambahkan metode baru meluncurkan wizard Konfigurasi Kueri TableAdapter, yang telah kami gunakan berkali-kali dalam tutorial sebelumnya. Langkah pertama meminta kami untuk menentukan bagaimana TableAdapter harus mengakses database: melalui pernyataan SQL ad-hoc atau melalui prosedur tersimpan baru atau yang sudah ada. Karena kami telah membuat dan mendaftarkan prosedur tersimpan terkelola GetDiscontinuedProducts dengan database, pilih opsi Gunakan prosedur tersimpan yang ada dan tekan Berikutnya.

Gambar 15: Pilih Opsi Gunakan prosedur tersimpan yang ada (Klik untuk melihat gambar ukuran penuh)

Layar berikutnya meminta prosedur tersimpan yang akan dipanggil metode. Pilih prosedur tersimpan terkelola GetDiscontinuedProducts dari daftar drop-down dan tekan Berikutnya.

Gambar 16: Pilih Prosedur Tersimpan GetDiscontinuedProducts Terkelola (Klik untuk melihat gambar ukuran penuh)

Kami kemudian diminta untuk menentukan apakah prosedur tersimpan mengembalikan baris, satu nilai, atau tidak sama sekali. Karena GetDiscontinuedProducts mengembalikan kumpulan baris produk yang dihentikan, pilih opsi pertama ( Data tabular ) dan klik Berikutnya.

Gambar 17: Pilih Opsi Data Tabular (Klik untuk melihat gambar ukuran penuh)

Layar wizard akhir memungkinkan kami menentukan pola akses data yang digunakan dan nama metode yang dihasilkan. Biarkan kedua kotak centang dicentang dan beri nama metode FillByDiscontinued dan GetDiscontinuedProducts. Klik Selesai untuk menyelesaikan wizard.

Gambar 18: Beri Nama Metode FillByDiscontinued dan GetDiscontinuedProducts (Klik untuk melihat gambar ukuran penuh)

Ulangi langkah-langkah ini untuk membuat metode bernama FillByPriceLessThan dan GetProductsWithPriceLessThan dalam ProductsTableAdapter untuk prosedur tersimpan terkelola GetProductsWithPriceLessThan .

Gambar 19 memperlihatkan cuplikan layar Perancang Himpunan Data setelah menambahkan metode ke ProductsTableAdapter untuk GetDiscontinuedProducts prosedur tersimpan dan GetProductsWithPriceLessThan terkelola.

Gambar 19: ProductsTableAdapter Menyertakan Metode Baru yang Ditambahkan dalam Langkah ini (Klik untuk melihat gambar ukuran penuh)

Langkah 7: Menambahkan Metode yang Sesuai ke Lapisan Logika Bisnis

Sekarang setelah kami memperbarui Lapisan Akses Data untuk menyertakan metode untuk memanggil prosedur tersimpan terkelola yang ditambahkan di Langkah 4 dan 5, kita perlu menambahkan metode yang sesuai ke Lapisan Logika Bisnis. Tambahkan dua metode berikut ke ProductsBLLWithSprocs kelas :

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Kedua metode hanya memanggil metode DAL yang sesuai dan mengembalikan ProductsDataTable instans. DataObjectMethodAttribute Markup di atas setiap metode menyebabkan metode ini disertakan dalam daftar drop-down di tab SELECT dari wizard Konfigurasi Sumber Data ObjectDataSource.

Langkah 8: Memanggil Prosedur Tersimpan Terkelola dari Lapisan Presentasi

Dengan Logika Bisnis dan Lapisan Akses Data yang dipasang untuk menyertakan dukungan untuk memanggil GetDiscontinuedProducts prosedur tersimpan dan GetProductsWithPriceLessThan terkelola, kita sekarang dapat menampilkan hasil prosedur tersimpan ini melalui halaman ASP.NET.

ManagedFunctionsAndSprocs.aspx Buka halaman di AdvancedDAL folder dan, dari Kotak Alat, seret GridView ke Perancang. Atur properti GridView ke IDDiscontinuedProducts dan, dari tag pintarnya, ikat ke ObjectDataSource baru bernama DiscontinuedProductsDataSource. Konfigurasikan ObjectDataSource untuk menarik datanya dari ProductsBLLWithSprocs metode kelas.GetDiscontinuedProducts

Gambar 20: Konfigurasikan ObjectDataSource untuk Menggunakan ProductsBLLWithSprocs Kelas (Klik untuk melihat gambar ukuran penuh)

Gambar 21: Pilih GetDiscontinuedProducts Metode dari Daftar Drop-Down di Tab SELECT (Klik untuk melihat gambar ukuran penuh)

Karena kisi ini akan digunakan untuk hanya menampilkan informasi produk, atur daftar drop-down di tab PERBARUI, SISIPKAN, dan HAPUS ke (Tidak Ada) lalu klik Selesai.

Setelah menyelesaikan wizard, Visual Studio akan secara otomatis menambahkan BoundField atau CheckBoxField untuk setiap bidang data di ProductsDataTable. Luangkan waktu sejenak untuk menghapus semua bidang ini kecuali ProductName dan Discontinued, di mana markup deklaratif GridView dan ObjectDataSource Anda akan terlihat mirip dengan yang berikut ini:

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Luangkan waktu sejenak untuk melihat halaman ini melalui browser. Ketika halaman dikunjungi, ObjectDataSource memanggil ProductsBLLWithSprocs metode kelas s GetDiscontinuedProducts . Seperti yang kita lihat di Langkah 7, metode ini memanggil ke metode kelas DAL s ProductsDataTableGetDiscontinuedProducts , yang memanggil prosedur tersimpan GetDiscontinuedProducts . Prosedur tersimpan ini adalah prosedur tersimpan terkelola dan menjalankan kode yang kami buat di Langkah 3, mengembalikan produk yang dihentikan.

Hasil yang dikembalikan oleh prosedur tersimpan terkelola dipaketkan ke dalam oleh ProductsDataTable DAL dan kemudian dikembalikan ke BLL, yang kemudian mengembalikannya ke Lapisan Presentasi tempat mereka terikat ke GridView dan ditampilkan. Seperti yang diharapkan, kisi mencantumkan produk yang telah dihentikan.

Gambar 22: Produk yang Dihentikan Tercantum (Klik untuk melihat gambar ukuran penuh)

Untuk praktik lebih lanjut, tambahkan TextBox dan GridView lain ke halaman. Minta GridView ini menampilkan produk kurang dari jumlah yang dimasukkan ke dalam TextBox dengan memanggil ProductsBLLWithSprocs metode kelas.GetProductsWithPriceLessThan

Langkah 9: Membuat dan Memanggil UDF T-SQL

Fungsi yang Ditentukan Pengguna, atau UDF, adalah objek database yang meniru semantik fungsi dalam bahasa pemrograman. Seperti fungsi dalam C#, UDF dapat menyertakan jumlah variabel parameter input dan mengembalikan nilai dari jenis tertentu. UDF dapat mengembalikan data skalar - string, bilangan bulat, dan sebagainya - atau data tabular. Mari kita lihat sekilas kedua jenis UDF, dimulai dengan UDF yang mengembalikan jenis data skalar.

UDF berikut menghitung perkiraan nilai inventaris untuk produk tertentu. Ini melakukannya dengan mengambil tiga parameter input - UnitPricenilai , , UnitsInStockdan Discontinued untuk produk tertentu - dan mengembalikan nilai jenis money. Ini menghitung nilai estimasi inventori dengan mengalikan UnitPrice dengan UnitsInStock. Untuk item yang dihentikan, nilai ini dibelah dua.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

Setelah UDF ini ditambahkan ke database, UDF dapat ditemukan melalui Management Studio dengan memperluas folder Programmability, lalu Functions, lalu Fungsi bernilai skalar. Ini dapat digunakan dalam SELECT kueri seperti:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Saya telah menambahkan udf_ComputeInventoryValue UDF ke database Northwind; Gambar 23 memperlihatkan output kueri di atas SELECT saat ditampilkan melalui Management Studio. Perhatikan juga bahwa UDF tercantum di bawah folder Fungsi bernilai skalar di Object Explorer.

Gambar 23: Setiap Nilai Inventori Produk Tercantum (Klik untuk melihat gambar ukuran penuh)

UDF juga dapat mengembalikan data tabular. Misalnya, kita dapat membuat UDF yang mengembalikan produk yang termasuk dalam kategori tertentu:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

udf_GetProductsByCategoryID UDF menerima @CategoryID parameter input dan mengembalikan hasil kueri yang ditentukanSELECT. Setelah dibuat, UDF ini dapat direferensikan dalam FROM klausa JOIN (atau SELECT) kueri. Contoh berikut akan mengembalikan ProductIDnilai , ProductName, dan CategoryID untuk setiap minuman.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Saya telah menambahkan udf_GetProductsByCategoryID UDF ke database Northwind; Gambar 24 memperlihatkan output kueri di atas SELECT saat ditampilkan melalui Management Studio. UDF yang mengembalikan data tabular dapat ditemukan di folder Fungsi nilai Tabel Object Explorer.

Gambar 24: ProductID, , ProductNamedan CategoryID Terdaftar untuk Setiap Minuman (Klik untuk melihat gambar ukuran penuh)

Catatan

Untuk informasi selengkapnya tentang membuat dan menggunakan FDU, lihat Pengantar ke User-Defined Functions. Lihat juga Keuntungan dan Kelemahan User-Defined Functions.

Langkah 10: Membuat UDF Terkelola

udf_ComputeInventoryValue UDF dan udf_GetProductsByCategoryID yang dibuat dalam contoh di atas adalah objek database T-SQL. SQL Server 2005 juga mendukung UDF terkelola, yang dapat ditambahkan ke ManagedDatabaseConstructs proyek seperti prosedur tersimpan terkelola dari Langkah 3 dan 5. Untuk langkah ini, mari kita terapkan udf_ComputeInventoryValue UDF dalam kode terkelola.

Untuk menambahkan UDF terkelola ke ManagedDatabaseConstructs proyek, klik kanan nama proyek di Penjelajah Solusi dan pilih Tambahkan Item Baru. Pilih Templat yang Ditentukan Pengguna dari kotak dialog Tambahkan Item Baru dan beri nama file udf_ComputeInventoryValue_Managed.csUDF baru .

Gambar 25: Tambahkan UDF Terkelola Baru ke ManagedDatabaseConstructs Proyek (Klik untuk melihat gambar ukuran penuh)

Templat Fungsi yang Ditentukan Pengguna membuat partial kelas bernama UserDefinedFunctions dengan metode yang namanya sama dengan nama file kelas (udf_ComputeInventoryValue_Managed, dalam instans ini). Metode ini didekorasi menggunakan SqlFunction atribut , yang menandai metode sebagai UDF terkelola.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Metode udf_ComputeInventoryValue saat ini mengembalikan SqlString objek dan tidak menerima parameter input apa pun. Kita perlu memperbarui definisi metode sehingga menerima tiga parameter input - , UnitPrice, UnitsInStockdan Discontinued - dan mengembalikan SqlMoney objek. Logika untuk menghitung nilai inventori identik dengan yang ada di T-SQL udf_ComputeInventoryValue UDF.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Perhatikan bahwa parameter input metode UDF adalah jenis SQL yang sesuai: SqlMoney untuk UnitPrice bidang , SqlInt16 untuk UnitsInStock, dan SqlBoolean untuk Discontinued. Jenis data ini mencerminkan jenis yang ditentukan dalam Products tabel: UnitPrice kolom berjenis money, UnitsInStock kolom jenis smallint, dan Discontinued kolom jenis bit.

Kode dimulai dengan membuat SqlMoney instans bernama inventoryValue yang diberi nilai 0. Tabel Products memungkinkan nilai database NULL dalam UnitsInPrice kolom dan UnitsInStock . Oleh karena itu, kita perlu terlebih dahulu memeriksa untuk melihat apakah nilai-nilai ini berisi NULL s, yang kita lakukan melalui SqlMoney properti objek sIsNull. Jika keduanya UnitPrice dan UnitsInStock berisi nilai non-NULL , maka kita menghitung inventoryValue menjadi produk dari keduanya. Kemudian, jika Discontinued benar, maka kita menghentikan nilainya.

Catatan

Objek SqlMoney hanya memungkinkan dua SqlMoney instans untuk dikalikan bersama-sama. Ini tidak memungkinkan SqlMoney instans dikalikan dengan angka floating-point harfiah. Oleh karena itu, untuk memisahkannya inventoryValue , kita kalikan dengan instans baru SqlMoney yang memiliki nilai 0,5.

Langkah 11: Menyebarkan UDF Terkelola

Sekarang setelah UDF terkelola dibuat, kami siap untuk menyebarkannya ke database Northwind. Seperti yang kita lihat di Langkah 4, objek terkelola dalam Proyek SQL Server disebarkan dengan mengklik kanan nama proyek di Penjelajah Solusi dan memilih opsi Sebarkan dari menu konteks.

Setelah Anda menyebarkan proyek, kembali ke SQL Server Management Studio dan refresh folder Fungsi bernilai skalar. Anda sekarang akan melihat dua entri:

• dbo.udf_ComputeInventoryValue - T-SQL UDF yang dibuat di Langkah 9, dan
• dbo.udf ComputeInventoryValue_Managed - UDF terkelola yang dibuat di Langkah 10 yang baru saja disebarkan.

Untuk menguji UDF terkelola ini, jalankan kueri berikut dari dalam Management Studio:

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Perintah ini menggunakan UDF terkelola udf ComputeInventoryValue_Managed alih-alih T-SQL udf_ComputeInventoryValue UDF, tetapi outputnya sama. Lihat kembali ke Gambar 23 untuk melihat cuplikan layar output UDF.

Langkah 12: Men-debug Objek Database Terkelola

Dalam tutorial Debugging Prosedur Tersimpan, kami membahas tiga opsi untuk debugging SQL Server melalui Visual Studio: Debugging Database secara Langsung, Debugging Aplikasi, dan Debugging dari Proyek SQL Server. Objek database terkelola tidak dapat di-debug melalui Debugging Database Langsung, tetapi dapat di-debug dari aplikasi klien dan langsung dari Proyek SQL Server. Namun, agar penelusuran kesalahan berfungsi, database SQL Server 2005 harus mengizinkan penelusuran kesalahan SQL/CLR. Ingat bahwa ketika kami pertama kali membuat ManagedDatabaseConstructs proyek Visual Studio bertanya kepada kami apakah kami ingin mengaktifkan penelusuran kesalahan SQL/CLR (lihat Gambar 6 di Langkah 2). Pengaturan ini dapat dimodifikasi dengan mengklik kanan database dari jendela Server Explorer.

Gambar 26: Pastikan Database Mengizinkan Penelusuran Kesalahan SQL/CLR

Bayangkan bahwa kami ingin men-debug prosedur tersimpan terkelola GetProductsWithPriceLessThan . Kita akan mulai dengan mengatur titik henti dalam kode GetProductsWithPriceLessThan metode .

Gambar 27: Atur Titik Henti dalam GetProductsWithPriceLessThan Metode (Klik untuk melihat gambar ukuran penuh)

Mari kita lihat terlebih dahulu penelusuran kesalahan objek database terkelola dari Proyek SQL Server. Karena Solusi kami mencakup dua proyek - ManagedDatabaseConstructs Proyek SQL Server bersama dengan situs web kami - untuk men-debug dari Proyek SQL Server, kami perlu menginstruksikan Visual Studio untuk meluncurkan ManagedDatabaseConstructs Proyek SQL Server ketika kita mulai men-debug. ManagedDatabaseConstructs Klik kanan proyek di Penjelajah Solusi dan pilih opsi Atur sebagai Proyek StartUp dari menu konteks.

Ketika proyek diluncurkan ManagedDatabaseConstructs dari debugger, proyek menjalankan pernyataan SQL dalam Test.sql file, yang terletak di Test Scripts folder . Misalnya, untuk menguji GetProductsWithPriceLessThan prosedur tersimpan terkelola, ganti konten file yang ada Test.sql dengan pernyataan berikut, yang memanggil GetProductsWithPriceLessThan prosedur tersimpan @CategoryID terkelola yang melewati nilai 14,95:

exec GetProductsWithPriceLessThan 14.95

Setelah Anda memasukkan skrip di atas ke dalam Test.sql, mulai debugging dengan masuk ke menu Debug dan pilih Mulai Debugging atau dengan menekan F5 atau ikon putar hijau di Toolbar. Ini akan membangun proyek dalam Solusi, menyebarkan objek database terkelola ke database Northwind, lalu menjalankan Test.sql skrip. Pada titik ini, titik henti akan dipukul dan kita dapat menelusuri GetProductsWithPriceLessThan metode , memeriksa nilai parameter input, dan sebagainya.

Gambar 28: Breakpoint di Metode GetProductsWithPriceLessThan Telah Dicapai (Klik untuk melihat gambar ukuran penuh)

Agar objek database SQL di-debug melalui aplikasi klien, sangat penting bahwa database dikonfigurasi untuk mendukung penelusuran kesalahan aplikasi. Klik kanan pada database di Server Explorer dan pastikan bahwa opsi Penelusuran Kesalahan Aplikasi dicentang. Selain itu, kita perlu mengonfigurasi aplikasi ASP.NET untuk diintegrasikan dengan SQL Debugger dan untuk menonaktifkan pengumpulan koneksi. Langkah-langkah ini dibahas secara rinci dalam Langkah 2 dari tutorial Debugging Prosedur Tersimpan .

Setelah Anda mengonfigurasi aplikasi dan database ASP.NET, atur situs web ASP.NET sebagai proyek startup dan mulai penelusuran kesalahan. Jika Anda mengunjungi halaman yang memanggil salah satu objek terkelola yang memiliki titik henti, aplikasi akan berhenti dan kontrol akan diserahkan ke debugger, di mana Anda dapat menelusuri kode seperti yang ditunjukkan pada Gambar 28.

Langkah 13: Mengkompilasi dan Menyebarkan Objek Database Terkelola secara Manual

Proyek SQL Server memudahkan untuk membuat, mengkompilasi, dan menyebarkan objek database terkelola. Sayangnya, Proyek SQL Server hanya tersedia di Visual Studio edisi Sistem Profesional dan Tim. Jika Anda menggunakan Visual Web Developer atau Visual Studio Edisi Standar dan ingin menggunakan objek database terkelola, Anda harus membuat dan menyebarkannya secara manual. Ini melibatkan empat langkah:

1. Buat file yang berisi kode sumber untuk objek database terkelola,
2. Kompilasi objek ke dalam rakitan,
3. Daftarkan rakitan dengan database SQL Server 2005, dan
4. Buat objek database di SQL Server yang menunjuk ke metode yang sesuai dalam rakitan.

Untuk mengilustrasikan tugas-tugas ini, mari kita buat prosedur tersimpan terkelola baru yang mengembalikan produk UnitPrice yang lebih besar dari nilai yang ditentukan. Buat file baru di komputer Anda bernama GetProductsWithPriceGreaterThan.cs dan masukkan kode berikut ke dalam file (Anda dapat menggunakan Visual Studio, Notepad, atau editor teks apa pun untuk menyelesaikannya):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = new SqlCommand();
        myCommand.CommandText =
            @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                     ReorderLevel, Discontinued
              FROM Products
              WHERE UnitPrice > @MinPrice";
        myCommand.Parameters.AddWithValue("@MinPrice", price);
        // Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand);
    }
};

Kode ini hampir identik dengan metode yang GetProductsWithPriceLessThan dibuat di Langkah 5. Satu-satunya perbedaan adalah nama metode, WHERE klausa, dan nama parameter yang digunakan dalam kueri. Kembali ke GetProductsWithPriceLessThan metode , WHERE klausul berbunyi: WHERE UnitPrice < @MaxPrice. Di sini, dalam GetProductsWithPriceGreaterThan, kami menggunakan: WHERE UnitPrice > @MinPrice .

Kita sekarang perlu mengkompilasi kelas ini ke dalam perakitan. Dari baris perintah, navigasikan ke direktori tempat Anda menyimpan GetProductsWithPriceGreaterThan.cs file dan gunakan pengkompilasi C# (csc.exe) untuk mengkompilasi file kelas ke dalam rakitan:

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Jika folder yang berisi csc.exe tidak dalam sistem PATH, Anda harus sepenuhnya mereferensikan jalurnya, %WINDOWS%\Microsoft.NET\Framework\version\, seperti:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Gambar 29: Kompilasi GetProductsWithPriceGreaterThan.cs Ke Dalam Rakitan (Klik untuk melihat gambar ukuran penuh)

Bendera /t menentukan bahwa file kelas C# harus dikompilasi ke dalam DLL (bukan yang dapat dieksekusi). Bendera /out menentukan nama rakitan yang dihasilkan.

Catatan

Daripada mengkompilasi GetProductsWithPriceGreaterThan.cs file kelas dari baris perintah, Anda dapat menggunakan Visual C# Express Edition atau membuat proyek Pustaka Kelas terpisah di Visual Studio Standard Edition. S ren Jacob Lauritsen telah dengan ramah menyediakan proyek Visual C# Express Edition dengan kode untuk GetProductsWithPriceGreaterThan prosedur tersimpan dan dua prosedur terkelola dan UDF yang dibuat di Langkah 3, 5, dan 10. Proyek S ren s juga menyertakan perintah T-SQL yang diperlukan untuk menambahkan objek database yang sesuai.

Dengan kode yang dikompilasi ke dalam rakitan, kami siap untuk mendaftarkan rakitan dalam database SQL Server 2005. Ini dapat dilakukan melalui T-SQL, menggunakan perintah CREATE ASSEMBLY, atau melalui SQL Server Management Studio. Mari fokus pada penggunaan Management Studio.

Dari Management Studio, perluas folder Programmability di database Northwind. Salah satu subfoldernya adalah Assemblies. Untuk menambahkan Assembly baru secara manual ke database, klik kanan pada folder Assemblies dan pilih Assembly Baru dari menu konteks. Ini menampilkan kotak dialog Rakitan Baru (lihat Gambar 30). Klik tombol Telusuri, pilih ManuallyCreatedDBObjects.dll rakitan yang baru saja kita kompilasi, lalu klik OK untuk menambahkan Assembly ke database. Anda tidak akan melihat ManuallyCreatedDBObjects.dll rakitan di Object Explorer.

Gambar 30: Tambahkan ManuallyCreatedDBObjects.dll Rakitan ke Database (Klik untuk melihat gambar ukuran penuh)

Gambar 31: ManuallyCreatedDBObjects.dll Tercantum di Penjelajah Objek

Meskipun kami telah menambahkan rakitan ke database Northwind, kami belum mengaitkan prosedur tersimpan GetProductsWithPriceGreaterThan dengan metode dalam perakitan. Untuk mencapai hal ini, buka jendela kueri baru dan jalankan skrip berikut:

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

Ini membuat prosedur tersimpan baru dalam database Northwind bernama GetProductsWithPriceGreaterThan dan mengaitkannya dengan metode GetProductsWithPriceGreaterThan terkelola (yang ada di kelas StoredProcedures, yang ada di perakitan ManuallyCreatedDBObjects).

Setelah menjalankan skrip di atas, refresh folder Prosedur Tersimpan di Object Explorer. Anda akan melihat entri prosedur tersimpan baru - - GetProductsWithPriceGreaterThan yang memiliki ikon kunci di sampingnya. Untuk menguji prosedur tersimpan ini, masukkan dan jalankan skrip berikut di jendela kueri:

exec GetProductsWithPriceGreaterThan 24.95

Seperti yang ditunjukkan Gambar 32, perintah di atas menampilkan informasi untuk produk-produk tersebut dengan UnitPrice lebih besar dari $24,95.

Gambar 32: ManuallyCreatedDBObjects.dll Tercantum di Object Explorer (Klik untuk melihat gambar ukuran penuh)

Ringkasan

Microsoft SQL Server 2005 menyediakan integrasi dengan Common Language Runtime (CLR), yang memungkinkan objek database dibuat menggunakan kode terkelola. Sebelumnya, objek database ini hanya dapat dibuat menggunakan T-SQL, tetapi sekarang kita dapat membuat objek ini menggunakan bahasa pemrograman .NET seperti C#. Dalam tutorial ini kami membuat dua prosedur tersimpan terkelola dan Fungsi yang Ditentukan Pengguna terkelola.

Jenis Proyek SQL Server Visual Studio memfasilitasi pembuatan, kompilasi, dan penyebaran objek database terkelola. Selain itu, ia menawarkan dukungan debugging yang kaya. Namun, jenis Proyek SQL Server hanya tersedia di Visual Studio edisi Sistem Profesional dan Tim. Bagi mereka yang menggunakan Visual Web Developer atau Visual Studio Edisi Standar, langkah-langkah pembuatan, kompilasi, dan penyebaran harus dilakukan secara manual, seperti yang kita lihat di Langkah 13.

Selamat Pemrograman!

Bacaan lebih lanjut

Untuk informasi selengkapnya tentang topik yang dibahas dalam tutorial ini, lihat sumber daya berikut:

• Keuntungan dan Kelemahan User-Defined Functions
• Membuat Objek SQL Server 2005 dalam Kode Terkelola
• Cara: Membuat dan Menjalankan Prosedur Tersimpan CLR SQL Server
• Cara: Membuat dan Menjalankan Fungsi User-Defined CLR SQL Server
• Cara: Mengedit Test.sql Skrip untuk Menjalankan Objek SQL
• Pengantar Fungsi yang Ditentukan Pengguna
• Kode Terkelola dan SQL Server 2005 (Video)
• ReferensiTransact-SQL
• Panduan: Membuat Prosedur Tersimpan dalam Kode Terkelola

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.

Terima kasih khusus untuk

Seri tutorial ini ditinjau oleh banyak peninjau yang bermanfaat. Peninjau prospek untuk tutorial ini adalah S ren Jacob Lauritsen. Selain meninjau artikel ini, S ren juga membuat proyek Visual C# Express Edition yang disertakan dalam unduhan artikel ini untuk mengkompilasi objek database terkelola secara manual. Tertarik untuk meninjau artikel MSDN saya yang akan datang? Jika demikian, hubungi saya di mitchell@4GuysFromRolla.com.

Berikutnya Sebelumnya