Lecture 13 ADO.net v1.01

Достъп до данниДостъп до даннис с ADO.NETADO.NET

C# MVP, MCSD.NET, MCDBA, MCTC# MVP, MCSD.NET, MCDBA, MCTТехнически директорТехнически директор, , BSH Ltd.BSH Ltd.C# MVP, MCSD.NET, MCDBA, MCTC# MVP, MCSD.NET, MCDBA, MCTТехнически директорТехнически директор, , BSH Ltd.BSH Ltd.

Програмиране заПрограмиране за .NET Framework .NET Frameworkhttp://http://www.nakov.comwww.nakov.com//dotnetdotnet//

Бранимир ГюровБранимир Гюров

Светлин НаковСветлин НаковНАРС (НАРС (academy.devbg.org))

Лазар КирчевЛазар КирчевИнститут по паралелнаИнститут по паралелнаобработка, БАНобработка, БАН

Стефан ЗахариевСтефан ЗахариевInterConsult BulgariaInterConsult Bulgaria

http://academy.devbg.org/


Необходими знанияНеобходими знания Базови познания за Базови познания за .NET Framework.NET Framework Базови познания за езика Базови познания за езика C#C# Базови познания по Базови познания по XMLXML технологии технологии Познания по релационни бази от данниПознания по релационни бази от данни Познания по езика Познания по езика SQLSQL

СъдържаниеСъдържание

Релационни бази от данниРелационни бази от данни Въведение в Въведение в SQL Server SQL Server Достъп до данни с Достъп до данни с ADO.NETADO.NET

Свързан моделСвързан модел Несвързан моделНесвързан модел

Сигурността при приложенията с Сигурността при приложенията с бази от даннибази от данни

Релационни бази от данни – Релационни бази от данни – съдържание (1)съдържание (1) Модели на базите от данниМодели на базите от данни Релационните бази от данниРелационните бази от данни RDBMSRDBMS системи системи Таблици, връзки, множественост на Таблици, връзки, множественост на

връзките, връзките, E/RE/R диаграми диаграми НормализацияНормализация Ограничения (Ограничения (constraints)constraints) ИндексиИндекси

Езикът SQLЕзикът SQL Съхранени процедури в базата Съхранени процедури в базата

((stored proceduresstored procedures)) Изгледи (views)Изгледи (views) Тригери (triggers)Тригери (triggers) Транзакции и изолацияТранзакции и изолация

Релационни бази от данни – Релационни бази от данни – съдържание (2)съдържание (2)

Релационни бази от данниРелационни бази от данни

Модели на базите от данниМодели на базите от данни йерархичен (дървовиден)йерархичен (дървовиден) мрежовимрежови релационен (табличен)релационен (табличен) обектно-релационенобектно-релационен

Релационните бази от данниРелационните бази от данни Представляват съвкупности от Представляват съвкупности от

таблици и връзки между тяхтаблици и връзки между тях ( (релации)релации) Ползват здрава математическа основа: Ползват здрава математическа основа:

релационната алгебрарелационната алгебра

Системи за управление на БДСистеми за управление на БД

Системи за управление на релационни Системи за управление на релационни бази от данни бази от данни ((СУБД) = СУБД) = Relational Relational Database Management System Database Management System ((RDBMS)RDBMS)

Осигуряват програмни средства за:Осигуряват програмни средства за: създаване / промяна / изтриване на създаване / промяна / изтриване на

таблици и връзки между тяхтаблици и връзки между тях добавяне, промяна, изтриване, търсене добавяне, промяна, изтриване, търсене

и извличане на данни от таблицитеи извличане на данни от таблиците поддръжка на езика поддръжка на езика SQLSQL управление на транзакции управление на транзакции

(незадължително)(незадължително)

Системи за управление на БДСистеми за управление на БД

RDBMS RDBMS системите се наричат ощесистемите се наричат още сървъри за управление на бази от даннисървъри за управление на бази от данни или просто или просто "Database "Database сървърисървъри""

По-известни По-известни RDBMS RDBMS сървъри:сървъри: Microsoft SQL ServerMicrosoft SQL Server Oracle DatabaseOracle Database IBM DB2IBM DB2 PostgreSQLPostgreSQL MySQLMySQL Borland InterbaseBorland Interbase

ТаблициТаблици

Таблиците представляват съвкупност Таблиците представляват съвкупност от стойности, подредени в редове и от стойности, подредени в редове и колони. Пример (таблица колони. Пример (таблица PERSONS)PERSONS)::

Редовете имат еднаква структураРедовете имат еднаква структура Колоните имат име и тип (число, Колоните имат име и тип (число,

символен низ, дата или др.)символен низ, дата или др.)

idid namename familyfamily employeremployer11 СветлинСветлин НаковНаков БАРСБАРС

22 БранимирБранимир ГюровГюров BSHBSH

33 МартинМартин КуловКулов CodeAttestCodeAttest

Схема на таблицаСхема на таблица

Схема на таблица е наредена Схема на таблица е наредена последователност от описания на последователност от описания на колони (име и тип)колони (име и тип)

Например таблицатаНапример таблицата PERSONSPERSONS има има следната схема:следната схема:

PERSONS (PERSONS ( idid:: число,число, namename: символен низ,: символен низ, familyfamily: символен низ,: символен низ, employer: employer: символен низсимволен низ))

Първичен ключПървичен ключ

Първичният ключ Първичният ключ (primary key) (primary key) ее колона колона от таблицата, която уникално от таблицата, която уникално идентифицира даден неин редидентифицира даден неин ред

Два записа (реда) са различни когато са Два записа (реда) са различни когато са различни първичните им ключоверазлични първичните им ключове

Първичният ключ може да е съвкупност Първичният ключ може да е съвкупност от няколко колониот няколко колони

idid namename familyfamily employeremployer11 СветлинСветлин НаковНаков БАРСБАРС

22 БранимирБранимир ГюровГюров BSHBSH

33 МартинМартин КуловКулов CodeAttestCodeAttest

Primary Primary keykey

Връзки (релации)Връзки (релации)

Връзките между таблиците се базират на Връзките между таблиците се базират на взаимоотношения взаимоотношения primary key / foreign keyprimary key / foreign key

idid namename country_idcountry_id11 СофияСофия 11

22 ПловдивПловдив 11

33 МюнхенМюнхен 22

44 БерлинБерлин 22

55 МоскваМосква 33

idid namename11 БългарияБългария

22 ГерманияГермания

33 РусияРусия

TOWNTOWN

COUNTRYCOUNTRY



Foreign Foreign keykey

Връзки (релации)Връзки (релации)

Външният ключ (Външният ключ (foreign key) foreign key) е номер на е номер на запис запис (primary key) (primary key) в друга таблицав друга таблица

Връзките спестяват повтарянето на Връзките спестяват повтарянето на информацияинформация В примера името на държавата не се В примера името на държавата не се

повтаря за всеки градповтаря за всеки град

Връзките имат множественост Връзките имат множественост ((multiplicity)multiplicity) : : 11 x 1 – x 1 – например човек / име на човекнапример човек / име на човек 1 x 1 x много – държава / градовемного – държава / градове многомного x x много – студент / учебен курсмного – студент / учебен курс

Множественост на връзкитеМножественост на връзките

idid namename popul_idpopul_id11 СофияСофия 11





Връзка Връзка 1 x 11 x 1 1 запис от едната таблица съответства на 1 запис от едната таблица съответства на

точно 1 запис от другата таблицаточно 1 запис от другата таблица Рядко се използва – няма смисълРядко се използва – няма смисъл

TOWNTOWN

idid populationpopulation11 1 177 0001 177 000

22 720 000720 000

33 1 260 0001 260 000

44 3 400 0003 400 000

55 11 800 00011 800 000

POPULATIONPOPULATION


Връзка Връзка 1 x 1 x много (или много много (или много xx 1)1) 1 запис от първата таблица съответства 1 запис от първата таблица съответства

на много записи от втората таблицана много записи от втората таблица Използва се много честоИзползва се много често

idid namename country_idcountry_id11 СофияСофия 11





idid namename11 БългарияБългария

22 ГерманияГермания


TOWNTOWN

COUNTRYCOUNTRY


Връзка многоВръзка много x x многомного 1 запис от първата таблица съответства 1 запис от първата таблица съответства

на много записи от втората таблица и на много записи от втората таблица и обратнотообратното

Реализира се чрез междинна таблицаРеализира се чрез междинна таблица

idid namename11 ПешоПешо

22 МинкаМинка

33 ГошоГошо

44 ПенкаПенка

idid namename11 .NET.NET

22 JavaJava

33 PHPPHP

STUDENTSTUDENTCOURSECOURSEstudent_idstudent_id course_idcourse_id

11 11

11 22

33 22

33 33

44 22

STUDENT_COURSESTUDENT_COURSE

Релационна схемаРелационна схема

Релационна схема на БД наричаме Релационна схема на БД наричаме съвкупността от:съвкупността от: схемите на всички таблицисхемите на всички таблици връзките между таблицитевръзките между таблиците

Релационната схема описва Релационната схема описва структурата на БДструктурата на БД не съдържа данни, а само метаданнине съдържа данни, а само метаданни

Релационните схеми се изобразяват Релационните схеми се изобразяват графично чрез графично чрез Entity/Relationship Entity/Relationship диаграми (диаграми (E/R Diagrams)E/R Diagrams)

E/R E/R диаграми – примердиаграми – примерДиаграмата е Диаграмата е създадена с създадена с

Microsoft SQL Server Microsoft SQL Server Enterprise ManagerEnterprise Manager

E/R E/R диаграми – примердиаграми – пример

Диаграмата е Диаграмата е създадена с създадена с

PLATINUM ERwinPLATINUM ERwin

E/R E/R диаграми – примердиаграми – примерДиаграмата е създадена Диаграмата е създадена с fabFORCE DB Designerс fabFORCE DB Designer

Инструменти за Инструменти за E/RE/R дизайн дизайн

E/R диаграмите се създават с E/R диаграмите се създават с инструменти за моделиране на данни инструменти за моделиране на данни (Data Modeling Tools):(Data Modeling Tools): Microsoft VisioMicrosoft Visio Oracle DesignerOracle Designer Computer Associates EComputer Associates ERRwinwin SQL Server Enterprise ManagerSQL Server Enterprise Manager IBM Rational RoseIBM Rational Rose theKompany Data ArchitecttheKompany Data Architect fabForce DBDesigner (GNU GPfabForce DBDesigner (GNU GPLL проект с проект с

отворен код за Windows и Linux)отворен код за Windows и Linux)

НормализацияНормализация

Нормализацията на релационната Нормализацията на релационната схема премахва повтарящите се даннисхема премахва повтарящите се данни

Денормализираните данни съдържат Денормализираните данни съдържат много повторениямного повторения. . Например:Например:

продуктпродукт произво-произво-дителдител

ценацена категориякатегория магазинмагазин градград

кисело кисело млякомляко

Млекис Млекис ООДООД

0.670.67 хранителни хранителни стокистоки

супермаркет супермаркет "Менте""Менте"

СофияСофия

хляб хляб "Добружда""Добружда"

Фурна Фурна "Пушека""Пушека"

0.550.55 хранителни хранителни стокистоки

супермаркет супермаркет "Менте""Менте"

СофияСофия

бира бира "Загорка""Загорка"

Загорка АДЗагорка АД 0.580.58 безалкох. безалкох. напиткинапитки

павилион павилион "24 часа""24 часа"

ВарнаВарна

бира бира "Tuborg""Tuborg"

Шуменско Шуменско пиво АДпиво АД

0.670.67 безалкох. безалкох. напиткинапитки

павилион павилион "24 часа""24 часа"

ВарнаВарна


1-ва нормална форма1-ва нормална форма Данните имат табличен видДанните имат табличен вид Полетата в редовете са атомарни Полетата в редовете са атомарни

(неделими) стойности(неделими) стойности Няма повторения на данни в Няма повторения на данни в

рамките на един редрамките на един ред Дефиниран е първичен ключ за Дефиниран е първичен ключ за

всяка таблицавсяка таблица

книгакнига ISBNISBN ( (PK)PK) авторавтор авторавтор__emailemail

.NET Framework.NET Framework 38470284373847028437 Бай КироБай Киро [email protected]@abv.bg

Beginning SQLBeginning SQL 72345344507234534450 Дядо МразДядо Мраз [email protected]@mraz.org


2-ра нормална форма2-ра нормална форма Запазва изискванията на 1-ва Запазва изискванията на 1-ва

нормална форманормална форма В таблиците няма колони, зависещи В таблиците няма колони, зависещи

от част от първичния ключ (ако е от част от първичния ключ (ако е съставен от няколко колони)съставен от няколко колони)

книгакнига ( (PK)PK) авторавтор (PK) (PK) ценацена авторавтор__emailemail

.NET Framework.NET Framework Бай КироБай Киро 37.2537.25 [email protected]@abv.bg

Beginning SQLBeginning SQL Дядо МразДядо Мраз 19.9519.95 [email protected]@mraz.org

Цената зависи Цената зависи от книгатаот книгата

E-mailE-mail-ът зависи -ът зависи от автораот автора


3-та нормална форма3-та нормална форма Запазва изискванията на 2-ра Запазва изискванията на 2-ра

нормална форманормална форма Единствените зависимости между Единствените зависимости между

колоните са "колона зависи от колоните са "колона зависи от първичния ключ"първичния ключ"

idid продуктпродукт произвопроизводителдител_id_id

ценацена категокатегориярия_id_id

магазмагазинин_id_id

градград_id_id

11 кисело млякокисело мляко 22 0.670.67 22 44 11

22 хляб "Добружда"хляб "Добружда" 33 0.550.55 22 44 11

33 ракия ракия "Пещерска""Пещерска"

66 44..3838 55 22 11

44 бира "Tuborg"бира "Tuborg" 44 0.670.67 44 11 33


44-та нормална форма-та нормална форма Запазва изискванията на 3-та Запазва изискванията на 3-та

нормална форманормална форма В таблиците има най-много една В таблиците има най-много една

колона, съдържаща атрибут с колона, съдържаща атрибут с няколко възможни стойности няколко възможни стойности (multi-(multi-valued attribute) valued attribute) за един ключза един ключ

авторавтор_id_id книгакнига статиястатия

22 ..NET ProgrammingNET Programming Regular Expressions in .NETRegular Expressions in .NET

44 Mastering J2EEMastering J2EE Best Practices in J2EEBest Practices in J2EE

Един автор има Един автор има много книгимного книги

Един автор има Един автор има много статиимного статии

НормализацияНормализация Пример за нормализирана схемаПример за нормализирана схема

((в 4-та нормална форма):в 4-та нормална форма):

idid имеиме

22 "Млекс" ООД"Млекс" ООД

44 "Загорка" АД"Загорка" АД

idid имеиме

44 бирабира

22 хранителнихранителни

idid продуктпродукт произвопроизводителдител_id_id

ценацена категокатегориярия_id_id

магазмагазинин_id_id

градград_id_id

11 кисело млякокисело мляко 22 0.670.67 22 44 11

22 хляб "Добружда"хляб "Добружда" 33 0.550.55 22 44 11

33 ракия ракия "Пещерска""Пещерска"

66 44..3838 55 22 11

44 бира "Tuborg"бира "Tuborg" 44 0.670.67 44 11 33

idid имеиме

11 BillaBilla

44 METROMETRO

idid имеиме

11 СофияСофия

33 ВарнаВарна

PRODUCTPRODUCT

VENDORVENDOR CATEGORYCATEGORY STORESTORE TOWNTOWN

ОграниченияОграничения (Constraints) (Constraints) Ограниченията (Ограниченията (constraints)constraints) задават задават

правила, за данните, които не могат правила, за данните, които не могат да бъдат нарушаванида бъдат нарушавани

Ограничение по първичен ключ Ограничение по първичен ключ ((primary key constraint)primary key constraint) Първичният ключ е уникален за всеки Първичният ключ е уникален за всеки

записзапис

Ограничение по уникален ключ Ограничение по уникален ключ ((unique key constraint)unique key constraint) Стойностите в дадена колона (или Стойностите в дадена колона (или

група колони) са уникалнигрупа колони) са уникални

ОграниченияОграничения (Constraints) (Constraints)

Ограничение по външен ключ Ограничение по външен ключ ((foreign key constraint)foreign key constraint) Стойността в дадена колона е ключ от Стойността в дадена колона е ключ от

друга таблица друга таблица Ограничение по стойност (Ограничение по стойност (check check

constraint)constraint) Стойностите в дадена колонаСтойностите в дадена колона

изпълняват дадено условиеизпълняват дадено условие Например:Например:

((hour>=0)hour>=0) ANDAND (hour<=24)(hour<=24) name = upper(name)name = upper(name)

ИндексиИндекси

Индексите ускоряват скоростта на Индексите ускоряват скоростта на търсене на стойност в дадена търсене на стойност в дадена колона или група от колониколона или група от колони

Ползват се при големи таблициПолзват се при големи таблици Реализират се най-често с Реализират се най-често с B-B-дървета дървета

или хеш-таблициили хеш-таблици Могат да бъдат външни (извън Могат да бъдат външни (извън

таблицата) или вграденитаблицата) или вградени Добавянето и изтриването от Добавянето и изтриването от

индексирани таблици е по-бавноиндексирани таблици е по-бавно

Езикът Езикът SQLSQL

SQL (Structured Query Language)SQL (Structured Query Language) Стандартизиран декларативен език Стандартизиран декларативен език

(стандарт) за манипулация на (стандарт) за манипулация на релационни бази от даннирелационни бази от данни

SQL-92 – SQL-92 – поддържан от всички поддържан от всички RDBMSRDBMS SQL-99 –SQL-99 – навлиза все повече навлиза все повече

SQL SQL поддържа:поддържа: Създаване, промяна, изтриване на Създаване, промяна, изтриване на

таблици и други обекти в БДтаблици и други обекти в БД Търсене, извличане, добавяне, Търсене, извличане, добавяне,

промяна и изтриване на даннипромяна и изтриване на данни

Езикът Езикът SQLSQL

SQL SQL се състои отсе състои от:: DDL – Data Definition LanguageDDL – Data Definition Language

Команди Команди CREATE, ALTER, DROPCREATE, ALTER, DROP DML – Data Manipulation LanguageDML – Data Manipulation Language

Команди Команди SELECT, INSERT, SELECT, INSERT, UPDATE, DELETEUPDATE, DELETE

ПримерПример за за SQL SELECT SQL SELECT заявка:заявка:

SELECT Town.name, Country.nameSELECT Town.name, Country.nameFROM Town, CountryFROM Town, CountryWHERE Town.country_id = Country.idWHERE Town.country_id = Country.id

Stored proceduresStored procedures

Процедури на ниво база (запазени Процедури на ниво база (запазени процедури, процедури, stored procedures)stored procedures) Програмен код, който се изпълнява в Програмен код, който се изпълнява в

самия сървър за бази даннисамия сървър за бази данни Работят много по-бързо от външен кодРаботят много по-бързо от външен код Данните са локално достъпниДанните са локално достъпни Могат да приемат параметриМогат да приемат параметри Могат да връщат резултатМогат да връщат резултат

единична стойностединична стойност съвкупност от записи (съвкупност от записи (record set)record set)

Stored proceduresStored procedures

Процедури на ниво база се пишат Процедури на ниво база се пишат на език, разширение на на език, разширение на SQLSQL T-SQL T-SQL – в – в Microsoft SQLMicrosoft SQL ServerServer PL/SQL – PL/SQL – в в OracleOracle

Пример за процедура на Пример за процедура на T-SQLT-SQL::

CREATE PROCEDURE sp_GetInventoryCREATE PROCEDURE sp_GetInventory @location varchar(10) @location varchar(10)ASAS SELECT Product, Quantity SELECT Product, Quantity FROM Inventory FROM Inventory WHERE Warehouse = @location WHERE Warehouse = @location

Изгледи (Изгледи (views)views)

Изгледите представляват Изгледите представляват именувани именувани SQL SELECT SQL SELECT заявки, заявки, които се използват като таблицикоито се използват като таблици

Улесняват писането на сложни Улесняват писането на сложни SQL SQL заявкизаявки

Прилагат се за фина настройка на Прилагат се за фина настройка на сигурността:сигурността: На даден потребител не се дават права На даден потребител не се дават права

над никоя таблицанад никоя таблица Дават му се права само над някои Дават му се права само над някои

изгледи (подмножество от данните)изгледи (подмножество от данните)

Изгледи (Изгледи (views) – views) – примерпример

idid companycompanytowntown_id_id

11 Менте ООДМенте ООД 11

22 BulkSoft Inc.BulkSoft Inc. 22

33 ХардСофт АДХардСофт АД 44

44 Спутник АДСпутник АД 33

idid towntowncountcountry_idry_id

11 СофияСофия 11

22 New YorkNew York 33



T_COMPANYT_COMPANY T_TOWNT_TOWN

idid countrycountry11 БългарияБългария


33 САЩСАЩ

T_COUNTRYT_COUNTRY

CREATE VIEW V_BG_COMPANY ASCREATE VIEW V_BG_COMPANY AS SELECT SELECT T_COMPANY.id AS id,T_COMPANY.id AS id, T_COMPANY.company AS companyT_COMPANY.company AS company FROM T_COMPANY INNER JOIN FROM T_COMPANY INNER JOIN (T_TOWN INNER JOIN T_COUNTRY ON(T_TOWN INNER JOIN T_COUNTRY ON T_TOWN.country_id=T_COUNTRY.id)T_TOWN.country_id=T_COUNTRY.id) ON T_COMPANY.town_id=T_TOWN.idON T_COMPANY.town_id=T_TOWN.id WHEREWHERE T_COUNTRY.country="България";T_COUNTRY.country="България";

idid companycompany11 Менте ООДМенте ООД

33 ХардСофт АДХардСофт АД

V_BG_COMPANYV_BG_COMPANY

ТригериТригери (triggers) (triggers)

Тригерите (Тригерите (triggers)triggers) са процедури на са процедури на ниво база, които се активират при ниво база, които се активират при някакво условие, например:някакво условие, например: при добавяне на записпри добавяне на запис при промяна на записпри промяна на запис при изтриване на записпри изтриване на запис

Тригерите могат да извършват Тригерите могат да извършват допълнителна обработка на даннитедопълнителна обработка на данните промяна на данните при добавянепромяна на данните при добавяне поддръжка на логове и историяподдръжка на логове и история

Тригери – примерТригери – пример

Имаме таблица с имена на фирми:Имаме таблица с имена на фирми:

Тригер, който при добавяне на новаТригер, който при добавяне на нова фирма добавя фирма добавя "Ltd." "Ltd." към името йкъм името й::

CREATE TABLE COMPANY(CREATE TABLE COMPANY( id int NOT NULL,id int NOT NULL, name varchar(50) NOT NULL)name varchar(50) NOT NULL)

CREATE TRIGGER trg_COMPANY_INSERTCREATE TRIGGER trg_COMPANY_INSERT ON COMPANYON COMPANY FOR INSERTFOR INSERTAS AS UPDATE COMPANY SET name = name + ' Ltd.'UPDATE COMPANY SET name = name + ' Ltd.' WHERE id = (SELECT id FROM inserted)WHERE id = (SELECT id FROM inserted)

ТранзакцииТранзакции

Транзакциите са последователности от Транзакциите са последователности от действия (заявки към базата данни), действия (заявки към базата данни), които се изпълняват атомарно:които се изпълняват атомарно: или се изпълняват всичките действия или се изпълняват всичките действия

заедно (като едно цяло)заедно (като едно цяло) или никое от действията не се или никое от действията не се

изпълнява изобщоизпълнява изобщо Пример:Пример:

Банково прехвърляне на пари от една Банково прехвърляне на пари от една сметка в друга (теглене + внасяне)сметка в друга (теглене + внасяне)

Ако тегленето или внасянето на парите Ако тегленето или внасянето на парите пропадне, пропада цялата операцияпропадне, пропада цялата операция

Отговорности на транзакциитеОтговорности на транзакциите Транзакциите в повечето Транзакциите в повечето RDBMS RDBMS

системи имат 4 отговорности, заради системи имат 4 отговорности, заради които се наричат които се наричат ACIDACID транзакции: транзакции: AAtomicity – tomicity – атомарностатомарност

Изпълнява се всичко или нищоИзпълнява се всичко или нищо CConsistencyonsistency – цялост на данните – цялост на данните

Базата винаги остава консистентнаБазата винаги остава консистентна IIsolationsolation – изолация на данните – изолация на данните

Отделните транзакции са изолирани Отделните транзакции са изолирани една от друга – не се виждатедна от друга – не се виждат

DDurabilityurability – стабилност на данните – стабилност на данните Ако една транзакция бъде потвърдена, Ако една транзакция бъде потвърдена,

тя не може да бъде изгубенатя не може да бъде изгубена

Транзакции – примерТранзакции – пример

Имаме таблица с банкови сметки:Имаме таблица с банкови сметки:

Използваме транзакция при трансфер Използваме транзакция при трансфер на пари от сметка на сметка:на пари от сметка на сметка:

CREATE TABLE CREATE TABLE ACCOUNTACCOUNT(( id int NOT NULL,id int NOT NULL, balancebalance decimaldecimal NOT NULL) NOT NULL)

CREATE PROCEDURE sp_Transfer_Money(CREATE PROCEDURE sp_Transfer_Money( @from_acc int,@from_acc int, @to_acc int,@to_acc int, @ammount decimal@ammount decimal) AS) AS

(примерът продължава)(примерът продължава)

Транзакции – примерТранзакции – примерBEGIN TRANSACTIONBEGIN TRANSACTION

UPDATE ACCOUNT set balance = balance - @ammountUPDATE ACCOUNT set balance = balance - @ammountWHERE id = @from_accWHERE id = @from_acc

IF @@rowcount <> 1 BEGINIF @@rowcount <> 1 BEGIN ROLLBACK TRANSACTIONROLLBACK TRANSACTION RAISERROR ('Invalid source account!', 16, 1)RAISERROR ('Invalid source account!', 16, 1) RETURNRETURNENDEND

UPDATE ACCOUNT set balance = balance + @ammountUPDATE ACCOUNT set balance = balance + @ammountWHERE id = @to_accWHERE id = @to_acc

IF @@rowcount <> 1 BEGINIF @@rowcount <> 1 BEGIN ROLLBACK TRANSACTIONROLLBACK TRANSACTION RAISERROR ('Invalid destination account!', 16, 1)RAISERROR ('Invalid destination account!', 16, 1) RETURNRETURNEND END

COMMIT TRANSACTIONCOMMIT TRANSACTION

Транзакции и изолацияТранзакции и изолация

Транзакциите могат да дефинират Транзакциите могат да дефинират нива на изолация нива на изолация (isolation levels)(isolation levels)

По-силната изолация осигурява по-По-силната изолация осигурява по-добра консистентност, но работи по-добра консистентност, но работи по-бавно и заключва данните за по-дългобавно и заключва данните за по-дълго

ниво наниво наизолацияизолация

четене на четене на непотвър-непотвър-дени даннидени данни

неповто-неповто-ряемост ряемост

при четенепри четенефантомнифантомни

записизаписи

Read uncommittedRead uncommitted дада дада дада

Read committedRead committed нене дада дада

Repeatable readRepeatable read нене нене дада

SerializableSerializable нене нене нене

Употреба на транзакцииУпотреба на транзакции Кога се ползват транзакции?Кога се ползват транзакции?

Винаги, когато за една бизнес Винаги, когато за една бизнес операция се осъществява достъп операция се осъществява достъп до повече от една таблицадо повече от една таблица

Пример: Пример: Добавяне на нов потребител в Добавяне на нов потребител в

таблицата с потребителитетаблицата с потребителите Добавяне на права за новия Добавяне на права за новия

потребител в таблицата с праватапотребител в таблицата с правата Ако добавянето на права не успее, Ако добавянето на права не успее,

трябва да се изтрие и добавеният трябва да се изтрие и добавеният потребител (да се анулира цялата потребител (да се анулира цялата транзакция)транзакция)

Въведение в Въведение в MS SQL ServerMS SQL Server – – съдържаниесъдържание История на SQL ServerИстория на SQL Server Компоненти на SQL Server 2000Компоненти на SQL Server 2000 Програмни среди и инструментиПрограмни среди и инструменти Въведение в T-SQLВъведение в T-SQL

Data Definition Language (DDL) командиData Definition Language (DDL) команди Data Manipulation Language (DML) командиData Manipulation Language (DML) команди Database Console Commands (DBCC) Database Console Commands (DBCC)

командикоманди Съхранени процедуриСъхранени процедури Транзакции в SQL ServerТранзакции в SQL Server

История на SQL ServerИстория на SQL Server

История на продуктовата линияИстория на продуктовата линия Версии на SQL ServerВерсии на SQL Server Версии на SQL Server съвместими Версии на SQL Server съвместими

със със System.Data.SqlClientSystem.Data.SqlClient SQL Server 7.0SQL Server 7.0 SQL Server 2000SQL Server 2000

Системни компоненти на Системни компоненти на SQL SQL Server 2000Server 2000 – услуги – услуги MSSQLSERVER – самият сървърMSSQLSERVER – самият сървър

Основната услуга на SQL Server базата Основната услуга на SQL Server базата данни за инстанцията по подразбиранеданни за инстанцията по подразбиране

SQLSERVERAGENT – SQL Server AgentSQLSERVERAGENT – SQL Server Agent Наблюдава SQL сървъра, извършва Наблюдава SQL сървъра, извършва

периодични действия и докладва за периодични действия и докладва за проблемипроблеми

MSSQLServerADHelperMSSQLServerADHelper Служи за интеграция с Active DirectoryСлужи за интеграция с Active Directory

MSSQLServerOLAPServiceMSSQLServerOLAPService Служи за OLAP анализ на данниСлужи за OLAP анализ на данни

Системни компоненти на Системни компоненти на SQL SQL Server 2000Server 2000 – инструменти – инструменти Enterprise ManagerEnterprise Manager

Цялостна администрация на Цялостна администрация на SQL SQL сървърсървър Менажиране на бази данни (създаване, Менажиране на бази данни (създаване,

backup, backup, възстановяване, конфигуриране)възстановяване, конфигуриране) Менажиране на обекти в базата (таблици, Менажиране на обекти в базата (таблици,

индекси, тригери, процедури, индекси, тригери, процедури, …)…) Query AnalyzerQuery Analyzer

Изпълнение и анализ на Изпълнение и анализ на SQLSQL заявки заявки DTS (Data Transformation Services)DTS (Data Transformation Services)

Извличане, трансформация и импортиране Извличане, трансформация и импортиране на данни от и към външни източницина данни от и към външни източници

Системни компоненти на Системни компоненти на SQL SQL Server 2000Server 2000 – инструменти – инструменти SQL ProfilerSQL Profiler

Проследяване на събития и оптимизация Проследяване на събития и оптимизация на производителността на на производителността на SQL ServerSQL Server

SQL XMLSQL XML Публикуване на данни в Публикуване на данни в Web Web среда (в среда (в

Internet Information Services – IIS)Internet Information Services – IIS) Изпълнява Изпълнява SQL SQL заявки и връща заявки и връща XMLXML

Analysis ManagerAnalysis Manager Компонент за Компонент за Data Warehousing (OLAP)Data Warehousing (OLAP) Извлича информация от многомерни Извлича информация от многомерни

аналитични моделианалитични модели

Програмиране за Програмиране за SQL Server SQL Server Програмни средиПрограмни среди и инструментии инструменти

Visual Studio .NET 2002/2003Visual Studio .NET 2002/2003 SQL Servers SQL Servers в в Server ExplorerServer Explorer-а-а

Работа с таблици, изгледи, Работа с таблици, изгледи, тригери, процедури, диаграмитригери, процедури, диаграми

Създаване / изтриване / промяна Създаване / изтриване / промяна и дебъгване на и дебъгване на stored stored процедурипроцедури

DB DB проектипроекти Query AnalyzerQuery Analyzer

Работа със съхранени процедуриРабота със съхранени процедури Изпълнение наИзпълнение на SQL SQL заявкизаявки Профилиране на Профилиране на SQL SQL заявкизаявки

Демонстрация Демонстрация ##11 Работа сРабота с инструмента инструмента Query Query

AnalyzerAnalyzer на на MS SQL ServerMS SQL Server

Демонстрация Демонстрация ##22 Visual Studio .NET Visual Studio .NET и поддръжката му и поддръжката му

на на SQL ServerSQL Server Работа със Работа със Server ExplorerServer Explorer Работа с таблициРабота с таблици Създаване на Създаване на DB ProjectDB Project Програмиране на Програмиране на T-SQL T-SQL (съхранени (съхранени

процедури във процедури във VS.NET 2003)VS.NET 2003)

Въведение в Въведение в T-SQLT-SQL Видове командиВидове команди

DData ata DDefinition efinition LLanguage (DDL)anguage (DDL) Дефиниция и управление на Дефиниция и управление на

обектите в базата от данни обектите в базата от данни (таблици, изгледи, тригери, ...)(таблици, изгледи, тригери, ...)

DData ata MManipulation anipulation LLanguage (DML)anguage (DML) Работа с данните и обектите, Работа с данните и обектите,

съхранени в базата от данни съхранени в базата от данни (извличане, промяна, ...)(извличане, промяна, ...)

DDataataBBase ase CConsole onsole CCommands (DBCC)ommands (DBCC) Системни съхранени процедуриСистемни съхранени процедури

Data Definition Language (1/2)Data Definition Language (1/2)

MS SQL Server 2000MS SQL Server 2000 поддържа SQL- поддържа SQL-92 DDL, но включва и специфични 92 DDL, но включва и специфични разширенияразширения

Команди за дефиниране и работа сКоманди за дефиниране и работа с Бази от данниБази от данни Таблици, индекси, тригери, Таблици, индекси, тригери, constraintsconstraints ИзгледиИзгледи Съхранени процедуриСъхранени процедури ДругиДруги

Data Definition Language (2/2)Data Definition Language (2/2)

Видове командиВидове команди ДефиниранеДефиниране / / редактиране на обектиредактиране на обекти

CREATECREATE ALTERALTER DROPDROP

Правила за достъпПравила за достъп GRANTGRANT DENYDENY REVOKEREVOKE

Видове Видове DDLDDL команди команди Създаване на обектиСъздаване на обекти

CREATECREATE

