Fırat Esmer

ANLATILAN SENİN HİKAYENDİR - KARL MARX

ADO.NET Bağlantı Havuzu

Herhangi bir veritabanı ile ilgili bir işlem için açılan bağlantı, sunucu tarafında oturum açılmasına sebep olur. Açılan her bağlantı yeni bir istek, her istek yük anlamına gelir. Birazdan okuyacağınız makalede tüm isteklerin bir bağlantıdan gönderilmesi (connection pooling - bağlantı havuzu) ile her isteğin ayrı ayrı bağlantılar üzerinden gönderilmesi arasındaki farkı ve avantajları göreceksiniz.

Senaryo : Veritabanına, içerisinde pooling özelliği true ve false olan 2 farklı bağlantı açacağım. Bize ne kadarlık süreye mal olacağını ve SQL Server Profiler'da nasıl görüneceğini göstereceğim.

Bilinmesi Gerekenler :

  • Makaledeki örnek .NET Framework 4.0 (Windows Form) ve Visual Studio 2010 ile hazırlandı,
  • Makalede Northwind veritabanı üzerinden bağlantı açacağım. Eğer Northwind veritabanına sahip değilseniz hata verecektir. İndirmek için buraya tıklayın. Northwind veritabanı kullanmamın sebebi çoğu yazılımcının mevcut olması. Projenin boyutunu büyütmemek adına veritabanını projeme eklemedim.

Neler Öğreneceksiniz :

  • Tanımlanan bağlantı üzerindeki pooling (havuz) mantığını kavrayacaksınız,
  • Min Pool Size ve Max Pool Size kavramlarını öğreneceksiniz.

Önceklikle şunu belirtmeliyim ki eğer tanımladığınız bağlantı cümlesine "pooling" özelliğini eklemezseniz, varsayılan olarak true olacaktır. Yani oluşturulmuş bağlantı havuzu üzerinden işlemlerinizi gerçekleştirecekseniz bir şey yapmanıza gerek yok. Ancak her istek için ayrı bir bağlantı tanımlamak istiyorsanız aşağıdaki gibi bağlantı tanımlayabilirsiniz.

SqlConnection connection = new SqlConnection("server=.; database=northwind; integrated security=sspi; pooling=false");

İlk olarak veritabanı ile bağlan kuralım ve SQL Server Profiler'dan veritabanında neler olduğuna bakalım.

// 1 numaralı bağlantım.
using (SqlConnection connection = new SqlConnection("server=.; database=northwind; integrated security=sspi;"))
{
  connection.Open();
}

// 2 numaralı bağlantım.
using (SqlConnection connection = new SqlConnection("server=.; database=ReportServer; integrated security=sspi;"))
{
  connection.Open();
}

// 1 numaralı bağlantım ile aynı olduğu için 3. bağlantıyı açmayıp, 1 numaralı bağlantı üzerinden işlemi gerçekleştirecek.
using (SqlConnection connection = new SqlConnection("server=.; database=northwind; integrated security=sspi;"))
{
  connection.Open();
}

Ve sonuç aşağıdaki gibi olacaktır. 

SQL Server Profiler

Gördüğünüz gibi tek bir havuzdan yönetilen bağlantılar sonucu, aynı bağlantılari çin tekrar tekrar oturum açılmadı. Gelin şimdi her istek için ayrı bir bağlantı açalım ve bu işlemin ne kadar süreceğine bakalım.

SqlConnection connection;

DateTime start = DateTime.Now;

// 1000 kere bağlantıyı açıp kapatacağız.
for (int i = 0; i < 1000; i++)
{
  connection = new SqlConnection("server=.; database=northwind; integrated security=sspi; pooling=false");

  connection.Open();
  connection.Close();
  connection.Dispose();
}

DateTime end = DateTime.Now;

// Geçen zamanın sonucunu alacağız.
TimeSpan result = end - start;

MessageBox.Show(String.Format("Açılan bağlantıların işlem süresi : {0}", result.ToString()));

Yukarıdaki işlemin sonucu tam olarak 00:00:07.3951136 saniye sürdü. Anlattığım kadarıyla "pooling=false" dediğimiz için bin ayrı oturum açılmış olması lazım. 

