ADO.NET – podłączenie się do bazy SQL Server w C#

Tworzenie aplikacji w środowisku .NET, które wykorzystują bazy danych (a zwłaszcza SQL Server) jest stosunkowo proste. Na początek potrzebujemy Visual Studio (najlepiej darmowy express C#) oraz SQL Server (też najlepiej express) lub SQL Server Client (w przypadku gdy SQL Server nie jest zainstalowany na komputerze na którym prowadzimy prace developerskie).

Ten wpis zawiera dość długi wstęp dla osób o niskim obeznaniu ze środowiskiem VS, dlatego jeśli jesteś bardziej zaawansowanym użytkownikiem przewiń proszę tekst.

Gotową klasę można pobrać tutaj

Na początek musimy utworzyć projekt w VS, najlepiej wybrać Windows Form Application (jeśli korzystamy z VS innego niż Express proszę zaznaczyć język C#).

Poniższe Screenshoty pochodzą z wersji VS 2010 Professional.

Utworzenie nowego projektu:

Po utworzeniu projektu naszym oczom powinna ukazać się formatka (form1) nazywana skrótowo formą:

Z lewej strony znajduje się toolbox (tam znajdziemy różne kontrolki). Metodą przeciągnij upuść można na formę bezpośrednio z Toolboxa przeciągać wszystkie dostępne kontrolki. Proponuję zacząć od Button’a (guzika).

Button znajduje się nad zaznaczonym na screenshocie checkbox’em. Kontrolki są posortowane alfabetycznie.


Finalnie na formie powinien pojawić się button1 nasz guzik. Z prawej strony ekranu powinien być object(solution) explorer i properties.

Po jednokrotnym kliknięciu guzika na formie w properties zobaczymy jego właściwości. Wszystkie te właściwości są dostępne z poziomu kodu (do którego przejdziemy za chwilę). Proponuję zwrócić uwagę na właściwość (Name) jest to nazwa naszego obiektu. Możemy ją zmienić – co zalecam uczynić. W momencie gdy guzików pojawi się więcej będzie trudno nad tym zapanować. Ja mój guzik nazwę pobieracz. Jak widać po zmianie nazwy opis guzika w ogóle się nie zmienia, za napis na guziku odpowiada właściwość Text, można ją dowolnie zedytować. Zalecam także przejrzenie innych dostępnych właściwości.
Gdy już skonfigurujemy guzik tak by nam odpowiadał możemy w niego 2 razy kliknąć, otworzony nam się okno z kodem odpowiedzialnym za stworzenie formy, oraz pojawi się tam metoda obsługująca zdarzenie (event) kliknięcia myszą w guzik.
cały kod powinien wyglądać +/- tak:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{

}
}
}

Omawiając kod od samej góry:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

Najprościej rzecz ujmując jest poinformowanie jakich przestrzeni nazw chcemy używać, tworząc obiekt możemy napisać w kodzie System.Data.SqlClient.SqlCommand sqlc = new System.Data.SqlClient.SqlCommand(); albo możemy dodać przestrzeń nazw using System.Data.SqlClient i wtedy w kodzie napiszemy SqlCommand sqlc = new SqlCommand();. Tak więc using wykorzystujemy tylko dla skrócenia zapisów i sprawienia by były bardziej czytelne.
Kolejno mamy:

namespace WindowsFormsApplication2
{

jest to deklaracja naszej własnej przestrzeni nazw. Standardowo VS tworzy przestrzeń nazw tożsamą z nazwą aplikacji/projektu/solucji.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

Teraz mamy deklarację publicznej klasy (public) o nazwie Form1 dziedziczącej (:) z ogólnej klasy Form. Pojawia się jeszcze słowo partial, które informuje kompilator że ta klasa może być deklarowana w wielu plikach. Ten zabieg pozwala zaprezentować nam kod który rzeczywiście jest dla nas istotny, podczas przeciągania kolejnych obiektów na formę VS tworzy wiele linii kodów które są przed nami ukrywane, dla zachowania czystości i pewnej czytelności.

public Form1() to konsturktor klasy Form1, konstruktor poznajemy zawsze po tym że nazywa się tak samo jak klasa oraz konstruktor nie ma definicji tego co zwraca (brak nazwy typu obiektu i słowa void przed nazwą metody). W konstruktorze mamy jedną funkcję która inicjuje nam kontrolki na formie.

Poniżej zostaje nam obsługa kliknięcia w guzik:

private void pobieracz_Click(object sender, EventArgs e)
{

}

moj guzik nazywa się pobierz więc VS automatycznie utworzyło funkcję według konwencji nazewnictwa ze środowiska .NET, pomiędzy klamrami nalezy dopisać kod który zostanie wykonany po kliknięciu guzika. Jako że poniższy post jest de facto o bazach danych a nie podstawach programowania w .NET przejdziemy do pisania kodu który umożliwi nam podłączenie się do bazy danych.

Dla zachowania czystości kodu proponuję utworzyć nową klasę, aby to zrobić należy z górnego menu wyrać project/add new item, albo add class.

VS utworzy nam klasę nazwaną tak samo jak plik w przestrzeni nazw naszej aplikacji:
namespace WindowsFormsApplication2
{
class jodb
{
}
}

Na samej górze w sekcji using warto (należy) dodać:

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

Teraz musimy utworzyć konstruktor, warto się zastanowić w tym miejscu w jaki sposób chcemy łączyć się z bazą danych. Mam na myśli tutaj autentykację windows(domenową) lub autentykację SQL Server. Standardowo SQL Server instalowany jest z włączoną autoryzacją windows. Oznacza to że otwierając połączenie z SQL Serverem zostanie przekazany nasz login do komputera i na tej podstawie zostaną nam przyznane uprawnienia. Autoryzacja SQL Server standardowo jest wyłączona, można ją włączyć w dość prosty sposób (opiszę to w osobnym poście).
Kod który stworzę pozwala na połączenie się jedną i drugą metodą.

W klasie jodb należy stworzyć konstruktory, które w ostatecznym wyglądzie powinny obsłużyć podłączenie się do bazy. Na początek proponuję by kod klasy wyglądał tak:

class jodb
{

public jodb(string user, string pass, string instance, string dbdir)
{

}

public jodb(string instance, string dbdir)
{

}
}

Mimo iż on nic nie robi sensownego, możemy powiedzieć że mamy 2 konstruktory i zależnie od wyboru możemy podłączyć się za pomocą jednej z dwóch metod autentykacji. Warto pamiętać także o komentarzach które pozwalają później w łatwy sposób operować na wytworzonym kodzie. Dodająć /// linię nad deklaracją metody VS wygeneruje nam komentarze które uzupełnione o nasze dopiski będą wyświetlały się w intelisense (podpowiedzi podczas pisania kodu).
Gdy nasz kod wygląda +\- tak możemy przejść do sedna sprawy, musimy utworzyć połączenie do SQL Server. W obu przypadkach posłużymy się obiektem SQLConnection.

Proponuję stworzyć właściwość połączenie, która będzie widoczna dla całej klasy odpowiedzialnej za łączenie się SQL Serverem.

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

W tym celu po klamrze otwierającej definicję klasy dodajemy private (czyli obiekt/właściwość widoczna tylko wewnątrz klasy – SQLConnection -> typ opbiektu/właściwości klasy, oraz polaczenie czyli nazwa naszego obiektu.
W takim wypadku mamy tylko deklarację zmiennej/obiektu bez utworzenia instancji. Teraz w konstruktorach należy dodać utworzenie instancji obiektu SQLConnection oraz przypisać właściwość ConnectionString i otworzyć połączenie. Teraz nasz kod powinien zacząć wyglądać tak:

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji SQL Server
///

///uzytkownik ///haslo ///nazwa instancji ///nazwa bazy danych public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji windows
///

///nazwa instancji ///nazwa bazy danych public jodb(string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
}

Do pełni szczęścia brakują nam jeszcze connectionStringi, czyli ciągi wskazujące na konkretną bazę danych, można je znaleść na connectionstrings.com – ja poniżej przedstawiam te z których korzystam (dla mnie są wystarczające, jednakże istnieje możliwość zdefiniowania większej ilości parametrów).

Po uzupełnieniu ConnectionString nasz kontruktor powinien wyglądać mniej więcej tak:

public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = "user id=" + user + ";" +
"password=" + pass + ";Data Source=" + instance + ";" +
"Trusted_Connection=no;" +
"database=" + dbdir + "; " +
"connection timeout=3";
polaczenie.Open();
}

