Dapper ile Stored Procedure: Output, Return Value ve 2.1.11'in Otomatik Algılama Tuzağı

Sürüm notu: Bu yazıdaki kodlar Dapper 2.1 ve .NET 10 içindir.

Önce Tahmin Et

SQL Server'daki Bookstore veritabanında dbo.CountBooks adında, kitap sayısını döndüren bir stored procedure var. Aşağıdaki iki çağrının hiçbirinde commandType belirtmedik:

using var conn = new SqlConnection(connectionString);

var total = conn.ExecuteScalar<int>("dbo.CountBooks");
Console.WriteLine(total);

conn.Execute("checkpoint");

İlk satır bir procedure adı, ikincisi ise SQL Server'ın bildiğin CHECKPOINT command'ı. Hangisi çalışır? İkisi de mi, hiçbiri mi, yoksa sadece biri mi?

4
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'checkpoint'.

Procedure adını vermek yetti, Dapper onu procedure olarak çağırdı. Sıradan bir T-SQL command'ı ise "böyle bir stored procedure yok" hatasıyla düştü. İkisinin sebebi aynı: Dapper 2.1.11'den beri boşluk içermeyen command metnini stored procedure kabul ediyor. Bu yazıda stored procedure'ları baştan sona ele alıp bu davranışa ayrı bir bölüm ayıracağız.

SQLite'tan Neden Ayrılıyoruz?

Serinin şimdiye kadarki bütün örnekleri sıfır kurulumlu SQLite ile çalıştı. Bu yazıda bunu bozmak zorundayız, çünkü SQLite'ta stored procedure diye bir şey yok. Provider bunu açıkça söylüyor:

using var sqlite = new SqliteConnection("Data Source=:memory:");
sqlite.Open();

sqlite.Execute("analyze");

Unhandled exception. System.ArgumentException: The CommandType 'StoredProcedure' is not supported.

Biz procedure çağırmak istemedik bile; analyze tek kelime olduğu için Dapper onu procedure sandı ve Microsoft.Data.Sqlite bu command tipini reddetti. Yani otomatik algılama SQLite'ta da seni bulabilir. Şimdilik bu kadarı yeter, asıl konuya geçelim.

Gerçek bir SQL Server'a ihtiyacımız var ve en kolay yol docker. Apple Silicon bir Mac'te bu imaj emülasyonla çalışır, biraz yavaştır ama iş görür:

docker run -d --name bookstore-sql -e ACCEPT_EULA=Y -e "MSSQL_SA_PASSWORD=Bookstore_Pass1" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
docker run -d --name bookstore-pg -e POSTGRES_PASSWORD=bookstore -e POSTGRES_DB=bookstore -p 5432:5432 postgres:17-alpine
dotnet add package Microsoft.Data.SqlClient --version 7.1.1
dotnet add package Npgsql --version 10.0.3

Provider olarak Microsoft.Data.SqlClient kullanıyoruz. Eski System.Data.SqlClient paketi NuGet'te artık "deprecated" işaretli; yeni bir projede ona dönmek için hiçbir sebep yok. Örnekleri SQL Server 2022 (CU24) ve PostgreSQL 17 üzerinde çalıştırdım.

İlk Stored Procedure Çağrısı

Bookstore şemasının SQL Server hâli ve ilk procedure'ımız:

create table dbo.authors (id int identity primary key, name nvarchar(100) not null);
create table dbo.books (id int identity primary key, title nvarchar(200) not null,
    author_id int not null references dbo.authors(id), price decimal(10,2) not null);
insert dbo.authors (name) values (N'Frank Herbert'), (N'Ursula K. Le Guin');
insert dbo.books (title, author_id, price) values
    (N'Dune', 1, 12.50), (N'Dune Messiah', 1, 10.00),
    (N'The Left Hand of Darkness', 2, 11.00), (N'The Dispossessed', 2, 13.00);

create or alter procedure dbo.GetBooksByAuthor
    @AuthorId int
as
begin
    set nocount on;
    select id, title, price
    from dbo.books
    where author_id = @AuthorId
    order by id;
end

Dapper tarafında değişen tek şey commandType. SQL yerine procedure adını veriyoruz, parameter'ları yine anonymous object ile geçiyoruz:

var connectionString = "Server=localhost,1433;Database=Bookstore;User Id=sa;Password=Bookstore_Pass1;TrustServerCertificate=True";
using var conn = new SqlConnection(connectionString);

var books = conn.Query<BookRow>(
    "dbo.GetBooksByAuthor",
    new { AuthorId = 2 },
    commandType: CommandType.StoredProcedure);

foreach (var b in books)
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

