Благодарности 21 страница

Запросы SQL

Далее перетащите два раза в окно приложения компонент SqlCommand. В результате будет создано два объекта, предназначенных для выполнения команд SQL.

Первый из них с идентификатором sqlCommand1 предназначен для выборки информации из таблицы Users. Ниже мы показали строку команды SQL, которую нужно записать в свойство CommandText:

SELECT id, login, password, access FROM dbo.Users

Объект sqlCommand2 содержит команду с параметрами, предназначенную для поиска в таблице Users строки пользователя с заданным идентификатором и паролем:

SELECT id, login, password, access FROM dbo.Users
WHERE (login = @login) AND (password = @password)

Вы можете подготовить эту команду с помощью мастера Query Builder (рис. 9-35).

Рис. 9-35. Подготовка команды для объекта sqlCommand2

Окно этого мастера появится на экране при редактировании свойства CommandText объекта sqlCommand2.

Здесь Вам нужно в окне Users отметить флажки всех столбцов таблицы.

Кроме того, в столбце Criteria задайте два условия для полей Login и Password:

= @login
= @password

Когда текст запроса SQL будет готов, щелкните кнопку OK чтобы закрыть окно мастера Query Builder.

Так как текст команды SQL ссылается на параметры @login и @password, нам нужно определить эти параметры, отредактировав свойство Parameters объекта sqlCommand2. Эта операция выполняется с помощью редактора SqlParameter Collection Editor (рис. 9-36).

Рис. 9-36. Свойства параметра @login

Каждый параметр имеет набор свойств. Эти свойства можно редактировать в правой части окна редактора.

Установите свойства параметра @login, как это показано на рис. 9-36. Установку свойства параметра @password мы показали на рис. 9-37.

Рис. 9-37. Свойства параметра @password

Как видите, отличия есть только в свойствах SourceColumn и ParameterName, задающих столбец таблицы и название параметра, соответственно.

Адаптер SqlDataAdapter

Для редактирования содержимого таблицы Users в приложении LoginApp мы будем использовать те же самую технологию, что и в ранее рассмотренном приложении SQLTestApp, основанную на использовании адаптера SqlDataAdapter в паре с элементом управления DataGrid.

Итак, перетащите значок компонента SqlDataAdapter в окно приложения LoginApp. При этом в приложении будет создан объект sqlDataAdapter1.

Во втором окне мастера адаптеров выберите то же самое соединение, что было использовано для объекта sqlConnection1. Настройте свойства адаптера аналогично тому, как Вы это делали для приложения SQLTestApp (рис. 9-38), но укажите в командах SQL таблицу Users.

Рис. 9-38. Настройка свойств адаптера sqlDataAdapter1

Кроме того, создайте набор данных DataSet, аналогичный набору данных из приложения SQLTestApp, в котором будет храниться содержимое таблицы Users. Отображение таблиц, задаваемое свойством TableMappings объекта sqlDataAdapter1, должно быть таким, как показано на рис. 9-39.

Рис. 9-39. Настройка отображения таблиц

Ссылка на созданный набор данных класса DataSet будет храниться в поле dataSet11.

Элемент управления DataGrid

Как мы уже говорили, элемент управления DataGrid будет использоваться в нашем приложении для просмотра и редактирования содержимого набора данных dataSet11. В этом наборе данных будет находиться содержимое таблицы Users, скопированной с сервера SQL.

Настраивая свойства элемента управления DataGrid, укажите в свойстве DataSource ссылку на объект dataSet11, а в свойстве DataMember — таблицу Users.

Для того чтобы снабдить отображаемую таблицу заголовком, задайте текст заголовка в свойстве CaptionText. Напомним, что шрифт заголовка можно изменить, редактируя свойство CaptionFont.

По умолчанию элемент управления DataGrid будет отображать все столбцы набора данных dataSet11, причем имена соответствующих столбцов набора данных будут использованы в качестве заголовков столбцов отображаемой таблицы.

Для того чтобы скрыть столбец id, который не содержит никакой информации, интересной для пользователя приложения, создайте стиль таблицы, отредактировав свойство TableStyles. Добавьте один стиль, как это показано на рис. 9-40.

