Monthly Archives: Kwiecień 2012

Efektywne ładowanie danych do bazy – klasa SqlBulkCopy

2012-04-26 

Od .NET 2.0 dostępna jest klasa System.Data.SqlClient.SqlBulkCopy, która pozwala na efektywne ładowanie dużych zbiorów danych do bazy SQL Server. Główną zaletą tej klasy jest wydajność operacji kopiowania danych porównywalna z instrukcją bulk insert dostępną z poziomu języka T-SQL. Bezpośrednim źródłem danych (przekazywanym do metody WriteToServer) może być tablica obiektów DataRow, obiekt DataTable lub obiekt implementujący interfejs IDataReader. Parametry operacji kopiowania ustawiamy poprzez następujące właściwości:

  • BatchSize – liczba rekordów w paczce danych (dane do bazy wysyłane są paczkami)
  • BulkCopyTimeout – maksymalny czas wykonywania operacji podany w sekundach
  • ColumnMappings – kolekcja obiektów SqlBulkCopyColumnMapping, które definiują mapowanie kolumn źródła danych na kolumny docelowej tabeli w bazie danych
  • DestinationTableName – nazwa docelowej tabeli w bazie danych
  • NotifyAfter – liczba rekordów, po przetworzeniu których wywoływane jest zdarzenie SqlRowsCopied

Konstruktor klasy SqlBulkCopy oprócz obiektu SqlConnection lub connection stringa do docelowej bazy danych może przyjąć także obiekt SqlBulkCopyOptions pozwalający na określenie dodatkowych opcji. Jest to enum z następującymi elementami:

  • Default – domyślne ustawienia wszystkich opcji
  • CheckConstraints – włączenie sprawdzania constraintów, domyślnie nie są sprawdzane
  • FireTriggers – wywoływanie triggerów po załadowaniu każdej paczki danych, domyślnie triggery nie są wywoływane
  • KeepIdentity – wstawianie w kolumnę ustawioną jako Identity wartości ze źródła danych (analogicznie do instrukcji T-SQL: SET IDENTITY_INSERT ON), domyślnie wartości źródłowe zastępowane są numeratorem kolumny
  • KeepNulls – wstawianie wartości NULL ze źródła w kolumnę z wartością Default, domyślnie w przypadku NULL-a wstawiane są wartości Default
  • TableLock – blokowanie tabeli na czas ładowania danych, domyślnie blokady zakładane są na rekordy
  • UseInternalTransaction – ładowanie każdej paczki danych w transakcji

Działanie omawianej klasy pokażę na przykładzie własnej metody wstawiającej dane z pliku tekstowego do tabeli w bazie SQL Server. Metoda jako parametry przyjmuje obiekt SqlConnection do bazy danych, nazwę tabeli docelowej oraz ścieżkę do pliku źródłowego. W wyniku zwraca ilość załadowanych rekordów. Zakładam, że wartości w pliku rozdzielane są tabulacją. Metoda na początku odczytuje dane z pliku i umieszcza je w obiekcie DataTable, następnie poprzez metodę WriteToServer ładuje dane z obiektu DataTable do bazy. W tym przykładzie nie użyłem właściwości ColumnMappings co oznacza, że kolumny ze źródła mapowane są na odpowiadające im w kolejności kolumny tabeli docelowej. Dodatkowo ustawiłem opcje obiektu SqlBulkCopy na KeepIdentity i FireTriggers.

private int BulkCopy(SqlConnection sqlConnection, string destinationTable, string fileName)
{
    DataTable sourceTable = new DataTable();
    string fileLine;
    string[] rowData;
    bool firstRow = true;
    int recordCount = 0;
    try
    {
        using (System.IO.StreamReader sr = System.IO.File.OpenText(fileName))
        {
            while ((fileLine = sr.ReadLine()) != null)
            {
                rowData = fileLine.Split('\t');
                if (rowData.Length > 0)
                {
                    if (firstRow)
                    {
                        foreach (var item in rowData)
                        {
                            sourceTable.Columns.Add(new DataColumn());
                        }
                        firstRow = false;
                    }
                    DataRow row = sourceTable.NewRow();
                    row.ItemArray = rowData;
                    sourceTable.Rows.Add(row);
                }
            }
        }

        SqlBulkCopyOptions bulkOptions =
            SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers;

        using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(sqlConnection.ConnectionString, bulkOptions))
        {
            bulkCopy.DestinationTableName = destinationTable;
            bulkCopy.BulkCopyTimeout = 300;
            bulkCopy.BatchSize = 1000;
            bulkCopy.WriteToServer(sourceTable);
            recordCount = sourceTable.Rows.Count;
        }
    }
    catch (Exception e)
    {
        //Obsługa błędów
    }
    return recordCount;
}

