Entity Framework – Code First İle Stored Procedure Kullanımı

Merhaba,

Bir önceki Entity Framework – Code First İle Stored Procedure Oluşturma başlıklı yazımda EF’de nasıl SP oluşturulduğunu detaylıca irdelemiştik. Bu yazımızda ise EF’de SP nasıl kullanılır? sorusunun cevabını aynı hassasiyette vereceğiz.

EF – Code First yaklaşımında SP kullanmak istiyorsak eğer yapmamız gereken oldukça basittir. Elimizde aşağıdaki gibi bir SP olduğunu varsayarsak eğer;

            CreateStoredProcedure(
             "dbo.OrnekSP",
             p => new
             {

             },
             body:
                 @"
                    Insert Ogrenciler Values('Gençay', 'Yıldız', 'Artvin')  
                  "
                                 );

bu SP’yi aşağıdaki gibi çalıştırabiliriz.

            OkulDBContext Veri = new OkulDBContext();
            Veri.Database.ExecuteSqlCommand("OrnekSP");

Context sınıfı üzerinden veritanında bir SQL sorgusunu “.Database.ExecuteSqlCommand” komutu ile çalıştırabiliyoruz. Haliyle ExecuteSqlCommand metoduna verdiğimiz sorgu ilgili veritabanı üzerinde çalıştırılacaksa, o sorgu yerine SP’mizin ismi olan “OrnekSP” sorgusunu yazıyor ve çalıştırıyoruz.

SP’mizin içeriği Insert sorgusundan oluştuğu için haliyle çalışıp çalışmadığını “Ogrenciler” tablosunu kontrol ederek anlayabiliriz.

Entity Framework - Code First İle Stored Procedure Kullanımı
Gördüğünüz gibi SP’mizi başarıyla çalıştırabildik.

Peki… Şimdide parametre alan bir SP oluşturalım ve onu kullanalım.

            CreateStoredProcedure(
             "dbo.OrnekParametreliSP",
             p => new
             {
                 OgrenciID = p.Int(),
                 Adi = p.String(),
                 SoyAdi = p.String(),
                 Memleketi = p.String()
             },
             body:
                 @"
                    Update Ogrenciler Set Adi = @Adi, SoyAdi = @SoyAdi, Memleketi = @Memleketi where OgrenciID = @OgrenciID
                  "
                                 );

Gördüğünüz gibi SP’miz “OgrenciID”, “Adi”, “SoyAdi” ve “Memleketi” olmak üzere dört adet parametre almaktadır.

            OkulDBContext Veri = new OkulDBContext();
            SqlParameter pOgrenciID = new SqlParameter("@OgrenciID", 1);
            SqlParameter pAdi = new SqlParameter("@Adi", "Kür");
            SqlParameter pSoyAdi = new SqlParameter("@SoyAdi", "Şad");
            SqlParameter pMemleketi = new SqlParameter("@Memleketi", "Ötüken");
            Veri.Database.ExecuteSqlCommand("OrnekParametreliSP @OgrenciID, @Adi, @SoyAdi, @Memleketi", pOgrenciID, pAdi, pSoyAdi, pMemleketi);

Yukarıdaki gibi “OrnekParametreliSP” isimli SP’yi çalıştırdığım parametre sırasına dikkat ederek gereken değerleri göndermekteyim. Tabi bu değerleri gönderirken her bir parametreyi SqlParameter tipinden bir nesnede tutmakta ve ExecuteSqlCommand metodunu çağırırken sorgu içerisinde parametre değerlerini vermekteyim. Nihayetinde parametre tanımlarken verdiğimiz sıra, bu şekilde kullanımda da öncelik önemine sahip olduğu için parametreleri gönderirken bu sıralamaya dikkat etmekteyim.

Şimdide output bir parametre alan SP üzerinde çalışmamızı yapalım.

            CreateStoredProcedure(
             "dbo.OrnekOutPutParametreliSP",
             p => new
             {
                 OutParametre = p.String(outParameter: true)
             },
             body:
                 @"
                    Select @OutParametre = 'Gençay YILDIZ'
                  "
                                 );

Kullanımı ise aşağıdaki gibidir.

            OkulDBContext Veri = new OkulDBContext();

            SqlParameter pOutParametre = new SqlParameter("@OutParametre", System.Data.SqlDbType.NVarChar)
            {
                Direction = System.Data.ParameterDirection.Output
            };

            Veri.Database.ExecuteSqlCommand("OrnekOutPutParametreliSP @OutParametre out", pOutParametre);

Eğer bu şekilde bir kullanıma yeltenirsek aşağıdaki hatayı alacağız.

An unhandled exception of type ‘System.InvalidOperationException’ occurred in EntityFramework.SqlServer.dll

Additional information: String[0]: Size özelliğinin 0 olan boyutu geçersiz.

