Cele ćwiczenia
Baza danych procedury na serwerze argo.umg.edu.pl zawiera trzy tabele
CREATE TABLE [dbo].[studenci]
(
[nr] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[nazwisko] [nvarchar](50) NULL,
[imie] [nvarchar](50) NULL,
)
CREATE TABLE [oceny]
( [IdStudenta] [int] NOT NULL REFERENCES [studenci] ([nr]),
[przedmiot] [nvarchar](50) NOT NULL,
[ocena] [nvarchar](50) NULL,
[data] [date] NULL DEFAULT (getdate()),
PRIMARY KEY ([IdStudenta],[przedmiot])
)
CREATE TABLE [dbo].[przedmioty](
[NazwaPrzedmiotu] [nvarchar](50) NOT NULL PRIMARY KEY
)
1. Przy pomocy programu SQL Server Management Studio lub Visual Studio połącz się z bazą danych procedury lub dodaj nowa bazę danych do aplikacji.
2. Jeśli pracujesz z nową bazą danych dodaj do niej tabele studenci i oceny jw.
3. W bazie danych utwórz nową procedurę o nazwie studentxy_NowyStudent, która
a. wstawia do tabeli studenci wiersz zawierający nazwisko i imię studenta przekazywane procedurze jako parametry,
b. wykonuje zapytanie SELECT zwracające dane nowo wpisanego studenta.
CREATE PROCEDURE [dbo].[studentxy_NowyStudent]
@nazwiko nvarchar(50),
@imie nvarchar(50)
AS
BEGIN
INSERT INTO studenci(nazwisko, imie)
VALUES (@nazwiko,@imie)
SELECT nr, nazwisko, imie
FROM studenci
WHERE (nr = @@IDENTITY)
END
4. Przetestuj działanie procedury np. poleceniem:
studentxy_NowyStudent 'Nowak', 'Jan'
5. Utwórz procedurę, która
a. dopisuje studenta do tabeli studenci,
b. dopisuje do tabeli oceny wiersze zawierające identyfikator nowego studenta oraz jego oceny = zdał z przedmiotów Matematyka, Fizyka, J. angielski i WF:
CREATE PROCEDURE student_NowyPlusOceny
@nazwiko nvarchar(50),
@imie nvarchar(50)
AS
BEGIN
DECLARE @ID int
INSERT INTO studenci(nazwisko, imie)
VALUES (@nazwiko,@imie)
SELECT @ID = @@IDENTITY
INSERT INTO oceny
VALUES(@ID,'Matematyka','zdał',DEFAULT)
INSERT INTO oceny
VALUES(@ID,'Fizyka','zdał',DEFAULT)
INSERT INTO oceny
VALUES(@ID,'J. angielski','zdał',DEFAULT)
INSERT INTO oceny
VALUES(@ID,'WF','zdał',DEFAULT)
END
6. Przetestuj działanie procedury.
7. Utwórz tabelę przedmioty zawierającą nazwy przedmiotów, wstaw do niej kilka nazw przedmiotów.
8. Utwórz procedurę która
a. dopisuje studenta do tabeli studenci,
b. wstawia do tabeli oceny wiersze zawierające oceny nowego studenta z przedmiotów wpisanych do tabeli przedmioty (ocena = Zaliczenie do: dzisiejsza data + 100).
CREATE PROCEDURE [dbo].[student_NowyPlusOcenyTab]
@nazwiko nvarchar(50),
@imie nvarchar(50)
AS
BEGIN
DECLARE @ID int
INSERT INTO studenci(nazwisko, imie)
VALUES (@nazwiko,@imie)
SELECT @ID = @@IDENTITY
INSERT INTO oceny
SELECT @ID AS IdStudenta, NazwaPrzedmiotu, 'Zaliczenie do: ' + CONVERT(varchar(15), GETDATE() + 100, 105 ) AS ocena, NULL AS [data]
FROM przedmioty
END
9. Przetestuj działanie procedury.
10. Utwórz i przetestuj procedery:
a. studentxy_NowyStudentNr wstawiającą wiersz do tabeli studenci i zwracającą identyfikator,
b. studentxy_JedenStudent zwracającą dane osobowe studenta o identyfikatorze przekazywanym jako parametr,
c. studentxy_JedenStudentOceny zwracającą dane osobowe i oceny studenta o identyfikatorze przekazywanym jako parametr,
d. studentxy_UsunStudenta usuwającą dane osobowe i oceny studenta o identyfikatorze przekazywanym jako parametr,
e. studentxy_ZmienStudenta zmieniającą dane osobowe studenta o identyfikatorze przekazywanym jako parametr.
1. Na nowej stronie umieść kontrolki:
a. TextBoxNazwisko – kontrolka jest przeznaczona do wpisywania nazwiska,
b. TextBoxImie – kontrolka jest przeznaczona do wpisywania imienia,
c. Label1, Label2 – kontrolki przeznaczone do komunikowania błędów łączności ze źródłem danych,
d. Label3 – kontrolka przeznaczona do komunikowania o ilości wierszy zmienionych przez zapytanie,
e. GridView1 – kontrolka przeznaczona do prezentowania wyników zapytań.
f. Button1 – kliknięcie przycisku będzie tworzyło zapytanie typu CommandType.Text, zawierające nazwę procedury i jej parametry, następnie wykonywało zapytanie,
g. Button2 – kliknięcie przycisku będzie tworzyło zapytanie typu CommandType.StoredProcedure, przekazywało obiektowi klasy SqlCommand nazwę procedury i jej parametry, następnie wykonywało zapytanie.
2. Na listingu 1 przedstawiono przykładowy kod obsługi zdarzeń kliknięcia przycisków Button1, Button2.
Listing 1. Kod obsługi zdarzeń kliknięcia przycisków Button1, Button2
protected void Button1_Click(object sender, EventArgs e)
{
Label1.Text = ""; //usuwanie starych komunikatów
Label2.Text = "";
Label3.Text = "";
string text_zapytania = "student_NowyStudent '" + TextBoxNazwisko.Text + "', '" + TextBoxImie.Text + "'";
//utworzenie nowego obiektu połączenia z bazą danych
SqlConnection polaczenie = new SqlConnection("Data Source=LUKCOM-PC;Initial Catalog=test;Integrated Security=True");
//utworzenie nowego obiektu zapytania SQL
SqlCommand zapytanie = new SqlCommand(text_zapytania);
try
{
polaczenie.Open(); //otwarcie połączenia z bazą danych
zapytanie.Connection = polaczenie; //dodanie zapytania do połączenia
SqlDataReader wynik = zapytanie.ExecuteReader();
GridView1.DataSource = wynik;
GridView1.DataBind();
//komunikat o ilości zmienionych lub dodanych wierszy
int x = wynik.RecordsAffected;
if (x == -1) Label3.Text = "";
else Label3.Text = "Liczba zmienionych lub dodanych wierszy = " + x.ToString();
}
catch (Exception ex)
{
Label1.Text = ex.GetType().ToString(); //wydruk typu błędu
Label2.Text = ex.Message.ToString(); //wydruk błędu
}
finally
{
polaczenie.Close(); //zamknięcie połączenia
}
protected void Button2_Click(object sender, EventArgs e)
{
Label1.Text = ""; //usuwanie starych komunikatów
Label2.Text = "";
Label3.Text = "";
//utworzenie nowego obiektu połączenia z bazą danych
SqlConnection polaczenie = new SqlConnection("Data Source=LUKCOM-PC;Initial Catalog=test;Integrated Security=True");
//utworzenie nowego obiektu zapytania SQL
SqlCommand zapytanie = new SqlCommand();
zapytanie.CommandText = "student_NowyStudent";
zapytanie.CommandType = CommandType.StoredProcedure;
SqlParameter nazwisko = zapytanie.Parameters.AddWithValue("@nazwisko", TextBoxNazwisko.Text);
SqlParameter imie = zapytanie.Parameters.AddWithValue("@imie", TextBoxImie.Text);
try
{
polaczenie.Open(); //otwarcie połączenia z bazą danych
zapytanie.Connection = polaczenie; //dodanie zapytania do połączenia
SqlDataReader wynik = zapytanie.ExecuteReader();
GridView1.DataSource = wynik;
GridView1.DataBind();
int x = wynik.RecordsAffected;
//komunikat o ilości zmienionych lub dodanych wierszy
if (x == -1) Label3.Text = "";
else Label3.Text = "Liczba zmienionych lub dodanych wierszy = " + x.ToString();
}
catch (Exception ex)
{
Label1.Text = ex.GetType().ToString(); //wydruk typu błędu
Label2.Text = ex.Message.ToString(); //wydruk błędu
}
finally
{
polaczenie.Close(); //zamknięcie połączenia
}
}
3. Uruchom stronę i przetestuj jej działanie.
4. Przy pomocy strony przetestuj inne utworzone wcześniej procedury.
5. Dodaj nową stronę umieść na niej kontrolkę GridView i skonfiguruj dla niej SqlDataSource korzystające z utworzonych wcześniej procedur.
6. Przetestuj działanie nowej strony.