Stored Procedure'den Başka Bir SP Çağırmak (Output Parametresi)

Bazen sql ile geliştirme yaparken, birden fazla procedure ile çalışmak zorunda kalabiliriz.Yani bir procedure'in çalışmasıyla ortaya çıkan sonucu, diğer bir procedure içerisinde kullanabiliriz. Bu tarz durumlarda, yazılım dillerindeki fonksiyon mantığıyla, o procedure'ü çağırmamız gerekir. Oradan dönen sonucu da mevcut procedure'ümüzde kullanırız.

Şimdi ufak bir senaryo üzerinden devam edersek daha iyi anlaşılacaktır. Öğrenci tablomuz olsun. ÖğrenciEkle ismindeki Stored Procedure'müz, tablomuza kayıt atsın. Ayrıca OgrenciLog tablosuna da, hangi öğrenci kayıt atılırsa, onun id'sini ekleyelim. Aslında bu örnekte birden fazla sp'ye ve tabloya gerek yok ama, sadece birbiriyle olan ilişkileri göstermek amacıyla basit bir örnek olarak düşündüm. Normalde, birbirinden bağımsız, 30-40 kolonluk bir tablo olduğunu düşünebilirsiniz...

İlk procedure'müz basit olarak insert işlemi yapacak, ardından oluşacak id'yi de output parametresi yardımıyla, dışarıya aktaracağız. Yazacağımız sp kodu şu şekilde olmalıdır.

create procedure sp_OgrenciEkle

(

@Ad nvarchar(20),

@Soyad nvarchar(20),

@No bigint,

@Okul nvarchar(20),

@Id int output

)

as

begin

      insert into Ogrenci values (@Ad,@Soyad,@No,@Okul)

      set @Id = SCOPE_IDENTITY()

end

Şimdi OgrenciLog tablomuzu dolduracak procedure'mizde bu id output değerini alıp, tabloya yazalım. Bunu yaparken de, "sp_OgrenciEkle" isimli procedure'u çağırmamız gerekecek. Kodlama şu şekilde olmalıdır;

alter procedure sp_OgrenciLog

as

begin

      declare @Id int

      exec.sp_OgrenciEkle @Ad='Ergin',@Soyad = 'Arslan', @No=1234, @Okul='YTU' , @Id = @Id output

      insert into OgrenciLog (OgrenciId) values (@Id)

end

Gördüğünüz gibi exec.Procedure_Adı diyerek procedure'e eriştim ve aldığı parametreleri değerleriyle birlikte yazarak, en son da geriye döndürdüğü değeri @Id isimli değişkenimize atıp, OgrenciLog tablomuza da değeri girmiş olduk. sp_OgrenciLog tablosunu çalıştırdığımız zaman, önce sp_OgrenciEkle procedure'u çalışacak, Ogrenci tablosuna kayıt attıktan sonra oluşan id'yi Scope_Identity() ile alıp output geçerek, bizim proc'umuza atacak ve biz de o değeri OgrenciLog tablomuza atacağız.

Dediğim gibi, böyle küçük örneklerde kullanılmaz output parametresi. Birden fazla procedurelar ile, 30-40 alanlı tablolara kayıt atarken, bazı alanları, bazı sp'lerin sonucundan almamız gerekebilir. İşte bu durumlarda, yukarıdaki gibi exec.SP_Adi diyip aldığı parametreleri değerleriyle birlikte, en sonda output parametresi olacak şekilde çalıştırırsak, o proc'un döndürdüğü değeri elde etmiş oluruz.

Sql Recursive Functions(Rekürsif Fonksiyonlar)

Recursive fonksiyonlar, kendi kendini çağıran fonksiyonlardır. C#'ta da vardır, Java'da da , C'de de. Basit bir mantık, kendi işlemini yaptıktan sonra, tekrar kendi fonksiyonunu çağırması ve tekrar kendi içinde bulunan işlemleri tekrarlamasıdır. Sql'de de böyle bir işleme ihtiyacımız olabilir. Örneğin, bir kategori kendi içinde alt kategorileri olsun, o alt kategorilerin de altında başka kategoriler daha olsun. Gördüğünüz gibi, bir hiyerarşi oluşmuş. Biz sadece bir taban altına gitmemiz, doğru bir yaklaşım olmayacaktır. En alt katmana gelene kadar, kendi kendini sürekli çağırmalı ve gerekli işlemler yapılmalıdır.