Hatamızın görsel hali ise;
Entity Framework - Code First İle Stored Procedure Kullanımı

Peki bu hata neden kaynaklanmaktadır?
Eğer string(nvarchar) tipinde bir Output parametre kullanıyorsak ve yukarıdaki gibi SqlParameter tipinden bir nesne üzerinde veri taşıyorsak bu nesnenin tutacağı veri aralığının boyunu belirtmemiz gerekmektedir.
Daha doğrusu, Output olarak verilecek string(nvarchar) parametrenin SP’deki aralığı ne ise en az o aralıkta bir parametre oluşturmalıyız.

            OkulDBContext Veri = new OkulDBContext();
            SqlParameter pOutParametre = new SqlParameter("@OutParametre", System.Data.SqlDbType.NVarChar, int.MaxValue)
            {
                Direction = System.Data.ParameterDirection.Output
            };
            Veri.Database.ExecuteSqlCommand("OrnekOutPutParametreliSP @OutParametre out", pOutParametre);

İşte boyutumuzuda constructerdan vererek hatamızı düzeltmiş olduk.

Bakalım pOutParametre isimli parametremize içeriden değer gelmiş mi?
Entity Framework - Code First İle Stored Procedure Kullanımı
Gördüğünüz gibi ilgili veriyi Output parametremiz getirmiş bulunmaktadır.

Farkındaysanız şuana kadar kullandığımız SP’lerin hiçbiri içerisinde tablo dönen Select sorgusu barındırmamaktadır. Haliyle son olarak geriye bir tablo dönecek sorguyu barındıran SP kullanımını irdeleyelim.

            CreateStoredProcedure(
             "dbo.OgrencilerSP",
             p => new
             {

             },
             body:
                 @"
                    Select * from Ogrenciler
                  "
                                 );

SP sonucunda geriye dönecek tabloyu temsil edecek bir Entity model oluşturulmalıdır.

Eğer ki, SP sonucu dönecek olan tablo farklı işlemler sonucu oluşturulmuş bir tablo ise o tabloyu temsil edecek Entity gerekeceğini baştan söyleyelim. Yok eğer SP sonucu gelen tablolar fiziksel tabloların birebir aynıları ise onları table classları temsil edebilmektedir.

Haliyle yukarıdaki SP sonucunda Ogrenciler tablosu geleceği için “Ogrenciler” tablosunu temsil eden entityi tip olarak kullanabileceğiz.

            OkulDBContext Veri = new OkulDBContext();
            var Ogrenciler = Veri.Database.SqlQuery<Ogrenci>("OgrencilerSP").ToList();

Yukarıdaki kod bloğunu incelerseniz eğer SP’den dönecek olan bir tablo mevzu bahis olduğu için “SqlQuery” metoduyla çalışmaktayız. Haliyle SP sonucu elde edilen tabloyu satır satır, bu Generic metoda verdiğimiz tipte oluşturduğu nesneler olarak tutmaktadır. Tablonun kolon isimleri ile, tipin property isimleri birbirini destekliyorsa hatasız bu işlemi gerçekleştirecek ve bize veri kümesini ilgili nesne tipinde dolduracaktır.

Bakalım çalışıyor mu?
Entity Framework - Code First İle Stored Procedure Kullanımı

Gördüğünüz gibi net bir şekilde verilerimizi elde ettik.

Peki SqlQuery metodunda parametreli SP kullanmaya çalışırsak nasıl olur?
Bakalım nasıl oluyormuş…

            CreateStoredProcedure(
             "dbo.OgrencilerSP",
             p => new
             {
                 OgrenciID = p.Int()
             },
             body:
                 @"
                    Select * from Ogrenciler where OgrenciID = @OgrenciID
                  "
                                 );

Kullanacağımız SP yukarıdadır.

            SqlParameter pOgrenciID = new SqlParameter("@OgrenciID", 1);
            OkulDBContext Veri = new OkulDBContext();
            var Ogrenciler = Veri.Database.SqlQuery<Ogrenci>("OgrencilerSP @OgrenciID", pOgrenciID).ToList();

Evet… Gördüğünüz gibi yukarıdaki işlemlerde parametre konusunda ne yaptıysak, bunda da aynı işlemi gerçekleştirmekteyiz.
Entity Framework - Code First İle Stored Procedure Kullanımı
Gördüğünüz gibi sorunsuz çalışmaktadır.

Sanırım biraz ağır bir yazı oldu… Malumunuz ben internette bu konuda pek fazla Türkçe kaynak olmaması üzerine olabilecek tüm hata ve durumları kapsayacak bir makale olsun istedim…

Okuduğunuz için teşekkür ederim…

Sonraki yazılarımda görüşmek üzere…

İyi çalışmalar…