USE master USE master GO GO CREATE DATABASE Sales CREATE DATABASE Sales ON ON ( NAME = Sales_dat( NAME = Sales_dataa, , FILENAME = 'c:\mssql\data\ FILENAME = 'c:\mssql\data\SSaleales_s_datdataa.mdf', .mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ONLOG ON( NAME = 'Sales_log', ( NAME = 'Sales_log', FILENAME = 'c:\mssql\data\ FILENAME = 'c:\mssql\data\SSaleales_s_log.ldf', log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GOGO

CreateDatabase.sqlCreateDatabase.sql

Видове Видове DDLDDL команди команди Промяна / дефиниране на обектиПромяна / дефиниране на обекти

ALTERALTER

USE master USE master GOGO

ALTER DATABASE SalesALTER DATABASE SalesADD FILE ADD FILE (( NAME = Sales_idx, NAME = Sales_idx, FILENAME = FILENAME = ''d:\mssql\data\d:\mssql\data\SSaleales_s_idx.ndfidx.ndf'',, SIZE = 5 SIZE = 5MBMB, MAXSIZE = 50, MAXSIZE = 50MBMB, FILEGROWTH = 5, FILEGROWTH = 5MBMB ))

AlterDatabase.sqlAlterDatabase.sql

Видове Видове DDLDDL команди команди

CREATE TABLE UsersCREATE TABLE Users(( UserID int identity NOT NULL, -- PRIMARY KEYUserID int identity NOT NULL, -- PRIMARY KEY FirstName nvarchar(50),FirstName nvarchar(50), LastName nvarchar(50),LastName nvarchar(50), Email varchar(50),Email varchar(50), Phone varchar(20),Phone varchar(20), Phone2 varchar(20),Phone2 varchar(20), Mobile varchar(20)Mobile varchar(20)))GOGO

CREATE CLUSTERED INDEX IDX_USERS_PK CREATE CLUSTERED INDEX IDX_USERS_PK ON Users (UserID ASC)ON Users (UserID ASC)GOGO

AlterDatabase.sqlAlterDatabase.sql

Създаване на таблица и индексСъздаване на таблица и индекс

Видове Видове DDLDDL команди команди

Изтриване на обектиИзтриване на обекти DROPDROP

USE SalesUSE SalesGOGO

DROP TABLE UsersDROP TABLE Users

USE master USE master GOGO

DROP DATABASE SalesDROP DATABASE Sales

DropDatabase.sqlDropDatabase.sql

Видове Видове DDLDDL команди команди Задаване на права за достъпЗадаване на права за достъп

GRANTGRANT – разрешава достъп – разрешава достъп DENYDENY – забранява достъп – забранява достъп REVOKEREVOKE – отменя действието на – отменя действието на

предишни предишни GRANTGRANT и и DENYDENY команди команди

USE Sales USE Sales GOGO

GRANT SELECT ON dbo.Users TO PublicGRANT SELECT ON dbo.Users TO PublicGOGO

DENY INSERT, UPDATE, DELETE ON dbo.Users ТО PublicDENY INSERT, UPDATE, DELETE ON dbo.Users ТО Public

GOGO

GrantPermissions.sqlGrantPermissions.sql

Демонстрация Демонстрация #3#3 DDL DDL команди в команди в SQL Server 2000SQL Server 2000

Data Manipulation LanguageData Manipulation Language SELECTSELECT

INSERTINSERT

UPDATEUPDATE

DELETEDELETE

SELECT * FROM UsersSELECT * FROM Users

INSERT INSERT INTO INTO Users (FirstName, LastName, Email) Users (FirstName, LastName, Email) VALUES ('Branimir', 'Giurov',VALUES ('Branimir', 'Giurov', '[email protected]')'[email protected]')

UPDATE Users UPDATE Users SETSET E Email=mail=''[email protected]'[email protected]'WHERE UserID = 1WHERE UserID = 1

DELETE Users WHERE UserID=1DELETE Users WHERE UserID=1

DMLDML команди команди

SELECTSELECT – извлича данни – извлича данни Синтаксис:Синтаксис:

Пример:Пример:

SELECT SELECT select_listselect_list[INTO [INTO new_table_namenew_table_name]]FROM FROM table_listtable_list[WHERE [WHERE search_conditionssearch_conditions]][GROUP BY [GROUP BY group_by_listgroup_by_list]][HAVING [HAVING search_conditionssearch_conditions]][ORDER BY [ORDER BY order_listorder_list [ASC | DESC]] [ASC | DESC]]

SELECT FirstName, LastName, Mobile as GSMSELECT FirstName, LastName, Mobile as GSMFROM Users ORDER BY LastNameFROM Users ORDER BY LastName

Агрегиращи функцииАгрегиращи функции COUNT(…)COUNT(…) – – връща броя редовевръща броя редове SUM(…)SUM(…) – сума на колона – сума на колона AVG(…)AVG(…) – средноаритметична – средноаритметична

стойност на колонастойност на колона MAX(…)MAX(…) – максимална стойност от – максимална стойност от

колонаколона MIN(…)MIN(…) – минимална стойност от – минимална стойност от

колонаколона

SELECT SELECT COUNT(LastName)COUNT(LastName)FROM Users FROM Users WHERE LastName = WHERE LastName = ‘‘IvanovIvanov''

Съединения (Съединения (JOINJOIN) на таблици) на таблици

Какво е съединение?Какво е съединение? Комбиниране на колони на две или Комбиниране на колони на две или

повече таблици и връщането им във повече таблици и връщането им във виртуална таблица, при изпълнение на виртуална таблица, при изпълнение на командата командата SELECTSELECT

Видове съединенияВидове съединения Вътрешно съединение Вътрешно съединение (INNER JOIN)(INNER JOIN) Външно съединение (Външно съединение (OUTER JOINOUTER JOIN)) Кръстосано съединение (Кръстосано съединение (CROSS JOINCROSS JOIN))

Вътрешно съединение Вътрешно съединение (INNER JOIN)(INNER JOIN)

Връща редовете от две таблици, Връща редовете от две таблици, само ако те имат съответствиесамо ако те имат съответствие

Горната заявка е еквивалентна на:Горната заявка е еквивалентна на:

Използвайте синтаксиса с Използвайте синтаксиса с INNER INNER JOINJOIN

SELECT a.Name, si.Quantity, si.SalePriceSELECT a.Name, si.Quantity, si.SalePriceFROM SaleItem si INNER JOIN Article aFROM SaleItem si INNER JOIN Article a ON si.ArticleID = a.ArticleIDON si.ArticleID = a.ArticleID

SELECT a.Name, si.Quantity, si.SalePriceSELECT a.Name, si.Quantity, si.SalePriceFROM SaleItem si, Article aFROM SaleItem si, Article aWHERE si.ArticleID = a.ArticleIDWHERE si.ArticleID = a.ArticleID

Външно съединение (Външно съединение (OUTER OUTER JOINJOIN)) (1/3) (1/3) Ляво външно съединение (Ляво външно съединение (LEFT LEFT

OUTER JOINOUTER JOIN)) Връща всички редове от първата Връща всички редове от първата

таблица плюс съответстващите им таблица плюс съответстващите им редове от втората таблицаредове от втората таблица

Ако във втората таблица няма Ако във втората таблица няма съответстващи редове, нейните съответстващи редове, нейните колони се връщат със стойност NULL колони се връщат със стойност NULL

SELECT a.Name, si.Quantity, si.SalePriceSELECT a.Name, si.Quantity, si.SalePriceFROM Article a LEFT OUTER JOIN SaleItem siFROM Article a LEFT OUTER JOIN SaleItem si

ON a.ArticleID = si.ArticleIDON a.ArticleID = si.ArticleID

Външно съединение (Външно съединение (OUTER OUTER JOINJOIN)) (2/3) (2/3) Дясно външно съединениеДясно външно съединение (RIGHT (RIGHT

OUTER JOIN)OUTER JOIN) Връща всички редове от втората Връща всички редове от втората

таблица плюс съответстващите им таблица плюс съответстващите им редове от първата таблицаредове от първата таблица

Ако в първата таблица няма Ако в първата таблица няма съответстващи редове, нейните колони съответстващи редове, нейните колони се връщат със стойност NULL се връщат със стойност NULL

SELECT a.Name, si.Quantity, si.SalePriceSELECT a.Name, si.Quantity, si.SalePriceFROM SaleItem siFROM SaleItem si RIGHT OUTER JOIN Article aRIGHT OUTER JOIN Article a

ON si.ArticleID = a.ArticleIDON si.ArticleID = a.ArticleID

Външно съединение (Външно съединение (OUTER OUTER JOINJOIN)) (3/3) (3/3) Пълно външно съединениеПълно външно съединение (FULL (FULL

OUTER JOIN)OUTER JOIN) Връща всички редове от двете таблици, Връща всички редове от двете таблици,

за които има съответствиеза които има съответствие Към тях се прибавят редовете от двете Към тях се прибавят редовете от двете

таблици, за които няма съответствиетаблици, за които няма съответствие Липсващите данни се запълват с Липсващите данни се запълват с

NULLNULL

SELECT a.Name, m.TextSELECT a.Name, m.TextFROM Author a FULL OUTER JOIN Message mFROM Author a FULL OUTER JOIN Message m

ON a.AuthorID = m.AuthorIDON a.AuthorID = m.AuthorID

Кръстосано съединение Кръстосано съединение ((CROSS JOINCROSS JOIN)) Връща комбинация на редовете Връща комбинация на редовете

от таблицитеот таблиците,, участващи в участващи в съединението съединението

SELECT fn.Name FirstName, ln.Name LastNameSELECT fn.Name FirstName, ln.Name LastNameFROM FirstName fnFROM FirstName fn

CROSS JOIN LastName lnCROSS JOIN LastName ln


INSERTINSERT – добавя записи в таблица – добавя записи в таблица СинтаксисСинтаксис::


INSERT [INTO] INSERT [INTO] ((ColumnListColumnList) VALUES () VALUES (ValuesListValuesList))

INSERT Users INSERT Users (FirstName, LastName,(FirstName, LastName, Phone, Mobile, Email) Phone, Mobile, Email) VALUES VALUES ('Branimir', 'Giurov',('Branimir', 'Giurov', '797461', ''797461', '+359 +359 8888 792792 5209',5209', ''[email protected]')[email protected]')


UPDATEUPDATE – обновява записи – обновява записи СинтаксисСинтаксис::


UPDATE UPDATE tabletable__namenameSETSET colname1=value1, colname2=value2 colname1=value1, colname2=value2[WHERE [WHERE conditioncondition]]

UPDATE UsersUPDATE UsersSET SET EmailEmail='[email protected]'='[email protected]'WHERE UserID = 118WHERE UserID = 118


DELETEDELETE – изтрива записи от – изтрива записи от таблицатаблица СинтаксисСинтаксис::


DELETE [FROM] DELETE [FROM] TableName TableName [WHERE [WHERE ConditionCondition]]

DELETE FROM UsersDELETE FROM UsersWHERE UPPER(LastName) = 'GIUROV'WHERE UPPER(LastName) = 'GIUROV'AND Mobile LIKE '+359%'AND Mobile LIKE '+359%'

Демонстрация Демонстрация #4#4 DDММL L команди в команди в SQL Server 2000SQL Server 2000

DBCC DBCC команди в команди в SQL ServerSQL Server DBCC – Database Console CommandsDBCC – Database Console Commands

Първоначално са използвани от Първоначално са използвани от поддръжката на Майкрософтподдръжката на Майкрософт

КатегорииКатегории ПоддръжкаПоддръжка

на задачи върху бази от даннина задачи върху бази от данни индекси и файлови групииндекси и файлови групи

Проверка на статусиПроверка на статуси Валидация на цялостта на даннитеВалидация на цялостта на данните::

за дадена база данниза дадена база данни за таблица, индекс и др.за таблица, индекс и др.

По-важни По-важни DBCCDBCC команди команди За поддръжка:За поддръжка:

DBCC DBREINDEXDBCC DBREINDEX DBCC INDEXDEFRAGDBCC INDEXDEFRAG DBCC SHRINKDATABASEDBCC SHRINKDATABASE DBCC SHRINKFILEDBCC SHRINKFILE DBCC UPDATEUSAGE (sysindexes)DBCC UPDATEUSAGE (sysindexes)

За проверка на статуси:За проверка на статуси: DBCC OPENTRANDBCC OPENTRAN DBCC INPUTBUFFERDBCC INPUTBUFFER DBCC OUTPUTBUFFERDBCC OUTPUTBUFFER DBCC PROCCACHEDBCC PROCCACHE DBCC SQLPERF ( LOGSPACE ) DBCC SQLPERF ( LOGSPACE ) DBCC USEROPTIONSDBCC USEROPTIONS

По-важни По-важни DBCCDBCC команди команди За вЗа валидацияалидация::

DBCC CHECKALLOCDBCC CHECKALLOC DBCC CHECKCATALOGDBCC CHECKCATALOG DBCC CHECKCONSTRAINTSDBCC CHECKCONSTRAINTS DBCC CHECKDBDBCC CHECKDB DBCC CHECKFILEGROUPDBCC CHECKFILEGROUP DBCC CHECKIDENTDBCC CHECKIDENT DBCC CHECKTABLEDBCC CHECKTABLE

ДругиДруги:: DBCC DllName(FREE)DBCC DllName(FREE) DBCC HELPDBCC HELP DBCC PINTABLEDBCC PINTABLE DBCC TRACEON / TRACEOFFDBCC TRACEON / TRACEOFF DBCC UNPINABLEDBCC UNPINABLE

Демонстрация Демонстрация ##55 DBCC DBCC команди в команди в SQL Server 2000SQL Server 2000

Демонстрация Демонстрация ##66 Документацията на Документацията на MS SQL ServerMS SQL Server – –

SQL Server Books OnlineSQL Server Books Online

Съхранени процедуриСъхранени процедури

Съхранени процедури (Stored Съхранени процедури (Stored Procedures)Procedures) Дефиниция и начин на работаДефиниция и начин на работа Системни и потребителски процедуриСистемни и потребителски процедури Конвенция за именуване – Конвенция за именуване – sp_*****sp_***** и и

usp_*****usp_***** Разширени съхранени процедури Разширени съхранени процедури

(Extended Stored Procedures)(Extended Stored Procedures) Дефиниция и начин на работаДефиниция и начин на работа Конвенция за именуване – Конвенция за именуване – xp_*****xp_*****

Съхранени процедуриСъхранени процедури Синтаксис:Синтаксис:


CREATE PROC CREATE PROC procedure_nameprocedure_name [[ { @{ @parameter data_type parameter data_type }} [VARYING] [=[VARYING] [=defaultdefault] [OUTPUT] ] [OUTPUT] ]] [ ,... [ ,...n n ]]ASAS sql_statementsql_statement [ ... [ ...n n ]]

CREATE PROCEDURE CREATE PROCEDURE sp_sp_TopExpensiveProductsTopExpensiveProducts @count int@count intASAS SET ROWCOUNT @countSET ROWCOUNT @count SELECT ProductName, UnitPriceSELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESCFROM Products ORDER BY UnitPrice DESC

Съхранени процедури – примерСъхранени процедури – пример

CREATE PROC sp_InsertAuthorCREATE PROC sp_InsertAuthor(( @au_fname varchar(50),@au_fname varchar(50), @au_lname varchar(50)@au_lname varchar(50)))ASAS

INSERT Authors (au_fname, au_lname, active)INSERT Authors (au_fname, au_lname, active)VALUES (@au_fname, @au_lname, 1)VALUES (@au_fname, @au_lname, 1)

DECLARE @inserted_au_id bigintDECLARE @inserted_au_id bigintSET @inserted_au_id = (SELECT @@identity) SET @inserted_au_id = (SELECT @@identity) SELECT @inserted_au_idSELECT @inserted_au_id

GOGO

Демонстрация Демонстрация #7#7 Създаване, изпълнение и дебъгване Създаване, изпълнение и дебъгване

на съхранена процедура с на съхранена процедура с VS.NETVS.NET

ТранзакцииТранзакции Транзакция – единица работа, която Транзакция – единица работа, която

трябва да бъде извършена атомарнотрябва да бъде извършена атомарно Ако възникнат грешки, транзакцията се Ако възникнат грешки, транзакцията се

“отказва” “отказва” (rollback) (rollback) и промените не се и промените не се нанасятнанасят

Типове транзакцииТипове транзакции Локални – вътрешни за Локални – вътрешни за SQL ServerSQL Server Разпределени – управлявани от Разпределени – управлявани от DTC DTC

(Distributed Transaction Coordinator)(Distributed Transaction Coordinator) Ниво на изолация на транзакциитеНиво на изолация на транзакциите

Определя видимостта на данните Определя видимостта на данните между отделните транзакциимежду отделните транзакции

ТранзакцииТранзакции вв SQL Server SQL Server Команди за работа с транзакции:Команди за работа с транзакции:

BEGIN TRANSACTIONBEGIN TRANSACTION (или (или BEGIN TRANBEGIN TRAN)) Започва нова транзакцияЗапочва нова транзакция

COMMIT TRANSACTIONCOMMIT TRANSACTION Потвърждава текущата транзакцияПотвърждава текущата транзакция

ROLBACK TRANSACTIONROLBACK TRANSACTION Анулира текущата транзакцияАнулира текущата транзакция

SET TRANSACTION ISOLATION LEVELSET TRANSACTION ISOLATION LEVEL Задава нивото на изолация (Задава нивото на изолация (READ READ

COMMITTEDCOMMITTED, , READ UNCOMMITTEDREAD UNCOMMITTED, , REPEATABLE READREPEATABLE READ, , SERIALIZABLESERIALIZABLE))

Транзакции в Транзакции в SQL Server –SQL Server – пример пример

CREATE PROCEDURE sp_InsertUserCREATE PROCEDURE sp_InsertUser(( @userName varchar(50),@userName varchar(50), @userPassHash varchar(50),@userPassHash varchar(50), @groupId bigint@groupId bigint))ASAS BEGIN TRANBEGIN TRAN

INSERT INTO Users (UserName, UserPassHash)INSERT INTO Users (UserName, UserPassHash) VALUES (@userName, @userPassHash)VALUES (@userName, @userPassHash)

IF (@@error = 0)IF (@@error = 0) INSERT UsersGroups (UserId, GroupId)INSERT UsersGroups (UserId, GroupId) VALUES (@@identity, @groupId)VALUES (@@identity, @groupId)

IF (@@error IF (@@error == 0) 0) COMMIT TRANCOMMIT TRAN ELSEELSE ROLLBACK TRANROLLBACK TRAN

Демонстрация Демонстрация #8#8 Транзакции в Транзакции в SQL ServerSQL Server

Демонстрация Демонстрация #9#9 Нива на изолация на транзакциитеНива на изолация на транзакциите

Пренасяне на база данниПренасяне на база данни

Налага се при инсталация на Налага се при инсталация на дадено приложение при клиента, дадено приложение при клиента, който ще го използвакойто ще го използва

Начини за пренасяне на Начини за пренасяне на SQL SQL Server Server база от данни:база от данни: Чрез архивиране и възстановяване Чрез архивиране и възстановяване Чрез откачане и закачане Чрез откачане и закачане

Пренасяне чрез архивиране и Пренасяне чрез архивиране и възстановяваневъзстановяване (1/ (1/55))1. 1. От От Enterprise Manager Enterprise Manager избираме базата избираме базата

данни и от контекстното меню избираме данни и от контекстното меню избираме Backup DatabaseBackup Database

Пренасяне чрез архивиране и Пренасяне чрез архивиране и възстановяваневъзстановяване (2/ (2/55))

2.2. Даваме име на Даваме име на архива и архива и задаваме пътя, задаваме пътя, където ще се където ще се създаде създаде архивното копиеархивното копие

Пренасяне чрез архивиране и Пренасяне чрез архивиране и възстановяваневъзстановяване (3/ (3/55))3. 3. На сървъра приемник избираме На сървъра приемник избираме Restore Restore

Database Database от от Enterprise ManagerEnterprise Manager

Пренасяне чрез архивиране и Пренасяне чрез архивиране и възстановяваневъзстановяване ( (44//55))

4. 4. Задаваме името, Задаваме името, под което ще се под което ще се възстанови възстанови базата от даннибазата от данни

Пренасяне чрез архивиране и Пренасяне чрез архивиране и възстановяваневъзстановяване ( (55//55))

5. Избираме 5. Избираме архива, от архива, от който ще се който ще се възстанови възстанови базата от базата от данниданни

Пренасяне чрез откачане и Пренасяне чрез откачане и закачане (1/3)закачане (1/3)1. В 1. В Enterprise Manager Enterprise Manager избираме базата от избираме базата от

данни и от контекстното меню избираме данни и от контекстното меню избираме Detach DatabaseDetach Database

Пренасяне чрез откачане и Пренасяне чрез откачане и закачане (2/3)закачане (2/3)

22. . Копираме файловете на базата от Копираме файловете на базата от данни от сървъра-източник на данни от сървъра-източник на сървъра-приемник.сървъра-приемник.

Файлове на базата от данни:Файлове на базата от данни: <име_на_база>.mdf<име_на_база>.mdf <име_на_база_log>.ldf<име_на_база_log>.ldf

Пренасяне чрез откачане и Пренасяне чрез откачане и закачане (3/3)закачане (3/3)3. Закачаме базата от данни на сървъра-3. Закачаме базата от данни на сървъра-

приемникприемник

ADO.NETADO.NET – съдържание (1) – съдържание (1) Модели за работа с даннитеМодели за работа с данните

Свързан и несвързанСвързан и несвързан Еволюция на приложениятаЕволюция на приложенията Архитектура на ADO.NETАрхитектура на ADO.NET

Data ProvidersData Providers Връзка с MS SQL ServerВръзка с MS SQL Server

SqlConnectionSqlConnection Реализация на свързан моделРеализация на свързан модел

SqlCommandSqlCommand, , SqlDataReaderSqlDataReader Параметрични заявкиПараметрични заявки

ADO.NETADO.NET – съдържание (2) – съдържание (2) Връзка с други бази от данни през Връзка с други бази от данни през

OLE DBOLE DB Реализация на несвързан моделРеализация на несвързан модел

Класът Класът DataSetDataSet, силно-типизирани , силно-типизирани DataSetDataSet-и-и

Класовете Класовете DataTableDataTable и и DataRelationDataRelation Връзка между ADO.NET и XMLВръзка между ADO.NET и XML КласътКласът DataView DataView Използване на Използване на DataAdapterDataAdapter Типичен сценарий за работа с данни в Типичен сценарий за работа с данни в

несвързана среданесвързана среда

Модели за работа с данниМодели за работа с данни Свързан модел (Свързан модел (connected modelconnected model))

Постоянна връзка с данните (Постоянна връзка с данните (online)online) Случаи на използванеСлучаи на използване Проблеми – лоша скалируемостПроблеми – лоша скалируемост

Несвързан моделНесвързан модел (disconnected model) (disconnected model) Връзката с данните се осъществява Връзката с данните се осъществява

offline – offline – за изтегляне на данни и за изтегляне на данни и нанасяне на променинанасяне на промени по даннитепо данните

Случаи на използванеСлучаи на използване ПримериПримери

Достъп до данни чрез Достъп до данни чрез WebWeb услуга услуга Интеграция с Интеграция с XMLXML

Свързан моделСвързан модел

Реализация на достъп до данни в Реализация на достъп до данни в среда, в която винаги има връзка среда, в която винаги има връзка до източника на даннитедо източника на данните

База отБаза отданниданни

DBDB

ADO.NETADO.NETклиентклиент

постояннапостояннасвързаностсвързаност

Свързан модел – за и противСвързан модел – за и против Предимства:Предимства:

Средата е по-лесна за подсигуряване Средата е по-лесна за подсигуряване (по-малко усилия за разработчика)(по-малко усилия за разработчика)

Контролът върху конкурентният достъп Контролът върху конкурентният достъп се упражнява по-лесносе упражнява по-лесно

По-добра вероятност за работа с По-добра вероятност за работа с текущата версия на даннитетекущата версия на данните

Недостатъци:Недостатъци: Нужда от постоянна мрежова връзкаНужда от постоянна мрежова връзка Проблеми при нужда от скалируемостПроблеми при нужда от скалируемост

Несвързан моделНесвързан модел

Подмножество на данните от Подмножество на данните от централната система за съхранение на централната система за съхранение на данните се копира локално при клиентаданните се копира локално при клиента

База отБаза отданниданни

DBDB

ADO.NETADO.NETклиентклиент

непостояннанепостояннасвързаностсвързаност

Несвързан модел – за и противНесвързан модел – за и против

Предимства:Предимства: Клиентът се свързва, когато има нужда, Клиентът се свързва, когато има нужда,

а в останалото време работи без а в останалото време работи без връзка с централната база от даннивръзка с централната база от данни

Други потребители могат да се Други потребители могат да се свързват междувременносвързват междувременно

Скалируемостта е доста добраСкалируемостта е доста добра

Недостатъци:Недостатъци: Данните не винаги са текущиДанните не винаги са текущи Допълнителни усилия за решаване на Допълнителни усилия за решаване на

конфликтите между различните версииконфликтите между различните версии

Еволюция на приложениятаЕволюция на приложенията

Еднослойни приложенияЕднослойни приложения Най-често работи само един Най-често работи само един

потребителпотребител ПредимстваПредимства

Всички компоненти са на едно мястоВсички компоненти са на едно място НедостатъциНедостатъци

Промяна на функционалността Промяна на функционалността изисква преинсталацияизисква преинсталация

ПримерПример Приложение базирано на Приложение базирано на MS AccessMS Access

Еволюция на приложениятаЕволюция на приложенията Двуслойни приложения (клиент-Двуслойни приложения (клиент-

сървър)сървър) Потребителският интерфейс и бизнес Потребителският интерфейс и бизнес

правилата се дефинират на едно мястоправилата се дефинират на едно място Данните се съхраняват във втория слойДанните се съхраняват във втория слой ПредимстваПредимства

Има разделяне на функционалносттаИма разделяне на функционалността НедостатъциНедостатъци

Лоша скалируемост – проблеми с Лоша скалируемост – проблеми с поддръжката на голям брой клиентиподдръжката на голям брой клиенти

ПримериПримери MS SQL Server MS SQL Server ↔↔ MS Query Analyzer MS Query Analyzer MS Exchange MS Exchange ↔↔ MS Outlook MS Outlook

Еволюция на приложениятаЕволюция на приложенията Трислойни приложенияТрислойни приложения

Различните типове функционалност са в Различните типове функционалност са в различни слоеверазлични слоеве

ПредимстваПредимства Отделяне на функционалността между Отделяне на функционалността между

потребителски интерфейс, бизнес правила потребителски интерфейс, бизнес правила и съхранение / достъп до даннитеи съхранение / достъп до данните

НедостатъциНедостатъци По-трудна поддръжкаПо-трудна поддръжка Повече усилия за осигуряване на Повече усилия за осигуряване на

сигурносттасигурността ПримерПример

ASP.NETASP.NET Web-Web-приложение приложение ↔↔ ASP.NET Web ASP.NET Web услуга услуга ↔↔ MS SQL ServerMS SQL Server

Еволюция на приложениятаЕволюция на приложенията Многослойни приложенияМногослойни приложения

Системи с повече от 3 логически слояСистеми с повече от 3 логически слоя Възможност за добавяне на още слоеве за Възможност за добавяне на още слоеве за

разширяване на функционалносттаразширяване на функционалността ПредимстваПредимства

Възможност за различни приложения да Възможност за различни приложения да достъпват части от функционалността през достъпват части от функционалността през отворени протоколи отворени протоколи

НедостатъциНедостатъци Много труден процес по дефиниране и Много труден процес по дефиниране и

реализация на правила за сигурен достъпреализация на правила за сигурен достъп Изискват повече планиране и по-големи Изискват повече планиране и по-големи

срокове за разработкасрокове за разработка

В търсене на балансаВ търсене на баланса

Брой на слоеветеБрой на слоевете

Ко

мп

лек

сно

ст,

скал

ир

уем

ост

Ко

мп

лек

сно

ст,

скал

ир

уем

ост

Какво е Какво е ADO.NETADO.NET??

Набор от класове за работа с данниНабор от класове за работа с данни Набор от класове, интерфейси, Набор от класове, интерфейси,

структури и други типове за достъп до структури и други типове за достъп до данни през изцяло данни през изцяло .NET .NET базирана базирана реализацияреализация

Програмен модел за работа с данниПрограмен модел за работа с данни Осигурява възможност за работа в Осигурява възможност за работа в

несвързана среданесвързана среда Осигурява връзка с Осигурява връзка с XMLXML Наследник на Наследник на ADOADO ( (Windows Windows

технология за достъп до базитехнология за достъп до бази от данниот данни))

Namespace-Namespace-и на и на ADO.NETADO.NET Пространства от имена от ADO.NETПространства от имена от ADO.NET

System.DataSystem.Data – основни архитектурни – основни архитектурни класове на ADO.NETкласове на ADO.NET

System.Data.CommonSystem.Data.Common – общи класове – общи класове за всички data Provider-иза всички data Provider-и

System.Data.SqlClientSystem.Data.SqlClient и и System.Data.SqlTypesSystem.Data.SqlTypes – Data Provider – Data Provider класове за достъп до SQL Serverкласове за достъп до SQL Server

System.Data.OleDbSystem.Data.OleDb – връзка с OleDB – връзка с OleDB System.Data.OdbcSystem.Data.Odbc – връзка с ODBC – връзка с ODBC System.XmlSystem.Xml – връзка с XML – връзка с XML

Еволюция на Еволюция на ADO ADO към към ADO.NETADO.NET

ADOADO

ConnectionConnection

CommandCommand

RecordsetRecordset

ADO.NETADO.NET

XxxConnectionXxxConnection

XxxCommandXxxCommand

XxxDataReaderXxxDataReader

XxxTransactionXxxTransaction

DataSetDataSet

XxxDataAdapterXxxDataAdapter

Компоненти на Компоненти на ADO.NETADO.NET

SQL Server SQL Server ..NET NET Data ProviderData Provider

OleDbOleDb ..NET NET Data ProviderData Provider

Oracle Oracle ..NET NET Data ProviderData Provider

ODBC ODBC ..NET NET Data ProviderData Provider

SQL Server 7.0 иSQL Sever 2000

OLE DB източници

(SQL Server 6.5,MS Acess и др.)

OracleDatabase

ODBC източници

DataSetDataSet

Disconnected modelDisconnected model

DataReaderDataReader CommandCommand

Connected modelConnected model

DataDataAdapterAdapter

Data Provider-Data Provider-и в и в ADO.NETADO.NET Data Provider-ите са съвкупности от Data Provider-ите са съвкупности от

класове, които осигуряват връзка с класове, които осигуряват връзка с различни бази от данниразлични бази от данни За различните RDBMS системи се За различните RDBMS системи се

използват различни Data Provider-и използват различни Data Provider-и Различните производители използват Различните производители използват

различни протоколи за връзка със различни протоколи за връзка със сървърите за даннисървърите за данни

Дефинират се от 4 основни обекта:Дефинират се от 4 основни обекта: ConnectionConnection – за връзка с базата – за връзка с базата CommandCommand – за изпълнение на SQL – за изпълнение на SQL DataReaderDataReader – за извличане на данни – за извличане на данни DataAdapterDataAdapter – за връзка с – за връзка с DataSetDataSet

Data Provider-Data Provider-и в и в ADO.NETADO.NET В ADO.NET има няколко стандартни В ADO.NET има няколко стандартни

Data Provider-aData Provider-a SqlClientSqlClient – за връзка със SQL Server – за връзка със SQL Server OleDBOleDB – за връзка със стандарта OleDB – за връзка със стандарта OleDB OdbcOdbc – за връзка със стандарта ODBC – за връзка със стандарта ODBC OracleOracle – за връзка с Oracle – за връзка с Oracle

Трети доставчици предлагат Data Трети доставчици предлагат Data Provider-и за връзки с други RDBMS:Provider-и за връзки с други RDBMS: IBM DB2IBM DB2 MySQLMySQL PostgreSQLPostgreSQL Borland Interbase / FirebirdBorland Interbase / Firebird

ADO.NET в свързана средаADO.NET в свързана среда

Данните са на сървъра Данните са на сървъра до затваряне на връзкатадо затваряне на връзката

1.1. Отваряне на връзка Отваряне на връзка ((SqlConnectionSqlConnection))

2.2. Изпълнение на команда Изпълнение на команда / / команди команди ((SqlCommandSqlCommand))

3.3. Обработка на редовете Обработка на редовете получени като резултат получени като резултат от заявката чрез четец от заявката чрез четец ((SqlDataReaderSqlDataReader))

4.4. Затваряне на четецаЗатваряне на четеца

5.5. Затваряне на връзкатаЗатваряне на връзката

SQL Server 7.0 /SQL Sever 2000

SqlConnectionSqlConnection

SqlCommandSqlCommand

SqlDataReaderSqlDataReader

ADO.NET в несвързана средаADO.NET в несвързана среда

Данните се кешират в Данните се кешират в DataSetDataSet обект и връзката обект и връзката се преустановявасе преустановява1.1. Отваряне на връзка Отваряне на връзка

((SqlConnectionSqlConnection))

2.2. Пълнене на Пълнене на DataSetDataSet (чрез (чрез SqlDataAdapter)SqlDataAdapter)


4.4. Работа със Работа със DataSetDataSet-a-a

5.5. Отваряне на връзкаОтваряне на връзка

6.6. Нанасяне на промени по Нанасяне на промени по данните по сървъраданните по сървъра



SqlDataAdapterSqlDataAdapter

DataSetDataSet


ADO.NETADO.NET, , XMLXML и и Web Web услугиуслуги ADO.NET ADO.NET е тясно интегрирано с е тясно интегрирано с XMLXML

Често се използваЧесто се използва в несвързан в несвързан сценарий посредством сценарий посредством Web Web услугиуслуги

WebWeb-услугата реализира бизнес слоя -услугата реализира бизнес слоя на трислойните приложенияна трислойните приложения Извършва бизнес операциите над даннитеИзвършва бизнес операциите над данните

Web Web услугауслуга

Извикване на Web услугаИзвикване на Web услуга Изпълнение на SQL заявкаИзпълнение на SQL заявка

Резултат от SQL заявкаРезултат от SQL заявкаXMLXMLDataSetDataSet

Промяна на данниПромяна на данни

през XMLпрез XMLDataSetDataSet

Заявки за нанасянеЗаявки за нанасяне

на променитена променитеКлиентКлиентБаза от данниБаза от данни

DBDBDataSetDataSet

SqlClient Data ProviderSqlClient Data Provider SqlConnectionSqlConnection – осъществява връзката с – осъществява връзката с

MS SQL Server MS SQL Server SqlCommandSqlCommand – изпълнява команди върху – изпълнява команди върху

SQL Server-а през вече установена връзкаSQL Server-а през вече установена връзка SqlDataReaderSqlDataReader – служи за извличане на – служи за извличане на

данни от SQL Server-аданни от SQL Server-а Данните са резултат от изпълнена командаДанните са резултат от изпълнена команда

SqlDataAdapterSqlDataAdapter – обменя данни между – обменя данни между DataSetDataSet обекти и обекти и SQL ServerSQL Server Осигурява зареждане на Осигурява зареждане на DataSetDataSet с данни и с данни и

обновяване на променени данниобновяване на променени данни Може да се грижи сам за състоянието на Може да се грижи сам за състоянието на

връзката с базата даннивръзката с базата данни

Система за сигурност в Система за сигурност в SQL SQL Server 2000Server 2000 Видове автентикацияВидове автентикация

Windows Windows (чрез потребител в ОС)(чрез потребител в ОС) Сигурна валидация и криптиранеСигурна валидация и криптиране Одитиране на достъпОдитиране на достъп Политики за паролиПолитики за пароли Заключване на акаунтиЗаключване на акаунти

Смесена (Смесена (Windows Windows и и SQLSQL Server)Server) Основно за съвместимост с Основно за съвместимост с

предишни версиипредишни версии Малка разлика в скоросттаМалка разлика в скоростта

Символен низ за връзка с база Символен низ за връзка с база от данни (от данни (ConnectionConnection String)String) Дефинира параметрите, необходими за Дефинира параметрите, необходими за

осъществяване на връзка с базата данниосъществяване на връзка с базата данни Основни параметриОсновни параметри

ProviderProvider – име на драйвера за достъп – име на драйвера за достъп Data SourceData Source – идентификатор на базата – идентификатор на базата User ID/PasswordUser ID/Password Integrated SecurityIntegrated Security Persist Security Info (False)Persist Security Info (False)


Server=localhost; Server=localhost; DDatabase=Pubs; atabase=Pubs; Integrated Security=true; Persist Integrated Security=true; Persist Security Info=falseSecurity Info=false

SqlConnectionSqlConnection – пример – пример

const string CONNECTION_STRING =const string CONNECTION_STRING = "Server=localhost; Database=Northwind; " +"Server=localhost; Database=Northwind; " + "Integrated Security=true; " +"Integrated Security=true; " + "Persist Security Info=false";"Persist Security Info=false";

// Create the connection// Create the connectionSqlConnection con = SqlConnection con = new SqlConnection(CONNECTION_STRING);new SqlConnection(CONNECTION_STRING);using (con)using (con){{ // Open connection// Open connection con.Open();con.Open();

// Use the connection here// Use the connection here // ...// ...}}

Connection PoolingConnection Pooling "Database Connection Pooling" е програмна "Database Connection Pooling" е програмна

техника, която:техника, която: Осигурява по-ефективно използване на Осигурява по-ефективно използване на

връзките към базата от даннивръзките към базата от данни Подобрява производителносттаПодобрява производителността

Пулингът работи така:Пулингът работи така: При отваряне на връзка тя се взима готова При отваряне на връзка тя се взима готова

от т. нар. "пул" (connection pool)от т. нар. "пул" (connection pool) Ако там няма свободна се отваря новаАко там няма свободна се отваря нова

При затваряне на връзка тя се връща в При затваряне на връзка тя се връща в пула без да се прекъсва физическипула без да се прекъсва физически

По подразбиране SqlClient ползва пулПо подразбиране SqlClient ползва пул

Още за работата със Още за работата със SqlConnectionSqlConnection Експлицитно отваряне и затваряне Експлицитно отваряне и затваряне

на връзката (работи с пула)на връзката (работи с пула) Open()Open() и и Close()Close() методите методите

Имплицитно отваряне и затваряне Имплицитно отваряне и затваряне на връзкатана връзката ( (работи с пула)работи с пула) Възможно е само при работа с Възможно е само при работа с

DataAdapterDataAdapter-и-и

Използване на Използване на Dispose()Dispose() метода метода Извиква се ако ползваме Извиква се ако ползваме using (con)using (con) Връща връзката в connection pool-аВръща връзката в connection pool-а

Събитията на Събитията на SqlConnectionSqlConnection Събития възникващи от връзката:Събития възникващи от връзката:

StateChangeStateChange и и InfoMessageInfoMessage

StateChangeEventArgsStateChangeEventArgs класът класът Дава информация за това какво се е Дава информация за това какво се е

случило с връзката към базата даннислучило с връзката към базата данни CurrentStateCurrentState и и OriginalStateOriginalState

SqlInfoMessageEventArgsSqlInfoMessageEventArgs ErrorsErrors – – SqlErrorCollectionSqlErrorCollection колекция от колекция от

съобщения за грешки и предупреждениясъобщения за грешки и предупреждения MessageMessage – съобщението за грешка – съобщението за грешка SourceSource – къде е възникнала – къде е възникнала

грешката/предупреждениетогрешката/предупреждението

StateChangeStateChange – пример – примерprivate const string CONNECTION_STRING =private const string CONNECTION_STRING = "Server="Server=..;;" +" + "" Database=Pubs; Integrated Security=true"; Database=Pubs; Integrated Security=true";

private SqlConnection mConn;private SqlConnection mConn;

private void InitConnection()private void InitConnection(){{ mConn = new SqlConnection(CONNECTION_STRING);mConn = new SqlConnection(CONNECTION_STRING); mConn.StateChange += mConn.StateChange += new StateChangeEventHandler(ConnStateChange);new StateChangeEventHandler(ConnStateChange); mConn.Open();mConn.Open();}}

private void ConnStateChange(object sender,private void ConnStateChange(object sender, StateChangeEventArgs e)StateChangeEventArgs e){{ Debug.WriteLine("SQL Server connection: " +Debug.WriteLine("SQL Server connection: " + e.OriginalState.ToString() + " --> " +e.OriginalState.ToString() + " --> " + e.CurrentState.ToString());e.CurrentState.ToString());}}

InfoMessageInfoMessage – пример – примерprivate const string CONNECTION_STRING = "Server=.;private const string CONNECTION_STRING = "Server=.; " +" + "Database=Pubs; Integrated Security=true";"Database=Pubs; Integrated Security=true";

private SqlConnection mConn;private SqlConnection mConn;

private void InitConnection()private void InitConnection(){{ mConn = new SqlConnection(CONNECTION_STRING);mConn = new SqlConnection(CONNECTION_STRING); mConn.InfoMessage +=mConn.InfoMessage += new new SqlInfoMessageEventHandler(ConnInfoMessage);SqlInfoMessageEventHandler(ConnInfoMessage); mConn.Open();mConn.Open();}}

private void ConnInfoMessage(object sender, private void ConnInfoMessage(object sender, SqlInfoMessageEventArgs e)SqlInfoMessageEventArgs e){{ Debug.WriteLine("SQL Server message: " + Debug.WriteLine("SQL Server message: " + e.Message + " Source: " + e.Source);e.Message + " Source: " + e.Source);}}

Демонстрация Демонстрация ##1010 Проследяване на събитията Проследяване на събитията

StateChangeStateChange и и InfoMessageInfoMessage

Реализация на свързан моделРеализация на свързан модел

Данните са на сървъра Данните са на сървъра до затваряне на връзкатадо затваряне на връзката

1.1. Отваряне на връзка Отваряне на връзка ((SqlConnectionSqlConnection))

2.2. Изпълнение на команда Изпълнение на команда / / команди команди ((SqlCommandSqlCommand))

3.3. Обработка на редовете Обработка на редовете получени като резултат получени като резултат от заявката чрез четец от заявката чрез четец ((SqlDataReaderSqlDataReader))

4.4. Затваряне на четецаЗатваряне на четеца





SqlDataReaderSqlDataReader

Класовете в свързана средаКласовете в свързана среда



SqlDataReaderSqlDataReader XmlReaderXmlReader

SqlParameterSqlParameterSqlParameterSqlParameterSqlParameterSqlParameter


Класът Класът SqlCommandSqlCommand Изпълнява обръщение към Изпълнява обръщение към SQL SQL

заявка или съхранена процедуразаявка или съхранена процедура По-важни свойстваПо-важни свойства

ConnectionConnection – връща / задава – връща / задава SqlConnectionSqlConnection-а на командата-а на командата

CommandTypeCommandType – тип команда – тип команда CommandType.StoredProcedureCommandType.StoredProcedure CommandType.TableDirectCommandType.TableDirect CommandType.TextCommandType.Text

CommandTextCommandText – – SQL SQL заявка или име заявка или име на съхранена процедурана съхранена процедура

ParametersParameters – параметри – параметри

Класът Класът SqlCommandSqlCommand По-важни методиПо-важни методи

ExecuteScalar()ExecuteScalar() Връща единична стойност (първата Връща единична стойност (първата

колона от първия ред от резултата)колона от първия ред от резултата) Върнатата стойност е Върнатата стойност е System.ObjectSystem.Object

ExecuteReader()ExecuteReader() Връща курсор (Връща курсор (SqlDataReader)SqlDataReader) CommandBehaviorCommandBehavior задава настройки задава настройки

ExecuteNonQuery()ExecuteNonQuery() Връща броя на засегнатите записи (Връща броя на засегнатите записи (intint))

ExecuteXmlReader()ExecuteXmlReader() Връща Връща XmlReaderXmlReader по резултата по резултата Поддържа се само в SqlClientПоддържа се само в SqlClient

Класът Класът SqlDataReaderSqlDataReader Извлича последователност от записи Извлича последователност от записи

(курсор) – резултат от изпълнена (курсор) – резултат от изпълнена командакоманда Достъпът е само за четене (Достъпът е само за четене (read-onlyread-only)) Достъпът е еднопосоченДостъпът е еднопосочен (forward-only) (forward-only)

По-важни методи и свойства:По-важни методи и свойства: Read()Read() – – придвижва курсора напред и придвижва курсора напред и

връща връща falsefalse ако няма следващ записако няма следващ запис ItemItem ( (индексаториндексатор)) – извлича стойността на – извлича стойността на

колона по име или индексколона по име или индекс Close()Close() – затваря курсора – трябва – затваря курсора – трябва

задължително да се вика!задължително да се вика!

Свързан моделСвързан модел – пример– примерusing System;using System;using System.Data;using System.Data;using System.Data.SqlClient;using System.Data.SqlClient;

class TestSqlCommandclass TestSqlCommand{{ private const string CONNECTION_STRING = "Server=.;" +private const string CONNECTION_STRING = "Server=.;" + " Database=pubs; Integrated Security=true";" Database=pubs; Integrated Security=true"; private const string COMMAND_SELECT_AUTHORS =private const string COMMAND_SELECT_AUTHORS = "SELECT au_fname, au_lname, phone FROM authors";"SELECT au_fname, au_lname, phone FROM authors";

static void Main()static void Main() {{ SqlConnection con = SqlConnection con = new SqlConnection(CONNECTION_STRING);new SqlConnection(CONNECTION_STRING); con.Open();con.Open(); trytry {{


Свързан моделСвързан модел – пример– пример SqlCommand command =SqlCommand command = new SqlCommand(COMMAND_SELECT_AUTHORS, con);new SqlCommand(COMMAND_SELECT_AUTHORS, con); SqlDataReader reader = command.ExecuteReader();SqlDataReader reader = command.ExecuteReader(); using (reader)using (reader) {{ while (reader.Read())while (reader.Read()) {{ string firstName = (String) string firstName = (String) reader["au_fname"];reader["au_fname"]; string lastName = (String) reader["au_lname"];string lastName = (String) reader["au_lname"]; string phone = (String) reader["phone"];string phone = (String) reader["phone"]; Console.WriteLine("{0} {1} - {2}", Console.WriteLine("{0} {1} - {2}", firstName, lastName, phone);firstName, lastName, phone); }} }} }} finallyfinally {{ con.Close();con.Close(); }} }}}}

Демонстрация Демонстрация ##1111 Реализация на свързан модел – Реализация на свързан модел –

SqlCommandSqlCommand и и SqlDataReaderSqlDataReader

Създаване на Създаване на SqlCommandSqlCommand Можете да създадете Можете да създадете SqlCommandSqlCommand

обект по няколко начинаобект по няколко начина:: ПрограмноПрограмно

От Server ExplorerОт Server Explorer във във VS.NETVS.NET От Toolbox във VS.NETОт Toolbox във VS.NET

SqlCommand cmSqlCommand cmdd = new SqlCommand( = new SqlCommand( "SELECT * FROM Products"SELECT * FROM Products, con);, con);

Демонстрация Демонстрация ##1212 Създаване на Създаване на SqlCommandSqlCommand във във

VS.NET VS.NET чрез чрез Server Explorer Server Explorer и чрез и чрез Data Data компонентите от компонентите от ToolboxToolbox-а-а

Класът Класът SqlParameterSqlParameter Какво представляват SqlParamer-ите?Какво представляват SqlParamer-ите?

SQL заявките и съхранени процедури SQL заявките и съхранени процедури могат да имат входящи и изходящи могат да имат входящи и изходящи параметрипараметри

Те се достъпват чрез Те се достъпват чрез ParametersParameters колекцията на колекцията на SqlCommandSqlCommand класа класа

По-важни свойства:По-важни свойства: ParameterNameParameterName – име на параметъра – име на параметъра DbTypeDbType – тип ( – тип (NVarCharNVarChar, , TimestampTimestamp, …), …) SizeSize – размер на типа (ако има) – размер на типа (ако има) DirectionDirection – входен, изходен, ... – входен, изходен, ...

Параметрични заявки – примерПараметрични заявки – пример

private void InsertShipper(string aName, string private void InsertShipper(string aName, string aPhone)aPhone){{ SqlCommand cmdInsertShipper = new SqlCommand(SqlCommand cmdInsertShipper = new SqlCommand( "INSERT INTO Shippers(CompanyName, Phone) " +"INSERT INTO Shippers(CompanyName, Phone) " + "VALUES (@Name, @Phone)", dbConnection);"VALUES (@Name, @Phone)", dbConnection);

SqlParameter paramName = SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar);new SqlParameter("@Name", SqlDbType.NVarChar); paramName.Value = aName;paramName.Value = aName; cmdInsertShipper.Parameters.Add(paramName);cmdInsertShipper.Parameters.Add(paramName);

SqlParameter paramPhone = SqlParameter paramPhone = new SqlParameter("@Phone", new SqlParameter("@Phone", SqlDbType.NVarChar);SqlDbType.NVarChar); paramPhone.Value = aPhone;paramPhone.Value = aPhone; cmdInsertShipper.Parameters.Add(paramPhone);cmdInsertShipper.Parameters.Add(paramPhone);

cmdInsertShipper.ExecuteNonQuery();cmdInsertShipper.ExecuteNonQuery();}}

Първичен ключ – извличанеПървичен ключ – извличане Извличането на автоматично-генериран Извличането на автоматично-генериран

първичен ключ е специфично запървичен ключ е специфично за всеки всеки database database сървърсървър

При При SQL Server SQL Server и и MS Access MS Access се използват се използват IdentityIdentity и и AutoNumberAutoNumber колониколони Стойността им се излича със заявката:Стойността им се излича със заявката:

Пример за извличане на автоматично-Пример за извличане на автоматично-генерирания първичен ключ от генерирания първичен ключ от ADO.NET:ADO.NET:

SELECT @@IdentitySELECT @@Identity

SqlCommand cmdSelectIdentity = SqlCommand cmdSelectIdentity = new SqlCommand("SELECT @@Identity", new SqlCommand("SELECT @@Identity", ddbCon);bCon);decimal insertedRecordId = decimal insertedRecordId = (decimal) cmdSelectIdentity.ExecuteScalar();(decimal) cmdSelectIdentity.ExecuteScalar();

Демонстрация Демонстрация ##1313 Работа с параметрични Работа с параметрични SQL SQL заявкизаявки

Извличане на превозвач по номерИзвличане на превозвач по номер Добавяне на превозвач и извличане на Добавяне на превозвач и извличане на

генерирания за него първичен ключгенерирания за него първичен ключ

Използване на транзакцииИзползване на транзакции Работа с транзакции в Работа с транзакции в SQL Server:SQL Server:

BEGIN TRANSACTIONBEGIN TRANSACTION

DECLARE DECLARE @orderDetailsError int,@orderDetailsError int, @productError int@productError int

DELETE FROM "Order Details" DELETE FROM "Order Details" WHERE ProductID=42WHERE ProductID=42SELECT @orderDetailsError = @@ERRORSELECT @orderDetailsError = @@ERROR

DELETE FROM Products WHERE ProductID=42DELETE FROM Products WHERE ProductID=42SELECT @productError = @@ERRORSELECT @productError = @@ERROR

IF @orderDetailsError = 0 AND @productError = 0IF @orderDetailsError = 0 AND @productError = 0 COMMIT TRANSCOMMIT TRANSELSE ELSE ROLLBACK TRANSROLLBACK TRANS

Използване на транзакцииИзползване на транзакции Работа с транзакции в ADO.NETРабота с транзакции в ADO.NET

Започване на транзакция:Започване на транзакция:

Въвличане на команда в дадена Въвличане на команда в дадена транзакциятранзакция::

Потвърждаване / анулиране на Потвърждаване / анулиране на транзакциятранзакция::

SqlTransaction trans =SqlTransaction trans = dbConnection.BeginTransaction();dbConnection.BeginTransaction();

command.Transaction = trans;command.Transaction = trans;

trans.Commit();trans.Commit();

ttrans.Rollback();rans.Rollback();

Използване на транзакцииИзползване на транзакции Нивото на изолация се дефинира с Нивото на изолация се дефинира с

енумерацията енумерацията IsolationLevelIsolationLevel Нива на изолация на Нива на изолация на SqlTransactionSqlTransaction

ReadUncommitedReadUncommited ReadCommitedReadCommited RepeatableReadRepeatableRead SerializableSerializable UnspecifiedUnspecified


SqlTransaction trans = dbConnection.SqlTransaction trans = dbConnection. BeginTransaction(IsolationLevel.Serializable);BeginTransaction(IsolationLevel.Serializable);

Демонстрация Демонстрация ##1414 Транзакции сТранзакции с ADO.NET ADO.NET

Връзка с други бази от данниВръзка с други бази от данни ADO.NET поддържа връзка с различни ADO.NET поддържа връзка с различни

бази от данни бази от данни ((освен освен SQL Server) SQL Server) чрез чрез съответни Data Providers:съответни Data Providers: OLE DB – поддържа се стандартноOLE DB – поддържа се стандартно Oracle – поддържа се стандартноOracle – поддържа се стандартно MySQL – инсталира се допълнителноMySQL – инсталира се допълнително PostgreSQL – инсталира се допълнителноPostgreSQL – инсталира се допълнително

Реализирани са интерфейсите:Реализирани са интерфейсите: IDbConnectionIDbConnection IDbCommandIDbCommand, , IDataParameterIDataParameter IDataReaderIDataReader IDbDataAdapterIDbDataAdapter

OLE DBOLE DB Data Provider Data Provider OleDbConnectionOleDbConnection – осъществява връзка с – осъществява връзка с

OLE DB източник на данниOLE DB източник на данни

OleDbCommandOleDbCommand – изпълнява SQL команди – изпълнява SQL команди върху OLE DB връзка към база даннивърху OLE DB връзка към база данни

OleDbParameterOleDbParameter – параметър на команда – параметър на команда OleDbDataReaderOleDbDataReader – за извличане на данни – за извличане на данни

от от команда, изпълнена през OLE DBкоманда, изпълнена през OLE DB OleDbDataAdapterOleDbDataAdapter – обменя данни между – обменя данни между

DataSetDataSet обекти и OLE DB база данни обекти и OLE DB база данни

OleDbConnection dbConn = new OleDbConnection(OleDbConnection dbConn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDB.mdb;Persist Security Info=False");Source=C:\MyDB.mdb;Persist Security Info=False");

Връзка с Връзка с OLE DB – OLE DB – примерпример Имаме база данни Имаме база данни C:\LC:\Library.mdbibrary.mdb, ,

създадена с MS Accessсъздадена с MS Access

В нея има таблица В нея има таблица UsersUsers::

Използваме Използваме "Microsoft"Microsoft Jet 4.0 Provider Jet 4.0 Provider"" за връзка от ADO.NET през OLE DBза връзка от ADO.NET през OLE DB

Създаваме Connection String чрез Създаваме Connection String чрез OleDbConnectionOleDbConnection компонентата от компонентата от Toolbox-a на VS.NET:Toolbox-a на VS.NET:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\LibraryC:\Library.mdb;Persist Security Info=False.mdb;Persist Security Info=False

Връзка с Връзка с OLE DB – OLE DB – примерпримерOleDbConnection dbConn = new OleDbConnection(OleDbConnection dbConn = new OleDbConnection(

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source" +@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source" +

@"=C:\Library.mdb;Persist Security Info=False");@"=C:\Library.mdb;Persist Security Info=False");

dbConn.Open();dbConn.Open();

OleDbCommand cmd = new OleDbCommand(OleDbCommand cmd = new OleDbCommand(

"INSERT INTO Users ([username], [password]) " +"INSERT INTO Users ([username], [password]) " +

"VALUES (@user, @pass)", dbConn);"VALUES (@user, @pass)", dbConn);

cmd.Parameters.Add("@user", cmd.Parameters.Add("@user",

OleDbType.VarChar).Value = "new user name";OleDbType.VarChar).Value = "new user name";

cmd.Parameters.Add("@pass",cmd.Parameters.Add("@pass",

OleDbType.VarChar).Value = "secret password";OleDbType.VarChar).Value = "secret password";

int affected = cmd.ExecuteNonQuery();int affected = cmd.ExecuteNonQuery();

Console.WriteLine("{0} records were inserted",Console.WriteLine("{0} records were inserted",

affected);affected);

Демонстрация Демонстрация ##1515 Връзка с Връзка с MS Access MS Access през през OLE DBOLE DB

Правилна работа с датиПравилна работа с дати Препоръки:Препоръки:

Използвайте вградените типове на Използвайте вградените типове на базата данни, с която работите, а не базата данни, с която работите, а не стрингстринг

Някои бази от данни поддържат Някои бази от данни поддържат повече от един тип за датиповече от един тип за дати В MS SQL Server 2000 има В MS SQL Server 2000 има datetimedatetime

(8 байта) и (8 байта) и smalldatetimesmalldatetime (4 байта) (4 байта) При работа с данни от тип дата, При работа с данни от тип дата,

използвайте стринг само за използвайте стринг само за визуализация към потребителявизуализация към потребителя

Правилна работа с датиПравилна работа с дати

Използвайте Използвайте System.DateTimeSystem.DateTime структурата за работа с дати в .NETструктурата за работа с дати в .NET

Използвайте параметрични заявки за Използвайте параметрични заявки за предаване на дати към базата даннипредаване на дати към базата данни

При нужда от конвертиране При нужда от конвертиране предавайте предавайте IFormatProviderIFormatProvider за за дефиниране на правилата за дефиниране на правилата за конвертиранеконвертиране

При необходимост използвайте При необходимост използвайте неутрални културни настройки: неутрални културни настройки: CultureInfo.InvariantCultureCultureInfo.InvariantCulture

Работа с дати – примерРабота с дати – примерCREATE TABLE MessagesCREATE TABLE Messages

((

MsgId int identity not null primary key,MsgId int identity not null primary key,

MsgText nvarchar(1000),MsgText nvarchar(1000),

MsgDate datetime MsgDate datetime –- Don’t use varchar for dates!–- Don’t use varchar for dates!

))

public void AddMsg(string aText,public void AddMsg(string aText, DateTimeDateTime aDate)aDate){{ SqlCommand cmdInsertMsg = new SqlCommand(SqlCommand cmdInsertMsg = new SqlCommand( "INSERT INTO Messages(MsgText, MsgDate) " +"INSERT INTO Messages(MsgText, MsgDate) " + "VALUES (@MsgText, @MsgDate)", mDbCon);"VALUES (@MsgText, @MsgDate)", mDbCon);

((примерът продължавапримерът продължава))

Работа с дати – примерРабота с дати – пример

SqlParameter paramMsgText =SqlParameter paramMsgText = newnew SqlParameter(SqlParameter(

"@MsgText","@MsgText", SqlDbType.NVarChar);SqlDbType.NVarChar);

paramMsgText.Value = aText;paramMsgText.Value = aText;

cmdInsertMsg.Parameters.Add(paramMsgText);cmdInsertMsg.Parameters.Add(paramMsgText);

SqlParameter paramMsgDate =SqlParameter paramMsgDate = new SqlParameter(new SqlParameter(

"@MsgDate", SqlDbType.DateTime);"@MsgDate", SqlDbType.DateTime);

paramMsgDate.Value = aDate;paramMsgDate.Value = aDate;

cmdInsertMsg.Parameters.Add(paramMsgDate);cmdInsertMsg.Parameters.Add(paramMsgDate);

cmdInsertMsg.ExecuteNonQuery();cmdInsertMsg.ExecuteNonQuery();

}}

Демонстрация #16Демонстрация #16 Работа с дати в Работа с дати в MS SQL ServerMS SQL Server

Работа с картинки в БДРабота с картинки в БД За записването на графични За записването на графични

изображения в бази от данни се изображения в бази от данни се използват бинарни полетаизползват бинарни полета:: типтип "image" "image" в в MS SQL ServerMS SQL Server тип "тип "blobblob"" в в OracleOracle тип "OLE Object" в тип "OLE Object" в MS AccessMS Access

При обемни файлове трябва да се При обемни файлове трябва да се използва работа с потоциизползва работа с потоци

Демонстрация Демонстрация ##2222 Съхранение на графични Съхранение на графични

изображения в база от данниизображения в база от данни

ADO.NET в несвързана средаADO.NET в несвързана среда

Данните се кешират в Данните се кешират в DataSetDataSet обект и връзката обект и връзката се преустановявасе преустановява1.1. Отваряне на връзка Отваряне на връзка

((SqlConnectionSqlConnection))

2.2. Пълнене на Пълнене на DataSetDataSet (чрез (чрез SqlDataAdapter)SqlDataAdapter)


4.4. Работа със Работа със DataSetDataSet-a-a

5.5. Отваряне на връзкаОтваряне на връзка

6.6. Нанасяне на промени по Нанасяне на промени по данните по сървъраданните по сървъра





DataSetDataSet

Работа в несвързана среда Работа в несвързана среда Типични сценарии, в които се използва Типични сценарии, в които се използва

работата в несвързана средаработата в несвързана среда Класове реализиращи достъпа до Класове реализиращи достъпа до

даннитеданните DataSetDataSet

Контейнер клас – съдържа таблици, Контейнер клас – съдържа таблици, релации, релации, constraintsconstraints и други обекти и други обекти

DataTableDataTable – съдържа таблица – съдържа таблица XxxDataAdapterXxxDataAdapter – осъществява – осъществява

достъпа до данните чрез достъпа до данните чрез XxxCommandXxxCommand, , XxxConnectionXxxConnection класове класове

DataRelationDataRelation – връзка между таблици – връзка между таблици

DataSetDataSet – обектен модел – обектен модел Колекции в Колекции в DataSetDataSet

TablesTables – съдържа таблиците – съдържа таблиците RelationsRelations – съдържа релациите между – съдържа релациите между

таблицитетаблиците

Поддръжка на автоматично Поддръжка на автоматично свързване (data binding)свързване (data binding)

Схема на Схема на DataSetDataSet Описва структурата на Описва структурата на DataSetDataSet-а-а Дефинира се или програмноДефинира се или програмно или чрез XSD схемаили чрез XSD схема

Модел на данните Модел на данните DataSetDataSet

DataSetDataSet

DataTableDataTable

11 ……22 ……33 ……44 ……

DataTableDataTable

11 ……22 ……33 ……44 ……

DataTableDataTable

11 ……22 ……33 ……44 ……

<?xml version="1.0" standalone="yes"?><?xml version="1.0" standalone="yes"?><xs:schema id="DataSetUsers" <xs:schema id="DataSetUsers" targetNamespace="http://www.tempuri.org/DataSetUsers.xsd" targetNamespace="http://www.tempuri.org/DataSetUsers.xsd" xmlns:mstns="http://www.tempuri.org/DataSet1.xsd" xmlns:mstns="http://www.tempuri.org/DataSet1.xsd" xmlns="http://www.tempuri.org/DataSet1.xsd" xmlns="http://www.tempuri.org/DataSet1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" attributeFormDefault="qualified" elementFormDefault="qualified">elementFormDefault="qualified"> <xs:element name="DataSetUsers" msdata:IsDataSet="true" <xs:element name="DataSetUsers" msdata:IsDataSet="true" msdata:Locale="bg-BG">msdata:Locale="bg-BG"> <xs:complexType><xs:complexType> <xs:choice maxOccurs="unbounded"><xs:choice maxOccurs="unbounded"> <xs:element name="Users"><xs:element name="Users"> <xs:complexType><xs:complexType> <xs:sequence><xs:sequence> <xs:element name="username" <xs:element name="username" type="xs:string" minOccurs="0" />type="xs:string" minOccurs="0" /> <xs:element name="password" <xs:element name="password" type="xs:string" minOccurs="0" />type="xs:string" minOccurs="0" /> </xs:sequence></xs:sequence> </xs:complexType></xs:complexType> </xs:element></xs:element> </xs:choice></xs:choice> </xs:complexType></xs:complexType> </xs:element></xs:element></xs:schema></xs:schema> XML SchemaXML Schema

Силно-типизирани Силно-типизирани DataSetsDataSets В .NET Framework се поддържат В .NET Framework се поддържат

силно-типизирани DataSet-исилно-типизирани DataSet-и Силно-типизираните Силно-типизираните DataSetDataSet-и са -и са

класове, наследници на класове, наследници на DataSetDataSet Дефинират свойства за достъп до Дефинират свойства за достъп до

таблиците, техните редове и колонитаблиците, техните редове и колони Достъп до нетипизиран Достъп до нетипизиран DataSetDataSet::

Достъп до силно-типизиран Достъп до силно-типизиран DataSetDataSet::

string username = string username = (string) (string) dsUsers.dsUsers. Tables["Users"].Rows[0]["username"];Tables["Users"].Rows[0]["username"];

string username = dsUsers.Users[0].string username = dsUsers.Users[0].usernameusername;;

Създаване на Създаване на DataSetDataSet Създаване на силно-типизиран Създаване на силно-типизиран

DataSetDataSet През XSD дизайнера на VS.NET 2003През XSD дизайнера на VS.NET 2003

FileFile | | Add New ItemAdd New Item | | Data SetData Set При записване на XSD файла, се При записване на XSD файла, се

генерира съответният C# класгенерира съответният C# клас С инструмента С инструмента xsd.exexsd.exe по по XSD XSD схема:схема:

Генерираният Генерираният MyDataSet.csMyDataSet.cs съдържа съдържа класове и свойства за таблиците, класове и свойства за таблиците, редовете и колоните на DataSet-аредовете и колоните на DataSet-а

xsd.exe MyDataSet.xsd /dataset/xsd.exe MyDataSet.xsd /dataset/

Демонстрация Демонстрация ##1717 Създаване на силно-типизиран Създаване на силно-типизиран

DataSetDataSet чрез VS.NET чрез VS.NET

Класът Класът DataTableDataTable Като таблиците в базите от данниКато таблиците в базите от данни Съставна част на Съставна част на DataSetDataSet класа класа Кеширано копие на данните в паметтаКеширано копие на данните в паметта Основни методи и свойства:Основни методи и свойства:

Rows[index]Rows[index] – достъп до редовете по номер – достъп до редовете по номер Columns[…]Columns[…] – достъп до колоните – достъп до колоните

((DataColumnDataColumn) по име или индекс) по име или индекс NewRow()NewRow() – създава нов ред ( – създава нов ред (DataRowDataRow), но не го ), но не го

добавя в таблицатадобавя в таблицата Rows.Add(DataRow)Rows.Add(DataRow) – добавя ред – добавя ред в таблицатав таблицата Columns.Add(DataColumn)Columns.Add(DataColumn) – добавя колона – добавя колона PrimaryKeyPrimaryKey – задава/извлича първичния ключ – задава/извлича първичния ключ

Модел на данните Модел на данните DataTableDataTable

DataTableDataTable11 …… …… …… ……

22 …… …… …… ……

33 …… …… …… ……

44 …… …… …… ……

DataColumnDataColumnDataColumnDataColumn

DataColumnDataColumn

DataDataRowRowDataDataRowRow

DataDataRowRow

Класове за работа с Класове за работа с DataTableDataTable Събития на класа DataTableСъбития на класа DataTable

RowChangingRowChanging – преди промяна на ред – преди промяна на ред RowChangedRowChanged – след промяна на ред – след промяна на ред RowDeletingRowDeleting – преди изтриване на ред – преди изтриване на ред RowDeletedRowDeleted – след изтриване на ред – след изтриване на ред

Класът Класът DataRowDataRow представя ред от таблица представя ред от таблица this[]this[] – дава достъп до стойността в дадена – дава достъп до стойността в дадена

колона (по име или индекс)колона (по име или индекс) DeleteDelete – маркира реда като изтрит – маркира реда като изтрит

Класът Класът DataColumnDataColumn представя колона представя колона NameName – име на колоната – име на колоната DataTypeDataType – тип на колоната ( – тип на колоната (intint, , stringstring, …), …)

Работа с Работа с DataTableDataTable – пример – пример

DataTable tbl = new DataTable("Authors");DataTable tbl = new DataTable("Authors");

tbl.Columns.Add("au_id", typeof(int));tbl.Columns.Add("au_id", typeof(int));tbl.Columns.Add("au_fname", typeof(string));tbl.Columns.Add("au_fname", typeof(string));tbl.Columns.Add("au_lname", typeof(string));tbl.Columns.Add("au_lname", typeof(string));tbl.Columns.Add("au_phone",typeof(string));tbl.Columns.Add("au_phone",typeof(string));

// The row is detached (not added to the table)// The row is detached (not added to the table)DataRow row = tbl.NewRow();DataRow row = tbl.NewRow();

row[0] = 1;row[0] = 1;row[1] = "Branimir";row[1] = "Branimir";row[2] = "Giurov";row[2] = "Giurov";row["phone"] = "+359 2 XXX XXXX";row["phone"] = "+359 2 XXX XXXX";

tbl.Rows.Add(row);tbl.Rows.Add(row);

Демонстрация Демонстрация ##1818 Работа сРабота с класа класа DataTableDataTable

Използване на ограничения Използване на ограничения ((constraints)constraints) Дефиниране на първичен ключ Дефиниране на първичен ключ

(Primary key)(Primary key)

// Single column PK// Single column PKdtCustomers.PrimaryKey = dtCustomers.PrimaryKey = nnew DataColumn()ew DataColumn(){{ dtCustomers.Columns("CustomerID")dtCustomers.Columns("CustomerID")}}

// Multiple columns PK// Multiple columns PKdtEmployees.PrimaryKey = dtEmployees.PrimaryKey = nnew DataColumn()ew DataColumn(){{ dtEmployees.Columns("LastName"), dtEmployees.Columns("LastName"), dtEmployees.Columns("FirstName")dtEmployees.Columns("FirstName")}}

Използване на ограничения Използване на ограничения ((constraints)constraints) Дефиниране на Дефиниране на UniqueConstraintUniqueConstraint

и и ForeignKeyConstraintForeignKeyConstraint

ds.Tables["Product"].Constraints.Add(ds.Tables["Product"].Constraints.Add( new UniqueConstraint("UC_ProductName",new UniqueConstraint("UC_ProductName", ds.Tables["Product"].Columns["ProductName"]));ds.Tables["Product"].Columns["ProductName"]));

ForeignKeyConstraint custOrderFK = ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",new ForeignKeyConstraint("CustOrderFK", ds.Tables["CustTable"].Columns["CustomerID"], ds.Tables["CustTable"].Columns["CustomerID"], ds.Tables["OrdersTable"].Columns["CustomerID"]);ds.Tables["OrdersTable"].Columns["CustomerID"]);

custOrderFK.DeleteRule = Rule.None; custOrderFK.DeleteRule = Rule.None; // Cannot delete a customer value // Cannot delete a customer value // that has associated existing orders.// that has associated existing orders.

ds.Tables["OrdersTable"].Constraints.Add(custOrderFK);ds.Tables["OrdersTable"].Constraints.Add(custOrderFK);

Потребителски изразиПотребителски изрази

Изразите са формули, дефинирани Изразите са формули, дефинирани от програмистаот програмиста

Дефинират се с Дефинират се с ExpressionExpression свойството на свойството на DataColumnDataColumn класа класа Sum([Unit Price] * [Quantity])Sum([Unit Price] * [Quantity])

Агрегатните функции могат да Агрегатните функции могат да използват родител/наследник използват родител/наследник релациитерелациите AvgAvg, , CountCount, , SumSum, , MaxMax, , MinMin

Потребителски изразиПотребителски изрази Използват за създаване на:Използват за създаване на:

калкулирана колонакалкулирана колона агрегирана колонаагрегирана колона конкатенация на една или повече колониконкатенация на една или повече колони обръщение към таблица родител или обръщение към таблица родител или

наследникнаследник Пример:Пример:

// Calculated field// Calculated field – VAT– VATDataColumn priceVat = new DataColumn(DataColumn priceVat = new DataColumn( "Price(VAT)"Price(VAT)"", typeof(decimal));, typeof(decimal));

priceVat.Expression = priceVat.Expression = ""Price * 1.2Price * 1.2"";;

productsTproductsTableable.Columns.Add(priceVat);.Columns.Add(priceVat);

DataRelationDataRelation обектиобекти Дефинират релации между родител / Дефинират релации между родител /

наследник колони в таблици с наследник колони в таблици с навигационна целнавигационна цел

Не създават ограничения (constraints)Не създават ограничения (constraints) Могат да се дефинират и към същата Могат да се дефинират и към същата

таблицатаблица Често се използват за навигация през Често се използват за навигация през

потребителския интерфейспотребителския интерфейс Използват се от колоните с потребителски Използват се от колоните с потребителски

изрази за дефиниране на агрегатни изразиизрази за дефиниране на агрегатни изрази Съдържат се в Съдържат се в RelationsRelations колекцията на колекцията на

DataSetDataSet обекта обекта

DataRelationDataRelation обектиобекти GetChildRows()GetChildRows() – връща редовете – връща редовете

наследници на даден реднаследници на даден ред

dsNorthwind.Relations.Add("FK_CustomersOrders",dsNorthwind.Relations.Add("FK_CustomersOrders", dtCustomers.Columns["CustomerID"],dtCustomers.Columns["CustomerID"], dtOrders.Columns["CustomerID"], true);dtOrders.Columns["CustomerID"], true);

//// Process all customers and their orders Process all customers and their ordersforeach (DataRow drCustomer in foreach (DataRow drCustomer in dsNorthwind.Tables[dsNorthwind.Tables[""CustomersCustomers""].Rows)].Rows){{ foreach (DataRow drOrder in drCustomer.foreach (DataRow drOrder in drCustomer. GetChildRows(GetChildRows(""FK_CustomerOrdersFK_CustomerOrders"")))) {{ // Do some work with the rows// Do some work with the rows }}}}

DataViewDataView клас клас Аналогия на изгледите (Аналогия на изгледите (ViewView

обектите) в базите от данниобектите) в базите от данни Динамичен изглед на данните базиран Динамичен изглед на данните базиран

на израз за филтриранена израз за филтриране Филтриране по версията на даннитеФилтриране по версията на данните Сортиране по колона или колониСортиране по колона или колони

DataTable usersTable = ...;DataTable usersTable = ...;

DataView usersView = new DataView(usersTable);DataView usersView = new DataView(usersTable);

// Show only modified versions of current rows and new rows// Show only modified versions of current rows and new rowsusersView.RowStateFilter = usersView.RowStateFilter = DataViewRowState.ModifiedCurrent |DataViewRowState.ModifiedCurrent | DataViewRowState.Added;DataViewRowState.Added;

Запазване и зареждане на Запазване и зареждане на данните от данните от DataSetDataSet Два основни методаДва основни метода

ReadXml(ReadXml(……)) – зарежда – зарежда DataSetDataSet от XML от XML WriteXml(WriteXml(……)) – записва – записва DataSetDataSet в XML в XML Пътят до файла се подава като Пътят до файла се подава като

параметърпараметър ReadXml(ReadXml(……)) хвърля изключение ако хвърля изключение ако

файлът не съществува или файлът не съществува или потребителят няма права потребителят няма права

WriteXml(WriteXml(……)) припокрива припокрива съществуващи файловесъществуващи файлове

DataSet.ReadXml()DataSet.ReadXml() Приема източник на XML данните:Приема източник на XML данните:

StreamStream, , TextReaderTextReader – отворен за четене – отворен за четене потокпоток

StringString – път до файл или URL – път до файл или URL XmlReaderXmlReader – XML четец – XML четец

Може да се задава режим на четене Може да се задава режим на четене ((XmlReadModeXmlReadMode)) AutoAuto, , DiffGramDiffGram, , FragmentFragment, , IgnoreSchemaIgnoreSchema, ,

InferSchemaInferSchema, , ReadSchemaReadSchema Текущата схема на данните може да се Текущата схема на данните може да се

запазва, да се прочете наново или да се запазва, да се прочете наново или да се генерира от самите даннигенерира от самите данни

DataSet.WriteXml()DataSet.WriteXml() Възможност за записване на Възможност за записване на

данните и схемата на данните и схемата на DataSetDataSet в в XMLXML форматформат

Синхронизация със Синхронизация със XmlDataDocumentXmlDataDocument класа класа

Може да се задава режим на Може да се задава режим на записване (записване (XmlWriteModeXmlWriteMode)) DiffGramDiffGram – записва оригиналните – записва оригиналните

данни и нанесените в тях промениданни и нанесените в тях промени IgnoreSchemaIgnoreSchema – не записва схемата – не записва схемата WriteSchemaWriteSchema – записва и схемата – записва и схемата

ReadXmlReadXml и и WriteXmlWriteXml – пример – примерDataSet dsStudents = new DataSet();DataSet dsStudents = new DataSet();// A string that contains the XML data// A string that contains the XML data

string xmlStudentData = string xmlStudentData = "<students><student>" +"<students><student>" +

"<name>Petar Petrov</name><fn>12345</fn>" + "<name>Petar Petrov</name><fn>12345</fn>" +

"</student><student>" +"</student><student>" +

"<name>Ivan Ivanov</name><fn>54321</fn>" +"<name>Ivan Ivanov</name><fn>54321</fn>" +

"</student></students>";"</student></students>";

// // A StringReader to pass the XML to ReadXml(...)A StringReader to pass the XML to ReadXml(...)

StringReader srXmlStudents = newStringReader srXmlStudents = new

StringReader(xmlStudentData);StringReader(xmlStudentData);

dsStudents.ReadXml(srXmlStudents,dsStudents.ReadXml(srXmlStudents,

XmlReadMode.InferSchema);XmlReadMode.InferSchema);

srXmlStudents.Close();srXmlStudents.Close();

((примерът продължавапримерът продължава))

ReadXmlReadXml и и WriteXmlWriteXml – пример – пример

// A StringWriter to store the data from the DataSet// A StringWriter to store the data from the DataSet

StringWriter swStudents = new StringWriter();StringWriter swStudents = new StringWriter();

dsStudents.WriteXml(swStudents, dsStudents.WriteXml(swStudents, XmlWriteMode.WriteSchema);XmlWriteMode.WriteSchema);

string strStudents = swStudents.ToString();string strStudents = swStudents.ToString();

swStudents.Close();swStudents.Close();

// Print to the console the XML before reading it// Print to the console the XML before reading it

// in the DataSet, and the XML produced by the // in the DataSet, and the XML produced by the

// DataSet's WriteXml(...) method// DataSet's WriteXml(...) method

Console.WriteLine("XML before:\n" +Console.WriteLine("XML before:\n" +

xmlStudentData + "\n\n\nXML after:\n " +xmlStudentData + "\n\n\nXML after:\n " +

strStudents);strStudents);

Демонстрация Демонстрация ##1919 Четене и писане на Четене и писане на DataSetDataSet от XML файл от XML файл

Използване на Използване на DataAdapterDataAdapter DataAdapter DataAdapter свързва свързва DataSetDataSet с с

източника на данниизточника на данни Използва се за:Използва се за:

Запълване на Запълване на DataSetDataSet с даннис данни Обновяване на данните в източника Обновяване на данните в източника

след извършване на промени в тяхното след извършване на промени в тяхното копие в копие в DataSetDataSet обекта обекта

Метод Метод Fill()Fill() на на DataAdapterDataAdapter Запълва таблица в Запълва таблица в DataSetDataSet Запълва Запълва DataTableDataTable обект обект Използва Използва SelectCommandSelectCommand

Използване на Използване на DataAdapterDataAdapter Свойство Свойство MissingSchemaActionMissingSchemaAction Запълване на повече от една Запълване на повече от една

таблица в таблица в DataSetDataSet Задаване на съответствие между Задаване на съответствие между

таблици и колони в източника на таблици и колони в източника на данни и данни и DataSetDataSet Свойство Свойство TableMappingsTableMappings на на

DataAdapterDataAdapter Метод Метод FillSchema()FillSchema() на на

DataAdapterDataAdapter – извличане на – извличане на схемата от източникасхемата от източника

Използване на Използване на DataAdapterDataAdapter

Свойства на Свойства на DataAdapterDataAdapter AcceptChangesDuringFillAcceptChangesDuringFill ContinueUpdateOnErrorContinueUpdateOnError

Събития на Събития на DataAdapterDataAdapter FillErrorFillError RowUpdatingRowUpdating RowUpdatedRowUpdated

Използване на Използване на DataAdapterDataAdapter

Обновяване на данните в източникаОбновяване на данните в източника Свойства Свойства InsertCommandInsertCommand, ,

UpdateCommandUpdateCommand, , DeleteCommandDeleteCommand Класът Класът CommandBuilderCommandBuilder Обновяване на източника с Обновяване на източника с

потребитеска логикапотребитеска логика Извличане на обновени стойности в Извличане на обновени стойности в

DataSetDataSet Обновяване на свързани таблициОбновяване на свързани таблици

DataAdapterDataAdapter – – архитектураархитектура



SelectCommandSelectCommandDataSetDataSet

……22

……33

……44

……11

DataTableDataTable

……22

……33

……44

……11

DataTableDataTable

……22

……33

……44

……11

DataTableDataTable

……22

……33

……44

……11

DataTableDataTable

……22

……33

……44

……11

DataTableDataTable

……22

……33

……44

……11

DataTableDataTable InsertCommandInsertCommand

UpdateCommandUpdateCommand

DeleteCommandDeleteCommand


DataAdapterDataAdapter – пример – примерstatic void Main()static void Main()

{{ string strCon = "Data Source=(local);" +string strCon = "Data Source=(local);" +

"Integrated Security=SSPI;Database=Northwind";"Integrated Security=SSPI;Database=Northwind";

SqlConnection cnNorthwind = new SqlConnection(strCon);SqlConnection cnNorthwind = new SqlConnection(strCon);

SqlCommand cmdSelect = CreateSelectCommand(cnNorthwind);SqlCommand cmdSelect = CreateSelectCommand(cnNorthwind);

SqlDataAdapter daEmployees = new SqlDataAdapter daEmployees = new SqlDataAdapter(cmdSelect);SqlDataAdapter(cmdSelect);

DataSet dsNorthwind = new DataSet();DataSet dsNorthwind = new DataSet();

daEmployees.Fill(dsNorthwind, "Employees");daEmployees.Fill(dsNorthwind, "Employees");

// Set the AutoIncrement property of EmployeeID column// Set the AutoIncrement property of EmployeeID column

DataTable employeesTable = DataTable employeesTable = dsNorthwind.Tables["Employees"];dsNorthwind.Tables["Employees"];

DataColumn columnEmployeeId =DataColumn columnEmployeeId =

employeesTable.Columns["EmployeeID"];employeesTable.Columns["EmployeeID"];

columnEmployeeId.AutoIncrement = true;columnEmployeeId.AutoIncrement = true;

columnEmployeeId.AutoIncrementSeed = -1;columnEmployeeId.AutoIncrementSeed = -1;

columnEmployeeId.AutoIncrementStep = -1;columnEmployeeId.AutoIncrementStep = -1;


DataAdapterDataAdapter – пример – пример // Create the commands for the data adapter// Create the commands for the data adapter

daEmployees.InsertCommand = CreateInsertCommand(cnNorthwind);daEmployees.InsertCommand = CreateInsertCommand(cnNorthwind);

daEmployees.DeleteCommand = CreateDeleteCommand(cnNorthwind);daEmployees.DeleteCommand = CreateDeleteCommand(cnNorthwind);

daEmployees.UpdateCommand = CreateUpdateCommand(cnNorthwind);daEmployees.UpdateCommand = CreateUpdateCommand(cnNorthwind);

// Add new record and update the database// Add new record and update the database

DataRow row = employeesTable.NewRow();DataRow row = employeesTable.NewRow();

row["LastName"] = "Ivanov";row["LastName"] = "Ivanov";

row["FirstName"] = "Ivan";row["FirstName"] = "Ivan";

employeesTable.Rows.Add(row);employeesTable.Rows.Add(row);

daEmployees.Update(dsNorthwind, "Employees");daEmployees.Update(dsNorthwind, "Employees");

Console.WriteLine("Inserted row id={0}.", row["EmployeeID"]);Console.WriteLine("Inserted row id={0}.", row["EmployeeID"]);

// Change the added record and update the database// Change the added record and update the database

row["LastName"] = "Petrov";row["LastName"] = "Petrov";

daEmployees.Update(dsNorthwind, "Employees");daEmployees.Update(dsNorthwind, "Employees");

Console.WriteLine("Updated the row.");Console.WriteLine("Updated the row.");


DataAdapterDataAdapter – пример – пример // Delete the added record and update the database// Delete the added record and update the database row.Delete();row.Delete(); daEmployees.Update(dsNorthwind, "Employees");daEmployees.Update(dsNorthwind, "Employees"); Console.WriteLine("Deleted the row.");Console.WriteLine("Deleted the row.");}}

static SqlCommand CreateSelectCommand(SqlConnection aConnection)static SqlCommand CreateSelectCommand(SqlConnection aConnection){{ string strSelect = "SELECT EmployeeID, LastName, FirstName" +string strSelect = "SELECT EmployeeID, LastName, FirstName" +

" FROM Employees";" FROM Employees";

SqlCommand cmdSelect = new SqlCommand(strSelect, aConnection);SqlCommand cmdSelect = new SqlCommand(strSelect, aConnection); return cmdSelect;return cmdSelect;}}

static SqlCommand CreateInsertCommand(SqlConnection aConnection)static SqlCommand CreateInsertCommand(SqlConnection aConnection){{ string strInsert = "INSERT Employees(LastName, FirstName) "+string strInsert = "INSERT Employees(LastName, FirstName) "+

"VALUES(@LastName, @FirstName);" +"VALUES(@LastName, @FirstName);" +"SET @EmployeeID=Scope_Identity()";"SET @EmployeeID=Scope_Identity()";


DataAdapterDataAdapter – пример – пример SqlCommand cmdInsert = new SqlCommand(strInsert, aConnection);SqlCommand cmdInsert = new SqlCommand(strInsert, aConnection);

SqlParameterCollection cparams = cmdInsert.Parameters;SqlParameterCollection cparams = cmdInsert.Parameters;

SqlParameter empID = cparams.Add("@EmployeeID", SqlParameter empID = cparams.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");SqlDbType.Int, 0, "EmployeeID");

empID.Direction = ParameterDirection.Output;empID.Direction = ParameterDirection.Output;

cparams.Add("@LastName", SqlDbType.NVarChar, 20,cparams.Add("@LastName", SqlDbType.NVarChar, 20, "LastName");"LastName");

cparams.Add("@FirstName", SqlDbType.NVarChar,cparams.Add("@FirstName", SqlDbType.NVarChar, 10,"FirstName");10,"FirstName");

return cmdInsert;return cmdInsert;

}}

static SqlCommand CreateUpdateCommand(SqlConnection aConnection)static SqlCommand CreateUpdateCommand(SqlConnection aConnection)

{{

string strUpdate = "UPDATE Employees SET " +string strUpdate = "UPDATE Employees SET " +

"LastName=@LastName, FirstName=@FirstName " +"LastName=@LastName, FirstName=@FirstName " +

"WHERE EmployeeID=@EmployeeID";"WHERE EmployeeID=@EmployeeID";

SqlCommand cmdUpdate = new SqlCommand(strUpdate,SqlCommand cmdUpdate = new SqlCommand(strUpdate, aConnection);aConnection);

SqlParameterCollection cparams = cmdUpdate.Parameters;SqlParameterCollection cparams = cmdUpdate.Parameters;

SqlCommand cmdUpdate = new SqlCommand(strUpdate,SqlCommand cmdUpdate = new SqlCommand(strUpdate, aConnection);aConnection);

SqlParameterCollection cparams = cmdUpdate.Parameters;SqlParameterCollection cparams = cmdUpdate.Parameters; (примерът продължава)(примерът продължава)

DataAdapterDataAdapter – пример – пример SqlParameter empID = cparams.Add("@EmployeeID", SqlParameter empID = cparams.Add("@EmployeeID",

SqlDbType.Int, 0, "EmployeeID");SqlDbType.Int, 0, "EmployeeID");

empID.SourceVersion = DataRowVersion.Original;empID.SourceVersion = DataRowVersion.Original;

cparams.Add("@LastName", SqlDbType.NVarChar,20,"LastName");cparams.Add("@LastName", SqlDbType.NVarChar,20,"LastName");

cparams.Add("@FirstName", SqlDbType.NVarChar, 10, cparams.Add("@FirstName", SqlDbType.NVarChar, 10, "FirstName");"FirstName");

return cmdUpdate;return cmdUpdate;

}}

static SqlCommand CreateDeleteCommand(SqlConnection static SqlCommand CreateDeleteCommand(SqlConnection aConnection)aConnection)

{{

string strDelete = "DELETE FROM Employees " +string strDelete = "DELETE FROM Employees " +"WHERE EmployeeID = @EmployeeID";"WHERE EmployeeID = @EmployeeID";

SqlCommand cmdDelete = new SqlCommand(strDelete, SqlCommand cmdDelete = new SqlCommand(strDelete, aConnection);aConnection);

SqlParameter empID = cmdDelete.Parameters.Add( SqlParameter empID = cmdDelete.Parameters.Add( "@EmployeeID", SqlDbType.Int, 0, "EmployeeID");"@EmployeeID", SqlDbType.Int, 0, "EmployeeID");

empID.SourceVersion = DataRowVersion.Original;empID.SourceVersion = DataRowVersion.Original;

return cmdDelete;return cmdDelete;

}}

DataSet.GetChanges()DataSet.GetChanges() и и DataSet.HasChanges()DataSet.HasChanges() Могат да приемат параметър за версията Могат да приемат параметър за версията

на данните от тип на данните от тип DataRowStateDataRowState AddedAdded – добавени редове – добавени редове DeletedDeleted – изтрити редове – изтрити редове DetachedDetached – разкачени от таблицата редове – разкачени от таблицата редове ModifiedModified – променени редове – променени редове UnchangedUnchanged – непроменени редове – непроменени редове

GetChanges()GetChanges() връща копие на връща копие на DataSetDataSet-а, -а, съдържащо промените след последното съдържащо промените след последното обръщение на обръщение на AcceptChanges()AcceptChanges() метода метода

DataSet.GetChanges()DataSet.GetChanges() – пример – пример

if(! myDataSet.HasChanges(DataRowState.Modified)) if(! myDataSet.HasChanges(DataRowState.Modified)) return;return;

// Create temporary DataSet variable// Create temporary DataSet variable

DataSet DataSet modifiedmodifiedDataSet;DataSet;

// GetChanges for modified rows only// GetChanges for modified rows onlymodifiedmodifiedDataSet = myDataSet.GetChanges(DataSet = myDataSet.GetChanges( DataRowState.Modified);DataRowState.Modified);

// Check the DataSet for errors// Check the DataSet for errorsif(if(modifiedmodifiedDataSet.HasErrors) {DataSet.HasErrors) { // Insert code to resolve errors// Insert code to resolve errors}}

// After fixing errors, update the data source // After fixing errors, update the data source // with the DataAdapter used to create the DataSet// with the DataAdapter used to create the DataSetadp.Update(adp.Update(modifiedmodifiedDataSet);DataSet);

Несвързан модел – типичен Несвързан модел – типичен сценарий на работасценарий на работа

1.1. Зареждаме данните в Зареждаме данните в DataSetDataSet чрез чрез DataAdapter.Fill()DataAdapter.Fill() или по друг начин: или по друг начин:

2.2. Обработваме данните – променяме, Обработваме данните – променяме, добавяме и изтриваме записидобавяме и изтриваме записи

3.3. Извличаме направените промени:Извличаме направените промени:

GetChanges()GetChanges() връща връща nullnull ако няма ако няма промени в промени в DataSetDataSet-а-а

userDataAdapter.Fill(dsUsers);userDataAdapter.Fill(dsUsers);

DataSet dsChanges = dsUsers.GetChanges();DataSet dsChanges = dsUsers.GetChanges();

Несвързан модел – типичен Несвързан модел – типичен сценарий на работасценарий на работа4.4. Прилагаме направените промени и Прилагаме направените промени и

разрешаваме конфликтитеразрешаваме конфликтите

5.5. Зареждаме отново Зареждаме отново DataSetDataSet-а от базата -а от базата данни, за да работим с актуални данниданни, за да работим с актуални данни

userDataAdapter.Fill(dsUsers);userDataAdapter.Fill(dsUsers);

userDataAdapter.RowUpdated +=userDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler(OnRowUpdated);new SqlRowUpdatedEventHandler(OnRowUpdated);

private void OnRowUpdated(object sender,private void OnRowUpdated(object sender,SqlRowUpdatedEventArgs e)SqlRowUpdatedEventArgs e)

{{// Handle the conflict …// Handle the conflict …е.е.Status = UpdateStatus.Continue;Status = UpdateStatus.Continue;

}}userDataAdapter.userDataAdapter.UpdateUpdate(dsChanges);(dsChanges);

Демонстрация Демонстрация ##2020 Реализация на несвързан модел с Реализация на несвързан модел с

DataSetDataSet и и DataAdapterDataAdapter

Класът Класът XmlDataDocumentXmlDataDocument Интеграция и поддръжка на XML Интеграция и поддръжка на XML

през през XmlDataDocumentXmlDataDocument класа класа Класът Класът XmlDataDocumentXmlDataDocument

Представлява DOM дървоПредставлява DOM дърво Наследник на Наследник на XmlDocumentXmlDocument

Реализира автоматична синхронизация Реализира автоматична синхронизация на на DataSetDataSet с DOM дърво с DOM дърво

При промяна в DOM дървото се При промяна в DOM дървото се промяна промяна DataSetDataSet-а-а

При промяна на При промяна на DataSetDataSet-а се промяна -а се промяна DOM дървотоDOM дървото

XmlDataDocumentXmlDataDocument – пример – примерDataSet myDataSet = new DataSet();DataSet myDataSet = new DataSet();

// // … … Fill the DataSetFill the DataSet … …

XmlDataDocument xmlDoc =XmlDataDocument xmlDoc = new XmlDataDocument(myDataSet); new XmlDataDocument(myDataSet);

// Get all elements with ProductID = 43// Get all elements with ProductID = 43XmlNodeList nodeList = xmlDoc.DocumentElement. XmlNodeList nodeList = xmlDoc.DocumentElement. SelectNodes("descendant::Customers[*/OrderDetails/SelectNodes("descendant::Customers[*/OrderDetails/ProductID=43]");ProductID=43]");

foreach (XmlNode myNode in nodeList)foreach (XmlNode myNode in nodeList){{ DataRow customer = xmlDoc.GetRowFromElement(DataRow customer = xmlDoc.GetRowFromElement( (XmlElement)myNode);(XmlElement)myNode); Console.WriteLine(customer["CompanyName"]);Console.WriteLine(customer["CompanyName"]);}}

Демонстрация Демонстрация ##2121 Използване на XPath за търсене в Използване на XPath за търсене в

DataSetDataSet чрез чрез XmlDataDocumentXmlDataDocument

Сигурността при работа с Сигурността при работа с бази от даннибази от данни Да не се сглобяват Да не се сглобяват SQLSQL команди с команди с

"+“"+“ Използване на параметрични Използване на параметрични

командикоманди Connection pooling Connection pooling и сигурности сигурност Съхраняване на Съхраняване на connection stringconnection string Криптиране на даннитеКриптиране на данните

Достъп до данниДостъп до даннис с ADO.NETADO.NET

Въпроси?Въпроси?

УпражненияУпражнения1.1. Какви модели на базите от данни познавате?Какви модели на базите от данни познавате?

2.2. Кои са основните функции, изпълнявани от една Кои са основните функции, изпълнявани от една система за управ¬ление на бази от данни (СУБД)?система за управ¬ление на бази от данни (СУБД)?

3.3. Дефинирайте понятието таблица в база от данни.Дефинирайте понятието таблица в база от данни.

4.4. Обяснете разликите между първичен и външен Обяснете разликите между първичен и външен ключ.ключ.

5.5. Посочете какви видове връзки между таблици Посочете какви видове връзки между таблици познавате.познавате.

6.6. Кога дадена база от данни е нормализирана до Кога дадена база от данни е нормализирана до четвърта нормална форма? Кои са предимствата четвърта нормална форма? Кои са предимствата на нормализираната база от данни?на нормализираната база от данни?

7.7. За какво се използват ограниченията в една база За какво се използват ограниченията в една база от данни?от данни?

УпражненияУпражнения8.8. Проектирайте база от данни, съхраняваща Проектирайте база от данни, съхраняваща

данните за студентите, преподавателите и данните за студентите, преподавателите и предметите, изучавани в един факултет.предметите, изучавани в един факултет.

9.9. Посочете предимствата и недостатъците на Посочете предимствата и недостатъците на използването на индекси в базите от данни.използването на индекси в базите от данни.

10.10. Какво е основното предназначение на езика SQL?Какво е основното предназначение на езика SQL?

11.11. За какво се използват транзакциите? За какво се използват транзакциите? Дефинирайте техните отговорности и обяснете Дефинирайте техните отговорности и обяснете нивата им на изолация.нивата им на изолация.

12.12. Посочете основните системни компоненти на MS Посочете основните системни компоненти на MS SQL Server.SQL Server.

13.13. Избройте основните инструменти, които се Избройте основните инструменти, които се използват при разработване на софтуер за SQL използват при разработване на софтуер за SQL Server. За какво служи всеки от тях?Server. За какво служи всеки от тях?

УпражненияУпражнения14.14. Кои команди спадат към DDL? Опишете тяхното Кои команди спадат към DDL? Опишете тяхното

действие.действие.

15.15. Напишете скрипт, съдържащ DDL командите, Напишете скрипт, съдържащ DDL командите, необходими за създаване на базата от данни, необходими за създаване на базата от данни, обслужваща даден факултет в университет. обслужваща даден факултет в университет. Базата трябва да обхваща студентите, Базата трябва да обхваща студентите, преподавателите, изучаваните предмети, преподавателите, изучаваните предмети, учебните програми и оценките на всеки студент.учебните програми и оценките на всеки студент.

16.16. Обяснете действието на командите, спадащи Обяснете действието на командите, спадащи към групата DML.към групата DML.

17.17. Какви видове съединения на таблици Какви видове съединения на таблици познавате?познавате?

18.18. Напишете заявка, която извлича всички Напишете заявка, която извлича всички изучавани предмети в университета, заедно със изучавани предмети в университета, заедно със записалите ги студенти.записалите ги студенти.

УпражненияУпражнения19.19. Каква е употребата на агрегиращите функции в Каква е употребата на агрегиращите функции в

езика SQL?езика SQL?

20.20. Напишете заявка, която извлича за всяка учебна Напишете заявка, която извлича за всяка учебна група средния успех на студентите, които я група средния успех на студентите, които я съставят.съставят.

21.21. За какво се използват DBCC командите в SQL За какво се използват DBCC командите в SQL Server?Server?

22.22. За какво се използват съхранените процедури? За какво се използват съхранените процедури? Посочете примери за използването им.Посочете примери за използването им.

23.23. Напишете съхранена процедура, която добавя Напишете съхранена процедура, която добавя нов студент към даден курс в базата от данни, нов студент към даден курс в базата от данни, предназначена за обслужване на факултет.предназначена за обслужване на факултет.

24.24. Кои команди се използват за управлението на Кои команди се използват за управлението на транзакциите в T-SQL?транзакциите в T-SQL?

УпражненияУпражнения25.25. Напишете съхранена процедура, която заменя Напишете съхранена процедура, която заменя

даден изучаван предмет от студент с друг.даден изучаван предмет от студент с друг.

26.26. Напишете съхранена процедура, която добавя Напишете съхранена процедура, която добавя нов преподавател, който води нов учебен нов преподавател, който води нов учебен предмет и записва всички студенти от трети курс предмет и записва всички студенти от трети курс за този учебен предмет. Поредицата операции за този учебен предмет. Поредицата операции трябва да се изпълнява атомарно – или всички трябва да се изпълнява атомарно – или всички операции да се изпълнят успешно, или никоя от операции да се изпълнят успешно, или никоя от тях да не се изпълни.тях да не се изпълни.

27.27. Какво е поведението на вложените транзакции?Какво е поведението на вложените транзакции?

28.28. При какви случаи е възможно възникването на При какви случаи е възможно възникването на ситуацията "мъртва хватка"?ситуацията "мъртва хватка"?

29.29. Какви са начините за пренасяне на база от данни Какви са начините за пренасяне на база от данни на друг компютър? Избройте предимствата и на друг компютър? Избройте предимствата и недостатъците им.недостатъците им.

УпражненияУпражнения30.30. Обяснете какво представляват свързаният и Обяснете какво представляват свързаният и

несвързаният модел за достъп до данни. несвързаният модел за достъп до данни. Опишете в кои случаи се използва единият, и в Опишете в кои случаи се използва единият, и в кои – другият. Дайте примери. Опишете кои – другият. Дайте примери. Опишете предимствата и недостатъците на двата модела.предимствата и недостатъците на двата модела.

31.31. Опишете еднослойните, двуслойните, Опишете еднослойните, двуслойните, трислойните и многослойните приложения – трислойните и многослойните приложения – какво представляват съответните модели, какво представляват съответните модели, техните предимства и недостатъци, случаи на техните предимства и недостатъци, случаи на използване. Дайте примери.използване. Дайте примери.

32.32. Обяснете накратко какво е ADO.NET и кои са Обяснете накратко какво е ADO.NET и кои са неговите основни пространства от имена. За неговите основни пространства от имена. За какво служат те?какво служат те?

33.33. Какво представляват доставчиците на данни и Какво представляват доставчиците на данни и кои са стандартните доставчици на данни в кои са стандартните доставчици на данни в ADO.NET?ADO.NET?

УпражненияУпражнения34.34. Опишете класовете от SqlClient Опишете класовете от SqlClient Data ProviderData Provider..

35.35. Опишете начините за автентикация пред MS SQL Опишете начините за автентикация пред MS SQL Server. Дайте пример за символен низ за връзка с Server. Дайте пример за символен низ за връзка с SQL Server (connection string)SQL Server (connection string)

36.36. Опишете механизма на connection pooling. Защо е Опишете механизма на connection pooling. Защо е необходимо да се използва?необходимо да се използва?

37.37. Обяснете какво представлява свързаният модел Обяснете какво представлява свързаният модел за достъп до данни. за достъп до данни.

38.38. Обяснете кои са основните класове и Обяснете кои са основните класове и интерфейси за работа със свързан модел.интерфейси за работа със свързан модел.

39.39. Напишете програма, която извлича от базата Напишете програма, която извлича от базата данни Northwind в SQL Server всички категории данни Northwind в SQL Server всички категории продукти и имената на продуктите от всяка продукти и имената на продуктите от всяка категория. Използвайте таблиците Categories и категория. Използвайте таблиците Categories и Products.Products.

УпражненияУпражнения40.40. Напишете процедура, която добавя нов продукт в Напишете процедура, която добавя нов продукт в

таблицата с продуктите в базата данни Northwind таблицата с продуктите в базата данни Northwind в SQL Server. Използвайте параметрична SQL в SQL Server. Използвайте параметрична SQL заявка.заявка.

41.41. Разглеждаме проста система за обслужване на Разглеждаме проста система за обслужване на банкомат. Създайте нова база данни ATM в MS банкомат. Създайте нова база данни ATM в MS SQL Server за съхранение на сметките на SQL Server за съхранение на сметките на картодържателите и наличностите по тях. картодържателите и наличностите по тях. Добавете нова таблица CardAccounts. В Добавете нова таблица CardAccounts. В таблицата дефинирайте следните полета: Idтаблицата дефинирайте следните полета: Id (int) (int), , CardNumberCardNumber (char(10)) (char(10)), CardPIN, CardPIN (char(4) (char(4), CardCash, CardCash (money).(money).

Добавете няколко примерни записа в таблицата Добавете няколко примерни записа в таблицата (ще ви трябват за тестване).(ще ви трябват за тестване).

УпражненияУпражнения42.42. Използвайки транзакции напишете процедура, Използвайки транзакции напишете процедура,

която тегли дадена сума (например 200 лв.) от която тегли дадена сума (например 200 лв.) от дадена картова сметка. Операцията по тегленето дадена картова сметка. Операцията по тегленето на пари се изпълнява успешно когато е налице на пари се изпълнява успешно когато е налице успешното изпълнение на следната поредица от успешното изпълнение на следната поредица от съставни операции:съставни операции:

Проверява се със заявка дали подаденият пин-код Проверява се със заявка дали подаденият пин-код (CardPIN) съответства на номера на картата (CardPIN) съответства на номера на картата (CardNumber).(CardNumber).

Проверява се наличността (CardCash) по картовата Проверява се наличността (CardCash) по картовата сметка дали е повече от заявената сума (повече от сметка дали е повече от заявената сума (повече от 200 лв.).200 лв.).

Банкоматът изплаща заявената сума (200 лв.) и Банкоматът изплаща заявената сума (200 лв.) и записва в таблицата CardAccounts новата наличност записва в таблицата CardAccounts новата наличност (CardCash = CardCash - 200).(CardCash = CardCash - 200).

Транзакциите да се реализират с Транзакциите да се реализират с ADO.NET, ADO.NET, не не чрез съхранена процедура.чрез съхранена процедура.

УпражненияУпражнения43.43. Разширете проекта от предната задача и Разширете проекта от предната задача и

добавете нова таблица AccountTransactions с добавете нова таблица AccountTransactions с полета (Id, CardId, TransactionDate, Ammount), полета (Id, CardId, TransactionDate, Ammount), съдържащa информация за всички тегления на съдържащa информация за всички тегления на пари по всички сметки. Променете процедурата пари по всички сметки. Променете процедурата за теглене на пари, така че да запазва за теглене на пари, така че да запазва информация в новата таблица за всяко успешно информация в новата таблица за всяко успешно извършено теглене.извършено теглене.

Съобразете се с необходимостта от използване Съобразете се с необходимостта от използване на транзакция за цялата операция по тегленето. на транзакция за цялата операция по тегленето. Добавете процедура, която по дадена карта и Добавете процедура, която по дадена карта и ПИН код показва списък на всички тегления, ПИН код показва списък на всички тегления, сортирани по дата.сортирани по дата.

Съобразете се с препоръките за правилна Съобразете се с препоръките за правилна работа с дати.работа с дати.

УпражненияУпражнения44.44. Създайте база данни в SQL Server за Създайте база данни в SQL Server за

съхранението на библиотека с филми и съхранението на библиотека с филми и видеоклипове. Напишете програма, която видеоклипове. Напишете програма, която записва и чете филми от базата данни. Имайте записва и чете филми от базата данни. Имайте предвид, че филмите са обемни фай¬лове (най-предвид, че филмите са обемни фай¬лове (най-често около 700 MB) и трябва да се вкарват и често около 700 MB) и трябва да се вкарват и извличат от базата данни на части.извличат от базата данни на части.

45.45. Опишете класовете, които се използват за Опишете класовете, които се използват за реализация на несвързания модел в ADO.NET. За реализация на несвързания модел в ADO.NET. За какво служи всеки от тях?какво служи всеки от тях?

46.46. Каква е разликата между силно-типизиран и Каква е разликата между силно-типизиран и нетипизиран DataSet? Как се създават двата нетипизиран DataSet? Как се създават двата вида DataSet?вида DataSet?

УпражненияУпражнения47.47. Кои класове се използват при работа с DataSet? Кои класове се използват при работа с DataSet?

За какво служи класът DataTable? Как се добавят За какво служи класът DataTable? Как се добавят редове и колони в DataTable? За какво служи редове и колони в DataTable? За какво служи класът DataRelation? Какви са основните класът DataRelation? Какви са основните приложения на DataView? Как се добавят приложения на DataView? Как се добавят ограничения в DataSet? За какво служат ограничения в DataSet? За какво служат потребителските изрази в колоните на потребителските изрази в колоните на таблиците?таблиците?

48.48. Опишете средствата за зареждане на DataSet от Опишете средствата за зареждане на DataSet от XML документ и записване на съдържанието на XML документ и записване на съдържанието на DataSet в XML документ. DataSet в XML документ.

49.49. Опишете предназначението на класа Опишете предназначението на класа XmlDataDocument.XmlDataDocument.

50.50. Опишете някои основни съображения относно Опишете някои основни съображения относно сигурността при работа с базите от данни.сигурността при работа с базите от данни.

УпражненияУпражнения51.51. Създайте нова база от данни University през Създайте нова база от данни University през

Enterprise Manager на SQL Server. Създайте в нея Enterprise Manager на SQL Server. Създайте в нея таблица Students със следната схема: таблица Students със следната схема: Students(Id int identity primary key, FirstName Students(Id int identity primary key, FirstName nvarchar(15) not null, LastName nvarchar(20) not nvarchar(15) not null, LastName nvarchar(20) not null, Age int, TimeRecordAdded datetime default null, Age int, TimeRecordAdded datetime default GETDATE()). Запълнете таблицата с малко GETDATE()). Запълнете таблицата с малко примерни данни.примерни данни.

След това напишете програма, която извлича След това напишете програма, която извлича данните от таблицата в DataSet обект и променя данните от таблицата в DataSet обект и променя заредените данни, като добавя, изтрива и заредените данни, като добавя, изтрива и модифицира редове. След това данните трябва модифицира редове. След това данните трябва да се обновяват в базата. Използвайте да се обновяват в базата. Използвайте DataAdapter с ръчно написани команди за DataAdapter с ръчно написани команди за обновяване на базата (без да използвате обновяване на базата (без да използвате CommandBuilder).CommandBuilder).

УпражненияУпражнения51.51. Обработете събитието RowUpdate, така че да се Обработете събитието RowUpdate, така че да се

справите с евентуални конфликти. Опитайте да справите с евентуални конфликти. Опитайте да създадете конфликти ръчно и да ги разрешите с създадете конфликти ръчно и да ги разрешите с програмна логика.програмна логика.

Запишете съдържанието на DataSet обекта във Запишете съдържанието на DataSet обекта във файл students.xml.файл students.xml.

David Sceppa, Microsoft ADO.NET, Microsoft David Sceppa, Microsoft ADO.NET, Microsoft Press, 2002, ISBN 0-7356-1423-7Press, 2002, ISBN 0-7356-1423-7

Rebecca Riordan, Designing Relational Database Rebecca Riordan, Designing Relational Database Systems, Microsoft Press, 1999, ISBN 0-7356-Systems, Microsoft Press, 1999, ISBN 0-7356-0634-X0634-X

Kalen Delaney, Inside SQL Server 2000, Kalen Delaney, Inside SQL Server 2000, Microsoft Press, 2001, ISBN 0-7356-0998-5Microsoft Press, 2001, ISBN 0-7356-0998-5

Microsoft Corporation, Microsoft SQL Server Microsoft Corporation, Microsoft SQL Server Books OnlineBooks Online

Accessing Data with ADO.NET (.NET Framework Accessing Data with ADO.NET (.NET Framework Developer's Guide) Developer's Guide) – – http://msdn. microsoft.com/library/en-us/cpguide/html/ cpconAccessingDataWithADONET.asp

Използвана литератураИзползвана литература

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconAccessingDataWithADONET.asp



Francesco Balena, Programming Microsoft Francesco Balena, Programming Microsoft Visual Basic .NET (Core Reference), Mirosoft Visual Basic .NET (Core Reference), Mirosoft Press, 2002, 0-7356-1375-3 – Chapter 21: Press, 2002, 0-7356-1375-3 – Chapter 21: ADO.NET in Disconnected Mode – ADO.NET in Disconnected Mode – http://www.microsoft.com/mspress/books/sampchap/5199.asp

MSDN Library – MSDN Library – http://msdn.microsoft.com

Използвана литератураИзползвана литература

http://www.microsoft.com/mspress/books/sampchap/5199.asp

http://www.microsoft.com/mspress/books/sampchap/5199.asp

http://msdn.microsoft.com/

http://www.devbg.org/


Lecture 13 ADO.net v1.01

Documents

Transcript of Lecture 13 ADO.net v1.01