Рис. 9-40. Добавление стиля таблицы

Укажите в свойстве MappingName имя таблицы Users.

Далее Вам нужно создать три стиля для каждого столбца таблицы, отредактировав свойство GridColumnStyles (рис. 9-41).

Рис. 9-41. Стиль для столбца идентификаторов пользователя

Здесь Вы должны указать привязку каждого столбца при помощи свойства MappingName, а также задать заголовок столбца, отредактировав свойство HeaderText. В результате получится таблица со столбцами Идентификатор, Пароль и Доступ (рис. 9-32).

Подключение пользователя

Согласно логике работы приложения LoginApp, когда пользователь щелкает кнопку Войти, выполняется проверка идентификатора и пароля пользователя. Если предъявленная парольная информация отсутствует в таблице Users, а также, если идентификатор и пароль не соответствуют друг другу, приложение выводит сообщение на экран с текстом «Доступ запрещен».

Когда идентификатор и пароль указан правильно, приложение проверяет код доступа пользователя. Как мы уже говорили, пользователю с кодом доступа 80 разрешается редактирование таблицы Users, а пользователям с другими кодами доступа — только просмотр этой таблицы.

Чтобы реализовать такое поведение, создайте для кнопки Войти следующий обработчик событий:

private void button1_Click(object sender, System.EventArgs e)
{
SqlDataReader myReader;

try
{
sqlConnection1.Open();
myReader = sqlCommand1.ExecuteReader();

if(! myReader.Read())
{
sqlConnection1.Close();
myReader.Close();
CreateAdminLogin();
}
else
{
myReader.Close();

sqlCommand2.Parameters[" @login" ].Value = LoginTextBox.Text;
sqlCommand2.Parameters[" @password" ].Value =
passwordTextBox.Text;

myReader = sqlCommand2.ExecuteReader();

if(! myReader.Read())
{
MessageBox.Show(" Доступ запрещен", " Ошибка");
myReader.Close();
sqlConnection1.Close();
return;
}

if(LoginTextBox.Text == myReader.GetString(1) & &
passwordTextBox.Text == myReader.GetString(2))
{
if(myReader.GetInt32(3)! = 80)
{
dataGrid1.ReadOnly = true;
button2.Enabled = false;
}
else
{
dataGrid1.ReadOnly = false;
button2.Enabled = true;
}

myReader.Close();

dataSet11.Clear();
sqlDataAdapter1.Fill(dataSet11);
}
else
{
MessageBox.Show(" Доступ запрещен", " Ошибка");
}
myReader.Close();
sqlConnection1.Close();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, " Ошибка");
}
}

Рассмотрим работу этого обработчика в деталях.

Прежде всего, заметим, что мы применили здесь класс SqlDataReader. Этот класс очень удобен для извлечения результатов запросов SQL в режиме последовательного чтения.

Получив управление, обработчик события button1_Click открывает соединение sqlConnection1, о котором мы говорили раньше:

sqlConnection1.Open();

Параметры этого соединения мы задавали во время проектирования нашего приложения.

Далее наш обработчик событий исполняет команду SQL, хранящуюся в объекте sqlCommand1:

SqlDataReader myReader;
…
myReader = sqlCommand1.ExecuteReader();

В результате выполнения метода ExecuteReader создается объект класса SqlDataReader, позволяющий последовательно прочитать все строки результата выполнения команды SQL.

Напомним, что эта команда выбирает все строки таблицы Users, возвращая столбцы id, login, password и access. Для выборки нужно использовать метод SqlDataReader.Read, вызывая его в цикле.

Когда все строки, полученные в результате выполнения команды SQL, будут получены, метод SqlDataReader.Read возвратит значение false. Мы используем это обстоятельство в самом начале работы обработчика событий button1_Click, для того чтобы определить, имеются ли в таблице Users какие-либо записи:

if(! myReader.Read())
{
sqlConnection1.Close();
myReader.Close();
CreateAdminLogin();
}
else
{
…
}

