Fluent SQL

  

Serenity, SELECT, INSERT, UPDATE ve DELETE ifadeleri için bir dizi sorgu oluşturucu içerir.

Bu oluşturucular basit dizelerle veya Serenity varlık (satır) sistemiyle kullanılabilir.

Çıktıları, Dapper (Serenity ile entegre olan) veya Serenity uzantıları gibi bir mikro-ORM aracılığıyla doğrudan yürütülebilir.

SQL Sorgusu

SqlQuery, akıcı bir arayüz aracılığıyla dinamik SQL SELECT sorguları oluşturmaya yönelik bir nesnedir.

SqlQuery, elle hazırlanmış SQL'e göre bazı avantajlar sunar:

• SQL oluştururken Visual Studio'nun intelli-sense özelliğini kullanma

• Minimum ek yük ile akıcı arayüz

• Sorgular yürütme zamanında değil derleme zamanında kontrol edildiğinden sözdizimi hataları azaltıldı.

• Select, Where ve Order By gibi ifadeler herhangi bir sırada kullanılabilir. Sorguyu bir dizeye dönüştürürken doğru konumlara yerleştirilirler. Benzer şekilde, bu tür cümleler birden fazla kullanılabilir ve dize dönüşümü sırasında birleştirilirler. Böylece giriş parametrelerine bağlı olarak koşullu olarak SQL oluşturabilirsiniz.

• Parametreler ve parametre adlarıyla uğraşmanıza gerek yok. Kullanılan tüm değerler otomatik olarak adlandırılmış parametrelere dönüştürülür (dize birleştirme kullanmadığınız veya çıkış karaktersiz kullanıcı girişi iletmediğiniz sürece). Gerekirse manüel olarak adlandırılmış parametreleri de kullanabilirsiniz.

• Yerel olarak desteklemeyen sunucu türlerinde (örn. SQL Server 2000) sayfalama gerçekleştirmek için özel bir sorgu oluşturabilir.

• Diyalekt sistemi ile sorgu belirli sunucu türleri ve versiyonlarına hedeflenebilir.

• Serenity varlıklarıyla birlikte kullanılırsa (Dapper gibi mikro ORM'lerle de kullanılabilir), sıfır yansımayla bir veri okuyucudan sorgu sonuçlarının yüklenmesine yardımcı olur. Ayrıca sol/sağ birleştirmeleri otomatik olarak yapar.

ÖNEMLİ UYARI!

Kullanıcı tarafından sağlanan bir dizeyi asla SQLQuery yöntemlerinden birine aktarmayın. Kodunuzu SQL-Injection saldırılarına açacağından ÇOK TEHLİKELİDİR! Bu, kullanıcı tarafından sağlanan dizenin bir dize birleştirme işleminde kullanılmasını ve iletilmesini içerir.

Burada, user-provided stringJSON, XML, sorgu dizesi, form, istek gövdesi vb. aracılığıyla API/eylemler/hizmetlerinizden birine gönderilen parametrelere ek olarak istemci tarafı forma girilen her şey bulunur.

https://en.wikipedia.org/wiki/SQL_injection

Basit Bir Seçim Sorgusu

var query = new SqlQuery();
query.Select("Firstname");
query.Select("Surname");
query.From("People");
query.OrderBy("Age");

Console.WriteLine(query.ToString());

Bu çıktıyla sonuçlanacaktır:

SELECT 
Firstname,
Surname 
FROM People 
ORDER BY Age

Programımızın ilk satırında tek parametresiz kurucusuyla SqlQuery'yi çağırdık. Bu noktada çağrılsaydı ToString()çıktı şöyle olurdu:

SELECT FROM

SqlQuery herhangi bir sözdizimi doğrulaması gerçekleştirmez. Yalnızca kendi oluşturduğunuz sorguyu yöntemlerini çağırarak dönüştürür. Herhangi bir alan seçmeseniz veya yöntemlerden çağrı yapmasanız bile, bu temel SELECT FROM ifadesini üretecektir .

SqlQuery boş sorgular oluşturamaz.

SelectDaha sonra metodu string parametresiyle çağırdık "FirstName". Sorgumuz artık şu şekilde:

SELECT Firstname FROM

İfade yürütüldüğünde , SqlQuery önceden seçilen alan ( ) ile bunun Select("Surname")arasına virgül koyar :Firstname

SELECT Firstname, Surname FROM

Çalıştırma Fromve OrderByyöntemlerden sonra son çıktımız:

SELECT Firstname, Surname FROM People ORDER BY Age

Yöntem Çağrı Sırası ve Etkileri

FromÖnceki örnekte, , OrderByve satırlarını yeniden sıralasak bile çıktı değişmeyecekti Select. Ancak ifadelerin sırasını değiştirseydik değişirdi Select...

var query = new SqlQuery();
query.From("People");
query.OrderBy("Age");
query.Select("Surname");
query.Select("Firstname");

Console.WriteLine(query.ToString());

...ancak yalnızca SELECT ifadesinin içindeki sütun sıralaması değişir:

SELECT 
Surname,
Firstname 
FROM People 
ORDER BY Age

Select, From, OrderBy ve GroupBy gibi yöntemleri herhangi bir sırayla kullanabilir ve bunları karıştırabilirsiniz (örn. Select'i çağırın, ardından OrderBy'yi arayın, tekrar Select...)

Özellikle Serenity varlıklarıyla birlikte kullanıldığında FROM'un başlangıca konulması önerilir çünkü otomatik birleştirmelere ve veritabanı lehçesinin belirlenmesine vs. yardımcı olur.

Yöntem Zincirleme

Biraz ayrıntılı ve her satıra başlamak o kadar da okunaklı değil "query.". Hemen hemen tüm SqlQueryyöntemler zincirlenebilir ve sonuç olarak sorgunun kendisini döndürür.

Sorguyu şu şekilde yeniden yazabiliriz:

var query = new SqlQuery()
    .From("People")
    .Select("Firstname")
    .Select("Surname")
    .OrderBy("Age");
Console.WriteLine(query.ToString());
}

