анотация: След като прочетете тази лекция, вие ще можете да: създавате заявки с помощта на SQL Server Management Studio Query Builder, да извличате информация за базата данни от системните таблици на базата данни, динамично да създавате прости заявки въз основа на въвеждане от потребителя, да форматирате въвеждането на потребителя и да филтрирате сложни динамични заявки, да извършвате синтактичен анализ и преформатирайте данните за използване във филтъра, защитете базата данни от атаки с "SQL-инжекция", използвайте процедурата sp_executeSql, за да изпратите заявката

Предишната лекция говори за това как да увеличим производителностискания. Вече знаете как да създадете ефективен набор от заявки, за да предоставите на потребителите най-полезната информация от вашето приложение, като използвате предварително изградени заявки в съхранени процедури или изгледи.

Въпреки това, във всички приложения, освен в най-простите, е невъзможно да се знаят предварително всички възможни опции за видовете информация, от които потребителите може да се нуждаят и как ще искат да я филтрират и организират. Вместо да се опитвате да си представите всички тези възможности, можете да дадете на потребителя контрол върху информацията, която приложението комуникира. Този урок ще ви покаже как динамично да създавате заявки въз основа на избора, който потребителят прави по време на изпълнение.

Потребителски интерфейс за изграждане на заявки

SQL Server Management Studio включва усъвършенстван интерфейс за изграждане на заявки. Нека разгледаме този интерфейс, за да ви дадем представа как можете да създавате заявки динамично. Вашето приложение няма да се нуждае от всичко контролипредоставено от SQL Server Management Studio. По принцип трябва да помислите внимателно как най-добре да ограничите избора на потребителите.

Изграждане на заявка с помощта на SQL Server Management Studio Query Builder

Извличане на информация за таблици в базата данни

За да представи на потребителя списък с параметри, приложението може да се наложи да извлече информация за таблиците на базата данни. Има няколко начина да получите тази информация. Най-важният от тези методи е използването на схемата INFORMATION_SCHEMA. Тази схема е стандартна във всяка база данни.

Използване на INFORMATION_SCHEMA

Схемата INFORMATION_SCHEMA е специална схема, която се намира във всяка база данни. Той съдържа дефинициите на някои от обектите на базата данни.

INFORMATION_SCHEMA отговаря на стандарта ANSI, който е предназначен за извличане на информация от всяка ANSI-съвместима база данни. В SQL Server INFORMATION_SCHEMA се състои от набор от изгледи, които отправят заявки към таблиците на базата данни sys *, съдържащи информация за структурата на базата данни. Тези таблици могат да бъдат запитани директно, точно както всички таблици на база данни. Въпреки това, в повечето случаи е по-добре да използвате изгледите на схема INFORMATION_SCHEMA, за да извлечете информация от * sys таблици.

Забележка... Схемата INFORMATION_SCHEMA понякога прави заявки за таблици, които не са необходими, което е пагубно за производителността. В следващия пример в тази глава това не е особено важно, тъй като приложението вече чакаше въвеждане от потребителя. Това обаче трябва да се има предвид, ако скоростта е важен аспект за вашето приложение.

Ето основния T-SQL код, който се използва за получаване на информация за колоните, които влизат в таблица:

Имайте предвид, че трябва да изберете полето TABLE_SCHEMA, за да получите схемата за таблицата. Това може да е важно за създаване на подобни заявки в бъдеще. За да експериментирате с техниките в тази глава, създайте нов проект във Visual Studio.

Създайте нов проект на Visual Studio

1. Изберете Всички програми, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 от менюто Старт.
2. От менюто на Visual Studio изберете Файл, Нов, Проект.
3. В екрана Типове проекти разгънете възела на Visual Basic и изберете шаблона за приложение в екрана Шаблони. Наименувайте проекта Chapter7 и щракнете върху OK.
4. Приложението за този пример може да бъде намерено в примерните файлове в папката \ Chapter7 \ DynQuery. Можете да изрежете и поставите кода за следните процедури от файла Form1.vb.

Извличане на списък с таблици и изгледи

Обикновено ще трябва да предоставите на потребителя възможността да избира не само колоните, но и таблицата за динамична заявка, така че ще трябва да покажете списък с таблици на потребителя. Необходимата информация може да бъде получена чрез следното запитване:

ИЗБЕРЕТЕ TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE ОТ INFORMATION_SCHEMA.TABLES

В приложение тази заявка може да се използва по следния начин.

Вземете списък с таблици

Горният код на Visual Basic инициализира SqlCommand с име com със SQL низ за изпълнение и след това изпълнява обекта SqlCommand. Това е най-лесният начин да изпълните T-SQL оператор от вашето приложение.