public record BookRow(int Id, string Title, decimal Price);

3 | The Left Hand of Darkness | 11.00
4 | The Dispossessed | 13.00

Property adı AuthorId, procedure'daki parameter @AuthorId. Başına @ koymana gerek yok; SqlClient procedure çağrısında parameter'ları adlarıyla eşler. Bu yüzden sıra önemli değil, ama adın birebir doğru olması önemli. Fazladan bir property gönderirsen procedure "too many arguments" hatası verir.

Output Parameter'lar

Yeni bir kitap ekleyip üretilen id'yi output parameter ile geri veren procedure:

create or alter procedure dbo.AddBook
    @Title nvarchar(200),
    @AuthorId int,
    @Price decimal(10, 2),
    @BookId int output
as
begin
    set nocount on;
    insert dbo.books (title, author_id, price) values (@Title, @AuthorId, @Price);
    set @BookId = cast(scope_identity() as int);
end

Anonymous object bir parameter'ın yönünü söyleyemez. Burada DynamicParameters devreye giriyor: direction ile parameter'ı output olarak işaretliyor, çağrıdan sonra değeri Get<T> ile okuyoruz:

var p = new DynamicParameters();
p.Add("Title", "Children of Dune");
p.Add("AuthorId", 1);
p.Add("Price", 11.75m);
p.Add("BookId", dbType: DbType.Int32, direction: ParameterDirection.Output);

conn.Execute("dbo.AddBook", p, commandType: CommandType.StoredProcedure);

Console.WriteLine(p.Get<int>("BookId"));

5

Output parameter'ı bir zarf gibi düşün: boş zarfı procedure'a veriyorsun, procedure içine bir şey koyup geri yolluyor. Zarfın ne kadar büyük olduğunu da baştan söylemen gerekiyor. Sayılarda bu tipten belli, string'lerde ise değil:

create or alter procedure dbo.GetAuthorName
    @AuthorId int,
    @Name nvarchar(100) output
as
begin
    set nocount on;
    select @Name = name from dbo.authors where id = @AuthorId;
end

var p = new DynamicParameters(new { AuthorId = 2 });
p.Add("Name", dbType: DbType.String, direction: ParameterDirection.Output);

conn.Execute("dbo.GetAuthorName", p, commandType: CommandType.StoredProcedure);
Console.WriteLine(p.Get<string>("Name"));

Unhandled exception. System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.

Hata SqlClient'tan geliyor: output string için bir uzunluk vermediğimiz için SqlClient parameter'ı göndermeyi reddediyor. size: 100 eklediğinde, yani kolonun nvarchar(100) tanımıyla aynı değeri verdiğinde, çıktı Ursula K. Le Guin olur. DynamicParameters(new { AuthorId = 2 }) kullanımına da dikkat: girdi parameter'larını anonymous object ile verip yalnızca output'u Add ile eklemek kodu kısaltır.

Return Value

Her stored procedure bir tamsayı döndürebilir. Genelde "durum kodu" olarak kullanılır: başarı, bulunamadı, iş kuralı ihlali gibi:

create or alter procedure dbo.DiscountBooks
    @AuthorId int,
    @Percent int
as
begin
    set nocount on;
    if not exists (select 1 from dbo.authors where id = @AuthorId)
        return -1;

    update dbo.books
    set price = price * (100 - @Percent) / 100.0
    where author_id = @AuthorId;

    return @@rowcount;
end

Return value'yu okumak için ParameterDirection.ReturnValue yönünde bir parameter ekliyoruz. Adı tamamen keyfi, çünkü procedure tarafında bir karşılığı yok:

foreach (var authorId in new[] { 1, 99 })
{
    var p = new DynamicParameters(new { AuthorId = authorId, Percent = 10 });
    p.Add("Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

    var affected = conn.Execute("dbo.DiscountBooks", p, commandType: CommandType.StoredProcedure);
    Console.WriteLine($"author {authorId}: return = {p.Get<int>("Result")}, Execute = {affected}");
}

author 1: return = 3, Execute = -1
author 99: return = -1, Execute = -1

Frank Herbert'in üç kitabı indirime girdi, olmayan yazar için procedure -1 döndü. Ama Execute'un kendi dönüşüne bak: iki durumda da -1. Etkilenen satır sayısını sunucu bildirmiyor, çünkü procedure'ın ilk satırı set nocount on. Buna tuzaklar bölümünde döneceğiz.

Result Set ve Output Birlikte

Sayfalı listelerde sık görülen bir desen: procedure hem bir sayfa satır döndürür hem de toplam kayıt sayısını output'a yazar.

create or alter procedure dbo.GetBookPage
    @Skip int,
    @Take int,
    @Total int output
as
begin
    set nocount on;
    select @Total = count(*) from dbo.books;

    select id, title, price
    from dbo.books
    order by id
    offset @Skip rows fetch next @Take rows only;
end

var p = new DynamicParameters(new { Skip = 0, Take = 2 });
p.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output);

