CommandText
Для извлечения таблиц и содержащихся в них данных используются SQL-запросы. Переменная CommandText содержит в себе SQL-запрос, синтаксис которого адаптирован для данного поставщика данных. Мы можем управлять извлечением данных, изменяя строку CommandText. Скопируйте папку проекта ProgrammDataSQL и назовите ее CommandText. Запустите проект и перейдите в код формы. Изменим string СommandText так, чтобы извлекать в DataGrid только поля CustomerID, ContactName, Country и Phone. Для этого удалим ненужные поля, в результате получится следующий SQL-запрос:
string CommandText = "SELECT CustomerID, ContactName, Country, Phone FROM Customers";
Запустите приложение. Теперь на форму выводятся только соответствующие четыре поля (рис. 2.25):
Рис. 2.25. Ограничение выводимых полей
Выведем теперь все записи клиентов, имена которых начинаются на "М":
string CommandText = "SELECT CustomerID, ContactName, Country, Phone FROM Customers where ContactName like 'M%'";
Запускаем приложение. Запрос выбрал только записи на букву "M" (рис. 2.26).
Рис. 2.26. Ограничение выводимых полей
Вы можете использовать все возможности языка SQL для отбора данных и модификации строки CommandText для получения нужного результата. Не пробуйте только использовать команды insert, update или delete - изменение записей в базе данных мы рассмотрим позже.
В программном обеспечении к курсу вы найдете приложение СommandText (Code\Glava1\ СommandText).
ConnectionString
Строка соединения ConnectionString определяет параметры, необходимые для установления соединения с источником данных. Строка соединений при использовании мастеров генерируется средой, но можно (и желательно - во избежание неточностей и ошибок) писать эту строчку вручную. Рассмотрим еще раз строки соединения, которые были созданы при подключении к базам данных xtreme и Northwind.
База данных xtreme, проект ProgrammDataMDB:
string ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Database Password=;Data Source=" "E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb"";Password=; Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0""; Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=; Mode=Share Deny None;Jet OLEDB:New Database Password=; Jet OLEDB:Create System Database=False;Jet OLEDB:Don' Jet Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin; Jet OLEDB:Encrypt Database=False";
База данных NorthwindCS, проект ProgrammDataSQL:
string ConnectionString = "workstation id=7EA2B2F6068D473;packet size=4096; integrated security=SSPI;data sou" + "rce=\"(local)\";persist security info=False; initial catalog=NorthwindCS";
В этих строках через точку с запятой просто перечисляются параметры соединения. В таблице 2.1 приводятся основные значения этих параметров.
| Provider (Поставщик) | Свойство применяется для установки или возврата имени поставщика для соединения, используется только для объектов OleDbConnection |
| Connection Timeout или Connect Timeout (Время ожидания связи) |
Длительность времени ожидания связи с сервером перед завершением попытки и генерацией исключения в секундах. По умолчанию 15 |
| Initial Catalog (Исходный каталог) |
Имя базы данных |
| Data Source (Источник данных) |
Имя используемого SQL-сервера, когда установлено соединение, или имя файла базы данных Microsoft Access |
| Password (Пароль) |
Пользовательский пароль для учетной записи SQL Server |
| User ID (Пользовательский ID) |
Пользовательское имя для учетной записи SQL Server |
| Workstation ID |
Имя рабочей станции или компьютера |
| Integrated Security или Trusted Connection (Интегрированная безопасность или Доверительное соединение) |
Параметр, который определяет, является ли соединение защищенным. True, False и SSPI - возможные значения. (SSPI - эквивалент True) |
| Persist Security Info (Удержание защитной информации |
Когда установлено False, нуждающаяся в защите информация, такая как пароль, не возвращается как часть соединения, если связь установлена или когда-либо была установленной. Выставление этого свойства в True может быть рискованным в плане безопасности. По умолчанию False |
При создании мастером строки ConnectionString происходит генерирование большого количества лишних параметров. Нельзя сказать, что они не нужны, просто мастер предусматривает все возможности использования этого подключения и вставляет соответствующие значения. В действительности необходимых значений для простого извлечения данных всего несколько:
Provider (только OLE DB)Data SourceInitial CatalogUser ID/PasswordPersist Security Info
Скопируйте папки с проектами ProgrammDataMDB и Programm DataSQL. Переименуйте копии в ConnStringMDB и ConnStringSQL. Измените значения ConnectionString следующим образом:
База данных xtreme, проект ConnStringMDB:
string ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0""; Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb""; User ID=Admin;Jet OLEDB:Encrypt Database=False";
База данных NorthwindCS, проект ConnStringSQL:
string ConnectionString = "workstation id=7EA2B2F6068D473;integrated security=SSPI; data source=\"(local)\";persist security info=False; initial catalog=NorthwindCS";
Мы значительно сократили количество параметров, получив прежнюю функциональность приложений.
Где определяется строка подключения, когда мы создаем объект DataAdapter не программно, а с помощью мастера? Среда генерирует строку подключения вместе с кодом для DataAdapter, и в этом нетрудно убедиться. Запустите приложение, которое мы делали, вообще ничего не зная об объектах ADO .NET - DataWizardMDB. Перейдите в код формы, откройте область Windows Form Designer generated code и найдите строку подключения:
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Database Password=;Data Source=" "D:\Uchebnik\Code\Glava4\RBProduct.mdb" ";Password=;Jet OLEDB:Engine Type=5; Jet OLEDB:Global Bulk Transactions=1;Provider=" "Microsoft.Jet.OLEDB.4.0""; Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=; Mode=Share Deny None;Jet OLEDB:New Database Password=; Jet OLEDB:Create System Database=False;Jet OLEDB:Don' Jet Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False; User ID=Admin;Jet OLEDB:Encrypt Database=False";
Когда мы переносим объект DataAdapter из панели инструментов Toolbox (со вкладки Data) на форму, вместе с ним образуется объект DBConnection, в свойствах которого указывается строка ConnectionString и другие параметры подключения (рис. 2.27):
увеличить изображение
Рис. 2.27. Свойства объекта sqlConnection1 проекта VisualDataSQL
DataAdapter
DataSet - это специализированный объект, содержащий образ базы данных. Для осуществления взаимодействия между DataSet и источником данных используется объект типа DataAdapter. Само название этого объекта - адаптер, преобразователь, - указывает на его природу. DataAdapter содержит метод Fill() для обновления данных из базы и заполнения DataSet.
Использование визуальной среды для работы с ADO .NET
Когда мы перетаскиваем на форму элемент управления, Visual Studio .NET автоматически генерирует код, описывающий этот элемент. Если при размещении кнопок, текстовых полей и других элементов управления создавать их программно нецелесообразно, то при работе с элементами данных все как раз наоборот - лучше всего создавать все объекты ADO .NET вручную, что обеспечивает большую гибкость приложения. Тем не менее на первых порах использования ADO .NET полезно работать с визуальной средой разработки.
Рассмотрим работу с базой данных Microsoft Acсess xtreme1), входящей в состав Microsoft Visual Studio.NET, и другую - с базой Microsoft SQL2) NorthwindCS . В каждой базе есть таблица Customer(s)3). Наша задача - вывести содержимое двух таблиц Customer на две Windows-формы.
| Microsoft Access, база данных xtreme | Microsoft SQL, база данных NorthwindCS |
Запустите Visual Studio .NET, создайте новый проект, тип проекта - Windows Application.
| Назовите его VisualDataMDB | Назовите его VisualDataSQL |
Размещаем на создавшейся форме элемент управления DataGrid, свойству Dock устанавливаем значение "Fill". Теперь в окне ToolBox переходим на закладку Data (рис. 2.8).
Рис. 2.8. На вкладке Data находятся все элементы управления для работы с ADO.NET
| Поместите на форму объект OleDbDataAdapter | Поместите на форму объект SqlDataAdapter |
Перед нами появляется мастер настройки элемента управления. Для OleDbDataAdapter этот мастер - тот же самый, который был описан и изображен на рис. 2.2 - 2.3. Вернитесь к этим рисункам и сконфигурируйте эти два шага OleDbDataAdapter самостоятельно. Для SqlDataAdapter на вкладке "Поставщик данных" по умолчанию выбран необходимый поставщик (рис. 2.9).
Рис. 2.9. Поставщик данных Microsoft OLE DB Provider for SQL Server
Нажимаем кнопку "Далее" (или переходим на вкладку "Подключение") (рис. 2.10).
Рис. 2.10. Определение параметров подключения
Для подключения к базе данных SQL, расположенной на вашем компьютере, в пункте 1 вводим (local) или просто точку - ".".
В пункте 2 используем учетные сведения системы: по умолчанию пароль учетной записи администратора SQL Server - пустой. В пункте 3 выбираем базу данных NorthwindCS.
В следующем шаге мастера - определение типа запроса. Параметры, предлагаемые мастером, будут отличаться (рис. 2.11 и рис. 2.12):
Рис. 2.11. Определение типа запросов объекта OleDbDataAdapter
Рис. 2.12. Определение типа запросов объекта SqlDbDataAdapter
Для базы данных Microsoft Access мы можем только использовать SQL-запросы - другие значения не активны. Для базы данных Microsoft SQL Server мы можем либо применять SQL-запросы, либо создать новую хранимую процедуру (stored procedure), либо использовать существующую хранимую процедуру. Различия подобного рода определяются типом баз данных. Для объекта SqlDataAdapter оставляем значение, предложенное по умолчанию: использование SQL-запросов.
В следующем шаге - создание SQL-запросов (Generate SQL statements) - мы можем вводить текст запроса непосредственно. Но удобнее всего воспользоваться "Построителем запросов": нажимаем на кнопку "Query Builder_" (рис. 2.13). Поскольку окна добавления таблиц для обеих баз данных одинаковы - незначительно различается лишь содержимое, - я привожу рисунок для OleDbDataAdapter:
Рис. 2.13. Добавление таблицы в окно Построителя запросов
Выбираем таблицу Customer (Customers), нажимаем кнопку Add и затем Close. Затем в окне Query Builder выбираем все столбцы (All Colimns), как показано на рис. 2.14:
Рис. 2.14. Добавление столбцов. Поставив галочку напротив пункта (All Columns), мы извлекаем все столбцы из таблицы
Нажимаем кнопку OK. В окне "Generate the SQl statements", к которому мы вернулись, приводится SQL- запрос, созданный мастером:
| SELECT Customer.* FROM Customer | SELECT Customers.* FROM Customers |
Нажимаем кнопку Next. В окне "Обзор результатов мастера" ( View Wizard Results) приводится список результатов работы мастера. Вид этого списка может отличаться, как это изображено на рис. 2.15 и 2.16:
Рис. 2.15. Список результатов работы мастера объекта OleDbDataAdapter
Рис. 2.16. Список результатов работы мастера объекта SqlDbDataAdapter
Чем обусловлено это различие? Для базы данных Access мастером создана возможность не только просмотра данных из базы данных - SQL-запрос SELECT, но и возможность ее изменения - запросы INSERT, UPDATE, DELETE. Для базы данных SQL Server создана лишь возможность просмотра базы данных. Для изменения данных необходимо проходить авторизацию (см. рис. 2.10) с учетной записью администратора базы данных.
Нажимаем кнопку Finish. Для объекта OleDbDataAdapter возникает окно (см. рис. 2.6), которое мы уже описывали выше.
Мастер создал на панели компонент по два элемента: oleDb DataAdapter1 и oleDbConnection1 для VisualDataMDB и sqlDbData Adapter1 и sqlDbConnection1 для VisualDataSQL. oleDbDataAdapter1 (или sqlDbDataAdapter1) является основным компонентом типа OleDbData Adapter (или SqlDataAdapter), который мы создавали при помощи мастера. Объект oleDbConnection1 (или sqlDbConnection1) был создан мастером как необходимый элемент подключения к базе. Образно говоря, мы получили адаптер (DataAdapter1) и вилку (Connection1) для подключения к источнику данных. Теперь нам необходимо добавить объект DataSet - тот самый "буфер", в котором будет храниться информация, полученная из базы данных. Щелкаем на oleDbDataAdapter1 (или sqlDbDataAdapter1), для его выделения. Открываем свойства этого элемента. На и нформационной панели щелкаем на ссылку "Generate DataSet" для создания объекта DataSet4) (рис. 2.17).
Рис. 2.17. Создание DataSet
В появившемся окне "Generate DataSet" задаем имена - dsCustomer и dsCustomers соответственно.
Итак, все "невидимые" части механизма подключения к базе данных у нас уже есть.
Осталось теперь связать видимый интерфейс с данными. Объектом, отображающим данные на форме, у нас является dataGrid1. В свойстве этого элемента DataSource (источник данных) выбираем в качестве источника созданный объект dsCustomer1 (рис. 2.18).
Рис. 2.18. Определение источника данных для элемента управления dataGrid1
На форме в режиме дизайна у нас немедленно появилась символическая таблица со знаком (+). Последнее, что нам осталось сделать, - это заполнить объект DataSet (здесь - dsCustomer1). Переходим в код форм и вносим соответствующие добавления:
using System.Data.OleDb; // Подключаем пространство имен для работы с поставщиком OleDb
private void Form1_Load(object sender, System.EventArgs e) { oleDbDataAdapter1.Fill(dsCustomer1); }
using System.Data.SqlClient; // Подключаем пространство имен для работы с поставщиком SQL
private void Form1_Load(object sender, System.EventArgs e) { sqlDataAdapter1.Fill(dsCustomers1); }
В обоих случаях в методе загрузки формы вызываем метод Fill объекта oleDbDataAdapter1 (или oleDbDataAdapter1) и передаем ему в качестве параметра dsCustomer1. Опять же, говоря образно, мы "заливаем" данные из адаптера в DataSet, который затем распоряжается ими - здесь мы определили, что DataSet выступает в качестве источника данных для dataGrid1.
Запустите приложения. Щелкните на знак (+), а затем на ссылку "Customer". Готовые приложения будут иметь следующий вид (рис. 2.19 и 2.20):
Рис. 2.19. Готовое приложение VisualDataMDB
Рис. 2.20. Готовое приложение VisualDataSQL
В программном обеспечении к курсу вы найдете эти приложения5) - Code\Glava1\VisualDataMDB и VisualDataSQL.
Модель объектов ADO .NET
Модель объектов ADO .NET состоит из набора классов. DataSet представляет собой класс ADO .NET, который отвечает за отображение таблицы (или таблиц, или даже всей базы) используемой базы данных, на компьютере пользователя без непрерывной связи с базой данных. В приложении, созданном с помощью Data Form Wizard, мы имели возможность вносить изменения в загруженную таблицу, перемещаться по записям, причем до нажатия кнопки Update изменения в самой базе данных, т.е. в файле RBProduct.mdb, не происходило. В чем же дело? Дело в том, что все данные и были загружены в объект DataSet, созданный мастером. Только при нажатии кнопки Update происходила передача данных из DataSet в саму базу данных. Не следует путать объект DataSet c оперативной памятью компьютера - загруженная таблица не находится в оперативной памяти компьютера.
Представьте себе, что вы обслуживаете гипотетическую доску объявлений. Каждое утро вы берете обычный бумажный блокнот, записываете в него все свежие объявления, хранящиеся в централизованном банке данных, и отправляетесь к доске. Вы переписываете из блокнота на доску все объявления, а в блокнот записываете все те объявления, которые были добавлены на доску посетителями. Затем вы возвращаетесь в банк данных и вносите в него информацию, которую вы записали в блокнот c доски. Ваш блокнот и будет являться экземпляром класса DataSet. В этом процессе самым важным - для вас, конечно, не для централизованного банка данных, - будет ваш блокнот. Аналогично, класс DataSet является ключевым во всей модели классов ADO .NET.
DataSet состоит из объектов типа DataTable и объектов DataRelation. В коде к ним можно обращаться как к свойствам объекта DataSet, т.е., используя точечную нотацию. Свойство Tables возвращает объект типа DataTableCollection, который содержит все объекты DataTable используемой базы данных.
Объект Command
Объект Command применяется для выполнения SQL-запросов к источнику данных. Чтобы выполнить запрос, свойству Connection объекта Command следует задать объект имя созданного Connection:
OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConnectionString; conn.Open(); OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = conn;
Объект Connection также предоставляет метод CreateCommand, позволяющий упростить данный процесс - этот метод возвращает новый объект Command, уже инициализированный для использования вашего объекта Connection:
OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConnectionString; conn.Open(); OleDbCommand myCommand = conn.CreateCommand();
Эти два способа совершенно эквивалентны.
Теперь нам следует определить SQL-запрос, который будет извлекать данные. Как и раньше, строкой, в которой будет содержаться этот запрос, будет CommandText. Объявляем переменную CommandText, извлекающую все столбцы таблицы Customer:
string сommandText = @"SELECT Address1, Address2, City, [Contact First Name], [Contact Last Name], [Contact Position], [Contact Title], Country, [Customer Credit ID], [Customer ID], [Customer Name], [E-mail], Fax, [Last Year's Sales], Phone, [Postal Code], Region, [Web Site] FROM Customer";
Объект myСommand имеет свойство, которое так и называется - CommandText. Чтобы избежать путаницы, изменим название переменной - CommandText на commandText (с маленькой буквы):
myCommand.CommandText = commandText;
Создаем объект OleDbDataAdapter:
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
Объект dataAdapter имеет свойство SelectCommand в котором мы и будем указывать объект myCommand:
dataAdapter.SelectCommand = myCommand;
Создаем объект DataSet:
DataSet ds = new DataSet();
Заполняем ds данными из dataAdapter:
dataAdapter.Fill(ds, "Customers");
Указываем источник данных DataSource для dataGrid1:
dataGrid1.DataSource = ds.Tables["Customers"].DefaultView;
Закрываем соединение явным образом:
conn.Close();
Все! Запускаем приложение. Мы получили уже знакомый результат, но теперь мы действительно управляем всеми объектами, работающими с данными.
Полный листинг проекта ConnectionMDB:
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb;
namespace ConnectionMDB { /// <summary> /// Summary description for Form1. /// </summary> public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.DataGrid dataGrid1; string ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0""; Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb" ";User ID=Admin;Jet OLEDB:Encrypt Database=False";
string commandText = @"SELECT Address1, Address2, City, [Contact First Name], [Contact Last Name], [Contact Position], [Contact Title], Country, [Customer Credit ID], [Customer ID], [Customer Name], [E-mail], Fax, [Last Year's Sales], Phone, [Postal Code], Region, [Web Site] FROM Customer";
/// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null;
public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); OleDbConnection conn = new OleDbConnection(ConnectionString); //OleDbConnection conn = new OleDbConnection(); //conn.ConnectionString = ConnectionString; conn.Open(); OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = conn; myCommand.CommandText = commandText; //OleDbCommand myCommand = conn.CreateCommand(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Customers"); dataGrid1.DataSource = ds.Table ["Customers"].DefaultView; conn.Close();
// // TODO: Add any constructor code after InitializeComponent call // }
/// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); }
Windows Form Designer generated code
/// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); }
private void Form1_Load(object sender, System.EventArgs e) {
} } }
Сравните использование строк ConnectionString и connectingText объектом myCommand с аналогичным использованием объектом DataAdapter (ProgrammDataMDB и ProgrammDataSQL).
Создание объекта Connection для базы данных SQL принципиально не отличается от примера, рассмотренного выше. В следующем фрагменте кода описываются те же объекты Connection и Command:
using System.Data.SqlClient;
string CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers"; string connectionString = "workstation id=7EA2B2F6068D473; integrated security=SSPI;data source=\"(local)\"; persist security info=False;initial catalog=NorthwindCS";
SqlConnection conn = new SqlConnection(connectionString); //SqlConnection conn = new SqlConnection(); //conn.ConnectionString = ConnectionString; conn.Open(); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = conn; myCommand.CommandText = CommandText; //SqlCommand myCommand = conn.CreateCommand(); SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Customers"); dataGrid1.DataSource = ds.Tables["Customers"].DefaultView; conn.Close();
В программном обеспечении к курсу вы найдете приложения ConnectionMDB и ConnectionSQL (Code\Glava1\ ConnectionMDB и ConnectionSQL).
У вас наверняка могло сложиться впечатление, что использование визуальных средств Microsoft Visual Studio .NET для добавления и конфигурирования объектов ADO .NET несравненно проще и логичней, чем программный способ создания этих объектов.
Однако это впечатление глубоко ошибочно. В действительности только последний пример может претендовать на завершенное приложение - все остальные являются своего рода вспомогательными модулями. Повторю, что только полностью вручную написанный код объектов ADO .NET позволяет создавать ясные, надежные и гибкие приложения.
 |
|
|
База данных Microsoft Access расположена по адресу: С(имя диска):\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb.
2)
Разумеется, на вашем компьютере должен быть установлен Microsoft SQL Server 2000 и запущено приложение Service Manager.
3)
В MS Access таблица называется Customer, в SQL - Customers.
4)
Можно также создать объект DataSet, "перетащив" его на форму из вкладки Data панели инструментов ToolBox.
5)
Если вы попытаетесь запустить эти приложения на своем компьютере, скорее всего, возникнет исключение. Дело в том, что параметры ConnectionString у нас с вами, безусловно, различаются. Для запуска следует изменить значение, соответствующее расположению базы данных (например, xtreme) на вашем компьютере. Как это сделать - см далее ConnectionString.
6)
Имя компьютера, расположение базы данных, разумеется, могут отличаться - эти значения верны только для моего компьютера.
7)
Здесь и далее - листинг указан без области Windows Form Designer generated code: когда весь пользовательский интерфейс состоит из единственного элемента DataGrid, нет особого смысла описывать его.
Объекты DataRelation
Объект DataSet имеет также свойство Relations, возвращающее коллекцию DataRelationCollection, которая в свою очередь состоит из объектов DataRelation. Каждый объект DataRelation выражает отношение между двумя таблицами (сами таблицы связаны по какому-либо полю (столбцу). Следовательно, эта связь осуществляется через объект DataColumn).
Объекты DBConnection и DBCommand
Объект DBConnection осуществляет связь с источником данных. Эта связь может быть одновременно использована несколькими командными объектами. Объект DBCommand позволяет послать базе данных команду (как правило, команду SQL или хранимую процедуру). Объекты DBConnection и DBCommand иногда создаются неявно в момент создания объекта DataSet, но их также можно создавать явным образом.
Подключение к базе данных - технология ADO .NET. Мастер Data Form Wizard
Мастер Data Form Wizard из Visual Studio .NET позволяет быстро, буквально в несколько шагов, создать связанную с данными форму. Код, генерируемый мастером, можно будет просматривать и изменять.
Мы рассмотрим подключение к базе данных Microsoft Access BDTur_firm.mdb, которую мы создали сами. Наша задача: вывести содержимое двух связанных таблиц - "Туристы" и "Информация о туристах" на Windows-форму.
Запустите Visual Studio .NET, создайте новый проект, тип проекта - Windows Application. Назовите его DataWizardMDB.
В окне Solution Explorer щелкаем правой кнопкой мыши на имени проекта DataWizardMDB и в появившемся меню выбираем Add/Add New Item. В появившемся окне выбираем мастер Data Form Wizard, как показано на рис. 2.1:
Рис. 2.1. Создание Data Form Wizard
Называем новую форму DataFormMDB.cs. В появившемся мастере нажимаем кнопку "Next". В следующем шаге определяется объект DataSet, название которого должно соответствовать содержимому, поэтому называем его dsTourists. В следующем шаге мастера "Choose a data connection" требуется создать подключение к базе данных. Поскольку у нас еще нет никакого подключения, нажимаем на кнопку "New Connection". В появившемся окне "Свойства связи с данными" необходимо определить параметры создаваемого подключения. Переключаемся на вкладку "Поставщик данных" для выбора нужного поставщика OLE DB (рис. 2.2):
Рис. 2.2. Определение поставщика данных
Поставщик данных Microsoft Jet 4.0 OLE DB Provider позволяет подключаться к базам данных формата Microsoft Aceess (mdb). Нажимаем кнопку "Далее" (или переходим на вкладку "Подключение") (рис. 2.3 ):
Рис. 2.3. Задание параметров подключения
В пункте 1 нажимаем кнопку (...) и указываем путь к базе данных BDTur_firm.mdb, расположенную на вашем компьютере. В пункте 2 можно оставить значение по умолчанию - если вы специально не изменяли имя пользователя и пароль доступа к базе, то стандартное имя "Admin" позволит создать подключение.
Если вы правильно настроили права доступа к базе данных, то при нажатии кнопки "Проверить подключение" система выдаст сообщение "Проверка подключения выполнена". На вкладках "Дополнительно" и "Все" можно устанавливать права доступа и просматривать все свойства формируемого подключения. Не изменяя ничего на этих вкладках, нажимаем "OK".
Далее нам предстоит определить, какую таблицу базы данных мы собираемся извлекать. В окне мастера перечислены все доступные в схеме базы данных таблицы, представления и хранимые процедуры. Выберите в списке Available Items нужные таблицы и переместите их в список Selected Items, щелкнув кнопку с направленной вправо стрелкой. Если вы ошиблись и хотите удалить какие-то таблицы из списка Selected Items, выберите их и щелкните кнопку со стрелкой влево. Кроме того, добавлять и удалять таблицы можно, дважды щелкнув их название мышью. Выбираем две таблицы: "Туристы" и "Информация о туристах" (рис. 2.4):
Рис. 2.4. Выбор таблиц, которые будут отображаться на форме
Теперь необходимо определить отношение между таблицами. Отношения позволяют обеспечивать соблюдение правил ссылочной целостности, каскадно передавая изменения от одной таблицы к другой. Кроме того, они упрощают поиск данных в таблицах. Название отношения рекомендуется составлять из имен родительской и дочерней таблиц (именно в таком порядке). В качестве имени выберем TouristsInfoTourists. Далее определяем родительскую таблицу (Parent table) - "Туристы" и дочернюю (Child table) - "Информация о туристах". Поле "Код туриста" определяет связь между этими таблицами. Щелкните кнопку со стрелкой вправо, чтобы добавить отношение в список Relations, и затем щелкните Next.
Рис. 2.5. Задание связей между таблицами
Далее определяем столбцы (поля), извлекаемые из обеих таблиц. Оставляем все поля (рис. 2.5).
В последнем шаге мастера предстоит определить вид размещения данных на форме - всех записей в виде таблицы (All records in a grid) либо каждой записи в отдельном текстовом поле (Single records in individual control).
Выбираем второй вариант. Здесь же можно определить наличие дополнительных элементов управления - кнопок навигации и изменения записей. Оставляем эти значения по умолчанию. Завершаем работу мастера, нажимая кнопку "Finish".
Возникает сообщение - "Пароль будет сохранен в виде текста и будет доступен для чтения в коде и сборке". Выбрав "Include password", вы включаете пароль в приложение (он будет доступен), но избавляетесь от необходимости введения имени пользователя и пароля каждый раз при подключении к базе данных (рис. 2.6).
Рис. 2.6. Выбор расположения пароля
Практически все готово. Однако, запустив приложение, мы обнаруживаем, что по-прежнему появляется пустая форма Form1. Переходим в код Form1 и копируем следующий участок кода:
[STAThread] static void Main() { Application.Run(new Form1()); }
В коде DataFormMDB после Windows Form Designer generated code вставляем код и изменяем название запускаемой формы:
static void Main() { Application.Run(new DataFormMDB ()); }
В окне Solution Explorer щелкаем правой кнопкой на Form1.cs и удаляем его. Теперь, запуская приложение, мы видим DataFormMDB (рис. 2.7):
Рис. 2.7. Готовая форма
В созданном приложении имеется несколько кнопок. Для загрузки данных нажимаем кнопку Load. Для перемещения по записям используем навигационные кнопки. При этом во второй таблице отображается список продуктов, поставляемых данным поставщиком. Для добавления новой или удаления текущей записи служат кнопки Add и Delete, для отмены изменений текущей записи - Cancel. Все изменения буферизуются и могут быть отменены нажатием кнопки Cancel All. Для передачи изменений в базу данных нажимаем кнопку Update - тогда записи изменяются в самом файле BDTur_firm.mdb.
Подключение к базе данных с помощью мастера позволяет создать приложение без всякого знания технологии ADO .NET. Конечно же, подобная разработка не может нас устраивать для коммерческих приложений, требующих несравненно более гибкого кода.DataForm Wizard всего лишь генерирует код, который можно просматривать и изменять в соответствии с задачами проекта.
В программном обеспечении к курсу вы найдете приложение DataWizardMDB (Code\Glava1 DataWizardMDB).
Программирование объектов ADO .NET
Мы рассмотрели создание приложений для работы с базами данных с использованием различных мастеров. Однако разрабатывать такие приложения можно также без использования визуальной среды. Создадим такие же приложения, как и в предыдущем примере.
Будет работать с теми же самыми таблицами.
| Microsoft Access, база данных xtreme Таблица Customer |
Microsoft SQL, база данных NorthwindCS Таблица Customers. |
Запустите Visual Studio .NET, создайте новый проект, тип проекта - Windows Application.
| Назовите его ProgrammDataMDB | Назовите его ProgrammDataSQL |
Размещаем на создавшейся форме элемент управления DataGrid, свойству Dock устанавливаем значение "Fill". Переходим в код формы. Подключаем соответствующие пространства имен:
| using System.Data.OleDb; | using System.Data.SqlClient; |
В конструкторе формы после InitializeComponent создаем объект DataAdapter:
public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString); }public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); SqlDataAdapter dataAdapter = new SqlDataAdapter(CommandText, ConnectionString);
}
В качестве параметров DataAdapter мы передаем CommandText и ConnectionString. Переменная типа string CommandText представляет собой обычный SQL-запрос на выборку из таблицы Customer, а переменная типа СonnectionString - это так называемая строка подключения, в которой указываются расположение базы данных, ее название, параметры авторизации и проч. Далее мы рассмотрим более подробно эти строки. Как составить эти переменные? Можно, конечно, написать вручную, но мы сейчас воспользуемся строчками, сгенерированными мастером. Откройте предыдущие проекты - VisualDataMDB и VisualDataSQL. Перейдите в код формы. Раскройте область Windows Form Designer generated code, щелкнув на знак (+). Найдите следующие строчки:
Для CommandText:
this.oleDbSelectCommand1.CommandText = @"SELECT Address1, Address2, City, [Contact First Name], [Contact Last Name], [Contact Position], [Contact Title], Country, [Customer Credit ID], [Customer ID], [Customer Name], [E-mail], Fax, [Last Year's Sales], Phone, [Postal Code], Region, [Web Site] FROM Customer";this.sqlSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers";
Для ConnectionString6):
this.oleDbConnection1.ConnectionString = @" Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=; Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Database Password=;Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb" ";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=; Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don'Jet Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";this.sqlConnection1.ConnectionString = "workstation id=7EA2B2F6068D473;packet size=4096;integrated security=SSPI; data source=\"(local) \";persist security info=False; initial catalog=NorthwindCS";
Скопируйте эти строчки, начиная от названия самих переменных, затем в коде форм ProgrammDataMDB и ProgrammDataSQL в классе Form 1 объявите две переменные по две переменных типа string и вставьте скопированные значения:
string CommandText = @"SELECT Address1, Address2, City, [Contact First Name], [Contact Last Name], [Contact Position], [Contact Title], Country, [Customer Credit ID], [Customer ID], [Customer Name], [E-mail], Fax, [Last Year's Sales], Phone, [Postal Code], Region, [Web Site] FROM Customer";string ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Database Password=;Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb"";Password=; Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0""; Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=; Mode=Share Deny None;Jet OLEDB:New Database Password=; Jet OLEDB:Create System Database=False;Jet OLEDB:Don' Jet Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False; User ID=Admin;Jet OLEDB:Encrypt Database=False";string CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers";string ConnectionString = "workstation id=7EA2B2F6068D473;packet size=4096;integrated security=SSPI;data sou" + "rce=\"(local)\";persist security info=False;initial catalog=NorthwindCS";
Обратите внимание на названия переменных CommandText и ConnectionString. Когда мы создаем объект DataAdapter, в качестве параметров мы можем передать названия строк, таких как cmdText и conString, или даже cmt и cns - совершенно равноправно, не забыв, конечно же, назвать также эти переменные в классе Form1. Но сама среда Visual Studio .NET генерирует эти строки именно с такими названиями - CommandText и ConnectionString, поэтому если вы пишите их не вручную, то облегчаете работу, называя их так же, как и среда.
Возвращаемся к нашим приложениям - ProgrammDataMDB и ProgrammDataSQL. Дальнейший код будет совершенно одинаковым для обоих приложений.
Итак, создаем объект DataSet:
DataSet ds = new DataSet();
Заполняем таблицу Customer объекта ds данными из базы:
dataAdapter.Fill(ds, "Customer");
Cвязываем источник данных объекта dataGrid1 (который мы нанесли на форму в режиме дизайна) с таблицей Customer объекта ds:
dataGrid1.DataSource = ds.Tables["Customer"].DefaultView;
Все! Запускаем оба приложения. Если вы были внимательны в самом начале, то заметили, что в базе данных xtreme таблица называется Customer, а в базе данных NorthwindCS - Customers ( "s" на конце). Тем не менее код работает для обоих приложений. В чем же дело? Таблица, которую мы называем "Customer", при вызове метода Fill объекта dataAdapter может быть названа как угодно - ее содержимое будет представлять собой извлекаемую таблицу из базы данных. При указании источника данных (DataSource) для объекта dataGrid1 мы ссылаемся именно на таблицу Customer, которая была создана при вызове метода Fill. Точнее говоря, эта таблица определена в объекте DataSet, а именно к нему мы и обращаемся для заполнения данными нашей формы в соответствии с моделью ADO .NET. Чтобы убедиться в этом, попробуйте такой код:
DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Bezimyannaya"); dataGrid1.DataSource = ds.Tables["Bezimyannaya"].DefaultView;
По-прежнему все работает.
Означает ли это, что можно небрежно относиться к названию таблицы в DataSet? Нет - в нашем учебном примере мы с самого начала извлекали только одну таблицу из базы данных. В реальных приложениях приходится иметь дело с десятками таблиц, и будет возникать большая путаница, если называть таблицы как попало. Поэтому следует давать названия таблицам в объекте DataSet названия тех таблиц, образы которых они представляют.
Полный7) листинг программы - подключение к базе данных xtreme:
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb;
namespace ProgrammDataMDB { /// <summary> /// Summary description for Form1. /// </summary> public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.DataGrid dataGrid1; /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; string CommandText = @"SELECT Address1, Address2, City, [Contact First Name], [Contact Last Name], [Contact Position], [Contact Title], Country, [Customer Credit ID], [Customer ID], [Customer Name], [E-mail], Fax, [Last Year's Sales], Phone, [Postal Code], Region, [Web Site] FROM Customer"; string ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0""; Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=; Jet OLEDB:Create System Database=False;Jet OLEDB:Don' Jet Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin; Jet OLEDB:Encrypt Database=False";
public Form1() {
// // Required for Windows Form Designer support // InitializeComponent();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString); DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Bezimyannaya"); dataGrid1.DataSource = ds.Tables["Bezimyannaya"].DefaultView; // // TODO: Add any constructor code after InitializeComponent call // }
/// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); }
Windows Form Designer generated code
/// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); }
private void Form1_Load(object sender, System.EventArgs e) {
} } }
Полный листинг программы - подключение к базе данных NorthwindCS:
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient;
namespace ProgrammDataSQL { /// <summary> /// Summary description for Form1. /// </summary> public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.DataGrid dataGrid1; /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; string CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region," + " PostalCode, Country, Phone, Fax FROM Customers"; string ConnectionString = "workstation id=7EA2B2F6068D473;packet size=4096;integrated security=SSPI;data sou" + "rce=\"(local)\";persist security info=False;initial catalog=NorthwindCS";
public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); SqlDataAdapter dataAdapter = new SqlDataAdapter(CommandText, ConnectionString); DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Customer"); dataGrid1.DataSource = ds.Tables["Customer"].DefaultView;
// // TODO: Add any constructor code after InitializeComponent call // }
/// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); }
Windows Form Designer generated code
/// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); }
private void Form1_Load(object sender, System.EventArgs e) {
} } }
Server Explorer
В состав Visual Studio .NET входит замечательный инструмент управления и обзора подключениями к базам данных - Server Explorer. С его помощью можно практически мгновенно создавать приложения, использующие базы данных. Создайте новый проект. Назовите его Server_Explorer. Выберите в меню View пункт Server Explorer (или воспользуйтесь сочетанием клавиш Ctrl+Alt+S). Появится окно Server Explorer. Щелкните на Data Connections правой кнопкой мыши и выберите пункт "Add Connection" (рис. 2.21):
Рис. 2.21. Добавление соединения
Появляется окно мастера "Свойства связи с данными", с которым мы уже встречались (см. рис. 2.2 и 2.3). Создайте самостоятельно подключение к базе данных xtreme - после того как вы это сделаете, оно появится в списке всех подключений окна Server Explorer (рис. 2.22):
увеличить изображение
Рис. 2.22. В окне Server Explorer выводятся все созданные подключения к базам данных
Щелкните на знак (+) около названия подключения, откройте Tables, выберите таблицу Customer и перетащите ее на форму (рис. 2.23).
Рис. 2.23. Содержимое базы данных xtreme
При появлении уже знакомого окна расположения пароля (см. рис. 2.6) выбираем "Include Password".
На панели компонент формы появились два элемента: oleDb Connection1 и oleDbDataAdapter1. Выделяем щелчком oleDbDataAdapter1, открываем его свойства, на информационной панели нажимаем "Generate DataSet". Называем объект "DataSet dsCustomer" и нажимаем "ОК". Помещаем на форме элемент управления DataGrid, в свойствах этого элемента указываем расположение Dock - Fill, DataSource - dsCustomer и переходим в код формы. Добавляем уже знакомый нам код:
private void Form1_Load(object sender, System.EventArgs e) { oleDbDataAdapter1.Fill(dsCustomer1); }
Запускаем приложение. Его внешний вид ничем не отличается от приложения, рассмотренного в предыдущем примере.
Созданное подключение к базе данных xtreme теперь будет отображаться во всех последующих проектах в окне Server Explorer. Вам остается только выбирать нужную таблицу и перетаскивать ее на форму.
Вы также можете использовать окно Solution Explorer для быстрого просмотра содержимого баз данных и - если подключение было создано с правами администратора - изменения их. Откройте нужную таблицу и просто дважды щелкните на нее (рис. 2.24):
увеличить изображение
Рис. 2.24. Просмотр таблицы Customers базы данных NorthwindCS
Строки (объект DataRow)
Коллекция Rows объекта DataTable возвращает набор строк (записей) заданной таблицы. Эта коллекция используется для изучения результатов запроса к базе данных. Мы можем обращаться к записям таблицы как к элементам простого массива.
Таблицы и поля (объекты DataTable и DataColumn)
Объекты DataTable используются для представления таблиц в DataSet. DataTable представляет одну таблицу из базы данных. В свою очередь, DataTable составляется из объектов DataColumn.
DataColumn - это блок для создания схемы DataTable. Каждый объект DataColumn имеет свойство DataType, которое определяет тип данных, содержащихся в каждом объекте DataColumn. Например, вы можете ограничить тип данных до целых, строковых и десятичных чисел. Поскольку данные, содержащиеся в DataTable, обычно переносятся обратно в исходный источник данных, вы должны согласовывать тип данных с источником.
Управление соединением. Объект Connection
Большинство источников данных поддерживает ограниченное количество соединений. Так, база данных Microsoft Access может поддерживать одновременную работу не более чем с 255 пользователями. При попытке обращения к базе данных, лимит соединений которой исчерпан, пользователь не получит нужной ему информации и будет вынужден ждать освобождения соединения. Задача разработчика заключается в минимизации времени связи с базой данных, поскольку соединение занимает полезные системные ресурсы.
Когда вы вызываете у объекта DataAdapter метод (например, Fill), то он сам проверяет, открыто ли соединение. Если соединения нет, то DataAdapter открывает соединение, выполняет задачи и затем закрывает соединение.
Явное управление соединением - лучший подход к работе с базами данных. Он обладает рядом преимуществ:
дает более чистый и удобный для чтения код;помогает при отладке приложения;является более эффективным.
Для явного управления соединением используется объект Connection. Создайте новый проект и назовите его ConnectionMDB. Перетащите элемент управления DataGrid из панели инструментов Toolbox и установите свойству Dock значение Fill. Перейдите в код формы. Подключаем пространство имен:
using System.Data.OleDb;
В конструкторе Form1 после InitializeComponent создаем объект Connection:
OleDbConnection conn = new OleDbConnection(ConnectionString);
В качестве параметра объекту conn передается строка подключения ConnectionString.
Можно также устанавливать строку подключения через свойство созданного объекта сonn:
OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConnectionString;
Теперь нам необходимо определить параметры самой строки ConnectionString. Вы можете сделать это вручную или скопировав код из приложения ConnStringMDB (подключаться будем к базе данных xtreme).
string ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0""; Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb"";User ID=Admin;Jet OLEDB:Encrypt Database=False";
Теперь можно устанавливать соединение, вызывая метод Open объекта Connection:
OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConnectionString; conn.Open();
Перемещение по записям. Объект CurrencyManager
Продолжим работу над проектом DataBindings. При выводе данных в виде отдельных записей необходимо реализовать возможность перемещения по записям. Это можно сделать с помощью экземпляра класса CurrencyManager. Располагаем на форме четыре кнопки и надпись в ряд следующим образом (рис. 3.4):
Рис. 3.4. Расположение кнопок
Устанавливаем следующие свойства элементов управления:
| Кнопка | btnFirst | << |
| Кнопка | btnPrevious | < |
| Кнопка | btnNext | > |
| Кнопка | btnLast | >> |
| Надпись | lblRecordsPosition |
Объявляем экземпляр cmRecords класса CurrencyManager в классе формы:
CurrencyManager cmRecords;
В конструкторе формы Form1 связываем созданный объект cmRecords с таблицей "Туристы" объекта ds:
cmRecords = (CurrencyManager)BindingContext[ds, "Туристы"];
Создаем обработчиков для событий ItemChanged и PositionChanged объекта cmRecords:
cmRecords.ItemChanged+=new ItemChangedEventHandler(cmRecords_ItemChanged); cmRecords.PositionChanged+=new EventHandler(cmRecords_PositionChanged);
Вызываем метод, отображающий навигацию по записям:
DisplayRecordsPosition ();
Создаем этот метод:
private void DisplayRecordsPosition() { lblRecordsPosition.Text = "Запись " + (cmRecords.Position + 1) + " из " + cmRecords.Count; }
Добавляем методы, вызывающие метод DisplayRecordsPosition() в случае наступления событий ItemChanged и PositionChanged:
private void cmRecords_ItemChanged( object sender, ItemChangedEventArgs e) { DisplayRecordsPosition(); } private void cmRecords_PositionChanged( object sender, System.EventArgs e) { DisplayRecordsPosition(); }
Добавляем обработчиков для нажатий навигационных кнопок:
private void btnFirst_Click(object sender, System.EventArgs e) { cmRecords.Position = 0; }
private void btnPrevious_Click(object sender, System.EventArgs e) { cmRecords.Position--; }
private void btnNext_Click(object sender, System.EventArgs e) { cmRecords.Position++; }
private void btnLast_Click(object sender, System.EventArgs e) { cmRecords.Position = cmRecords.Count - 1; }
Запускаем приложение. Теперь можно перемещаться по записям (рис. 3.5):
Рис. 3.5. Форма с навигационными кнопками
В программном обеспечении к курсу вы найдете приложение DataBindings (Code\Glava2\DataBindings).
Подключение к базе данных Microsoft
Microsoft Access предоставляет средства распределенного доступа к базе данных. С одним файлом могут одновременно работать большое количество пользователей, обладающих разными правами: одни могут только просматривать таблицы, другие - только вносить новые данные, и лишь администраторы базы обладают полным доступом. Когда мы устанавливаем пакет Microsoft Office на локальный компьютер и, ни о чем не задумываясь, начинаем создавать свою базу в программе Access, мы по умолчанию выступаем в роли администратора. Поставим теперь задачу: разделить доступ для двух пользователей - один сможет только просматривать данные (читать), другой по-прежнему будет обладать полным доступом. Скопируйте файл BDTur_firm.mdb и назовите его "BDwithUsers.mdb". Открываем базу, в главном меню программы переходим "Сервис \ Защита \ Мастер" (см. рис. 2.7). Появляется мастер защиты, в первом шаге которого доступен единственный переключатель "Создать файл рабочей группы" ( рис. 3.16):
Рис. 3.16. Первый шаг мастера защиты
Файл рабочей группы представляет собой своеобразный электронный ключ, в котором будут храниться созданные настройки. Он имеет расширение *.mdw. В следующем шаге мастера нажимаем кнопку "Обзор" - по умолчанию мы оказываемся в той же директории, где расположен исходный файл базы данных BDwithUsers.mdb, вводим название создаваемого файла BDWorkFile.mdw3). Нажимаем кнопку "Выбрать", возвращаясь в окно мастера. Устанавливаем переключатель на значение "Использовать файл рабочей группы по умолчанию". На другие параметры - "Код рабочей группы", "Ваше имя", "Организация" - можно не обращать внимания (рис. 3.17). Нажимаем кнопку "Далее".
увеличить изображение
Рис. 3.17. Определение файла рабочей группы BDWorkFile.mdw
Теперь предстоит определить, какие объекты базы данных защищены. Оставляем все таблицы и нажимаем кнопку "Далее" (рис. 3.18).
База данных, защищенная на уровне пользователей, может также содержать пароль на открытие. Скопируйте файл базы BDwithUsers.mdb и назовите его "BDwithUsersP.mdb". Открываем новый файл в монопольном режиме от имени пользователя "Chingiz Kariev". В главном меню программы переходим "Сервис \ Защита \ Задать пароль базы данных". В появившемся окне вводим пароль "98765" и подтверждаем его. Теперь при повторном открытии от имени любого пользователя необходимо вначале проходить аутентификацию, а затем вводить пароль базы данных (рис. 3.39):
Рис. 3.39. Аутентификация и ввод пароля на открытие базы
Базы данных BDwithUsers.mdb и BDwithUsersP.mdb используют один файл рабочей группы BDWorkFile.mdw. Просмотрев его содержимое при помощи программы Access Password, мы обнаружим, что его содержимое не изменилось (см. рис. 3.33). Этого и следовало ожидать - в файле рабочей группы хранятся сведения об учетных записях пользователей и группах, но не пароли самих баз данных. Для просмотра пароля базы открываем файл BDwithUsersP.mdb (рис. 3.40):
увеличить изображение
Рис. 3.40. Просмотр файла BDwithUsersP.mdb
Создайте новое Windows-приложение и назовите его "Programm BDwithUsersP". На этот раз создадим подключение программным образом. Добавляем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой:
using System.Data.OleDb;
В классе формы создаем строки connectionString и commandText:
string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0" ";Data Source=""D:\Uchebnik\Code\Glava2\BDwithUsersP.mdb" ";Jet OLEDB:System database=""D:\Uchebnik\Code\Glava2 \BDWorkFile.mdw"";User ID=Adonetuser;Password=12345; Jet OLEDB:Database Password=98765;"; string commandText = "SELECT * FROM Туристы";
Здесь мы добавили значения пароля "98765" в параметре "Jet OLEDB:Database Password".
После запуска этой программы выбираем
После запуска этой программы выбираем файл MS Access, пароль к которому нужно подобрать, - и пароль моментально появляется в главном окне (рис. 3.9).
Рис. 3.9. Вскрытие пароля файла MS Access
Далее нам снова понадобится вводить пароль - чтобы не тратить время, установите снова его значение равным "12345". Займемся теперь подключением к файлу базы данных из приложений. Конечно, вы теперь будете сомневаться, применять ли вообще задание пароля как средство безопасности, но нам в любом случае нужно научиться подключаться даже к такой, "ненадежной" базе. Создайте новый Windows-проект и назовите его "VisualBD_withPassword". Из окна Toolbox перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Переходим на вкладку Data дважды щелкаем на объекте OleDbDataAdapter. В появившемся мастере настраиваем подключение к файлу BD_withPassword.mdb. Теперь в окне "Свойства связи с данными" при проверке соединения появляется сообщение об ошибке (рис. 3.10):
Рис. 3.10. Ошибка проверки подключения, возникающая при отсутствии пароля
Исходя из текста ошибки, возникает естественное желание снять галочку "Пустой пароль" и в поле пароля ввести "12345". Но и на этот раз возникает ошибка2) (рис. 3.11):
Рис. 3.11. Ошибка проверки подключения, возникающая при неправильном указании пароля
Дело в том, что интерфейс вкладки "Подключение" предназначен для ввода параметров учетной записи при защите на уровне пользователей. Далее мы рассмотрим ее. Здесь же снова устанавливаем галочку "Пустой пароль" и переходим на вкладку "Все". Дважды щелкаем на свойстве Jet OLEDB:Database Password (или, выделив его, нажимаем на кнопку "Изменить значение"), в появившемся окне "Изменение значения свойства" вводим пароль "12345" (рис. 3.12).
Рис. 3.18. Определение объектов базы данных, которые будут защищены
Мы добрались до самих рабочих групп. Программа Microsoft Access предлагает несколько рабочих групп, в каждой из которых может быть большое число пользователей. К примеру, пять пользователей могут обладать полными правами, десять - быть разработчиками проекта и еще пять - обладать правами на обновление данных. Код группы также можно не запоминать. Мы выбираем группу "Только чтение", отмечая ее галочкой4), нажимаем кнопку "Далее" (рис. 3.19).
Рис. 3.19. Выбор рабочей группы
Теперь требуется определить права группы Users. Это группа в любом случае будет входить в файл BDWorkFile.mdw; по умолчанию пользователи, входящие в нее, могут работать с базой данных без всякого пароля. Предоставление каких-либо прав этой группе означает предоставление этих же прав любому пользователю. Поэтому из соображений безопасности Microsoft Access предлагает вариант по умолчанию. Изменение этого варианта означает встраивание "черного" входа в файл рабочей группы. Мы оставляем предложенное значение и нажимаем кнопку "Далее" (рис. 3.20):
Рис. 3.20. Определение разрешений группы Users
В следующем шаге мастера следует определить пользователей и пароли. Именно эти сведения для каждого пользователя будут постоянно использоваться в работе с приложением, поэтому на них следует обратить внимание. В поле "Пользователь:" вводим "Adonetuser", задаем этому пользователю пароль "12345", нажимаем кнопку "Добавить пользователя в список". Поля "Пользователь" и "Пароль" очищаются, а в списке, расположенном в левой части формы, появляется новая запись. Нажимаем кнопку "Далее" (рис. 3.21).
Рис. 3.21. Добавление пользователя "Adonetuser"
Итак, теперь у нас уже есть рабочая группа - "Только чтение", и теперь появился пользователь "Adonetuser". Из выпадающего списка "Группа или пользователь" следует выбрать "Adonetuser" и отметить галочкой группу "Только чтение" (рис. 3.22, А). При выборе второго пользователя из выпадающего списка - здесь5) "Chingiz Kariev", - можно заметить, что он входит в группу Admins (рис. 3.22, Б).
Это очень важный момент: должен быть хотя бы один пользователь-администратор, входящий в эту группу, в противном случае после завершения работы мастера мы не сможем добавлять новых пользователей и изменять права существующих!
Рис. 3.22. Распределение пользователей в рабочие группы. А - включение пользователя "Adonetuser" в группу "Только чтение", Б - вхождение пользователя "Chingiz Kariev" в группу "Admins" по умолчанию
В последнем шаге мастера создается резервная копия базы данных. Она располагается в той же самой директории, где и основная (рис. 3.23). Несмотря на свое расширение - *.bak (backup), это, по сути, обычный файл базы данных Microsoft Access.
Рис. 3.23. Создание резервной копии базы данных
При нажатии на кнопку "Готово" появляется отчет, создаваемый мастером защиты. Он включает в себя перечень всех сведений, которые в дальнейшем могут понадобиться для восстановления доступа к базе:
Отчет мастера защиты Данный отчет содержит все сведения, необходимые для воссоздания файла рабочей группы и восстановления доступа к защищенной базе данных в случае повреждения. Напечатайте или экспортируйте этот отчет и сохраните его в надежном месте.
Незащищенная база данных: D:\Uchebnik\Code\Glava2\BDwithUsers.bak Защищенная база данных: D:\Uchebnik\Code\Glava2\BDwithUsers.mdb Файл рабочей группы: D:\Uchebnik\Code\Glava2\BDWorkFile.mdw Пользователь: Microsoft Access Организация:
Код рабочей группы: mx4qX5Gy6OGUgwmOZpt
Защищенные объекты: Таблицы: Информация о туристах Оплата Путевки Сезоны Туристы Туры <Новые таблицы и запросы> Запросы: <Новые таблицы и запросы> Формы: <Новые формы> Отчеты: <Новые отчеты> Макросы: <Новые макросы> База данных: Пароль VBE не установлен Группы: Имя: Только чтение Код группы: FecIdp7S4zQTMaV5qAp Users: Adonetuser
Имя: Admins Код группы: <Созданные ранее> Users: Chingiz Kariev
Имя: Users Код группы: <Созданные ранее> Users: Chingiz Kariev Adonetuser
Пользователи: Имя: Chingiz Kariev Личный код: ifXdiQ2D2GaQvBhly Пароль: Группы: Admins
Имя: Adonetuser Личный код: U6QTwfQ5qGu1Djmrbe4 Пароль: 12345 Группы: Только чтение
Имя: admin Личный код: <Созданные ранее> Пароль: S0nxw3IDds5rO Группы: Users
Отчет мастера защиты Дата работы г.
Желательно последовать совету мастера и сохранить эти сведения в надежном месте.
Итак, в результате всех проделанных действий в рабочем каталоге появились три файла - BDwithUsers.bak, BDwithUsers.mdb и BDWorkFile.mdw (рис. 3.24):
Рис. 3.24. Файлы, полученные в результате работы мастера
В программном обеспечении к курсу вы найдете эти три файла (Code\Glava2\ BDwithUsers.bak, BDwithUsers.mdb и BDWorkFile.mdw).
Файл BDwithUsers.bak тоже лучше сохранить в надежном месте, поскольку он представляет собой незащищенную копию базы данных.
Запускаем файл BDWorkFile.mdw - появляется окно, в котором следует ввести имя пользователя "Adonetuser" и пароль "12345" (рис. 3.25):
Рис. 3.25. Аутентификация пользователя "Adonetuser"
Открывается окно базы данных, в котором имеющиеся таблицы доступны только для чтения. Выходим из приложения и запускаем его снова. Введем на этот раз имя администратора базы - "Chingiz Kariev", без пароля6) (рис. 3.26).
Рис. 3.26. Аутентификация пользователя "Chingiz Kariev"
На этот раз база данных открывается с полным доступом, более того, выбрав пункт главного меню "Сервис \ Защита \ Мастер_", можно редактировать уже существующий файл рабочей группы, добавляя например, новых пользователей (рис. 3.27):
Рис. 3.27. Первый шаг мастера. Изменение файла BDWorkFile.mdw рабочей группы
При создании файла рабочей группы BDWorkFile.mdw мы определили его дальнейшее использование по умолчанию (см. рис. 3.17). Это означает, что вся дальнейшая работа с программой Microsoft Access на данном компьютере будет производиться от имени этого файла и определенных в нем пользователей. Без наличия соответствующих прав будет невозможно создать даже новую базу данных7). Для возврата к файлу рабочей группы, принятому по умолчанию, проделываем следующие действия:
включаем режим отображения скрытых файлов и папок; открываем базу данных BDwithUsers.mdb от имени администратора (здесь - от имени пользователя "Chingiz Kariev"); в главном меню программы переходим "Сервис \ Защита \ Администратор рабочих групп" (см. рис. 2.7); появляется окно "Администратор рабочих групп", в котором отображается связь с текущим файлом BDWorkFile.mdw. Нажимаем кнопку "Связь" для смены рабочего файла (рис. 3. 28):
Рис. 3.28. Окно "Администратор рабочих групп"
в появившемся окне "Файл рабочей группы" нажимаем кнопку "Обзор". Переходим в директорию C:\Documents and Settings\ Имя пользователя (здесь - Chingiz Kariev)\Application Data\ Microsoft\Access и выбираем файл System.mdw. Это файл рабочей группы, который программа использует с момента своей установки. Нажимаем кнопку "Открыть". В окне "Файл рабочей группы" нажимаем "OK". Появляется уведомление "Успешное присоединения файла рабочей группы" (рис. 3.29):
Рис. 3.29. Уведомление присоединения файла рабочей группы
нажимаем кнопку "OK" - теперь в окне "Администратор рабочих групп" отображается связь с файлом по умолчанию "System.mdw". Нажимаем "OK" для завершения (рис. 3.30):
Рис. 3.30. Связь с файлом System.mdw в окне "Администратор рабочих групп"
В результате проделанных операций мы вернулись к файлу рабочей группы, принятому по умолчанию. Теперь при создании новых баз данных снова будем работать от имени администратора "Admin".
Однако мы не сможем открыть базу данных BDwithUsers.mdb, использующую другой файл рабочей группы (рис. 3.31):
Рис. 3.31. Попытка открыть базу данных BDwithUsers.mdb
Для открытия базы данных нам снова нужно будет связаться со своеобразным электронным ключом - файлом BDWorkFile.mdw. Сделайте это самостоятельно.
Для распространения подготовленной базы данных на компьютеры пользователей вам потребуется скопировать сам файл базы данных и файл рабочей группы, а затем связать их.
Изменим пароль администратора "Chingiz Kariev" базы BDwithUsers.mdb. Открываем от имени этого пользователя базу данных, в главном меню переходим "Сервис \ Защита \ Пользователи и группы_" (см. рис. 3.7). В появившемся окне "Пользователи и группы" из выпадающего списка "Имя" выбираем этого пользователя, переходим на вкладку "Изменение пароля". Оставляя пустым поле "Текущий пароль", вводим и подтверждаем пароль "a1s2d3f4g5h6j7k8l9z0" (рис. 3.32):
Рис. 3.32. Изменение пароля администратора базы данных
Аналогично, открывая базу от имени пользователя "Adonetuser", можно изменить его пароль.
Может показаться, что вся эта продуманная система разделения пользователей предоставляет надежную безопасность создаваемым приложениям. Но это не так - вся информация по-прежнему хранятся в незашифрованном виде в файле рабочей группы. Достаточно получить к этому файлу доступ - а для работы самой базы данных это необходимое условие, - чтобы получить все сведения о пользователях и их паролях. Утилита Access Password (http://www.thegrideon.com) позволяет считывать все данные (рис. 3.33):
увеличить изображение
Рис. 3.33. Вскрытие учетных записей пользователей MS Access
С помощью утилиты просмотрим также содержимое файла System.mdw. Здесь мы видим всего одного пользователя "admin"с пустым паролем, обладающего правами администратора (рис. 3.34):
увеличить изображение
Рис. 3.34. Просмотра файла System.mdw
Именно от этого пользователя по умолчанию мы начинаем работать с MS Access после установки пакета Microsoft Office.
Займемся подключением к базе из приложений. Создайте новый Windows-проект и назовите его "VisualBDwithUsers". Добавляем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Переходим на вкладку Data панели инструментов Toolbox, дважды щелкаем на объекте OleDbDataAdapter. В появившемся мастере настраиваем подключение к базе BDwithUsers.mdb, в поле "Пользователь" вводим "Adonetuser", в поле "Пароль" - значение "12345". Но при проверке подключения снова появляется сообщение об ошибке (рис. 3.35):
Рис. 3.35. Ошибка при проверке подключения к базе "BDwithUsers.mdb"
В чем же дело? Мы ведь ввели все значения в специально предназначенные для этого поля! Интерфейс вкладки "Подключение" снова не предоставляет возможности ввода всех нужных сведений для подключения. Переходим на вкладку "Все", выделяем свойство "Jet OLEDB:System database" и нажимаем кнопку "Изменить значение" (можно также дважды щелкнуть на этом свойстве). В появившемся окне "Изменение значения свойства" указываем путь к файлу BDWorkFile.mdw вместе с его полным названием (включающим расширение файла) (рис. 3.36).
увеличить изображение
Рис. 3.36. Определение значение свойства "Jet OLEDB:System database"
Переходим на вкладку "Подключение", проверяем связь с базой - на этот раз проверка будет успешной (рис. 3.37).
Рис. 3.37. Успешная проверка подключения к базе "BDwithUsers.mdb"
Нажимаем кнопку "OK" для закрывания окна "Свойства связи с данными". Теперь появляется дополнительное окно, в котором следует повторно ввести пароль пользователя (рис. 3.38).
Рис. 3.38. Дополнительно окно подключения к базе данных
В этом окне заметно "обрезанное" расположение подписей полей: это можно считать недоработкой VS 2003 - даже при разрешении экрана 1280х1024 форма выглядит так же.
Дополнительно в выпадающем списке " Режим открытия" выбираем значение DB_MODE_READ, поскольку пользователь "Adonetuser" обладает правами только на чтение данных. Закрываем это окно, завершаем работу мастера, настраивая извлечение всех записей из таблицы "Туристы". В последнем шаге мастера оставляем включение пароля в строку connection string (см. рис. 3.14).
На панели компонент формы выделяем объект DataAdapter, переходим в его окно Properties и нажимаем на ссылку Generate dataset. Оставляем название объекта DataSet, предлагаемое по умолчанию. В конструкторе формы заполняем объект DataSet, а также определяем источник данных для элемента DataGrid:
public Form1() { InitializeComponent(); oleDbDataAdapter1.Fill(dataSet11); dataGrid1.DataSource = dataSet11.Tables[0].DefaultView; }
Запускаем приложение. На форму снова выводятся данные из базы (см. рис. 3.15).
В программном обеспечении к курсу вы найдете приложение VisualB DwithUsers (Code\Glava2\ VisualBDwithUsers).
Сделаем точно такое же приложение без использования визуальных средств студии. Создайте новый Windows-проект, назовите его "ProgrammBDwithUsers". Добавляем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой:
using System.Data.OleDb;
В классе формы создаем строки connectionString и commandText:
string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0" ";Data Source=""D:\Uchebnik\Code\Glava2\BDwithUsers.mdb" ";Jet OLEDB:System database=""D:\Uchebnik\Code\Glava2 \BDWorkFile.mdw"";User ID=Adonetuser;Password=12345;";
string commandText = "SELECT * FROM Туристы";
В конструкторе формы создаем все объекты ADO .NET:
public Form1() { InitializeComponent(); OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = connectionString; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandText, conn); DataSet ds = new DataSet(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; conn.Close(); }
В программном обеспечении к курсу вы найдете приложение ProgrammBDwithUsers (Code\Glava2\ ProgrammBDwithUsers).
Связывание элементов управления с данными
Представление данных в виде таблицы на форме достаточно удобно в ряде случаев: можно сразу просматривать большое количество записей. Однако для постоянного внесения изменений в базу данных более удобным будет индивидуальное представление записей, когда значение каждого поля находится в отдельном текстовом поле. Проект DataWizardMDB, рассмотренный в первой главе, представляет собой подобную форму.
Создайте новое Windows-приложение. Назовите его "DataTextBox". Переходим на вкладку Data панели инструментов Toolbox и перетаскиваем oleDbDataAdapter. В запустившемся мастере устанавливаем подключение к файлу Microsoft Access "BDTur_firm.mdb" и выбираем все поля таблицы "Туристы". В свойствах oleDbDataAdapter1 на информационной панели нажимаем "Generate Dataset". Называем его "dsTourists". Переходим в код формы, подключаем пространство имен:
using System.Data.OleDb;
В конструкторе формы после InitializeComponent вызываем метод Fill объекта oleDbDataAdapter:
oleDbDataAdapter1.Fill(dsTourists1);
Переключаемся в режим дизайна. Располагаем на форме Label и TextBox. В свойстве Text элемента label вводим "Фамилия", в этом же свойстве элемента textBox оставляем пустую строку. Свойству Size формы устанавливаем значение 300; 100.
Щелкаем на знак (+) свойства DataBindings элемента управления textBox. В значении поля Text этой группы снова щелкаем на знак (+) около элемента dsProvider и выбираем поле "Фамилия" (рис. 3.1):
Рис. 3.1. Связывание элемента textBox с данными
Запускаем приложение. Теперь в текстовое поле выводится первое значение столбца "Фамилия" (рис. 3.2):
Рис. 3.2. Готовая форма
В программном обеспечении к курсу вы найдете приложение DataTextBox (Code\Glava2\ DataTextBox).
Теперь рассмотрим связывание элементов управления с данными, осуществляемое программным образом. Создайте новое Windows-приложение. Назовите его "DataBindings". На создавшейся форме располагаем по четыре элемента TextBox и Label (рис. 3.3):
Обработка исключений
Подключение к базе данных представляет собой одно из слабых мест в работе программы. В силу самых разных причин клиент может не получить доступ к базе данных. Поэтому при создании приложения следует обязательно включать обработку исключений и возможность предоставления пользователю информации о них.
Для получения специализированных сообщений при возникновении ошибок подключения к базе данных Microsoft SQL Server используются классы SqlException и SqlError. Объекты этих классов можно применять для перехвата номеров ошибок, возвращаемых базой данных (таблица 4.2):
| 17 | Неверное имя сервера |
| 4060 | Неверное название базы данных |
| 18456 | Неверное имя пользователя или пароль |
Дополнительно вводятся уровни ошибок SQL Server, позволяющие охарактеризовать причину проблемы и ее сложность (таблица 4.3):
| 11-16 | Ошибка, созданная пользователем | Пользователь должен повторно ввести верные данные |
| 17-19 | Ошибки программного обеспечения или оборудования | Пользователь может продолжать работу, но некоторые запросы будут недоступны. Соединение остается открытым |
| 20-25 | Ошибки программного обеспечения или оборудования | Сервер закрывает соединение. Пользователь должен открыть его снова |
Создайте новое Windows-приложение и назовите его "ExceptionsSQL". Свойству Size формы устанавливаем значение "600;380". Добавляем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Перетаскиваем элемент Panel, определяем следующие его свойства:
| Dock | Right |
| Location | 392; 0 |
| Size | 200; 346 |
На панели размещаем четыре текстовых поля, надпись и кнопку:
| Name | txtDataSource |
| Location | 8; 8 |
| Size | 184; 20 |
| Text | Введите название сервера |
| Name | txtInitialCatalog |
| Location | 8; 40 |
| Size | 184; 20 |
| Text | Введите название базы данных |
| Name | txtUserID |
| Location | 8; 72 |
| Size | 184; 20 |
| Text | Введите имя пользователя |
| Name | txtPassword |
| Location | 8; 104 |
| Size | 184; 20 |
| Text | Введите пароль1) |
| Location | 16; 136 |
| Size | 176; 160 |
| Text |
| Name | btnConnect |
| Location | 56; 312 |
| Size | 96; 23 |
| Text | Соединение |
Интерфейс приложения готов. Подключаем пространство имен для работы с базой данных:
using System.Data.SqlClient;
Объекты ADO .NET и весь блок обработки исключений помещаем в обработчик кнопки "Соединение":
private void btnConnect_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); label1.Text = ""; try { //conn.ConnectionString = "workstation id=9E0D682EA8AE448;data source=\"(local) //\";" + "persist security info=True;initial catalog=Northwind; //user id=sa;password=12345";
//Строка ConnectionString в качестве параметров //будет передавать значения, введенные в текстовые поля: conn.ConnectionString = "initial catalog=" + txtInitialCatalog.Text + ";" + "user id=" + txtUserID.Text + ";" + "password=" + txtPassword.Text + ";" + "data source=" + txtDataSource.Text + ";" + "workstation id=9E0D682EA8AE448;persist security info=True;"; SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM Customers", conn); DataSet ds = new DataSet(); conn.Open(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; } catch (SqlException OshibkiSQL) { foreach (SqlError oshibka in OshibkiSQL.Errors) { //Свойство Number объекта oshibka возвращает //номер ошибки SQL Server switch (oshibka.Number) { case 17: label1.Text += "\nНеверное имя сервера!"; break; case 4060: label1.Text += "\nНеверное имя базы данных!"; break; case 18456: label1.Text += "\nНеверное имя пользователя или пароль!"; break; } //Свойство Class объекта oshibka возвращает //уровень ошибки SQL Server, //а свойство Message - уведомляющее сообщение label1.Text +="\n"+oshibka.Message + " Уровень ошибки SQL Server: " + oshibka.Class; } } //Отлавливаем прочие возможные ошибки: catch (Exception ex) { label1.Text += "\nОшибка подключения: " + ex.Message; } finally { conn.Dispose(); } }
Закомментированная строка подключения содержит обычное перечисление параметров. При отладке приложения будет легче сначала добиться наличия подключения, а затем осуществлять привязку параметров, вводимых в текстовые поля. Запускаем приложение. При вводе неверных параметров в надпись выводятся соответствующие сообщения, а при правильных параметрах элемент DataGrid отображает данные (рис. 4.11):
увеличить изображение
Рис. 4.11. Готовое приложение ExceptionsSQL
В программном обеспечении к курсу вы найдете приложение Exceptions SQL (Code\Glava2\ ExceptionsSQL).
Скопируйте папку приложения ExceptionsSQL и назовите ее "ExceptionsMDB". Удаляем с панели на форме имеющиеся текстовые поля и добавляем три новых:
| Name | txtDataBasePassword |
| Location | 8; 16 |
| Size | 184; 20 |
| Text | Введите пароль базы данных |
| Name | txtUserID |
| Location | 8; 48 |
| Size | 184; 20 |
| Text | Введите имя пользователя |
| Name | TxtPassword |
| Location | 8; 80 |
| Size | 184; 20 |
| Text | Введите пароль пользователя |
using System.Data.OleDb;
Обработчик кнопки "Соединение" будет выглядеть так:
private void btnConnect_Click(object sender, System.EventArgs e) { OleDbConnection conn = new OleDbConnection(); label1.Text = ""; try { // conn.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0""; //Data Source=""D:\Uchebnik\Code\Glava2\BDwithUsersP.mdb""; Jet OLEDB:System database=""D:\Uchebnik\Code\Glava2\BDWorkFile.mdw""; User ID=Adonetuser;Password=12345;Jet OLEDB:Database Password=98765;";
//Строка ConnectionString в качестве параметров //будет передавать значения, введенные в текстовые поля: conn.ConnectionString = "Jet OLEDB:Database Password=" + txtDataBasePassword.Text + ";" + "User ID=" + txtUserID.Text + ";" + "password=" + txtPassword.Text + ";" +
@"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""D:\Uchebnik\Code\Glava2\BDwithUsersP.mdb""; Jet OLEDB:System database=""D:\Uchebnik\Code\Glava \BDWorkFile.mdw"";";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM Туристы", conn); DataSet ds = new DataSet(); conn.Open(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; } catch (OleDbException oshibka) { // Пробегаем по всем ошибкам for (int i=0; i < oshibka.Errors.Count; i++) { label1.Text+= "Номер ошибки " + i + "\n" + "Сообщение: " + oshibka.Errors[i].Message + "\n" + "Номер ошибки NativeError: " + oshibka.Errors[i].NativeError + "\n" + "Источник: " + oshibka.Errors[i].Source + "\n" + "Номер SQLState: " + oshibka.Errors[i].SQLState + "\n"; } } //Отлавливаем прочие возможные ошибки: catch (Exception ex) { label1.Text += "\nОшибка подключения: " + ex.Message; } finally { conn.Dispose(); } }
Запускаем приложение (рис. 4.12). Свойство Message возвращает причину ошибки на русском языке, поскольку установлена русская версия Microsoft Office 2003. Свойство NativeError (внутренняя ошибка) возвращает номер исключения, генерируемый самим источником данных. Вместе или по отдельности со свойством SQL State их можно использовать для создания переключателя, предоставляющего пользователю расширенную информацию (мы это делали в приложении ExceptionsSQL) .
Рис. 4.12. Готовое приложение ExceptionsMDB
Кроме обработки исключений, здесь следует обратить внимание на интерфейс, предоставляющий возможность пользователю вводить сведения своей учетной записи. Теперь в целях безопасности приложения не нужно включать пароль в код при использовании мастеров, поскольку строка соединения будет генерироваться динамически. Разумеется, ваше приложение может иметь другой интерфейс - например, окно соединения будет появляться в дочерней форме.
В программном обеспечении к курсу вы найдете приложение Exceptions MDB (Code\Glava2\ ExceptionsMDB).
Подключение к базе данных Microsoft SQL Server с разделенным доступом
Среда Microsoft SQL Server предоставляет средства разделенного управления объектами сервера. Для доступа используются два режима аутентификации: режим аутентификации Windows (Windows Authentication) и режим смешанной аутентификации (Mixed Mode Authentication). При установке первый режим предлагается по умолчанию, поэтому, скорее всего, ваш сервер сконфигурирован с его использованием (рис. 4.1):
Рис. 4.1. Режим аутентификации Windows предлагаемый по умолчанию при установке
В этом случае аутентификация пользователя осуществляется операционной системой Windows. Затем SQL Server использует аутентификацию операционной системы для определения уровня доступа. При подключении в окне "Свойства связи с данными" мы также указывали этот режим (рис. 4.2):
Рис. 4.2. Режим аутентификации Windows в окне "Свойства связи с данными"
Смешанный режим позволяет проводить аутентификацию пользователя как средствами операционной системы, так и с применением учетных записей Microsoft SQL Server. Для включения этого режима запускаем SQL Server Enterprise Manager, на узле локального сервера щелкаем правой кнопкой и выбираем пункт меню "Свойства". В появившемся окне "SQL Server Properties" переходим на вкладку "Security", устанавливаем переключатель в положение "SQL Server and Windows" (рис. 4.3).
увеличить изображение
Рис. 4.3. Включение режима смешанной аутентификации
После подтверждения изменений закрываем окно свойств. Раскрываем узел "Security" текущего сервера, выделяем объект "Logins". В нем мы видим две записи - "BULTIN\Администраторы" и "sa". Первая из них предназначена для аутентификации учетных записей операционной системы. Вторая - "sa" (system administrator) - представляет собой учетную запись администратора сервера, по умолчанию она конфигурируется без пароля. Для его создания щелкаем правой кнопкой мыши на записи, в появившемся меню выбираем пункт "Свойства".
В поле "Password" окна " SQL Server Login Properties" вводим пароль "12345" и подтверждаем его (рис. 4.4):
увеличить изображение
Рис. 4.4. Установка пароля на учетной записи "sa"
Займемся теперь подключением к заданной базе данных, например Northwind, от имени учетной записи "sa". Создайте новое Windows-приложение, назовите его "VisualSQLUser_sa". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". В окне Toolbox переходим на вкладку Data и дважды щелкаем на объекте SqlDataAdapter. В появившемся мастере создаем новое подключение. В окне "Свойства связи с данными" указываем название локального сервера (local), имя пользователя (sa) и пароль (12345), а также базу данных Northwind (рис. 4.5):
Рис. 4.5. Окно "Свойство связи с данными". Приложение VisualSQLUser_sa
Дополнительно мы установили галочку "Разрешить сохранение пароля". При этом его значение (12345) будет сохранено в виде текста в строке connectionString. Пока мы вынуждены это сделать - интерфейс нашего приложения не предусматривает возможность ввода пароля в момент подключения. Завершаем работу мастера "Data Adapter Configuration Wizard", настраивая извлечение всех записей из таблицы Customers. В последнем шаге мы снова соглашаемся сохранить пароль в виде текста (рис. 4.6).
Рис. 4.6. Диалоговое окно сохранения пароля
На панели компонент формы выделяем объект DataAdapter, переходим в его окно Properties и нажимаем на ссылку "Generate dataset". Оставляем название объекта DataSet, предлагаемое по умолчанию. В конструкторе формы заполняем объект DataSet, а также определяем источник данных для элемента DataGrid:
public Form1() { InitializeComponent(); sqlDataAdapter1.Fill(dataSet11); dataGrid1.DataSource = dataSet11.Tables[0].DefaultView; }
Запускаем приложение. На форму выводятся записи таблицы Customers (рис. 4.7):
Рис. 4.7. Готовое приложение VisualSQLUser_sa
В программном обеспечении к курсу вы найдете приложение VisualSQL User_sa (Code\Glava2\ VisualSQLUser_sa).
Сделаем точно такое же приложение без использования визуальных средств студии. Создайте новый Windows-проект, назовите его "ProgrammSQLUser_sa". Добавляем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой:
using System.Data.SqlClient;
В классе формы создаем строки connectionString и commandText:
string connectionString = "workstation id=9E0D682EA8AE448; user id=sa;data source=\"(local)\";" + "persist security info=True;initial catalog=Northwind;password=12345"; string commandText = "SELECT * FROM Customers";
В конструкторе формы создаем все объекты ADO .NET:
public Form1() { InitializeComponent(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlDataAdapter dataAdapter = new SqlDataAdapter(commandText, conn); DataSet ds = new DataSet(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; conn.Close(); }
В программном обеспечении к курсу вы найдете приложение Programm SQLUser_sa (Code\Glava2\ ProgrammSQLUser_sa).
В отличие от подключений к базе данных Microsoft Access здесь мы не встретили никаких сложностей. Вкладка "Подключение" в окне "Свойства связи с данными" действительно предоставляет все средства для подключения к базе данных Microsoft SQL Server. Для аутентификации пользователя достаточно указать его имя и пароль.
Работа с пулом соединений. Microsoft SQL Profiler
Подключение к базе данных требует затрат времени - в самом деле, необходимо установить соединение по каналам связи, пройти аутентификацию и лишь после этого можно выполнять запросы и получать данные. Клиентское приложение, взаимодействующее с базой данных и закрывающее каждый раз соединение при помощи метода Close, будет не слишком производительным: значительная часть времени и ресурсов будет тратиться на установку повторного соединения. А что, если использовать трехуровневую модель, при которой клиентское соединение будет взаимодействовать с базой данных через промежуточный сервер? В этой модели клиентское приложение открывает соединение через промежуточный сервер (рис. 4.13, А). После завершения работы соединение закрывается приложением, но промежуточный сервер продолжает удерживать его в течение заданного промежутка времени, например, 60 секунд. По истечении этого времени промежуточный сервер закрывает соединение с базой данных (рис. 4.13, Б). Если в течение этой минуты, например, после 35 секунд, клиентское приложение снова требует связи с базой данных, то сервер просто предоставляет уже готовое соединение, причем после завершения работы обнуляет счет времени и готов снова минуту ждать обращения (рис. 4.13, В).
увеличить изображение
Рис. 4.13. Трехуровневая модель соединения с базой данных. А - открытие соединения, начало отсчета, Б - Закрытие соединения промежуточным сервером по истечении минуты, В - Обращение клиентского приложения и предоставление сервером соединения в течении минуты ожидания
В результате использования этой модели сокращается время, необходимое для установки связи с удаленной базой данных. Можно провести аналогию со следующей ситуацией: представьте, что вам и вашим друзьям нужно поговорить с одним и тем же человеком на другом конце земного шара. Вы можете звонить по отдельности и потратить достаточно много времени на то, чтобы дозвониться до человека, дождаться, пока его позовут к телефону, и т.п.
А можно собраться вместе с друзьями и дозвониться один раз, затем просто передавать трубку друг другу для разговора. Телефон в данном случае будет выступать в качестве пула соединений. Вернемся к нашей модели. Промежуточный сервер тоже будет выступать в качестве пула соединений. Более того, если к нему будут обращаться несколько клиентских приложений (несколько друзей), использующих одинаковую базу данных и параметры авторизации (один человек на другом конце земного шара), то выигрыш времени будет еще более заметным.
При создании подключения с использованием поставщиков данных .NET автоматически создается пул соединений. При вызове метода Close соединения не разрывается, а по умолчанию помещается в пул. В течение 60 секунд соединение остается открытым, и если оно не используется повторно, поставщик данных закрывает его. Если же по каким-либо причинам нам необходимо закрывать соединение, не помещая его в пул, в строке соединения СonnectionString нужно вставить дополнительный параметр. Для поставщика OLE DB:
OLE DB Services=-4;
Для поставщика SQL Server:
Pooling=False;
Теперь при вызове метода Close соединение действительно будет разорвано.
Поставщик данных Microsoft SQL Server предоставляет также дополнительные параметры управления пулом соединений (таблица 4.4).
| Connection Lifetime | Время (в сек.), по истечении которого открытое соединение будет закрыто и удалено из пула. Сравнение времени создания соединения с текущим временем проводится при возвращении соединения в пул. Если соединение не запрашивается, а время, заданное параметром, истекло, соединение закрывается. Значение 0 означает, что соединение будет закрыто по истечении максимального предусмотренного тайм-аута (60 сек.) | 0 |
| Enlist | Необходимость связывания соединения с контекстом текущей транзакции2) потока | True |
| Max Pool Size | Максимальное число соединений в пуле. При исчерпании свободных соединений клиентское приложение будет ждать освобождения свободного соединения | 100 |
| Min Pool Size | Минимальное число соединений в пуле в любой момент времени | 0 |
| Pooling | Использование пула соединений | True |
Для задания значения параметра, отличного от принятого по умолчанию, следует явно включить его в строку ConnectionString.
Для слежения за процессом подключения к серверу и организацией пула соединений воспользуемся утилитой Profiler3), входящей в пакет Microsoft SQL Server 2000. Переходим в меню "Пуск" к группе Microsoft SQL Server и запускаем утилиту. В появившемся окне программы переходим "File \ New \ Trace" (или используем сочетание клавиш Ctrl+N). Появляется подключение к серверу. Это окно нам уже знакомо по работе с программой Query Analyzer. На этот раз подключимся к серверу от имени администратора "sa" (рис. 4.14):
Рис. 4.14. Подключение к серверу
Далее появляется окно Trace Properties (Свойства трассировки), в котором можно задать название трассировки, а также расположение файла для сохранения (галочка "Save to file") (рис. 4.15).
Рис. 4.15. Свойства трассировки
Нажимаем кнопку "Run" для начала работы. Появляется окно, в котором будет записываться все обращения к серверу. Запускаем приложение ExceptionsSQL, вводим данные для аутентификации и нажимаем кнопку "Соединение" - SQL Profiler немедленно зафиксирует обращение (рис. 4.16).
увеличить изображение
Рис. 4.16. Окно трассировки после подключения к серверу
Соединение по умолчанию помещается в пул, в окне трассировки мы не видим его разрыва - записи "Audit Logout". Ее можно увидеть, завершив работу с приложением ExceptionsSQL (рис. 4.17).
увеличить изображение
Рис. 4.17. Окно трассировки после завершения работы с приложением "ExceptionsSQL"
Открываем проект ExceptionsSQL в среде Visual Studio .NET, изменим строку соединения - отключим необходимость создания пула. Строка ConnectionString теперь будет выглядеть так (добавлен параметр "Pooling"):
conn.ConnectionString = "initial catalog=" + txtInitialCatalog.Text + ";" + "user id=" + txtUserID.Text + ";" + "password=" + txtPassword.Text + ";" + "data source=" + txtDataSource.Text + ";" + "workstation id=9E0D682EA8AE448;persist security info=True;Pooling=False";
Теперь при соединении с сервером соединение будет разрываться без помещения в пул - запись "Audit Logout" будет появляться сразу же (рис. 4.18):
увеличить изображение
Рис. 4.18. Окно трассировки после подключения к серверу без создания пула соединений
Помещение соединений в пул, включенное по умолчанию, - одно из средств повышения производительности приложений. Не следует без надобности отключать эту возможность.
|
|
|
Для скрывания пароля при вводе можно в свойстве "PasswordChar" текстового поля ввести заменяющий символ, например, звездочку ("*").
2)
Описание транзакций см. в лекции 7.
3)
Подробное описание работы с этой утилитой вы можете найти здесь: http://www.intuit.ru/department/database/sqlserver2000/35/sqlserver2000_35.html
События объекта Connection
Класс Connection поддерживает несколько событий, позволяющих отслеживать статус соединения и получать уведомляющие сообщения для экземпляра этого класса. Описание событий приводится в таблице 4.1.
| Disposed | Возникает при вызове метода Dispose экземпляра класса |
| InfoMessage | Возникает при получении информационного сообщения от поставщика данных |
| StateChange | Возникает при открытии или закрытии соединения. Поддерживается информация о текущем и исходном состояниях |
При вызове метода Dispose объекта Connection происходит освобождение занимаемых ресурсов и сборка мусора. При этом неявно вызывается метод Close.
Рассмотрим применение события StateChange и обработчик события Disposed. Создайте новое Windows-приложение и назовите его "ConnectionEventsSQL". Свойству Size формы устанавливаем значение "600;300". Помещаем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Добавляем элемент Panel, его свойству Dock устанавливаем значение "Bottom". На панели размещаем две надписи и одну кнопку, устанавливая следующие их свойства:
| Location | 8; 8 |
| Size | 208; 80 |
| Text |
| Location | 232; 8 |
| Size | 208; 80 |
| Text |
| Name | btnFill |
| Location | 488; 40 |
| Text | Заполнить |
Подключаем пространство имен для работы с базой:
using System.Data.SqlClient;
В классе формы создаем строки connectionString и commandText:
string connectionString = "workstation id=9E0D682EA8AE448; packet size=4096;integrated security=SSPI;data source=\"(local)\"; persist security info=False;initial catalog=Northwind"; string commandText = "SELECT * FROM Customers";
Объекты ADO .NET будем создавать в обработчике события Click кнопки "btnFill":
private void btnFill_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; //Делегат EventHandler связывает метод-обработчик conn_Disposed //с событием Disposed объекта conn conn.Disposed+=new EventHandler(conn_Disposed); //Делегат StateChangeEventHandler связывает метод-обработчик //conn_StateChange с событием StateChange объекта conn conn.StateChange+= new StateChangeEventHandler(conn_StateChange); SqlDataAdapter dataAdapter = new SqlDataAdapter(commandText, conn); DataSet ds = new DataSet(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; //Метод Dispose, включающий в себя метод Close, //разрывает соединение и освобождает ресурсы.
conn.Dispose(); }
Не забывайте про возможности IntelliSense - как всегда, для создания методов-обработчиков дважды нажимаем клавишу TAB (рис. 4.8):
Рис. 4.8. Автоматическое создание методов-обработчиков
В методе conn_Disposed просто выводим текстовое сообщение в надпись "label2":
private void conn_Disposed(object sender, EventArgs e) { label2.Text+="Событие Dispose"; }
При необходимости в этом методе могут быть определены соответствующие события. В методе conn_StateChange будем получать информацию о текущем и исходном состояниях:
private void conn_StateChange(object sender, StateChangeEventArgs e) { label1.Text+="\nИсходное состояние: "+e.OriginalState.ToString() + "\nТекущее состояние: "+ e.CurrentState.ToString(); }
Запускаем приложение. До открытия соединения состояние объекта conn было закрытым. В момент открытия текущим состоянием становится открытое, а предыдущим - закрытое. Этому соответствуют первые две строки, выведенные в надпись (рис. 4.9):
Рис. 4.9. Готовое приложение ConnectionEventsSQL
После закрытия соединения (вызова метода Dispose) текущим состоянием становится закрытое, а предыдущим - открытое. Этому соответствуют последние две строки, выводимые в надпись.
Конечно, в таком предельно простом приложении и так ясен статус соединения. Но сама идея может применяться в любых, сколь угодно сложных приложениях, когда необходимо определять статус одного из нескольких подключений.
В программном обеспечении к курсу вы найдете приложение Connection EventsSQL (Code\Glava2\ ConnectionEventsSQL).
Создадим теперь аналогичное приложение, использующее базу данных Microsoft Access. Для того чтобы не терять время на создание пользовательского интерфейса, скопируйте папку приложения Connection EventsSQL и назовите ее "ConnectionEventsMDB". Перейдем к редактированию кода. Подключаем пространство имен для работы с базой данных:
using System.Data.OleDb;
В классе формы создаем строки connectionString и commandText:
string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0" "; Data Source=""E:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme.mdb"";User ID=Admin;Jet OLEDB:Encrypt Database=False"; string commandText = "SELECT * FROM Customer";
Здесь мы снова будем подключаться к базе данных xtreme.mdb. Обработчик события Click кнопки "btnFill" примет следующий вид:
private void btnFill_Click(object sender, System.EventArgs e) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = connectionString; //Делегат EventHandler связывает метод-обработчик conn_Disposed //с событием Disposed объекта conn conn.Disposed+=new EventHandler(conn_Disposed); //Делегат StateChangeEventHandler связывает метод-обработчик //conn_StateChange с событием StateChange объекта conn conn.StateChange+= new StateChangeEventHandler(conn_StateChange); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandText, conn); DataSet ds = new DataSet(); dataAdapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; //Метод Dispose, включающий в себя метод Close, //разрывает соединение и освобождает ресурсы. conn.Dispose(); }
Обработчики conn_Disposed и conn_StateChange будут иметь в точности такой же вид. Запускаем приложение - на форму снова выводится статус соединения (рис. 4.10):
Рис. 4.10. Готовое приложение ConnectionEventsMDB
В программном обеспечении к курсу вы найдете приложение Connection EventsMDB (Code\Glava2\ ConnectionEventsMDB).
Создание хранимых процедур в SQL Query Analyzer
Хранимая процедура - это одна или несколько SQL-конструкций, которые записаны в базе данных. Задача администрирования базы данных включает в себя в первую очередь распределение уровней доступа к ней. Разрешение выполнения обычных SQL-запросов большому числу пользователей может стать причиной неисправностей из-за неверного запроса или их группы. Чтобы их избежать, разработчики базы данных могут создать ряд хранимых процедур для работы с данными и полностью запретить доступ для обычных запросов. Такой подход при прочих равных условиях обеспечивает большую стабильность и надежность работы. Это одна из главных причин создания собственных хранимых процедур. Другие причины - быстрое выполнение, разбиение больших задач на малые модули, уменьшение нагрузки на сеть - значительно облегчают процесс разработки и обслуживания архитектуры "клиент-сервер".
Сами базы данных используют огромное количество встроенных хранимых процедур для функционирования. Запустим программу SQL Query Analyzer1), входящую в пакет Microsoft SQL Server 2000. Создадим новый бланк (Ctrl +N) и введем в нем следующее:
exec sp_databases
В результате выполнения выводится список всех баз, созданных на данном локальном сервере (рис. 5.1):
увеличить изображение
Рис. 5.1. Программа SQL Query Analyzer. Выполнение запроса. Выделена процедура "sp_databases"
Мы запустили одну из системных хранимых процедур, которая находится в базе master. Ее можно найти в списке "Stored Procedures" базы - все системные хранимые процедуры имеют приставку "sp". Обратите внимание, что системные процедуры выделяются бордовым цветом и для многих из них не нужно указывать в выпадающем списке конкретную базу. Запустим еще одну процедуру:
exec sp_monitor
В результате ее выполнения выводится статистика текущего SQL-сервера (рис. 5.2).
Рис. 5.2. Статистика Microsoft SQL-Server
Для вывода списка хранимых процедур в учебной базе Northwind используем следующую процедуру:
USE Northwind exec sp_stored_procedures
Можно было, конечно, указать название и в выпадающем списке
Рис. 5.3. Вывод списка хранимых процедур базы данных Northwind
Перейдем к созданию своих собственных процедур. Скопируйте базу BDTur_firm.mdb из лекции 1, назовите ее "BDTur_firm2.mdb". Открываем ее в Microsoft Access и в названиях таблиц и полей удаляем все пробелы. Например, таблица "Информация о туристах" будет теперь называться так: "Информацияотуристах", а поле "Код туриста" станет полем "Кодтуриста". Затем конвертируем базу в формат Microsoft SQL и присоединяем ее к локальному серверу2). Запускаем SQL Query Analyzer, открываем чистый бланк и вводим запрос3):
create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы
Здесь create procedure - оператор, указывающий на создание хранимой процедуры, proc1 - ее название, далее после оператора as следует обычный SQL-запрос. Запускаем его - появляется сообщение:
The COMMAND(s) completed successfully.
Это означает, что мы все сделали правильно и команда создала процедуру proc1. Для просмотра результата вызываем ее:
exec proc1
Появляется уже знакомое нам извлечение всех записей таблицы "Туристы" со всеми записями (рис. 5.4):
Рис. 5.4. Результат запуска процедуры proc1
Как видите, создание содержимого хранимой процедуры не отличается ничем от создания обычного SQL-запроса. В таблице 5.1 приведены примеры хранимых процедур:
| 1 | create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы | exec proc1 | Вывод всех записей таблицы Туристы |
| Результат запуска | |||
| |||
| 2 | create procedure proc2 as select top 3 Фамилия from туристы | exec proc2 | Вывод первых трех значений поля Фамилия таблицы Туристы |
| Результат запуска | |||
 | |||