Recursive fonksiyonlara verilebilecek en klasik örnek, faktöriyel hesabıdır. 5! dediğimiz zaman, önce 5 diyecek, daha sonra kendini çağırıp 5.4 olacak, daha sonra yine kendini çağırıp 5.4.3 diye diye 0 olana kadar bu kendini çağırma işlemi devam edecek.

Benzer senaryo, Sql'de de karşımıza gelebilir. Örneğin, AdventureWorks veritabanı üzerinde, HumanResources.Employee tablosunda, id'si 4 olan personelin bağlı olduğu şef, o şefin bağlı olduğu diğer şef, o şefin bağlı olduğu diğer şef ... vs. Genel Müdür gelene kadar bu işlem devam etmesini istiyorsak, gördüğünüz üzere bu hiyerarşik yapıyı kullanmak için recursive fonksiyon yazmamız gerekir. Kodlama aşağıdaki gibi olmalıdır;

with CTE as

(

      select EmployeeID,ManagerID,Title from HumanResources.Employee

      where EmployeeID=4 --baslangic id

      union all

      select HE.EmployeeID,HE.ManagerID,HE.Title from HumanResources.Employee HE join CTE

      on HE.EmployeeID=CTE.ManagerID

)

select * from CTE

Sonuç kümesi de aşağıdaki gibi olur;

Kodlama hakkında bilgi vermek gerekirse, öncelikle CommonTableExpression yazmamız gerekir. Bunu herhangi bir yazılım dilindeki fonksiyonlara benzetebilirsiniz. Orada o fonksiyonu sürekli çağırırken, burada da sürekli CTE ile joinlediğimiz zaman, sürekli bir üst katmana gittiğini göreceğiz. Rekürsif fonksiyonlar, ikinci sorgumuz -CTE ile join yaptığımız- boş sonuç verene kadar devam eder. Ama bu işlem ciddi performans kayıplarına yol açmaması için, Sql Server default'u olarak 100 verilmiş. Yani en fazla 100 kez bu işlem devam edecektir. Ama biz en fazla kaç kere dönmesini de değiştirebiliriz. Bunun için yazmamız gereken kod, Select * from CTE'nin sonuna 

OPTION(MAXRECURSION 4)

yazmak olmalıdır.

Sql Pivotlama Kavramı

Pivotlama, kısaca satırların sütuna taşınma işlemleridir. Özellikle kurumsal firmalarda, rapor çekilirken, raporun daha okunur kılınması için veya oluşacak binlerce satırlık raporu minimize etmek için pivotlama yöntemi kullanılır. 2005 öncesi case-when yapısı kullanılarak pivotlama işlemi yapılırken, 2005 sonrası dinamik pivotlama yardımıyla artık daha kolay yapmaktayız.

Şimdi kısaca bir senaryo uydurup, beraber pivotlama yöntemiyle sorgumuzu yazalım.

Senaryomuz kısaca, AdventureWorks database'i içinde departman bazında kaç erkek, kaç bayan çalıştığını bulalım. Normal sorgulamamızı yaparsak önce departmanlara göre kaç bayan var, sonra yine departmanlara göre kaç erkek var alt alta göreceğiz, dolayısıyla satır sayısı fazla, okunulurluğu yok. Nasıl oluştuğunu görelim önce.

Sorgumuzu yazmak için 3 tabloya ihtiyacımız var. Bunlar Department, Employee ve EmployeeDepartmentHistory. Bunları birbirine joinledikten sonra cinsiyet ve departmana göre group by yapıp toplam sayı bizim istediğimiz sonuc olacak. Burada yeri gelmişken, kısaca sql'in kendi editöründen nasıl joinleme işlemlerini kolayca yapabileceğimizi göstereyim. Sorgu sayfasında herhangi bir yere sağ tıklayıp design query in editor dedikten sonra önümüze bir ekran gelecek. Add Table penceresinden yukarıda bahsettiğim üç tabloyu seçtiğimizde önümüze şu şekilde bir diyagram getirecek.

Yukarıda da görüldüğü gibi, bize sadece Gender ve Name kolonu gerektiğinden sadece onları seçiyoruz. Tabloların altında sql'in bizim için kodu kendi oluşturduğunu görüyoruz.Daha sonra oluşan kodu çalıştırdığımız zaman aşağıdaki şekilde bir sonuç kümesi geleceğiniz görürüz.

