SqlQuery

Sağladığı Fayda ve Kolaylıklar

Öncelikle bu nesneyi kullanmanın bize sağlayacağı bazı avantajları sıralayalım:

Basit Bir SELECT Sorgusu

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SimpleSelectFromOrderBy()
  9.         {
  10.             var query = new SqlQuery();
  11.             query.Select("Firstname");
  12.             query.Select("Surname");
  13.             query.From("People");
  14.             query.OrderBy("Age");
  16.             return query.ToString();
  17.         }
  18.     }
  19. }

Programı çalıştırdığımızda fonksiyon aşağıdaki gibi bir sonuç verir:

  1. SELECT Firstname, Surname FROM People ORDER BY Age

İlk satırda SqlQuery nesnemizi yegane parametresiz constructor’ı ile oluşturduk. Eğer bu noktada ToString() metodunu çağırsaydık aşağıdaki gibi bir sonuç alacaktık:

  1. SELECT FROM

SqlQuery sorgunuza herhangi bir doğrulama yapmaz. Sadece çağrılarınızla ürettiğiniz sorguyu metne çevirir. SELECT ve FROM deyimleri siz hiçbir alan seçmeseniz de, tablo adı belirtmeseniz de metinsel gösterimde yukarıdaki gibi çıkacaktır. SqlQuery bu deyimleri içermeyen bir sorgu üretemez.

Ardından Select metodunu “Firstname” parametresiyle çağırdık. Sorgumuz aşağıdaki hale geldi:

  1. SELECT Firstname FROM

Yine Select metodunu, bu sefer “Surname” parametresiyle çağırınca, SqlQuery bir önceki seçilen alan ile “Surname” arasına virgül koyarak sorguyu aşağıdaki gibi üretti:

  1. SELECT Firstname, Surname FROM

From ve OrderBy metodlarını da çağırarak sorgumuza nihai halini verdik:

  1. SELECT Firstname, Surname FROM People ORDER BY Age

Metod Çağırım Sırası ve Etkisi

Örnek koddaki “From”, “OrderBy” ve “Select” içeren satırları hangi sırada yazarsak yazalım sonuç değişmeyecekti. Ancak aşağıdaki gibi Select çağrılarının sırası değişirse yani Surname alanını Firstname’den önce seçseydik…

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string ReorderedQuery()
  9.         {
  10.             var query = new SqlQuery();
  11.             query.OrderBy("Age");
  12.             query.Select("Surname");
  13.             query.From("People");
  14.             query.Select("Firstname");
  16.             return query.ToString();
  17.         }
  18.     }
  19. }

…sonuçta, sadece alanların SELECT ifadesinde görünme sıraları değişecekti:

  1. SELECT Surname, Firstname FROM People ORDER BY Age

Zincirleme Metod Çağrımı (Method Chaining)

Yukarıdaki kod listelerine dikkatli bakınca sorgumuzu düzenlediğimiz her satıra “query.” ile başladığımızı ve bunun göze pek de hoş gelmediğini farkedebilirsiniz.

SqlQuery’nin, method chaining (zincirleme metod çağrımı) özelliğinden faydalanarak, bu sorguyu daha okunaklı ve kolay bir şekilde yazabiliriz:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string MethodChaining()
  9.         {
  10.             var query = new SqlQuery()
  11.                 .Select("Firstname")
  12.                 .Select("Surname")
  13.                 .From("People")
  14.                 .OrderBy("Age");
  16.             return query.ToString();
  17.         }
  18.     }
  19. }

jQuery yada LINQ kullanıyorsanız bu zincirleme çağırım şekline aşina olmalısınız.

İstersek “query” yerel değişkenininden de kurtulabiliriz:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SimpleSelectRemoveVariable()
  9.         {
  10.             return new SqlQuery()
  11.                 .Select("Firstname")
  12.                 .Select("Surname")
  13.                 .From("People")
  14.                 .OrderBy("Age")
  15.                 .ToString();
  16.         }
  17.     }
  18. }

Select Metodu

  1. public SqlQuery Select(string expression)

Şu ana kadar verdiğimiz örneklerde, Select metodunun yukarıdaki overload’ını kullandık. Expression (ifade) parametresi, bir alan adı ya da (Adi + Soyadi) gibi bir SQL ifadesi olabilir. Bu metodu her çağırdığınızda sorgunun SELECT listesine, verdiğiniz alan adı ya da ifade eklenir (araya virgül konarak).