var page = conn.Query<BookRow>("dbo.GetBookPage", p, commandType: CommandType.StoredProcedure);
Console.WriteLine($"{page.Count()} of {p.Get<int>("Total")}");

2 of 5

Bu buffered sorguda sorunsuz çalıştı. Kritik bilgi şu: TDS protokolünde output değerleri result set'lerden sonra gelir. Buffered Query bütün satırları okuyup reader'ı kapattığı için Get çağrıldığında değer hazırdır. 10. yazıdaki unbuffered okumaya geçersen tablo değişir:

var stream = conn.Query<BookRow>("dbo.GetBookPage", p,
    commandType: CommandType.StoredProcedure, buffered: false);

foreach (var b in stream)
{
    Console.WriteLine(b.Title);
    Run(() => Console.WriteLine(p.Get<int>("Total")));
}
Console.WriteLine(p.Get<int>("Total"));

static void Run(Action action)
{
    try { action(); }
    catch (Exception ex) { Console.WriteLine(ex.GetType() + ": " + ex.Message); }
}

Dune
System.NullReferenceException: Object reference not set to an instance of an object.
Dune Messiah
System.NullReferenceException: Object reference not set to an instance of an object.
5

Döngü içinde değer henüz yok, döngüden sonra var. Dapper'ın kaynak kodunda bu durum için açıklayıcı bir mesaj bile var ("out/return parameters will not have updated values until the data stream completes"), ama o mesaj değer DBNull olduğunda fırlatılıyor. SqlClient okuma sürerken output'u null bıraktığı için sen çıplak bir NullReferenceException görüyorsun. QueryMultiple için de aynı kural geçerli: output değerlerini bütün grid'ler okunduktan sonra iste.

2.1.11'in Sürprizi: Otomatik Algılama

Açılıştaki tahmine dönelim. Dapper, commandType verilmediğinde command metnine bakıp tipini kendisi seçer. 2.1.89'un kaynak kodundaki kural kısaca şöyle: metinde boşluk karakteri (Unicode boşlukları dahil) ya da ;, /, -, +, * varsa ya da metin büyük-küçük harf fark etmeden tam olarak vacuum, commit, rollback veya revert ise command metin olarak gider. Geri kalan her şey stored procedure kabul edilir.

Bu yüzden "dbo.CountBooks" ve "[dbo].[CountBooks]" procedure olarak çağrıldı, "checkpoint" de. İstisna listesi 2.1.15 ve 2.1.21'de genişledi, REVERT 2.1.35'te eklendi; yani bu liste insanların gerçekten tökezlediği command'lardan oluşuyor. Senin kullandığın tek kelimelik command listede yoksa çözüm basit, tipi açıkça söylemek:

conn.Execute("checkpoint", commandType: CommandType.Text);

Benim kuralım şu: procedure çağırırken commandType: CommandType.StoredProcedure'ı her zaman açıkça yaz. Algılama bir kolaylık, ama kod okuyan birine "bu bir procedure" dediğin tek yer o parametre. Command metnini dinamik olarak üretiyorsan, örneğin bir yönetim ekranından gelen bakım command'larını çalıştırıyorsan, CommandType.Text'i de açıkça yaz.

PostgreSQL: Function ile Procedure Aynı Şey Değil

SQL Server'dan PostgreSQL'e geçen birinin ilk sürprizi burada. PostgreSQL'de veri döndüren rutinler genelde function'dır; procedure'lar 11. sürümde geldi, CALL ile çağrılır ve result set döndürmez. Bookstore'un PostgreSQL hâlinde bir function ve bir procedure tanımlayalım (Npgsql 10.0.3):

create function get_books_by_author(p_author_id int)
returns table (id int, title text, price numeric)
language sql
as $$
    select b.id, b.title, b.price from books b where b.author_id = p_author_id order by b.id;
$$;

create procedure add_book(p_title text, p_author_id int, p_price numeric, inout p_book_id int default null)
language plpgsql
as $$
begin
    insert into books (title, author_id, price) values (p_title, p_author_id, p_price)
    returning id into p_book_id;
end;
$$;

SQL Server alışkanlığıyla function'ı CommandType.StoredProcedure ile çağırırsak:

var pgConnectionString = "Host=localhost;Port=5432;Username=postgres;Password=bookstore;Database=bookstore";
using var pg = new NpgsqlConnection(pgConnectionString);

