Makale Özeti

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..

Makale

3 - 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
kadirs@yazgelistir.com