Microsoft Visual Basic.NET ve ADO.NET ile SQL Server İşlemleri / 3

Yazar: Kadir Sümerkent
Kategori: ADO.NET & Entity Framework
Eklenme Tarihi: 14.10.2007 01:43:05



Command nesneleri genellikle DataReader nesnesi tarafından kapsanacak verinin belirlendiği SQL cümlelerini tanımlamakta kullanılır. Bu bölümde Command ve DataReader nesnelerinin kullanımı üzerinde duracağız. Bu bölümde veri görüntüleme işlemlerinin ötesinde, Command nesnesinin data definition işleml

- Command ve DataReader Nesneleri ile Çalışmak

Command nesneleri genellikle DataReader nesnesi tarafından kapsanacak verinin belirlendiği SQL cümlelerini tanımlamakta kullanılır. Bu bölümde Command ve DataReader nesnelerinin kullanımı üzerinde duracağız. Bu bölümde veri görüntüleme işlemlerinin ötesinde, Command nesnesinin data definition işlemlerinin gerçekleştirilmesinde kullanımına bir örnek vereceğiz ve bir kullanıcı tanımlı fonksiyon oluşturacağız..

Sonuçları MessageBox ile Görüntülemek
SQL Server veritabanında bulunan verileri görüntülemek için SqlCommand ile SqlDataReader nesnesine aktarmak son derece kolaydır. İşe, verileri görüntülemek istediğiniz veritabanına bağlantı oluşturmakla başlar, bir SqlCommand nesnesi oluşturur ve SqlCommand nesnesine kullanacağı bağlantıyı (SqlConnection) belirtir ve çalıştırılacak T-SQL ifadesini belirtiriz. Command nesnelerini kullanırken t-sql ifadeleri yerine stored procedureleri de kullanabiliriz.

DataReader nesnesi Command nesnesi tarafından döndürülen sonuç kümesini okumakta kullanılır. Command nesnesinin sonuç kümesini bir DataReader nesnesine aktarmak için Command nesnesinin ExecuteReader metodunu kullanırız. Sonuç kümesinin DataReader nesnesine aktarılmasından sonra DataReader nesnesinin Read metodunu kullanarak sonuç kümesindeki satırları sırayla okuyabiliriz. DataReader nesnesindeki satırların ilgili satırı temsil eden bir index değeri vardır. Bu değer birinci kayıt için 0dır ve her satır için 1 artar.

Aşağıdaki örnekte Northwind örnek veritabanı içindeki kategorileri bir DataReader nesnesine aktarıyor ve buradan sırayla okuyor, son olarakta bir mesaj kutusu ile görüntülüyoruz.

Sub KategorileriListele()

Yeni bir SqlConnection nesnesi oluşturuyoruz

SqlConnection için ConnectionString belirtiyoruz

Dim c As New SqlConnection("Data Source=(local); Integrated Security=SSPI;Initial Catalog=northwind")

Bağlantıyı açıyoruz

c.Open()

Yeni bir SqlCommand nesnesi oluşturuyoruz

Ancak bu SqlCommand nesnesini SqlConnection

nesnesinin CreateCommand metodu ile oluşturuyoruz

Dim cmd As SqlCommand = c.CreateCommand()

Oluşturduğumuz SqlCommand nesnesi için

Commandtext değerini belirtiyoruz

cmd.CommandText = "SELECT CategoryID, CategoryName FROM Categories"

Yeni bir SqlDataReader nesnesi oluşturuyoruz ve

Oluşturduğumuz SqlCommand nesnesinin ExecuteReader

metodu ile belirttiğimiz Select ifadesi ile oluşacak

sonuç kümesini SqlDataReader nesnesine aktarıyoruz

Dim r As SqlDataReader = cmd.ExecuteReader

Mesaj kutusunda görüntüleyeceğimiz metni oluşturmaya başlıyoruz

Dim s As String = "CategoryID ve Kategori Adlarının Özeti" _

& StrDup(2, vbCr)

