.NET Framework 2.0 versiyonunun yayınlanmasının ardından CLR ile SQL entegrasyonu hayatımıza girdi. Bu entegrasyon sayesinde herhangi bir .NET tabanlı programlama dili ile sql nesneleri yazılabilmektedir. Örneğin bir tablodan her veri silindiğinde System.IO isim alanı altında yer alan tiplerden faydalanarak arşivleme yapan bir trigger yazılabilir. Ya da aldığı parametrenin içinde geçen sesli harfleri sayan bir aggregate fonction, for döngüsü ve diziler ile kolayca yazılabilir. Bu örnekler tabi ki çoğaltılabilir. SQL CLR entegrasyonu kullanılarak aşağıdaki sql nesneleri yazılabilir :
- User Defined Funtion
- Stored Procedure
- Aggregate function
- Trigger
- User Defined Type
Sql nesnelerini .Net tarafında geliştirmek için gösterilebilecek en önemli sebep, .Net temel sınıf kütüphanesinden faydalanabilmektir. Bu sayede standart bir stored procedure ile yapılması zor ya da imkansız olan bir çok vaka sıradan hale gelmektedir.
Bu yazıda, kullanıcı tanımlı bir fonksiyonun nasıl yazılacağını incelenmekle kalmayıp, fonksiyon içerisinde koşan iş mantığında bir web servisten faydalanacağız. Son olarak yazdığımız fonksiyonu, sql server içerisinde oluşturulan bir tabloya koyacağımız check constraint içerisinde kullanacağız.
CLR SQL nesnesi yazmak için öncelikle proje şablonları arasından Database altında yer alan Sql Server Project şablonu kullanılarak yeni bir uygulama açılır. Yazılacak sql nesnelerinin hangi sunucu ve veritabanı için yazıldıklarını belirlemek için karşımıza bir form çıkar (Daha önce bağlantı bilgileri seçilmiş bir uygulama açılırsa aşağıdaki form değil; önceki bağlantı bilgilerini içeren küçük bir onay formu açılır; istenirse o formdan bu seçim formuna geçilebilir ya da onaylanarak devam edilebilir).
Burada ilgili parametreler (Lokal sql server altında AdventureWorks veritabanına windows authantication ile) set edildikten sonra açılan projeye sağ tıklanarak bir User Defined Function şablon kod dosyası eklenir. Uygulama içerisinde bir web servisinden faydalanmak için öncelikle Solution Explorer altında yer alan projenin References menüsüne sağ tıklanarak Add Web Reference... seçilir. Açılan forma, hizmet alınacak web servisinin *.asmx uzantılı adresi yazılır ve Go butonuna ya da klavyede Enter tuşuna basılır. Bu örnek için kullanacağımız web servisinin adresi aşağıda yer almaktadır :
http://www.webservicex.net/ValidateEmail.asmx
Bu web servisi IsValidEmail adındaki web metodu aracılığıyla hizmet verir. Metot, geçerlilik testi yapılacak olan mail adresini parametre alır ve geriye bool tipinde bir sonuç döndürür. Yapılan geçerlilik testi hem mail formatını hem de mail hesabının gerçekekten var olup olmadığını test eder. Bu servis dilenirse açılan formda test edilebilir (Metot adı linkine tıklanarak bu test yapılabilir). Bu web servisini pojeye referans olarak eklemek için formun sağ tarafında yer alan metin kutusuna Web reference name girilir. Buraya verilen isim, projede isim alanı olarak kullanılacaktır. Bu isim alanı, web metodu çağırmak için örneklenmesi gereken sınıfın isim alanı olur. Ben MailValidation ismini verip ilerliyorum.
Web servisin hizmet olarak sunduğu web metot ile ilgili bilgi almak için yukarıdaki formda Service Description linkine tıklanabilir ya da tarayıcıya aşağıdaki adres yazılabilir.
http://www.webservicex.net/ValidateEmail.asmx?wsdl
Gelen xml tabanlı dosyada şu bilgiler yer alır: Web metodu içeren sınıfın adı, metodun adı, metodun aldığı parametre sayısı, parametre tipi vb. Uygulamaya referans eklendikten sonra istemci makinede oluşturulan proxy sınıfından faydalanılacaktır. Bu sınıfın isim alanı aşağıdaki formattadır :
using ProjeAdi.WebReferansAdi;
Benim projem için bu isim alanı şöyledir:
using Blog_Test.MailValidation;
Fonksiyonun yapacağı işe dönecek olursak; parametre alınan mail adresinin geçerli bir mail adresi olup olmadığını kontrol etmek için, Xml tabanlı *.wsdl dosyasından faydalanılarak istemcide otomatik olarak oluşturulan proxy sınıfından bir nesne oluşturulur. Elde edilen mantıksal sonuç geri döndürülür.
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean MailiKontrolEt(SqlString mail)
{
ValidateEmail eMail = new ValidateEmail();
return eMail.IsValidEmail(mail.ToString());
}
};
Web metodu hazırladıktan sonra oluşan assembly veritabanına yayınlanmadan (deploy) önce yapılması gereken iki adım daha vardır:
Bunlardan birincisi assemby'nin dış kaynaklara ulaşacak şekilde konfigüre edilmesidir. Bunun için Solution Explorer'da projeye sağ tıklanır ve property menüsü seçilir. Burada Database sekmesinde Permission Level, External olarak belirlenir.
İkincisi ise clr sql fonksiyonunda web servis kullanılmasından kaynaklanan bir ihtiyaçtır: Var olan assembly içerisindeki tiplerin xml serileştirme kodlarını oluşturan bir assembly'ye ihtiyaç vardır. Bu iş için .Net Framework ile birlikte gelen sgen.exe aracından (Xml Serializer Generator Tool) faydalanılır. Bu araç parametre olarak, üretilen assembly'yi alır; sonuçta başka bir assembly üretir. Üretilen bu assembly'nin, fonksiyonu içeren assembly ile birlikte sql server'a yayınlanması gerekir. Bu araç, manuel olarak kullanılabileceği gibi daha kolay bir kullanımı daha vardır. Projeye sağ tıklanarak açılan Property penceresinden Build sekmesinde en altta yer alan Generate serialization assembly, On olarak değiştirilmesi, ilk derlemede xml serileştirmeyi sağlayan assembly'nin bin/debug klasöründe oluşturulmasını sağlar.
Artık elimizde sql server'a yayınlanması gereken iki tane assembly vardır:
'D:\Gündem\Blog_Test\Blog_Test\bin\Debug' yolunda
Blog_Test.dll ve sgen.exe tarafından üretilen Blog_Test.XmlSerializers.dll.
Projeye sağ tıklanarak Deploy seçilir ve bu yolla assembly'lerin sql server'a yayınlanması ve fonksiyonun oluşturulması hedeflenir; ancak xml serileştirme assembly'sibu yolla sql server'a yayınlanamaz. Fonksiyonun assembly'si Sql Server Management Studio'da Object Explorer (F8) altında Server - Databases - AdventureWorks - Programmability - Assemblies menüsünde oluşmuştur. Xml serileştirme assembly'si ise transact sql ile manuel oluşturulur :
use AdventureWorks
create assembly XmlSerilestirmeBlog_Test
from 'D:\Gündem\Blog_Test\Blog_Test\bin\Debug\Blog_Test.XmlSerializers.dll'
Artık Sql Server'da her iki assembly'de hazırdır. Ayrıca fonksiyon da oluşturulmuş ve kullanıma hazırdır.
Şimdi dbo.MailKontrolEt() fonksiyonunundan, her yeni kayıt eklenirken yapılacak kontrolde kullanacak bir check constraint içerisinde faydalanalım. Bunun için aşağıdaki transact sql kodları ile bir tablo ve UyeMail kolonuna koyulan check constraint oluşturulabilir:
Use [AdventureWorks]
Create table [dbo].[Uyeler]
(
[UyeId] [int] IDENTITY(1,1),
[UyeAdi] [nvarchar](50),
[UyeMail] [nvarchar](50)
)
Go
Alter table [dbo].[Uyeler] WITH CHECK ADD CONSTRAINT [CK_Uyeler] CHECK (([dbo].[MailiKontrolEt]([UyeMail])=(1)))
Artık bu tabloya yapılan her yeni kayıt ekleme ve güncellemede, son mail adresi format ve geçerlilik olarak bir web servisi aracılığıyla kontrol edilmektedir.
- Eğer sql server'ın bulunduğu makine internete bağlı değil ise SocketException alınır.
- Eğer girilen mail adresi doğru formatta değil ise SoapException alınır.
- Eğer girilen mail adresi dogru formatta ancak geçerli ve şu an kullanımda olan bir mail değil ise Sql Server tarafında CheckConstraint hatası alınır.