Tek bir çağırımda, birden fazla alan seçmek isterseniz, şu overload’ı kullanabilirsiniz:

  1. public SqlQuery Select(params string[] expressions)

Örneğin:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SelectMultipleFieldsInOneCall()
  9.         {
  10.             return new SqlQuery()
  11.                 .Select("Firstname", "Surname", "Age", "Gender")
  12.                 .From("People")
  13.                 .ToString();
  14.         }
  15.     }
  16. }
  1. SELECT Firstname, Surname, Age, Gender FROM People

Seçtiğiniz bir alana kısa ad (column alias) atamak isterseniz SelectAs metodundan faydalanabilirsiniz:

  1. public SqlQuery SelectAs(string expression, string alias)
  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SelectAs()
  9.         {
  10.             return new SqlQuery()
  11.                 .SelectAs("(Firstname + Surname)", "Fullname")
  12.                 .From("People")
  13.                 .ToString();
  14.         }
  15.     }
  16. }
  1. SELECT (Firstname + Surname) Fullname FROM People

From Metodu

  1. public SqlQuery From(string table)

SqlQuery’nin From metodu, sorgunun FROM ifadesini üretmek için en az (ve genellikle) bir kez çağrılmalıdır. İlk çağırdığınızda sorgunuzun ana tablo ismini belirlemiş olursunuz.

İkinci bir kez çağırırsanız, verdiğiniz tablo ismi, sorgunun FROM kısmına, asıl tablo adıyla aralarına virgül konarak eklenir. Bu durumda da CROSS JOIN yapmış olursunuz.

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string CrossJoinWithFrom()
  9.         {
  10.             return new SqlQuery()
  11.                 .Select("Firstname")
  12.                 .Select("Surname")
  13.                 .From("People")
  14.                 .From("City")
  15.                 .From("Country")
  16.                 .OrderBy("Age")
  17.                 .ToString();
  18.         }
  19.     }
  20. }

Oluşan sorgu aşağıdaki gibi olacaktır:

  1. SELECT Firstname, Surname FROM People, City, Country ORDER BY Age

Alias Nesnesi ve SqlQuery ile Kullanımı

Sorgularımız uzadıkça ve içindeki JOIN sayısı arttıkça, hem alan adı çakışmalarını engellemek, hem de istenen alanlara daha kolay ulaşmak için, kullandığımız tablolara aşağıdaki gibi kısa adlar (alias) vermeye başlarız.

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string FromWithStringAliases()
  9.         {
  10.             return new SqlQuery()
  11.                 .Select("p.Firstname")
  12.                 .Select("p.Surname")
  13.                 .Select("p.CityName")
  14.                 .Select("p.CountryName")
  15.                 .From("Person p")
  16.                 .From("City c")
  17.                 .From("Country o")
  18.                 .OrderBy("p.Age")
  19.                 .ToString();
  20.         }
  21.     }
  22. }
  1. SELECT p.Firstname, p.Surname, c.CityName, o.CountryName FROM People p, City c, Country o ORDER BY p.Age

Görüleceği üzere alanları seçerken de başlarına tablolarına atadığımız kısa adları (p.Surname gibi) getirdik. Bu sayede tablolardaki alan isimleri çakışsa da (aynı alan adı People, City, Country tablolarında olsa da) sorun çıkmasını engellemiş olduk.

Sorgu içinde kullandığımız bu kısa adları, dilersek SqlQuery ile birlikte kullanabileceğimiz Alias nesnesleri olarak ta tanımlayabiliriz.

  1. var p = new Alias("Person", "p");

Alias aslında bir string e verdiğiniz ad gibidir. Fakat string ten farklı olarak, SQL ifadelerinde kullanacabileceğimiz “kısa ad”.”alan adı” şeklinde metinleri, “+” operatörü aracılığıyla üretmemize yardımcı olur:

  1. p + "Surname"

p.Surname

Bu işlem C#’ın “+” operatörünün overload edilmesi sayesinde gerçekleşmektedir. Bir alias’ı bir alan adı ile topladığınızda, alias’ın kısa adı ve alan adı, aralarına “.” konarak birleştirilir

ne yazık ki C#’ın member access operatorünü (“.”) overload edemiyoruz, bu yüzden “+” kullanılmak durumunda.