Aşağıdaki satır, do..loop bloğu içindeki işlemlerin

SqlDataReader nesnesinin son satırına kadar

tekrarlanmasını sağlıyor

Do While r.Read

s = s & "Kategori " & r.GetInt32(0).ToString & " = " & r.GetString(1) & vbCr

Loop

Mesajımızı görüntülüyoruz

MsgBox(s)

r.Close()sistem kaynakları bizim için önemli :)

c.Close()

 End Sub


Şekil 3:1 – Kategoriler Listesi


DataReader’daki Satırların Bloklar Halinde Görüntülenmesi
Yukarıdaki örnekte mesaj kutusunun DataReader içindeki satırların görüntülenmesi için ne kadar uygun bir yer olduğunu gördük. Ancak büyük sonuç kümelerinde mesaj kutusunun karakter limiti, DataReader içindeki tüm satırların görüntülenmesi için yetmeyebilir. Bu sorunun çözümü ise sonuç kümesindeki satırların her defasında n adedinin görüntülenmesi, yani bloklar halinde görüntülenmesidir. Bu sayede kullanıcılar sonuç kümesindeki verileri sırayla ve her defasında makul bir miktarda görecektir.



Aşağıdaki grafikte gördüğümüz örnekte gibi son kayıda ulaşana kadar müşteriler beşer beşer, sonuç kümesinin sonuna ulaşıldığında ise kalan müşteriler (farklı bir diyalog ile) görüntüleniyor ;



Şekil 3:2 – Müşterilerin Bloklar Halinde Listelenmesi



Aşağıda bu işlemi gerçekleştiren bir prosedürü açıklamalı olarak görebilirsiniz;

Sub MusteriNumaralariniGoruntule(ByVal intSize As Integer)

Yeni bir SqlConnection nesnesi oluşturuyor ve

ConnectionString belirtiyoruz

Dim c As New SqlConnection("Data Source=(local);" & _

"Integrated Security=SSPI;Initial Catalog=northwind")

ve bağlantıyı açıyoruz

c.Open()

SqlConnection nesnesinin CreateCommand metodunu

kullanarak yeni bir SqlCommand nesnesi oluşturuyoruz

Dim cmd As SqlCommand = c.CreateCommand

Oluşturduğumuz SqlCommand nesnesinin

CommandText özelliğine sorgu kriterimizi içeren

T-Sql ifadesini yazıyoruz

cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers"

Yeni bir SqlDatareader nesnesi oluşturuyor ve

SqlCommand nesnemizin ExecuteReader metodu ile

T-Sql ifadesinin çalıştırılması ile oluşacak sonuç

kümesini SqlDataReader nesnesine aktarıyoruz

Dim r As SqlDataReader = cmd.ExecuteReader

Datareader içinde intSize ile belirtilen boyutta

sırayla ilerle

Dim i As New Integer

Dim s As String = "Müşteri No ve Firma Adı: " & StrDup(2, vbCr)

Do While r.Read()

s = s & r.GetString(0) & vbTab & _

r.GetString(1) & vbCrLf

i += 1

If (i Mod intSize) = 0 Then

s = s & StrDup(2, vbCr) & _

"Devam etmek için OK butonunu tıklayın " & _

intSize.ToString & " müşteri."

MsgBox(s, , "Müşteri No ve Adı")

s = _

"Müşteri No ve Firma Adı: " & StrDup(2, vbCr)

End If

Loop



Eğer sonuç kümesinin sonuna ulaşmışsak son

mesaj kutusunu görüntüle

If (i Mod intSize) > 0 Then

s = s & StrDup(2, vbCr) _

& "Mesaj kutusunu kapatmak için OK butonunu tıklayınız."

MsgBox(s, , "Müşteri No ve Adı")

End If



Reader ve Connection nesne referanslarını kapatıyoruz

r.Close()

c.Close()



 End Sub