var books = pg.Query<BookRow>(
    "get_books_by_author",
    new { p_author_id = 2 },
    commandType: CommandType.StoredProcedure);

Unhandled exception. Npgsql.PostgresException (0x80004005): 42883: procedure get_books_by_author(p_author_id => integer) does not exist

Npgsql 7'den beri CommandType.StoredProcedure bir CALL üretir, yani yalnızca gerçek procedure'lar için çalışır. Eski davranışı AppContext.SetSwitch("Npgsql.EnableStoredProcedureCompatMode", true) ile geri açabilirsin, ben denedim, çalışıyor. Ama doğal olan function'ı bir tablo gibi sorgulamak:

var books = pg.Query<BookRow>(
    "select * from get_books_by_author(@AuthorId)",
    new { AuthorId = 2 });

foreach (var b in books)
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

3 | The Left Hand of Darkness | 11.00
4 | The Dispossessed | 13.00

Procedure'ın inout parameter'ı ise Dapper tarafında InputOutput yönüne karşılık geliyor:

var p = new DynamicParameters();
p.Add("p_title", "Children of Dune");
p.Add("p_author_id", 1);
p.Add("p_price", 11.75m);
p.Add("p_book_id", dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

pg.Execute("add_book", p, commandType: CommandType.StoredProcedure);
Console.WriteLine(p.Get<int>("p_book_id"));

5

Sık Düşülen Tuzaklar

Execute'un dönüşüne güvenmek. set nocount on içeren bir procedure'da etkilenen satır sayısı istemciye gelmez ve Execute -1 döner. Aynı update'i yapan iki procedure'la denedim:

Console.WriteLine(conn.Execute("dbo.RenameAuthor",
    new { AuthorId = 1, Name = "Frank Herbert" }, commandType: CommandType.StoredProcedure));
Console.WriteLine(conn.Execute("dbo.RenameAuthorQuiet",
    new { AuthorId = 1, Name = "Frank Herbert" }, commandType: CommandType.StoredProcedure));

1
-1

İkincisinin tek farkı set nocount on. Etkilenen satır sayısı iş kuralı için önemliyse onu return value ya da output parameter ile açıkça döndür; nocount'u kapatmak gereksiz ağ trafiği demek.

Output string'lere size vermemek ya da yanlış vermek. Sıfır boyut hata verir; kolondan kısa bir boyut ise değeri sessizce keser. Boyutu procedure'daki tanımla aynı tut.

Output'u erken okumak. Unbuffered sorguda ya da QueryMultiple'da bütün satırlar okunmadan Get çağırırsan SqlClient'ta NullReferenceException alırsın. Okuma bitince iste.

Fazladan property göndermek. Procedure'lar SQL metni gibi kullanılmayan parameter'ları yok saymaz. Bir DTO'yu doğrudan param olarak verirsen procedure'da karşılığı olmayan her property bir "too many arguments" hatasına dönüşür. Ya amaca özel bir anonymous object kur ya da DynamicParameters'ı elle doldur.

EF Core ile karşılaştırma için bir not: EF Core'da procedure'ı FromSql ya da ExecuteSql ile çağırırsın ve output parameter için provider'ın kendi SqlParameter'ına inmen gerekir. Dapper'da aynı iş DynamicParameters ile provider'dan bağımsız kalıyor. EF Core serisinin 15. yazısı o tarafı anlatıyor.

Küçük Bir Deneme

★☆☆ dbo.GetBooksByAuthor'a @MaxPrice decimal(10,2) = null diye opsiyonel bir parameter ekle. Dapper'dan bu parameter'ı hiç göndermeden ve null göndererek iki çağrı yap, sonuçların neden aynı olduğunu düşün.

★★☆ dbo.AddBook'u kitap başlığı zaten varsa return 1, eklediyse return 0 döndürecek şekilde değiştir. Aynı çağrıda hem output'u hem return value'yu oku ve iki durumu da ekrana yazdır.

★★★ CommandDefinition'ı commandType vermeden "sp_who", "VACUUM", "select 1" ve "dbo.CountBooks--" metinleriyle oluşturup CommandType property'sini yazdır. Hangisinin hangi tipe düştüğünü kuralı okumadan önce tahmin et.

SQL Server'a gelmişken bırakmayalım: bir sonraki yazıda yine docker'daki SQL Server üzerinde table-valued parameter'ları, ANSI string'lerin index'lere etkisini, {=literal} değiştirmeyi ve parameter sniffing ile IN listelerinin plan cache'e ne yaptığını inceleyeceğiz.