Makale Özeti

SQL Server 2005 ile duyurulan CLR desteğini Stored Procedureler ile kullanmak için, önceki makaleden daha gelişmiş örnekleri içeren bir makale

Makale

CLR Destekli Stored Procedure Yazmak - II

Bir önceki makalemizde, Stored Procedure yazmıştık, bu Stored Procedure bir değer almadan, sadece bir çıktı yolluyordu. Daha sonrada yazdığımız bu .Net Assembly sini nasıl SQL Server a deploy edeceğimizi öğrenmiştik. Bu makalede ise, bir parametre alıp, bu parametreye göre sonucu döndüren, parametrenin OUTPUT tipini nasıl kullanacağımızı, Return değerini nasıl kullanacağımızı ve birazda kopmleks bir örnek yaparak, .Net ile yazdığımız bu Stored Procedureyi nasıl kullanabileceğimizi göreceğiz.

Daha önceden oluşturmuş olduğumuz, SQL Server Project tipindeki projemize kod eklemeye devam ediyoruz,

//buraya dikkat, lafları buna göre düzenlemek lazım.

Biliyorsunuzki genelde Stored Procedure ler bir yada daha çok parametre alır. Peki ben ya bir parametreye göre veri döndürmesini istiyorsam yani, ben AddressTypeId yi verip geriyede verdiğim bu Id ye sahip olan satırın döndürülmesini istiyorsam,

hemen aşağıdaki kodu, bir önceki koda ekleyin.

    <Microsoft.SqlServer.Server.SqlProcedure()> _

        Public Shared Sub GetPersonAddressTypeByAddressTypeId(ByVal parAddressTypeId As Integer)

        Dim conn As SqlConnection = New SqlConnection

        conn.ConnectionString = "Context Connection=true"

 

        Dim comm As SqlCommand = New SqlCommand

        comm.CommandText = "SELECT * FROM Person.AddressType WHERE AddressTypeId = " & parAddressTypeId.ToString()

        comm.Connection = conn

        Dim sp As SqlPipe = SqlContext.Pipe

        conn.Open()

        sp.Send(comm.ExecuteReader)

        conn.Close()

 

    End Sub

Buradaki fark aslında parametre olarak bir değer vermekten geçiyor, ben fonksiyonuma .Net tiplerinden olan Integer tipinden bir değişken tanımladım ve bunu sorgulama mantığıma WHERE deyimi ile ekledim (Sql den bildiğiniz üzere). Aslında bu Stored Procedure yi bildiğiniz parametre ekleme yöntemi ilede yazabilirim. Aşağıda bunun örneğini bulacaksınız.

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub GetPersonAddressTypeByAddressTypeId2(ByVal parAddressTypeId As Integer)

        Dim conn As SqlConnection = New SqlConnection

        conn.ConnectionString = "Context Connection=true"

 

        Dim comm As SqlCommand = New SqlCommand

        comm.CommandText = "SELECT * FROM Person.AddressType WHERE AddressTypeId = @parAddressTypeId"

        comm.Parameters.Add("@parAddressTypeId", SqlDbType.Int).Value = parAddressTypeId

        comm.Connection = conn

        Dim sp As SqlPipe = SqlContext.Pipe

        conn.Open()

        sp.Send(comm.ExecuteReader)

        conn.Close()

 

    End Sub

Burada gördüğünüz gibi WHERE deyiminden sonra @parAddressTypeId yi parametre olarak tanımladım ve daha sonra Command a Parametre ekledim.

Daha sonra Build edin ve hata yoksa Deploy edin. SQL Server Management Studio yu açın (eğer gözükmüyorsa refresh yapın) Stored Procedures bölümüne son eklediğim Stored Procedurenin de eklendiğini göreceksiniz. Şimdi bu Stored Procedure ü çalıştıralım, unutmayın bu Stored Procedure sizden bir rakam bekliyor veriyi ona göre döndürecek.

 