Stored Procedure’lerin Parametreler ile Kullanımı
DataReader nesnesine sonuç kümelerini aktarmak için Command nesnesinde T-Sql ifadeleri kullanabildiğimiz gibi Stored procedure’ler de kullanabiliriz. Stored Procedure’leri kullanmak bize iki önemli avantaj sağlar. Birincisi, Stored Procedure’ler derlenmiş olarak Sql Server üzerinde barındırılırlar. Bu sayede verilerin size aktarılması için T-SQL ifadesinin compile edilmesi ve execution plan çıkarılması için gerekecek zamanı kazanmış oluruz. Tabi bu bize zamanla birlikte bu işlemlerin gerçekleştirilmesinde kullanılacak sistem kaynaklarınıda kazanmamızı sağlar. Stored Procedure’lerin kullanılmasının sağladığı ikinci avantaj ise parametreler kabul edebiliyor olmasıdır. Bu sonuç kümesinin, istenilen kriterler doğrultusunda çalışma zamanında düzenlenebilmesini sağlar.



Stored Procedure parametrelerine değer atama işlemi iki şekilde gerçekleştirilebilir. Çoğu developer stored procedure’u çalıştıran bir T-Sql ifadesi kullanmayı ve parametre değerlerini göndermeyi tercih eder. İkinci yaklaşım ise .NET Framework ile gelen ve parametreyi gönderirken parametrenin türünü de belirtebileceğimiz yeni bir sözdiziminin kullanılmasıdır. Biz bu bölümde yazılım geliştiricilerin çoğunluğunun kullandığı metodu, ilerleyen bölümlerde ise .NET Framework ile gelen söz dizimini kullanıyor olacağız.



Bu bölümdeki örneğimiz Northwind veritabanında yer alan CustOrdHist stored procedure’une dayanıyor. Bu stored procedure, bir müşteri tarafından hangi üründen kaç adet sipariş verildiğini görüntülemektedir. Prosedür müşteriyi tanımlayan, 5 karakterden oluşan string türünde bir parametre kabul etmekte. Her satırda ürün adı ve belirtilen müşterinin bu üründen kaç adet sipariş verdiği görüntülenmektedir. Aşağıda bu Stored Procedure’un kodları yer almaktadır.



CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND 
O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName


Bahsettiğimiz çözümü iki alt prosedür kullanarak geliştireceğiz. Birinci prosedürümüz; RunCustOrderHistWithString stored procedure’u çağıracak ve dönen sonuç kümesini içeren bir DataReader nesnesi oluşturacak. Bu prosedür iki parametre alıyor. Birincisi CustomerID değeri ve bir mesaj kutusunda kaç adet kayıt görüntüleneceğini belirleyen Integer türündeki intSize parametresi. Bu prosedür temel olarak şu işlemleri gerçekleştirmektedir;

- Bir Connection nesnesi oluşturur.
- CustOrderHist adlı stored procedure’u bir parametre göndererek çalıştıracak bir Command nesnesi oluşturur.
- CustOrderList stored procedure’unden dönen sonuçları içeren bir DataReader nesnesi oluşturur.



Stored procedure’u çalıştırmak için bu şekilde bir T-SQL ifadesi kullanmanın en önemli avantajı, Command nesnesine bir SQL ifadesi belirtmek ile arasındaki sözdizimi benzerliğidir. Ancak tek avantajıda budur diyebiliriz. Bu uygulamada SQL Server gönderdiğimiz SQL ifadesini yeniden derlemek ve execution plan çıkartmak zorunda kalacaktır. Bir diğer dezavantaj ise explicit data typing imkanından faydalanamamamızdır. Explicit Data Typing kullanmanın yani SQL Server’a gönderdiğimiz parametrelerin türünü belirtmenin artısı ise, SQL Server’ın hatalı parametre değerlerini tespit etmek ve kullanıcıya bildirmek için harcayacağı zamanı oldukça kısaltmasıdır.


