Makale Özeti

SQL Server 2005"in .NET Programcıları tarafından en önemli ve çekici özelliği olan Assembly host edebilme özelliğini inceleyeceğiz.

Makale

.NET ile SQL Server 2005 Programlama - 2

In-Proc Managed Provider

Oluşturulan assembly dosyaları veritabanına nasıl kaydedilir, kaydedilen bir assembly veritabanında nasıl silinir gibi işlemleri bi önceki bölümde inceledik. Fakat bu DLL dosyaları nasıl oluşturulur. Oluşturulan sınıfların nasıl bir tasarıma sahip olması gerekir. Stored Procedure, Function gibi veritabanı nesneleri için nasıl bir sınıf tasarımı yapılır, bu sınıflardaki metodlar veritabanında nasıl veritabanı nesnesi haline getirilir gibi konuları In-Proc Managed Provider başlıklı bu bölümde inceleyeceğiz.

SQL Server için Assembly Oluşturmak

SQL Server 2005 üzerinde bir veritabanına kaydedilecek ve “Yönetilen Veri Tabanı Nesneleri” olarak kullanılacak sınıfları ve metodları içeren assemblyler herhangi bir .NET dili ile yazılmış olabilirler. Bu diller .NET ile uygulama geliştirme alanında genellikle tercih edilen Visual Basic.NET veya C# dillerinden bir tanesi olabileceği gibi COBOL gibi .NET derleyicisi olan herhangi bir programlama dili olabilir. Bu dokumanda örnek programlama dili olarak Visual Basic.NET kullanılacaktır.

Veritabanı nesnesi (stored procedure, function, trigger gibi) olarak kullanılmak üzere metodlar tanımlanır. .NET ortamında seçilen dil ile oluşturulan metodlar veritabanında stored procedure, function gibi veritabanı nesneleri olarak tanımlanırlar.

