21 Tem by NURULLAH ÇAKIR

CLR(Common Language Runtime)

CLR(Common Language Runtime) sayesinde TSQL ile yapamayacağınız bir çok kompleks işlemi .NET Framework’ünü kullanarak gerçekleştirebilir ve ürettiği dll’i SQL Server’a import ederek veritabanı seviyesinde kullanabilirsiniz.

Ayrıca stored procedure’leri, trigger’ları, user-defined type’ları, user-defined function’ları (scalar ve table-valued) ve user-defined aggregate function’ları herhangi bir net.framework diliyle yazabilirsiniz.

Not: Visual Studio 2003 üzerinde bu işlemleri gerçekleştiremezsiniz.

CLR’yi bir örnek yaparak netleştirelim. Öncelikle instance seviyesinde CLR’nin aktif hale getirilmesi gerekiyor.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Daha sonra Visual Studio ile bir kod hazırlayalım. Ben diskler’deki boş alanı çıkaran aşağıdaki kodu içeren bir program yazdım. Sizde bunu kullanabilirsiniz. Makalemizde bu kodun oluşturduğu dll’i sql server’a import edeceğiz ve daha sonra bu dll’i kullanarak tsql ile işletim seviyesi seviyesinde diskler’deki boş alanı çıkaran script’i çalıştıracağız.

Ben uygulamamı Visual Studio 2010 kullanarak hazırlayacağım. Öncelikle aşağıdaki gibi Visual Studio üzerinden yeni bir SQL CLR Projesi açacağız.

Karşımıza gelen ekranda aşağıdaki gibi Database sekmesinden SQL Server’ı ve hemen ardından Visual C# SQL CLR Database Project’i seçiyoruz.

Eğer kodunuzu Visual Basic ile yazmak isterseniz Visual Basic SQL CLR Database Project’i seçmelisiniz.

Uygulamamızın adına SqlServerCLRUygulamasi adını verdik.

OK’ e bastığımda aşağıdaki gibi bir uyarı veriyor. Bu uyarıda, uygulamamızı SQL Server 2005 ya da SQL Server 2008 için geliştiriyorsak uygulamayı geliştirdiğimiz sunucuda .NET Framework 3.5’in yüklü olması gerektiğini ve uygulamanın kullandığı .NET Framework versiyonunu projenin properties sekmesinden değiştirebileceğimizi söylüyor.

Aşağıdaki şekide Solution Explorer’dan proje’ye sağ tıklayarak properties dedikten sonra .net framework ayarını yapabilirsiniz.

Tamam dedikten sonra karşımıza kodu geliştireceğimiz SQL Server Instance’ını seçeceğimiz ekran geliyor. Add New Reference diyoruz.

Karşımıza çıkan ekranda Server name kısma dll’i yükleyeceğimiz instance ismini, Connect to database kısmına’da ilgili veritabanı ismini aşağıdaki gibi yazıyoruz.

Ok’e tıkladığımızda aşağıdaki gibi bir uyarı veriyor. Bu uyarıda SQL/CLR’nin aktif edilmesi gerektiğini yazıyor.

En sonda ise SQL/CLR’yi aktif etmek ister misiniz sorusu var. Evet’e tıklayarak devam ediyoruz.

Aşağıdaki kod yardımıyla da CLR’yi instance seviyesinde aktif edebilirsiniz.

sp_configure 'show advanced options',1
reconfigure
GO
sp_configure 'clr enabled',0
reconfigure

Proje açıldıktan sonra aşağıdaki gibi Add diyerek istediğiniz Item’ı ekleyebilirsiniz.

Biz yukardaki ekrandan Class’ı seçiyoruz ve gelen sayfada aşağıdaki kütüphaneleri ekliyoruz.

using System.IO;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

Class’ımızı ve fonksiyonumuzu sql server’a import sırasında hata almamak için yukardaki gibi public yapıyoruz. Ayrıca fonksiyonun static olması da önemli.

Kodu kopyalayabilmeniz için aşağıda paylaşıyorum.