Bu özellik, jQuery ve LINQ numaralandırılabilir yöntem zincirlemeye benzer.

Sorgu değişkeninden bile kurtulabiliriz:

Console.WriteLine(
    new SqlQuery()
        .From("People")
        .Select("Firstname")
        .Select("Surname")
        .OrderBy("Age")
        .ToString());

Okunabilirlik ve tutarlılık nedenleriyle her yöntemin kendi satırına yerleştirilmesi ve düzgün şekilde girintilenmesi önemle tavsiye edilir.

Yöntem Seç

public SqlQuery Select(string expression)

Şu ana kadar elimizdeki örneklerde Selectyukarıda gösterilen yöntemin aşırı yüklemesini kullandık (yaklaşık 11 aşırı yüklemeye sahiptir).

Parametre expressionbasit bir alan adı veya şöyle bir ifade olabilir:"FirstName + ' ' + LastName"

Bu yöntem her çağrıldığında, ayarladığınız ifade, ortaya çıkan sorgunun SELECT ifadesine, arada virgül olacak şekilde eklenir.

Tek çağrıda birden fazla alan seçmek için SelectMany yöntemi de vardır:

public SqlQuery SelectMany(params string[] expressions)

Örneğin:

var query = new SqlQuery()
    .From("People")
    .SelectMany("Firstname", "Surname", "Age", "Gender")
    .ToString();

Console.WriteLine(query.ToString());

SELECT 
Firstname,
Surname,
Age,
Gender 
FROM People

Kişisel olarak Select yöntemini birden çok kez çağırmayı tercih ederim.

Çoklu seçimin neden sadece başka bir aşırı yükleme olmadığını merak ediyor olabilirsiniz Select. Bunun nedeni, Selectyöntemin takma ad içeren bir sütunu seçmek için daha yaygın olarak kullanılan bir aşırı yüklemeye sahip olmasıdır:

public SqlQuery Select(string expression, string alias)

var query = new SqlQuery()
    .Select("(Firstname + ' ' + Surname)", "Fullname")
    .From("People")
    .ToString();

Console.WriteLine(query.ToString());

SELECT 
(Firstname + ' ' + Surname) AS [Fullname] 
FROM People

Yöntemden

public SqlQuery From(string table)

SqlQuery.From yöntemi en az bir kez (ve genellikle bir kez) çağrılmalıdır.

..ve önce aranmanız tavsiye edilir.

İkinci kez çağırdığınızda FROM deyimine tablo adı arasına virgül konularak eklenecektir. Böylece, bir CROSS JOIN olacaktır:

var query = new SqlQuery()
    .From("People")
    .From("City")
    .From("Country")
    .Select("Firstname")
    .Select("Surname")
    .OrderBy("Age");

Console.WriteLine(query.ToString());

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

Takma Ad Nesnesini SqlQuery ile Kullanmak

Başvurulan tabloların sayısı arttığında ve sorgularımız uzadığında tablo takma adlarının kullanılması yaygındır:

var query = new SqlQuery()
    .From("Person p")
    .From("City c")
    .From("Country o")
    .Select("p.Firstname")
    .Select("p.Surname")
    .Select("c.Name", "CityName")
    .Select("o.Name", "CountryName")
    .OrderBy("p.Age")
    .ToString();

Console.WriteLine(query.ToString());
}

SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

pHer ne kadar bu şekilde çalışsa da , c, ve ' yi nesneler oolarak tanımlamak daha iyidir Alias:

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

Nesne Alias, bir tabloya atanan kısa ad gibidir. Aşağıdaki gibi SQL üye erişim ifadeleri oluşturmak için bir indeksleyici ve operatör aşırı yüküne sahiptir p.Surname:

var p = new Alias("Person", "p");
Console.WriteLine(p + "Surname"); // + operator overload
Console.WriteLine(p["Firstname"]); // through indexer

p.Surname
p.Firstname

Maalesef C#üye erişim operatörü (.) geçersiz kılınamadığından (+) kullanmak zorunda kaldık. Dinamik ile geçici bir çözüm mümkün olabilir, ancak performansı kötü olacaktır.

Nesneleri kullanarak sorgumuzu değiştirelim Alias:

var p = new Alias("Person", "p");
var c = new Alias("City", "c");
var o = new Alias("Country", "o");

var query = new SqlQuery()
    .From(p)
    .From(c)
    .From(o)
    .Select(p + "Firstname")
    .Select(p + "Surname")
    .Select(c + "Name", "CityName")
    .Select(o + "Name", "CountryName")
    .OrderBy(p + "Age")
    .ToString();

Console.WriteLine(query.ToString());

SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

Yukarıda görüldüğü gibi sonuç aynı ancak yazdığımız kod biraz daha uzun. Peki takma ad kullanmanın avantajı nedir?

Alan adlarına sahip sabitlerin bir listesi olsaydı…

const string Firstname = "Firstname";
const string Surname = "Surname";
const string Name = "Name";
const string Age = "Age";

var p = new Alias("Person", "p");
var c = new Alias("City", "c");
var o = new Alias("Country", "o");
var query = new SqlQuery()
    .From(p)
    .From(c)
    .From(o)
    .Select(p + Firstname)
    .Select(p + Surname)
    .Select(c + Name, "CityName")
    .Select(o + Name, "CountryName")
    .OrderBy(p + Age)
    .ToString();

Console.WriteLine(query.ToString());

Intelli-sense özelliğinden yararlanacağız ve birkaç derleme zamanı kontrolü daha yapacağız.

Görüldüğü gibi her sorgu için alan adı tanımlamak mantıklı ve kolay değildir. Bu merkezi bir konumda veya varlık bildirimlerimizde olmalıdır.

Alias'ı kullanarak fakir bir adamın basit ORM'sini oluşturalım:

public class PeopleAlias : Alias
{
    public PeopleAlias(string alias) 
        : base("People", alias) { }

    public string ID { get { return this["ID"]; } }
    public string Firstname { get { return this["Firstname"]; } }
    public string Surname { get { return this["Surname"]; } }
    public string Age { get { return this["Age"]; } }
}

public class CityAlias : Alias
{
  public CityAlias(string alias)
      : base("City", alias) { }

  public string ID { get { return this["ID"]; } }
  public string CountryID { get { return this["CountryID"]; } }
  public new string Name { get { return this["Name"]; } }
}

public class CountryAlias : Alias
{
  public CountryAlias(string alias)
      : base("Country", alias) { }

  public string ID { get { return this["ID"]; } }
  public new string Name { get { return this["Name"]; } }
}

void Main()
{
    var p = new PeopleAlias("p");
    var c = new CityAlias("c");
    var o = new CountryAlias("o");
    var query = new SqlQuery()
        .From(p)
        .From(c)
        .From(o)
        .Select(p.Firstname)
        .Select(p.Surname)
        .Select(c.Name, "CityName")
        .Select(o.Name, "CountryName")
        .OrderBy(p.Age)
        .ToString();
    
    Console.WriteLine(query.ToString());
}

Artık alan adlarına sahip bir dizi tablo takma adı sınıfımız var ve bunlar tüm sorgularda yeniden kullanılabilir.

Bu sadece takma adları açıklamaya yönelik bir örnektir. Bu tür dersler yazmanızı önermiyorum. Varlıklar çok daha fazlasını sunuyor.

Yukarıdaki örnekte parametre SqlQuery.Fromalan aşırı yüklemeyi kullandık Alias:

public SqlQuery From(Alias alias)

Bu yöntem çağrıldığında sorgunun FROM ifadesine tablo adı ve takma adı eklenir.

OrderBy Yöntemi

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

OrderBy ayrıca Select gibi bir alan adı veya ifadeyle de çağrılabilir.

descİsteğe bağlı bağımsız değişkeni doğru olarak atarsanız DESCanahtar sözcük, alan adına veya ifadeye eklenir.

