 SİTE
İÇİ ARAMA |
|
|
 Blogroll |
|
|
|
|
|
|
C# ile .NET'te SP ve UDF Yazmak |
|
| Gönderiliyor lütfen bekleyin... |
|
|
Bugünkü makalemizde SQL Server ve .NET entegrasyonunun getirdiği yeniliklerden olan .NET’de saklı yordam(Stored Procedure) ve kullanıcı tanımlı fonksiyon(User Defined Function) yazılmasını inceleyelim
SQL Server-CLR entegrasyonu ile birlikte, T-SQL yerine bildiğimiz .NET dillerinden biri ile de saklı yordam, User Defined Function vs yazabiliriz. Bunun için öncelikle SQL Server 2005’i, sp_configure saklı yordamını kullanarak "clr enabled" özelliğini aktif hale getirmeliyiz. Aşağıdaki kodları SQL Server 2005’te Query editörüne yazıp, çalıştırın.
sp_configure ’clr enabled’, 1
GO
RECONFIGURE
GO
|
Kod 1 -Clr Enabled özelliğini aktif hale getirmek
Artık SQL Server 2005’imiz clr ile entegre halinde çalışabilir. Yani .NET’de yazdığımız kodlar SQL Server’da çalıştırılabilir.
Şimdi .NET’i açıp, SQL Server Project oluşturalım. Solution Explorer’da projemiz oluşturulmuş olur. Proje adına sağ tıklayıp, Add menüsünden User Defined Function(Kullanıcı Tanımlı Fonksiyon) seçeneğini seçip, çıkan iletişim kutusunda yazacağımız fonksiyona bir ad verip projemize ekleyelim. Aşağıda, projemize ekleyebileceğimiz SQL Server tabanlı işlevler görünmektedir.
1.Şekil: SQL Server tabanlı işlevler
Fonksiyonumuzu yazmadan önce kullanmak için küçük bir tablo oluşturalım. Aşağıdaki gibi Users adında üç alandan oluşan bir tablo oluşturdum.
CREATE TABLE Users
(
UserId INT IDENTITY(1,1) PARIMARY KEY not null,
UserName NVARCHAR (30) not null,
Password VARBINARY(32) not null
) |
Kod 2-User Tablosu
Tablomuz kullanıcı adı ve şifrelerini tutacak. "Şifre alanı neden varbinary tipinde tanımlandı?" gibi bir soru aklınıza gelebilir. İşte yazacağımız fonksiyon bu kısımla alakalı. Yazacağımız fonksiyon parametre olarak girilen şifreyi alacak ve kriptolayarak geriye kriptolanmış değeri döndürecek. Yazacağımız bir saklı yordam ile bu değer kullanıcı adı ile birlikte tablomuza kaydedilecek.
Şimdi fonksiyonumuzu yazalım. İlk açıldığında sayfadaki kodlar aşağıdaki gibidir.
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 Function1()
{
// Put your code here
return new SqlString("Hello");
}
}
|
Kod 3-Kullanılan kütüphaneler.
Kodda yapacağımız küçük değişikliklerle birlikte fonksiyonumuzun kodlarını yazmaya başlayalım. Öncelikle Unicode işlemleri için System.Text ve kriptolama işlemi için System.Security.Cryptography isim alanını projemize ekleyelim. Varsayılan olarak UserDefinedFunctions olan sınıf adını bize göre daha anlamlı bir isimle değiştirebiliriz. Ben "MySecurity" olarak değiştirdim. Sınıf içerisindeki ilk satıra bakarsak bir attribute(SqlFunction) belirtilmiş. Yazacağımız kodların saklı yordammı, fonksiyonmu, tetikleyicimi vs olarak algılacağını bu attribute ile belirtmiş oluyoruz. Bir alt satırda işleteceğimiz kodları yazacağımız fonksiyon başlamaktadır. Varsayılan olarak bu fonksiyonun dönüş tipi SqlString’dir. Fakat biz fonksiyonumuzdan binary bir değer döndüreceğimizden dönüş tipini SqlBinary olarak değiştiriyoruz. Hatırlarsanız fonksiyonumuz dışarıdan şifreyi parametre olarak alacaktı. SqlString tipinde bir değişkeni fonksiyonumuza parametre olarak verelim. Fonksiyonumuzun ismini daha anlamlı bir isimle değiştirelim. Ben, "Kontrol" olarak değiştirdim. Şimdi, fonksiyonumuzun kodlarını yazalım. Öncelikle girilen şifreyi Unicode çevirip, byte tipinden bir diziye aktaracağız. Sonra, bu diziyi kriptolama algoritmalarından biri olan SHA1 sınıfı ile kriptolayıp yine byte türünden başka bir diziye aktaracağız. Geriye SqlBinary tipine çevrilecek olan bu dizi dönecektir. Kodlarımız aşağıdaki gibidir.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Security.Cryptography;
public partial class MySecurity
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary Kontrol(SqlString sifre)
{
UnicodeEncoding uc = new UnicodeEncoding();
byte[] dizi = uc.GetBytes((string)sifre);
SHA1 sha = new SHA1CryptoServiceProvider();
byte[] SifreliDizi = sha.ComputeHash(dizi);
return (SqlBinary)SifreliDizi;
}
}
|
Kod 4- Kontrol fonksiyonu
İlk satırda sifreyi Unicode çevirmek için UnicodeEncoding nesnesinden bir yavru üye tanımlıyoruz. Sonra, bu üyenin GetBytes fonksiyonunu kullanarak şifreyi byte çevirip diziye aktarıyoruz. Sonra SHA1 algoritmasını kullanmak için SHA1 kripto servis sağlayıcı tanımlıyoruz(RSA, DSA vs. her algoritmanın kendi servis sağlayıcısı vardır). ComputeHash metodu ile kriptolayıp byte türündeki diziye kriptolanmış değeri aktarıyoruz. Bu değeri döndürürken, SqlBinary tipine çeviriyoruz. Fonksiyonumuz dışarıdan SqlString tipinden veri almaktadır. Fonksiyon içerisinde bu değer byte çevrilmeden önce stringe çevrilmektedir. SqlString tipindeki değişkeni stringe çevirmeden derlerseniz hata alırsınız. Çünkü, GetBytes metodu SqlString değil string türünden değer almaktadır. Şimdi projemizi Build edip sonra Deploy edelim. .NET’te kodlarımızı yazdık, derledik. Bakalım, yazdığımız "Kontrol" fonksiyonu SQL Server’da oluşturulmuş mu? SQL Server 2005’i açalım. Object Browser’da Programmability\Functions\Scalar-valued Functions altında yazdığımız fonksiyon görünecektir. Assemblies klasörü altında ise yazdığımız SQL projesinin assemblysi görünecektir. Görüntü aşağıdadır.
2.Şekil: SQL projemizin assmeblysi ve Kontrol fonksiyonu
Fonksiyonumuzu yazdık. Şimdi, yeni kullanıcı adı ve şifre girmemizi sağlayan bir saklı yordam yazalım. Bu saklı yordamımızı yazarken T-SQL kullanacağız. Sonra T-SQL ile yazdığımız bu saklı yordamı C# ile .NET’de yazacağız. Önce T-SQL’de yazalım. Ekle isimli saklı yordamımız aşağıdaki gibidir.
CREATE PROCEDURE Ekle
(
@UserName nvarchar(32),
@Password nvarchar(32)
)
AS
INSERT INTO Users(UserName,Password) VALUES (@UserName,dbo.Kontrol(@Password))
|
Kod 5- Ekle saklı yordamı
Dikkat ederseniz, alınan @Password değeri "Kontrol" fonksiyonuna gönderiliyor ve dönen değer tabloda Password alanına kaydediliyor. Evet, kontrolümüzü yazdık, saklı yordamımızı yazdık. Şimdi, bakalım çalışıyormu:)
SQL Server’da Query editörünü açalım ve saklı yordamımızı çalıştıralım.
| exec Ekle ’Ninja Kedi’,’Sifre’ |
Kod 6- Ekle saklı yordamının Query Analizerda çalıştırılması
Tablomuza, UserName "Ninja Kedi" ve Password "Sifre" olan bir kayıt eklenmiş oldu. Tablomuzu açıp eklenip eklenmediğine bakalım. Tablo ismini sağ tıklayıp, Open Table tıklarsak görüntü aşağıdaki gibidir.
3.Şekil: Open Table sonucu tablo görüntüsü
Görüldüğü gibi Password alanında sadece binary tipinde bir değer olduğu belirtilmekte ama değer görülmemektedir. Kriptolanmış değeride görmek için tablomuzu select ifadesini kullanarak görüntüleyelim.
"select * from Users" sorgusunun görüntüsü aşağıdaki gibidir.
4.Şekil: Select sorgusunun sonucu
Şimdi, yukarıdaki saklı yordamımızı C# kullanarak .NET’de yazalım. SQL Server projemize geri dönelim. Solution Explorer’da proje ismine sağ tıklayıp Add menüsünden Stored Procedure seçeneğini seçelim. Açılan iletişim kutusunda saklı yordamımıza bir isim verip projemize ekleyelim. Ben, saklı yordamıma Ekle1 adını verdim. Koda dikkat ederseniz bir önceki koddan farkı SqlFunction olan attribute SqlProcedure olarak değişmiştir.
Yukarıda T-SQL ile oluşturduğumuz saklı yordamı ele alalım. @UserName ve @Password adından iki parametre almaktadır. Bu parametreler kullanılarak ekleme işlemi gerçekleştirilmektedir. .NET’de yazacağımız saklı yordam dışarıdan SqlString tipinden iki paremetre almalıdır. Metodun kalıbı aşağıdaki gibidir.
public static void Ekle1(SqlString UserName,SqlString Password)
{
}
|
Kod 7- Ekle1 metodu
Veritabanı işlemlerini gerçekleştirmek için öncelikle bir Connection nesnesine ihtiyacımız vardır. Dikkat ederseniz yazdığımız saklı yordamın çalışacağı veritabanı bellidir. En başta .NET’de dil ve proje türü seçerken, proje türü olarak SQL Server Project seçtiğimizde bağlantı ayarlarını ayarladığımız, aşağıdaki gibi bir iletişim kutusu ile karşılaşırız. Yukarıda bu kısma değinmeyi unutmuşum.
5.Şekil: SQL Server Project bağlantı ayarları
Görüldüğü gibi bağlantı ayarlarını en başta ayarlıyoruz. Saklı yordamımızın hangi serverda hangi veritabanında çalışacağı bellidir. Dolaysı ile Connection nesnesini oluştururken ConnectionString özelliği ile bu bağlantı ayarlarını ayarlamamıza gerek yoktur. Aşağıdaki kodla Connection nesnesi tanımlanmaktadır. Saklı yordamımızın çalıştıracağı kodları, oluşturduğumuz Connection nesnesini parametre olarak alan using çerçevesi içerisine yazıyoruz.
SqlConnection baglan = new SqlConnection("context connection=true");
using (baglan)
{
SqlCommand cmd = new SqlCommand("insert into Users(UserName,Password) values (@UserName,dbo.Kontrol(@Password))", baglan);
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
baglan.Open();
cmd.ExecuteNonQuery();
SqlContext.Pipe.Send("Ekleme işlemi başarıyla gerçekleştirildi. ");
} |
Kod 8- Saklı yordamı çalıştıran kod örneği
İlk olarak saklı yordamımızın çalıştıracağı komutu tanımlıyoruz. Sonra kullanılan parametreleri ekliyoruz. Önceden, parametreyi tanımlıyor sonra parametrenin alacağı değeri belirliyorduk. Burada ise Command nesnesinin AddWithValue metodu ile sadece parametre adını ve alacağı değeri yazıyoruz. Parametreler, değer olarak saklı yordamımıza gelen UserName ve Password değerlerini almaktadırlar. Bağlantımızı açıp, komutumuzu çalıştırıyoruz. SqlContext sınıfı metodları ile saklı yordamın sonuçları çağrıldığı yere aktarılabilir. Sadece yordam sonuçları değil herhangi bir bilgiyide aktarabiliriz. Yukarıda SQL Server Messages bölümünde görüntülenmek için, işlem yapıldıktan sonra işlemin gerçekleştirildiğini bildiren bir uyarı gönderilmiştir.
Saklı yordamımızı çalıştırıp, Messages sekmesindeki mesajı görelim. Aşağıda, saklı yordamın çalıştırılması ve Messages sekmesi görülmektedir.
6.Şekil: Messages sekmesi
Sonraki makalede görüşmek üzere.
Makale:
C# ile .NET'te SP ve UDF Yazmak ADO.NET ve SQL Mustafa Özen
|
|
|
|
|
-
-
Eklenen Son 10
-
Bu Konuda Geçmiş 10
Bu Konuda Yazılmış Yazılmış 10 Makale Yükleniyor
Son Eklenen 10 Makale Yükleniyor
Bu Konuda Yazılmış Geçmiş Makaleler Yükleniyor
|
|
|
|