[SqlProcedure]
        public static void GetTotalFreeSpace(String isim, out String mesaj)
        {
            mesaj = "";
            foreach (DriveInfo drive in DriveInfo.GetDrives())
            {
                if (drive.IsReady && drive.Name == isim)
                {
                    mesaj = Convert.ToDecimal(drive.TotalFreeSpace / 1024.0 / 1024.0 / 1024.0).ToString("#.##");
                    break;
                }
                else
                {
                mesaj="Disk Bulunamadı";
                }
            }
        }

Daha sonra projemizi aşağıdaki gibi Build ediyoruz.

Build sonrası projemizin dll’i oluştu. Sağ tarafta Solution Explorer’da SQLServerCLRUygulamasi üzerine tıkladığınızda alt tarafta açılan Properties sekmesinde ProjectFolder’daki path’e gidip bin->Debug’ın altındaki dll’i kopyalıyoruz ve istediğimiz bir yere yapıştırıyoruz. Ben C:\Backup klasörüne kopyaladım.

Daha sonra aşağıdaki script yardımıyla kodu sql server’a import ediyoruz.

CREATE ASSEMBLY BosDiskAlani from 'C:\Backup\SqlServerCLRUygulamasi.dll' WITH PERMISSION_SET = UNSAFE;

Eğer veritabanında TRUSTWORTHY aktif değilse yukardaki kod aşağıdaki gibi hata alacaktır.

Msg 10327, Level 14, State 1, Line 9

CREATE ASSEMBLY for assembly ‘SqlServerCLRUygulamasi’ failed because assembly ‘SqlServerCLRUygulamasi’ is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Veritabanında TRUSTWORTHY özelliğini aktif etmek için aşağıdaki script’i kullanabilirsiniz. TRUSTWORTH özelliğini aktif hale getirmek beraberinde bazı riskler getirir. “Login’lere View_Server_State Yetkisi Vermeden dmw ve dmf’lere Kısıtlı Bir Şekilde Eriştirmek” isimli makalemde bu riskleri anlattım.

ALTER DATABASE AdventureWork2014 SET TRUSTWORTHY ON

Yukardaki gibi veritabanında TRUSTWORTHY özelliğini aktif ettikten sonra ASSEMBLY create script’ini tekrar UNSAFE mode’da çalıştırıyorum ve ardından aşağıdaki gibi bu dll’i kullanacak stored procedure’ü oluşturuyorum.

CREATE PROCEDURE BosDiskAlani_SP
@diskismi nvarchar(200),
@mesaj nvarchar(MAX)OUTPUT
AS EXTERNAL NAME BosDiskAlani.[SqlServerCLRUygulamasi.Class1].GetTotalFreeSpace

Stored Procedure’ü aşağıdaki gibi çalıştırdığımda bana C diskim’de kalan boş alanı aşağıdaki gibi gösteriyor.

DECLARE @mesaj varchar(MAX)
EXEC BosDiskAlani_SP 'C:\',@mesaj output
PRINT @mesaj

ASSEMBLY kodunu SAFE olarak set ederseniz stored procedure’ü çalıştırdığınızda aşağıdaki gibi bir hata alabilirsiniz.

Msg 6522, Level 16, State 1, Procedure BosDiskAlani_SP, Line 22

A .NET Framework error occurred during execution of user-defined routine or aggregate “BosDiskAlani_SP”:

System.Security.SecurityException: ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ türündeki izin isteği başarısız oldu.

System.Security.SecurityException:

konum: System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

konum: System.Security.CodeAccessPermission.Demand()

konum: System.IO.Directory.GetLogicalDrives()

konum: System.IO.DriveInfo.GetDrives()

konum: SqlServerCLRUygulamasi.Class1.GetTotalFreeSpace(String driveName)

Visual Studio 2015 üzerinde projeyi ve class’ı oluşturmak biraz daha farklı. Öncelikle projeyi SQL Server Database Project olarak aşağıdaki gibi oluşturuyoruz.