ADO.NET wstawianie danych do bazy – SqlCommand

 

W tym temacie zdecydowałem się poruszyć zagadnienie wstawiania danych do bazy z wykorzystaniem obiektu typu SqlCommand. Początkowo opis miał zawierać również opis wykorzystania SqlDataAdapter, ale temat zrobiłby się wtedy zbyt obszerny jak na jeden post. Dodatkowo poruszona zostanie kwestia zapytań z parametrami oraz wywyoływania procedur składowanych.

W poprzednich odsłonach pokazałem jak wykorzystać obiekt typu SqlCommand do pobrania danych z bazy za pomocą polecenia SELECT. Można go jednak stosować również m.in. do innych operacji DML (INSERT, UPDATE, DELETE). Opis tabel, które zostaną teraz wykorzystane, przedstawiłem w poprzednim poście dotyczącym tworzenia tabel będących w relacji 1-do-1. Na początek zajmijmy się zatem najprostszym wariantem wstawiania danych, czyli na sztywno zapisanym zapytaniem. Zapytanie takie w moim przypadku będzie miało następującą postać:

string query = "INSERT INTO dbo.Addresses (City, Street) VALUES ('Warszawa', 'Mickiewicza')";

Widzimy tu na sztywno wpisane wartości dla miasta – Warszawa, dla ulicy – Mickiewicza. Aczkolwiek przydatność takiego zapytania jest niewielka, to warto zacząć właśnie od takiej prostej konstrukcji w celu sprawdzenia, czy dane mogą zostać bez komplikacji dodane do tabeli. Polecenia niezwracające wyników które miałyby być analizowane wywoływane są za pomocą metody ExecuteNonQuery() wywoływanej na rzecz obiektu typu SqlCommand. Metoda ta zwraca jedynie liczbę wierszy, które zostały, w tym przypadku, wstawione do tabeli. Kod dla takiego, podstawowego sposobu wstawiania danych do tabeli może wyglądać następująco:
 