Bunlar da hoşunuza gidebilir...

16 Cevaplar

  1. OZGE dedi ki:

    Çokzel bir makale olmuş. Elinize sağlık. Teşekkürler 🙂

  2. semih dedi ki:

    örneğiniz güzel anlaşılır fakat
    Parametreli kullanımda SqlParameter kullanıldığında mssql de sorunsuz çalışır.
    peki data base bağımsız bir uygulama geliştiriliyor ise. connection stringe göre sistem kendini mysql veya oracle olarak değiştiriyor ise, yazılı olan stored prosedürleri kullanmak için ne yapmalı?

    oluşturulan stored prosedürler herhangi bir entitiye ait değil, dbde bazı hesaplamalar yapması gerekiyor.tüm db çeşitlerinde o dbye özgü yaratılşmış durumda.

    stored prosedürü öyle bir çağırmalıyız ki db değişikliklerinde hiç bir şey değişmesin.
    fikriniz nedir? yaklaşımımız code first

    • Gençay dedi ki:

      Merhaba Semih,
      Aslında SqlParameter tipinden parametrede alınan hata “olası” bir hatadır. O yüzden alınırsa eğer tavsiye edilen yöntemin uygulanması hatadan kurtulmanıza yardımcı olacaktır.

      Sorduğunuz soruya gelirsek eğer; bu makalede gösterilen Stored Procedure oluşturma yöntemi sanırım SQL Server’a uyumlu bir tarzda yaklaşım sergilemektedir. Eğer ki, kullanılan veritabanına göre Code First yaklaşımıyla S.P. kullanmak istiyorsanız bu konuda en az benim kadar araştırma yapmanız gerekecektir.

      Ayriyetten hangi veritabanı olursa olsun eğer ki System.Data.SqlClient namespacei dışındaki diğer veritabanı kütüphanelerindeki yapılar aşağı yukarı benzerse, S.P. yi çağırmamız aynı şekilde olacaktır kanaatindeyim.
      Sevgiler…

  3. serkan dedi ki:

    Makale çok güzel olmuş elinize sağlık ancak ben bir yerde takıldım, Benim Dbde olan bir procedure’ü bir türlü çeviremedim, yardımcı olursan çok sevinirim..

    CREATE PROCEDURE [dbo].[getCurrencyWithDate] (
    @paramTarih as varchar(20)
    ,@currencyId as int
    )

    AS
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    SET NOCOUNT ON;
    set dateformat ‘dmy’
    declare @tarih as datetime

    declare @newTarih as varchar(50)
    set @newTarih = SUBSTRING(@paramTarih,7,4)+’-‘+SUBSTRING(@paramTarih,4,2)+’-‘+SUBSTRING(@paramTarih,1,2)
    set @tarih =CONVERT(Datetime, @newTarih, 120)

    ;WITH currencyDate
    as
    (
    SELECT @tarih as ‘DateStart’
    UNION ALL
    SELECT DATEADD(day, -1, DateStart) as ‘DateStart’
    FROM currencyDate where not exists (select date from p_exchangeRate where date=currencyDate.DateStart and currencyId = @currencyId)
    )
    select * from p_exchangeRate where date=(select top 1 DateStart from currencyDate order by DateStart) and currencyId = @currencyId
    END

  4. serkan dedi ki:

    Yazmış olduğum Sp’yi bir türlü code first yazmaya çalıştım, ancak Datetime hata veriyor, sizce nerede hata yapıyorum, örnek kod :

    public override void Up()
    {
    CreateStoredProcedure(“getCurrencyWithDate”,
    p => new
    {
    paramTarih = p.DateTime(),
    currencyId = p.Int()
    },
    body:
    @”SET NOCOUNT ON;
    set dateformat ‘dmy’
    declare @tarih datetime
    declare @newTarih varchar(50)
    set @newTarih = SUBSTRING(@paramTarih,7,4)+’-‘+SUBSTRING(@paramTarih,4,2)+’-‘+SUBSTRING(@paramTarih,1,2)
    set @tarih = CONVERT(Datetime, @newTarih, 120)
    ;WITH currencyDate
    as
    (
    SELECT @tarih as’DateStart’
    UNION ALL
    SELECT DATEADD(day, -1, DateStart) as ‘DateStart’
    FROM currencyDate where not exists (select date from p_exchangeRate where date=currencyDate.DateStart and currencyId = @currencyId)
    )
    select * from p_exchangeRate where date=(select top 1 DateStart from currencyDate order by DateStart) and currencyId = @currencyId”
    );
    }

  5. serkan dedi ki:

    Error Number:8116,State:1,Class:16
    Argument data type datetime is invalid for argument 1 of substring function.

    • Gençay dedi ki:

      Yazdığınız Stored Procedure’u Management Studio aracılığıyla direkt olarak SQL Server üzerinde bir deneyiniz. Bakın bakalım orada bir hata verecek mi?

  6. OĞUZHAN SARI dedi ki:

    Merhaba, güzel makale için teşekkürler.

    Ben veri içinde Database i göremiyorum.

    Neden göremiyorum acaba?

    BiCommerceContext.cs dosyası;

    using BiEntities.Models;
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    namespace BiEntities
    {
    public class BiCommerceContext : DbContext
    {
    public BiCommerceContext() : base(“BiCommerceConnect”) { }

    public DbSet Address { get; set; }
    public DbSet ApiIntegration { get; set; }
    public DbSet BankAccount { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    #region Default Value Covnerter
    var Address = modelBuilder.Entity();
    Address.Property(c => c.DateAdd).HasColumnAnnotation(“DefaultValue”, “GETDATE()”);
    Address.Property(c => c.DateLastUpdate).HasColumnAnnotation(“DefaultValue”, “GETDATE()”);
    Address.Property(c => c.IsDelete).HasColumnAnnotation(“DefaultValue”, 0);
    Address.Property(c => c.IsForeign).HasColumnAnnotation(“DefaultValue”, 0);
    Address.Property(c => c.IsCorporate).HasColumnAnnotation(“DefaultValue”, 0);

    var BankAccount = modelBuilder.Entity();
    BankAccount.Property(c => c.DateAdd).HasColumnAnnotation(“DefaultValue”, “GETDATE()”);
    BankAccount.Property(c => c.DateLastUpdate).HasColumnAnnotation(“DefaultValue”, “GETDATE()”);
    BankAccount.Property(c => c.IsDelete).HasColumnAnnotation(“DefaultValue”, 0);
    BankAccount.Property(c => c.IsActive).HasColumnAnnotation(“DefaultValue”, 1);

    }
    }
    }

    Configuration.cs dosyası;
    namespace BiEntities.Migrations
    {
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration
    {
    public Configuration()
    {
    AutomaticMigrationsEnabled = true;
    AutomaticMigrationDataLossAllowed = true;
    }

    protected override void Seed(BiEntities.BiCommerceContext context)
    {
    //base.Seed(context);
    // This method will be called after migrating to the latest version.
    // You can use the DbSet.AddOrUpdate() helper extension method
    // to avoid creating duplicate seed data. E.g.
    //
    // context.People.AddOrUpdate(
    // p => p.FullName,
    // new Person { FullName = “Andrew Peters” },
    // new Person { FullName = “Brice Lambson” },
    // new Person { FullName = “Rowan Miller” }
    // );
    //
    }
    }
    }

    • OĞUZHAN SARI dedi ki:

      Bu sorunu çözdüm.
      Başka bir projede çalıştırdığım için entitiy kurmadığımdan kaynaklanıyormuş.

      Şimdi şöyle bir şey sormak istiyorum.

      SP de değişilik yapınca DB tarafında nasıl güncellenecek.

      Ben değşikliği yapıp update-database yapınca güncelleme yapmıyor.

  7. EVREN dedi ki:

    Ellerinize sağlık sabahlattı bana ama bütün EFCodeFirst makalellerinizi tek tek projelendirdim ve inanılmaz yararlı oldu çalışmalarınızın devamını dilerim şahsım adına teşekkür ediyorum gencay hocam

  8. Ece dedi ki:

    Makale için emeğinize, ellerinize sağlık , yalnız benim hala küçük bir sıkıntım var parametre kullanımıyla ilgili.

    SqlParameter productName = new SqlParameter(“@productName”, SqlDbType.Text,*size);
    productName.Direction = ParameterDirection.Output;
    command.Parameters.Add(productName);

    yukarıdaki gibi tanımladığım kod sütunlarım var SqlDbType.Text iken *size ‘ ı boş bırakınca sizin yukarıda bahsettiğiniz hatanın aynısını alıyorum.
    (An unhandled exception of type ‘System.InvalidOperationException’ occurred in EntityFramework.SqlServer.dll

    Additional information: String[0]: Size özelliğinin 0 olan boyutu geçersiz.)

    İnt.MaxValue gibi bir şey yazdığım zaman da

    “An exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll but was not handled in user code

    Additional information: Invalid parameter 2 (‘@productName’): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.” böyle bir hata alıyorum.
    Nedenini çözemedim. Bir süredir araştırmama rağmen örneğini de bulamadım.
    Yardımcı olabilirseniz çok memnun olurum.

    • Gençay dedi ki:

      Merhaba Ece Hanım,
      Makalede de bahsedildiği gibi ilgili hata alınınca size’ın girilmesi gerekmektedir.

      Başka size nasıl yardımcı olabilirim?

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

*

Copy Protected by Chetan's WP-Copyprotect.