Aplikacja .NET C# korzystająca z danych MS SQL Server cz.2

23-sie-2013

W tym artykule pokażę, jak można z aplikacji w c# sięgać do bazy danych SQL. Artykuł jest częścią serii artykułów na ten temat prezentujących różne sposoby.

PRACA Z POLECENIAMI SQL

Z góry uprzedzam, że lepszym rozwiązaniem wydaje się jednak generowanie odpowiedniego DataSet i korzystanie ze strongly typed dataset. Dzięki temu unikniesz literówek, których tu można wygenerować dziesiątki…

Wyświetlenie rekordów

Zakładam, że mamy bazę danych i właśnie piszemy aplikację, która powinna dane z SQL Servera pobierać i modyfikować.

Twój program musi wiedzieć, gdzie znajdują się dane, czyli baza danych. Wskazuje na to tzw. connection string. Każdy projekt .NET ma plik konfiguracyjny, w przypadku ASP.NET jest to web.config. W nim można znaleźć miejsce, gdzie definiuje się connection string-i wykorzystywane w aplikacji:

<?xml version=”1.0″ encoding=”utf-8″?>

<!–

For more information on how to configure your ASP.NET application, please visit

http://go.microsoft.com/fwlink/?LinkId=169433

–>

</connectionStrings>

i.t.d. W tym przypadku widać 3 connection stringi. Jeżeli w aplikacji chcesz skorzystać z danych, zawartych w bazach wskazanych przez ConnectionString, należy najpierw pobrać właściwy Connection String.

Można to zrobić tak:

string connStr = ConfigurationManager.ConnectionStrings[„WandaConnectionString”].ToString();

Dostęp do danych uzyskuje się dzięki obiektowi SqlConnection:

SqlConnection sqlConnection = new SqlConnection(connStr);

Teraz należy napisać swoje zapytanie i umieścić je w obiekcie SqlCommand. Ale uwaga. Zapytanie nie powinno powstać wskutek sklejania tekstu (np. „WHERE DivisionId=”+divisionId+” AND employeeName=”+employeeName;). Takie podejście powodowałoby, że Twój kod polubią hakerzy… (Poczytaj na tym blogu o SQL INJECTION) Zamiast tego należy użyć parametrów. Parametry dodajesz wywołując metodę Add na rzecz kolekcji Parameters obiektu command. Dodając parametr określasz jego typ oraz wielkość (istotne dla napisów). Aby zmienić wartość parametru musisz dodatkowo wypełnić jego pole Value.

SqlCommand sqlCommand = new SqlCommand(„SELECT * FROM dbo.Employee WHERE DivisionId=@companyId”);

sqlCommand.Parameters.Add(„companyId”, System.Data.SqlDbType.Int,4);

sqlCommand.Parameters[„companyId”].Value=1;

Ostatnia czynność na tym etapie to połączenie Command z Connection:

sqlCommand.Connection = sqlConnection;

Wszelkie komendy wysyła się do serwera wykorzystując obiekt adaptera. Trzeba go więc utworzyć i powiązać z komendą:

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);

Teraz dochodzimy do najważniejszej rzeczy, czyli rzeczywistego pobrania rekordów. Potrzebny będzie dataset, w którym umieścisz pobrane dane. Później poleceniem Fill, wykonasz polecenie skonstruowane wyżej i wypełnisz dataseta tabelą, której nazwę podasz wywołując metodę fill:

DataSet ds = new DataSet();

try

{

//sqlConnection.Open();

dataAdapter.Fill(ds,”employee”);

//sqlConnection.Close();

}

catch(Exception ex)

{

txtError = ex.Message;

}

Dane już mamy, teraz podejmij decyzję, co dalej z nimi zrobić, można np tak:

var employeesList = from emp in ds.Tables[„employee”].AsEnumerable()

orderby emp.Field<string>(„Name”)

select emp;

Jak dodać rekord?

Załóżmy, że masz już odpowiednie pola tekstowe na formularzu do odczytania nazwy, wartości itp… Chcesz, aby po kliknięciu OK. rekord zapisał się w bazie. Zróbmy to tak:

string connStr = ConfigurationManager.ConnectionStrings[„WandaConnectionString”].ToString();

using (SqlConnection sqlConnection = new SqlConnection(connStr))
{
int newID;
string cmd = „INSERT INTO dbo.Employee VALUES(@Name,@LastName,@FirstName,”,”,3,”,1,”,GetDate());SELECT CAST(scope_identity() AS int)”;using (var insertCommand = new SqlCommand(cmd, sqlConnection))
{
insertCommand.Parameters.AddWithValue(„@Name”, „John”);
insertCommand.Parameters.AddWithValue(„@LastName”, „Smith”);
insertCommand.Parameters.AddWithValue(„@FirstName”, „John”);

sqlConnection.Open();
newID = (int)insertCommand.ExecuteScalar();
}

}

Po pierwsze trzeba znowu odczytać Connection String znajdujący się w pliku web.config.
Teraz można stworzyć obiekt SqlConnection, który połączy się do serwera bazy danych zgodnie z tym, co jest zapisane w connection stringu.
Polecenie ma wstawić rekord do tabeli i zwrócić nadany mu ID. Dlatego deklarujemy zmienną newID.
Pora na napisanie polecenia INSERT. W moim przypadku w insercie będą występowały 3 parametry (@Name, @LastName, @FirstName). Polecenie INSERT kończy się średnikiem, a po nim polecenie SELECT pobierze wartość scope_identity, która w SQL Server zwraca ostatnio wygenerowany ID w ramach tej sesji.
Pozostaje stworzyć obiekt SqlCommand, który połączy treść polecenia INSERT z wartościami parametrów. Obiekt insertCommand ma kolekcję Parameters, do której należy przekazać wartości parametrów. Tutaj robię to na „sztywno” przekazując napisy, ale mogłem się przecież odwołać do zmiennych.
Pora na otwarcie połączenia i…
… wykonanie komendy poprzez metodę ExecuteScalar. ExecuteScalar zadziała poprawnie, jeżeli komenda zwraca pojedyńczą wartość, a u nas tak właśnie jest. Zwrócone będzie nowo wygenerowane ID.

Jak edytować lub usunąć rekord?

Obie instrukcje są podobne, ponieważ uruchamiają polecenie SQL, ale niczego nie zwracają, więc opiszę je razem.

string connStr = ConfigurationManager.ConnectionStrings[„WandaConnectionString”].ToString();

using (SqlConnection sqlConnection = new SqlConnection(connStr))
{
int numRows;
string cmd = „DELETE FROM dbo.Employee WHERE LastName=@LastName”;
using (var deleteCommand = new SqlCommand(cmd, sqlConnection))
{
deleteCommand.Parameters.AddWithValue(„@LastName”, „Smith”);
sqlConnection.Open();
numRows = (int)deleteCommand.ExecuteNonQuery();
}
}

Znowu zaczynamy od pobrania właściwego Connection String. Widać, że warto pobrać go raz i przechowywać w klasie, jako obiekt wspólny dla wszystkich metod. W oparciu o connection string tworzymy połączenie do serwera.
Deklarujemy zmienną, która będzie przechowywać informację o ilości wykasowanych rekordów
Deklarujemy polecenie DELETE, które zawiera w sobie parametr @LastName
Tworzymy obiekt SqlCommand, który wykorzystuje połączenie do bazy danych.
Przekazujemy parametr do polecenia DELETE (w tym przypadku nazwisko Smith)
Wywołujemy metodę ExecuteNonQuery, która przesyła polecenie do bazy danych i w odpowiedzi zwraca liczbę wykasowanych rekordów. Liczba ta jest sumą liczby wykasowanych rekordów i ewentualnej liczby rekordów zwróconych (affected) poprzez triggery. Gdyby doszło do błędu możesz otrzymać -1. (http://blogs.msdn.com/b/spike/archive/2009/01/27/sqlcommand-executenonquery-returns-1-when-doing-insert-update-delete.aspx)