using (SqlCommand cmd = new SqlCommand(query, cn))
{
     //Execute query 
    try{
        int rowsAffected = cmd.ExecuteNonQuery();
                                   
        if (rowsAffected != 0)
        {
            System.Diagnostics.Debug.WriteLine("Rows inserted: " + rowsAffected);
        }
        else
        {
            System.Diagnostics.Debug.WriteLine("No rows inserted!");
        }
    }
    catch(Exception ex)
    {
        System.Diagnostics.Debug.WriteLine("Error executing query");
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}

Najczęściej wartości przeznaczone do wstawienia do tabeli pochodzą ze zmiennych. Należałoby więc uczynić coś, aby zamiast wpisywanych na sztywno wartości przekazywać wartości zmiennych. Zmienne do zapytania można przekazać poprzez umieszczenie w klauzuli VALUES (….) nazw tych zmiennych ze znakiem @, czyli w takiej postaci, jak deklarowane są zmienne w języku T-SQL:
 
string query = "INSERT INTO dbo.Addresses (City, Street) VALUES (@City, @Street);";

Sposób odbierania wyników jest identyczny jak w poprzednim przypadku. Tym razem należy jednak w jakiś sposób powiązać zmienne występujące w aplikacji ze zmiennymi znajdującymi się w zapytaniu. Można to uczynić na dwa sposoby. Pierwszy z nich, nieco dłuższy:
 
using (SqlCommand cmd = new SqlCommand(query, cn))
{
    //At first using standard INSERT with parameters - insert into Addresses table 
    SqlParameter p = new SqlParameter();
    p.ParameterName = "@City";
    p.Value = CityTextBox.Text;
    cmd.Parameters.Add(p);//Add parameter to set of parameters

    ...
}

Sposób ten wymaga utworzenia obiektu typu SqlParameter, a następnie nadaniu mu nazwy parametru (takiej jak w zapytaniu) oraz wartości, która użyta będzie jako parametr zapytania. W przedstawionym kodzie wartość ta pochodzi z pola tekstowego uzupełnianego przez użytkownika. Na koniec pozostaje jedynie dodać parametr do kolekcji zawartej w obiekcie typu SqlCommand. Drugi, szybszy sposób, w jednym poleceniu tworzy automatycznie obiekt typu SqlParameter, wypełnia odpowiednie właściwości i dodaje do kolekcji:
 
using(SqlCommand cmd = new SqlCommand(query, cn))
{
    ...

    //Another way to create a parameter
    cmd.Parameters.AddWithValue("@Street", StreetTextBox.Text);
}

W tym przypadku wykorzystana została metoda AddWithValue() wywoływana dla kolekcji parametrów obiektu typu SqlCommand.

Stąd już tylko krok do pokazania w jaki sposób można wywoływać procedury składowane z poziomu kodu C#. Przede wszystkim, gdy chcemy skorzystać z procedury należy zmienić argument dostarczany do konstruktora obiektu SqlCommand. Przykładowo, dla utworzonej przez mnie procedury o nazwie sp_InsertShopProcedure, wyglądać to będzie następująco:

using (SqlCommand cmd = new SqlCommand("sp_InsertShopProcedure", cn))
{
    ...
}

Widać więc, że w momencie tworzenia obiektu cmd przekazujemy w konstruktorze jedynie nazwę procedury, pomijając jakiekolwiek informacje o parametrach. Dopiero dla tak utworzonego obiektu parametry te są dostarczane poprzez wykorzystanie odpowiednich metod, przy czym należy pamiętać, aby zaznaczyć iż wykonywana będzie procedura, a nie zwykłe zapytanie:
 
cmd.CommandType = CommandType.StoredProcedure;

Następnie można dodać odpowiednie parametry w taki sposób:
 
cmd.Parameters.AddWithValue("@Name", NameTextBox.Text);
cmd.Parameters.AddWithValue("@City", CityTextBox.Text);
cmd.Parameters.AddWithValue("@Street", StreetTextBox.Text);

lub z zaznaczeniem typu sql i jego rozmiaru, w taki:
 
//Another way to supply parameters
cmd.Parameters.Add("@ChainName", SqlDbType.VarChar, 15);
cmd.Parameters["@ChainName"].SqlValue = ChainNameTextBox.Text;

Sposób wywołania takiej procedury jest identyczny jak zwykłego zapytania, czyli wymaga użycia metody ExecuteNonQuery() na rzecz obiektu typu SqlCommand.

T-SQL: pierwsze tabele, relacja 1-do-1

 

Do dalszej pracy nad projektem niezbędna będzie poprawnie zaprojektowana tabela. W tym momencie projekt całej bazy, aczkolwiek przydatny, nie jest niezbędny. Przed utworzeniem bazy dla swojej aplikacji chciałbym najpierw poszerzyć swoją wiedzę z zakresu modelowania danych. Do dalszych rozważań, pojedyncza tabela (a właściwie dwie ze względu na powiązanie ich), której projekt przedstawię w niniejszym poście, będzie wystarczająca.

Celem do zrealizowania jest utworzenie tabeli przechowującej dane o sklepach. Sklep może mieć swoją nazwę oraz należeć do sieci handlowej. Ponadto sklep oczywiście musi znajdować się w określonym mieście oraz mieć przypisany konkretny adres. Tutaj występuje pewne ułatwienie w stosunku np. do tabeli przechowującej dane osoby, gdyż sklep może mieć tylko jeden adres. Wszystkie te dane muszą być określone. Nie da się przecież odnaleźć sklepu nie znając jego nazwy i adresu. Jedynym atrybutem, który nie musi być znany jest nazwa sieci handlowej. Z racji tego, że aplikacja ma umożliwiać jedynie zlokalizowanie sklepu na mapie, nie są potrzebne dodatkowe dane takie jak kod pocztowy, czy też telefon. W razie przeoczenia jakiegoś atrybutu adresu pozostawiam sobie możliwość jego dodania. W związku z tym dane adresowe zostaną umieszczone w innej tabeli niż podstawowe dane sklepu, które również potem w razie potrzeby mogą być uzupełnione. Powstaną więc dwie tabele, pierwsza  o nazwie Shops oraz druga o nazwie Addresses. Tabele te muszą być w relacji 1-do-1 ze względu na to, że pod jednym adresem może istnieć tylko jeden sklep (nawet jeśli są na tej samej ulicy i pod tym samym numerem to z reguły jest jeszcze dodatek w postaci określenia lokalu lok., literka dodana na końcu, czy też inne oznaczenie). Analogicznie sklep z powodu wymagań administracyjnych również może mieć przypisany tylko jeden adres. Zatem do dzieła.

Tworzenie tabeli Shops:

USE ShoppingAppDb;
GO

IF OBJECT_ID('dbo.Shops') IS NOT NULL
    DROP TABLE dbo.Shops;
    
CREATE TABLE Shops
(
    Shop_Id int IDENTITY(1,1) not null primary key,
    Name varchar(50) not null,
    Addr_Id int unique not null, -- 1-to-1 relation
    ChainName varchar(50) null
);

Przed utworzeniem tabeli należy oczywiście sprawdzić, czy nie istnieje już tabela o takiej nazwie. W tym celu wykorzystywana jest funkcja OBJECT_ID(), która jako parametr przyjmuje ciąg znakowy określający nazwę tabeli (w ten sposób można również sprawdzać fakt istnienia innych obiektów). Kolumna Shop_Id jednoznacznie musi określać dany sklep w bazie. Z tego powodu posiada ona atrybut IDENTITY(1, 1), który oznacza, że numeracją poszczególnych wierszy obarczymy SQL Server, przy czym wiersze numerowane będą od numeru 1, z krokiem inkrementacyjnym równym 1. Podkreślić należy, że nie oznacza to, że wszystkie w kolejności numery porządkowe zostaną wykorzystane. Zapewniona jest jedynie ich unikalność. W kolumnie tej nie mogą znajdować się wartości NULL, a ponadto jej cechy decydują o tym, że warto w niej założyć klucz główny (w tym momencie nie potrzebuję klucza nazwanego, być może zostanie to zweryfikowane w późniejszym czasie).

Tworzenie tabeli Addresses:

IF OBJECT_ID('dbo.Addresses') IS NOT NULL
    DROP TABLE dbo.Addresses;
    
CREATE TABLE Addresses
(
    Addr_Id int IDENTITY(1,1) not null primary key,
    City varchar(30) not null, 
    Street varchar(40) not null,
);

Tabela z adresami również posiada na tej samej zasadzie utworzony klucz główny. Kolumny City oraz Street muszą być określone, natomiast w kolumnie Street przechowywany będzie cały adres, łącznie z numerem (ponownie na tę chwilę nie widzę konieczności rozgraniczania numeru ulicy od jej nazwy z powodów wcześniej wymienionych, dotyczących różnorodności sposobów określania adresów).
 
Przy zwracaniu wyników zapytań z tabeli Shops, chcielibyśmy otrzymywać adres w postaci ciągu znakowego, a nie jedynie numeru określającego adres. Ponadto dodanie nowego sklepu wymaga podania adresu. Z tych powodów należy powiązać ze sobą te dwie tabele za pomocą klucza obcego:
ALTER TABLE dbo.Shops 
ADD CONSTRAINT FK_shops_addresses FOREIGN KEY(Addr_Id)
REFERENCES dbo.Addresses (Addr_Id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Po wywołaniu powyższego kodu klucz obcy powstaje w kolumnie Addr_Id w tabeli Shops, która to jest powiązana z kolumną Addr_Id z tabeli Addresses.
 
W tym momencie można by ulec pokusie, że rekordy w tabeli Shops oraz Addresses nie mogą się dublować, w związku z czym można zakończyc pracę nad tymi tabelami. Jednak nic bardziej mylnego. Najpierw odnośnie drugiej z tabel czyli Addresses – to, że kolejne wiersze z tabeli będą posiadać unikatowe identyfikatory nie oznacza jeszcze, że dane w niej nie mogą się powtarzać. Przy takim zdefiniowaniu tabeli łatwo można bowiem dodać dwa razy tę samą nazwę miasta (co akurat jest pożądane) oraz ten sam adres (co już nie jest pożądane, gdyż powoduje duplikowanie rekordów). Należy zatem uniemożliwić takie działanie poprzez dodanie ograniczenia unikalności (Unique Constraint), które nie pozwoli na ponowne wpisanie tej samej kombinacji miasta i adresu:
ALTER TABLE dbo.Addresses
ADD CONSTRAINT UQ_ADDRESSES UNIQUE(City, Street);

Wykonane, ale czy to zapewnia realizację postawionego na początku celu? Tak, ale należy zwrócić baczną uwagę na pewien drobny szczegół, jakim jest atrybut unique dodany do definicji kolumny Addr_Id w tabeli Shops. Aby przykład był bardziej dydaktyczny nie powinienem umieszczać go w pierwszym kodzie źródłowym ale, ze względu na zbytnie nie rozwlekanie postów, zdecydowałem się go tam już na początku umieścić. Wyobraźmy sobie przez chwilę, że nie ma tam słowa unique. Co w takim wypadku otrzymujemy? Otóż, o ile jeden sklep w dalszym ciągu może posiadać tylko jeden adres, o tyle dodanie drugiego sklepu pozwala ponownie na przypisanie tej samej wartości z tabeli Addresses, co oznacza, że dwa różne sklepy mogłyby istnieć pod tym samym adresem. Takiej operacji jednak już na początku nie uznaliśmy za poprawną. Dodanie atrybutu unique sprawia, że wartości w tej kolumnie nie mogą się dublować, a co za tym idzie nie można dwóm różnym sklepów przypisać tych samych adresów (dla przypomnienia unikatowość danych w tabeli Addresses została już wcześniej zapewniona). Sprawdzenie tego faktu pozwalam sobie pominąć w tym poście (sprawdzenie takiego oczywiście dokonałem w swojej bazie).
 
Na zakończenie tworzenia tabel chciałbym poruszyć jeszcze jedną istotną kwestię. Dodanie danych do tabeli ze sklepami wymaga wcześniejszego istnienia adresu w tabeli z adresami (ograniczenie klucza obcego). Po pierwsze na użytkownika nie powinno się nakładać obowiązku dowiadywania się co do jakiej tabeli należy wstawić, aby można było coś wstawić do innej tabeli. Ponadto, ze względów bezpieczeństwa, nie powinno się umożliwiać swobodnego dokonywania operacji INSERT, UPDATE, czy też DELETE. Wypadałoby stworzyć więc procedurę składowaną (Stored Procedure), która będzie ukrywać sposób realizacji procesu wstawiania danych przed użytkownikiem.:
CREATE PROCEDURE sp_InsertShopProcedure
(
    -- input parameters
    @Name varchar(50),
    @ChainName varchar(50),
    @City varchar(30),
    @Street varchar(40)
)
AS
BEGIN
    INSERT INTO dbo.Addresses(City, Street) VALUES (@City, @Street);
    DECLARE @id int = SCOPE_IDENTITY();
    
    INSERT INTO dbo.Shops (Name, ChainName, Addr_Id) VALUES (@Name, @ChainName, @id);

END;

W powyższej procedurze dane uzyskane od użytkownika, czyli nazwa sklepu, sieci, miasto oraz jego adres, wykorzystywane są w takiej kolejności, że najpierw następuje dodanie miasta i adresu do tabeli Addresses, co umożliwia z kolei wykorzystanie tych danych do wstawienia sklepu do tabeli Shops. Po wstawieniu danych do tabeli Addresses uzyskiwany za pomocą funkcji SCOPE_IDENTITY() jest identyfikator wstawionego wiersza w ramach tej procedury do wspomnianej tabeli, który z kolei umożliwia poprawne wstawienie danych do tabeli Shops. Calość można by umieścić jeszcze w ramach transakcji, czego w powyższym kodzie nie uczyniłem. Wykorzystanie opisanej tabeli przedstawię w kolejnych postach. Tym razem już na zakończenie – diagram z utworzonymi tabelami:

tabele

ADO.NET zapytanie zwracające pojedynczą wartość

 

W poprzednich postach omówiłem wykorzystanie metody ExecuteReader() wywoływanej na rzecz obiektu typu SqlCommand, która to zwracała obiekt typu SqlDataReader. Dysponowanie takim obiektem pozwala na analizę wyników zwróconych przez zapytanie. Kolejne dane z SqlDataReader’a odczytywane są wierszami, co sugeruje, że metoda ExecuteReader() powinna być stosowana wtedy, gdy chcemy pobrać z bazy danych wiele wierszy. Czasem jednak, wykorzystując do tworzenia zapytań np. funkcje agregujące oczekujemy, że zwrócona zostanie tylko jedna wartość. W takim wypadku nie ma sensu wykorzystywanie zbyt skomplikowanych rozwiązań, ręczne wykonywanie i analizowanie wyników zwróconych przez metodę ExecuteReader(). Obiekt typu SqlCommand posiada bowiem metodę   ExecuteScalar(), której zadaniem jest zwrócenie właśnie pojedynczej wartości.

Przykład:

Rozważmy stosowaną w poprzednich przykładach tabelę z przygotowanej bazy danych:

Id

FirstName

LastName

LoginName

Age

ExperienceLevel

1

NULL

NULL

Michal

18

2.2

3

Zbigniew

Kowalski

sa

NULL

3.3

Zmianie uległa jedynie wartość poziomu doświadczenia dla użytkownika o loginie ‘sa’ z wcześniejszej wartości 2.2 do 3.3. Celem przykładu jest zaimplementowanie funkcjonalności, która zwracałaby średni poziom zaawansowania użytkowników. Wiązać będzie się to z wykorzystaniem funkcji agregujących. Zapytanie (ściśle mówiąc łańcuch znakowy przechowujący zapytanie) będzie miało zatem następującą postać:

string strSql = "SELECT AVG(ExperienceLevel) FROM dbo.SimpleLogins";
Pojedynczą wartość można oczywiście uzyskać stosując metodę ExecuteReader(). Spójrzmy jak w tym przypadku wyglądałby stosowny kod.
 
using (SqlDataReader reader = cmd.ExecuteReader())
{
    //Take first row from reader
    reader.Read();

    //Assign result to ExperiencaLabel
    ExperienceLabel.Text = reader[0].ToString();
}

Przy takim podejściu należy utworzyć obiekt typu SqlDataReader, odczytać pierwszy wiersz (zakładamy, że zwrócony zostanie przynajmniej jeden), a także w przypadku gdy nie jest z jakiegoś powodu zastosowany blok using, pamiętać o zamknięciu odczytywanego strumienia. Wydaje się to być zbyt wiele zachodu do odczytania jednej wartości. Spójrzmy zatem co oferuje nam metoda ExecuteScalar():
 
ExperienceLabel.Text = cmd.ExecuteScalar().ToString();

Tak, zgadza się, wszystko za pomocą jednej linii kodu. Jak to możliwe? Otóż wywołanie metody ExecuteScalar() powoduje automatyczne utworzenie obiektu typu SqlDataReader i odczytanie z niego pożądanej wartości. Na zakończenie wywołania metoda ta ponadto sama zamyka i usuwa obiekt typu SqlDataReader. Wchodząc nieco głębiej w szczegóły metoda ExecuteScalar() wykonuje zapytanie zwracając przy tym pierwszą kolumnę z pierwszego wiersza otrzymanego wyniku. Dodatkowe kolumny i wiersze, nawet gdyby istniały, są ignorowane. Zwracany jest obiekt typu Object, stąd konieczne jest rzutowanie. W razie niepowodzenia przy odczycie wyrzucony zostanie wyjątek SqlException.

Wynikiem w przedstawionym przykładzie jest oczywiście wartość 2.750000, która wpisywana jest do utworzonej kontrolki ExperienceLabel.

ADO.NET proste zapytania do bazy + ASP.NET

 

W poprzednich dwóch odsłonach informacje pobrane z bazy danych wyświetlane były na wyjściu diagnostycznym. Nadszedł czas na przedstawienie bardziej praktycznego rozwiązania, które wymaga interakcji ze strony użytkownika. W tym celu wykorzystana zostanie kontrolka ASP.NET, jaką jest CheckBoxList. W przedstawionym przeze mnie przykładzie taka lista zapełniana jest opcjami pochodzącymi z przygotowanej bazy danych. Jej elementy zawierają pola pochodzące z kolumny LoginName z tabeli SimpleLogins. Użytkownik, po zaznaczeniu wybranych przez siebie pozycji, a także kliknięciu w odpowiedni przycisk, otrzymuje informację zwrotną o swoim wyborze. Zatem do dzieła…

Po naciśnięciu przez użytkownika przycisku o nazwie “insertDataIntoTableButton” wykonywany jest następujący kod:

protected void insertDataIntoTableButton_Click(object sender, EventArgs e)
{
    //Select all logins from table
    string strSql = "SELECT LoginName FROM SimpleLogins";
    List<string> loginsList = new List<string>(2);//initial capacity set to 2
    int ordinalColumn = -10;//bad column number at start

    using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
    {
        try
        {
            cn.Open();

            using (SqlCommand cmd = new SqlCommand(strSql, cn))
            {
                try
                {   
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        try
                        {
                            ordinalColumn = reader.GetOrdinal("LoginName");

                            while (reader.Read())
                            {
                                loginsList.Add(reader.GetString(ordinalColumn));
                            }

                        }
                        catch (Exception ex)
                        {
                            //reader exception implement
                        }
                        finally
                        {
                            loginsList.TrimExcess();
                            reader.Close();
                        }

                    }

                }
                catch (Exception ex)
                {
                    //implement
                }

            }
        }
        catch (Exception ex)
        {
            //implement
        }

    }

    //Connection closed - data in List named loginsList
    populateCheckBoxList(loginsList);
}

Omówmy teraz to, co zostało zawarte w powyższym kodzie. Dane pobrane z bazy zapisywane są w obiekcie typu List<string>, przy przedstawionym użyciu metody ExecuteReader() nie znamy bowiem liczby zwróconych wierszy z bazy, potrzebujemy zatem obiektu, który zmieniał będzie dynamicznie swój rozmiar. W porównaniu do poprzednich przykładów zmienił się nieco sposób odczytu danych. Jak zostało to w poprzednich postach wspomniane, wykorzystanie metod zwracających określony typ danych wymaga podania jako argumentu numeru odczytywanej kolumny. Zdarzyć się może jednak taka sytuacja, w której nie będziemy znali położenia w zapytaniu tej kolumny. Obiekt typu SqlDataReader posiada pomocną metodą, która pozwala ominąć to ograniczenie. Mowa tutaj o metodzie GetOrdinal(). Spójrzmy na poniższy fragment przedstawionego wcześniej kodu:

try
{
    ordinalColumn = reader.GetOrdinal("LoginName");

    while (reader.Read())
    {
        loginsList.Add(reader.GetString(ordinalColumn));
    }
}

Argumentem metody GetOrdinal() jest nazwa kolumny. Jej zadaniem jest ‘przetłumaczenie’ nazwy kolumny na jej numer porządkowy. Warto zaznaczyć, że taka operacja, w przypadku wykonania jej przed odczytem danych ze strumienia, może znacząco przyśpieszyć sam proces odczytu, szczególnie dla dużej ilości danych. Znając już numer odpowiedniej kolumny można przystąpić do zapisania odczytanych danych na utworzonej wcześniej liście.

Po zamknięciu połączenia wykonywana jest metoda, której zadaniem jest powiązanie obiektu typu CheckBoxList z wypełnioną przez nas danymi listą. Metoda ta została zaimplementowana następująco:

/// <summary>
/// Populates CheckBoxList with options to select
/// </summary>
private void populateCheckBoxList(List<string> cbl)
{
    try
    {
        //Bind data from loginsList to CheckBoxList1
        this.CheckBoxList1.DataSource = cbl;
        this.CheckBoxList1.DataBind();
    }
    catch (Exception ex)
    {
        //implement
    }
}

Związanie źródła danych z kontrolką następuje poprzez przypisanie go do właściwości DataSource. Faktyczne związanie następuje jednak dopiero po wywołaniu metody DataBind(), więc nie można o nim zapomnieć.

Załóżmy teraz, że wybrane opcje z CheckBoxList-y, nie znając ich wcześniejszego pochodzenia, chcemy wyświetlić użytkownikowi na innej stronie w przyjemnej formie, dodatkowo po naciśnięciu przycisku. Utworzenie i wypełnienie przygotowanej listy może przyjąć następującą postać:

List<string> myList = new List<string>(2);
           
if (CheckBoxList1.Items != null)
{
   foreach (ListItem elem in CheckBoxList1.Items)
   {
       if (elem.Selected)
       {
           myList.Add(elem.Value);
       }
   }
}

Najprostszym sposobem przekazania wszystkich wartości znajdujących się na liście byłoby przekazanie jej w całości do innej strony. Nie rozważając w tej chwili skuteczności takiego rozwiązania ASP.NET umożliwia przekazanie takiego obiektu poprzez stan sesji – Session State. (z racji posiadanej przeze mnie prawie wyłącznie anglojęzycznej literatury nie jestem pewien polskiego odpowiednika – kiedyś w php wiem, że funkcjonowało określenie zmienne sesyjne w odniesieniu do tak przekazywanych zmiennych). Przypisanie obiektu do zmiennej sesyjnej odbywa się poprzez wykorzystanie indeksów z odpowiednim ciągiem znakowym, czyli:
Session["myList"] = myList;
Przekierowanie na inną stronę może być natomiast zrealizowane w następujący sposób:
Server.Transfer("Details.aspx");
Zmienne sesyjne na nowej stronie są odczytywane również z wykorzystaniem indeksów. Tym razem jednak należy dokonać operacji rzutowania na określony typ. Uprzednio należy sprawdzić, czy dana zmienna istnieje w sesji. O ile próba odczytania danych ze zmiennej, która nie istnieje nie powoduje wyrzucenia wyjątku, o tyle jeśli spróbujemy iterować po obiekcie, do którego została ona nieświadomie przypisana, można już otrzymać wyjątek. Uprzednio dobrze jest więc sprawdzić, czy dana zmienna sesyjna istnieje:
if (Session["myList"] != null)
{
    List<string> myList = (List<string>)Session["myList"];
    foreach (string elem in myList)
    {
        TextBox1.Text += elem + Environment.NewLine;
    }
}

ADO.NET proste zapytania do bazy cz. 2

 

Dla przypomnienia, poprzednia część rozważań na temat zapytań do bazy danych zakończyła się na wywołaniu zapytania komendą:

SqlDataReader reader = cmd.ExecuteReader();

wyniki natomiast odbierane były za pomocą odwołania:

reader["Id"];

przy czym należy pamiętać o zamknięciu strumienia, aby nie blokować otwartego połączenia:

reader.Close();
lub wykorzystaniu bloku using, która na zakończenie tego właśnie bloku zwalnia wszelkie zasoby, zatem powinien również zamknąć strumień, jakim jest reader. Warto jeszcze raz podkreślić, że raz odczytane dane z obiektu SqlDataReader stają się niedostępne (nie można ich odczytać jeszcze raz).

W niniejszej części drugiej chciałbym zademonstrować bardzieje efektywne metody odczytu danych z zapytania wysłanego do bazy.

Przykład 2

W poprzednim przykładzie dane pobierane były jedynie w celu wyświetlenia ich na ekranie. Tym razem spróbujmy przypisać odczytane dane odpowiednim zmiennym. Zapytanie tym razem będzie miało następującą postać:

strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 1";

co oznacza, że będziemy potrzebować zmiennej o typie odpowiadającym typowi tinyint w Sql Server, czyli byte, zmiennej typu int, która określa wiek oraz zmiennej opisującej doświadczenie, w tym przypadku typ zmiennej zarówno w języku C# jak i w Sql Server brzmi tak samo – będzie to zatem decimal.

Jak zostało to zaznaczone w poprzedniej części wspomniane na początku pobranie wartości z bazy danych przy użyciu indeksów zwraca obiekty typu Object. Minusem takiej operacji jest to, że konieczne w przypadku przypisania do zmiennej będzie wykonanie operacji unboxingu. Dobra praktyka programistyczna nie pozwala na tego typu marnotrawstwo zasobów, zatem udostępnione zostały metody zwracające wartość określonego typu. Zobaczmy jak to wygląda na przykładzie:

protected void GetTypesButton_Click(object sender, EventArgs e)
{
    string strCon, strSql;
    byte idValue = 0;
    int ageValue = 0;
    decimal experienceValue = 0;

    strCon = BuildConnectionString();
    
    //Create new SqlConnection object using given string
    using (SqlConnection cn = new SqlConnection(strCon))
    {
        strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 1";
          
        //Open the connection
        cn.Open();
        
        //Create SqlCommand object in order to query database
        using (SqlCommand cmd = new SqlCommand(strSql, cn))
        {
            try
            {
                //Use SqlDataReader to retrieve sequentially results
                using(SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.FieldCount == 3)
                    {
                        while (reader.Read())
                        {
                             //Use ordinal numbers to retrieve results
                            idValue = reader.GetByte(0);
                            ageValue = reader.GetInt32(1);
                            experienceValue = reader.GetDecimal(2);
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                //implement
            }
        }
    }

    //Write these values to output 
    System.Diagnostics.Debug.WriteLine("idValue: " + idValue.ToString());
    System.Diagnostics.Debug.WriteLine("ageValue: " + ageValue.ToString());
    System.Diagnostics.Debug.WriteLine("experienceValue: " + experienceValue.ToString());
}

Wykorzystanie tych metod nastąpiło poprzez wywołania:
  • reader.GetByte(0);
  • reader.GetInt32(1);
  • reader.GetDecimal(2);

Widać zatem, że odwołania do obiektów mają inną postać. Nie dość, że korzystamy z metod zwracających określony typ danych to jeszcze nie pojawił się indeks z odpowiednimi nazwami, a mało znaczące cyfry jako argumenty wywołania. To jest właśnie jedno z ograniczeń tego podejścia. Mianowicie nie możemy już odwoływać się po nazwie do odpowiedniej zmiennej, a musimy skorzystać z numery porządkowego, który odpowiada odpowiednim wartościom w zapytaniu. Oznacza to, że w przypadku wywołania reader.GetByte(0) zaznaczamy, że chcemy tej zmiennej przypisać wartość kolumny nazwanej “Id”. Pomimo tej niegodności taki sposób odczytu wyników powinien być znacznie szybszy, bowiem kompilator (czy też inne cudo zajmujące się tym zadaniem) nie musi porównywać niejednokrotnie długich nazw.

Mamy zatem szybką metodę do odczytu danych z bazy, dobrze działającą z typami zaimplementowanymi w C#. Otóż nie do końca. Rozważmy następujący przykład.

Przykład 3

W poprzednim przykładzie dane odczytywane z wiersza były określone, tzn. nie zawierały wartości nieznanych – NULL. Tym razem odczytajmy dane z wiersza oznaczonego poprzez identyfikator Id = 3:

strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 3";

Zaglądając do tabeli zamieszczonej w poprzedniej części przedstawionej na blogu, zauważmy, że jedna z interesujących nas w tym zapytaniu zmiennych, czyli wartość kolumny Age jest nieokreślona – przyjmuje wartość NULL. Jak w tym przypadku zachowa się poprzedni kod (jest on taki sam, z wyjątkiem przedstawionego powyżej zapytania)? Oto wyniki:

Data is Null. This method or property cannot be called on Null values.

idValue: 3

ageValue: 0

experienceValue: 0

Widzimy więc, że o ile identyfikator został odczytane poprawnie o tyle zmienna ageValue ma początkową wartość 0, mimo iż w bazie danych nie była ona zdefiniowana, a takżę wartość zmiennej experienceValue również wynosi 0 pomimo, że w bazie znajdowała się wartość 2.2 (po wyrzuceniu wyjątku pozostałe dane nie zostały odczytane). Nie jest to zatem to, o co chodziło.

Jeśli chcielibyśmy przypisać wartości z bazy danych, które mogą zawierać wartości NULL należałoby najpierw pobrać informację, czy wyniki zapytania taką wartość zawierają. Efekt taki można uzyskać przy użyciu metody IsDBNull() wywoływanej na obiekcie typu SqlDataReaderi zastosowaniu instrukcji warunkowej:

if (reader.IsDBNull(1))
{
    ageValue = null;
}
else
{
    ageValue = reader.GetInt32(1);
}

if (reader.IsDBNull(2))
{
    experienceValue = null;
}
else
{
    experienceValue = reader.GetDecimal(2);
}

pamiętając przy tym, aby odpowiednie zmienne zadeklarowane były jako typ nullable, czyli:
int? ageValue = null;
decimal? experienceValue = null;

W takim przypadku na wyjściu otrzymujemy:

idValue: 3

ageValue:

experienceValue: 2.2

czyli oczekiwane wyniki. Czy jednak pisanie instrukcji warunkowych dla każdej zmiennej jest efektywne? Po raz kolejny odpowiedź brzmi – nie.

Przykład 4

Twórcy platformy .NET z pewnością przewidzieli, że taki sposób pisania kodu może być nieco uciążliwy i postarali się ułatwić ten proces wprowadzając “typy Sql” do języków programowania. Przed skorzystaniem z nich należy zadbać o dodanie odpowiedniej przestrzenii nazw, czyli:

using System.Data.SqlTypes;

Typy te mają wbudowaną obsługę wartości NULL. Na stronach msdn można znaleźć następującą informację o tych typach: “The System.Data.SqlTypes namespace provides classes for native data types in SQL Server. These classes provide a safer, faster alternative to the data types provided by the .NET Framework common language runtime (CLR). Using the classes in this namespace helps prevent type conversion errors caused by loss of precision. Because other data types are converted to and from SqlTypes behind the scenes, explicitly creating and using objects within this namespace also yields faster code.”

Otrzymujemy zatem, wedle zapewnień, “szybsze” typy niż te, które oferowane są jako wbudowane (porównanie typów Sql można znaleźć na stronie http://msdn.microsoft.com/en-us/library/system.data.sqltypes.aspx). Ponadto oferują one również dostęp za pomocą metod zwracających określony typ danych. Można by rzec rozwiązanie doskonałe. Spójrzmy zatem na następujący kod źródłowy:

protected void GetSqlTypesButton_Click(object sender, EventArgs e)
{
    string strCon, strSql;
    SqlByte idValue = 0;
    SqlInt32 ageValue = 0;
    SqlDecimal experienceValue = 0;
    
    strCon = BuildConnectionString();

    //Create new SqlConnection object using given string
    using (SqlConnection cn = new SqlConnection(strCon))
    {
        strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 3";

        //Open the connection
        cn.Open();

        //Create SqlCommand object in order to query database
        using (SqlCommand cmd = new SqlCommand(strSql, cn))
        {
            try
            {
                //Use SqlDataReader to retrieve sequentially results
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    try
                    {
                        if (reader.FieldCount == 3)
                        {
                            while (reader.Read())
                            {
                                //Use ordinal numbers to retrieve results
                                idValue = reader.GetSqlByte(0);                            
                                ageValue = reader.GetSqlInt32(1);
                                experienceValue = reader.GetSqlDecimal(2);
                            }
                        }
                    }
                    catch (SqlNullValueException ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                //implement
            }
        }
    }

    //Write these values to output 
    System.Diagnostics.Debug.WriteLine("idValue: " + idValue.ToString());
    System.Diagnostics.Debug.WriteLine("ageValue: " + ageValue.ToString());
    System.Diagnostics.Debug.WriteLine("experienceValue: " + experienceValue.ToString());
}

Mamy deklarację odpowiednich zmiennych wykorzystujących typy Sql, czyli:
SqlByte idValue = 0;
SqlInt32 ageValue = 0;
SqlDecimal experienceValue = 0;
oraz użycie odpowiednich metod dla tych typów:
idValue = reader.GetSqlByte(0);
ageValue = reader.GetSqlInt32(1);
experienceValue = reader.GetSqlDecimal(2);
Wyniki uzyskane dzięki wywołaniu kodu znajdującego się w metodzie obsługującej zdarzenia dla GetSqlTypesButton wyglądają następująco:

idValue: 3

ageValue: Null

experienceValue: 2.2

Zgodne z oczekiwaniami wyniki nie pozostawiają żadnych zarzutów, co można uznać za mały sukces 😉

W następnym poście na moim blogu przedstawię proste wykorzystanie ASP.NET do uzyskanych w ten sposób wyników. Nikt bowiem tworząc aplikację internetową nie będzie przedstawiał wyników na wyjściu diagnostycznym.

P.S. Widzę, że część osób jest zainteresowanych z moimi zmaganiami z ADO.NET opisywanymi na blogu więc, w razie gdybym jakieś zagadnienie poruszył niedostatecznie lub niejasno, zachęcam do zostawienia jakiegoś feedbacka w komentarzach. Zaznaczam przy tym, że jestem osobą początkującą w tym temacie, jednak na postawione pytania w ramach moich umiejętności oraz wolnego czasu postaram się odpowiedzieć.

ADO.NET proste zapytania do bazy cz. 1

 

W kolejnej odsłonie zagadnień dotyczących ADO.NET chciałbym przedstawić proces pobierania danych z bazy. Na potrzeby omówienia tego zagadnienia wykorzystana została baza danych o nazwie ShoppingAppDb, która będzie służyć w nieodległej przyszłości jako główna baza dla tworzonej przeze mnie aplikacji.

W nawiązaniu do mojego poprzedniego posta, o zarządzaniu użytkownikami z poziomu ASP.NET, również w tej bazie danych prawa db_owner przyznane zostały użytkownikowi o nazwie AspNetUser. Hasło dla tego użytkownika to ‘password’. Opisane w tym poście czynności wykonane zostały na tabeli SimpleLogins specjalnie utworzonej do tego celu. Nie jest ona znormalizowana. Jej postać oraz dane w niej zawarte powstały jedynie w celu łatwej prezentacji zagadnienia, jakim jest pobieranie danych z bazy.

Tabela SimpleLogins utworzona została w następujacy sposób:

USE ShoppingAppDb;
GO

IF OBJECT_ID('SimpleLogins') IS NOT NULL
    DROP TABLE SimpleLogins;
    
CREATE TABLE SimpleLogins
(
    Id tinyint IDENTITY(1,1) not null PRIMARY KEY,
    FirstName varchar(20) null, 
    LastName varchar(20) null, 
    LoginName varchar(20) unique not null,
    Age int null,
    ExperienceLevel decimal(2,1) not null
)
GO

W powyższym fragmencie kodu, na początek sprawdzone zostaje czy tabela o danej nazwie już istnieje, jeśli tak, to jest ona usuwana. Zadaniem tabeli jest przechowywanie informacji o administratorach systemu. W kolumnie o nazwie Id założony został klucz podstawowy wraz z atrybutem IDENTITY(1, 1), co oznacza, że numeracja kolejnych identyfikatorów rozpoczyna się od 1, z krokiem zwiększającym tę wartość również o 1. Dane użytkownika takie jak imię i nazwisko nie zawsze są znane, aczkolwiek konieczne jest identyfikowanie się użytkownika loginem, czyli kolumna LoginName posiada atrybut unique. Oznacza to, że SQL Server ma zadbać o to, aby w tej kolumnie nie pojawiły się zduplikowane wartości. Wiek również nie musi być znany, a zatem już teraz zwracam uwagę na potencjalny problem, jakim jest fakt że zmienna typu int może przyjmować wartość NULL. Ostatnia kolumna, czyli ExperienceLevel, to wymyślony przez potencjalnego twórcę takiej bazy danych stopień doświadczenia danego administratora/użytkownika. Typem dla tej kolumny jest decimal(2, 1). Oznacza to, że przyjmowane będą wartości z przedziału 0.0-9.9, z krokiem równym 0.1. 

Tabela ta została uzupełniona następującymi danymi:

Id

FirstName

LastName

LoginName

Age

ExperienceLevel

1

NULL

NULL

Michal

18

2.2

3

Zbigniew

Kowalski

sa

NULL

2.2

Dla przypomnienia, wykorzystanie obiektów, których zadaniem jest wykonywanie operacji na bazie danych znajdującej się w SQL Server, wymaga dołączenia następujących przestrzenii nazw:

  • System.Data
  • System.Data.SqlClient

Na stronie wyświetlanej po uruchomieniu aplikacji wyświetlane są 4 opcje do wyboru za pomocą buttonów. Każdy z nich pokazuje inny aspekt pobierania danych z bazy, zatem dla ułatwienia i większej czytelności kodu, utworzenie connectionString-a przeniesione zostało do oddzielnej metody:

/// <summary>
/// Builds connection string in order to connect to ShoppingAppDb for specified user
/// </summary>
/// <returns>Connection string</returns>
private string BuildConnectionString()
{    
   string result = null;

   SqlConnectionStringBuilder strConn = new SqlConnectionStringBuilder();
   strConn.DataSource = @".\SQLEXPRESS";
   strConn.InitialCatalog = "ShoppingAppDb";
   strConn.UserID = "AspNetUser";
   strConn.Password = "password";
   result = strConn.ConnectionString;

   return result;
}

Proces tworzenia connectionStringa przy wykorzystaniu obiektu klasy SqlConnectionStringBuilder przedstawiłem w jednym z wcześniejszych postów na moim blogu.

Uwaga: Przedstawiony w dalszej części kod z racji ilości linii niezbędnych do zamieszczenia nie jest w 100% zabezpieczony, a jedynie poglądowo (np. nie są obsługiwane wyjątki, które wiem, że nie pojawią się w tym przykładzie).

Przykład 1:

Po kliknięciu w przycisk z tekstem “Get Login Information” pobierane z bazy danych są informacje o pierwszym użytkowniku, czyli posiadającym Id = 1. Następnie dane te wypisywane są na wyjście diagnostyczne:

protected void GetLoginButton_Click(object sender, EventArgs e)
{
    string strCon, strSql;
    strCon = BuildConnectionString();

    //Create new SqlConnection object using given string
    using (SqlConnection cn = new SqlConnection(strCon))
    {
        strSql = "SELECT Id, FirstName, LastName, LoginName FROM dbo.SimpleLogins WHERE Id=1";
          
        //Open the connection
        cn.Open();
        
        //Create SqlCommand object in order to query database
        using (SqlCommand cmd = new SqlCommand(strSql, cn))
        {
            try
            {
                //Use SqlDataReader to retrieve sequentially results
                using(SqlDataReader reader = cmd.ExecuteReader())
                {                        
                    while (reader.Read())
                    {
                        //Use column names to retrieve value
                        System.Diagnostics.Debug.WriteLine("Id: " + reader["Id"].ToString());
                        System.Diagnostics.Debug.WriteLine("FirstName: " + reader["FirstName"].ToString());
                        System.Diagnostics.Debug.WriteLine("LastName: " + reader["LastName"].ToString());
                        System.Diagnostics.Debug.WriteLine("LoginName: " + reader["LoginName"].ToString());
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                //implement
            }
        }
    }
}

Wykorzystanie obiektu typu SqlConnection już znamy z poprzedniego wprowadzenia. Przede wszystkim do wykonania zapytania musimy dysponować jego treścią, która ukryta jest w zmiennej strSql. Wysłanie zapytania do serwera (wykonywanego w taki właśnie sposób) wymaga również otwartego połączenia do bazy danych. Łatwo w ten sposób utworzyć obiekt typu SqlCommand, który odpowiedzialny jest za wykonywanie zapytań. W podanym przykładzie wykorzystany został konstruktor przyjmujący dwa argumenty: treść zapytania oraz obiekt typu SqlConnection odpowiedzialny za ustanowienie połączenia:

SqlCommand cmd = new SqlCommand(strSql, cn);

Obiekt SqlCommand zawiera w sobie jedynie zapytanie do bazy. Musi ono być wysłane do bazy, a wyniki poprawnie odebrane. Wysłanie zapytania, które zwraca więcej niż jedno pole, odbywa się za pomocą wywołania metody ExecuteReader() na obiekcie typu SqlCommand. Wyniki odbierane są poprzez przypisanie tego wywołania do obiektu typu SqlDataReader, który służy do przechowywania danych odczytywanych następnie sekwencyjnie. Obiekt ten ma również tę cechę, że raz odczytanych danych nie można ponownie odczytać, gdyż nie są one już dostępne.

SqlDataReader reader = cmd.ExecuteReader();

Następnie, w celu “zdjęcia” danych z takiego readera, należy wywołać na nim metodę Read(). Metoda ta, poza tym że pozwala na dostęp do jednego wiersza danych, to ponadto pozwala na ustalenie czy pozostały jeszcze jakieś dane do odczytania, zwracając wartość typu boolean.

Dostęp do pól odczytywanego wiersza możliwy jest poprzez wykorzystanie indeksów, zawierających nazwy odczytywanych kolumn, np:

System.Diagnostics.Debug.WriteLine("Id: " + reader["Id"].ToString());

Zaznaczyć należy, że taki sposób jest nieefektywny, gdyż pobranie w ten sposób wartości readera zwraca obiekt typu Object. Następuję więc operacja zwana boxingiem, a co za tym idzie musi potem nastąpić unboxing. Obie te operacje są niezmiernie kosztowne.

Na koniec należy zamknąć odczytywany strumień wywołując metodę Close(). Jest to niezbędne działanie, gdyż reader blokuje połączenie. Oznacza to, że na raz możliwe jest korzystanie tylko z jednego obiektu typu SqlDataReader:

reader.Close();

Po wykonanie zamieszczonego kodu w przykładzie otrzymujemy na wyjściu następujący wynik:

Id: 1

FirstName:

LastName:

LoginName: Michal

Widać więc, że pola zawierające wartości NULL po wykonaniu na obiektach metody ToString() nie są wyświetlane. Pozostałe dane odczytane zostały prawidłowo.

Kolejne przykłady, wykorzystujące bardziej efektywne metody odczytywania danych z bazy oraz problemy z tego tytułu wynikające, przedstawione zostaną w kolejnym poście.

Wstęp do logowania (users, roles, permissions) w ASP.NET

 

Zanim nastąpi kolejna odsłona cyklu o ADO.NET na moim blogu, przedstawiam kolejne zagadnienie, którego wymagała będzie moja aplikacja, a mianowicie logowanie użytkowników. W swojej aplikacji spodziewam się podziału użytkowników na administratorów, zarządców (którzy nie będą np. mieć prawa do usunięcia administratora lub innego zarządcy) oraz użytkowników zwykłych, zarówno zalogowanych jak i anonimowych. Podejście to wymaga zastosowania jakiegoś systemu zarządzania kontami użytkowników. Nie wdając się w skuteczność i jakość tego rozwiązania zdecydowałem się zapoznać z wbudowanym narzędziem ASP.NET, jakim jest Web Site Administration Tool.

Dla przypomnienia mojej konfiguracji, opis odnosił będzie się do następującego zestawu narzędzi:

  • Microsoft Visual Web Developer 2010 Express
  • Microsoft SQL Server 2008 R2 Express

Jak łatwo zarządzać użytkownikami, logowaniem, rolami itp. za pomocą mechanizmów ASP.NET nie będę w tym poście opisywał. Wszystko to można obejrzeć, wchodząc na stronę www.asp.net/learn/security-videos. Przedstawię natomiast, jak zawsze, swoje problemy ze zmuszeniem tego mechanizmu do zadziałania na mojej maszynie. Zaznaczam, że jestem osobą dopiero poznającą technologię ASP.NET, zatem przedstawione przeze mnie czynności w środowiskach innych niż zacisze domowe mogą wymagać odmiennego podejścia.

Zaczynamy od utworzenia nowego projektu. File –> New Project –> (Visual C#) Web –> ASP.NET Web Application –> w polu na dole podajemy oczywiście nazwę aplikacji. Dla ustalenia uwagi niech będzie to LoginApplication. Przeglądając różne przypadkowo znalezione materiały w Internecie natrafić można na prezentacje i opisy, które w celu włączenia funkcji zarządzania użytkownikami pokazują, że należy wybrać z menu Project ostatnią pozycję, czyli ASP.NET Configuration. Następnie, po przejściu do przeglądarki internetowej, należy skierować się do zakładki Security i już można dodać nowego użytkownika itp. Tak, ale jak zawsze jest jakieś “ale” :). Zamiast opcji konfiguracji użytkowników moim oczom ukazuje się komunikat:

“There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.
The following message may help in diagnosing the problem: Unable to connect to SQL Server database.”

Dla pewności czy nie da się z tym nic zrobić przechodzimy do zakładki Provider, testujemy połączenie i….

“Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.”

Czyli już wiemy, że nie można podłączyć się do bazy danych, ale serwer został dobrze skonfigurowany (co przedstawiłem w jednym z wcześniejszych postów). Komunikat zachęca do zapoznania się narzędziem aspnet_regsql. Służy ono niejako do powiązania bazy danych z naszą aplikacją ASP.NET. Chwila szukania i odpowiedź, co należy zatem zrobić:

  1. Przede wszystkim należy odnależć wyżej wspomniane narzędzie, czyli C:\Windows\Microsoft.Net\  dalej ja framework i odpowiednia wersja. W moim przypadku był to framework64\v4.0.30319\aspnet_regql.exe
  2. Po uruchomieniu wizarda przy domyślnych ustawieniach wybieramy bazę… kolejna niespodzianka, znowu nie można nawiązać połączenia z serwerem. Należy zmienić nazwę w polu Server, gdyż domyślnie znajduje się tam jedynie nazwa komputera, czyli dopisujemy \SQLEXPRESS (nazwa instancji). Teraz już można wybrać bazę danych, w której przechowywane będą ustawienia. Ja na swoje potrzeby utworzyłem wcześniej bazę o nazwie aspnetdb (tak, jest to nazwa domyślnej bazy danych w konfiguracji aplikacji 😉 ) Dwa razy next, potem finish. Wszystko powinno zakończyć się sukcesem.

Powiązanie udało się, Człowiek zadowolony, że baza danych o takiej nazwie jest już domyślnie skonfigurowana w plikach, dodajmy więc nowego użytkownika. Ponownie, tak jak na początku ASP.NET Configuration, security i…. tym razem już komunikat:

“There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.
The following message may help in diagnosing the problem: Could not load type ‚LoginApplication.Global’.”

To już daje domyślenia. Ponownie dłuższa chwila szukania i okazuje się, że bez grzebania w plikach konfiguracyjnych się nie obejdzie.

Interesujące dla tego zagadnienia są dwa pliki. Pierwszy znajduje się w podanym wcześniej katalogu z frameworkiem, z tą różnicą, że zamiast wywoływać polecenie aspnet_regsql należy wejść do katalogu Config i w przyjaznym dla oka edytorze otworzyć plik machine.config. W sekcji connectionStrings znajduje się następujący wpis:

<connectionStrings>
        <add name="LocalSqlServer"
connectionString="data source=.\SQLEXPRESS;
Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;
User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Wszystko wydaje się być w porządku, jednak zwracam uwagę na nazwę, czyli LocalSqlServer. Dla porównania w pliku Web.config dostępnym w katalogu z naszym projektem z poziomu Visual Studio można odnaleźć wpis:

<connectionStrings>
  <add name="ApplicationServices"
       connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
       providerName="System.Data.SqlClient" />
</connectionStrings>

 
Tutaj nazwa dla odmiany to “ApplicationServices”. Widać zatem, że coś nie gra. Moje rozwiązanie: dodanie własnego, następującego wpisu:

<remove name ="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;DataBase=aspnetdb;Uid=AspNetUser;
Pwd=password "/>

oraz zmiana wszystkich wartosci connectionStringName tak, aby znalazł się tam wybrany wpis, czyli LocalSqlServer.

Odrobina wyjaśnień – na początku komendą remove zaznaczamy, że nie chcemy korzystać z wpisu znajdującego się machine.config, a potem dodajemy własny, w którym znajdzie się nazwa wybranej bazy danych, identyfikator użytkownika oraz hasło (wymaga to wcześniejszego utworzenia użytkownika – w moim przypadku AspNetUser, zezwolenia mu na dostęp do serwera i uczynienie go właścicielem wybranej bazy). Widać w tym momencie, że wykorzystane zostało uwierzytelnianie na poziomie SQL Servera, należy więc zadbać także, aby authentication mode w tym pliku został ustawiony na “Forms”.

Pozostaje wybranie z menu Debug opcji Build i już przechodząc do ASP.NET configuration dostajemy możliwość zarządzania użytkownikami 🙂

Uwaga: Ze względu na i tak dość pokaźną długość posta wszelkie nieopisane przeze mnie czynności, niezbędne do wykonania, odnaleźć można w materiale źródłowym, z którego czerpałem swoją wiedzę, czyli: http://www.asp.net/security/videos/configuring-sql-to-work-with-membership-schemas. Rozwiązanie podane przeze mnie ma dla mnie jedną niezaprzeczalną zaletę, mianowicie działa. Zachęcam jednak do komentowania osoby bardziej doświadczone, które mogłyby się wypowiedzieć jak można je udoskonalić.

Wkrótce na blogu zostanie dodany również odnośnik do kodów źródłowych mojej aplikacji, wykorzystujących przedstawione zagadnienie.