SQL Server Profiler

Evet liste böylece akıp gidiyor aşağıya doğru. Şimdi... Şimdi "pooling=true" yapacağız. Yani tek bir bağlantıyı açık tutacağız ve işlemleri bellekteki bağlantıdan yürüteceğiz. Gelin bunun ne kadar zaman alacağına ve SQL Server Profiler'da nasıl gözükeceğine bakalım.

SqlConnection connection;

DateTime start = DateTime.Now;

for (int i = 0; i < 1000; i++)
{
  connection = new SqlConnection("server=.; database=northwind; integrated security=sspi; pooling=true");

  connection.Open();
  connection.Close();
  connection.Dispose();
}

DateTime end = DateTime.Now;

TimeSpan result = end - start;

MessageBox.Show(String.Format("Açılan bağlantıların işlem süresi : {0}", result.ToString()));

Yukarıdaki işlemin sonucu tam olarak 00:00:00.2275035 saniye sürdü. Buradaki asıl mantık şu; açılan ilk bağlantı bellekte tutulur ve geri kalan bağlantılar için kullanılır. Gelin bir de SQL Server Profiler'da nasıl göründüğüne bakalım.

SQL Server Profiler

Dediğim gibi, tek bir bağlantı açıldı ve geri kalan bağlantılar bellekten kullanıldı. Gördüğünüz gibi inanılmaz performans artışı sağlandı. Peki bağlantı havuzundaki bağlantı sayımıza sınır koyma şansımız var mı? Evet.

Max Pool Size : Bağlantı havuzumuzdaki saklanacak en fazla bağlantı sayısını belirtir. Varsayılan olarak 100'dür.

Min Pool Size : Bağlantı havuzumuzdaki saklanacak en az bağlantı sayısını belirtir. Varsayılan olarak 0'dır.

Eğer Max Pool Size'ı 50 olarak belirtir ve 50'den fazla bağlantı açarsanız (kapatmadan), şöyle bir hata ile karşılacaksınız.

Hata

"Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached." Diyor ki : Zaman aşımı süresi doldu. Havuza bağlantı elde edilemeden zaman aşımı süresi doldu. Bu, tüm havuz bağlantıları kullanıldığı ve en büyük havuz boyutuna erişildiği için oluşmuş olabilir. Bağlantınızı kapatmayı asla unutmayın! (Bağlantılı sınıflarda tabii)

Son olarak SqlConnection üzerinden kullanabileceğiniz 2 adet statik metottan bahsetmek istiyorum.

  1. SqlConnection.ClearAllPools() ile tüm havuzları temizleyebilir,
  2. SqlConnection.ClearPool(SqlConnection) ile belirli bağlantı havuzunu temizleyebilirsiniz.

Makaledeki örnek uygulamayı indirmek isterseniz buraya tıklayın.

DataTableReader Nesnesi

Bu makalemizde DataTableReader nesnesinin ne olduğunu ve nasıl kullanılacağını öğreneceğiz.

Senaryo : SqlDataAdapter nesnesi ile verileri veritabanından çekip bir DataTable'ı dolduracağım. Daha sonra DataTableReader ile bu DataTable üzerinden verileri okuyacağım.

Bilinmesi Gerekenler :

  • Makaledeki örnek .NET Framework 4.0 ve Visual Studio 2010 ile hazırlandı,
  • Makale, Northwind veritabanı gerektirmektedir. Northwind veritabanını indirmek istiyorsanız buraya tıklayın. Northwind veritabanı kullanmamın sebebi çoğu yazılımcının mevcut olması. Projenin boyutunu büyütmemek adına veritabanını projeme eklemedim.

Neler Öğreneceksiniz :

  • DataTableReader nesnesinin ne olduğunu, nerede ve nasıl kullanacağımızı öğreneceğiz.