Ama biz bunu pivotlama yöntemiyle yaparsak, aşağıdaki gibi sorgu yazmamız gerekecekti. Sorgunun mantığını aşağıda anlatacağım. Önce ortaya çıkan rapor'a bakalım,

Yazacağımız kod da şu şekilde olmalıdır.

WITH CTE

as

(

SELECT Department.Name as Departman,Employee.Gender  as Cinsiyet,COUNT(HumanResources.Employee.EmployeeID) [count] FROM HumanResources.Department

INNER JOIN   HumanResources.EmployeeDepartmentHistory ON HumanResources.Department.DepartmentID = HumanResources.EmployeeDepartmentHistory.DepartmentID

INNER JOIN HumanResources.Employee ON HumanResources.EmployeeDepartmentHistory.EmployeeID = HumanResources.Employee.EmployeeID

group by Gender,Department.Name,Department.ModifiedDate                     

)

      select * from CTE PIVOT (SUM([count]) for Cinsiyet in ([F],[M])) as p  

Önce CommonTableExpression ile bir tablo oluşturuyoruz. Aslında bizim pivotsuz kodumuz budur. Zaten yukarıdaki resimden de query editorun yazdığı koddan karşılaştırabilirisiniz.Bu CTE'yi yazdıktan sonra bu tablodan select * from'la tüm verileri alıyoruz fakat, 2005 yılından sonra gelen PIVOT konumu ile toplam kayıt sayısına göre Cinsiyetleri üst kolona köşeli parantez içinde yazarak kolonlara taşımış oluyoruz.

Not: En sondaki yazdığımız select sorgusunun sonuna as p diyerek bir alias veriyoruz. Bu Sql'in gözden kaçırdığı veya neden böyle bir şeyi yaptırmaya mecbur tuttuğunu anlamadığım bir zorlamasıdır. Pivotlama yaptıktan sonra, yazdığımız o sorguya alias vermek zorundayız. Aksi taktirde hata mesajı verecektir. Bu yüzden rasgele bir alias verebilirsiniz, zira daha sonra herhangi bir yerde kullanılmayacaktır.

Asp.Net'de IIS Hatası

Yazdığımız web sayfasını IIS'imize ekledikten sonra, çalıştırmak istediğimizde şu hatayı alırsak;

İstediğiniz sayfa uzantı yapılandırması nedeniyle sunulamıyor. Sayfa bir komut dizisi ise, bir işleyici ekleyin. Dosyanın karşıdan yüklenmesi gerekiyorsa, bir MIME eşlemesi ekleyin.

anlamalıyız ki, IIS'imiz aspx sayfalarını tanımıyor. Bunu düzeltmemiz için yapmamız gereken şey;

Başlat-Tüm Programlar-Microsoft Visual Studio 2010- Visual Studio Tools - Command Prompt 2010 'u açıp,

aspnet_regiis -i yazmak olmalıdır.

Eğer hata olarak, admin olarak girin tarzı bir hata gelirse,

run cmd diyip,

cd c:\Program Files (x86)\Microsoft Visual Studio 2010\VC 

yazıp, tekrar 

aspnet_regiis -i yazmalıyız.

Bu yaptıklarımızla, aspx dosyalarını iis'e yüklemiş oluruz. Artık localhost\sayfa.aspx dersek, sayfamızın açıldığını görürüz.

LinqToXml

Xml, -eXtensible Markup Language- kısacası verileri elde etmek için, taşımak için vs. platform bağımsız çalışabilen, yani javacısı c#'cısı, php'cisi tarafından kullanılan bir işaretleme dilidir. Günlük hayatımızda sıkça xml dosyaları ile uğraşırız. Ya xml dosyasından veri okuruz, veya kendi xml'imizi okuyup, başka servislerin kullanımına sunarız. İşte bu Xml okuma ve yazma işlemlerini inceleyeceğiz birazdan.

C# 3.0 ile birlikte gelen LINQ teknolojisiyle, bu işlem daha da kolaylaşmış oldu. Artık onlarda satırlık xml oluşturma işlemini tek satıra bile sığdırabiliyoruz. Şimdi sırasıyla Xml okuma, güncelleme ve oluşturma işlemlerini Linq teknolojisini kullanarak örneklemeye çalışacağım.