Если таблица Users пустая (как бывает при самом первом запуске приложения), обработчик закрывает соединение методом sqlConnection1.Close, а также закрывает объект SqlDataReader. После этого он вызывает метод CreateAdminLogin, предназначенный для создания самой первой учетной записи администратора в таблице Users. Исходный текст этого метода мы рассмотрим чуть позже.

Теперь мы рассмотрим ситуацию, когда в таблице Users уже имеются какие-то записи. Вы можете добавить новые записи в эту таблицу, например, вручную с помощью утилиты Microsoft SQL Server Enterprise Manager.

Прежде всего, в этом случае мы закрываем ненужный нам больше объект SqlDataReader:

myReader.Close();

Теперь нам нужно выполнить команду sqlCommand2, которая выбирает из таблицы Users учетные записи с заданным идентификатором пользователя и паролем. Перед выполнением этой команды необходимо подготовить параметры @login и @password, воспользовавшись для этого свойством Value контейнера sqlCommand2.Parameters:

sqlCommand2.Parameters[" @login" ].Value = LoginTextBox.Text;
sqlCommand2.Parameters[" @password" ].Value = passwordTextBox.Text;
myReader = sqlCommand2.ExecuteReader();

В результате выполнения этого запроса будет создан объект класса SqlDataReader. Если таблица Users не содержит ни одной записи с заданным идентификатором пользователя и паролем, то метод myReader.Read вернет значение false:

if(! myReader.Read())
{
MessageBox.Show(" Доступ запрещен", " Ошибка");
myReader.Close();
sqlConnection1.Close();
return;
}

Это означает, что был задан неправильный идентификатор или пароль пользователя. В этом случае наш обработчик событий выводит сообщение об отказе в доступе, закрывает объект myReader и соединение sqlConnection1, а затем возвращает управление.

Теперь мы рассмотрим случай, когда идентификатор и пароль был введен правильно.

Наша программа выполняет дополнительную проверку, сравнивая данные из ячеек строки, считанной методом myReader.Read, со значениями, введенными пользователем в полях Идентификатор и Пароль:

if(LoginTextBox.Text == myReader.GetString(1) & &
passwordTextBox.Text == myReader.GetString(2))
{
…
}
else
{
MessageBox.Show(" Доступ запрещен", " Ошибка");
}
myReader.Close();
sqlConnection1.Close();

Обратите внимание на то, как мы извлекаем значения ячеек прочитанной строки — для получения текстовой строки мы вызываем метод myReader.GetString, указывая ей в качестве параметра индекс столбца. Самый первый столбец нашей таблицы id имеет индекс 0, второй столбец Login — индекс 1 и т.д.

В табл. 9-2 мы перечислили некоторые методы класса SqlDataReader, предназначенные для извлечения из ячеек строки данных различных типов, встроенных в язык программирования C#.

Таблица 9-2. Методы класса SqlDataReader для извлечения содержимого ячеек строки стандартных типов данных

Метод GetValue позволяет получить данные в естественном формате, т.е. в том формате, в котором они хранятся в ячейке строки.

Специально для работы с серверами баз данных, такими как Microsoft SQL Server, этот набор был расширен методами, возвращающими данные в форматах этих серверов. Некоторые из этих методов перечислены в табл. 9-3.

Таблица 9-3. Методы класса SqlDataReader для извлечения содержимого ячеек строки типов данных серверов SQL

Вернемся к нашему приложению.

Итак, обработчик события button1_Click, создаваемого кнопкой Войти, определил, что пользователь ввел правильный идентификатор и пароль. Теперь ему нужно получить код доступа ткущего пользователя. Он извлекает этот код из столбца access, имеющего индекс 3, при помощи метода GetInt32:

if(myReader.GetInt32(3)! = 80)
{
dataGrid1.ReadOnly = true;
button2.Enabled = false;
}
else
{
dataGrid1.ReadOnly = false;
button2.Enabled = true;
}

Если код доступа не равен 80, обработчик события приравнивает свойству dataGrid1.ReadOnly значение true. Это запрещает редактирование строк, отображаемых в окне элемента управления DataGrid. Кроме этого, обработчик блокирует кнопку Обновить, приравнивая свойству button2.Enabled значение false.