Kilka uwag odnośnie stosowania klasy SqlBulkCopy:

  1. W przypadku kopiowania danych z jednej bazy do tabeli w innej bazie, jako źródło danych najlepiej przekazać obiekt SqlDataReader zwracany przez metodę SqlCommand.ExecuteReader.
  2. W przypadku domyślnego mapowania pól, źródło może mieć mniej kolumn niż tabela docelowa (wówczas w dodatkowe kolumny wstawione zostaną wartości domyślne lub NULL), większa liczba kolumn w źródle niż w tabeli docelowej nie jest dopuszczalna.
  3. Podczas próby ładowania danych może pojawić się następujący błąd:
    "Bulk copy failed. User does not have ALTER TABLE permission on table 'DestinationTable'. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the bulk copy command."
    Wystąpi on w sytuacji gdy docelowa tabela posiada triggery lub constrainty, a użytkownik, w kontekście którego łączymy się z bazą nie ma uprawnień ALTER TABLE wymaganych do wyłączenia ich działania. Próba ich wyłączenia wynika z domyślnych ustawień SqlBulkCopyOptions. Rozwiązaniem jest albo nadanie użytkownikowi odpowiednich uprawnień do tabeli (jeżeli nie chcemy wywoływać triggerów lub constraintów), albo ustawienie opcji CheckConstraints i/lub FireTriggers (wówczas triggery lub constrainty będą działały).
  4. Z mojego doświadczenia wynika, że ustawienie opcji UseInternalTransaction nie ma żadnego znaczenia. Zawsze w przypadku błędu podczas kopiowania wszystkie dane załadowane we wcześniejszych paczkach pozostają w tabeli, dane z bieżącej paczki są anulowane, a proces dalszego kopiowania jest przerywany. Nawet MSDN podaje identyczne zachowanie w obu przypadkach:
    "Performing a Non-transacted Bulk Copy Operation:
    In this first example, the bulk copy operation is non-transacted. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.
    Performing a Dedicated Bulk Copy Operation in a Transaction:
    In this example, the bulk copy operation manages its own transactions. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches."

Więcej o klasie SqlBulkCopy: MSDN

Reklamy

Nowości w SQL Server 2012 – stronicowanie wyników (OFFSET, FETCH)

2012-04-22

W SQL Server aż do najnowszej wersji brakowało mechanizmu pozwalającego na stronicowanie wyników zapytań. Programiści radzili sobie z tym problemem wykorzystując dostępne konstrukcje (klauzula TOP, funkcja RowNumber(), tabela tymczasowa lub CTE) ale nie było gotowego rozwiązania. W najnowszej wersji języka T-SQL pojawiły się polecenia OFFSET i FETCH, które umożliwiają wygodne stronicowanie danych zwracanych przez zapytanie. Powyższe polecenia umieszczane są po klauzuli ORDER BY, która w ich przypadku jest wymagana. Składnia:

OFFSET offset_value { ROW | ROWS } 
[FETCH { FIRST | NEXT } fetch_value { ROW | ROWS } ONLY] 

offset_value – ilość rekordów do pominięcia
fetch_value – ilość rekordów do pobrania

Kilka uwag:

  1. Wartości dla OFFSET i FETCH  możemy podawać jako liczba, zmienna typu int lub zapytanie zwracające wartość typu int;
  2. Słów kluczowych ROW i ROWS możemy używać zamiennie, to samo dotyczy słów FIRST i NEXT;
  3. Polecenie FETCH jest opcjonalne, bez niego zwrócone zostaną rekordy od wartości OFFSET + 1 do końca zbioru;