DataTableReader ilk olarak aklınıza SqlDataReader'ı getirebilir ki haksız değildir. Çok benzer yapıya sahiptirler. DataTableReader da SqlDataReader gibi DbDataReader sınıfından türemiştir. İkisi de read-only ve forward-only yapıya sahiptir. Madem ki DataTableReader ile SqlDataReader bir çok ortak noktaya sahip, farkı neresinde? SQL veritabanı için nasıl ki SqlDataReader kullanılıyor, DataTable veya DataSet için de DataTableReader kullanılıyor ve bağlantının açık kalması gibi bir durum söz konusu değil. Çünkü bağlantısız nesneler (disconnected mimari) üzerinden çalışıyor. 

SqlDataAdapter adapter = new SqlDataAdapter("Select FirstName,LastName from Employees", "server=.; database=northwind; integrated security=sspi");

DataTable table = new DataTable();

adapter.Fill(table);

//DataTableReader bu satırda yaratılıyor.
DataTableReader tableReader = table.CreateDataReader();

if (tableReader.HasRows)
{
  while (tableReader.Read())
  {
     lst_employees.Items.Add(String.Format("{0} {1}", tableReader[0], tableReader[1]));
  }
}

adapter.Dispose();
table.Dispose();
tableReader.Dispose();

DataSet nesnesi üzerinden DataTableReader yaratmak için:

DataSet dataSet = new DataSet();

adapter.Fill(dataSet);

DataTableReader tableReader = dataSet.CreateDataReader();

Birden fazla DataTable üzerinde çalışmak için NextResult metodunu kullanabilirsiniz. Dediğim gibi, SqlDataReader'dan pek farkı yok ama kullanım alanında farklılık yaratabilir.

Makale ile ilgili örnek uygulamayı indirmek isterseniz buraya tıklayın.

Tutarlılık İhlali Kontrolü

Tutarlılık ihlali kavramı (Eşzamanlılık ihlali), birden fazla kişinin aynı veri üzerinde aynı anda değişiklik yapması sonucu ortaya çıkan sorundur. Çok kullanıcılı sistemlerde ortaya çıkması zor ancak çıktığı zaman hataya sebebiyet veren bir sorundur. Aşağıdaki makalede bu soruna çözüm üretip, uygulayacağız.

Senaryo : Veritabanındaki bir kayıt üzerinde değişiklik yaparak DBConcurrencyException hatası alacağız ve bunun çözümünü uygulayacağız.

Bilinmesi Gerekenler :

  • Makaledeki örnek .NET Framework 4.0 ve Visual Studio 2010 ile hazırlandı,
  • Makale, Northwind veritabanı gerektirmektedir. Northwind veritabanını indirmek istiyorsanız buraya tıklayın. Northwind veritabanı kullanmamın sebebi çoğu yazılımcının bilgisayarında mevcut olması. (projenin boyutunu artırmamak için eklemedim)

Neler Öğreneceksiniz :

  • Aşağıdaki makalenin sonucunda Optimistic Locking ve Pessimistic Locking ifadeleri anlayacaksınız,
  • DBConcurrencyException hatası ile karşılaştığınızda ne yapacağınızı bileceksiniz.

Öncelikle yaşadığımız/yaşayacağımız sorunu anlayalım. Çoklu kullanıcının kullanıldığı bir veritabanındaki bir satırı çeken Kullanıcı1 adlı kişi ve ardından aynı satırı çeken Kullanıcı2'yi düşünün. Kullanıcı1 satırda değişiklik yapıp veritabanına güncellediği zaman bundan Kullanıcı2'nin haberi olmayacaktır ve Kullanıcı2 de satırı değiştirmek istediği zaman hangi verinin tutarlı olup olmayacağı gibi bir sıkıntı oluşuyor. Bu sıkıntıyı önlemek adına iki tane yöntem var. Bunlar:

Optimistic Locking (İyimser Kilitleme) / Optimistic Concurrency Control (İyimser Tutarlılık İhlali Kontrolü) : Aynı anda birden fazla kişinin aynı veri üzerinde değişiklik yapabilme prensibine dayalıdır. Çünkü aynı anda birden fazla kişinin aynı veri üzerinde oynama ihtimali düşüktür. İşlem sırasında kilit konmaz, kullanıcı tarafından işlem yapılırken değişiklik olup olmadığına dair kontrol edilir.