В том случае, когда код доступа пользователя равен 80, обработчик события разрешает редактирование строк и разблокирует кнопку Обновить, изменяя соответствующим образом свойства dataGrid1.ReadOnly и button2.Enabled.

Завершив проверку прав доступа пользователя, обработчик событий закрывает объект myReader, т.к. он больше не понадобится:

myReader.Close();

Дальнейшие действия обработчика button1_Click заключаются в очистке содержимого набора данных dataSet11 с последующим его наполнением из таблицы Users сервера SQL:

dataSet11.Clear();
sqlDataAdapter1.Fill(dataSet11);

Эта работа выполняется с помощью адаптера sqlDataAdapter1. Соответствующие процедуры были подробно описаны в разделе «Приложение SQLTestApp» этой главы.

Обновление таблицы Users

Для обновления таблицы Users, отредактированной пользователем с кодом доступа 80, мы применяем адаптер sqlDataAdapter1 и метод Update. Вот исходный текст обработчика событий кнопки Обновить:

private void button2_Click(object sender, System.EventArgs e)
{
try
{
sqlDataAdapter1.Update(dataSet11);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, " Ошибка");
}
}

Точно такая же методика обновления применялась и для таблицы Contacts в упомянутом ранее приложении SQLTestApp.

Хранение дерева в базе данных

Очень часто возникает задача хранения информации, организованной иерархически. Реляционные базы данных, такие как Microsoft SQL Server, позволяют хранить информацию в виде иерархического дерева, причем для представления такого дерева нужна всего одна таблица.

Каждая строка таблицы, хранящей структуру дерева, соответствует одному узлу этого дерева. При этом в таблице должно быть, как минимум, два столбца. Первый из них должен содержать уникальные идентификаторы строки (т.е. идентификаторы узлов), а второй — идентификатор соответствующего родительского узла. Для корневого узла в качестве идентификатора родительского узла обычно используется нулевое или какое-либо другое особое значение.

Для демонстрации способа хранения дерева в базе данных, а также для того чтобы на конкретном примере изучить некоторые новые для нас методы работы с базами данных в приложениях C#, разработали приложение ArticlesApp. Это приложение будет подробно рассмотрено в следующем разделе.

Приложение ArticlesApp

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

Главное окно приложения ArticlesApp показано на рис. 9-42.

Рис. 9-42. Главное окно приложения ArticlesApp

В левой части этого окна имеется дерево, созданное с использованием элемента управления TreeView. Окно этого дерева отображает заголовки статей, а также так называемые веса сортировки заголовков, указанные в круглых скобках.

Заметим, что сразу после запуска приложения, когда в базе данных нет ни одной записи, дерево заголовков не содержит ни одного элемента. Если щелкнуть окно дерева правой клавишей мыши, на экране появится контекстное меню со строками Add, Delete и Edit.

Выбор строки Add приведет к тому, что на экране появится диалоговое окно, показанное на рис. 9-43.

Рис. 9-43. Добавление новой или редактирование существующей статьи

При помощи этого окна можно добавить в базу данных новую статью, определив для нее заголовок, тело и вес сортировки.

Если в дереве нет ни одного элемента, то при первом использовании строки Add контекстного меню в дерево будет добавлен корневой элемент.

Для того чтобы добавить в дерево дочерний элемент, нужно вначале выделить левой клавишей мыши заголовок родительского элемента, а потом, щелкнув этот заголовок правой клавишей мыши, выбрать из контекстного меню строку Add.

Редактирование любого элемента выполняется аналогично. Для выполнения этой операции нужно выделить элемент, а затем, щелкнув его правой клавишей мыши, выбрать из контекстного меню строку Edit.

С помощью строки Delete можно удалить элемент дерева. Заметим, что программа удаляет только элементы, не имеющие дочерних элементов. Попытки удалить элемент с дочерними элементами наше приложение игнорирует.

Для чего нужен вес сортировки?

Мы используем его для управления порядком размещения статей, находящихся на одном уровне иерархии. Чем этот вес меньше, тем статья располагается выше в окне дерева. Использование какой-либо другой сортировки, например, сортировки по алфавиту, для решения задачи упорядочивания заголовков статей неэффективно, т.к. только тот, кто создает базу данных статей, знает, как нужно располагать заголовки.