| 3 | create procedure proc3 as select * from туристы where Фамилия = 'Андреева' | exec proc3 | Вывод всех полей таблицы Туристы, содержащих в поле Фамилия значение " Андреева " |
| Результат запуска | |||
 | |||
| 4 | create procedure proc4 as select count (*) from Туристы | exec proc4 | Подсчет числа записей таблицы Туристы |
| Результат запуска | |||
 | |||
| 5 | create procedure proc5 as select sum(Сумма) from Оплата | exec proc5 | Подсчет значений поля Сумма таблицы Оплата |
| Результат запуска | |||
 | |||
| 6 | create procedure proc6 as select max(Цена) from Туры | exec proc6 | Вывод максимального значения поля Цена таблицы Туры |
| Результат запуска | |||
 | |||
| 7 | create procedure proc7 as select min(Цена) from Туры | exec proc7 | Вывод минимального значения поля Цена таблицы Туры |
| Результат запуска | |||
 | |||
| 8 | create procedure proc8 as select * from Туристы where Фамилия like '%и%' | exec proc8 | Вывод всех записей таблицы Туристы, содержащих в значении поля Фамилия букву "и" (в любой части слова) |
| Результат запуска | |||
 | |||
| 9 | create procedure proc9 as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc9 | Операция inner join объединяет записи из двух таблиц, если поле (поля), по которому связаны эти таблицы, содержат одинаковые значения. Общий синтаксис выглядит следующим образом: from таблица1 inner join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2 |
| Результат запуска | |||
 | |||
