SQL Serwer umożliwia zdefiniowanie procedury składowanej, której dowolny parametr (lub parametry) może służyć do zwracania jakichś informacji w ramach tej procedury uzyskanych. Oprócz tego procedura może zwracać także jakiś zestaw wyników w postaci tabelarycznej (wystarczy w jej treści wykonać select), co więcej takich zestawów może być więcej niż jeden. Połączenie tych dwóch cech pozwala tworzyć rozbudowane procedury, które mogą zwracać wiele zestawów wyników i dodatkowo jakieś informacje w parametrach wyjściowych.

Można sobie np. wyobrazić, że w zależności od parametru Task – czyli zadania, jakie ma wykonać procedura składowana, zwróci ona różną liczbę zestawów wyników (jedną, dwie, trzy, itd. tabele). Jednocześnie liczba tych tabel będzie określona w parametrze wyjściowym Count. Dzięki temu będzie można z góry określić ile tabel powinniśmy uzyskać. Prawda, że zgrabny tandemik się z tego zrobił – wykorzystano obie opisane możliwości i zaprzęgnięto je do współpracy.

Niestety, to co w teorii wygląda nieźle w praktyce okazuje się … awykonalne. Jeśli bowiem zechcemy skorzystać z DataReadera, okaże się, że parametr wyjściowy nie jest ustalony. Trzeba dopiero zamknąć DataReadera, aby móc odczytać parametr. Więc nie dowiemy się ile będzie zestawów wyników? Stosując taki mechanizm – nie. Dowiemy się o tym już po fakcie, czyli w momencie kiedy pobierzemy wszystkie zestawy. To samo będzie z jakimkolwiek innym zastosowaniem parametrów wyjściowych – będzie to ostatnia informacja jaką uda nam się uzyskać. To zachowanie opisane jest w dokumentacji SqlDataReadera. Dlaczego tak dzieje i jak sobie z tym poradzić?

Zacznę najpierw od odpowiedzi na drugie pytania. Zamiast zwracać liczbę zestawów w parametrze, należy ją zwrócić … jako pierwszy zestaw. Po prostu trzeba zrobić tak:

select @Count as RecordSetCount;
-- pozostałe selecty

odczytać ten jednowierszowy i jednokolumnowy zestaw, i użyć przekazanej w nim ilości. Tak samo można zrobić z jakąkolwiek inną informacją. Dopiero po tym select’cie należy wykonywać pozostałe.

Aby uzyskać odpowiedź na pytanie dlaczego tak się dzieje, należy uświadomić sobie, w jaki sposób ADO.NET uzyskuje wartości parametrów wyjściowych. Otóż robi to wykonując … select! Wygląda to mniej więcej tak:

declare @p3 int;
exec dbo.Task @Name = N'SingleResult', @Count = @p3 output;
select @p3;

Parametry są więc de facto ostatnim zwracanym zestawem wyników – dlatego nie jest możliwe ich odczytanie dopóki DataReader nie zakończył swoich działań. Skoro jednak jest to zestaw wyników, to być może uzyskanie dostępu do nich będzie możliwe poprzez zastosowaniu metody NextResult DataReadera? Wszak gdyby zacząć przetwarzać wszystkie zwracane zestawy, wówczas dotrze się także do zestawu parametrów wyjściowych. Na szczęście nie będzie możliwe ich odczytanie przez DataReadera (w sensie zinterpretowania ich jako kolejnego zestawu wyników), ale działanie to „uwolni” de facto parametry i będzie można je odczytać nie zamykając DataReadera.

Oto przykład: należy przygotować następującą procedurę na serwerze SQL:

if not exists (select * from sys.objects where name = 'OutputParametersTest')
	exec dbo.sp_executesql N'create procedure dbo.OutputParametersTest as return 0';
GO
alter procedure dbo.OutputParametersTest
	@In varchar(255),
	@Out varchar(255) output
as
begin
	-- prezentacja zawartości parametrów przed ich przetworzeniem
	select @In as [In], @Out as [Out];
	-- przetworzenie parametru
	set @Out = @In;
	-- prezentacja zawartości parametrów po ich przetworzeniu
	select @Out as [ModifiedOut];
	-- ostateczne przetworzenie parametru
	set @Out = 'Przekazano: "' + @Out + '"';
end;
GO

I następujący projekt konsolowy w Visual Studio (ConnectionString należy dostosować do nazwy posiadanego serwera i bazy danych, w której utworzono procedurę OutputParametersTest):