łańcuch do łączenia z bazą wygląda tak:
“user id=” + user + ;password=” + pass + “;Data Source=” + instance+ “;Trusted_Connection=no;database=” + dbdir + “;connection timeout=3”;

znaki + są konkatenacją (łączeniem łańcuchów) i parametryzują nasz łańcuch tak by podstawić zmienne które podajemy jako argumenty. Powyższy ciąg służy do łączenia za pomocą autentykacji SQL Server.

W drugim kostruktorze stosujemy trochę krótszy ConnectionString:
polaczenie.ConnectionString = "Data Source=" + instance + ";" +
"Trusted_Connection=yes;" +
"database=" + dbdir + "; " +
"connection timeout=3";

Jak widać główna różnica polega na braku użytkownika i hasła oraz zmianie na trusted_connection=yes.
Powyższy kod warto uzupełnić o obsługę błędów Try i catch, ale zrobimy to później, na tym etapie nie jest nam to potrzebne. Na tym etapie warto skompilować projekt (wciskając F5) lub zieloną strzałkę. Jeśli wszystko dobrze pójdzie ujrzymy naszą formatkę jako osobną aplikację. W przeciwnym wypadku musimy usunąć wszystkie błędy. Proszę pamiętać że konstruktory muszą się nazywać tak samo jak klasa więc jeśli klasa nazywa się inaczej niż jodb to konstruktory także. Warto też przeczytać ewentualne komunikaty błędów i sprawdzić w google co one mogą znaczyć. Jeśli na tym etapie coś nie działa proszę o komentarz pod postem, postaram się pomóc.

Jeśli projekt się nam kompiluje trzeba przetestować połączenie.
Przejdźmy do kodu w pliku form1.cs

Do góry na belce mamy listę otwartych plików:

Możemy też w solution explorer kliknąc dwa razy na form1.cs i jak pokaże się forma kliknąc 2 razy guzik lub wcisnąć klawisz F7.

Do funkcji/zdarzenia/metody odpowiedzialnej za kliknięcie guzikiem dopiszmy kod który utworzy nam obiekt do obsługi bazy danych, dodatkowo także wraz z tym obiektem zostanie ustanowione połączenie z bazą.


Jak widać przy dobrym opisaniu własnego kodu, łatwe jest jego późniejsze wykorzystanie.
Teraz nasz kod form1.cs powinien wyglądać podobnie do:
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{
jodb baza = new jodb(“test”, “test”, “localhost\\s2012n”, “zajecia”);
}
}
}

Kompilujemy projekt, jeśli wszystko poszło ok po kliknięciu guzika nic się nie stanie. Jeśli coś jest nie tak albo projekt się nie skompiluje albo po kliknięciu guzika pojawi się wyjątek (exception) który poinformuje nas o błędzie. Warto zwrócić uwagę na podwójny \\ w nazwie instancji, \ jest znakiem wyjścia i znakiem wyjścia dla niego jest on sam stąd podwojenie. Proszę także pamiętać że SQL Server ma standardowo wyłączoną obsługę TCIP co oznacza że nie podłączymy się do niego za pomocą adresu IP ( tylko po nazwie hosta Windows) ew localhost.