İkinci alt prosedür ise DataReader nesnesindeki satırları görüntüleyecek olan drdToMessageBox prosedürür. Bu prosedürde yaptığımız işlemler her ne kadar bir önceki örnektekine benzesede, prosedürün kabul ettiği parametrelerden ilk ikisinin, yani DataReader ve Connection değerlerinin, referans değeri alıyor olmasıdır. Diğer iki parametre, yani CustomerID ve bir mesaj kutusunda görüntülenecek en fazla kayıt sayısını belirleyen intSize ise Visual Basic .NET’in varsayılan olarak kullandığı Value değerini kullanmaktadır. Buradaki işlemi iki alt prosedüre bölmemiz, bir sonraki bölümde ikinci alt prosedürdeki kodları yeniden yazmaktan kurtaracak..



Sub RunCustOrderHistWithString(ByVal CustomerID As String, _

ByVal intSize As Integer)

Northwind veritabanına bağlanıyoruz.

Dim c As SqlConnection = New SqlConnection("Data Source=(local);" & _

"Integrated Security=SSPI;Initial Catalog=northwind")

c.Open()

Yeni bir SqlCommand nesnesi oluşturuyoruz ve

CommandText özelliğine istediğimiz Stored Procedureu

çalıştıracak SQL ifadesini yazıyoruz

Dim cmd As SqlCommand = New SqlCommand("EXEC CustOrderHist " & CustomerID, c)

DataReader nesnesini oluşturuyor ve

sonuç kümesini yüklüyoruz

Dim r As SqlDataReader = cmd.ExecuteReader()

Sonuç kümesini görüntüleyecek prosedürü

çağırıyor ve parametreleri belirtiyoruz

drdToMessageBox(r, c, CustomerID, intSize)

End Sub



Sub drdToMessageBox(ByRef r As SqlClient.SqlDataReader, _

ByRef c As SqlClient.SqlConnection, ByVal CustomerID As String, _

ByVal intSize As Integer)

Mesaj kutuları için başlık metnini

oluşturuyoruz

Dim s As String = CustomerID & " tarafından verilen siparişler: " & StrDup(2, vbCr)

Dim i As Integer

Mesaj kutularını görüntülüyoruz

Do While r.Read()

s = s & r.GetInt32(1) & vbTab & r.GetString(0).ToString & vbCrLf

i += 1

If (i Mod intSize) = 0 Then

s = s & StrDup(2, vbCr) _

& "Devam etmek için OK butonunu tklayınız " & _

intSize.ToString & " customers."

MsgBox(s, , "CustOrderHist Stored Procedure unden gelen veriler")

s = CustomerID & " tarafından verilen siparişler: " & StrDup(2, vbCr)

End If

Loop

Sonuç kümesinin sonuna yani DataReaderda yer alan son

satıra ulaşıldığında görüntülenecek mesaj kutusu

If (i Mod intSize) <> 0 Then

s = s & StrDup(2, vbCr) "Çıkmak için OK butonunu tıklayınız."

MsgBox(s, , "CustOrderHist Stored Procedure unden gelen veriler")

End If

sistem kaynakları..

r.Close()

c.Close()

End Sub

 


Örneği yazmadan önce bu örneğin mevcut imkanlar doğrultusunda sistem kaynaklarımızı daha az kullanacak yöntemler olduğu için doğru bir çözüm olmadığını söylemiştik. Peki doğrusu nasıl? Şimdi yukarıdaki iki alt prosedürden birincisini (RunCostOrderHistWithString) yeniden yazalım..

Stored Procedure’u İsmi ile Çağırmak ve Parametre Göndermek
Bir Stored Procedure’u SQL ifadesi kullanmadan çağırmak ve parametre(ler) göndermek mümkündür ki (ben de dahil) pek çok developer bu yöntemi daha doğru görmektedir. Bu yaklaşım strong data typing’in sunduğu avantajlardan faydalanmamızı sağlayacaktır ve geçersiz değerler, sunucuca ek yük getirmeden ayırt edilebilecek ve zaman kazanılacaktır.



Aşağıdaki örnek, SQL Server’a bir T-SQL ifadesi göndermeden doğrudan Stored Procedure’u çalıştırır ve gerekli parametreleri gönderir. Yaptığı iş açısından yukarıdaki alt prosedür (RunCostOrderHistWithString) ile bir fark olmamasına rağmen, daha doğru bir ifadedir.



Sub RunCustOrderHistWithParameter(ByVal CustomerID As String, _

ByVal intSize As Integer)



Northwind veritabanı bağlantısı

Dim c As SqlConnection = _

New SqlConnection("Data Source=(local);" & _

"Integrated Security=SSPI;Initial Catalog=northwind")

c.Open()



Yeni bir SqlCommand nesnesi oluşturuyoruz

ve kullanacağımız SPnin adını belirtiyoruz

Dim cmd As SqlCommand = _

New SqlCommand("CustOrderHist", c)

CommandType özelliğini StoredProcedure olarak belirtiyoruz

cmd.CommandType = CommandType.StoredProcedure



Göndereceğimiz parametreyi oluşturuyoruz.

Bu aşamada SqlDbType ile türünü belirterek dönütürme

için zaman harcanmasının önüne geçiyoruz ve

tanımladığımız parametreye değer atıyoruz

Dim p1 As SqlParameter = _

cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5)

p1.Value = CustomerID



DataReader nesnesini oluşturuyor ve veri yüklüyoruz

Dim r As SqlDataReader = cmd.ExecuteReader()



Sonuçları görüntülemek için ikinci alt prosedürü

çağırıyor ve parametreleri gönderiyoruz

drdToMessageBox(r, c, CustomerID, intSize)



 End Sub


Şu ana kadar veri görüntülemek için Command ve Datareader nesnelerinin kullanımı konusunu ele aldık. Bu noktadan sonra SQL Server üzerinde nesneler oluşturmak için Command nesnesinin kullanımını ele alacağız.

Command Nesnesi ile Veritabanı Nesnelerinin Oluşturulması

Command nesnesi bize sadece sonuç kümeleri döndürmekten çok daha fazlasını sunar. Örneğin SQL Server üzerindeki nesneleri yönetmek amacıyla Command nesnesini kullanabilirsiniz. Bu bölümde Command nesnesini veri döndürmek dışında bir kullanımı üzerinde duracağız ve Sql Server üzerinde kullanıcı tanımlı bir fonksiyon oluşturacağız, daha sonra bu fonksiyonu kullanacağız ve son olarak sileceğiz.

Örnek olarak geliştireceğimiz kullanıcı tanımı fonksiyon udfDaysDiffLessX iki tarih arasındaki süreyi belirlenen sapma ile hesaplamakta. Bu örnek fonksiyonu, bir işin kaç gün gecikme ile gerçekleştiğini hesaplamakta kullanabilirsiniz. Örneğin bir sipariş alındıktan sonraki üç gün içinde teslim edilmeli ise bu kullanıcı tanımlı fonksiyonu kullanarak işlemin belirlenen işlem süresinden kaç gün gecikme ile yapıldığını hesaplayabiliriz.

Bu kullanıcı tanımlı fonksiyonu oluşturmak için CreateAndInvokeUDF adlı bir prosedür geliştireceğiz. Prosedürümüz önce kullanıcı tanımlı fonksiyonu oluşturacak, sonra çalıştıracak, son olarakta silecektir.

Sub CreateAndInvokeUDF(Optional ByVal intOrderNo As Integer = 10248, _

Optional ByVal strx As String = "1")

Northwind veritabanı bağlantısı

Dim cnn1 As SqlConnection = _

New SqlConnection("Data Source=(local);" & _

"Integrated Security=SSPI;Initial Catalog=northwind")

cnn1.Open()



Önce (eğer varsa) udfDaysDiffLessx adlı kullanıcı tanımlı

fonksiyonu silecek SQL ifadesini yazıyor ve Command nesnesinin

ExecuteNonQuery metodunu kullanarak çalıştırıyoruz.