Pessimistic Locking (Kötümser Kilitleme) / Pessimistic Concurrency Control (Kötümsel Tutarlılık İhlali Kontrolü) : Bu kontrolde, ilk kullanıcı veriyi okuduğu an veri üzerine kilit konur. Böylece sonraki kullanıcıların veriye erişmesi engellenerek, verinin doğruluğu garanti altına alınmış olur.

Örnekten önce Optimistic Locking ile ilgili bilgi vermek istiyorum. Optimistic Locking'de, değişiklik yapılacak verinin üzerine kilit konulmadığı için bir kaç kontrol yapılmalıdır. Bunlardan ilki değişiklik yapılacak verideki kolonun ilk hali kontrol edilmelidir. Örnek :

UPDATE Employees set LastName = @YeniLastName where LastName = @OrjinalLastName

İkinci yol ise satırın üzerinde bulunan timestamp kolonunun değişip değişmediği kontrol edilir. (timestamp read-only olup mevcut satırın versiyonunu üzerinde tutan veri tipidir. Satır üzerinde yapılan değişiklikte timestamp değeri de değişir.)

UPDATE Employees set LastName = @YeniLastName where TimeStampID = @OrjinalTimeStampID

Yukarıdaki yöntemler sırasında eğer hata meydana gelirse bu da DBConcurrencyException olacaktır. Şimdi hata alabileceğiniz bir uygulama yapalım. Senaryomuz : Veritabanındaki bir satırda güncelleme yapmadan önce programı bir yerde durdurup, veritabanından elle değiştirdikten sonra programımızdan güncelleme işlemine devam edeceğiz. Yani sistemde veri tutarsızlığına sebebiyet vecerek bir durum yaratacağız.

SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection("server=.; database=northwind; integrated security=sspi");
SqlCommand command = new SqlCommand("select * from employees", connection);

// SqlDataAdapter'e select komutumuzu veriyoruz.
adapter.SelectCommand = command;

command = new SqlCommand("update employees set LastName=@LastName where FirstName=@FirstName", connection);
command.Parameters.AddWithValue("@LastName", "Esmer");
command.Parameters.AddWithValue("@FirstName", "Firat");

// SqlDataAdapter'e update komutumuzu veriyoruz.
adapter.UpdateCommand = command;

DataSet set = new DataSet();
adapter.Fill(set, "Employees");

// SqlDataAdapter - Update işlemini yapabilmek için herhangi bir değişiklik yapıyoruz.
DataTable table = set.Tables[0];
table.Rows[0]["LastName"] = "DenemeSoyad";

/* Bu satıra breakpoint koyup veritabanınızdan, update sorgumuzda where koşulunda belirtmiş olduğumuz veriyi 
değiştirmenizi istiyorum. (yani FirstName'i Firat yerine başka bir şey yazın.)*/
int rowCount = adapter.Update(set, "Employees");

adapter.Dispose();
connection.Dispose();
set.Dispose();

DBConcurrencyException

Breakpoint'i koyduğunuz yerde veritabanınızdan veriyi değiştirip Update metodunu çalıştırdığınızda GÜM! "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." hatası alıyoruz. Türkçe : "Tutarlılık ihlali: UpdateCommand, beklenen 1 kaydın 0 kaydını etkiledi." Buradaki hatayı almamızın sebebi UpdateCommand'da belirtilen FirstName'in ilk başta Firat, daha sonra veritabanından değiştirdiğimiz şekilde değişmesi ve buna bağlı tutarsızlığın meydana gelmesi. Bu hatayı gidermek için SqlDataDapter nesnesinin RowUpdated olayını (event'ini) kullanabiliriz. Az önce hata aldığımız Update metotu satırında RowUpdated olayı tetiklenecek. RowUpdated olayı içerisinde DataRow nesnesinin HasErrors özelliğini kullanarak hatalı satır olup olmayacağını kontrol edeceğiz ve eğer satırda hata varsa satır işlenmeyecek, es geçilecek.

SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection("server=.; database=northwind; integrated security=sspi");
SqlCommand command = new SqlCommand("select * from employees", connection);