Oluşturulacak olan sınıf tasarımında uyulması gereken kurallar :

  • Sınıf public olmalıdır.
  • Tanımlanan metod public olmalıdır.
  • Tanımlanan metod shared olmalıdır. (C#’da static)
  • Yuvalanmış (bir sınıfın içersinde yer alan diğer sınıf) sınıf olmamalıdır.

Stored Procedure Oluşturmak

İstenilen kurallara uygun bir sınıf tasarımı yapıp assembly dosyası derlenir. Elde edilen assembly bir .dll dosyası olmalıdır. .exe uzantılı dosyalar veritabanına kaydedilemez. Bir veritabanı nesnesi oluşturmadan önce daha önce bahsettiğimiz CREATE ASSEMBLY ifadesi ile assembly dosyası veritabanına kaydedilmelidir. Ardından CREATE PROCEDURE gibi veritabanı nesnesi oluşturmak için kullandığımız ifadeler kullanılabilir.

Public Class test4

Public Shared Sub Method1()

kodlar burada

End Sub

End Class

Yukarıdaki örnek sınıf tasarımı Stored Procedure (SP) olarak kullanılacak bir metod tanımlaması içeriyor. Buradaki Method1 ismindeki metodun bir SP olarak kaydedilmesinden önce bu sınıfın bir dll haline getirilmesi ve SQL Server veritabanına kaydedilmesi gerekir. Bunun için .NET Framewok ile beraber gelen VBC.EXE derleyici kullanılabilir.

Not: SQL Server 2005 üzerine uygulama geliştirmek için Visual Studio 2005 aracını da kullanabilirsiniz. Bu araç ile CREATE ASSEMBLY, CREATE PROCEDURE gibi işlemler basit bir arabirim ile kolaylık ile yapılabilmektedir.

%WINDIR%\Microsoft.NET\Framework\v2.0.40607\vbc.exe test4.vb /t:library

Yukarıdaki kod satırında test4.vb ismindeki kod dosyası bir sınıf kütüphanesi (/t:library parametresi bunu belirler.) olarak derleniyor.

Vbc.exe derleyicisi ile .vb kod dosyası derlendikten sonra dll dosyası oluşturulur.

Dll dosyası elde edildikten sonra SP olarak tanımlanması yapmadan önce CREATE ASSEMBLY ile assemblynin veritabanına kaydedilmesi gerekir.

CREATE ASSEMBLY test4

FROM C:\test\4\test4.dll

Yukarıdaki T-SQL sorgusu ile test4.dll dosyası veritabanına kaydedilir.

Not: SQL Server’ın C:\test\4\test4.dll konumundaki dosya ile artık bir ilişkisi yoktur, assembly dosyası veritabanında sistem tablolalarında saklıdır.

Uygun kurallar ile bir sınfı tasarımı yaptık, bunu derleyip DLL dosyası haline getirdik, veritabanına kaydedilmesini sağladık. Şimdi veritabanında kataloglanmış olan bu assemblyi kullanarak bir SP oluşturmalıyız.

CREATE PROCEDURE

AS EXTERNAL NAME ..

Durumumuz için çalıştırılması gereken T-SQL sorgusu

CREATE PROCEDURE Method1

AS EXTERNAL NAME test4.test4.Method1

Yukarıdaki sorgu ile SPmiz oluşturulur ve SP çağrıldığı zaman test4 ismindeki assemblydeki Method1 çalıştırılacaktır.

EXTERNAL NAME ifadesinde sonraki bölümde verilen Method ismi büyük küçük harf duyarlıdır. Burada büyük küçük harf duyarlılığı uygulama geliştirilen dilin büyük küçük harf duyarlı olmasına bağlı değildir. Örneğin VB.NET ile yazılan programlarda büyük küçük harf duyarlılığı yoktur. Fakat SQL Server’da bir assembly kullanılması durumu söz konusu olduğunda metod isimleri büyük küçük harf duyarlılığı göstermektedir.

Oluşturulan Stored Procedure nesnesini kullanmak için aşağıdaki T-SQL sorgusunu yazabilirsiniz.

EXEC Method1

Parametre Kullanımı

Stored Procedure’ler genellikle parametre ile kullanılırlar. Parametreli bir Stored Procedure veri tabanı nesnesi oluşturmak için parametre alan bir metod tanımlanması gerekmektedir. Ardından tanımlanan bu metodun bulunduğu assembly sisteme kaydedilip, CREATE PROCEDURE ifadesi ile SP tanımlanması yapılmalıdır. Bir önceki önceki örnekten farklı olarak .NET metodunun ve SP’nin parametrik olarak tanımlanmış olması gerekmektedir.

.NET ile parametre tanımlaması yaparken kullandığımız ByRef ve ByVal ifadelerinin yönetilen bir Stored Procedure nesnesinde kullanımıda desteklenmektedir.

Aşağıdaki örnek .NET kodunu inceleyiniz.

Imports System.Data

Public Class test5

Public Shared Sub SP1(ByVal sayi As SqlTypes.SqlInt32)

sayi = 34

End Sub

Public Shared Sub SP2(ByRef sayi As SqlTypes.SqlInt32)

sayi = 34

End Sub

End Class

Yukarıdaki metodlarda dikkat edilmesi gereken bir nokta SP1’de parametrenin ByVal olarak, SP2’de ise ByRef olarak aktarılmasıdır.

Yukarıdaki kodu test5.vb ismi ile kaydettikten sonra aşağıdaki şekilde test5.dll dosyası derlenmelidir. Burada /r ile System.Data.dll’ e referans verilmelidir. Çünkü System.Data.SqlTypes namespace’i içerisindeki SqlInt32 sınıfı uygulamamızda kullanılmıştır.

%WINDIR%\Microsoft.NET\Framework\v2.0.40607\vbc.exe test5.vb

/r:System.Data.dll /t:library

C:\test\5\test5.dll konumunda derlediğim assembly dosyasını Sql Server’a kaydetmek için aşağıdaki T-SQL kodunu çalıştırıyorum. Atık veritabanında test5 adında bir assembly kayıtlı durumda.

CREATE ASSEMBLY test5

FROM c:\test\5\test5.dll

Tanımladığımız test5 assemblysi içersinde yer alan test5 ismindeki sınıfın içerisinde yer alan iki metodu (SP1 ve SP2) sistemde yönetilen birer Stored Procedure olarak tanımlamak için gerekli olan CREATE PROCEDURE deyimlerini aşağıdaki gibi yazıyorum.

CREATE PROCEDURE test5sp1

@para1 int

AS

EXTERNAL NAME test5.test5.SP1

CREATE PROCEDURE test5sp2

@para1 int OUTPUT

AS

EXTERNAL NAME test5.test5.SP2

Yukarıdaki iki CREATE PROCEDURE deyimlerinde dikkat edilmesi gereken bir nokta .net metodundaki parametre ismi ile t-sql ile tanımlanan parametre adının aynı olmak zorunda olmaması. Bir .net metodu ile CREATE PROCEDURE deyiminde tanımlanan parametreler sırasına göre eşleştirilir. İkinci tanımlamada parametrenin sonunda OUTPUT parametresi vardır bir T-SQL kuralı olan bu OUTPUT parametresi ile bu parametreden değer döneceği belirlenir. ByRef olan parametrelerde OUTPUT ifadesini parametre tanımlama yapısında kullanamak gerekmektedir. Aynı şekilde ByVal olan parametre tanımlama yapısında OUTPUT kullanmaya çalışmak CREATE PROCEDURE çalışması aşamasında hataya sebep olacak ve procedure oluşturulamayacaktır. OUTPUT ifadesi sadece ByRef tanımlamalarda kullanabilmektedir.

Not: C#’da ByRef ifadesine alternatif olarak ref ve out ifadeleri yer almaktadır. Kullanım yöntemleri ve etkileri VB.NET’deki ByRef gibidir.

Bu kodları çalıştırdıktan ve yönetilen stored procedure nesnelerini oluşturduktan sonra bunların kullanan aşağıdaki kodları yazarak ByVal ve ByRef ifadelerinin yönetilen veritabanı nesnelerine nasıl yansıdığını görebiliriz.

DECLARE @x int

SET @x=10

EXEC test5sp1 @x

SELECT @x AS ‘Sonuç’

DECLARE @x int

SET @x=10

EXEC test5sp2 @x OUTPUT

SELECT @x AS ‘Sonuç’

Görüldüğü gibi ByRef olan test5sp2 Stored Procedure nesnesinin .net metodu içerisinde parametre değişkeninde üzerinde yapılan değişiklik (yeni değer ataması) parametre olarak gelen @x değişkenine yansımıştır. Ve SELECT @x AS ‘Sonuç’ olarak yapılan sorgu ile @x değişkeninin değeri .net metodu içerisin atanan değer olan 34 olarak gözükmektedir.

Geriye Değer Döndürmek

Stored Procedure nesneleri ile User Defined Function nesnelerinde olduğu geriye bir değer döndürülebilir. User Defined Function nesnesinden farklı olarak nesne tanımlaması (CREATE .. ) yapılırken Stored Procedure nesnesinde geri dönüş değeri için veri türü tanımlaması yapılmaz.

Imports System.Data

Imports System.Data.SqlTypes

Public Class test6

Public Shared Function ArtiBir(ByVal sayi As SqlInt32) As SqlInt32

Return sayi + 1

End Function

End Class

Yukarıdaki sınıf tasarımı ile ArtiBir ismiden bir function tanımlanıyor. Bu Function aldığı parametrede ki tam sayı değeri bir arttırıp geriye dönüyor.

%WINDIR%\Microsoft.NET\Framework\v2.0.40607\vbc.exe test6.vb /r:System.Data.dll /t:library

Bu kaynak kodu derleyip asseembly dosyasına çevirmek için yukarıdaki kodu kullanıyoruz. Bu komut satırı derleyicisi çalıştıktan sonra test6.dll oluşturuluyor.

CREATE ASSEMBLY test6

FROM C:\test\6\test6.dll

Oluşan assembly dosyasını veritabanına kaydetmek için yukarıdaki CREATE ASSEMBLY T-SQL ifadesini kullanıyoruz.

CREATE PROCEDURE ArtiBir

@sayi int

AS EXTERNAL NAME test6.test6.ArtiBir

test6 isminde assmebly veritabanında oluşturulduktan sonra bu assemblydeki test6 sınıfında bulunan ArtiBir ismindeki metodu bir yönetilen Stored Procedure olarak tanımlamak için yukarıdaki kodu çalıştırıyoruz. Burada dikkat edilmesi gerekn nokta parametrelerin .net metodu ile aynı sırada olması ve veri türü uygunluğudur. Burada tek parametre olduğu için sıra sorunu yoktur fakat veri türünün uygun olması dikkat edilmesi gereken bir noktadır.

DECLARE @x int

EXEC @x=ArtiBir 34

SELECT @x AS Sonuç

Yönetilen Stored Procedur oluşturulduktan sonra test etmek için yukarıdaki kodu yazıyoruz. Burada @x isminde int türünde bir değişken tanımlanıyor, EXEC @x=ArtiBir 34 satırı ile @x değişkenine ArtiBir 34 SP çağrısının sonucu (35) atanıyor. SELECT ile görüntülenen @x değerinin sonucu aşağıda 35 olarak gözükmektedir.

User Defined Function Oluşturmak

SQL Server için .NET ile Function oluşturmak Stored Procedure oluşturmaktan pek farklı değildir.

CREATE FUNCTION

(

)

RETURNS

AS EXTERNAL NAME ..

Yukarıdaki şemada gözüktüğü gibi bir T-SQL ifadesi ile .NET assemblysi içinde yer alan bir metodu SQL Server 2005 Function nesnesi olarak tanımlayabilirsiniz.

bölümünde fonksiyona verilecek olan isim yazılmalıdır. bölümünde ise Function’a verilecek olan parametreler yazılmalıdır.

Örneğin aldığı iki tam sayı parametreyi toplayıp geriye sonucunu dönecek olan bir function yazmak için aşağıdaki adımlar izlenmelidir.

Imports System.Data

Imports System.Data.SqlTypes

Public Class test7

Public Shared Function Topla(ByVal sayi1 As SqlInt32, _

ByVal sayi2 As SqlInt32) As SqlInt32

Return sayi1 + sayi2

End Function

End Class

Yukarıdaki kodu test7.vb olarak kaydettikten sonra aşağıdaki şekilde derlenir.

%WINDIR%\Microsoft.NET\Framework\v2.0.40607\vbc.exe test7.vb /r:System.Data.dll /t:library

Derlenen assembly dosyası test7 adıyla veritabanına kaydedilir.

CREATE ASSEMBLY test7

FROM C:\test\7\test7.dll

test7 assemblysinde yer alan test7 sınıfının içindeki Topla metodu aşağıdaki CREATE FUNCTION T-SQL ifadesi ile yönetilen bir function olarak tanımlanır.

CREATE FUNCTION Topla

(

@sayi1 int,

@sayi2 int

)

RETURNS int

AS EXTERNAL NAME test7.test7.Topla

Tanımlandığımız function nesnesini kullanmak için aşağıdaki SELECT ifadesini çalıştırıyoruz. Sonuç resimde gözükmektedir.

SELECT dbo.Topla(8,9) AS Sonuç





İlgili makaleler :
.NET ile SQL Server 2005 Programlama - 1
.NET ile SQL Server 2005 Programlama - 2
.NET ile SQL Server 2005 Programlama - 3



Cengiz HAN

Microsoft ASP.NET MVP

cengiz.han@yazgelistir.com

www.cengizhan.com