| 10 | create procedure proc10 as select * from Туристы left join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc10 | Прежде чем создать эту процедуру и затем ее извлечь, запускаем программу SQL Server Enterprise Manager, выделяем таблицу "Туристы" базы данных " BDTur_firm2". Щелкаем на ней правой кнопкой и в появившемся меню выбираем Open Table - Return all rows. Теперь добавляем запись - "Корнеев Глеб Алексеевич". В результате в таблице "Туристы" у нас получилось 6 записей, а в связанной с ней таблице "Информацияотуристах" - 5. В SQL Query Analyzer создаем хранимую процедуру и запускаем ее. Операция left join используется для создания так называемого левого внешнего соединения. С помощью объединения выбираются все записи первой (левой) таблицы, даже если они не соответствуют записям во второй (правой) таблице. Общий синтаксис имеет вид: from таблица1 left join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2. Здесь в таблице "Информацияотуристах" нет связанной записи для туриста "Корнеев Глеб Алексеевич", поэтому соответствующие поля заполняются значениями null |
| Результат запуска | |||
 | |||
| 11 | create procedure proc11 as select * from Туристы right join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста | exec proc11 | Перед созданием этого запроса нам снова придется изменить таблицы. В SQL Server Enterprise Manager удаляем шестую запись в таблице "Туристы", добавляем шестую запись в таблицу " Информацияотуристах"(значения полей - см. на рисунке). Операция right join используется для создания правого внешнего соединения. С его помощью выбираются все записи второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице. Общий синтаксис имеет вид: from таблица1 right join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2. |
| Результат запуска | |||
 | |||
На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:
create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля "Фамилия" установлен этот тип. Попытаемся запустить процедуру:
exec proc_p1
Появляется диагностическое сообщение (рис. 5.5):
Рис. 5.5. Сообщение при запуске процедуры exec proc_p1
Перевод этого сообщения: "Процедура 'proc_p1' ожидает параметр '@Фамилия', который не указан".
Запустим процедуру так:
exec proc_p1 'Андреева'
В результате выводится запись, соответствующая фамилии "Андреева" (рис. 5.6):
Рис. 5.6. Запуск процедуры proc_p1
Если мы укажем фамилию, которая не содержится в таблице, появится пустая запись (рис. 5.7):
exec proc_p1 'Сидоров'
Рис. 5.7. Запуск процедуры proc_p1. Фамилия не найдена
В таблице 5.2 приводятся примеры хранимых процедур с параметрами.
| 1 | create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия | exec proc_p1 'Андреева' |
| Описание | ||
| Извлечение записи из таблицы "Туристы" с заданной фамилией | ||
| Результат запуска | ||
| ||
| 2 | create proc proc_p2 @nameTour nvarchar(50) as select * from Туры where Название=@nameTour | exec proc_p2 'Франция' |
| Описание | ||
| Извлечение записи из таблицы "Туры" с заданным названием тура. Обратите внимание на название параметра "nameTour " - он может быть произвольным, не обязательно, чтобы он совпадал с заголовком столбца извлекаемой таблицы | ||
| Результат запуска | ||
 | ||
| 3 | create procedure proc_p3 @Фамилия nvarchar(50) as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста = Информацияотуристах.КодТуриста where Туристы.Фамилия = @Фамилия | exec proc_p3 'Андреева' |
| Описание | ||
| Вывод родительской и дочерней записей с заданной фамилией из таблиц "Туристы" и "Информацияотуристах" | ||
| Результат запуска | ||
 | ||
| 4 | create procedure proc_p4 @nameTour nvarchar(50) as select * from Туры inner join Сезоны on Туры.Кодтура=Сезоны.Кодтура where Туры.Название = @nameTour | exec proc_p4 'Франция' |
| Описание | ||
| Вывод родительской и дочерней записей с заданной названием тура из таблиц "Туры" и "Сезоны" | ||
| Результат запуска (изображение разрезано) | ||
 | ||
| 5 | create proc proc_p5 @nameTour nvarchar(50), @Курс float as update Туры set Цена=Цена/(@Курс) where Название=@nameTour | exec proc_p5 'Франция', 26 или exec proc_p5 @nameTour = 'Франция', @Курс= 26 Просматриваем изменения простым SQL - запросом: select * from Туры |
| Описание | ||
| Процедура с двумя входными параметрами - названием тура и курсом валюты. При извлечении процедуры они последовательно указываются. Поскольку в самом запросе используется оператор update, не возвращающий данных, то для просмотра результата следует извлечь измененную таблицу оператором select | ||
| Результат запуска | ||
(1 row(s) affected) После запуска оператора select: | ||
| 6 | create proc proc_p6 @nameTour nvarchar(50), @Курс float = 26 as update Туры set Цена=Цена/(@Курс) where Название=@nameTour | exec proc_p6 'Таиланд' или exec proc_p6 'Таиланд', 28 |
| Описание | ||
| Процедура с двумя входными параметрами, причем один их них - @Курс имеет значение по умолчанию. При запуске процедуры достаточно указать значение первого параметра - для второго параметра будет использоваться его значение по умолчанию. При указании значений двух параметров будет использоваться введенное значение | ||
| Результат запуска | ||
| Запускаем процедуру с одним входным параметром: exec proc_p6 'Таиланд' Для просмотра используем оператор select:  Запускаем программу SQL Server Enterprise Manager, восстанавливаем значение поля "Цена" для тура "Таиланд" и запускаем процедуру с двумя входными параметрами:  exec proc_p6 'Таиланд', 28 Теперь используется введенное значение второго параметра: | ||
Процедуры с выходными параметрами позволяют возвращать значения, получаемые в результате обработки SQL-конструкции при подаче определенного параметра. Представим, что нам нужно получать фамилию туриста по его коду (полю "Кодтуриста"). Создадим следующую процедуру:
create proc proc_po1 @TouristID int, @LastName nvarchar(60) output as select @LastName = Фамилия from Туристы where Кодтуриста = @TouristID
Оператор output указывает на то, что выходным параметром здесь будет @LastName. Запустим эту процедуру, извлекая фамилию туриста, значение поля "Кодтуриста" которого равно "4":
declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName
Оператор declare нужен для объявления поля, в которое будет выводиться значение. Получаем фамилию туриста (рис. 5.8)
Рис. 5.8. Результат запуска процедуры proc_po1
Для задания названия столбца можно применить псевдоним:
declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName as 'Фамилия туриста'
Теперь столбец имеет заголовок (рис. 5.9):
Рис. 5.9. Результат запуска процедуры proc_po1. Применение псевдонима
В таблице 5.3 приводятся примеры хранимых процедур с входными и выходными параметрами.
| 1 | create proc proc_po1 @TouristID int, @LastName nvarchar(60) output as select @LastName = Фамилия from Туристы where Кодтуриста = @TouristID | declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName as 'Фамилия туриста' |
| Описание | ||
| Извлечение фамилии туриста по заданному коду | ||
| Результат запуска | ||
| ||
| 2 | create proc proc_po2 @CountCity int output as select @CountCity = count(Кодтуриста) from Информацияотуристах where Город like '%рг%' | declare @CountCity int exec proc_po2 @CountCity output select @CountCity as 'Количество туристов, проживающех в городах %рг%' |
| Описание | ||
| Подсчет количества туристов из городов, имеющих в своем названии сочетание букв "рг". Следует ожидать число три (Екатеринбург, Оренбург, Санкт-Петербург) | ||
| Результат запуска | ||
 | ||
| 3 | create proc proc_po3 @TouristID int, @CountTour int output as select @CountTour = count(Туры.Кодтура) from Путевки inner join Сезоны on Путевки.Кодсезона = Сезоны.Кодсезона inner join Туры on Туры.Кодтура = Сезоны.Кодтура inner join Туристы on Путевки.Кодтуриста = Туристы.Кодтуриста where Туристы.Кодтуриста = @TouristID | exec proc_po3 '1', @CountTour output select @CountTour AS 'Количество туров, которые турист посетил' |
| Описание | ||
| Подсчет количества туров, которых посетил турист с заданным значением поля "Кодтуриста" | ||
| Результат запуска | ||
 | ||
| 4 | create proc proc_po4 @TouristID int, @BeginDate smalldatetime, @EndDate smalldatetime, @SumMoney money output as select @SumMoney = sum(Сумма) from Оплата inner join Путевки on Оплата.Кодпутевки = Путевки.Кодпутевки inner join Туристы on Путевки.Кодтуриста = Туристы.Кодтуриста where Датаоплаты between(@BeginDate) and (@EndDate) and Туристы.Кодтуриста = @TouristID | declare @TouristID int, @BeginDate smalldatetime, @EndDate smalldatetime, @SumMoney money exec proc_po4 '1', '1/20/2007', '1/20/2008', @SumMoney output select @SumMoney as 'Общая сумма за период' |
| Описание | ||
| Подсчет общей суммы, которую заплатил данный турист за определенный период. Турист со значением "1" поля "Кодтуриста" внес оплату 4/13/2007 | ||
| Результат запуска | ||
 | ||
| 5 | create proc proc_po5 @CodeTour int, @ChisloPutevok int output as select @ChisloPutevok = count(Путевки.Кодсезона) from Путевки inner join Сезоны on Путевки.Кодсезона = Сезоны.Кодсезона inner join Туры on Туры.Кодтура = Сезоны.Кодтура where Сезоны.Кодтура = @CodeTour | declare @ChisloPutevok int exec proc_po5 '1', @ChisloPutevok output select @ChisloPutevok AS 'Число путевок, проданных в этом туре' |
| Описание | ||
| Подсчет количества путевок, проданных по заданному туру | ||
| Результат запуска | ||
 | ||
