Daha onceki yazimda scalar deger donduren bir fonksiyonu nasil MSSQL Server'a tanittigimizi anlatmaya calismistim. Bu yazimda ise bir T-SQL'deki table-valued fonksiyonu CLR ile nasil tanimlayabilecegimizden bahsedecegim. Table-valued fonksiyonlar bildiginiz gibi bir data listesi seklinde (table) donus degeri olan fonksiyonlardir. Bunun Microsoft CLR karsiligi tahmin edebileceginiz gibi IEnumarable tipindedir. Fazla uzatmadan kod uzerinden anlatmaya calisacagim.
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;
namespace ArticleCodes
{
public class CLRTableValuedFunctions
{
private class CustomerResultMember
{
public SqlString Name { get; private set; }
public SqlString Country { get; private set; }
public CustomerResultMember(SqlString name, SqlString country)
{
Name = name;
Country = country;
}
}
[SqlFunction(FillRowMethodName = "FillRow",
DataAccess = DataAccessKind.Read)]
public static IEnumerable GetCompaniesSince(SqlDateTime creationDateStart)
{
ArrayList resultCollection = new ArrayList();
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT Name, Country FROM Companies WHERE CreationDate >= @creationDateStart ", conn);
SqlParameter creationDateParam = cmd.Parameters.Add(
"@creationDateStart",
SqlDbType.DateTime);
creationDateParam.Value = creationDateStart;
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
resultCollection.Add(new CustomerResultMember(sdr.GetSqlString(0), sdr.GetSqlString(1)));
}
}
return resultCollection;
}
}
public static void FillRow(object CustomerResultMemberObj, out SqlString customerName, out SqlString customerCountry)
{
CustomerResultMember crm = (CustomerResultMember)CustomerResultMemberObj;
customerName = crm.Name;
customerCountry = crm.Country;
}
}
}
Oncelik olarak asil isi yapan metodumuza bakalim.
public static IEnumerable GetCompaniesSince(SqlDateTime creationDateStart)
Bu metod gordugunuz gibi IEnumerable donduren ve ayni scalar fonksiyon tanimindaki gibi (bkz.
onceki makale) statik bir metod. Kisaca ArrayList icerisine Select metodu ile belirlenmis ve metod parametresiyle sinirlanmis verileri ekleyip, bu ArrayList'i donduruyor. CLR tanimlamak icin daha once de belirttigim gibi belirli standartlar var. Scalar fonksiyon taniminda iki adet standart tanim yapmaniz gerekiyor, bunlar 1 adet init metod ve bu metodun fillrow metodudur. Init metodu, yani bizim ornegimizde GetCompaniesSince datayi getiren ve isi yapan metoddur. FillRow ise IEnumarable'dan anlamaya SQL server'a bu datayi nasil isleyebilecegini gosteren metoddur. Cunku artik olusacak fonksiyon diger T-SQL query'leri icinde de bir tabloymuscasina kullanabilir. Sonucta konumuz tablo donen fonksiyonlar
.
Dikkat edilmesi gereken husus, sadece init metodunuzun icerisinde bir sql connection yapabilirsiniz ancak FillRow metodunda bunu yapamazsiniz. Init metodunda da bunu yapabilmek icin SqlFunction attributenun DataAccess parametresi DataAccessKind.Read seklinde set edilmesi gerekmektedir.
Son olarak bu yeni CLR metodumuzu SQL server'a nasil tanitacagimiz konusu kaldi. Daha onceki yazimda bu tanimlamada yapilan isin detaylari bulabilirsiniz. Burada tekrar etmeden sadece kodu veriyorum.
CREATE ASSEMBLY ClrCode FROM '<DLL_Path>'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO
CREATE FUNCTION GetCompaniesSince(@CreationDate datetime)
RETURNS TABLE (
Name nvarchar(50),
Country nvarchar(50)
)
AS EXTERNAL NAME ClrCode.[ArticleCodes.CLRTableValuedFunctions].GetCompaniesSince
Herkese iyi calismalar.