Yukarıdaki şekildende nasıl kullanacağınıza bakabilirsiniz. Şimdi OUTPUT tipli bir parametre örneği yapalım, aslında kullanacağımız deyim, .Net ten aşina olduğunuz, ByRef deyimi (C# için ise out deyimi) aşağıdaki kodu daha önceki projedeki class a ekleyin.

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub GetPersonAddressTypeOutName(ByVal parAddressTypeId As Integer, ByRef parAddressTypeName As String)

 

        Dim conn As SqlConnection = New SqlConnection

        conn.ConnectionString = "Context Connection=true"

 

        Dim comm As SqlCommand = New SqlCommand

        comm.CommandText = "SELECT * FROM Person.AddressType WHERE AddressTypeId = " & parAddressTypeId.ToString()

        comm.Connection = conn

        conn.Open()

        Dim dr As SqlDataReader = comm.ExecuteReader()

        dr.Read()

        parAddressTypeName = dr.GetString(1)

        conn.Close()

    End Sub

Gördüğünüz gibi Stored Procedure üm iki tane parametre alıyor, bunlardan bir tanesi, normal olan sadece veriyi getiren, ancak diğeri ise (parAddressTypeName) ByRef ile tanımlanmış durumda, yani bulunan değeri geri döndürecek yapıda. Buradaki fark ise parAddressTypeId değişkenimi kullanarak dönen satırdaki ikinci elemanı almak, yani Name sütununda olan  veriyi. Gördüğünüz gibi kod çok basit ve bizim Ado.Net te yazdığımız yapının nerdeyse aynısı.

Kodunuzu Build edin ve hata yoksa Deploy edin, SQL Server Management Studio ile açın ve yeni yazdığınız Stored Procedurenizin burada olduğuna dikkat edin. Şimdi bu OUTPUT tipli parametreyi nasıl kullanacağımıza bakacağız. Normal bir OUTPUT tipli parametreyi kullanmadan farkı yok. Aşağıdaki kodu deneyin.

DECLARE @AddressTypeName nvarchar(20)

EXEC dbo.GetPersonAddressTypeOutName 2, @AddressTypeName OUTPUT

SELECT @AddressTypeName

Sonuç olarakta çıktıyı aşağıda bulabilirsiniz.

 

Son adım olarakta Stored Procedure nin geriye değer döndürmesi durumunu inceleyelim. Stored Procedure de nasıl return deyimini kullanıyorsak buradada aynı işi yapacağız, evet şu zamana kadar hep Sub yazmıştık, şimdi Function yazacağız. Aşağıdaki örneği inceleyin.

    <Microsoft.SqlServer.Server.SqlProcedure()> _

        Public Shared Function GetPersonAddressTypeReturnCount() As Integer

        Dim conn As SqlConnection = New SqlConnection

        conn.ConnectionString = "Context Connection=true"

 

        Dim comm As SqlCommand = New SqlCommand

        comm.CommandText = "SELECT Count(*) FROM Person.AddressType"

        comm.Connection = conn

        conn.Open()

        Dim returnValue As Integer = CInt(comm.ExecuteScalar())

        conn.Close()

        Return returnValue

    End Function

Gördüğünüz gibi bu sefer bir Function tanımladım ve geri dönüş tipi olarak Integer seçtim. Yazdığım bu kod hiç parametre almıyor, parametre alan halinide yazabilirdik, bu kod parçacığı kaç tane kayıt olduğunu buluyor ve bunu fonksiyonun geri dönüş değeri ile yolluyor. Daha önceden hep ExecuteReader Metodunu kullanıyordum command ın, bu sefer ise ExecuteScalar Metodunu kullanıyorum. Sonuç olarak bulduğum değeride return ile fonksiyonun geri dönüş değeri şeklinde yolluyorum. Dikkat edin, yine .Net ile gelen primitive tipleri kullanıyorum, Sql tiplerini değil.

Yine kodunuzu Build edin ve eğer problem yoksa tekrar Deploy edin. Yeni Stored Procedureniz eklenmişmi diye baktıktan sonra aşağıdaki kodları SQL Server Management Studio ile yeni bir Query Penceresi açarak yapıştırın.

DECLARE @ReturnValue int

EXEC @ReturnValue = dbo.GetPersonAddressTypeReturnCount

SELECT @ReturnValue

Sonucu için aşağıdaki ekran görüntüsüne bakabilirsiniz.

Şimdi daha gelişmiş bir örnek yapalım, bu Stored Procedure mizi şöyle kullanabiliriz, örneğin Data katmanınızda başka bir Insert Stored Procedure ünü çalıştırdıktan sonra eğer hata alırsanız kullanabileceğiniz bir şekilde yada diğer Stored Proecedure lerinizin içinde hata oluşursa (SQL Server 2005 ile T-Sql deyimlerindede Try – Catch bloklarını kullanabiliyorsunuz) kullanabileceğiniz bir halde oluşturdum. Projenize yeni bir Stored Proecure dosyası ekleyin, Kod için aşağıya bakabilirsiniz.

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.IO

 

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub Logs(ByVal parErrorMessages As String, ByVal parLogType As Boolean)

        If parLogType = True Then

            LogToText(parErrorMessages)

        Else

            LogToEventLog(parErrorMessages)

        End If

    End Sub

    Public Shared Sub LogToText(ByVal parErrorMessages As String)

        Dim fileName As String = "C:\logs.dat"

        Dim fs As FileStream = New FileStream(fileName, FileMode.Append, FileAccess.Write)

        Dim sw As StreamWriter = New StreamWriter(fs)

        sw.WriteLine(Environment.NewLine & "--------------------------")

        sw.WriteLine("Log Time  : " & DateTime.Now.ToShortDateString() & " " & DateTime.Now.ToShortTimeString())

        sw.Write("Log Message : " & parErrorMessages)

        sw.Flush()

        sw.Close()

    End Sub

    Public Shared Sub LogToEventLog(ByVal parErrorMessages As String)

        Dim el As System.Diagnostics.EventLog = New System.Diagnostics.EventLog

        el.Source = "MyApplication"

        el.WriteEntry(parErrorMessages)

    End Sub

End Class

Gördüğünüz gibi bir tane SqlProcedure Attribute ile işaretlenmiş metot var, diğerleri yardımcı metotlar, aslında sizin SQL Server da kullanacağınız metot bu metot. Bu metot iki parametre alıyor, bunlardan ilki hata mesajı, diğeride hatayı nereye loglayacağı. Yardımcı metotları anlatmayacağım, sadece bir tanesi sizin verdiğiniz bir log dosyasına bu hataları kaydediyor (c:\logs.dat) diğeri Event Log a yazıyor. Kodunuzu Build ettikten sonra Deploy edin. SQL Server Management Studio da, Stored Procedures klasörünün altında dbo.Logs u gördüyseniz denemeye başlamadan önce bir ayar daha yapamamız gerekiyor, bu eklediğimiz Assembly i UNSAFE olarak işaretlememiz gerekiyor, böylece hard disk teki bir dosyaya erişip, ona işlem yapabileceğiz. Bu işlem için AdventureWorks altındaki Programmability nin içindeki Assemblies i açın orada ekli olarak gözüken YazGelistirOrnek Assemblysini (Hatırlarsanız ilk makalede Projemizin adını bu vermiştik ve oluşan Assembly mizin adıda bu olmuştu) bulun sağ tuş özellikler yapın ve Permission Set bölümünü UNSAFE e getirin. Şimdi deneyelim.

EXEC Logs 'Hata Mesajim',0

İle bir kere çalıştırın ve daha sonra Computer Management ten, Event Viewer bölümünden Application a bakın,

 

Şimdi bir kerede 1 parametresi ile çağırıp, Log dosyasına kaydetmesini sağlayalım.

EXEC Logs 'Hata Mesajim',1

 

Aslında ekleyecek bir konu daha kaldı, bunun için bir sonraki makalem olan CLR Destekli Trigger Yazmak makalesine bakabilirsiniz. Bu makale seriisinde CLR Destekli olarak Stored Procedure ler yazdık. İstek, Öneri ve eleştirileriniz için aşağıdaki mailimi kullanabilirsiniz.

 

Levent Cenk ÇAĞLAR

cenk.caglar@yazgelistir.com