Като упражнение можете да поставите диаграмите и таблиците от процедурата Load на нашия формуляр в потребителския интерфейс на приложението като формуляр, така че потребителят да може да избере схемата и таблицата, с които да работи. За примера в тази глава ще приемем, че потребителят е избрал схемата за продажби и таблицата Клиент.

След като потребителят е избрал таблица, списъкът с колони за тази таблица може да бъде извлечен, като се използва същия метод, като се използва въвеждане от потребителя като името на таблицата в заявката. За да направите това, въведете заместител в низа на заявката и след това заменете този заместител с извикване на String.Format. В кода по-долу заместителят в низа на заявката е (0).

Вземете списъка с колони

1. Добавете следната процедура RetrieveColumns към кода под процедурата RetrieveTables:

   Sub RetrieveColumns (ByVal TableName като низ) MyConnection като нов SqlClient.SqlConnection (_ "Източник на данни =. \ SQLExpress;" & _ "Начален каталог = AdventureWorks; Trusted_Connection = Yes;") Dim sqlStr Като "Стринг" sqlABNSCH = "ИЗБИРАНЕ НА СТРИНГ sqlABNStr" , "+ _" ORDINAL_POSITION, DATA_TYPE "+ _" FROM INFORMATION_SCHEMA.COLUMNS "+ _" WHERE (TABLE_NAME = "(0)") "Dim tableColumns като нова таблица с данни Dim da като нов SqlClientFatter.SqlStrName, Table ( ) da.Fill (tableColumns) For i As Integer = 0 To tableColumns.Rows.Count - 1 С tableColumns.Rows.Item (i) Console.WriteLine ("(0) (1) (2 )", _ .Item ( 1), .Елемент (2), .Позиция (3)) Край със следващ край под

2. В процедурата Form1_Load добавете следното извикване към процедурата RetrieveColumns след процедурата RetrieveTables:

В тази статия ще научите какво е LINQ и как да го използвате за запитване на списъци.

Какво е LINQ? Езиковото интегрирано запитване (LINQ) е интегриран език за заявки, или по-скоро спорен проект на Microsoft за добавяне на синтаксис на SQL-подобен език за заявки. По-конкретно, този инструмент улеснява търсенето на таблици и списъци с данни, а не непременно база данни.

В примерите на Microsoft липсва един малък, но важен детайл. За да работим с набора от данни, трябва да използваме структурата DataContext, чиято дефиниция може да бъде получена с помощта на SPMetal.exe за списъка или библиотеката, присъстващи на сървъра на Sharepoint. С други думи, първо трябва да дефинираме този тип данни и след това да създадем заявка Като цяло препоръчвам да използвате специална помощна програма за работа в MVS: Imtech Get SPMetal Definition Extension.

За работа се нуждаем от някакъв списък. Нека използваме прост списък – „персонализиран списък“; заглавие – „Просто обжалване“; Полета по подразбиране: Номер, Име.

И така, нека създадем проста (вижте съответната страница), с която ще наблюдаваме резултата от нашите манипулации. Когато създаваме проект, ще изберем изолиран модел на доверие, ако има нужда от работа със списъци или данни от други възли, тогава е необходимо да се създаде проект за фермата.

След това нека създадем файл от класа "SimpleOffice.cs", използвайки SPMetal.exe или плъгин за студиото. Нека добавим връзка към библиотеката Microsoft.Sharepoint.Linq. Повече информация е налична на уебсайта на MSDN.

Нека създадем функция Render и my_mess. В последната функция ще формираме данни за показване.

// претоварване на функцията
защитено отмяна void Render (запис на HtmlTextWriter)
{
база .Render (писател);
my_mess (писател);
}

// Нашата функция за работа със списъка
public void my_mess (писател на HtmlTextWriter)
{
// Създаване на контекст
DataContext данни = нов DataContext ("http: // localhost");
// Свържете го със съответния списък на сайта в корена
EntityList<Элемент>Simple_Message = data.GetList<Элемент>("Просто обжалване");
// Изпълнете заявката - изберете всички линии от списъка "Simple Call".
var query = от бъркотия в Simple_Message
изберете бъркотия;
// показва всички данни от резултата от заявката
foreach (var elem в заявката)
{
writer.WriteLine ( "Елемент от списъка:"+ elem.Name.ToString ());
писател.WriteBreak ();
}

}

Компилираме проекта и го добавяме към страницата. В резултат на това ще се покажат всички редове в списъка.

Продължение по-късно (статията не е завършена) ...