class Program
{
	static void Main(string[] args)
	{
		SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS2K8;Initial Catalog=Test;Integrated Security=SSPI;");
		SqlCommand command = new SqlCommand("dbo.OutputParametersTest", connection);
		command.CommandType = CommandType.StoredProcedure;
		
		command.Parameters.AddWithValue("@Success", -1).Direction = ParameterDirection.ReturnValue;
		MakeParameter(command, "@In", SqlDbType.VarChar, ParameterDirection.Input, 255, "Do przekazania na wyjście");
		MakeParameter(command, "@Out", SqlDbType.VarChar, ParameterDirection.InputOutput, 255, "Bądź gotowy na wejście");

		Show(command);
		connection.Open();
		using (IDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
		{
			Line();
			Show(dr);
			Line();
			Console.WriteLine();
			Show(command);
		}

		Show(command);
		Console.ReadLine();
	}

	static private void Line()
	{
		Console.WriteLine("-----------------------------------------------------------------------");
	}

	static private void Show(SqlCommand command)
	{
		Console.WriteLine("rezultat:\t" + command.Parameters["@Success"].Value);
		Console.WriteLine("     @In:\t" + command.Parameters["@In"].Value);
		Console.WriteLine("    @Out:\t" + command.Parameters["@Out"].Value);
		Console.WriteLine();
	}

	static private void Show(IDataReader dr)
	{
		bool exists = false;
		while (dr.Read())
		{
			exists = true;
			for (int i = 0; i < dr.FieldCount; i++)
				Console.Write("{0}\t", dr[i]);
			Console.WriteLine();
		}
		if (!exists)
			Console.WriteLine("Brak wyników.");
	}

	static private void MakeParameter(SqlCommand cmd, string Name, SqlDbType type, ParameterDirection pd, int size, string value)
	{
		SqlParameter p = cmd.Parameters.Add(Name, type);
		if (size > 0) p.Size = size;
		p.Direction = pd;
		p.Value = value;
	}
}

Wykonanie programu da następujący wynik:

rezultat:       -1
     @In:       Do przekazania na wyjście
    @Out:       Bądź gotowy na wejście

-----------------------------------------------------------
Do przekazania na wyjście       Bądź gotowy na wejście
-----------------------------------------------------------

rezultat:       -1
     @In:       Do przekazania na wyjście
    @Out:       Bądź gotowy na wejście

rezultat:       0
     @In:       Do przekazania na wyjście
    @Out:       Przekazano: "Do przekazania na wyjście"

Pomiędzy przerywanymi liniami widoczny jest pierwszy zwrócony przez procedurę OutputParametersTest zestaw wyników, następnie widoczne są wartości parametrów uzyskane w ramach bloku using() – takie same jak przed wywołaniem procedury (czyli nic się nie zmieniło). Tuż pod nimi widoczne są wartości parametrów już po zamknięciu DataReadera (zmienione przez procedurę).

Teraz należy dokonać modyfikacji bloku using() w metodzie Main, w następujący sposób:

using (IDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
{
	Line();
	Show(dr);
	dr.NextResult();
	Line();
	Show(dr);
	dr.NextResult();
	Line();
	Show(dr);
	Line();
	Console.WriteLine();
	Show(command);
}

Wykonanie programu da nowy wynik:

rezultat:       -1
     @In:       Do przekazania na wyjście
    @Out:       Bądź gotowy na wejście

-----------------------------------------------------------
Do przekazania na wyjście       Bądź gotowy na wejście
-----------------------------------------------------------
Do przekazania na wyjście
-----------------------------------------------------------
Brak wyników.
-----------------------------------------------------------

rezultat:       0
     @In:       Do przekazania na wyjście
    @Out:       Przekazano: "Do przekazania na wyjście"

rezultat:       0
     @In:       Do przekazania na wyjście
    @Out:       Przekazano: "Do przekazania na wyjście"

Pomiędzy przerywanymi liniami widać teraz trzy zestawy wyników, z czego dwa pierwsze są zwrócone przez procedurę, a ostatni jest pusty (czyli parametrów nie udało się odczytać z poziomu DataReadera). To co teraz jest natomiast widoczne, to wartości parametrów zgodne z tymi zwróconymi z procedury składowanej. Są to parametry uzyskane w ramach bloku using(). Tuż pod nimi – tak jak poprzednio – widoczne są wartości parametrów już po zamknięciu DataReadera.

Na zakończenie jedynie podkreślę, że jest to efekt uboczny i stosowanie go w celach produkcyjnych jest niewskazane (by nie rzec – złe), pokazuje on jednak, że parametry rzeczywiście są pobierane przez ADO.NET w opisany przeze mnie sposób.