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.
Levent Cenk ÇAĞLAR