В прозореца Server Explorer, където таблиците на базата данни на Microsoft SQL Server са представени под формата на дърво (вж. Фиг. 4.1.), можете да отворите прозорец за всяка таблица, като щракнете върху бутона Покажи данни от таблицата. Именно в този прозорец е предоставена възможността за редактиране на таблични данни. Този прозорец за редактиране изглежда приблизително както е показано на фиг. 4.2. Приблизително, защото пълният обем не беше включен във фигурата поради големия брой колони.

1. Внедряване на картографиране на база данни

Картографирането на базата данни (компонент DataSet) е необходимо, за да се изключат ненужни повиквания към базата данни и към сървъра. Това е много важна характеристика, защото ако много потребители работят с базата данни, натоварването на сървъра може да бъде доста голямо - броят на заявките е многократно по-голям, което ще забави изпълнението на заявките.

Следователно за сметка на DataSet на компютъра на клиента има изобразяване на базата данни, с която той работи. След приключване на работата клиентската и сървърната база данни се синхронизират (или по-скоро промените, направени в клиентската база данни, се правят в сървърната база данни)

Показването на нашата база данни на MS Visual Studio 2005 изглежда така:

Всички числови данни са от тип Int32, низовите данни са от тип String. Размерите на низовите данни съответстват на тези, дадени във физическия модел на база данни.

Екранната снимка показва, че във всяка таблица има и TableAdapters. Това са нещо като мостове между DataSet и източника на данни. Има методи като Fill и GetData ()

Този метод е предназначен да попълни таблицата с необходимите данни от източника (файл с база данни)

GetData () метод

Предназначен е за вземане на данни от таблицата за картографиране.

1. Внедряване на софтуер

Основната характеристика в софтуерната реализация е фактът, че всички класове, отговорни за редактирането на таблици на база данни, са наследени от един базов клас, което го прави лесен за внедряване. Методите на този базов клас са описани по-горе. Виртуалните методи на базовия клас трябва да бъдат отменени в класа-потомка. Всички тези методи ви позволяват да променяте параметрите на детето, като например: името на заглавката на формуляра, попълване и запазване на желаната таблица на базата данни (един клас потомък работи само с една таблица, в смисъл на редактиране на данни; обаче, когато се показват данни към потребителят, е възможно да се използват няколко таблици на база данни, за да се покажат данните във форма, която е разбираема за потребителя).

1. Програмен интерфейс

Главният прозорец е MDI приложение, което има следното меню:

Всяка от таблиците за редактиране на базата данни се извиква от съответния елемент от менюто "Таблици".

Отчет за натоварването на учителите се създава и чрез щракване върху съответния елемент от менюто „Отчети“.

Като допълнителна софтуерна функция има елемент от менюто "Конзола", който ви позволява да изпълнявате SQL заявки към въведената от потребителя база данни и да извеждате резултата в таблица (компонент DataTable). За да направите това, разбира се, трябва да знаете имената на таблици и полета и по принцип той е създаден с цел удобство при внедряването на софтуер, но други „посветени“ потребители също могат да се възползват от това.

Пример за създаване на локална база данни на Microsoft SQL Server вMS Visual Studio

Тази тема показва решението на проблема със създаването на база данни като SQL Server с помощта на MS Visual Studio. Разглеждат се следните въпроси:

• работа с прозореца Server Explorer в MS Visual Studio;
• създаване на локална база данни като SQL Server Database;
• създаване на таблици в базата данни;
• редактиране на таблични структури;
• свързване на таблици на база данни една с друга;
• въвеждане на данни в таблици с помощта на MS Visual Studio.

Задачата

Използвайки инструментите на MS Visual Studio, създайте база данни на MS SQL Server с име Образование. Базата данни съдържа две таблици Student и Session. Таблиците са свързани една с друга чрез някакво поле.

Структурата на първата маса "Студент".

Структурата на втората таблица „Сесия“.

производителност

1. Изтеглете MS Visual Studio.

2. АктивирайтепрозорецServer Explorer.

За работа с бази данни, Microsoft предлага лек сървър на база данни, Microsoft SQL Server. Има различни версии на Microsoft SQL Server, например: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2014 и други.

Можете да изтеглите тези версии от уебсайта на Microsoft www.msdn.com.

Този сървър е чудесен за работа с бази данни. Той е безплатен и има графичен интерфейс за създаване и администриране на бази данни с помощта на инструмента за управление на SQL Server.

На първо място, преди да създадете базата данни, трябва да активирате помощната програма Server Explorer. За да направите това, в MS Visual Studio трябва да се обадите (фиг. 1)

Изглед -> Server Explorer