drop proc proc1
Здесь proc1 - название процедуры (см. табл. 5.1).
Создание хранимых процедур в SQL Server Enterprise Manager
Программа SQL Server Enterprise Manager предоставляет графический интерфейс для работы с хранимыми процедурами, равно как и для других объектов базы данных. Для просмотра определенной процедуры базы данных BDTur_firm2 переходим на соответствующий узел, щелкаем правой кнопкой (или дважды левой) и в появившемся меню выбираем пункт "Свойства" (рис. 5.10, А). В появившемся окне "Stored Procedure Properties" выводится SQL-конструкция, для проверки синтаксиса которой нажимаем на "Check Syntax" (рис. 5.10, Б и В).
увеличить изображение
Рис. 5.10. Узел "Stored Procedures" в SQL Server Enterprise Manager. А - список хранимых процедур. Б - свойства выбранной процедуры. В - проверка синтаксиса
Для создания новой процедуры выбираем пункт меню "New Stored Procedure", появляется окно "Stored Procedure Properties" где можно вводить SQL-конструкцию.
Для быстрой разработки удобно применять мастер. На панели инструментов нажимаем на кнопку "Run a Wizard", в появившемся окне раскрываем узел "DataBase", переходим к заголовку "Create Stored Procedure Wizard" (рис. 5.11).
Рис. 5.11. Запуск мастера
В первом шаге мастера, в окне приветствия, нажимаем кнопку "Далее". Во втором шаге выбираем базу BDTur_firm2. Затем выбираем таблицу "Туристы" и отмечаем галочками три команды - insert, update, delete (рис. 5.12) - мастер создаст сразу три хранимых процедуры для вставки, обновления и удаления записей.
Рис. 5.12. Выбор таблицы и команд модификации
В последнем шаге мастера можно отредактировать создаваемые процедуры, нажав кнопку "Edit_" (рис. 5.13, А). В окне "Edit Stored Procedure Properties" в поле "Name" задается название текущей процедуры. Нажимая на кнопку "Edit SQL_", открываем SQL-конструкцию, сгенерированную мастером (рис. 5.13, Б и В).
увеличить изображение
Рис. 5.13. Настройка хранимой процедуры. А - переход от последнего шага мастера к режиму редактирования, Б - окно "Edit Stored Procedure Properties", В - SQL-конструкция создаваемой процедуры
Оставим все названия как есть, нажимаем кнопку "Готово". В результате в списке появляется три новых объекта (рис. 5.14).
Рис. 5.14. Появившиеся в списке "Stored Procedures" объекты
Мастер сгенерировал три SQL-конструкции, для insert_Туристы_1:
CREATE PROCEDURE [insert_Туристы_1] (@Кодтуриста_1 [int], @Фамилия_2 [nvarchar](50), @Имя_3 [nvarchar](50), @Отчество_4 [nvarchar](50))
AS INSERT INTO [BDTur_firm2].[dbo].[Туристы] ( [Кодтуриста], [Фамилия], [Имя], [Отчество]) VALUES ( @Кодтуриста_1, @Фамилия_2, @Имя_3, @Отчество_4) GO
Для update_Туристы_1:
CREATE PROCEDURE [update_Туристы_1] (@Кодтуриста_1 [int], @Фамилия_2 [nvarchar], @Имя_3 [nvarchar], @Отчество_4 [nvarchar], @Кодтуриста_5 [int], @Фамилия_6 [nvarchar](50), @Имя_7 [nvarchar](50), @Отчество_8 [nvarchar](50))
AS UPDATE [BDTur_firm2].[dbo].[Туристы]
SET [Кодтуриста] = @Кодтуриста_5, [Фамилия] = @Фамилия_6, [Имя] = @Имя_7, [Отчество] = @Отчество_8
WHERE ( [Кодтуриста] = @Кодтуриста_1 AND [Фамилия] = @Фамилия_2 AND [Имя] = @Имя_3 AND [Отчество] = @Отчество_4) GO
Для delete_Туристы_1:
CREATE PROCEDURE [delete_Туристы_1] (@Кодтуриста_1 [int], @Фамилия_2 [nvarchar], @Имя_3 [nvarchar], @Отчество_4 [nvarchar])
AS DELETE [BDTur_firm2].[dbo].[Туристы]
WHERE ( [Кодтуриста] = @Кодтуриста_1 AND [Фамилия] = @Фамилия_2 AND [Имя] = @Имя_3 AND [Отчество] = @Отчество_4) GO
Мы получили три хранимые процедуры для вставки, изменения и удаления записей в таблице "Туристы". Для процедур update_Туристы_1 и delete_Туристы_1 в условии WHERE (где) мастер добавил оператор AND (и) для объединения параметров запроса. Изменим его на оператор OR (или) для получения более гибкого запроса. В окне SQL Server Enterprise Manager дважды щелкаем на процедуре update_Туристы_1 и в появившемся окне свойств изменяем SQL-конструкцию:
... WHERE ( [Кодтуриста] = @Кодтуриста_1 OR [Фамилия] = @Фамилия_2 OR [Имя] = @Имя_3 OR [Отчество] = @Отчество_4) GO
Точно так же этот фрагмент будет выглядеть и для delete_Туристы_1. Прежде чем мы начнем проверять работу созданных процедур, сделаем поле "Код туриста" в таблице "Туристы" ключевым. Открываем таблицу в режиме дизайна, выделяем это поле, щелкаем правой кнопкой и выбираем пункт меню "Set Primary Key". Теперь в SQL Query Analyzer запускаем процедуру insert_Туристы_1 с параметрами, передающими значения полей новой записи:
exec insert_Туристы_1 @Кодтуриста_1 = 6, @Фамилия_2 = 'Смирнов', @Имя_3 = 'Валерий', @Отчество_4 = 'Константинович'
Появляется сообщение - одна запись добавлена4):
(1 row(s) affected)
Попытаемся добавить еще раз эту же запись - нажимаем F5. Поскольку мы задали ключевое поле, не допускающее дублирование значений, появляется сообщение об ошибке:
Server: Msg 2627, Level 14, State 1, Procedure insert_Туристы_1, Line 7 Violation of PRIMARY KEY constraint 'PK_Туристы'. Cannot insert duplicate key in object 'Туристы'. The statement has been terminated.
Изменим значение параметра "@Кодтуриста":
exec insert_Туристы_1 @Кодтуриста_1 = 7, @Фамилия_2 = 'Смирнов', @Имя_3 = 'Валерий', @Отчество_4 = 'Константинович'
Еще одна запись будет добавлена:
(1 row(s) affected)
Выведем все записи:
select * from Туристы
В таблице появились две новые записи (рис. 5.15):
Рис. 5.15. Таблица "Туристы". Добавление записей
Теперь изменим в последней записи фамилию "Смирнов" на "Тихонов". Для этого запускаем процедуру update_Туристы_1 следующим образом:
exec update_Туристы_1 @Кодтуриста_1 = 7, @Фамилия_2 = 'Смирнов', @Имя_3 = 'Валерий', @Отчество_4 = 'Константинович', @Кодтуриста_5 = 7, @Фамилия_6 = 'Тихонов', @Имя_7 = 'Валерий', @Отчество_8 = 'Константинович'
Снова появляется сообщение о изменении записи:
(1 row(s) affected)
Здесь первые четыре параметра задают текущие значения, а следующие четыре указывают новые.
В результате получаем следующие записи в таблице (рис. 5.16):
Рис. 5.16. Таблица "Туристы". Изменение записей
Для удаления записей, поскольку мы задали оператор OR, можно вызвать процедуру delete_Туристы_1, передавая параметры следующим образом:
exec delete_Туристы_1 @Кодтуриста_1 = 7, @Фамилия_2 = 'Тихонов', @Имя_3 = 'Валерий', @Отчество_4 = 'Константинович'
(1 row(s) affected)
exec delete_Туристы_1 @Кодтуриста_1 = 6, @Фамилия_2 ='', @Имя_3='', @Отчество_4='' (1 row(s) affected)
Мы получаем прежнее число записей (рис. 5.17):
Рис. 5.17. Таблица "Туристы". Удаление записей
В программном обеспечении к курсу вы найдете файлы5) BDTur_ firm2.mdf, BDTur_firm2.ldf, а также исходный файл Microsoft Access BDTur_firm2.mdb (Code\Glava3\).
Создание хранимых процедур в Visual Studio .NET
Среда Visual Studio .NET предоставляет интерфейс для создания хранимых процедур в базе данных при наличии подключения к ней. Это удобно - если вы работаете с базой данных по сети, встроенные средства администрирования Microsoft SQL Server могут оказаться недоступными. Запускаем Visual Studio (нам даже не нужно создавать какой-либо проект), переходим на вкладку "Server Explorer", раскрываем подключение к базе данных BDTur_firm2, затем на узле "Stored Procedures" щелкаем правой кнопкой и выбираем пункт "New Stored Procedure" (рис. 5.29):
Рис. 5.29. Создание новой процедуры в окне "Server Explorer"
Появляется шаблон структуры, сгенерированный мастером:
CREATE PROCEDURE dbo.StoredProcedure1 /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ RETURN
Для того чтобы приступить к редактированию, достаточно убрать знаки комментариев "/*". Команда NOCOUNT со значением ON отключает выдачу сообщений о количестве строк таблицы, получающейся в качестве запроса. Дело в том, что при использовании более чем одного оператора (SELECT, INSERT, UPDATE или DELETE) в начале запроса надо поставить команду "SET NOCOUNT ON", а перед последним оператором SELECT - "SET NOCOUNT OFF". С другими частями шаблона мы уже сталкивались. Например, хранимую процедуру proc_po1 (см. таблицу 5.3) можно переписать так:
CREATE PROCEDURE dbo.proc_vs1
( @TouristID int, @LastName nvarchar(60) OUTPUT )
AS SET NOCOUNT ON SELECT @LastName = Фамилия FROM Туристы WHERE Кодтуриста = @TouristID
RETURN
После завершения редактирования SQL-конструкция будет обведена синей рамкой. Щелкнув правой кнопкой в этой области и выбрав пункт меню "Design SQL Block", можно перейти к построителю выражения ("Query Builder") (рис. 5.30, А, Б). При выборе в этом же меню пункта "Run Stored Procedure" появляется одноименное окно, где отслеживаются передаваемые параметры (рис. 5.30, В).
увеличить изображение
Рис. 5.30. Редактирование хранимой процедуры в Visual Studio .NET. А - контекстное меню, Б - построитель выражений ( режим "Design SQL Block"), В - окно "Run stored procedure", Г - окно "Output".
В данном случае необходимо указывать значение параметров (см. таблицу 5.3), поэтому после нажатия кнопки "ОК" в окне "Run stored procedure" процедура выполнена не будет, в окне "Output" появляется следующее сообщение (рис. 5.30, Г).
Running dbo."proc_vs1" ( @TouristID = <DEFAULT>, @LastName = <DEFAULT> ).
Procedure 'proc_vs1' expects parameter '@TouristID', which was not supplied.
Для сохранения процедуры в базе данных выбираем "File \ Save proc_vs1" (или нажимаем Ctrl+S), теперь можно закрывать студию - хранимая процедура создана. Впрочем, для продолжения работы выбираем пункт "Refresh" контекстного меню в окне "Server Explorer". Происходит синхронизация с базой данных, и процедура "proc_vs1" появляется в списке. Двойной щелчок открывает ее для редактирования, причем заголовок имеет следующий вид:
ALTER PROCEDURE dbo.proc_vs1
Оператор ALTER позволяет производить действия (редактирование) с уже имеющимся объектом базы данных.
|
|
|
Вводные сведения об этой программе см. в первой лекции.
2)
См. первую лекцию.
3)
Названия операторов принято писать прописными буквами, вот так: CREATE PROCEDURE. Однако если вам неудобно постоянно переключать регистр, вы можете писать операторы строчными буквами: create procedure. Это не совсем строго, и, возможно, далее придется отказаться от этой привычки, но на первых порах это экономит много времени - SQL Query Analyzer понимает любой регистр и сохраняет процедуру в нужном формате.
4)
Affected - перев. с англ., здесь - "изменена".
5)
После присоединения базы к Microsoft SQL Server все созданные хранимые процедуры будут находиться в узле "Stored Procedures".
6)
Далее мы будем работать с этой базой данных. Вполне возможно, что у вас ее нет - вы начали читать с этого места книгу, не выполняли упражнения или потеряли диск. В этом случае вам нужно будет сделать следующее: а) Прочитать первую лекцию, создать по описаниям базу данных BDTur_firm.mdb в Microsoft Access. б) Как описывается в начале уже этой, пятой лекции, изменить названия таблиц и полей базы. в) Преобразовать файл BDTur_firm.mdb в формат Microsoft SQL Server 2000, заодно присоединив его к своему локальному серверу.
7)
Кроме удаления самого объекта DataSet с панели компонент формы, потребуется также удаление его схемы. Переходим в окно "Server Explorer" и удаляем файл схемы, имеющий расширение XSD. Например, dataSet1.xsd.
8)
Здесь я привожу названия операторов прописными буквами. Построитель выражений генерирует запросы именно в этом регистре.
9)
Если наличие большого количества полей и свойств кажется запутанным - лучше отложить Visual Studio .NET, запустить Access и как следует разобраться с созданием запросов. Достаточно одного учебника или даже справочной системы, чтобы научиться создавать запросы среднего уровня сложности.
Вызов простых хранимых процедур при помощи объекта DataAdapter
Мы разобрались с созданием и запуском хранимых процедур, приступим теперь к их использованию в Windows-приложениях, связанных с базами данных. Создайте новый Windows-проект и назовите его "VisualDataAdapterSP". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". В окне Toolbox переходим на вкладку Data и перетаскиваем на форму элемент SqlDataAdapter. В появившемся мастере в поле имени сервера вводим ".", выбираем тип входа "учетные сведения Windows NT", а из выпадающего списка баз данных выбираем6) "BDTur_firm2" (рис. 5.18):
Рис. 5.18. Подключение к базе данных BDTur_firm2
Проверив подключение, закрываем окно "Свойства связи с данными". В шаге "Choose a Query Type" мастера выбираем пункт "Use existing stored procedures" (рис. 5.19):
Рис. 5.19. Шаг "Choose a Query Type" мастера настройки объекта DataAdapter
Далее выбираем процедуру "proc1" - как вы помните, она извлекала все записи из таблицы "Туристы". Выводимые поля отображаются в окне "Set Select procedure parameters" (рис. 5.20):
Рис. 5.20. Выбор хранимой процедуры
Нажимаем кнопку "Next", а в следующем, заключительном шаге - "Finish". Просмотрим данные, которые будут извлечены объектом DataAdapter. Выделяем sqlDataAdapter1, переходим в окно Properties и щелкаем по ссылке "Preview Data_". В появившемся окне "Data Adapter Preview" нажимаем кнопку Fill для просмотра данных (рис. 5.21).
увеличить изображение
Рис. 5.21. Просмотр данных, извлекаемых объектом DataAdapter
Закрываем окно "Data Adapter Preview", снова выделяем объект sqlDataAdapter1, в его окне Properties нажимаем на ссылку "Generate Dataset_" (см. рис. 5.21). В появившемся окне "Generate Dataset" предлагается создать новый объект "DataSet1".
Нажимаем кнопку "OK". Выделяем элемент DataGrid, из выпадающего списка свойства "DataSource" выбираем "dataSet11.proc1" (рис. 5.22).
Рис. 5.22. Свойство DataSource элемента DataGrid
Вид формы изменился - на нем появились названия полей. В конструкторе формы вызываем метод Fill объекта DataAdapter для заполнения DataSet:
public Form1() { InitializeComponent(); sqlDataAdapter1.Fill(dataSet11); }
Запускаем приложение. На форму выводятся данные, полученные в результате выполнения хранимой процедуры proc1 (рис. 5.23).
Рис. 5.23. Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc1"
Изменим настройку объекта DataAdapter. Выделяем sqlDataAdapter1, в окне Properties щелкаем по ссылке "Configure DataAdapter_" (см. рис. 5.21). Появляется уже знакомый мастер "Data Adapter Configuration Wizard", нажимаем кнопку "Next". В шаге "Choose Your Data Connection" оставляем имеющееся подключение - мы будем работать с той же самой базой данных. В шаге "Binds Commands to Existing Stored Procedures" на этот раз выбираем процедуру proc9 - она извлекала данные из таблиц "Туристы" и "Информацияотуристах" (см. таблицу 5.1). Завершаем работу мастера. Изменим свойство DataSource объекта DataGrid - установим теперь значение "dataSet11" (рис. 5.24):
Рис. 5.24. Изменение свойства DataSource объекта DataGrid
Запускаем приложение. Теперь мы видим две ссылки - "proc1" и "proc9". Переходя по последней, мы видим данные хранимой процедуры (рис. 5.25, Б).
увеличить изображение
Рис. 5.25. Приложение "VisualDataAdapterSP". А - данные хранимой процедуры "proc1". Б - данные хранимой процедуры "proc9"
Если мы перейдем по ссылке "proc1", мы обнаружим, что данных в ней нет, однако названия полей сохранились (рис. 5.25, А).
Дело в том, что в структуре объекта DataSet остался "след" первой хранимой процедуры. В восьмой лекции мы научимся работать со структурой DataSet, а пока, если нам не нужна такая пустая ссылка "proc1", можно удалить объект DataSet7), а затем сгенерировать его заново по ссылке объекта DataAdapter окна Properties.
Создадим теперь хранимую процедуру при помощи мастера настройки объекта DataAdapter. Выделяем sqlDataAdapter1 и в окне Properties снова нажимаем на ссылку " Configure DataAdapter_". В шаге "Choose a Query Type" (см. рис. 5.19) выбираем "Create new stored procedures". В следующем шаге "Generate the stored procedures" нажимаем кнопку "Query Builder" (Построитель запроса). Добавляем таблицу "Туристы". Создадим еще раз запрос, выводящий всех туристов, фамилия которых содержит букву "и" (см. табл. 5.1, процедура proc8). Ставим галочку в поле *(All Columns), затем просто вводим условие отбора WHERE8):
SELECT * FROM Туристы WHERE (Фамилия LIKE '%и%')
Обратите внимание на небольшое отличие синтаксиса - здесь условие находится в круглых скобках. Внешний вид построителя выражения также изменился: в таблице "Туристы" появился значок фильтра, в поле "Column" - заголовок "Фамилия", в поле "Criteria" (Условие) - выражение "LIKE '%и%'". Щелкнув правой кнопкой в любой части построителя, выбираем пункт меню "Run" - в нижней таблице появляются данные, извлеченные запросом (рис. 5.26):
Рис. 5.26. Создание запроса в Query Builder
Работа с Query Builder очень похожа на создание запросов в режиме конструктора в Microsoft Access. Читатель, с этим знакомый, без труда разберется во всех полях и свойствах построителя выражения9). Завершив настройку, закрываем построитель, нажимая кнопку "ОК". Нажимаем кнопку "Next", в шаге "Create the Stored Procedures" задаем название созданной процедуре - "proc_da1" (см.
рис. 5.27).
увеличить изображение
Рис. 5.27. Окно "Preview SQL Script" и шаг мастера "Create the Stored Procedures"
По умолчанию мастер также генерирует процедуры типа insert, update и delete. В построители выражения мы создали саму SQL-конструкцию, без указания команд создания хранимой процедуры. Нажав кнопку "Preview SQL Script_", можно просмотреть команды, которые были сгенерированы автоматически. В окне "Create the Stored Procedures" также по умолчанию отмечено автоматическое создание хранимых процедур в самой базе данных. Завершаем работу, нажимая кнопку "Finish". Запускаем приложение - на форму выводятся данные хранимой процедуры proc_da1 (рис. 5.28).
Рис. 5.28. Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc_da1"
В программном обеспечении к курсу вы найдете приложение VisualData AdapterSP (Code\Glava3\ VisualDataAdapterSP).
Методы ExecuteNonQuery, ExecuteScalar и ExecuteReader объекта Command
Мы рассмотрели всю возможную функциональность, которую можно получить без использования каких-либо методов объекта Command. Для выполнения запросов на выборку простейших процедур достаточно просто указать тип и передать название запроса или процедуры. Все работает, но этого явно недостаточно для серьезных приложений. Поэтому забудем пока про хранимые процедуры, другие объекты ADO .NET, и сосредоточим все внимание на методах объекта Command.
Метод ExecuteNonQuery применяется для выполнения запросов, не возвращающих данные. Как же запросы, предназначенные именно для извлечения данных, могут не возвращать их? Речь идет о запросах типа UPDATE, INSERT и DELETE - в самом деле, они вносят изменения в таблицу базы данных, не возвращая ничего назад в результате выполнения. В самом языке SQL эти запросы образуют категорию DML (Data Manipulation Language, DML). Дословный перевод названия категории - "язык манипулирования данными", наличие слова "язык" вносит небольшую путаницу: в языке SQL есть язык DML. Но это именно так. После небольшого опыта подобные названия станут привычными.
Создайте новое консольное приложение и назовите его "Example ExecuteNonQuery". Привожу его полный листинг:
using System; using System.Data.SqlClient;
namespace ExampleExecuteNonQuery { class Class1 { [STAThread] static void Main(string[] args) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI; data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "UPDATE Туристы SET Фамилия = 'Сергеева' WHERE Кодтуриста = 3"; myCommand.ExecuteNonQuery(); conn.Close(); } } }
Объект Command можно создавать, вызывая метод CreateCommand объекта Connection:
SqlCommand myCommand = conn.CreateCommand();
Ранее мы пользовались следующим способом:
SqlCommand myCommand = new SqlCommand(); myCommand.Connection = conn;
Эти два способа совершенно эквивалентны. В свойстве CommandText указывается непосредственно текст запроса, который устанавливает значение "Сергеева" поля "Фамилия" для записи с полем "Кодтуриста" = 3. Для выполнения запроса просто вызываем метод ExecuteNonQuery:
myCommand.ExecuteNonQuery();
Запускаем приложение, нажимая Ctrl+F5. При успешном выполнении запроса в консольном окне появляется надпись "Press any key to continue" (рис. 6.10):
Рис. 6.10. Выполнение приложения ExampleExecuteNonQuery
Запускаем SQL Server Enterprise Manager, открываем таблицу "Туристы" и убеждаемся в том, что запись изменилась (рис. 6.11).
Рис. 6.11. Таблица "Туристы", изменение записи
Метод ExecuteNonQuery все-таки что-то неявно возвращает - результат выполнения запроса в виде количества измененных записей; это может применяться для проверки (изменим фрагмент кода):
int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { Console.WriteLine ("Изменения внесены"); } else { Console.WriteLine("Не удалось внести изменения"); }
Теперь, в зависимости от результата, будет появляться соответствующее сообщение (рис. 6.12).
Рис. 6.12. Результаты проверки выполнения запроса
Неудачный результат можно получить, установив в тексте запроса значения поля "Код туриста", равное, скажем, 10.
Закомментируем имеющееся свойство CommandText и добавим новое:
myCommand.CommandText = "INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович')";
Запускаем приложение, переходим в SQL Server Enterprise Manager - запрос добавил новую запись (рис. 6.13):
Рис. 6.13. Таблица "Туристы", добавление записи
Снова закомментируем свойство CommandText, добавим теперь запрос на удаление записи:
myCommand.CommandText = "DELETE FROM Туристы WHERE Кодтуриста = 4";
Запускаем приложение - из таблицы удалена четвертая запись (рис. 6.14):
Рис. 6.14. Таблица "Туристы", удаление записи
Метод ExecuteNonQuery применяется также для выполнения запросов, относящихся к категории DDL языка SQL. Язык определения данных3) (Data Definition Language, DDL) позволяет создавать и изменять структуру объектов базы данных, например, создавать и удалять таблицы. Основными операторами этого языка являются CREATE, ALTER, DROP. В результате выполнения запросов DDL не возвращаются данные - именно поэтому мы можем применять метод ExecuteNonQuery. Закомментируем имеющееся свойство CommandText и напишем новое, создающее в базе "BDTur_firm2" новую таблицу "Отзывы":
myCommand.CommandText = "CREATE TABLE Отзывы (Кодотзыва INT NOT NULL, Кодтуриста INT NOT NULL, Комментарий VARCHAR(50)";
Запускаем приложение, затем переходим в SQL Server Enterprise Manager, нажимаем кнопку
Рис. 6.15. База данных BDTur_firm2, новая таблица "Отзывы"
Для добавления нового столбца "Отзывтуриста" строка Command Text должна иметь следующий вид:
myCommand.CommandText = "ALTER TABLE Отзывы ADD Отзывтуриста VARCHAR(50)";
В SQL Server Enterprise Manager дважды щелкаем по названию таблицы - в появившемся окне "Table Properties" видим новое поле "Отзывтуриста" (рис. 6.16):
Рис. 6.16. Свойства таблицы "Отзывы"
Для удаления таблицы "Отзывы" запускаем приложение, содержащее следующую строку CommandText:
myCommand.CommandText = "DROP TABLE Отзывы";
Переходим в SQL Server Enterprise Manager - таблица полностью исчезла из базы данных. Если бы нам нужно было лишь удалить данные из таблицы, сохранив структуру, мы бы воспользовались следующей командой:
myCommand.CommandText = "DELETE FROM Отзывы";
Объектами базы данных могут быть не только таблицы, но и хранимые процедуры, схемы, представления.
В любом случае манипуляция с ними будет относиться к категории DDL.
Метод ExecuteNonQuery применяется для выполнения запросов, относящихся к категории DCL. Язык управления данными (Data Control Language, DCL) предназначен для управления доступом (определения полномочий) к объектам базы данных. Основными операторами этого языка являются GRANT, DENY, REVOKE. Мы не будем рассматривать выполнение этих запросов - использование в данном случае объекта Commnad не отличается ничем от рассмотренного выше.
В программном обеспечении к курсу вы найдете приложение Example ExecuteNonQuery (Code\Glava3\ ExampleExecuteNonQuery).
Метод ExecuteScalar объекта Command применяется для запросов, возвращающих одно значение. Мы сталкивались с такими запросами, когда использовали агрегатные функции COUNT, MIN, MAX. Создайте новое консольное приложение и назовите его "ExampleExecuteScalar". Полный листинг этого приложения:
using System; using System.Data.SqlClient;
namespace ExampleExecuteScalar { class Class1 { [STAThread] static void Main(string[] args) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); // SqlCommand myCommand = new SqlCommand(); // myCommand.Connection = conn; myCommand.CommandText = "SELECT COUNT (*) FROM Туры"; string KolichestvoTurov = Convert.ToString(myCommand.ExecuteScalar()); conn.Close(); Console.WriteLine("Количество туров: " + KolichestvoTurov); } } }
Возвращаемый методом ExecuteScalar результат мы привели к типу string для вывода в окно консоли. Запускаем приложение - как и следовало ожидать, запрос вернул число 5 (рис. 6.17):
Рис. 6.17. Приложение ExampleExecuteScalar, вывод количества туров
Можно несколько раз применять этот метод:
... myCommand.CommandText = "SELECT COUNT (*) FROM Туры"; string KolichestvoTurov = Convert.ToString(myCommand.ExecuteScalar()); myCommand.CommandText = "SELECT MAX (Цена) FROM Туры"; string MaxPrice = Convert.ToString(myCommand.ExecuteScalar()); myCommand.CommandText = "SELECT MIN (Цена) FROM Туры"; string MinPrice = Convert.ToString(myCommand.ExecuteScalar()); myCommand.CommandText = "SELECT AVG (Цена) FROM Туры"; string AvgPrice = Convert.ToString(myCommand.ExecuteScalar()); conn.Close(); Console.WriteLine("Количество туров: " + KolichestvoTurov + "\nСамый дорогой тур, цена в руб. : " + MaxPrice + "\nСамый дешевый тур, цена в руб.: " + MinPrice + "\nСредняя цена туров: " + AvgPrice); }
Запускаем приложение4) - получаем несколько значений из базы данных (рис. 6.18):
Рис. 6.18. Приложение ExampleExecuteScalar, вывод нескольких значений
Когда требуется получать подобные одиночные значения, всегда следует применять метод ExecuteScalar - это позволяет значительно повысить производительность.
В программном обеспечении к курсу вы найдете приложение Example ExecuteScalar (Code\Glava3\ ExampleExecuteScalar).
Мы переходим к рассмотрению очень важного метода - ExecuteReader. Он применяется для получения набора записей из базы данных. Особенностью этого метода является то, что он возвращает специальный объект DataReader, с помощью которого просматриваются записи. Для хранения данных, полученных из базы, мы до этого использовали объект DataSet. Объект DataReader, в отличие от DataSet, требует наличия постоянного подключения для извлечения и просмотра данных, кроме того, он открывает данные только для чтения. Создайте новое консольное приложение и назовите его "ExampleExecuteReader". Полный листинг этого приложения:
using System; using System.Data.SqlClient;
namespace ExampleExecuteReader { class Class1 { [STAThread] static void Main(string[] args) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; SqlCommand myCommand = conn.CreateCommand(); //SqlCommand myCommand = new SqlCommand(); //myCommand.Connection = conn; myCommand.CommandText = "SELECT * FROM Туристы"; conn.Open(); SqlDataReader dataReader = myCommand.ExecuteReader();
while (dataReader.Read()) { Console.WriteLine(dataReader["Фамилия"]); } dataReader.Close(); conn.Close(); } } }
Объект dataReader создается в результате вызова метода Execute Reader объекта myCommand:
SqlDataReader dataReader = myCommand.ExecuteReader();
Перед считыванием первой записи вызываем метод Read объекта dataReader и выводим набор записей в консольное окно.
Запускаем приложение5) (рис. 6.19).
Рис. 6.19. Приложение "ExampleExecuteReader". Вывод поля "Фамилия"
Объект DataReader возвращает набор данных типа object, причем для обращения можно использовать индекс:
Console.WriteLine(dataReader[1]);
Это совершенно эквивалентная строка вывода6).
Перечислим несколько полей:
... Console.WriteLine(dataReader[0]); Console.WriteLine(dataReader[1]); Console.WriteLine(dataReader[2]); Console.WriteLine(dataReader[3]); ...
При выводе они будут располагаться в структурированном виде (рис. 6.20):
Рис. 6.20. Приложение ExampleExecuteReader, вывод содержимого всех полей
Поскольку мы имеем дело с объектами (тип данных object), для вывода записей в виде строк неприменимо их простое объединение:
... Console.WriteLine(dataReader[0] + dataReader[1] + dataReader[2] + dataReader[3]); ...
Преобразованные к типу string значения можно объединять:
Console.WriteLine(Convert.ToString(dataReader[0]) + " "+ Convert.ToString(dataReader[1]) + " "+Convert.ToString(dataReader[2]) + " "+ Convert.ToString(dataReader[3]));
Теперь записи выводятся в более привычном виде (рис. 6.21):
Рис. 6.21. Приложение ExampleExecuteReader. Вывод содержимого всех полей в виде записей
В программном обеспечении к курсу вы найдете приложение "Example ExecuteReader" (Code\Glava3\ ExampleExecuteReader).
Объект Command. Свойства CommandType и CommandText
При определении объектов ADO .NET мы назвали DataAdapter адаптером, преобразователем, предназначенным для взаимодействия с базой данных. Это действительно так, однако если рассматривать взаимодействие с базой данных более глубоко, то выясняется, что в ADO .NET есть специализированный объект для выполнения запросов, называемый Command. Под запросами понимается выполнение SQL-конструкций или запуск хранимых процедур. С объектом Command мы познакомились еще во второй лекции, но у вас могло сложиться впечатление, что его введение достаточно формально - вполне можно обойтись одним объектом DataAdapter. Например, в приложении VisualDataAdapterSP мы создавали и выполняли хранимые процедуры, настраивая DataAdapter без всякого объекта Command! В действительности, среда создала его неявным образом - открываем метод InitializeComponent и видим объявление sqlSelectCommand1, а также описание нескольких его свойств:
private void InitializeComponent() { ... this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand(); ... // // sqlSelectCommand1 // this.sqlSelectCommand1.CommandText = "[proc_da1]"; this.sqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure; this.sqlSelectCommand1.Connection = this.sqlConnection1; this.sqlSelectCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter ("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null)); ...
Нам нужно научиться понимать это описание и создавать его программным образом. Среда Visual Studio .NET содержит инструменты для визуальной настройки объекта Command, и поскольку разобраться в них проще, чем в коде, начнем с них. Создайте новое Windows-приложение и назовите его "VisualCommand". Добавляем элемент управления DataGrid, его свойству Dock устанавливаем значение Fill. В окне ToolBox переходим на вкладку Data, перетаскиваем на форму объект SqlCommand (рис. 6.1):
Рис. 6.1. Окно Toolbox, вкладка Data
Переходим в окно Properties появившегося объекта sqlCommand1, в свойстве "Connection" из выпадающего списка выбираем создание нового подключения (New) (рис. 6.2).
Рис. 6.2. Свойство "Connection" объекта sqlCommand1
В окне "Свойства связи с данными" настраиваем подключение к базе данных BDTur_firm2 (см. рис. 5.18). На панели компонент появляется объект sqlConnection1 - если бы мы вначале создали и настроили подключение, то в свойстве Connection объекта sqlCommand1 можно было выбрать "Existing/sqlConnection1". Свойство CommandType указывает на тип команды (запроса), который будет направляться к базе данных (рис. 6.3):
Рис. 6.3. Свойство CommandType объекта sqlCommand1
Возможны следующие значения:
Text. Текстовая команда состоит из SQL-конструкции, направляемой к базе данных. Это значение используется по умолчанию.StoredProcedure. Текстовая команда состоит из названия хранимой процедуры.TableDirect. Текстовая команда состоит из названия таблицы базы данных. В результате извлекается все содержимое таблицы. Эта команда аналогична текстовой команде SELECT * FROM Название_таблицы. Команда TableDirect поддерживается только управляемым поставщиком OLE DB.
Оставляем пока значение по умолчанию. В поле свойства CommandText нажимаем на кнопку
Рис. 6.4. Свойство CommandText объекта sqlCommand1
В появившемся построителе выражений настраиваем извлечение содержимого таблицы "Туры". Обратите внимание на то, что в поле свойства CommandText появилась SQL-конструкция:
SELECT Туры.* FROM Туры
Снова в окне Toolbox переходим на вкладку Data, перетаскиваем на форму объект SqlDataAdapter. В мастере "Data Adapter Configuration Wizard" нажимаем кнопку "Cancel" - у нас уже есть подключение и настроенный объект sqlCommand1, достаточно указать в свойстве Select Command объект sqlCommand1 (рис. 6.5):
Рис. 6.5. Свойство SelectCommand объекта sqlDataAdapter1
Выделяем sqlDataAdapter1, создаем DataSet, нажимая на ссылку "Generate Dataset". В окне "Generate Dataset" оставляем название "DataSet1", нажимаем "OK". В выпадающем списке свойства DataSource элемента управления DataGrid выбираем "datSet11" в качестве источника данных (рис. 6.6):
Рис. 6.6. Свойство DataSource элемента dataGrid1
Наконец в конструкторе формы вызываем метод Fill объекта DataAdapter:
public Form1() { InitializeComponent(); sqlDataAdapter1.Fill(dataSet11); }
Запускаем приложение. На форму выводится содержимое таблицы "Туры" (рис. 6.7):
Рис. 6.7. Приложение VisualCommand, вывод таблицы "Туры"
Установим теперь в свойстве CommandType объекта Command значение "StoredProcedure", а в свойстве CommandText введем название имеющейся хранимой процедуры - "[proc10]" (рис. 6.8):
Рис. 6.8. Настройка свойств объекта sqlCommand1 для вывода процедуры [proc10]
Чтобы избежать проблем со структурой DataSet, удалим имеющийся объект с панели компонент. Дополнительно в окне Solution Explorer удаляем файл DataSet1.xsd. Снова генерируем DataSet и определяем его в качестве источника данных для элемента DataGrid. Запускаем приложение - на форму выводится результат выполнения хранимой процедуры (рис. 6.9):
увеличить изображение
Рис. 6.9. Приложение VisualCommand, извлечение хранимой процедуры proc10
Таким образом можно извлекать данные из таблиц при помощи SQL-конструкций и запускать хранимые процедуры таблицы 5.1. Поставщик данных SQL Server не поддерживает значение TableDirect свойства CommandType - при его выборе появляется диагностическое сообщение об ошибке. Это значение можно применять при подключении к базе данных Microsoft Access.
В программном обеспечении к курсу вы найдете приложение Visual Command (Code\Glava3\ VisualCommand).
Перейдем теперь к реализации аналогичного приложения без применения визуальных средств студии для объектов ADO .NET.
Создайте новое Windows-приложение и назовите его "ProgrammCommand". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". В классе формы создаем строки connectionString и commandText:
string connectionString = "integrated security=SSPI; data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; string commandText = "SELECT * FROM Туры";
Конструктор формы будет иметь следующий вид:
public Form1() { InitializeComponent(); SqlConnection conn = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = conn; myCommand.CommandText = commandText; SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); conn.Open(); dataAdapter.Fill(ds, "Туры"); conn.Close(); dataGrid1.DataSource = ds; }
Запускаем приложение. На форму выводится содержимое таблицы "Туры" (см. рис. 6.7). Здесь мы используем всего один объект DataAdapter, который сам открывает и закрывает соединение для получения данных. Поэтому можно не вызывать методы Open и Close объекта conn. Конструктор объекта SqlCommand может принимать значения, указанные в таблице 6.1.
| 1 |  | |
| Пример 2) | Описание | |
public Form1() { InitializeComponent(); SqlConnection conn = new SqlConnection(connectionString); SqlTransaction trans = conn.BeginTransaction(); SqlCommand myCommand = new SqlCommand(commandText, conn, trans); //SqlCommand myCommand = // new SqlCommand("SELECT * FROM Туры", conn, trans); SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Туры"); dataGrid1.DataSource = ds; } | Первый параметр - строка commandText, причем можно сразу задавать SQL-конструкцию без создания строки в классе. Затем экземпляры объектов SqlConnection и SqlTransaction. Подробно о транзакциях см. далее | |
| 2 |  | |
| Пример | Описание | |
| public Form1() { InitializeComponent(); SqlConnection conn = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(commandText, conn); //SqlCommand myCommand = // new SqlCommand("SELECT * FROM Туры", conn); SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Туры"); dataGrid1.DataSource = ds; } | Вариант без экземпляра объекта SqlTransaction | |
| 3 |  | |
| Пример | Описание | |
| public Form1() { SqlConnection conn = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(commandText); //SqlCommand myCommand = // new SqlCommand("SELECT * FROM Туры"); myCommand.Connection = conn; SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Туры"); dataGrid1.DataSource = ds; } | Конструктор принимает в качестве параметра только строку commandText. Для указания соединений используем свойство Connection созданного экзмепляра myCommand | |
| 4 |  увеличить изображение | |
| Пример | Описание | |
| public Form1() { SqlConnection conn = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = conn; myCommand.CommandText = commandText; //Command.CommandText = "SELECT * FROM Туры"; SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Туры"); dataGrid1.DataSource = ds; } | Конструктор не принимает параметров. Для указания соединения и строки commandText вызываем соответствующие свойства созданного экземпляра myCommand. Строке commandText можно передать SQL-конструкцию непосредственно - в этом случае создавать переменную в классе не нужно | |
Первый вариант конструктора обеспечивает самую компактную форму записи. Его можно использовать при работе с одним соединением с базой данных. В случае работы с несколькими соединениями удобнее определить значения командных строк в классе формы (или вообще в отдельном классе) и использовать четвертый вариант. При изменении параметров подключения нужно будет исправить только сами строки, не затрагивая код, относящийся к объекту Command.
Для вывода хранимой процедуры следует указать значение "Stored Procedure" свойства CommandType:
public Form1() { SqlConnection conn = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = conn; myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc10]"; SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet ds = new DataSet(); dataAdapter.Fill(ds, "Туры"); dataGrid1.DataSource = ds; }
Название процедуры можно также присвоить переменной commandText, созданной в классе формы.
В программном обеспечении к курсу вы найдете приложение Programm Command (Code\Glava3\ ProgrammCommand).
Применение методов объекта Command в Windows-приложениях. Параметризированные запросы
Мы рассмотрели основные методы объекта Command в консольных приложениях. Это дало нам возможность понять синтаксис самих методов, без привязки к какому-либо интерфейсу. Однако, после того как синтаксис стал ясен, возникает вопрос - как же использовать эти методы в реальных приложениях? Понятно, что простое копирование кода в конструктор формы, по сути, не изменит пример. Следовательно, мы должны привязывать вводимые значения, например, к текстовым полям. Но это означает, что параметры строки запроса будут неизвестны до тех пор, пока пользователь не введет соответствующие значения. Например, для метода ExecuteNonQuery строка commandText имела следующий вид:
myCommand.CommandText = "UPDATE Туристы SET Фамилия = 'Сергеева' WHERE Кодтуриста = 3";
Если мы создадим приложение, где пользователь будет вводить фамилию и код туриста, то мы не можем заранее указать, какие это будут значения. Логически запрос можно представить примерно так:
myCommand.CommandText = "UPDATE Туристы SET Фамилия = 'Какая-то_фамилия,_которую_введет_пользователь' WHERE Кодтуриста = Какой-то_код,_который_введет_пользователь";
Для решения таких задач, которые возникли еще в самом начале разработки языка SQL, были придуманы параметризированные запросы. В них неизвестные значения заменяются параметрами. Вот так:
myCommand.CommandText = "UPDATE Туристы SET Фамилия = @Family WHERE Кодтуриста = @TouristID";
Здесь @Family (обратите внимание, пишется без кавычек!) - параметр для неизвестного значения фамилии, @TouristID - параметр для неизвестного значения кода туриста. Теперь мы можем привязывать параметры к тексту, вводимому пользователем. Создайте новое Windows-приложение и назовите его "ExamWinExecuteNonQuery". Устанавливаем следующие свойства формы:
| FormBorderStyle | FixedSingle |
| MaximizeBox | False |
| Size | 620; 240 |
Добавляем на форму элементы управления и устанавливаем их свойства:
| Location | 16; 16 |
| Size | 296; 112 |
| Text | Пример UPDATE |
| Location | 320; 16 |
| Size | 280; 176 |
| Text | Пример INSERT |
| Location | 16; 136 |
| Size | 296; 56 |
| Text | Пример DELETE |
| Name | btnUpdate |
| Location | 80; 80 |
| Text | Обновить |
| Name | btnInsert |
| Location | 88; 144 |
| Text | Добавить |
| Name | BtnDelete |
| Location | 208; 24 |
| Text | Удалить |
| Name | TxtTouristIDUpdate |
| Location | 16; 24 |
| Size | 224; 20 |
| Text | Введите код туриста |
| Name | TxtFamilyUpdate |
| Location | 16; 56 |
| Size | 224; 20 |
| Text | Введите фамилию туриста |
| Name | TxtTouristIDInsert |
| Location | 16; 24 |
| Size | 224; 20 |
| Text | Введите код туриста |
| Name | TxtFamilyInsert |
| Location | 16; 56 |
| Size | 224; 20 |
| Text | Введите фамилию туриста |
| Name | TxtFirstNameInsert |
| Location | 16; 88 |
| Size | 224; 20 |
| Text | Введите имя туриста |
| Name | TxtMiddleNameInsert |
| Location | 16; 120 |
| Size | 224; 20 |
| Text | Введите отчество туриста |
| Name | txtTouristIDDelete |
| Location | 8; 24 |
| Size | 192; 20 |
| Text | Введите код туриста для удаления |
Форма в режиме дизайна будет иметь следующий вид (рис. 6.22):
увеличить изображение
Рис. 6.22. Приложение ExamWinExecuteNonQuery, вид формы в режиме дизайна
Подключаем пространство имен для работы с базой данных:
using System.Data.SqlClient;
В классе формы создаем экземпляр conn7):
SqlConnection conn = null;
Обработчик кнопки btnUpdate будет иметь следующий вид:
private void btnUpdate_Click(object sender, System.EventArgs e) { try { //Создаем переменную Family, в которую помещаем значение, //введенное пользователем в поле txtFamilyUpdate: string Family = Convert.ToString(this.txtFamilyUpdate.Text); //Создаем переменную TouristID, в которую помещаем значение, //введенное пользователем в поле txtTouristIDUpdate: int TouristID = int.Parse(this.txtTouristIDUpdate.Text); conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "UPDATE Туристы SET Фамилия = @Family WHERE Кодтуриста = @TouristID"; //Добавляем параметр @Family в коллекцию параметров //объекта myCommand myCommand.Parameters.Add("@Family", SqlDbType.NVarChar, 50); //Устанавливаем значение параметра @Family //равным значению переменной Family myCommand.Parameters["@Family"].Value = Family; //Добавляем параметр @TouristID в коллекцию параметров //объекта myCommand myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4); //Устанавливаем значение параметра @TouristID //равным значению переменной TouristID myCommand.Parameters["@TouristID"].Value = TouristID; int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); }
} catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
Обратите внимание - в блоке finally мы закрываем соединение, это нужно сделать в любом случае, независимо от результата выполнения команды.
Значения, введенные пользователем в текстовые поля txtFamilyUpdate и txtTouristIDUpdate, помещаются в переменные Family и TouristID. В запросе к базе данных используются два параметра - @Family и @TouristID. Мы добавляем их в коллекцию объекта Command, используя метод Add свойства Parameters, а затем устанавливаем значения параметров равными переменным Family и TouristID. Конструктор метод Add перегружен, первый вариант принимает наибольшее количество свойств8) (рис. 6.23):
Рис. 6.23. Конструктор метода Add свойства Parameters объекта Command
Описание некоторых свойств метода Add приводится в таблице 6.2.
| parameterName | Название параметра |
| sqlDbType | Тип данных передаваемого параметра |
| size | Размер параметра |
| sourceColumn | Название имени столбца объекта DataSet, на который ссылается данный параметр |
Рис. 6.24. Конструктор метода Add свойства Parameters объекта Command поставщика данных OLE DB
Добавляем обработчик кнопки btnInsert:
private void btnInsert_Click(object sender, System.EventArgs e) { try { int TouristID = int.Parse(this.txtTouristIDInsert.Text); string Family = Convert.ToString(this.txtFamilyInsert.Text); string FirstName = Convert.ToString(this.txtFirstNameInsert.Text); string MiddleName = Convert.ToString(this.txtMiddleNameInsert.Text); conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (@TouristID, @Family, @FirstName, @MiddleName)"; myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4); myCommand.Parameters["@TouristID"].Value = TouristID; myCommand.Parameters.Add("@Family", SqlDbType.NVarChar, 50); myCommand.Parameters["@Family"].Value = Family; myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50); myCommand.Parameters["@FirstName"].Value = FirstName; myCommand.Parameters.Add("@MiddleName", SqlDbType.NVarChar, 50); myCommand.Parameters["@MiddleName"].Value = MiddleName; int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); }
} catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
В запросе используются четыре параметра: @TouristID, @Family, @FirstName, @MiddleName. Тип данных создаваемых параметров соответствует типу данных полей таблицы "Туристы" в базе.
Добавляем обработчик кнопки btnDelete:
private void btnDelete_Click(object sender, System.EventArgs e) { try { int TouristID = int.Parse(this.txtTouristIDDelete.Text); conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "DELETE FROM Туристы WHERE Кодтуриста = @TouristID"; myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4); myCommand.Parameters["@TouristID"].Value = TouristID; int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); }
} catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
Запускаем приложение. В каждой из групп заполняем поля, затем нажимаем на кнопки. Проверять результат можно, запуская SQL Server Enterprise Manager и просматривая каждый раз содержимое таблицы "Туристы" (рис. 6.25):
Рис. 6.25. Готовое приложение ExamWinExecuteNonQuery. Таблицы взяты из SQL Server Enterprise Manager
В программном обеспечении к курсу вы найдете приложение Exam WinExecuteNonQuery (Code\Glava3\ ExamWinExecuteNonQuery).
Применять метод ExecuteScalar объекта Command в Windows-приложениях очень легко - достаточно указать элемент управления (текстовое поле, надпись) для вывода одиночного значения.
Рассмотрим теперь метод ExecuteReader. Одна из главных задач при использовании этого метода - разместить возвращаемый набор данных в элементе управления на форме.
Создайте новое приложение и назовите его "ExamWinExecuteReader". Перетаскиваем на форму элемент ListBox, его свойству Dock устанавливаем значение Bottom. Добавляем элемент Splitter, свойству Dock также устанавливаем значение Bottom. Наконец, перетаскиваем элемент ListView, свойству Dock устанавливаем значение Fill. Нам нужно настроить внешний вид элемента ListView: в окне Properties в поле свойства Columns нажимаем на кнопку
| chTouristID | Код туриста |
| chFamily | Фамилия |
| chFirstName | Имя |
| chMiddleName | Отчество |
увеличить изображение
Рис. 6.26. Свойство Columns элемента ListView и редактор "ColumnHeader Collection Editor"
Для отображения созданных столбцов свойству View элемента устанавливаем значение "Details". Также включим режим отображения линий сетки - в свойстве GridLines выбираем значение "True". Сделанные изменения немедленно отобразятся на элементе.
Подключаем пространство имен для работы с базой:
using System.Data.SqlClient;
В классе формы создаем объекты conn и dataReader:
SqlConnection conn = null; SqlDataReader dataReader;
В конструкторе формы добавляем код для заполнения данными элементов управления:
public Form1() { InitializeComponent(); try { conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandText = "SELECT * FROM Туристы"; dataReader = myCommand.ExecuteReader(); while (dataReader.Read()) { // Создаем переменные, получаем для них значения //из объекта dataReader, используя метод GetТипДанных int TouristID = dataReader.GetInt32(0); string Family = dataReader.GetString(1); string FirstName = dataReader.GetString(2); string MiddleName = dataReader.GetString(3); //Выводим данные в элемент listBox1 listBox1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); //Создаем экземпляр item класса ListViewItem //для записи в него данных из dataReader ListViewItem item = new ListViewItem(new string[]{Convert.ToString(dataReader[0]), Convert.ToString(dataReader[1]), Convert.ToString(dataReader[2]), Convert.ToString(dataReader[3])}); listView1.Items.Add(item); } } catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { dataReader.Close(); conn.Close(); } }
Метод "GetТипДанных" позволяет приводить значения, возвращаемые объектом DataReader, если заранее известен их тип данных. Запускаем приложение. На форму выводятся данные в виде списка в элементе ListBox и в виде таблицы в элементе ListView (рис. 6.27):
Рис. 6.27. Приложение ExamWinExecuteReader
В программном обеспечении к курсу вы найдете приложение ExamWin ExecuteReader (Code\Glava3\ ExamWinExecuteReader).
Вывод данных в элемент ListView приводит к достаточно удовлетворительному результату, однако более привычным для нас является вывод в элемент DataGrid. Раньше, при использовании объекта DataSet, мы указывали источник данных для элемента DataGrid так:
dataGrid1.DataSource = dataset.Tables["Название_таблицы"].DefaultView;
Или так:
dataGrid1.DataSource = dataset;
Объект DataReader не поддерживает аналогичного вывода - мы не можем связать объекты таким простым образом:
dataGrid1.DataSource = datareader;
Одним из способов вывода является применение дополнительных объектов DataTable. Объект DataTable предназначен для хранения таблицы, полученной из базы данных (в восьмой лекции мы рассмотрим подробно этот объект). Создайте новое приложение и назовите его "DataReaderToDataGrid". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой:
using System.Data.SqlClient;
В классе формы создаем следующие объекты:
SqlConnection conn = null; //Создаем экземпляр FullDataTable, в который будут помещаться данные DataTable FullDataTable = new DataTable(); //Создаем экземпляр FullDataTable для получения структуры таблицы из базы данных DataTable ShemaDataTable = new DataTable(); SqlDataReader dataReader; SqlCommand myCommand; //Создаем объект objectRow для получения информации о числе столбцов object[] objectRow;
Основной код помещаем в конструктор формы:
public Form1() { InitializeComponent(); try { conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); myCommand = conn.CreateCommand(); myCommand.CommandText = "SELECT * FROM Туристы"; dataReader = myCommand.ExecuteReader(); //Вызываем метод GetSchemaTable, который получает схему таблицы из базы //и передает ее объекту ShemaDataTable ShemaDataTable = dataReader.GetSchemaTable(); //Свойство FieldCount возвращает число столбцов для текущей записи. //Передаем это значение объекту objectRow objectRow = new object[dataReader.FieldCount]; //Определяем структуру объекта FullDataTable for(int i =0; i <dataReader.FieldCount; i++) { FullDataTable.Columns.Add(ShemaDataTable.Rows[i] ["ColumnName"].ToString(), ((System.Type)ShemaDataTable.Rows[i] ["DataType"])); } //Добавляем записи в объект FullDataTable while(dataReader.Read()) { dataReader.GetValues(objectRow); myDataRow = FullDataTable.Rows.Add(objectRow); } //Определяем источник данных для элемента dataGrid1 dataGrid1.DataSource = FullDataTable; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { dataReader.Close(); conn.Close(); } }
Возможно, этот код показался вам сложным. Лучше будет к нему вернуться еще раз после изучения девятой лекции. Запускаем приложение (рис. 6.28):
Рис. 6.28. Приложение DataReaderToDataGrid
В программном обеспечении к курсу вы найдете приложение Data ReaderToDataGrid (Code\Glava3\ DataReaderToDataGrid).
|
|
|
Конструктор объекта OleDbCommand в точности такой же.
2)
Приводится только создание объекта SqlTransaction, без его методов, - см далее Лекцию 7, тему "Работа с транзакциями".
3)
Здесь снова "язык DDL языка SQL:" Так уж это принято, ничего не могу с этим поделать.
4)
Скорее всего, у вас будет измененное содержимое таблицы "Туры" - если, конечно, вы выполняли все действия, описанные в этой главе. Для получения исходной таблицы запустите SQL Server Enterprise Manager, удалите имеющуюся таблицу "Туры" и импортируйте ее снова из файла BDTur_firm2.mdb ((Code\Glava3\ BDTur_firm2.mdb).
5)
Таблица "Туристы" была заменена на оригинальную из базы BDTur_firm2.mdb.
6)
Использование индексов повышает производительность приложения.
7)
Мы это делаем для включения блока обработки исключений.
8)
Вообще-то, раньше свойства, входящие в какой-либо метод, мы называли параметрами. Здесь я не стал использовать это слово, чтобы не было путаницы с параметрами, входящими в коллекцию объекта Command.
Хранимые процедуры в Microsoft Access
В завершение этой лекции рассмотрим хранимые процедуры в Microsoft Access. Хранимые процедуры? А разве Microsoft Access их поддерживает? По правде говоря, нет. Мы не можем создавать в MSAccess такие процедуры, как мы это делали в MS SQL Server, синтаксис не будет содержать ключевого слова "PROC" или "PROCEDURE", и вообще, это совсем не так называется! Но база данных способна хранить SQL-запросы, и если их запускать из внешнего приложения, то функциональность уже будет напоминать саму концепцию хранимых процедур.
Открываем базу BDTur_firm2.mdb. В окне базы данных переключаемся на вкладку "Запросы" и дважды щелкаем на заголовке "Создание запроса в режиме конструктора" (рис. 7.14):
Рис. 7.14. Вкладка "Запросы" в окне базы данных
В появившемся окне добавления таблицы выбираем "Туристы" и нажимаем кнопку "OK" (рис. 7.15).
Рис. 7.15. Добавление таблицы
В режиме конструктора требуется установить столбцы, которые будут извлекаться в запросе. Для этого последовательно выбираем из выпадающего списка названия полей таблицы (рис. 7.16).
Рис. 7.16. Создание запроса
Добавим сортировку по столбцу "Фамилия". В поле "Сортировка" из выпадающего списка выбираем значение "по возрастанию" (рис. 7.17).
Рис. 7.17. Задание сортировки
Можно просмотреть SQL-конструкцию готового запроса. В главном меню выбираем "Вид \ Режим SQL". Окно конструктора изменяет свой вид - в нем появляется текст запроса:
SELECT Туристы.Кодтуриста, Туристы.Фамилия, Туристы.Имя, Туристы.Отчество FROM Туристы ORDER BY Туристы.Фамилия;
Сохраняем запрос, называя его "Сортировка_туристы". Дважды щелкнув на нем в окне базы данных, запускаем - записи таблицы отсортированы (рис. 7.18).
Рис. 7.18. Запуск готового запроса
Займемся теперь созданием приложения, которое будет запускать этот запрос как хранимую процедуру. Создайте новый Windows-проект и назовите его "Stored_Procedure_MSAccess".
Перетаскиваем на форму элемент управления ListBox, его свойству Dock устанавливаем значение "Fill". Подключаем пространство имен для работы с базой данных:
using System.Data.OleDb;
В классе формы определяем строку connectionString:
string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0""; Data Source=""D:\Uchebnik\Code\Glava3\BDTur_firm2.mdb" ";User ID=Admin;Jet OLEDB:Encrypt Database=False";
В конструкторе формы создаем объекты ADO .NET, причем в свойстве CommandType объекта Command задаем тип запроса StoredProcedure:
public Form1() { InitializeComponent(); OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = connectionString; OleDbCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[Сортировка_туристов]"; conn.Open(); OleDbDataReader dataReader = myCommand.ExecuteReader(); while (dataReader.Read()) { // Создаем переменные, получаем для них значения из объекта dataReader, //используя метод GetТипДанных int TouristID = dataReader.GetInt32(0); string Family = dataReader.GetString(1); string FirstName = dataReader.GetString(2); string MiddleName = dataReader.GetString(3); //Выводим данные в элемент llistBox1: listBox1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); } conn.Close(); }
Весь код уже достаточно хорошо знаком - мы его применяли для запуска хранимых процедур MS SQL Server. Запускаем приложение - на форму выводится результат запроса (рис. 7.19).
Рис. 7.19. Готовое приложение Stored_Procedure_MSAccess
В программном обеспечении к курсу вы найдете приложение Stored_ Procedure_MSAccess (Code\Glava3\Stored_Procedure_MSAccess).
|
|
|
Разумеется, с небольшим увеличением производительности.
2)
Она называется SQL Server Books Online. Как вы могли заметить, эту справку можно вызвать из любого приложения, входящего в пакет Microsoft SQL Server 2000.
Работа с транзакциями
Транзакцией называется выполнение последовательности команд (SQL-конструкций) в базе данных, которая либо фиксируется при успешном извлечении каждой команды, либо отменяется при неудачном извлечении хотя бы одной команды. Большинство современных СУБД поддерживают механизм транзакций, и подавляющее большинство клиентских приложений, работающих с ними, используют для выполнения команд транзакции. Зачем нужны транзакции? Представим себе, что в базу данных BDTur_firm2 требуется вставить связанные записи в две таблицы - "Туристы" и "Информацияотуристах". Если запись, вставляемая в таблицу "Туристы", окажется неверной, например, из-за неправильно указанного кода туриста, база данных не позволит внести изменения, а тогда в таблице "Информацияотуристах" появится ненужная запись. Запускаем SQL Query Analyzer, в новом бланке вводим запрос для добавления двух записей:
INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович'); INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
Две записи успешно добавляются в базу данных:
(1 row(s) affected)
(1 row(s) affected)
Изменим код туриста только во втором запросе:
INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович'); INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (7, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
Появляется сообщение о невозможности вставки первой записи с уже имеющимся значением ключевого поля. Вторая запись, однако, была добавлена в таблицу:
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Туристы'. Cannot insert duplicate key in object 'Туристы'. The statement has been terminated. (1 row(s) affected)
Извлекаем содержимое обеих таблиц следующим двойным запросом:
SELECT * FROM Туристы SELECT * FROM Информацияотуристах
В таблице "Информацияотуристах" последняя запись добавилась безо всякой связи с записью таблицы "Туристы" (рис. 7.8):
увеличить изображение
Рис. 7.8. Содержимое таблиц "Туристы" и "Информацияотуристах"
Для того чтобы избегать подобных ошибок, нам нужно применить транзакцию. Удалим все внесенные записи из обеих таблиц (это можно сделать с помощью запроса или в SQL Server Enterprise Manager) и оформим исходные SQL-конструкции в виде транзакции:
BEGIN TRAN DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович'); SELECT @OshibkiTabliciTourists=@@ERROR INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548); SELECT @OshibkiTabliciInfoTourists=@@ERROR IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0 COMMIT TRAN ELSE ROLLBACK TRAN
Начало транзакции мы объявляем с помощью команды BEGIN TRAN. Далее создаем два параметра - @OshibkiTabliciTourists, @OshibkiTabliciInfoTourists для сбора ошибок. После первого запроса возвращаем значение, которое встроенная функция @@ERROR присваивает первому параметру:
SELECT @OshibkiTabliciTourists=@@ERROR
То же самое делаем после второго запроса для другого параметра:
SELECT @OshibkiTabliciInfoTourists=@@ERROR
Проверяем значения обоих параметров, которые должны быть равными нулю при отсутствии ошибок:
IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0
В этом случае подтверждаем транзакцию (внесение изменений) при помощи команды COMMIT TRAN. В противном случае - если значение хотя бы одного из параметров @OshibkiTabliciTourists и @Oshibki TabliciInfoTourists оказывается отличным от нуля, отменяем транзакцию при помощи команды ROLLBACK TRAN.
После выполнения транзакции появляется уже знакомое сообщение:
(1 row(s) affected)
(1 row(s) affected)
Снова изменим код туриста во втором запросе:
BEGIN TRAN DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович'); SELECT @OshibkiTabliciTourists=@@ERROR INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (7, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548); SELECT @OshibkiTabliciInfoTourists=@@ERROR IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0 COMMIT TRAN ELSE ROLLBACK TRAN
Запускаем транзакцию - появляется в точности такое же сообщение, что и в случае применения обычных запросов:
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Туристы'. Cannot insert duplicate key in object 'Туристы'. The statement has been terminated.
(1 row(s) affected)
Однако теперь изменения не были внесены во вторую таблицу (рис. 7.9):
увеличить изображение
Рис. 7.9. Содержимое таблиц "Туристы" и "Информацияотуристах" после выполнения неудачной транзакции
Сообщение "(1 row(s) affected)", указывающее на "добавление" одной записи, в данном случае всего лишь означает, что вторая SQL-конструкция была верной и запись могла быть добавлена в случае успешного выполнения транзакции. Сделаем ошибку во втором запросе и снова попытаемся выполнить транзакцию:
BEGIN TRAN DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (7, 'Тихомиров', 'Андрей', 'Борисович'); SELECT @OshibkiTabliciTourists=@@ERROR INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548); SELECT @OshibkiTabliciInfoTourists=@@ERROR IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0 COMMIT TRAN ELSE ROLLBACK TRAN
Появляется аналогичное сообщение:
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Информацияотуристах'. Cannot insert duplicate key in object 'Информацияотуристах'. The statement has been terminated.
Изменения снова не были внесены в базу данных - в этом можно убедиться, вернув содержимое обеих таблиц. Читатель, хорошо знакомый с теорией баз данных, может заметить, что обеспечить целостность данных двух таблиц (в данном случае это именно так и называется) вполне можно и другими средствами, например, просто связать их и установить соответствующие правила. Это правильно, но для нас сейчас важно понимать, что в одной транзакции можно выполнить несколько самых разных запросов, которые можно разом применить или отклонить. Начало транзакции мы объявляем с помощью команды BEGIN TRAN, а затем принимаем ее - COMMIT TRAN - или отклоняем (откатываем) - ROLLBACK TRAN.
Перейдем теперь к рассмотрению транзакций в ADO .NET. Создайте новое консольное приложение и назовите его "EasyTransaction". Поставим задачу: передать те же самые данные в две таблицы - "Туристы" и "Информацияотуристах". Привожу полный листинг консольного приложения:
using System; using System.Data.SqlClient;
namespace EasyTransaction { class Class1 { [STAThread] static void Main(string[] args) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); //Создаем транзакцию myCommand.Transaction = conn.BeginTransaction(); try { myCommand.CommandText = "INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович')"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, 'CA 1234567', apos;Новосибирск', 'Россия', 1234567, 996548)"; myCommand.ExecuteNonQuery(); //Подтверждаем транзакцию myCommand.Transaction.Commit(); Console.WriteLine("Передача данных успешно завершена"); } catch(Exception ex) { //Отклоняем транзакцию myCommand.Transaction.Rollback(); Console.WriteLine("При передаче данных произошла ошибка: "+ ex.Message); } finally { conn.Close(); } } } }
Перед запуском приложения снова удаляем все добавленные записи из таблиц. При успешном выполнении запроса появляется соответствующее сообщение, а в таблицы добавляются записи (рис. 7.10):
Рис. 7.10. Приложение EasyTransaction. Транзакция выполнена
Повторный запуск этого приложения приводит к отклонению транзакции - нельзя вставлять записи с одинаковыми значениями первичных ключей (рис. 7.11):
увеличить изображение
Рис. 7.11. Приложение EasyTransaction. Транзакция отклонена
В виде транзакции можно заключать выполнение одной или нескольких хранимых процедур, - в самом деле, общая конструкция имеет следующий вид:
//Создаем соединение //Создаем транзакцию myCommand.Transaction = conn.BeginTransaction(); try { //Выполняем команды, вызываем одну или несколько хранимых процедур //Подтверждаем транзакцию myCommand.Transaction.Commit(); } catch(Exception ex) { //Отклоняем транзакцию myCommand.Transaction.Rollback(); } finally { //Закрываем соединение conn.Close(); }
При выполнении транзакций несколькими пользователями одной базы данных могут возникать следующие проблемы:
Dirty reads - "грязное" чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь (или создаваемая им транзакция) извлекает частично измененные данные, которые не являются верными. Non-repeatable reads - неповторяемое чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь начинает и завершает другую транзакцию. Первый пользователь при повторном чтении данных (например, если в его транзакцию входит несколько инструкций SELECT) получает другой набор записей.Phantom reads - чтение фантомов. Первый пользователь начинает транзакцию, выбирающую данные из таблицы. В это время другой пользователь начинает и завершает транзакцию, вставляющую или удаляющую записи. Первый пользователь получит другой набор данных, содержащий фантомы - удаленные или измененные строки.
Для решения этих проблем разработаны четыре уровня изоляции транзакции:
Read uncommitted. Транзакция может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции может привести ко всем перечисленным проблемам.Read committed. Транзакция не может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции исключает проблему "грязного" чтения.Repeatable read. Транзакция не может считывать данные, с которыми работают другие транзакции. Другие транзакции также не могут считывать данные, с которыми работает эта транзакция. Применение этого уровня изоляции исключает все проблемы, кроме чтения фантомов. Serializable. Транзакция полностью изолирована от других транзакций. Применение этого уровня изоляции полностью исключает все проблемы.
По умолчанию устанавливается уровень Read committed. В справке Microsoft SQL Server 20002) (Указатель - вводим "isolation levels" - заголовок "overview") приводится таблица, иллюстрирующая различные уровни изоляции (рис. 7.12):
Рис. 7.12. Уровни изоляции Microsoft SQL Server 2000
Использование наибольшего уровня изоляции (Serializable) означает наибольшую безопасность и вместе с тем наименьшую производительность - все транзакции выполняются в виде серии, последующая вынуждена ждать завершения предыдущей. И наоборот, применение наименьшего уровня (Read uncommitted) означает максимальную производительность и полное отсутствие безопасности. Впрочем, нельзя дать универсальных рекомендаций по применению этих уровней - в каждой конкретной ситуации решение будет зависеть от структуры базы данных и характера выполняемых запросов.
Для установки уровня изоляции применяется следующая команда:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED или READ COMMITTED или REPEATABLE READ или SERIALIZABLE
Например, в транзакции, добавляющей две записи, уровень изоляции указывается следующим образом:
BEGIN TRAN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int ... ROLLBACK TRAN
В ADO . NET уровень изоляции можно установить при создании транзакции:
myCommand.Transaction = conn.BeginTransaction (System.Data.IsolationLevel.Serializable);
Дополнительно поддерживаются еще два уровня (см. рис. 7.13):
Chaos. Транзакция не может перезаписать другие непринятые транзакции с большим уровнем изоляции, но может перезаписать изменения, внесенные без использования транзакций. Данные, с которыми работает текущая транзакция, не блокируются; Unspecified. Отдельный уровень изоляции, который может применяться, но не может быть определен. Транзакция с этим уровнем может применяться для задания собственного уровня изоляции.
увеличить изображение
Рис. 7.13. Определение уровня транзакции
Транзакции обеспечивают целостность базы данных, при разработке многоуровневых приложений их применение является обязательным правилом.
В программном обеспечении к курсу вы найдете приложение Easy Transaction (Code\Glava3 \EasyTransaction).
Вызов хранимых процедур с входными и выходными параметрами
На практике наиболее часто используются хранимые процедуры с входными и выходными параметрами (см. таблицу 5.3). Создайте новое приложение и назовите его "VisualOutputParameter". Устанавливаем следующие свойства формы:
| FormBorderStyle | FixedSingle |
| MaximizeBox | False |
| Size | 400; 96 |
Добавляем на форму элементы управления и устанавливаем их свойства:
| Name | txtTouristID |
| Location | 15; 20 |
| Size | 120; 20 |
| Text | Введите код туриста |
| Name | lblFamily |
| Location | 151; 20 |
| Size | 144; 23 |
| Text |
| Name | btnRun |
| Location | 303; 20 |
| Text | Запуск |
Переходим на вкладку Server Explorer, раскрываем узел Stored Procedures базы данных BDTur_firm2 и перетаскиваем процедуру proc_po1. Выделяем появившейся объект sqlCommand1, в окне Properties нажимаем на кнопку
увеличить изображение
Рис. 7.4. Приложение VisualOutputParameter. Свойство Parameters объекта sqlCommand1
Сообщение об успешности выполнения команды возвращается при помощи параметра @RETURN_VALUE, значение которого для запроса данной хранимой процедуры будет равно нулю. Для процедур, содержащих запросы UPDATE, INSERT и DELETE, возвращаемое значение будет равно числу измененных записей. Обратите внимание, в тексте процедуры мы не создавали этот параметр - среда сгенерировала его автоматически! Два последних параметра были определены при создании хранимой процедуры. Подключаем пространство имен для работы с базой:
using System.Data.SqlClient;
В обработчике кнопки btnRun выводим фамилию искомого туриста в качестве текста надписи:
private void btnRun_Click(object sender, System.EventArgs e) { try { int TouristID = int.Parse(this.txtTouristID.Text); sqlCommand1.Parameters["@TouristID"].Value = TouristID; sqlConnection1.Open(); sqlCommand1.ExecuteScalar(); lblFamily.Text = Convert.ToString(sqlCommand1.Parameters["@LastName"].Value); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { sqlConnection1.Close(); } }
Запускаем приложение. Если в таблице имеется фамилия, соответствующая заданному коду, она выводится на форму (рис. 7.5):
Рис. 7.5. Готовое приложение VisualOutputParameter
При использовании визуальных средств код в обработчике ничем не отличается от рассматриваемого ранее - мы нигде не указывали значение output параметра @LastName. Дело в том, что среда сама настроила это значение в свойстве Direction (см. рис. 7.4). При программном вызове хранимой процедуры нам придется определять его вручную. Скопируйте папку приложения VisualOutputParameter и переименуйте ее в "ProgrammOutputParameter". Открываем проект, удаляем все объекты с панели компонент формы. В классе формы создаем объект Connection:
SqlConnection conn = null;
Обработчик кнопки btnRun принимает следующий вид:
private void btnRun_Click(object sender, System.EventArgs e) { try { conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_po1]"; int TouristID = int.Parse(this.txtTouristID.Text); myCommand.Parameters.Add("@TouristID", SqlDbType.Int, 4); myCommand.Parameters["@TouristID"].Value = TouristID; //Необязательная строка, т.к. совпадает со значением по умолчанию. //myCommand.Parameters["@TouristID"].Direction = ParameterDirection.Input; myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 60); myCommand.Parameters["@LastName"].Direction = ParameterDirection.Output; conn.Open(); myCommand.ExecuteScalar(); lblFamily.Text = Convert.ToString (myCommand.Parameters["@LastName"].Value); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
Мы добавили параметр @LastName в набор Parameters, причем его значение output указали в свойстве Direction:
myCommand.Parameters["@LastName"].Direction = ParameterDirection.Output;
Параметр @ TouristID является исходным, поэтому для него в свойстве Direction указывается Input. Поскольку это является значением по умолчанию для всех параметров набора Parameters, указывать его явно не нужно. Перечисление ParameterDirection принимает еще два значения - InputOutput и ReturnValue (рис. 7.6).
Рис. 7.6. Значения перечисления ParameterDirection
Для параметров, работающих в двустороннем режиме, устанавливается значение InputOutput, для параметров, возвращающих данные о выполнения хранимой процедуры, - ReturnValue. Примером последнего может служить @RETURN_VALUE (см. рис. 7.4).
В программном обеспечении к курсу вы найдете приложения Visual OutputParameter и ProgrammOutputParameter (Code\Glava3 \VisualOutput Parameter и ProgrammOutputParameter).
Вызов хранимых процедур с входными параметрами
Теперь, когда мы разобрались с методами объекта Command, мы можем вернуться к работе с хранимыми процедурами. Мы уже применяли самые простые процедуры (они приводятся в таблице 5.1), содержимое которых представляло собой, по сути, простой запрос на выборку в Windows-приложениях. Применение хранимых процедур с параметрами (таблица 5.2), как правило, связано с интерфейсом приложения - пользователь имеет возможность вводить значение и затем на основании его получать результат.
Среда Visual Studio .NET предоставляет средства для визуальной работы с хранимыми процедурами. Создайте новый Windows-проект и назовите его "VisualParametersSP". Устанавливаем следующие свойства формы:
| FormBorderStyle | FixedSingle |
| MaximizeBox | False |
| Size | 450; 330 |
Добавляем на форму элементы управления и устанавливаем их свойства:
| Location | 17; 12 |
| Size | 408; 136 |
| Text | Хранимая процедура proc_p1 |
| Location | 17; 156 |
| Size | 408; 64 |
| Text | Хранимая процедура proc_p5 |
| Location | 17; 228 |
| Size | 408; 56 |
| Text | Хранимая процедура proc6 |
| Name | txtFamily_p1 |
| Location | 16; 32 |
| Size | 288; 20 |
| Text | Введите фамилию туриста |
| Name | txtNameTour_p5 |
| Location | 16; 24 |
| Size | 136; 20 |
| Text | Введите название тура |
| Name | txtKurs_p5 |
| Location | 168; 24 |
| Size | 128; 20 |
| Text | Введите курс валюты |
| Name | btnRun_p1 |
| Location | 320; 32 |
| Text | Запуск |
| Name | btnRun_p5 |
| Location | 320; 24 |
| Text | Запуск |
| Name | btnRun_proc6 |
| Location | 16; 24 |
| Size | 208; 23 |
| Text | Цена самого дорогого тура |
| Name | lbResult_p1 |
| Location | 16; 72 |
| Size | 376; 43 |
| Name | lblPrice_proc6 |
| Location | 264; 24 |
| Text | |
| TextAlign | MiddleCenter |
Интерфейс приложения готов. Переходим в окно Server Explorer, раскрываем узел подключения к базе данных, перетаскиваем на форму процедуры proc_p1, proc_p5 и proc6 (рис. 7.1, А).
На панели компонент проекта появляются объект sqlConnection1 с тремя объектами sqlCommand (рис. 7.1, Б):
увеличить изображение
Рис. 7.1. Хранимые процедуры в окне Server Explorer. А - перемещение на форму, Б - готовая панель компонент
Среда настроила все нужные свойства объектов sqlCommand, такие как CommandType, CommandText, Connection. Выделяем объект sqlCommand1, переходим в окно Properties, в поле свойства Parameters нажимаем на кнопку
увеличить изображение
Рис. 7.2. Окно Properties объекта sqlCommand1 и редактор SqlParameter Collection Editor
В появившемся окне редактора "SqlParameter Collection Editor" можно видеть настроенные свойства "Size" и "ParameterName" параметра "@Фамилия". Эти значения были получены из базы данных. Аналогичным образом настроены другие объекты sqlCommand. Переходим в код формы. Подключаем пространство имен для работы с базой данных:
using System.Data.SqlClient;
Далее нам нужно выбрать, какой из методов объекта Command нужно применить. Для хранимой процедуры proc_p1 это будет ExecuteReader - возвращаемое значение представляет собой запись (см. таблицу 5.2). Добавляем обработчик кнопки btnRun_p1:
private void btnRun_p1_Click(object sender, System.EventArgs e) { string FamilyParameter = Convert.ToString(txtFamily_p1.Text); sqlCommand1.Parameters["@Фамилия"].Value = FamilyParameter; sqlConnection1.Open(); SqlDataReader dataReader = sqlCommand1.ExecuteReader(); while (dataReader.Read()) { // Создаем переменные, получаем для них значения из объекта dataReader, //используя метод GetТипДанных int TouristID = dataReader.GetInt32(0); string Family = dataReader.GetString(1); string FirstName = dataReader.GetString(2); string MiddleName = dataReader.GetString(3); //Выводим данные в элемент lbResult_p1 lbResult_p1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); } sqlConnection1.Close(); }
В результате выполнения процедуры proc_p1 изменяется значения поля "Цена" в таблице "Туры" - запрос не возвращает результатов. Поэтому здесь применяем метод ExecuteNonQuery:
private void btnRun_p5_Click(object sender, System.EventArgs e) { string NameTourParameter = Convert.ToString(txtNameTour_p5.Text); double KursParameter = double.Parse(this.txtKurs_p5.Text); sqlCommand2.Parameters["@nameTour"].Value = NameTourParameter; sqlCommand2.Parameters["@Курс"].Value = KursParameter; sqlConnection1.Open(); int UspeshnoeIzmenenie = sqlCommand2.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); } sqlConnection1.Close(); }
Процедура proc6 возвращает результат в виде значения наибольшей цены в таблице "Туры". Для вывода одиночного значения используем метод ExecuteScalar. Поскольку процедура не имеет входных параметров, обработчик кнопки btnRun_proc6 будет выглядеть предельно просто:
private void btnRun_proc6_Click(object sender, System.EventArgs e) { sqlConnection1.Open(); string MaxPrice = Convert.ToString(sqlCommand3.ExecuteScalar()); lblPrice_proc6.Text = MaxPrice; sqlConnection1.Close(); }
Запускаем приложение (рис. 7.3). Для просмотра результатов выполнения хранимой процедуры proc_p5 (таблицы "Туры") запускаем SQL Server Enterprise Manager.
Рис. 7.3. Готовое приложение VisualParametersSP
В программном обеспечении к курсу вы найдете приложение Visual ParametersSP (Code\Glava3\ VisualParametersSP).
Создадим в точности такое же приложение программно. Для того чтобы не делать заново интерфейс приложения, скопируем всю папку проекта VisualParametersSP, переименуем ее в "ProgrammParametersSP". Открываем проект и удаляем все объекты с панели компонент. В классе формы создаем строку подключения:
string connectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2";
В каждом из обработчиков кнопок создаем объекты Connection и Command, определяем их свойства, для последнего добавляем нужные параметры в набор Parameters:
private void btnRun_p1_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_p1]"; string FamilyParameter = Convert.ToString(txtFamily_p1.Text); myCommand.Parameters.Add("@Фамилия", SqlDbType.NVarChar, 50); myCommand.Parameters["@Фамилия"].Value = FamilyParameter; conn.Open(); SqlDataReader dataReader = myCommand.ExecuteReader(); while (dataReader.Read()) { // Создаем переменные, получаем для них значения // из объекта dataReader, используя метод GetТипДанных int TouristID = dataReader.GetInt32(0); string Family = dataReader.GetString(1); string FirstName = dataReader.GetString(2); string MiddleName = dataReader.GetString(3); //Выводим данные в элемент lbResult_p1 lbResult_p1.Items.Add("Код туриста: " + TouristID+ " Фамилия: " + Family + " Имя: "+ FirstName + " Отчество: " + MiddleName); } conn.Close(); }
private void btnRun_p5_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_p5]"; string NameTourParameter = Convert.ToString(txtNameTour_p5.Text); double KursParameter = double.Parse(this.txtKurs_p5.Text); myCommand.Parameters.Add("@nameTour", SqlDbType.NVarChar, 50); myCommand.Parameters["@nameTour"].Value = NameTourParameter; myCommand.Parameters.Add("@Курс", SqlDbType.Float, 8); myCommand.Parameters["@Курс"].Value = KursParameter; conn.Open(); int UspeshnoeIzmenenie = myCommand.ExecuteNonQuery(); if (UspeshnoeIzmenenie !=0) { MessageBox.Show("Изменения внесены", "Изменение записи"); } else { MessageBox.Show("Не удалось внести изменения", "Изменение записи"); } conn.Close(); }
private void btnRun_proc6_Click(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = connectionString; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc6]"; conn.Open(); string MaxPrice = Convert.ToString(myCommand.ExecuteScalar()); lblPrice_proc6.Text = MaxPrice; conn.Close(); }
Результат работы приложения будет такой же, как и в случае применения визуальных средств студии1).
Сравните листинги приложений VisualParametersSP и Programm ParametersSP - в первом из них среда создала все объекты соединения и наборы параметров, нам оставалось только связать значения параметров с элементами управлений при помощи свойства Value. С набором Parameters объекта Command мы уже встречались в приложении ExamWinExecuteNonQuery, когда применяли параметризированные запросы.
В программном обеспечении к курсу вы найдете приложение Programm ParametersSP (Code\Glava3\ ProgrammParametersSP).
Вызов хранимых процедур, содержащих несколько SQL-конструкций
Хранимая процедура может содержать несколько SQL-конструкций, определяющих работу приложения. При ее вызове возникает задача распределения данных, получаемых от разных конструкций. Запускаем Visual Studio .NET, переходим на вкладку Server Explorer, раскрываем узел базы BDTur_firm2. Щелкаем правой кнопкой на узле Stored Procedures и в появившемся меню выбираем "New Stored Procedure". Процедура proc_NextResult будет состоять из двух конструкций: первая будет возвращать содержимое таблицы "Туристы", а вторая - содержимое таблицы "Туры":
CREATE PROCEDURE proc_NextResult AS SET NOCOUNT ON SELECT *FROM Туристы SELECT * FROM Туры RETURN
После сохранения процедуры создайте новое Windows-приложение VisualNextResult. Перетаскиваем на форму элемент управления ListBox, его свойству Dock устанавливаем значение Bottom. Добавляем элемент Splitter (разделитель), свойству Dock которого также устанавливаем значение Bottom. Наконец, добавляем еще один элемент ListBox, свойству Dock которого устанавливаем значение Fill. Из окна Server Explorer перетаскиваем на форму только что созданную процедуру proc_NextResult. В классе формы добавляем пространство имен для работы с базой:
using System.Data.SqlClient;
Наша задача: в первый элемент ListBox вывести несколько произвольных столбцов таблицы "Туры", а во второй - несколько столбцов таблицы "Туристы". Конструктор формы будет иметь следующий вид:
public Form1() { InitializeComponent(); sqlConnection1.Open(); SqlDataReader dataReader = sqlCommand1.ExecuteReader(); while(dataReader.Read()) { listBox2.Items.Add(dataReader.GetString(1)+" "+dataReader.GetString(2)); } dataReader.NextResult(); while(dataReader.Read()) { listBox1.Items.Add(dataReader.GetString(1) + ". Дополнительная информация: "+dataReader.GetString(3)); } dataReader.Close(); sqlConnection1.Close(); }
Метод GetString объекта DataReader позволяет получать содержимое столбца c заданным индексом, приведенное к типу String.
В первом цикле while мы получаем результаты первого запроса SELECT, затем, вызывая метод NextResult объекта DataReader, переходим к результатам второго запроса. Запускаем приложение - в каждом элементе содержится свой набор записей (рис. 7.7):
Рис. 7.7. Готовое приложение VisualNextResult
Нетрудно сделать это же самое приложение без применения визуальных средств студии. Скопируйте папку приложения VisualNextResult и переименуйте ее в ProgrammNextResult. Открываем проект, удаляем все объекты с панели компонент формы. Конструктор формы примет следующий вид:
public Form1() { InitializeComponent(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\". \"; persist security info=False; initial catalog=BDTur_firm2"; SqlCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandText = "[proc_NextResult]"; conn.Open(); SqlDataReader dataReader = myCommand.ExecuteReader(); while(dataReader.Read()) { listBox2.Items.Add(dataReader.GetString(1)+" "+dataReader.GetString(2)); } dataReader.NextResult(); while(dataReader.Read()) { listBox1.Items.Add(dataReader.GetString(1) + ". Дополнительная информация: "+dataReader.GetString(3)); } dataReader.Close(); conn.Close(); }
В программном обеспечении к курсу вы найдете приложения VisualNext Result и ProgrammNextResult (Code\Glava3\VisualNextResult и Programm NextResult).
Объект DataRow
Содержимое объекта DataSet представляет собой набор записей, который представлен объектами DataRow. В запущенном приложении содержимое объекта DataSet доступно для изменений, например, если данные выводятся в элемент управления DataGrid, то, перемещаясь по отдельным клеткам, можно править значения как в обычной электронной таблице. При этом происходит изменение объекта DataRow, соответствующее заданной записи. Рассмотрим программное создание и изменение записей. Создайте новое Windows-приложение и назовите его ProgrammDataRow. Перетаскиваем на форму элемент управления DataGrid, свойству Dock устанавливаем значение Fill. В конструкторе формы создаем экземпляр dtTours и поля, соответствующие таблице "Туры":
public Form1() { InitializeComponent(); DataTable dtTours = new DataTable(); DataColumn dсIDtour = dtTours.Columns.Add("Код тура", typeof(Int32)); dсIDtour.Unique = true; DataColumn dcName = dtTours.Columns.Add("Название"); DataColumn dcPrice = dtTours.Columns.Add("Цена", typeof(Decimal)); DataColumn dcInformation = dtTours.Columns.Add("Информация"); DataView myDataView = new DataView(dtTours); dataGrid1.DataSource = myDataView; }
Для того чтобы привязать созданные данные к элементу управления DataGrid, нам понадобилось создать экземпляр myDataView класса DataView. Каждый объект DataTable содержит объект DataView, причем этот объект, используемый по умолчанию, называется DataTable.DefaultView. Мы уже сталкивались с ним неоднократно, например, в предыдущем проекте CustomExpression для вывода данных:
dataGrid1.DataSource = dsTours.Tables["Туры"].DefaultView;
Один объект DataTable может иметь несколько объектов DataView - это удобно для вывода одних и тех же данных, отфильтрованных или отсортированных различным образом. В следующей лекции мы рассмотрим подробно DataView. Запускаем приложение (рис. 8.13). Мы видим готовую структуру таблицы "Туры":
Рис. 8.13. Структура таблицы "Туры"
Мы не будем сейчас подключаться к какой-либо базе данных - попробуем заполнить таблицу записями программно. Для добавлений одной новой записи перед созданием экземпляра myDataView вставляем следующий фрагмент кода:
DataRow myRow = dtTours.NewRow(); myRow["Код тура"] = 1; myRow["Название"] = "Кипр"; myRow["Цена"] = 25000; myRow["Информация"] = "В стоимость двух взрослых путевок входит цена одной детской (до 7 лет)"; dtTours.Rows.Add(myRow);
Запускаем приложение (рис. 8.14). В таблице появилась первая запись.
Рис. 8.14. Добавление записи в таблицу
Добавим еще одну запись:
DataRow myRow2 = dtTours.NewRow(); myRow2["Код тура"] = 2; myRow2["Название"] = "Греция"; myRow2["Цена"] = 32000; myRow2["Информация"] = "В августе и сентябре действуют специальные скидки"; dtTours.Rows.Add(myRow2);
Название, указываемое в квадратных скобках объектов myRow или myRow2, представляет собой имя столбца, которые мы определили в самом начале. К столбцу можно обращаться и по индексу - закомментируйте добавление двух записей и внесите следующий код:
DataRow myRow = dtTours.NewRow(); myRow[0] = 1; myRow[1] = "Кипр"; myRow[2] = 25000; myRow[3] = "В стоимость двух взрослых путевок входит цена одной детской (до 7 лет)"; dtTours.Rows.Add(myRow);
DataRow myRow2 = dtTours.NewRow(); myRow2[0] = 2; myRow2[1] = "Греция"; myRow2[2] = 32000; myRow2[3] = "В августе и сентябре действуют специальные скидки"; dtTours.Rows.Add(myRow2);
Нумерация столбцов начинается с нуля. Более удобный способ добавления записей - применение свойства ItemArray объекта DataRow, где можно задавать значения полей в виде массива:
DataRow myRow3 = dtTours.NewRow(); myRow3.ItemArray = new object[]{3,"Таиланд", 30000, null}; dtTours.Rows.Add(myRow3);
Здесь мы указали значение поля "Информация", равное null, - таким образом можно пропускать неизвестные поля (рис. 8.15):
Рис. 8.15. Вставка записи с одним значением null
В программном обеспечении к курсу вы найдете приложение Programm DataRow (Code\Glava4\ ProgrammDataRow).
Конечно, вставка записей вручную в объект DataSet, не связанный с хранилищем данных, имеет не очень большой смысл. Поэтому давайте рассмотрим, как вставлять (и изменять) данные в уже загруженный кэш данных. Скопируйте папку проекта CustomExpression и переименуйте ее в WorkWithRow. Чтобы не тратить время на привязку действий к элементам управления, будем далее писать код после отображения данных в элементе DataGrid:
dataGrid1.DataSource = dsTours.Tables["Туры"].DefaultView;
Для изменения, например, пятой строки, мы указываем в свойстве Rows объекта dtTours ее индекс (4, нумерация начинается с нуля), затем вызываем метод BeginEdit для начала редактирования, устанавливаем группу свойств и в заключение принимаем изменения, вызывая метод EndEdit:
DataRow myRow=dtTours.Rows[4]; myRow.BeginEdit(); myRow["Код тура"] = 5; myRow["Название"] = "Турция"; myRow["Цена"] = "27000"; myRow["Информация"] = "Осенние скидки с 15 октября"; myRow.EndEdit();
Тот же самый результат мы получим с помощью свойства ItemArray:
DataRow myRow=dtTours.Rows[4]; myRow.BeginEdit(); myRow.ItemArray = new object[]{5,"Турция", 27000, null, null, "Осенние скидки с 15 октября"}; myRow.EndEdit();
Здесь мы установили для третьего и четвертого полей, которые являются вычисляемыми, значения null, подразумевая, что они останутся по умолчанию, а при запуске заполнятся своими значениями (рис. 8.16):
Рис. 8.16. Пропущенные вычисляемые поля заполняются своими значениями
Для удаления заданной записи нужно создать объект DataRow, которому передается индекс строки, а затем вызвать метод Remove свойства Rows объекта DataTable:
DataRow myRow2 = dtTours.Rows[0]; dtTours.Rows.Remove(myRow2);
Этого достаточно для удаления строки, но для того, чтобы пометить заданную строку как удаленную, вызываем метод Delete:
myRow2.Delete();
В результате у нас удалится строка (рис. 8.17), причем объект DataTable пометит ее в качестве удаленной - это необходимо, чтобы избежать ошибок (например, в связанных записях).
увеличить изображение
Рис. 8.17. Первая строка, имеющая индекс 0, была удалена
В программном обеспечении к курсу вы найдете приложение WorkWith Row (Code\Glava4\ WorkWithRow).
Понятие DataSet, DataTable и DataColumn
Итак, DataSet представляет собой буфер для хранения данных из базы. Этот буфер предназначен для хранения структурированной информации, представленной в виде таблиц, поэтому первым, самым очевидным вложенным объектом DataSet является DataTable. Внутри одного объекта DataSet может храниться несколько загруженных таблиц из базы данных, помещенных в соответствующие объекты DataTable. Всякая таблица состоит из столбцов (называемых также полями или колонками) и строк. Для обращения к ним и для управления столбцами и строками в объекте DataTable предназначены специальные объекты - DataColumn и DataRow. Между таблицами, как мы знаем, могут быть связи - здесь они представлены объектом DataRelation. Наконец, в таблицах есть первичные и вторичные ключи - объект Constraint со своими двумя подклассами UniqueConstraint и ForeighKeyConstraint описывают их. Я все вр емя говорю "представлены", "описывают", избегая слов "отображают" и "определяются" - дело в том, что нельзя ставить знак равенства между, например, объектом DataRelation и связью таблиц. В загруженных таблицах не формируются автоматически все нужные объекты - кое-где мы должны делать это самостоятельно. Сами объекты имеют также довольно тонкую и сложную структуру, поэтому это было бы довольно грубым приближением. Однако, на первых порах, для понимания сути полезно держать в голове следующие "формулы":
DataSet = одна или несколько таблиц = один или несколько объектов DataTable. DataTable = таблица. DataColumn = столбец, поле, колонка. DataRow = строка. DataTable = таблица = несколько полей, столбцов, колонок = несколько объектов DataColumn. DataTable = таблица = несколько строк = несколько объектов DataRow. DataRelation = связь между таблицами.
Возникает вопрос: для чего нужны эти объекты, если мы прекрасно обходились и без них для вывода содержимого таблицы, например в элемент DataGrid? Дело в том, что для простого отображения информации создавать эти объекты не требуется, но тогда все данные будут однородными текстовыми переменными, подобно таблицам в документе Microsoft Word.
DataSet не может сам сформировать структуру данных - тип переменных, первичные и вторичные ключи, связи между таблицами. Для управления структурой, для сложного отображения (например, вывод информации с привязкой к элементам, создаваемым в режиме работы приложения) и нужно определение этих объектов.
Лучший способ разобраться с работой всех объектов - применить их на практике. Создадим простую тестовую программу, в которой можно отвечать на вопросы, перемещаться по ним и определять количество верных ответов. Для хранения вопросов и вариантов ответов создадим базу данных Tests Microsoft SQL. База будет состоять всего из двух таблиц (рис. 8.1):
Рис. 8.1. Структура базы Tests (диаграмма "QuestVar")
Каждый вопрос будет содержать несколько ответов, для синхронного перемещения по структуре нужна связь по полю questID. Галочка "Allow Nulls" (Разрешить пустые значения) снята для всех полей - это означает, что все поля будут обязательными для заполнения. Структура таблиц Questions и Variants приводится в таблице 8.1:
| Questions - таблица вопросов | questID Номер вопроса |
| question Текст вопроса | |
| questType Тип вопроса (с одним правильным вариантом ответа или с несколькими) | |
| Variants - таблица вариантов ответов | id Номер ответа |
| questID Номер вопроса | |
| variant Текст варианта ответа | |
| isRight Является ли данный вопрос верным |
| 1 | Для переустановки операционной системы Windows XP вам необходимо экспортировать банк сообщений программы Microsoft Outlook Express, расположенный по адресу: | 0 |
| 2 | При компиляции программы в среде Microsoft Visual Studio .NET возникает систематическая ошибка в модуле AssemblyInfo из-за неудачного выбора имени пользователя и организации (были использованы кавычки) при установке системы (Для просмотра: "Мой компьютер - правая кнопка - Свойства - вкладка "Общие""). Вам необходимо изменить эти параметры | 0 |
| 3 | Выберите группы, состоящие из файлов, размер которых после архивирования составляет 5-10% от исходного | 1 |
| 4 | Укажите ряд, состоящий из агрегатных (агрегаторных) функций SQL | 0 |
| 5 | Вы изменили ключ BootExecute в разделе реестра [HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Session Manager]. В результате было выполнено следующее действие: | 0 |
| 1 | 1 | C:\Program Files\Outlook Express\Mail | 0 |
| 2 | 1 | C:\Documents and Settings\Имя_Пользователя\ Local Settings\Application Data\Identities\ {F4CB90C4-3FD5-406B-83FB-85E644627B87}\Microsoft\Outlook Express | 1 |
| 3 | 1 | C:\WINDOWS\system32\Microsoft\Outlook Express\Bases | 0 |
| 4 | 1 | C:\Documents and Settings\Default User\Cookies | 0 |
| 5 | 2 | "Мой компьютер - правая кнопка - Свойства - Вкладка "Общие" на подписи - правая кнопка - Свойства - Переименовать" | 0 |
| 6 | 2 | [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion] затем меняем параметры RegisteredOwner и RegisteredOrganization | 1 |
| 7 | 2 | [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT] удаляем раздел CurrentVersion | 0 |
| 8 | 2 | [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run] | 0 |
| 9 | 3 | *.mpeg, *.mdb, *. Htm | 0 |
| 10 | 3 | *.xls, *.txt , *.mht | 1 |
| 11 | 3 | *. jpeg, *.gif, *.mp3 | 0 |
| 12 | 3 | *. doc, *. xml, *.bmp | 1 |
| 13 | 4 | update, insert, sum | 0 |
| 14 | 4 | where, like, create | 0 |
| 15 | 4 | count, min, max | 1 |
| 16 | 4 | select, avg, into | 0 |
| 17 | 5 | Отключилась проверка на ошибки всех дисковых разделов при загрузке Windows ХР | 1 |
| 18 | 5 | Установлен таймер на автоматическое отключение | 0 |
| 19 | 5 | Отключены все диагностические сообщения | 0 |
| 20 | 5 | Система перестанет загружаться | 0 |
В поле isRight таблицы Variants правильные ответы отмечаются значением "1". Понятно, что для практического применения тестовой программы следует ограничить доступ к базе данных Tests. Мы, однако, будем использовать подключения без пароля.
Создайте новое Windows-приложение и назовите его "Tests". Устанавливаем следующие свойства формы:
| FormBorderStyle | FixedSingle |
| MaximizeBox | False |
| Size | 550; 350 |
| Text | Тест |
| Location | 16;8 |
| Text | Вопрос: |
| Name | txtQuestion |
| Location | 12; 24 |
| Multiline | True |
| Size | 520; 90 |
| TabIndex | 8 |
| Text |
| Name | GbVariants |
| Location | 12; 120 |
| Size | 520; 150 |
| Text | Варианты ответов |
| Name | btnFirst |
| Location | 24; 280 |
| Text | << |
| Name | BtnPrev |
| Location | 99; 280 |
| Text | < |
| Name | BtnNext |
| Location | 174; 280 |
| TabIndex | 0 |
| Text | > |
| Name | BtnLast |
| Location | 249; 280 |
| Text | > |
| Name | BtnCheck |
| Location | 360; 280 |
| Size | 150; 23 |
| Text | Результат |
В свойстве Tables элемента DataSet нажимаем на кнопку
| TableName | Questions |
| Name | dtQuestions |
увеличить изображение
Рис. 8.2. Запуск редактора Table Collection Editor
Значение dtQuestions свойства Name указывает название созданного объекта DataTable, а значение Questions свойства TableName указывает название таблицы, которая будет помещена в DataTable.
Добавим поля к объекту DataTable. В редакторе Tables Collection Editor в поле свойства Columns нажимаем на кнопку
| ColumnName | questID |
| DataType | System.Int32 |
| Unique | True |
| Name | dсQuestID |
| ColumnName | question |
| Name | dcQuestion |
| ColumnName | questType |
| DataType | System.Int32 |
| Name | dcQuestType |
увеличить изображение
Рис. 8.3. Запуск редактора Columns Collection Editor
Завершив работу с редактором Columns Collection Editor, нажимаем кнопку Close. Мы закончили создание объекта DataTable для таблицы Questions. Аналогичные действия надо проделать, чтобы создать DataTable для таблицы Variants (рис. 8.4) и соответствующих полей id, questID, variant и isRight:
| TableName | Variants |
| Name | dtVariants |
| ColumnName | id |
| DataType | System.Int32 |
| Unique | True |
| Name | dcID |
| ColumnName | questID |
| DataType | System.Int32 |
| Name | dcVariantQuestID |
| ColumnName | variant |
| Name | dcVariant |
| ColumnName | isRight |
| DataType | System.Boolean |
| Name | dcIsRight |
увеличить изображение
Рис. 8.4. Создание объектов DataTable и DataColumn для таблицы Variants
В базе данных Tests таблицы Questions и Variants мы связали по полю questID. Теперь при проектировании схемы базы нам следует также создать это отношение. В окне Properties объекта DataSet нажимаем на кнопку (_) в поле свойства Relations. В появившемся редакторе Relations Collection Editor нажимаем кнопку "Add" для добавления связи. Называем отношение "QuestionsVariants", а в качестве ключевого поля указываем questID (рис. 8.5):
Рис. 8.5. Создание отношения QuestionsVariants
Мы закончили работу с визуальными средствами. Программное создание объектов DataTable, DataColumn, DataRelation (см.
далее) обеспечивает, при прочих равных условиях, большую производительность, однако редакторы позволяют быстрее осуществлять редактирование и обладают большей наглядностью. На первых порах проще работать именно с ними, но по мере роста опыта следует отказаться от их применения.
Переходим в код формы, подключаем пространство имен:
using System.Data.SqlClient;
В классе создаем перечисление QuestionType - вопросу с одним правильным вариантом будет соответствовать постоянная SingleVariant, вопросу с несколькими вариантами - MultiVariant:
public enum QuestionType { SingleVariant, MultiVariant }
Создаем перечисление Direction для навигации по вопросам:
public enum Direction { First, Prev, Next, Last }
В методе LoadDataBase заполняем объект DataSet данными из базы данных:
private void LoadDataBase() { SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Tests;Integrated Security=SSPI;");
SqlDataAdapter questAdapter = new SqlDataAdapter("select * from questions", conn); SqlDataAdapter variantsAdapter = new SqlDataAdapter("select * from variants", conn); conn.Open(); //Заполняем таблицу "Questions" данными из questAdapter questAdapter.Fill(dsTests.Tables["Questions"]); //Заполняем таблицу "Variants" данными из variantsAdapter variantsAdapter.Fill(dsTests.Tables["Variants"]); conn.Close(); }
Создаем два экземпляра класса Hashtable1):
// Экземпляр clientAnswers для хранения ответов пользователей Hashtable clientAnswers = new Hashtable(); // Экземпляр keys для хранения правильных ответов Hashtable keys = new Hashtable();
Создаем метод InitAnswersKeysTable, в котором связываем элементы экземпляров Hashtable с записями таблиц
private void InitAnswersKeysTable() { // Создаем цикл, длина которого равна числу записей в таблице "Questions" for(int i = 0; i < dsTests.Tables["Questions"].Rows.Count; i++) { // Выбираем записи из таблицы "Questions". DataRow drquestion = dsTests.Tables["Questions"].Rows[i];
// Выбираем записи из таблицы "Variants". DataRow[] drvariants = drquestion.GetChildRows (dsTests.Relations["QuestionsVariants"]); // Устанавливаем значение j, равное false для всех вариантов. bool[] answers = new bool[drvariants.Length]; for(int j = 0; j < answers.Length; j++) answers[j] = false;
// Добавляем значения к экземплярам Hashtable keys.Add(drquestion, drvariants); clientAnswers.Add(drquestion, answers); } }
Объект DataRow предназначен для просмотра и изменения содержимого отдельной записи в объекте DataTable. Для обращения к конкретной записи используется свойство Rows[i], где i - номер записи. Метод GetChildRows позволяет обращаться к дочерним записям, он принимает название отношения. Здесь мы фактически обращаемся к записям таблицы Variants. В экземпляре keys ключами будут записи из таблицы Questions, а значениями - элементы массива записей с вариантами ответов. В экземпляре clientAnswers ключами также будут записи из таблицы Questions, а значениями - элементы массива типа bool, зависящие от ответа пользователя.
В классе формы создаем экземпляр cmTest класса CurrencyManager для перемещения по записям:
CurrencyManager cmTest = null;
В методе InitDefaultSettings определяем настройки по умолчанию:
private void InitDefaultSettings() { //В свойство Tag каждой кнопки помещаем константу из перечисления Direction btnFirst.Tag = Direction.First; btnPrev.Tag = Direction.Prev; btnNext.Tag = Direction.Next; btnLast.Tag = Direction.Last;
//Для всех кнопок будет один обработчик btnFirst_Click btnFirst.Click += new EventHandler(btnFirst_Click); btnPrev.Click += new EventHandler(btnFirst_Click); btnNext.Click += new EventHandler(btnFirst_Click); btnLast.Click += new EventHandler(btnFirst_Click); //Вызываем метод LoadDataBase(); //Определяем действия для случая, //если нет записей в таблице "Questions" if(dsTests.Tables["Questions"].Rows.Count == 0) { txtQuestion.Text = "Нет данных о вопросах"; btnFirst.Enabled= false; btnPrev.Enabled= false; btnNext.Enabled= false; btnLast.Enabled= false; btnCheck.Enabled= false; } else { //Вызываем метод.
InitAnswersKeysTable(); //Связываем эземпляр cmTest с содержимым таблицы "Questions" cmTest = (CurrencyManager)this.BindingContext[dsTests, "Questions"]; //Определяем обработчик для события cmTest.PositionChanged += new EventHandler(cmTest_PositionChanged); ShowQuestion(dsTests.Tables["questions"].Rows[0]); //Включаем доступность кнопок ">" и ">>" btnFirst.Enabled= false; btnPrev.Enabled= false; btnNext.Enabled= true; btnLast.Enabled= true; } }
Создаем метод cmTest_PositionChanged, для обработки события PositionChanged объекта cmTest, в котором определяем доступность кнопок навигации:
private void cmTest_PositionChanged(object sender, EventArgs e) { if (cmTest.Position == 0) { btnPrev.Enabled = false; btnFirst.Enabled = false; btnNext.Enabled = true; btnLast.Enabled = true; } else if(cmTest.Position == dsTests.Tables["questions"].Rows.Count - 1) { btnNext.Enabled = false; btnLast.Enabled = false; btnPrev.Enabled = true; btnFirst.Enabled = true; } else { btnPrev.Enabled = true; btnFirst.Enabled = true; btnNext.Enabled = true; btnLast.Enabled = true; } }
Создаем метод btnFirst_Click - общий обработчик для всех кнопок навигации:
private void btnFirst_Click(object sender, EventArgs e) { Button btn = (Button)sender; Direction direction = (Direction)btn.Tag;
switch (direction) { case Direction.First: cmTest.Position = 0; break; case Direction.Prev: --cmTest.Position; break; case Direction.Next: ++cmTest.Position; break; case Direction.Last: cmTest.Position = dsTests.Tables["questions"].Rows.Count - 1; break; }
int rowIndex = cmTest.Position; //Вызываем метод ShowQuestion, который выводит вопросы на форму ShowQuestion(dsTests.Tables["questions"].Rows[rowIndex]); }
Вызываем метод InitDefaultSettings в конструкторе формы:
public Form1() { InitializeComponent(); InitDefaultSettings(); }
В методе ShowQuestion выводим вопрос на форму:
private void ShowQuestion(DataRow drquestion) { txtQuestion.Text = drquestion["question"].ToString(); //Вызываем метод ShowVariants, который выводит на форму варианты ответов ShowVariants(drquestion); }
В методе ShowVariants в зависимости от типа вопроса формируется набор элементов RadioButton или CheckBox c вариантами ответов, который затем выводится в элемент gbVariants:
private void ShowVariants(DataRow question) { //Удаляем все элементы из GroupBox gbVariants.Controls.Clear(); //Снова создаем экземпляр childVariants //для обращения к записям таблицы "Variants" DataRow[] childVariants = question.GetChildRows (dsTests.Relations["QuestionsVariants"]);
//Определяем тип вопроса bool[] vars = (bool[])clientAnswers[question]; int i = 0; QuestionType questType = (QuestionType)question["questType"]; switch(questType) { //Если вопрос имеет всего один правильный вариант, //на форме будут созданы элементы Radiobutton case QuestionType.SingleVariant: foreach(DataRow childVariant in childVariants) { RadioButton rb = new RadioButton(); #region Ищем выбранный ответ в таблице ответов bool selectedAnswer = (bool)vars[i++]; rb.Checked = selectedAnswer; #endregion //Определяем свойства созданного элемента RadioButton rb.Text = childVariant["variant"].ToString(); rb.Tag = childVariant; rb.CheckedChanged += new EventHandler(rb_CheckedChanged); int y = (gbVariants.Controls.Count == 0)?20: ((RadioButton)gbVariants.Controls[gbVariants.Controls.Count - 1]).Bottom + 2; //Определяем размеры создаваемых элементов RadioButton //500 - ширина в пикселях, rb.Height+5 - высота rb.Size = new Size(500, rb.Height+5); rb.Location = new Point(10, y); gbVariants.Controls.Add(rb); } break; //Если вопрос имеет несколько правильных вариантов, //на форме будут созданы элементы CheckBox case QuestionType.MultiVariant: foreach(DataRow childVariant in childVariants) { CheckBox chb = new CheckBox(); #region Ищем выбранный ответ в таблице ответов bool selectedAnswer = (bool)vars[i++]; chb.Checked = selectedAnswer; #endregion //Определяем свойства созданного элемента RadioButton chb.Text = childVariant["variant"].ToString(); chb.Tag = childVariant; chb.CheckedChanged += new EventHandler(chb_CheckedChanged); int y = (gbVariants.Controls.Count == 0)?20: ((CheckBox)gbVariants.Controls[gbVariants.Controls.Count - 1]).Bottom + 2; //Определяем размеры создаваемых элементов RadioButton //500 - ширина в пикселях, chb.Height+5 - высота chb.Size = new Size( 500, chb.Height+5); chb.Location = new Point(10, y); gbVariants.Controls.Add(chb); } break; } }
Когда пользователь отметит галочкой элементы CheckBox или выберет RadioButton, будет происходить событие CheckedChanged. В этом событии мы фиксируем положение отмеченного элемента - при возврате к решенному вопросу пользователь будет видеть свои ответы:
private void rb_CheckedChanged(object sender, EventArgs e) { RadioButton rb = (RadioButton)sender; if(!rb.Checked) return; //Создаем объект drvariant класса DataRow, с которым связываем //свойство Tag элемента RadioButton DataRow drvariant = (DataRow)rb.Tag; //Отмечаем текущее положение объекта cmTest int questIndex = cmTest.Position; DataRow drquestion = dsTests.Tables["Questions"].Rows[questIndex]; int answIndex = gbVariants.Controls.IndexOf(rb); //Выводим элемент RadioButton отмеченным, если он уже был выбран bool[] answers = (bool[])clientAnswers[drquestion]; for(int i = 0; i < answers.Length; i++) { if(i == answIndex) answers[i] = rb.Checked; else answers[i] = !rb.Checked; } }
private void chb_CheckedChanged(object sender, EventArgs e) { CheckBox chb = (CheckBox)sender; //Создаем объект drvariant класса DataRow, с которым связываем //свойство Tag элемента CheckBox DataRow drvariant = (DataRow)chb.Tag; //Отмечаем текущее положение объекта cmTest int rowIndex = cmTest.Position; DataRow drquestion = dsTests.Tables["Questions"].Rows[rowIndex]; int answIndex = gbVariants.Controls.IndexOf(chb); //Выводим элемент CheckBox отмеченным, если он уже был выбран bool[] answers = (bool[])clientAnswers[drquestion]; for(int i = 0; i< answers.Length; i++) { if (i == answIndex) { answers[i] = chb.Checked; break; } } }
Переключаемся в режим дизайна, щелкаем на кнопке "Результат" - в обработчике события подсчитываем количество правильных ответов:
private void btnCheck_Click(object sender, System.EventArgs e) { //Создаем счетчик double counter = 0; //Перебираем все ключи экземпляра key класса Hashtable, //в котором хранятся ответы пользователя foreach(object key in keys.Keys) { bool flag = true; DataRow[] drvariants = (DataRow[])keys[key]; bool[] answers = (bool[])clientAnswers[key]; int i = 0; foreach(DataRow variant in drvariants) { if(((bool)variant["isRight"]) == answers[i++]) continue; else { flag = false; break; } } if (flag) ++counter; } //Делим количество правильных ответов на общее число ответов, //результат умножаем на 100 int result = (int)(counter / dsTests.Tables["questions"].Rows.Count * 100); MessageBox.Show(String.Format("Вы ответили правильно на {0}% вопросов.", result), "Результат тестирования", MessageBoxButtons.OK, MessageBoxIcon.Information); }
Запускаем приложение (рис. 8.6). Мы рассмотрели простейший случай подсчета результатов - конечно же, в реальных приложениях кнопка "Результат" не может быть доступна в любой момент времени. Впрочем, это достаточно легко изменить.
увеличить изображение
Рис. 8.6. Готовое приложение Tests. А - вид формы с двумя правильными ответами, Б - вид формы с одним правильным ответом, В - результат тестирования
В программном обеспечении к курсу вы найдете приложение Tests (Code\Glava4\Tests).
Программное создание объектов
Еще в первой лекции мы узнали, что все объекты вкладки Data или все объекты ADO .NET можно создавать программно. Скопируйте папку приложения Tests и назовите ее "ProgrammTests". Открываем проект, удаляем объект DataSet с панели компонент формы. Для создания этого объекта программно достаточно следующей строчки кода:
DataSet dsTests = new DataSet();
Поскольку созданный экземпляр dsTests будет использоваться в нескольких методах, эту строку следует написать в классе формы. Здесь dsTests - это название объекта DataSet, которое мы затем используем в коде - это известно нам с первой лекции. Среда Visual Studio .NET однако, сгенерировала следующий фрагмент кода (его можно найти в исходном проекте "Tests"):
this.dsTests = new System.Data.DataSet(); ... // // dsTests // this.dsTests.DataSetName = "NewDataSet"; this.dsTests.Locale = new System.Globalization.CultureInfo("ru-RU");
Как всегда, автоматический код избыточен - здесь указывается культура ru-RU. Дело в том, что у меня установлена русская версия Windows XP и соответствующие региональные настройки.
Среда определила еще свойство DataSetName = "NewDataSet", эквивалентное описание будет иметь следующий вид:
DataSet dsTests = new DataSet("NewDataSet");
Что же это за еще одно название объекта DataSet? Свойство DataSetName используется для работы с XSD-схемами, пока про это название (до лекции 10) мы можем просто забыть.
Создадим теперь объект DataTable для таблицы Questions:
DataTable dtQuestions = dsTests.Tables.Add("Questions"); //Или //DataTable dtQuestions = new DataTable("Questions"); //dsTests.Tables.Add(dtQuestions);
Здесь мы создаем экземпляр dtQuestions объекта DataTable, затем вызываем метод Add свойства Tables объекта dsTests, которому передаем название таблицы Questions. Далее создаем поля в объекте dtQuestions:
DataColumn dсQuestID = dtQuestions.Columns.Add("questID", typeof(Int32)); dсQuestID.Unique = true; DataColumn dcQuestion = dtQuestions.Columns.Add("question"); DataColumn dcQuestType = dtQuestions.Columns.Add("questType", typeof(Int32));
Мы создаем поля, нужные для отражения соответствующих столбцов в таблице Questions. Перегруженный метод Add свойства Columns объекта dtQuestions позволяет задавать название столбца и его тип данных (рис. 8.7):
увеличить изображение
Рис. 8.7. Создание поля
Свойство Unique указывает, что в этом поле не должно быть повторяющихся значений, оно должно быть уникальным (здесь - поле questID является первичным ключом таблицы Questions).
Точно так же создаем объект DataTable для таблицы Variants и соответствующие поля:
//Cоздаем таблицу "Variants" DataTable dtVariants = dsTests.Tables.Add("Variants");
//Заполняем поля таблицы "Variants" DataColumn dcID = dtVariants.Columns.Add("id", typeof(Int32)); dcID.Unique = true; dcID.AutoIncrement = true; DataColumn dcVariantQuestID = dtVariants.Columns.Add("questID", typeof(Int32)); DataColumn dcVariant = dtVariants.Columns.Add("variant"); DataColumn dcIsRight = dtVariants.Columns.Add("isRight", typeof(Boolean));
Здесь мы дополнительно установили свойству AutoIncrement объекта dcID значение true. Свойство AutoIncrement (Счетчик) позволяет создать счетчик для поля, аналогичный типу данных "Счетчик" в Microsoft Access.
Теперь приступим к созданию связи между таблицами. В базе данных Microsoft SQL Tests между родительской таблицей Questions и дочерней Variants была установлена связь по полю questID, которое было в обеих таблицах. При программном создании объектов для поля questID таблицы Questions был создан объект dсQuestID, для этого же поля таблицы Variants создан объект dcVariantQuestID. В коде создание отношения между таблицами будет иметь следующий вид:
DataRelation drQuestionsVariants = new DataRelation("QuestionsVariants", dсQuestID, dcVariantQuestID); dsTests.Relations.Add(drQuestionsVariants);
Здесь в конструкторе drQuestionsVariants - название экземпляра объекта (класса) DataRelation, а QuestionsVariants - свойство relationName - название связи, которая будет содержаться в объекте drQuestionsVariants.
Другими словами, drQuestionsVariants - название экземпляра DataRelation, которое мы будем использовать в коде, а свойство relationName - всего лишь название отражаемой связи, которую можно удалить или переименовать.
Итак, мы создали все объекты для отображения таблиц, полей и даже связей между таблицами.
Теперь нам осталось определить некоторые свойства таблиц, называемые ограничениями. Свойство ограничения (Constraint) объекта DataTable бывает двух типов - UniqueConstraint и ForeignKeyConstraint. Свойство UniqueConstraint определяет первичный ключ таблицы, например, в таблице Questions ключевым полем является questID. Объект dсQuestID представляет это поле:
DataColumn dсQuestID = dtQuestions.Columns.Add("questID", typeof(Int32));
Ограничение UniqueConstraint, налагаемое на объект dсQuestID, запрещает появление дублированных строк:
UniqueConstraint UC_dtQuestions = new UniqueConstraint(dсQuestID); dtQuestions.Constraints.Add(UC_dtQuestions);
Однако при создании объекта dсQuestID мы ведь уже определяли его уникальность:
dсQuestID.Unique = true;
Действительно, последняя строка представляет собой неявный способ задания ограничения UniqueConstraint. Если мы уже определили уникальное поле или поля, используя свойство Unique, задавать ограничение UniqueConstraint не нужно.
Второе ограничение - ForeignKeyConstraint - определяет, как должны себя вести дочерние записи при изменении родительских записей и наоборот. Конечно, интерфейс нашего приложения вообще не подразумевает внесение изменений, но ADO .NET требует точного описания объектов для управления ими. Ограничение ForeignKeyConstraint содержит следующие три правила:
UpdateRule - применяется при изменении родительской строки;DeleteRule - применяется при удалении родительской строки;AcceptRejectRule - применяется при вызове метода AcceptChanges объекта DataTable, для которого определено ограничение.
Для этих правил могут применяться следующие значения:
Cascade - каскадное обновление связанных записей;None - изменения в родительской таблице не отражаются в дочерних записях; SetDefault - полю внешнего ключа в дочерних записях присваивается значение, заданное в свойстве DefaultValue этого поля; SetNull - полю внешнего ключа в дочерних записях присваивается значение Null.
Значением по умолчанию для правил UpdateRule и DeleteRule является Cascade, для правила AcceptRejectRule - None. Дополнительно, правило AcceptRejectRule принимает значения только Cascade или None.
Создадим ограничение для связи QuestionsVariants:
ForeignKeyConstraint FK_QuestionsVariants = new ForeignKeyConstraint(dtQuestions.Columns["questID"], dtVariants.Columns["questID"]); dtVariants.Constraints.Add(FK_QuestionsVariants);
Здесь задается вначале родительская колонка, а затем дочерняя (рис. 8.8). Добавлять созданное ограничение следует к объекту DataTable, представляющему дочернюю таблицу (в данном случае - объект dtVariants)
увеличить изображение
Рис. 8.8. Создание ограничения FK_QuestionsVariants
Этот фрагмент кода оставляет значения правил UpdateRule, DeleteRule и AcceptRejectRule заданными по умолчанию, т.е. Cascade и None, что соответствует значениям настройки с помощью мастера Relation (рис. 8.9):
увеличить изображение
Рис. 8.9. Мастер Relation и соответствующий фрагмент кода
Если бы нам потребовалось задать значение одному из правил, отличное по умолчанию, мы бы просто применили другой вариант конструктора (рис. 8.10):
увеличить изображение
Рис. 8.10. В этом конструкторе можно задать значения правил RejectRule, DeleteRule и UpdateRule
Полностью2) метод LoadDataBase в проекте ProgrammTests будет выглядеть так:
private void LoadDataBase() { SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Tests;Integrated Security=SSPI;"); SqlDataAdapter questAdapter = new SqlDataAdapter("select * from questions", conn); SqlDataAdapter variantsAdapter = new SqlDataAdapter("select * from variants", conn); //dsTests.EnforceConstraints = true; //Cоздаем таблицу "Questions" DataTable dtQuestions = dsTests.Tables.Add("Questions"); //Или //DataTable dtQuestions = new DataTable("Questions"); //dsTests.Tables.Add(dtQuestions); //Заполняем поля таблицы "Questions" DataColumn dсQuestID = dtQuestions.Columns.Add("questID", typeof(Int32)); dсQuestID.Unique = true; //Или //UniqueConstraint UC_dtQuestions = new UniqueConstraint(dсQuestID); //dtQuestions.Constraints.Add(UC_dtQuestions); DataColumn dcQuestion = dtQuestions.Columns.Add("question"); DataColumn dcQuestType = dtQuestions.Columns.Add ("questType", typeof(Int32)); //Cоздаем таблицу "Variants" DataTable dtVariants = dsTests.Tables.Add("Variants"); //Заполняем поля таблицы "Variants" DataColumn dcID = dtVariants.Columns.Add("id", typeof(Int32)); dcID.Unique = true; dcID.AutoIncrement = true; DataColumn dcVariantQuestID = dtVariants.Columns.Add("questID", typeof(Int32)); DataColumn dcVariant = dtVariants.Columns.Add("variant"); DataColumn dcIsRight = dtVariants.Columns.Add("isRight", typeof(Boolean)); //Создаем ограничение ForeignKeyConstraint FK_QuestionsVariants = new ForeignKeyConstraint(dtQuestions.Columns["questID"], dtVariants.Columns["questID"]); dtVariants.Constraints.Add(FK_QuestionsVariants); //Создаем отношение DataRelation drQuestionsVariants = new DataRelation("QuestionsVariants", dсQuestID, dcVariantQuestID); dsTests.Relations.Add(drQuestionsVariants); conn.Open(); //Заполняем таблицу "Questions" данными из questAdapter questAdapter.Fill(dsTests.Tables["Questions"]); //Заполняем таблицу "Variants" данными из variantsAdapter variantsAdapter.Fill(dsTests.Tables["Variants"]); conn.Close(); }
Обратим внимание на несколько деталей этого метода. Значение true свойства EnforceConstraints объекта dsTests разрешает использование ограничений. По умолчанию в созданном объекте DataSet это свойство и так принимает значение true, поэтому этот фрагмент кода закомментирован. Следует иметь в виду, что для снятия всех ограничений достаточно установить свойству EnforceConstraints значение false. Ограничение FK_QuestionsVariants создается перед отношением drQuestionsVariants - вначале следует создавать ограничения, а затем определять отношения. Соединение conn открывается как можно позже - непосредственно перед заполнением данными объектов DataAdapter, - и тут же закрывается. Открыть его в начале метода и закрыть в конце было бы нерациональным.
В программном обеспечении к курсу вы найдете приложение Programm Tests (Code\Glava4\ProgrammTests).
События объекта DataTable
Объект DataTable содержит ряд событий, которые могут применяться для слежения за происходящими изменениями. Наиболее часто используются следующие события:
ColumnChanged - наступает после изменения содержимого поля таблицы;ColumnChanging - происходит в течение редактирования содержимого поля таблицы;RowChanged - наступает после изменения объекта DataRow (записи);RowChanging - происходит в течение редактирования объекта DataRow;RowDeleted - наступает после удаления объекта DataRow;RowDeleting - происходит при удалении объекта DataRow.
Скопируйте папку приложения RowVersion, назовите ее "Data TableEvents". В конструкторе формы добавим обработку четырех событий объекта DataTable:
public Form1() { ... dtTours.RowChanging += new DataRowChangeEventHandler(dtTours_RowChanging); dtTours.RowChanged += new DataRowChangeEventHandler(dtTours_RowChanged); dtTours.RowDeleting += new DataRowChangeEventHandler(dtTours_RowDeleting); dtTours.RowDeleted += new DataRowChangeEventHandler(dtTours_RowDeleted); }
В соответствующих методах просто выводим сообщение в текстовое поле:
private void dtTours_RowChanging(object sender, DataRowChangeEventArgs e) { rtbReport.Text += String.Format("Событие - изменение записи\n", e.Row["Название"]); }
private void dtTours_RowChanged(object sender, DataRowChangeEventArgs e) { rtbReport.Text += "\nСобытие - запись изменена\n"; }
private void dtTours_RowDeleting(object sender, DataRowChangeEventArgs e) { rtbReport.Text += String.Format("Событие - удаление записи\n", e.Row["Название"]); }
private void dtTours_RowDeleted(object sender, DataRowChangeEventArgs e) { rtbReport.Text += "\nСобытие - запись удалена\n"; }
Запускаем приложение. Нажимаем кнопку "Begin Edit", затем "End Edit" - происходят события RowChanging и RowChanged. Удаляем запись - происходят события RowDeleting и RowDeleted (рис. 8.21).
Рис. 8.21. Проект "DataTableEvents"
В обработчиках событий можно добавить соответствующие действия, например, подтверждение изменения (RowChanging) или удаления (RowDeleting).
В программном обеспечении к курсу вы найдете приложение DataTable Events (Code\Glava4\ DataTableEvents).
|
|
|
1)
Дополнительные сведения об этом классе см. в конце Лекции 9.
2)
Не забудьте в классе формы создать объект DataSet: DataSet dsTests = new DataSet();
Создание столбцов, основанных на выражении
При создании базы данных не следует помещать в нее значения, которые могут быть получены из уже имеющихся данных. В первой лекции мы создали базу данных BDTur_firm.mdb, в которой есть таблица "Туры". В этой таблице имеется поле "цена", где указывается стоимость туров. На практике может понадобиться значение цены, выраженное в различных валютах, значение с учетом налогов, значение со скидкой и т.п. Для каждого конкретного случая можно получить дополнительное поле, не вводя его в саму базу данных. Технология ADO .NET позволяет создавать объекты DataColumn, основанные на выражении. Создайте новое Windows-приложение и назовите его CustomExpression. Перетаскиваем на форму элемент управления DataGrid, свойству Dock устанавливаем значение Fill. Переходим в код формы, подключаем пространство имен:
using System.Data.OleDb;
В классе формы определяем строки CommandText и ConnectionString:
string commandText = "SELECT Информация, [Код тура], Название, Цена FROM Туры"; string connectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""D:\Uchebnik\Code\Glava1\BDTur_firm.mdb"";User ID=Admin; Jet OLEDB:Encrypt Database=False";
В конструкторе формы программно создаем все объекты для вывода таблицы "Туры" в элемент управления DataGrid:
public Form1() { InitializeComponent(); OleDbConnection conn = new OleDbConnection (connectionString); OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = conn; myCommand.CommandText = commandText; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = myCommand; DataSet dsTours = new DataSet(); DataTable dtTours = dsTours.Tables.Add("Туры"); DataColumn dсIDtour = dtTours.Columns.Add("Код тура", typeof(Int32)); dсIDtour.Unique = true; DataColumn dcName = dtTours.Columns.Add("Название"); DataColumn dcPrice = dtTours.Columns.Add("Цена", typeof(Decimal)); DataColumn dcInformation = dtTours.Columns.Add("Информация"); conn.Open(); dataAdapter.Fill(dsTours.Tables["Туры"]); conn.Close(); dataGrid1.DataSource = dsTours.Tables["Туры"].DefaultView; }
Запускаем приложение (рис. 8.11):
увеличить изображение
Рис. 8.11. Вывод содержимого таблицы "Туры"
Теперь добавим два объекта DataColumn, в которых будет вычисляться налог и скидка, после добавления объекта dcPrice:
... DataColumn dcPrice = dtTours.Columns.Add("Цена", typeof(Decimal)); DataColumn dcPriceNDS = dtTours.Columns.Add("Цена c НДС", typeof(Decimal)); dcPriceNDS.Expression = "Цена*0.15+Цена"; DataColumn dcPricewithDiscount = dtTours.Columns.Add("Цена cо скидкой", typeof(Decimal)); dcPricewithDiscount.Expression = "Цена-Цена*0.10"; ...
Свойство Expression созданного объекта DataColumn задает выражения для всех значений заданного поля (рис. 8.12):
увеличить изображение
Рис. 8.12. Значения полей "Цена с НДС" и "Цена со скидкой" получены в результате вычислений
Свойство Expression поддерживает также агрегатные функции, объединение строк, ссылки на родительские и дочерние таблицы.
В программном обеспечении к курсу вы найдете приложение Custom Expression (Code\Glava4\CustomExpression).
Свойство RowState
При работе с данными приходится постоянно вносить изменения в записи - добавлять, редактировать или удалять. Объект DataRow обладает свойством RowState, позволяющим отслеживать текущий статус строки. Создайте новое приложение, назовите его "RowState". Перетаскиваем на форму элементы RichTextBox, Splitter, DataGrid, устанавливаем для них следующие свойства:
| Dock | Bottom |
| Text |
| Dock | Bottom |
| Dock | Fill |
В конструкторе формы мы создаем всего одно поле, затем одну запись, статус которой будем отслеживать:
public Form1() { InitializeComponent(); DataTable dtTours = new DataTable("Туры"); DataColumn IDtour = new DataColumn("Код тура", typeof(Int32)); dtTours.Columns.Add(IDtour); dataGrid1.DataSource = dtTours; DataRow myRow; // Создаем новую, отсоединенную запись myRow = dtTours.NewRow(); richTextBox1.Text += Convert.ToString("Новая запись: " + myRow.RowState); //Добавляем запись в объект DataTable dtTours.Rows.Add(myRow); richTextBox1.Text += Convert.ToString("\nДобавление записи: " + myRow.RowState); //Принимаем все изменения в объекте DataTable dtTours.AcceptChanges(); richTextBox1.Text += Convert.ToString("\nМетод AcceptChanges: " + myRow.RowState); //Редактируем запись myRow["Код тура"] = 1; richTextBox1.Text += Convert.ToString("\nРедактирование строки: " + myRow.RowState); //Удаляем строку myRow.Delete(); richTextBox1.Text += Convert.ToString("\nУдаление: " + myRow.RowState); //Отменяем все изменения в объекте DataTable dtTours.RejectChanges(); richTextBox1.Text += Convert.ToString("\nМетод RejectChanges: " + myRow.RowState); }
Запускаем приложение. В текстовое поле выводится статус записи myRow (рис. 8.18):
Рис. 8.18. Приложение RowState
Значение Detached означает, что запись не относится к объекту DataTable. После добавления ее статус изменяется на Added - теперь она существует в объекте DataTable, но ее нет в базе данных. Конечно, здесь мы не рассматривали взаимодействие с источником записей, но это же значение будет у записей, добавляемых в DataGrid после вывода данных из базы при наличии подключения. Вызывая метод AcceptChanges объекта DataTable, мы принимаем все изменения, поэтому статус DataRow изменяется на Unchanged - теперь запись считается "своей", она не была изменена после вызова метода. После вызова метода Delete запись помечается удаленной - она еще не полностью удалена, в случае отмены изменений статус будет восстановлен. Действительно, вызывая метод RejectChanges объекта DataTable, мы восстанавливаем запись до состояния Unchanged.
Свойство RowState всегда возвращает отдельное, доступное только для чтения значение. Это свойство используется для поиска записей, соответствующих заданному статусу, а также при передаче изменений в базу данных.
В программном обеспечении к курсу вы найдете приложение RowState (Code\Glava4\ RowState).
Свойство RowVersion
Свойство RowVersion предназначено для извлечения значения записи (объекта DataRow), зависящего от совершенных изменений. Возможны следующие версии записи:
Current - текущее значениеDefault - значение по умолчаниюOriginal - первоначальное значениеProposed - предполагаемое значение
Создайте новое приложение, назовите его "RowVersion". Свойству Size формы устанавливаем значение 500; 300. Из окна Toolbox перетаскиваем следующие элементы и устанавливаем их свойства:
| Dock | Bottom |
| Dock | Left |
| CaptionText | Туры |
| Dock | Left |
| Name | rtbReport |
| Dock | Fill |
| Text |
| Name | BtnBeginEdit |
| Location | 14; 21 |
| Text | Begin Edit |
| Name | btnEndEdit |
| Location | 106; 21 |
| Text | End Edit |
| Name | btnCancelEdit |
| Location | 198; 21 |
| Text | Cancel Edit |
| Name | btnDelete |
| Location | 290; 21 |
| Text | Удалить |
| Name | btnClearReport |
| Location | 382; 21 |
| Size | 96; 23 |
| Text | Очистить отчет |
Переходим к коду. В классе формы объявляем объекты DataRow и DataTable:
DataRow myRow; DataTable dtTours;
В конструкторе формы создаем запись, определяем источник данных для элемента DataGrid, а также отключаем его доступность:
public Form1() { InitializeComponent(); dtTours = new DataTable("Туры"); DataColumn IDtour = new DataColumn("Название", typeof(string)); dtTours.Columns.Add(IDtour); myRow = dtTours.NewRow(); dtTours.Rows.Add(myRow); myRow["Название"] = "Таиланд"; dataGrid1.DataSource = dtTours; dataGrid1.Enabled = false; }
Создаем метод TestRowVersion, в котором будет проверяться свойство RowVersion записи:
private void TestRowVersion() { if(myRow.HasVersion(DataRowVersion.Original)) rtbReport.Text += String.Format("Значение original: {0}\n", myRow["Название", DataRowVersion.Original]); if(myRow.HasVersion(DataRowVersion.Current)) rtbReport.Text += String.Format("Значение current: {0}\n", myRow["Название", DataRowVersion.Current]); if(myRow.HasVersion(DataRowVersion.Default)) rtbReport.Text += String.Format("Значение default: {0}\n", myRow["Название", DataRowVersion.Default]); if(myRow.HasVersion(DataRowVersion.Proposed)) rtbReport.Text += String.Format("Значение proposed: {0}\n", myRow["Название", DataRowVersion.Proposed]); }
Метод HasVersion позволяет определить, поддерживает ли объект myRow версию данных, указываемую в скобках. В случае подтверждения будет выполняться код оператора - выводится в элемент rtbReport соответствующее сообщение.
В обработчике кнопки "Begin Edit" вызываем метод BeginEdit, устанавливаем новое значение записи:
private void btnBeginEdit_Click(object sender, System.EventArgs e) { myRow.BeginEdit(); myRow["Название"] = "Франция"; rtbReport.Text += "BeginEdit\n"; TestRowVersion(); }
В обработчике кнопки "End Edit" завершаем редактирование записи:
private void btnEndEdit_Click(object sender, System.EventArgs e) { myRow.EndEdit(); rtbReport.Text += "EndEdit\n"; TestRowVersion(); }
В обработчике кнопки "Cancel Edit" отказываемся от внесенных изменений:
private void btnCancelEdit_Click(object sender, System.EventArgs e) { myRow.CancelEdit(); rtbReport.Text += "CancelEdit\n"; TestRowVersion(); }
В обработчике кнопки "Удалить" удаляем объект myRow:
private void btnDelete_Click(object sender, System.EventArgs e) { myRow.Delete(); rtbReport.Text += "Запись удалена\n"; TestRowVersion(); }
В обработчике кнопки "Очистить отчет" просто удаляем содержимое текстового поля:
private void btnClearReport_Click(object sender, System.EventArgs e) { this.rtbReport.Text = ""; }
Запускаем приложение. После нажатия кнопки "BeginEdit" мы начинаем редактирование записи, вводится новое значение - "Франция". Оно становится значением по умолчанию "Default" и предполагаемым "Proposed", значение "Таиланд" является текущим "Current" (рис. 8.19, А). Отменяем редактирование, нажимая кнопку "Cancel Edit". При этом значение "Таиланд" становится текущим "Current" и по умолчанию " Default" (рис. 8.19, Б). Снова начинаем редактирование - картина повторяется (рис. 8.19, В). На этот раз завершаем его, нажимая кнопку "End Edit" - новое значение "Франция" становится текущим "Current" и по умолчанию " Default" (рис. 8.19, Г).
Нажимаем кнопку "Удалить" - при этом удаляется сам объект myRow и дальнейшее изменение его з начений оказывается невозможным (рис. 8.19, Д).
увеличить изображение
Рис. 8.19. Приложение "RowVersion"
В программном обеспечении к курсу вы найдете приложение RowVersion (Code\Glava4\ RowVersion).
Подобная функциональность вряд может нас устроить в реальных приложениях - в самом деле, мы вставляем только заранее определенное значение "Франция". Скопируйте папку приложения RowVersion и назовите ее RowVersion2. Добавим на форму элементы "надпись" и "текстовое поле":
| location | 24; 16 |
| Size | 72; 24 |
| Text | Название |
| Name | txtmyRowName |
| location | 104; 16 |
| Size | 344; 20 |
| Text |
private void btnBeginEdit_Click(object sender, System.EventArgs e) { myRow.BeginEdit(); rtbReport.Text += "BeginEdit\n"; TestRowVersion(); txtmyRowName.Enabled = true; }
private void btnEndEdit_Click(object sender, System.EventArgs e) { myRow["Название"] = txtmyRowName.Text; myRow.EndEdit(); rtbReport.Text += "EndEdit\n"; TestRowVersion(); txtmyRowName.Enabled = false; }
private void btnCancelEdit_Click(object sender, System.EventArgs e) { myRow.CancelEdit(); rtbReport.Text += "CancelEdit\n"; TestRowVersion(); txtmyRowName.Enabled = false; }
Обработчик кнопки "Удалить" изменим так же - теперь будет удаляться текущий экземпляр myRow и тут же создаваться новый:
private void btnDelete_Click(object sender, System.EventArgs e) { myRow.Delete(); rtbReport.Text += "Запись удалена\n"; TestRowVersion(); myRow = dtTours.NewRow(); dtTours.Rows.Add(myRow); }
В конструкторе формы отключим доступность текстового поля:
public Form1() { ... txtmyRowName.Enabled = false; }
Запускаем приложение. Выполняя знакомую последовательность действий, получаем похожий отчет; теперь при удалении записи можно продолжить работу - объект myRow будет воссоздан (рис. 8.20):
Рис. 8.20. Приложение RowVersion2
В программном обеспечении к курсу вы найдете приложение RowVersion2 (Code\Glava4\ RowVersion2).