Sorgumuzu Alias nesnesinden faydalanarak düzenleyelim:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string FromUsingAlias()
  9.         {
  10.             var p = new Alias("People", "p");
  11.             var c = new Alias("City", "c");
  12.             var o = new Alias("Country", "o");
  14.             return new SqlQuery()
  15.                 .Select(p + "Firstname")
  16.                 .Select(p + "Surname")
  17.                 .Select(c + "CityName")
  18.                 .Select(o + "CountryName")
  19.                 .From(p)
  20.                 .From(c)
  21.                 .From(o)
  22.                 .OrderBy(p + "Age")
  23.                 .ToString();
  24.         }
  25.     }
  26. }
  1. SELECT p.Firstname, p.Surname, c.CityName, o.CountryName FROM People p, City c, Country o ORDER BY p.Age

Görüldüğü gibi sonuç aynı, ancak yazdığımız kod biraz daha uzadı. Peki Alias nesnesi kullanmak burada bize ne kazandırdı?

Şu haliyle değerlendirildiğinde avantajı ilk bakışta görülmeyebilir. Ancak aşağıdaki koddaki gibi alan isimlerini tutan sabitlerimiz olsaydı…

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         const string Firstname = "Firstname";
  9.         const string Surname = "Surname";
  10.         const string Age = "Age";
  11.         const string CityName = "CityName";
  12.         const string CountryName = "CountryName";
  14.         public static string UsingFieldNameConsts()
  15.         {
  16.             var p = new Alias("People", "p");
  17.             var c = new Alias("City", "c");
  18.             var o = new Alias("Country", "o");
  20.             return new SqlQuery()
  21.                 .Select(p + Firstname)
  22.                 .Select(p + Surname)
  23.                 .Select(c + CityName)
  24.                 .Select(o + CountryName)
  25.                 .From(p)
  26.                 .From(c)
  27.                 .From(o)
  28.                 .OrderBy(p + Age)
  29.                 .ToString();
  30.         }
  31.     }
  32. }

…Visual Studio’nun IntelliSense özelliğinden daha çok faydalanmış ve birçok yazım hatasını derleme anında yakalamış olabilirdik.

Tabi örnekteki gibi, her sorguyu yazmadan önce, alan isimlerini sabit olarak tanımlamak çok ta mantıklı ve kolay değil. Dolayısıyla bunların merkezi bir yerde tanımlanmış olması lazım (hatta, direk entity tanımımızdan bulunması, ki buna daha sonra değineceğiz)

Tabloya özel alan adı tanımlarınızı, entity yapısı kullanmayacaksanız, aşağıdaki örnekteki gibi de yapabilirsiniz:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public class PeopleAlias : Alias
  9.         {
  10.             public PeopleAlias(string alias)
  11.                 : base(alias)
  12.             {
  13.             }
  15.             public string ID { get { return this["ID"]; } }
  16.             public string Firstname { get { return this["Firstname"]; } }
  17.             public string Surname { get { return this["Surname"]; } }
  18.             public string Age { get { return this["Age"]; } }
  19.         }
  21.         public class CityAlias : Alias
  22.         {
  23.             public CityAlias(string alias)
  24.                 : base(alias)
  25.             {
  26.             }
  28.             public string ID { get { return this["ID"]; } }
  29.             public string CountryID { get { return this["CountryID"]; } }
  30.             public string CityName { get { return this["CityName"]; } }
  31.         }
  33.         public class CountryAlias : Alias
  34.         {
  35.             public CountryAlias(string alias)
  36.                 : base(alias)
  37.             {
  38.             }
  40.             public string ID { get { return this["ID"]; } }
  41.             public string CountryName { get { return this["CountryName"]; } }
  42.         }
  44.         public static string UsingPredefinedClasses()
  45.         {
  46.             var p = new PeopleAlias("p");
  47.             var c = new CityAlias("c");
  48.             var o = new CountryAlias("o");
  50.             return new SqlQuery()
  51.                 .Select(p.Firstname)
  52.                 .Select(p.Surname)
  53.                 .Select(c.CityName)
  54.                 .Select(o.CountryName)
  55.                 .From(p)
  56.                 .From(c)
  57.                 .From(o)
  58.                 .OrderBy(p.Age)
  59.                 .ToString();
  60.         }
  61.     }
  62. }

Bu sayede alan adlarını tutan sınıflarımızı bir kez tanımladık ve tüm sorgularımızda kolaylıkla kullanabileceğiz.

Yukarıdaki örneklerde ayrıca SqlQuery.From’un aşağıdaki gibi Alias parametresi alan overload’ından faydalandık:

  1. public SqlQuery From(Alias alias)

Bu fonksiyon çağrıldığında, SQL sorgusunun FROM ifadesine, alias oluşturulurken tanımlanan tablo, kısa adıyla birlikte eklenir.