Ориз. 1. Извикване на Server Explorer

Веднъж извикан, прозорецът Server Explorer ще има приблизителен изглед, както е показано на Фигура 2.

Ориз. 2. Прозорец Server Explorer

3. Създаване на база данни "Образование".

За да създадете нова база данни на базата на доставчика на данни на Microsoft SQL Server, щракнете върху възела Data Connections и след това изберете „ Създайте нова база данни на SQL Server...” (фиг. 3).

Ориз. 3. Извикване на командата за създаване на база данни на SQL Server

В резултат на това прозорецът „ Създайте нова база данни на SQL Server"(фиг. 4).

Прозорецът (в полето "Име на сървъра") показва името на локалния сървър, инсталиран на вашия компютър. В нашия случай това име е „SQLEXPRESS“.

Полето „Име на нова база данни:“ съдържа името на базата данни, която ще бъде създадена. В нашия случай това е името Образование.

Оставете опцията Използване на удостоверяване на Windows непроменена и щракнете върху OK.

Ориз. 4. Създаване на нова база данни SQL Server 2008 Express с помощта на MS Visual Studio 2010

След извършените действия прозорецът Server Explorer ще приеме формата, както е показано на фигура 5. Както можете да видите от фигура 5, базата данни за образование с името

sasha-pc \ sqlexpress.Education.dbo

Ориз. 5. Прозорец Server Explorer след добавяне на базата данни за образование

4. Обекти на базата данни Образование.

Ако разширите базата данни за образование (знак "+"), можете да видите списък със следните основни обекти:

• Диаграми на база данни - диаграми на база данни. Диаграмите показват връзки между таблици на база данни, връзки между полета на различни таблици и т.н.;
• Таблици – таблици, които съхраняват данни от базата данни;
• Изгледи - гледки. Разликата между изгледите и таблиците е, че таблиците на базата данни съдържат данни, изгледите на данни не, а съдържанието се извлича от други таблици или изгледи;
• Съхранените процедури са съхранени процедури. Те са група от свързани SQL изрази, които осигуряват допълнителна гъвкавост при работа с база данни.

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

Към момента базата данни Education е напълно празна и не съдържа никакви обекти (таблици, съхранени процедури, изгледи и т.н.).

За да създадете таблица, трябва да извикате контекстното меню (щракнете с десния бутон) и да изберете командата „Добавяне на нова таблица“ (Фигура 6).

Ориз. 6. Команда за добавяне на нова таблица

Има и друга опция за добавяне на таблица на база данни с помощта на командите от менюто Данни:

Данни -> Добавяне на ново -> Таблица

Ориз. 7. Алтернативна опция за добавяне на нова таблица

В резултат на това ще се отвори прозорец за добавяне на таблица, който съдържа три колони (Фигура 8). В първата колона „Име на колона“ трябва да въведете името на съответното поле на таблицата на базата данни. Във втората колона „Тип данни“ трябва да въведете типа данни на това поле. Третата колона „Разрешаване на нула“ показва опцията за възможността за липса на данни в полето.

Ориз. 8. Прозорец за създаване на нова таблица

Използвайки редактора на таблици, трябва да създадете таблицата Student, както е показано на фигура 9. Името на таблицата трябва да бъде зададено, когато тя бъде затворена.

В редактора на таблици можете да зададете свойства на полето в прозореца Свойства на колоната. За да зададете дължината на низ (nvchar) в знаци, има свойство Length в прозореца Свойства на колоната. Стойността по подразбиране за това свойство е 10.

Ориз. 9. Студентска маса

Следващата стъпка е да зададете ключовото поле. Това става чрез извикване на командата „Задаване на първичен ключ“ от контекстното меню на полето Num_book. С помощта на ключовото поле ще се установят връзки между таблиците. В нашия случай ключовото поле е номерът на класната книга.

Ориз. 10. Задаване на ключовото поле

След като зададете първичния ключ, прозорецът на таблицата ще изглежда, както е показано на фигура 11.

Ориз. 11. Маса Студент след окончателно оформяне

Сега можете да затворите масата. В прозореца за запазване на таблицата трябва да зададете нейното име - Студент (фиг. 12).

Ориз. 12. Въвеждане на името на Студентската таблица

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

Следвайки примера за създаване на таблица Student, се създава таблицата Session.

Фигура 13 показва изгледа на таблицата на сесиите след окончателното формиране. Първичният ключ е зададен в полето Num_book. Името на таблицата се дава от сесията.

Ориз. 13. Сесийна маса

След завършените стъпки прозорецът Server Explorer ще покаже две таблици Student и Session.