База данных Articles

Прежде чем создавать проект приложения ArticlesApp, подготовим базу данных Articles. В этой базе нам нужно создать две таблицы и три хранимые процедуры.

Таблица Tree

Таблица Tree предназначена для хранения структуры дерева. В ней необходимо создать четыре столбца с именами id, parent_id, title и weight. Столбец id должен быть первичным ключом.

Вот сценарий SQL, при помощи которого можно создать таблицу Tree:

CREATE TABLE [dbo].[Tree] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[parent_id] [int] NOT NULL,
[title] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL,
[weight] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tree] WITH NOCHECK ADD
CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

Здесь столбец id хранит идентификаторы узлов дерева, а столбец parent_id — идентификаторы родительских узлов. Таким образом, вместе с каждым узлом хранится идентификатор его родительского узла.

Поля title и weight предназначены, соответственно, для хранения заголовка статьи и веса сортировки, назначенного этой статье.

Таблица Documents

Мы могли бы хранить тексты документов непосредственно в таблице Tree, однако это привело бы к неэффективному расходованию памяти.

В самом деле, при отображении дерева нам фактически нужно загрузить в память все содержимое таблицы Tree. Однако в каждый момент времени мы просматриваем или редактируем только одну статью, поэтому нет никакой необходимости загружать эти данные в память вместе со структурой дерева.

Для хранения текстов статей мы создали отдельную таблицу Documents, содержащую столбцы id, document и tree_id. Первый из этих столбцов является ключевым:

CREATE TABLE [dbo].[Documents] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[document] [varchar] (5000) COLLATE Cyrillic_General_CI_AS NOT NULL,
[tree_id] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Documents] WITH NOCHECK ADD
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

В столбце id таблицы Documents хранятся уникальные идентификаторы статей, которые напрямую не используются в нашем приложении.

Столбец tree_id хранит идентификатор узла дерева, соответствующего данной статье. Этот столбец является внешним ключом для таблицы Tree.

И, наконец, столбец document хранит текст самой статьи.

Хранимая процедура sp_InsertDocument

Часть работы с базой данных наше приложение будет выполнять при помощи команд SQL, оформленных в виде объектов класса SqlCommand. Однако на примере этого приложения мы покажем Вам как можно работать с хранимыми процедурами сервера Microsoft SQL Server.

Хранимая процедура sp_InsertDocument предназначена для добавления нового документа в таблицу Documents:

CREATE PROCEDURE [dbo].[sp_InsertDocument]
@tree_id AS INT,
@document AS VARCHAR(2000)
AS

INSERT INTO dbo.Documents(tree_id, document) VALUES (@tree_id, @document);
RETURN @@identity
GO

Этой процедуре необходимо передать два параметра @tree_id и @document. Первый из этих параметров предназначен для передачи идентификатора узла, в который добавляется статья, а второй — для передачи текста этой статьи.

Процедура возвращает идентификатор добавленной строки @@identity.

Хранимая процедура sp_ InsertNode

Хранимая процедура sp_ InsertNode вставляет новую строку в таблицу Tree, возвращая идентификатор новой строки:

CREATE PROCEDURE [dbo].[sp_InsertNode]
@parent_id AS INT,
@title AS VARCHAR(50),
@weight AS INT
AS

INSERT INTO dbo.Tree(parent_id, title, weight) VALUES (@parent_id, @title, @weight);
RETURN @@identity
GO

Этой процедуре нужно передать через входные параметры идентификатор родительского узла @parent_id (равный 0 для корневого узла), заголовок статьи @title и вес сортировки @weight.

Хранимая процедура sp_ UpdateDocument

При помощи хранимой процедуры sp_UpdateDocument наше приложение обновляет тексты статей, хранящиеся в таблице Documents:

CREATE PROCEDURE [dbo].[sp_UpdateDocument]
@tree_id AS INT,
@document AS VARCHAR(2000)
AS

UPDATE dbo.Documents SET document = @document WHERE (tree_id = @tree_id)
GO