Żeby zobaczyć działanie omawianych poleceń musimy przygotować dane testowe:

--Utworzenie tabeli tymczasowej
create table #Test(Id int Identity(101, 1), Name varchar(20))

--Wstawienie 12 wierszy ("go 12" pozwala w Management Studio wykonać daną instrukcję dowolną ilość razy, w tym przypadku 12)
insert into #Test(Name)
select 'Name_' + Cast(DatePart(ms, GetDate()) as varchar(3))
go 12

select * from #Test

Oto zawartość tabeli #Test:

Poniższe zapytanie pomija pierwsze 6 rekordów (OFFSET 6 ROWS) i pobiera kolejne 4 rekordy (FETCH NEXT 4 ROWS ONLY):

select Id, Name
from #Test
order by Id
offset 6 rows
fetch next 4 rows only

Oto wynik (pominięte rekordy o Id 101-106 i pobrane rekordy o Id 107-110):

Oczywiście bezpośrednie podawanie wartości OFFSET i FETCH jest mało elastyczne, poniżej przykład prostego mechanizmu stronicowania w oparciu o zmienne:

--Numer strony do pobrania
declare @PageNumber int = 2
--Ilość rekordów na stronie
declare @PageSize int = 3

select Id, Name
from #Test
order by Id
offset ((@PageNumber - 1) * @PageSize) rows
fetch next @PageSize rows only

Oto wyniki, pobrana druga strona przy trzech rekordach na stronie:

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Leniwa inicjalizacja obiektów – klasa Lazy

2012-04-21 

W .NET 4 pojawiła się nowa klasa System.Lazy<T>. Opakowuje ona obiekt danego typu, dzięki czemu jego inicjalizacja następuje dopiero w momencie pierwszego użycia. Jeżeli nie odwołamy się do obiektu opakowanego klasą Lazy pozostanie on niezainicjalizowany.  Ma to zastosowanie w przypadku obiektów, których utworzenie wymaga zwiększonego zapotrzebowania na zasoby, zwłaszcza gdy może wystąpić sytuacja, iż obiekt ten nie zostanie użyty. Klasa Lazy posiada dwie właściwości:

  • IsValueCreated – informacja czy opakowany obiekt został utworzony
  • Value – odwołanie do opakowanego obiektu (typ <T>)

Przykład 1:

//Opakowanie obiektu typu string
//Argumentem konstruktora klasy Lazy jest funkcja inicjalizująca obiekt
Lazy<string> MyLazyString = new Lazy<string>(() => "Lazy test");

//Sprawdzenie czy obiekt został utworzony, wynik: FALSE
MessageBox.Show(MyLazyString.IsValueCreated.ToString());

//Odwołanie do obiektu
this.Text = MyLazyString.Value;

//Ponowne sprawdzenie czy obiekt został utworzony, wynik: TRUE
MessageBox.Show(MyLazyString.IsValueCreated.ToString());

Przykład 2:

//Opakowanie obiektu typu Label
Lazy<Label> MyLazyLabel = new Lazy<Label>(() =>
    {
        Label MyLabel = new Label();
        MyLabel.Parent = this;
        MyLabel.Text = "Lazy test";
        MyLabel.Top = 50;
        MyLabel.Left = 50;
        MyLabel.Visible = false;
        return MyLabel;
    }
);

//Sprawdzenie czy obiekt został utworzony, wynik: FALSE
MessageBox.Show(MyLazyLabel.IsValueCreated.ToString());

//Odwołanie do obiektu (zmiana właściwości Visible)
MyLazyLabel.Value.Visible = true;

//Ponowne sprawdzenie czy obiekt został utworzony, wynik: TRUE
MessageBox.Show(MyLazyLabel.IsValueCreated.ToString());

Przykład 3:

//Metoda tworząca obiekt Label
private Label CreateMyLabel()
{
    Label MyLabel = new Label();
    MyLabel.Parent = this;
    MyLabel.Text = "Lazy test";
    MyLabel.Top = 50;
    MyLabel.Left = 50;
    MyLabel.Visible = true;
    return MyLabel;
}

//Opakowanie obiektu typu Label z wykorzystaniem powyższej metody
Lazy<Label> MyLazyLabel = new Lazy<Label>(() => CreateMyLabel());