Eğer alias’ınızı oluştururken bir tablo adı belirtmediyseniz (new Alias(“c”) gibi) şu overload’ı kullanabilirsiniz:

  1. public SqlQuery From(string table, Alias alias)

With Uzantı (Extension) Metodu

Örneğimizde, kısa adlarımızı (alias) önceden değişken olarak tanımlamamız gerekli oldu. Eğer bunu SQL query’nin zincirleme akışını bozmadan yapmak isteseydik, With extension metodundan faydalanabilirdik:

  1.     public static string UsingWithExtension()
  2.     {
  3.         return new SqlQuery().With(
  4.             new Alias("People", "p"),
  5.             new Alias("City", "c"),
  6.             new Alias("Country", "o"),
  7.             (me, p, c, o) => me
  8.                 .Select(p + Firstname)
  9.                 .Select(p + Surname)
  10.                 .Select(c + CityName)
  11.                 .Select(o + CountryName)
  12.                 .From(p)
  13.                 .From(c)
  14.                 .From(o)
  15.                 .OrderBy(p + Age))
  16.         .ToString();
  17.     }

ChainingExtensions altında statik bir uzantı (extension) metodu olarak tanımlanmış olan With metodu, sorgunuzun akışını bozmadan, inline olarak değişkenler tanımlamanıza imkan verir. With metoduna geçirdiğiniz parametreler, son parametre verdiğiniz lambda metoduna, sırasıyla geçirilir. Metoda ilk geçirilen parametre (me) ise sorgunun kendisidir.

Inline değişken tanımlamak, sorgumuzu daha akışkan hale getirse de, bir miktar okunurluluğu azaltmış olabilir. Fonksiyonalite ve akıcılık arasında ne tercih yapacağına siz karar vermelisiniz.

OrderBy Metodu

  1. public SqlQuery OrderBy(string expression, bool desc = false)

OrderBy metodu da Select gibi bir alan adı ya da ifadesiyle çağrılabilir. “Desc” opsiyonel parametresine true atarsanız, alan adı ya da ifadenizin sonuna “ DESC” getirilir.

OrderBy metodu, verdiğiniz ifadeleri ORDER BY deyiminin sonuna ekler. Bazen alanı listenin başına getirmek te isteyebiliriz. Örneğin çeşitli alanlara göre sıralanmış bir sorgu hazırladıktan sonra, kullanıcı arayüzünde tıklanan kolona göre sıralamanın değişmesi (önceki sıralamayı tümüyle kaybetmeden) gerekebilir. Bunun için SqlQuery, OrderByFirst metodunu sağlar:

  1. public SqlQuery OrderByFirst(string expression, bool desc = false)
  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string OrderByFirst()
  9.         {
  10.             var query = new SqlQuery()
  11.                 .Select("Firstname")
  12.                 .Select("Surname")
  13.                 .From("Person")
  14.                 .OrderBy("PersonID");
  16.             return query.OrderByFirst("Age")
  17.                 .ToString();
  18.         }
  19.     }
  20. }
  1. SELECT Firstname, Surname FROM Person ORDER BY Age, PersonID

Order by kullanmış olsaydık şunu elde edecektik:

  1. SELECT Firstname, Surname FROM Person ORDER BY PersonID, Age

Distinct Metodu

  1. public SqlQuery Distinct(bool distinct)

DISTINCT deyimini içeren bir sorgu üretmek istediğinizde bu metodu kullanabilirsiniz:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string DistinctMethod()
  9.         {
  10.             return new SqlQuery()
  11.                 .From("Person")
  12.                 .Distinct(true)
  13.                 .Select("Firstname")
  14.                 .ToString();
  15.         }
  16.     }
  17. }
  1. SELECT DISTINCT Firstname FROM Person

Group By Metodu

  1. public SqlQuery GroupBy(string expression)

GroupBy metodu bir alan adı ya da ifadesiyle çağrılır ve bu ifadeyi sorgunun GROUP BY deyiminin sonuna ekler.

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string GroupByMethod()
  9.         {
  10.             new SqlQuery()
  11.                 .From("Person")
  12.                 .Select("Firstname", "Lastname", "Count(*)")
  13.                 .GroupBy("Firstname")
  14.                 .GroupBy("LastName")
  15.                 .ToString();
  16.         }
  17.     }
  18. }
  1. SELECT Firstname, Lastname, Count(*) FROM Person GROUP BY Firstname, LastName

Having Metodu

  1. public SqlQuery Having(string expression)