ExeCuteNonQuery metodu bize SQL İfadesi sonucunda etkilenen

(silinen, eklenen, değiştirilen) satır sayısını döner

etkilenen satır olmazsa -1 değeri döner

Dim str1 As String = _

"IF EXISTS " & _

"(SELECT * " & _

"FROM INFORMATION_SCHEMA.ROUTINES " & _

"WHERE ROUTINE_NAME = udfDaysDiffLessx) " & _

"DROP FUNCTION udfDaysDiffLessx"

Dim cmd1 As SqlCommand = New SqlCommand(str1, cnn1)

cmd1.ExecuteNonQuery()



Kullanıcı tanımlı fonksiyonu oluşturacak SQL ifadesini

oluşturuyor ve yine ExecuteNonQuery metodunu kullanarak

çalıştırıyoruz

str1 = "CREATE FUNCTION udfDaysDiffLessx" & _

"(@date1 as datetime, @date2 as datetime, " & _

"@x as Integer) " & _

"RETURNS int " & _

"AS " & _

"BEGIN " & _

"Return(DATEDIFF(day,@date1,@date2)-@x) " & _

"END"

cmd1.CommandText = str1

cmd1.ExecuteNonQuery()



Oluşturulan kullanıcı tanımlı fonksiyonu kullanacak

Yeni bir SELECT ifadesi oluşturuyoruz ve SqlCommand

nesnesinin (cmd1) Commandtext özelliğine atıyoruz

Dim strSQL As String

strSQL = "SELECT LEFT(OrderDate,11) AS Order Date, " & _

"LEFT(ShippedDate,11) AS Shipped Date, " & _

"dbo.udfDaysDiffLessx(OrderDate, ShippedDate, " & _

strx & ") AS Days Late " & _

"FROM Orders " & _

"WHERE OrderID = " & intOrderNo.ToString

cmd1.CommandText = strSQL



Sonuç kümesini DataReader nesnesine aktarıyoruz ve içeriğini

MsgBox fonksiyonu ile görüntülemek için biçimlendiriyoruz

Dim drd1 As SqlDataReader = cmd1.ExecuteReader()

drd1.Read()

str1 = intOrderNo.ToString & " numaralı sipariş için.." & vbCr & _

"Sipariş Tarihi: " & drd1.GetString(0) & vbCr & _

"Gönderim Tarihi: " & drd1.GetString(1) & vbCr & _

"Normal gönderim süresi olan " & strx & " günden " _

& drd1.GetInt32(2).ToString & " gün gecikme var."

MsgBox(str1, , _

"Kullanıcı Tanımlı Fonksiyon")



UDFi silerek Northwind üzerinde yaptığımız değişiklikleri

geri alıyoruz

str1 = _

"IF EXISTS " & _

"(SELECT * " & _

"FROM INFORMATION_SCHEMA.ROUTINES " & _

"WHERE ROUTINE_NAME = udfDaysDiffLessx) " & _

"DROP FUNCTION udfDaysDiffLessx"

cmd1.CommandText = str1



DataReader nesnesini kapatıyoruz

drd1.Close()

Oluşturduğumuz yeni SQL ifadesini çalıştırarak

Kullanıcı tanımlı fonksiyonu Nowthwind veritabanından

siliyoruz

cmd1.Connection = cnn1

cmd1.ExecuteNonQuery()

Son olarak bağlantıyı kapatıyoruz

cnn1.Close()



 End Sub


Yukarıdaki örnekte oluşturduğumuz kullanıcı tanımlı fonksiyonu 10249 kodlu sipariş için 3 günlük bir offset ile çalıştırıyoruz.. Sonuç ise aşağıdaki gibi..



Şekil 3:3 – Kullanıcı Tanımlı Fonksiyonumuz Çalışıyor

Bir sonraki bölümde, DataAdapter, DataSet, Form ve Form kontrolleri konularını ele alıyor olacağız..

Kadir SÜMERKENT