По този начин към базата данни могат да бъдат добавени произволен брой таблици.

7. Редактиране на структурата на таблиците.

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

За да направите промени в таблиците на базата данни в MS Visual Studio, първо трябва да премахнете отметката от „Предотвратяване на запазване на промените които изискват повторно създаване на маса”Както е показано на Фигура 14. В противен случай MS Visual Studio ще блокира промените в предварително създадената таблица. Прозорецът с опции, показан на фигура 14, се извиква от менюто Инструменти в следната последователност:

Инструменти -> Опции -> Инструменти за база данни -> Дизайнери на таблици и бази данни

Ориз. 14. Опция “ Предотвратяване на запазване на промени, които изискват повторно създаване на таблица”

След персонализиране можете да промените структурата на таблицата. За да направите това, използвайте командата „Отваряне на дефиниция на таблица“ (Фигура 15) от контекстното меню, което се извиква за избраната таблица (щракване с десен бутон).

Ориз. 15. Извикване на командата "Open Table Definition"

Тази команда също се намира в менюто Данни:

Данни -> Отворете дефиницията на таблица

Първо трябва да изберете таблицата.

8. Установяване на връзки между таблици.

В съответствие с условието на задачата таблиците са свързани една с друга чрез полето Num_book.

За да създадете връзка между таблици, първо трябва (Фигура 16):

• изберете обекта Диаграма на базата данни;
• изберете командата Добавяне на нова диаграма от контекстното меню (или от менюто Данни).

Ориз. 16. Извикване на командата за добавяне на нова диаграма

В резултат на това ще се отвори прозорецът Добавяне на таблица за добавяне на нова диаграма (Фигура 17). В този прозорец трябва да изберете последователно две таблици Session и Student и да щракнете върху бутона Добавяне.

Ориз. 17. Прозорец за добавяне на таблици към диаграма

Ориз. 18. Таблици Студент и сесия след добавянето им към диаграмата

За да започнете да установявате връзка между таблиците, трябва да щракнете върху полето Num_book на таблицата Student и след това (без да отпускате бутона на мишката) да го плъзнете в полето Num_book на таблицата Session.

В резултат на това ще се отворят последователно два прозореца: Таблици и колони (фиг. 19) и Връзка с външния ключ (фиг. 20), в които трябва да оставите всичко както е и да потвърдите избора си с OK.

В прозореца Таблици и колони указвате името на връзката (FK_Session_Student) и имената на родителската (Студентска) и дъщерната таблица.

Ориз. 19. Прозорец таблици и колони

Ориз. 20. Прозорец за настройка на свойствата на връзката

След извършените действия ще се установи връзката между таблиците (Фигура 21).

Ориз. 21. Връзка между таблиците Student и Session

Записването на диаграма става по същия начин като записването на таблица. Името на диаграмата трябва да бъде избрано по ваша преценка (например Диаграма1).

След като посочите името на диаграмата, ще се отвори прозорецът Запазване, в който трябва да потвърдите избора си (Фигура 22).

Ориз. 22. Потвърждение за запазване на промените в таблици

9. Въвеждане на данни в таблици.

Microsoft Visual Studio ви позволява директно да въвеждате данни в таблици на база данни.

В нашия случай при установяване на връзка (фиг. 19) като основна се избира таблицата Student (Primary Key Table). Следователно, първо трябва да въведете данни в клетките на тази конкретна таблица. Ако първо се опитате да въведете данни в таблицата на сесиите, системата ще блокира такова въвеждане с изхода на съответното съобщение.

За да извикате режима за въвеждане на данни в Студентската таблица, трябва да извикате командата Покажи данни от таблицата от контекстното меню (щракване с десен бутон) или от менюто Данни (фиг. 23).

Ориз. 23. Команда за показване на данни от таблица

Ще се отвори прозорец, в който трябва да въведете входните данни (фиг. 24).

Ориз. 24. Въвеждане на данни в Студентска таблица

След като въведете данни в таблицата на студентите, трябва да въведете данните в таблицата за сесии.

Когато въвеждате данни в полето Num_book на таблицата Session, трябва да въведете точно същите стойности, които са въведени в полето Num_book на таблицата Student (тъй като тези полета са свързани).

Например, ако полето Num_book на таблицата Student съдържа стойностите “101”, “102”, “103” (виж фиг. 24), тогава тези стойности трябва да бъдат въведени в полето Num_book на таблицата Session. Ако се опитате да въведете различна стойност, системата ще покаже приблизително следния прозорец (фиг. 25).

Ориз. 25. Съобщение за грешка при въвеждане на данни от свързани таблици Student и Session

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