В качестве параметра этой хранимой процедуре необходимо передать идентификатор узла @tree_id обновляемой статьи, а также текст статьи @document.

Создание проекта приложения ArticlesApp

В окно нашего приложения нужно поместить элемент управления TreeView, разделитель Splitter, а также редактор текста RichTextBox. Дерево TreeView должно занимать левую часть окна, а редактор RichTextBox — правую. Соответствующие рекомендации по настройке свойств элементов управления TreeView и RichTextBox мы приводили в 7 главе.

Помимо только что перечисленных элементов управления в проект приложения ArticlesApp нужно будет добавить множество других программных компонентов, предназначенных главным образом для работы с сервером базы данных (рис. 9-44).

Рис. 9-44. Компоненты приложения ArticlesApp

Рассмотрим порядок добавления этих компонент, настройку свойств, а также дополнительный программный код, который Вы должны добавить для наделения нашего приложения нужной функциональностью.

Соединение с базой данных

Прежде всего, обеспечьте приложение возможностью соединения с базой данных Articles. С этой целью добавьте программный компонент SqlConnection. Идентификатор этого компонента будет храниться в поле sqlConnection1 класса Form1.

Для создания соединения с базой данных используйте те же приемы, что и в предыдущих приложениях этой главы. Вот, примерно, каким должно быть значение свойства ConnectionString объекта sqlConnection1:

data source=localhost; initial catalog=Articles; password=12345;
persist security info=True; user id=c_sharp_app;
workstation id=FROLOV; packet size=4096

Вероятно, у Вас будет другое значение идентификатора рабочей станции workstation id, а также, возможно, идентификатор пользователя user id и пароль password.

Добавление адаптера SqlDataAdapter

Для того чтобы приложение могло загружать содержимое таблицы Tree базы данных Articles, хранящей структуру дерева статей, добавьте в него адаптер SqlDataAdapter. Ссылка на адаптер будет храниться в поле sqlDataAdapter1.

Для этого адаптера необходимо использовать соединение sqlConnection1, о котором мы говорили в предыдущем разделе.

Детально процедура добавления адаптера уже рассматривалась в этой главе, поэтому мы не будем описывать ее заново.

Создание набора данных DataSet

После добавления адаптера создайте набор данных DataSet, воспользовавшись строкой Generate Dataset контекстного меню. Это меню появится на экране, если щелкнуть правой клавишей мыши область окна дизайнера форм, занимаемую значками программных компонентов (нижняя часть окна, показанного на рис. 9-44).

В результате должен быть создан набор данных dataSet11. Кроме того, в проект будут автоматически добавлены компоненты класса SqlCommand, предназначенные для чтения, обновления, удаления и добавления данных в таблицу Tree. Эти команды предназначены для совместной работы с адаптером sqlDataAdapter1, но могут использоваться и независимо от него.

Добавление контекстного меню

До сих пор в этой книге мы не касались приемов создания и редактирования контекстного меню. Вы можете легко добавить такое меню в приложение, перетащив значок компонента ContextMenu из инструментальной панели Toolbox в окно элемента управления, с которым это меню должно быть связано.

В нашем случае нужно связать контекстное меню с деревом просмотра заголовков статей treeView1, перетащив в него упомянутый значок.

Если выделить левой клавишей мыши компонент contextMenu1 в области значков программных компонентов дизайнера форм, то в верхней части формы появится меню Context Menu, показанное на рис. 9-45.

Рис. 9-45. Редактирование контекстного меню

Вы сможете редактировать это меню и создавать обработчики событий для его строк аналогично тому, как это делается и для обычного главного меню формы.

Создайте в контекстном меню строки Add, Delete и Edit. Первая из этих строк будет использована для создания узлов дерева, вторая — для удаления этих узлов, а третья — для редактирования информации, хранящейся в узле дерева (заголовка, веса сортировки и текста статьи).

Создание узла дерева

Создание дерева начинается с того, что пользователь запускает приложение, щелкает правой клавишей мыши пустое окно дерева и выбирает из контекстного меню строку Add. В результате на экране появляется диалоговое окно, показанное на рис. 9-43, где пользователь может ввести информацию для узла дерева.