adapter.SelectCommand = command;

command = new SqlCommand("update employees set LastName=@LastName where FirstName=@FirstName", connection);
command.Parameters.AddWithValue("@LastName", "Esmer");
command.Parameters.AddWithValue("@FirstName", "Firat");

adapter.UpdateCommand = command;

adapter.RowUpdated += adapter_RowUpdated;

DataSet set = new DataSet();
adapter.Fill(set, "Employees");

DataTable table = set.Tables[0];
table.Rows[0]["LastName"] = "DenemeSoyad";

/* Bu satıra breakpoint koyup veritabanınızdan, update sorgumuzda where koşulunda belirtmiş olduğumuz veriyi değiştirmenizi 
istiyorum. (yani FirstName'i Firat yerine başka bir şey yazın.)*/
int rowCount = adapter.Update(set, "Employees");

string hataliSatir = string.Empty;

foreach (DataRow item in table.Rows)
{
   if (item.HasErrors)
    {
      // Değişiklik yapılacak satırın sırası ve hatasını alıyoruz.
      hataliSatir = String.Format("{0} -> {1}", item[0], item.RowError);
    }
}

adapter.Dispose();
connection.Dispose();
set.Dispose();

// İşlemin en sonunda kullanıcıyı bilgilendirecek mesajı gösteriyoruz.
MessageBox.Show(String.Format("Güncellenen kayıt sayısı : {0}\nHatalı Satır : {1}", rowCount, hataliSatir));
}

/* SqlDataAdapter satır güncelleme olayı. Hatayı burada yakalayacağız ve hata mesajını belirtip kullanıcıyı 
bilgilendireceğiz.*/
void adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    // 0 = Değişiklik olmadığını gösterir.
    if (e.RecordsAffected == 0)
    {
      e.Row.RowError = "Tutarlılık ihlali: UpdateCommand, beklenen 1 kaydın 0 kaydını etkiledi.";

      // Hatalı satır üzerinde işlem yapılmadan es geçiliyor.
      e.Status = UpdateStatus.SkipCurrentRow;
     }
}

Evet gördüğünüz gibi kilitleme yapmadık, ancak hata da almadık. Pessimistic Locking pek kullanmıyorum. Onun yerine Optimistic Locking ile hata kontrolü yapıyorum. Eğer kilitleme yapmak istiyorsanız (satır) aşağıdaki basit yapıya göz atın derim.

SELECT * FROM Employees  (HOLDLOCK, ROWLOCK) WHERE  EmployeeID = 10

Eğer hazırlamış olduğum örnek uygulamayı indirmek isterseniz buraya tıklayın.

ADO.NET ile MARS Kullanımı

Hazırlamış olduğumuz projelerde bir kere dahi olsa bir yerlerde açık DataReader unutmuşuzdur (Connected Mimari ile çalışıyorsak). Bu yüzden her DataReader kullanımı öncesi connection'ı kapatıp, sonraki işlemde tekrar açmak ve bunun kontrolünü neredeyse her sayfada yapmak çok can sıkıcı ve projemizi hataya açık bir hale getiriyordu. Fakat tek bir connection üzerinden, mevcut connection'ı kapatmadan birden fazla işlemi gerçekleştirmek mümkün. İşlemlerden kastım sorgu ve stored procedure'ler. Peki nasıl yapacağız bu işi?

Senaryo : Veritabanımız için bir tane SqlConnection, 3 tane SqlCommand ve bu SqlCommand'ları çalıştırmak için de 3 tane SqlDataReader yaratacağım. Yaratmış olduğum bu tek connection üzerinden (hiçbir DataReader arasında bağlantıyı açıp / kapatmadan) sorguları çalıştıracağım.

Bilinmesi Gerekenler :

  • Makaledeki örnek .NET Framework 3.5 ve Visual Studio 2010 ile hazırlandı,
  • Makale, Northwind veritabanı gerektirmektedir. Northwind veritabanını indirmek istiyorsanız buraya tıklayın. (projenin boyutunu artırmamak için eklemedim)
  • Makalede dispose işlemlerini yapmadım (veya using)