//Sprawdzenie czy obiekt został utworzony, wynik: FALSE
//Mimo ustawienia Visible na true, obiekt będzie widoczny dopiero po pierwszym odwołaniu do niego, wtedy nastąpi inicjalizacja
MessageBox.Show(MyLazyLabel.IsValueCreated.ToString());

//Odwołanie do obiektu (zmiana właściwości Top)
MyLazyLabel.Value.Top = 100;

//Ponowne sprawdzenie czy obiekt został utworzony, wynik: TRUE, obiekt jest widoczny
MessageBox.Show(MyLazyLabel.IsValueCreated.ToString());

Więcej informacji o klasie Lazy: MSDN 

Nowości w SQL Server 2012 – funkcje konwertujące

2012-04-20 

Język T-SQL w najnowszej wersji SQL Server został wzbogacony o szereg nowych funkcji. W tym wpisie zajmę się trzema funkcjami służącymi do konwersji typów danych:

  • PARSE
  • TRY_PARSE
  • TRY_CONVERT

PARSE

Funkcja PARSE służy do konwersji ciągu znaków na inny typ, w przypadku niepowodzenia operacji zgłaszany jest błąd.

PARSE(string_value AS data_type [USING culture])

string_value – ciąg znaków do skonwertowania
data_type – docelowy typ danych
culture – opcjonalne wskazanie ustawień regionalnych w jakich sformatowany jest ciąg znaków, domyślnie używane są ustawienia bieżącej sesji

--Ok
select PARSE('2011/01/01' as datetime)

--Error converting string value into data type using culture ''.
select PARSE('100,00 zł' as money)
select PARSE('01 styczeń 2012' as datetime)

--Ok
select PARSE('100,00 zł' as money using 'Pl-PL')
select PARSE('01 styczeń 2012' as datetime using 'Pl-PL')
select PARSE('2012 sty 15' as datetime using 'Pl-PL')

TRY_PARSE

Funkcja TRY_PARSE działa tak samo jak PARSE ale w przypadku niepowodzenia operacji zwracany jest NULL.

TRY_PARSE(string_value AS data_type [USING culture])

string_value – ciąg znaków do skonwertowania
data_type – docelowy typ danych
culture – opcjonalne wskazanie ustawień regionalnych w jakich sformatowany jest ciąg znaków, domyślnie używane są ustawienia bieżącej sesji

--NULL
select TRY_PARSE('100,00 zł' as money)
select TRY_PARSE('01 styczeń 2012' as datetime)

TRY_CONVERT

Funkcja TRY_CONVERT działa podobnie jak TRY_PARSE ale pozwala na konwersję z innych typów niż ciąg znaków, w przypadku niepowodzenia operacji zwracany jest NULL. W przypadku próby niedozwolonej konwersji (np. z typu INT na DATE) zgłaszany jest błąd.

TRY_CONVERT(data_type, value [, style])

data_type – docelowy typ danych
value – wartość do skonwertowania
style – opcjonalne wskazanie formatu danych wyjściowych, działa analogicznie jak w dotychczasowej funkcji CONVERT

--Ok: 20120420
select TRY_CONVERT(varchar(8), GetDate(), 112)

--NULL
select TRY_CONVERT(money, 'test')

--Explicit conversion from data type int to date is not allowed.
select TRY_CONVERT(date, 1)

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Pierwszy wpis!

W dniu dzisiejszym uruchomiona została niniejsza strona, czyli mndevnotes.wordpress.com. Jej zawartość będzie ściśle związana z programowaniem w technologiach firmy Microsoft. Wpisy głównie będą dotyczyły platformy .NET, języka C# oraz serwera baz danych SQL Server wraz z językiem T-SQL. W poszczególnych tematach postaram się przedstawiać ciekawostki i niuanse związane z daną technologią. Będą to treści na zasadzie „How to…”, a więc mało teorii, dużo kodu. Po zakończeniu niezbędnych czynności konfiguracyjnych rozpocznę regularne (mam nadzieję, że czas na to pozwoli) zamieszczanie wpisów. Zapraszam do odwiedzania strony :).