GroupBy metodu ile birlikte kullanabileceğiniz Having metodu, mantıksal bir ifadeyle çağrılır ve bu ifadeyi sorgunun HAVING deyiminin sonuna ekler.

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string HavingMethod()
  9.         {
  10.             new SqlQuery()
  11.                 .From("Person")
  12.                 .Select("Firstname", "Lastname", "Count(*)")
  13.                 .GroupBy("Firstname")
  14.                 .GroupBy("LastName")
  15.                 .Having("Count(*) > 5")
  16.                 .ToString();
  17.         }
  18.     }
  19. }
  1. SELECT Firstname, Lastname, Count(*) FROM Person GROUP BY Firstname, LastName HAVING Count(*) > 5

Sayfalama İşlemleri (SKIP / TAKE / TOP / LIMIT)

  1. public SqlQuery Skip(int skipRows)
  3. public SqlQuery Take(int rowCount)

SqlQuery, LINQ’de Take ve Skip olarak geçen sayfalama metodlarını destekler. Bunlardan Take, Microsoft SQL Server’da TOP’a karşılık gelirken, SKIP’in direk bir karşılığı olmadığından SqlQuery, ROW_NUMBER() fonksiyonundan faydalanır. Bu nedenle SQL server için SKIP kullanmak istediğinizde, sorgunuzda en az bir ORDER BY ifadesinin de olması gerekir.

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SkipTakePaging()
  9.         {
  10.             new SqlQuery()
  11.                 .From("Person")
  12.                 .Select("Firstname", "Lastname")
  13.                 .OrderBy("PersonId")
  14.                 .Skip(300)
  15.                 .Take(100)
  16.                 .ToString();
  17.         }
  18.     }
  19. }
  1. SELECT * FROM (
  2.     SELECT TOP 400 Firstname, Lastname, ROW_NUMBER() OVER (ORDER BY PersonId) AS _row_number_ FROM Person
  3. ) _row_number_results_ WHERE _row_number_ > 300

Farklı Veritabanları Desteği

Sayfalama işlemlerindeki örneğimizde, SqlQuery, Microsoft SQL Server 2008’e uygun bir sayfalama metodu kullandı.

Dialect metodu ile SQL query nin kullandığı DIALECT ya da sunucu tipini değiştirebiliriz:

  1. public SqlQuery Dialect(SqlDialect dialect)

Şu an için aşağıdaki sunucu tipleri desteklenmektedir:

  1. [Flags]
  2. public enum SqlDialect
  3. {
  4.     MsSql = 1,
  5.     Firebird = 2,
  6.     UseSkipKeyword = 512,
  7.     UseRowNumber = 1024,
  8.     UseOffsetFetch = 2048,
  9.     MsSql2005 = MsSql | UseRowNumber,
  10.     MsSql2012 = MsSql | UseOffsetFetch | UseRowNumber
  11. }

Örneğin SqlDialect.MsSql2012’yi seçip, SQL Server 2012 ile gelen OFFSET FETCH deyimlerinden faydalanmak isteseydik:

  1. namespace Samples
  2. {
  3.     using Serenity;
  4.     using Serenity.Data;
  6.     public partial class SqlQuerySamples
  7.     {
  8.         public static string SkipTakePaging()
  9.         {
  10.             new SqlQuery()
  11.                 .Dialect(SqlDialect.MsSql2012)
  12.                 .From("Person")
  13.                 .Select("Firstname", "Lastname")
  14.                 .OrderBy("PersonId")
  15.                 .Skip(300)
  16.                 .Take(100)
  17.                 .ToString();
  18.         }
  19.     }
  20. }
  1. SELECT Firstname, Lastname FROM Person ORDER BY PersonId
  2. OFFSET 300 ROWS FETCH NEXT 100 ROWS ONLY

SqlDialect.Firebird’ü tercih etseydik şu şekilde bir sorgu oluşurdu:

  1. SELECT FIRST 100 SKIP 300 Firstname, Lastname FROM Person ORDER BY PersonId

SqlQuery’nin Sunucu/Dialect desteği henüz mükemmel olmasa da, temel işlemlerde sorun çıkarmayacak düzeydedir.

Uygulamanızda tek tipte sunucu kullanıyorsanız, her sorgunuzda, dialect ayarlamak istemeyebilirsiniz. Bunun için SqlSettings.CurrentDialect’i değiştirmelisiniz. Örneğin aşağıdaki kodu program başlangıcında, ya da global.asax dosyanızdan çağırabilirsiniz:

  1. SqlSettings.CurrentDialect = SqlDialect.MsSql2012;