Yukarıda belirtmiş olduğum yöntem dışında yapılacak iki alternatif yol var. Birincisi bağlantıyı açıp kapatmak, ikincisi ise çalıştırmak istediğimiz sql sorgusu kadar connection tanımlamak. Ama ADO.NET 2.0 ile gelen yeni bir özellik var. Adı MARS (Multiple Active Result Sets). Tanımlamış olduğumuz SqlConnection'larda MARS default olarak false'tur. MARS'ı aktif etmek için yapmanız gereken tek şey yaratmış olduğunuz SqlConnection'a "MultipleActiveResultSets=true" ifadesini eklemek. Ama daha önce bunu neden yaptığımızı anlatmak istiyorum. Aşağıdaki kod parçasıyla Northwind veritabanında bulunan "Products" ve "Customers" tablolarından veri çekeceğim. Basit bir işlemle bunu gerçekleştirmek istiyorum.

SqlConnection baglanti = new SqlConnection("Server=.; Database=Northwind; Trusted_Connection=Yes");

SqlCommand komut1 = new SqlCommand("select * from Products", baglanti);
SqlCommand komut2 = new SqlCommand("select * from Customers", baglanti);

baglanti.Open();

SqlDataReader okuyucu1 = komut1.ExecuteReader();
SqlDataReader okuyucu2 = komut2.ExecuteReader();

baglanti.Close();

Hata

ve GÜM! Yukarıdaki hata mesajında (There is already an open DataReader associated with this Command which must be closed first.) diyor ki : "Bu komut ile ilişkili, öncelikle kapatılması gereken açık bir DataReader zaten var." Akla gelen ilk yollardan birisi hemen Connection'ımızı kapatıp tekrardan açmak olacak. O da böyle görünecektir.

baglanti.Open();

SqlDataReader okuyucu1 = komut1.ExecuteReader();

baglanti.Close();

baglanti.Open();

SqlDataReader okuyucu2 = komut2.ExecuteReader();

baglanti.Close();

İkincisi ise DataReader sayımız kadar SqlConnection yaratmak.

SqlConnection baglanti = new SqlConnection("server=.; database=northwind; Trusted_Connection=Yes");
SqlConnection baglanti2 = new SqlConnection("server=.; database=northwind; Trusted_Connection=Yes");

SqlCommand komut1 = new SqlCommand("select * from Products", baglanti);
SqlCommand komut2 = new SqlCommand("select * from Customers", baglanti2);

baglanti.Open();
baglanti2.Open();

SqlDataReader okuyucu1 = komut1.ExecuteReader();
SqlDataReader okuyucu2 = komut2.ExecuteReader();

baglanti.Close();
baglanti2.Close();

Bu iki alternatif yol da işimizi görür. Ancak hiç gerek yok. Şimdi en kısa ve güzel yolu yazacağım.

// MultipleActiveResultSets=True ibaresini SqlConnection'ımıza ekledikten sonra işimiz bitiyor.
SqlConnection baglanti = new SqlConnection("server=.; database=northwind; MultipleActiveResultSets=True; Trusted_Connection=Yes");

SqlCommand komut1 = new SqlCommand("select * from Products", baglanti);
SqlCommand komut2 = new SqlCommand("select * from Customers", baglanti);

baglanti.Open();

SqlDataReader okuyucu1 = komut1.ExecuteReader();
SqlDataReader okuyucu2 = komut2.ExecuteReader();

baglanti.Close();

SQL Server Profiler ile bağlantıyı incelediğimizde bağlantının kapatılmadan art arda gerçekleştiğini göreceksiniz.

SQL Server Profiler

Not : Multiple Active Result Sets, MS SQL Server 2005 ve yüksek versiyonları tarafından destekleniyor. MS SQL Server 2005'in altındaki versiyonlar için kullanılırsa ne olur bilmiyorum. Hatta deneme şansınız olursa bana da bildirin. Bu arada SqlConnection.ServerVersion ile bağlandığınız veritabanı versiyonunu kontrol edebilirsiniz.

Uygulamalı halini görmek için buraya tıklayın.