To jest moment w którym zaczynają się schody i większośc ludzi ma problem z podłączeniem do bazy, najczęstszymi powodami są literówki w ConnectionString dlatego proszę go dokładnie sprawdzić w razie problemów, lub napisać komentarz postaram się pomóc.

Aby cały projekt miał sens brakuje nam metody która pozwoli pobrać nam dane z bazy. W poprzednich moich postach stworzona została baza zajęcia do której były dołączone dwa zestawy szkoleniowe, w dalszym kodzie będziemy się opierać na tej bazie.

Wracamy do pliku klasy do łączenia się z bazą danych, klikamy jego nazwę do góry na belce lub też klikamy go podwójnie w solution explorer.

Dopisujemy kolejną metodę publiczną, ja ją nazwę pobierz_dane.

///
/// Metoda do pobierana danych z SQL Server
///

///zapytanie sqL /// zwraca dane w obiekcie DataTable
public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable();

return dt;

}


Jak widać powyższy kod opatrzyłem komentarzami. Po słowie public mamy nazwę obiektu (DataTable) który oznacza co metoda zwróci, przygotowałem też wnętrze funkcji (wydmuszka) by było można skompilować projekt. W tym momencie ta metoda nie robi niczego sensownego po prostu za każdym razem zwróci pusty obiekt DataTable.
Za chwilę dopiszemy kod który pozwoli nam pobierać dane.

W tym celupotrzebujemy obiekt SQLCommand, któremu musimy wskazać z jakiego połączenia ma korzystać oraz jakie zapytanie ma wykonać, potrzebujemy także obiekt SQLDataReader, który to odczyta dane z bazy i na koniec trzeba te dane umieścić w obiekcie DataTable.

public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
dr =sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

cała funkcja do pobierania danych powinna wyglądać jak powyżej.

Teraz należy utworzyć obiekt w którym możemy zaprezentować pobrane dane, do tego celu możemy skorzystać z obiektu DataGridView. Można go utworzyć z poziomu kodu, lub też przeciągnąć z toolbox’a. W tym celu klikamy w solution explorer form1.cs dwa razy i jak pojawi się forma klikamy toolbox i przeciągamy DataGridView.
Proszę zwrócić uwagę iż kontrolka DataGridView może nie znajdować się w zakładce common controls.

Gdy już mamy datagridview, (domyślnie VS utworzy nam obiekt o nazwie datagridview1), wchodzimy do kodu form1.cs (przycisk F7 lub podwójny klik na guzik). Przy tworzeniu datagridview VS chce wymusić na nas ustawienia DataSource, ale my z tego świadomie rezygnujemy(nie klikamy w nic na formie co się pojawia wokół Datagridview).

W kodzie form1.cs w funkcji która wykonuje się po kliknięciu guzikiem dopisujemy linijkę kodu:

dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");

Z lewej strony mamy obiekt datagridview1 i jego właściwość Datasource, przypisujemy do niej wynik zwrócony przez metodę pobierz_dane obiektu baza_win (wcześniej był pokazywany obiekt baza, jednak dodałem sobie także obiekt baza_win który łączy się z SQLServer poprzez logowanie windows).
Metoda pobierz_dane przyjmuje jeden argument typu string i jest nim zapytanie SQL.
Cała metoda wygląda tak:

poniżej jeszcze kod:
private void pobieracz_Click(object sender, EventArgs e)
{
// jodb baza = new jodb("test", "test", "localhost\\s2012n", "zajecia");
jodb baza_win = new jodb("localhost\\s2012n", "zajecia");
dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");
}

Teraz zostaje nam tylko skompilować i uruchomić projekt (F5).
Po uruchomieniu programu powinno się pokazać coś podobnego do:

Po kliknięciu guzika powinny w DatagridView pojawić się wyniki: