01 czerwca 2011

MARSianie atakują SqlDataReader

Otwarty SqlDataReader


Może się zdarzyć, że kolejność przetwarzania danych będzie wymagała wywoływania różnych procedur/zapytań naprzemiennie.

Załóżmy, że chcemy wyciągnąć listę tabel systemowych z bazy master z tabeli sys.objects. Dla każdej tabeli chcemy pobrać oddzielnym zapytaniem nazwę pierwszej kolumny dla danej tabeli. Oczywiście przykład jest tylko hipotetyczny, gdyż tę informację można wybrać jednym zapytaniem z prostym JOIN'em.

using(var connection = new SqlConnection(
    "Data Source=.;Initial Catalog=master;Integrated Security=true;"))
{
    connection.Open();
    var sysObjectsCmd = new SqlCommand(
        "SELECT object_id, name FROM sys.objects WHERE type = 'S'", 
        connection);
    using(SqlDataReader userTableReader = sysObjectsCmd.ExecuteReader())
    {
        while (userTableReader.Read())
        {
            string select = @"SELECT name 
                FROM sys.columns WHERE Object_id = @objectId";
            var nestedCmd = new SqlCommand(select, connection);
            nestedCmd.Parameters.AddWithValue("objectId", 
                userTableReader["object_id"]);

            object firstColumName = nestedCmd.ExecuteScalar();
            Console.ForegroundColor = ConsoleColor.White;
            var table = string.Format("Tabel {0}",userTableReader["name"]);
            Console.WriteLine(table);
            Console.ForegroundColor = ConsoleColor.Green;
            var column = string.Format("First table column {0}", firstColumName);
            Console.WriteLine(column);
        }
    }
}


Przy wykonaniu tego kodu otrzymamy następujący wyjątek:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.


Jest to spowodowane tym, że przy domyślnyej konfiguracji połączenia nie można mieć odtworzonego SqlDataReadera (w przykładzie userTableReader) i jednoczesnie wywołać ExecuteScalar na kolejnym SqlCommand (w przykładzie nestedCmd).

Ten problem można rozwiązać na kilka sposobów, podam dwa.

  • Użyć MARS (MARS czyli Multiple Active Result Sets), o czym napiszę poniżej
  • Otworzyć osobne połączenie dla wywoływania ExecuteScalar

MARS czyli Multiple Active Result Sets


Microsoft SQL Server od wersji 2005 wspiera wykonywanie kilku operacji w tym samym czasie na jednym połączeniu. Wystarczy w Connection Stringu dodać opcję "MultipleActiveResultSets=True". Należy pamiętać, że polecenia są wywoływane synchronicznie na danym połączeniu do SQL Servera. W scenariuszu gdzie chcemy uzyskać współbieżność należy otwierać nowe połączenie do SQL Servera, oczywiście będzie to bardziej kosztowne.

Czyli w przykładzie poniżej należy zmienić tylko ConnectionString

using(var connection = new SqlConnection(
    "Data Source=.;Initial Catalog=master;Integrated Security=true;MultipleActiveResultSets=True"))
{...}

I otrzymujemy upragnioną listę tabel i kolumn.

Brak komentarzy:

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.