Öncelikle bir xml dosyası oluşturup, buradan verileri okuyalım. Oluşturacağımız Urunler.xml dosyası aşağıdaki gibi olsun.

1-Xml Okuma: Öncelikle xml dosyasını yüklememiz gerekecek.

 XDocument doc = XDocument.Load("..\\..\\Urunler.xml");

Xml dosyamızı yükledikten sonra, artık tagler içinde dolaşabiliriz. Xml formatı gereği, en dışta tek element bulunması gerekir. Onun içinde birden fazla element bulunabilir ama. Buna göre, yazacağımız kodlarda şu iki metodu çok iyi bilmemiz gerekecek;
Element() ve Elements() Metotları...

Adım adım xml dosyasında dolaştığımızdan, en dıştan içe doğru girerken, önce kök elementini göreceğiz. Ürünler xml'inde tag'i bizim için kök elementtir. Yani tektir. Bu yüzden Element("Urunler") metodunu kullanmalıyız. Ardından birden fazla Urun olduğundan, Element("Urun") metodunu kullanacağız. Yani;

doc.Element("Urunler").Elements("Urun");

Bu yazdığımız kod, geriye IEnumerable  döndürür. Yani bir sorgudur bu yazdığımız kod. Bu sorgunun içinde foreach ile gezersek, Tüm ürün sınıfının altındaki elementlere ulaşmış olacağız. Yani ; 

foreach (XElement urun in doc.Element("Urunler").Elements("Urun"))
{
      Console.WriteLine("{0}-{1}", urun.Element("ID").Value, urun.Element("Ad").Value);
}

Bir örnek daha yazalım, daha anlaşılır olsun. Fiyatı 100'den küçük olan kaç tane ürün olduğunu ekrana yazdıralım.

Console.WriteLine(doc.Root.Elements().Count(s => int.Parse(s.Element("Fiyat").Value) < 100));

2-Xml Oluşturma : Yine aynı şekilde xml'imizi yükleyelim.

 XDocument doc = XDocument.Load("..\\..\\Urunler.xml");

Yine mantık aynı. en dışta kök elementi, ardından ürün sayısı kadar alt elementler oluşacak. Eğer kök elementinin içinde tek element varsa new Element() ile tek bir element oluşturuyoruz, birden fazla var ise new Elements() diyip yeni elementleri parametre olarak veriyoruz. Örnekte daha da iyi anlaşılacaktır.

 XElement yeni = new XElement("Urun",
                                   new XElement("ID", 4),
                                   new XElement("Ad", "Laptop"),
                                   new XElement("Fiyat", 1000));
            doc.Root.Add(yeni);

Yukarıda da anlayacağınız gibi, kök elementimize tek bir ürün elementi ekledik (XElement). Urun tag'inin içine de ID,Ad ve Fiyat elementlerini virgülle ayrılacak şekilde yazdık. Alt satıra geçmesi kafa karıştırmasın, tamamiyle kod okulurluğu sağlanması amaçtır. buna göre xml'imize Urun tagi içinde ID,Ad ve Fiyat bilgileri de eklenmiş olacaktır.

3-Xml Güncelleme: Diğer iki işlemlere göre daha basit bir işlem gerektirecek. Direk örnek üzerinden anlatmaya çalışacağım, anlaşılır olacaktır.

//1 idli ürünün fiyatını değiştirelim 400 olsun
  XElement urun = doc.Root.Elements().FirstOrDefault(s => s.Element("ID").Value == "1");
  urun.SetElementValue("Fiyat", 400);

Önce id'si 1 olan ürünü getirip, SetElementValue metodunu kullanarak, Fiyat'ını 400 yaptık. Oldukça basit bir işlem.

         Gördüğünüz gibi LINQ to XML ile, xml dosyası oluşturmak, var olan dosyada gerekli değişiklikler yapmak veya dosyadan veri okumak oldukça kolaylaştı. Eski yöntemlerde hata yapma oranı oldukça fazla olmasının yanında, onlarca satırlık kodu, 2-3 satıra sığdırmış olacağız. Bu konuyu öğrenmekte büyük fayda var, çünkü xml her ortamda karşımıza çıkacak olan bir konudur. Eğer linq biliyorsanız veya bilmeseniz bile kodları biraz kurcalamanız sonucunda rahatlıkla, verilen işlemleri yapabilir duruma geleceksiniz.