Varsayılan olarak OrderBy, belirtilen ifadeleri ORDER BY ifadesinin sonuna ekler. Bazen başlamak için bir ifade/alan eklemek isteyebilirsiniz.

Örneğin, önceden tanımlanmış bir sıraya sahip bir sorgunuz olabilir, ancak kullanıcı bir ızgaradaki bir sütuna göre sıralama yaparsa, sütunun adı 0 dizinine eklenmelidir.

public SqlQuery OrderByFirst(string expression, bool desc = false)

var query = new SqlQuery()
    .Select("Firstname")
    .Select("Surname")
    .From("Person")
    .OrderBy("PersonID");
    
query.OrderByFirst("Age");
    
Console.WriteLine(query.ToString());
}

SELECT 
Firstname,
Surname 
FROM Person 
ORDER BY Age, PersonID

Farklı Yöntem

public SqlQuery Distinct(bool distinct)

SELECT ifadesinin önüne DISTINCT anahtar sözcüğünü eklemek için bu yöntemi kullanın.

var query = new SqlQuery()
    .Select("Firstname")
    .Select("Surname")
    .From("Person")
    .OrderBy("PersonID")
    .Distinct(true);
    
Console.WriteLine(query.ToString());

SELECT DISTINCT 
Firstname,
Surname 
FROM Person 
ORDER BY PersonID 

GroupBy Yöntemi

public SqlQuery GroupBy(string expression)

GroupByile benzer şekilde çalışır OrderByancak GroupByFirst değişkeni yoktur.

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName

Yöntem sahibi olmak

public SqlQuery Having(string expression)

Sahip olmak, GroupBy ile birlikte kullanılabilir (GroupBy'yi kontrol etmese de) ve ifadeyi HAVING ifadesinin sonuna ekler.

var query = new SqlQuery()
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .GroupBy("Firstname")
    .GroupBy("LastName")
    .Having("Count(*) > 5");
    
Console.WriteLine(query.ToString());

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName 
HAVING Count(*) > 5

Çağrı İşlemleri (SKIP / AL / TOP / LIMIT)

public SqlQuery Skip(int skipRows)

public SqlQuery Take(int rowCount)

SqlQuery, LINQ Take ve Skip'e benzer sayfalama yöntemlerine sahiptir.

Bunlar, veritabanı türüne bağlı olarak SQL anahtar sözcükleriyle eşlenir.

2012'den önceki SqlServer sürümlerinde SKIP eşdeğeri bulunmadığından, SKIP'i kullanmak için sorgunuzda ROW_NUMBER() kullanılacağından en az bir ORDER BY ifadesi bulunmalıdır. SqlServer 2012+ lehçesini kullanıyorsanız bu gerekli değildir.

var query = new SqlQuery()
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .OrderBy("PersonId")
    .Skip(100)
    .Take(50);
    
Console.WriteLine(query.ToString());

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

Bu örnekte varsayılan SQLServer2012 lehçesini kullanıyoruz.

Veritabanı Lehçe Desteği

Sayfalama örneğimizde SqlQuery, SQL Server 2012 ile uyumlu bir sözdizimi kullandı.

Dialect metodu ile SqlQuery'nin hedeflediği sunucu tipini değiştirebiliriz:

public SqlQuery Dialect(ISqlDialect dialect)

Yazım itibarıyla desteklenen lehçe türlerinin listesi aşağıdadır:

• Firebird Lehçesi
• Postgres Lehçesi
• SqliteDialect
• SqlServer2000Dialect
• SqlServer2005Dialect
• SqlServer2012Dialect

Sorgumuzu SQL Server 2005'e hedeflemek istersek:

var query = new SqlQuery()
    .Dialect(SqlServer2005Dialect.Instance)
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .OrderBy("PersonId")
    .Skip(100)
    .Take(50);
    
Console.WriteLine(query.ToString());

SELECT * FROM (
SELECT TOP 150 
Firstname,
Lastname,
Count(*), ROW_NUMBER() OVER (ORDER BY PersonId) AS __num__ 
FROM Person) __results__ WHERE __num__ > 100

SqliteDialect.Instance ile çıktı şöyle olacaktır:

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId LIMIT 50 OFFSET 100

Uygulamanızda yalnızca tek bir tür veritabanı sunucusu kullanıyorsanız, varsayılan lehçeyi ayarlayarak her sorgu başlattığınızda bir lehçe seçmek zorunda kalmayı önleyebilirsiniz:

SqlSettings.DefaultDialect = SqliteDialect.Instance;

Bir bağlantı dizesinin lehçesi ProviderName, bağlantı dizesinin alanından veya Dialectözelliğinden türetilir appsettings.json. SqlSettings.DefaultDialect yalnızca bir geri dönüştür.