Аннотация: Данный материал был написан исключительно под его использование в Visual Studio .Net 2003 и ASP.NET 1.0 (других тогда не было). Сменялись версии студии, немного менялись компоненты доступа к данным, но неизменным оставались возможности доступа к записям и их отображения (DataSet, DataTable, DataGrid, DataReader... ). Поэтому материал пока не подвергался адаптации. Все, что здесь написано, до сих пор, от момента загрузки данных в DataSet или создания DataReader, попрежнему актуально и может использоваться в любой Visual Studio.
Глава 2. Основы работа с сервером SQL в Visual Studio .NET
Параграф 1. Отображение записей данных в DataGrid
Параграф 2. Сортировка, фильтрация и доступ к данным
Параграф 3. Изменение и сохранение данных
Параграф 4. SqlCommand и выполнение операций непосредственно в базе данных
Параграф 5. Работа с двоичными данными
Параграф 6. О сопоставлении между таблицей источника и DataTable
Параграф 7. Работа с несколькими связанными таблицами
Для рассмотрения данного материала создадим новый проект (Рис.1) с именем AppBD, с панелью инструментов на базе контрола ToolBar с тремя кнопками. (Подробно о создании проектов решения см. в разделе "Простейшие Windows Application решения").
Рис.1 Форма проекта решения
Установим свойство Tag у кнопок в значения 1, для кнопки 1 (левой), 2 для кнопки 2. Создадим обработчик нажатия кнопок для ToolBar и запишем следующий код, который далее будем использовать для всех примеров этого параграфа.
private void toolBar1_ButtonClick(object sender,
System.Windows.Forms.ToolBarButtonClickEventArgs e)
{
int i=Convert.ToInt32(e.Button.Tag);
switch(i)
{
case 1:
break;
case 2:
break;
default:
Application.Exit();
break;
}
}
В case 1 и 2 будем размещать код, в котором выполняется какое либо действие с БД и вводом или выводом информации, в default действия по закрытию приложения (выход из приложения).
Параграф 1. Отображение данных в DataGrid
Поставим целью отобразить содержимое какой либо таблицы в сетке данных. Кроме этого, данный пример должен позволить нам уяснить общую методику доступа к данным с использованием DataSet.
Предполагается, что Вы имеете доступ к серверу MsSQL и Вам известны необходимые параметры доступа.
В ADO.NET существует два принципа доступа к данным:
1. Выполнить подключение, загрузить требуемые данные в набор данных с помощью адаптера данных, закрыть подключение. Выполнение требуемых операций при этом будет осуществляться в наборе данных, находящемся в КЭШе памяти компьютера.
2. Выполнить подключение, затем, используя контрол или компонент DataCommand и инструкции SQL выполнять требуемые операции непосредственно в базе данных, закрыть подключение. Если предполагается выполнение инструкции Select то используется DataReader, который, в этом случае, обеспечивает и средства интерпретации данных.
В начале мы подробно рассмотрим первый метод.
1.1. Подключение к SQL Server и создание адаптера данных
Из выше сказанного ясно, что для загрузки данных нам потребуется адаптер данных и, естественно, компонент для подключения к источнику данных - компонент Connection. Два возможных пути выполнения этого этапа - создание компонента подключения и, затем, создание DataAdapter, в свойствах Connection которого потребуется напрямую указать имя созданного компонента Connection. Второй способ позволяет выполнить эти действия за один шаг за счет использования Data Adapter Configuration Wizard.
На вкладке Data ToolBox выбирем компонент SqlDataAdapter и перенесем его на форму нашего проекта решения. SqlDataAdapter предназначен для работы с SQL Server 7.0 и более поздними версиями, но можно использовать и OleDbDataAdapter как универсальный адаптер, обеспечивающий доступ к любым совместимым с OLE DB источником данных. Прежде чем компонент займет свое место внизу окна дизайнера формы, будет запущен Data Adapter Configuration Wizard, на панели которого, после прочтения информации о назначении мастера и нажатия кнопки Next, будет предложено создать новое или выбрать уже существующее подключение к базе данных. Нажимаем кнопку New Connection и переходим к окну Data Link Properties. На вкладке Provider выбираем Microsoft Ole DB Provider for SQL Server, на вкладке Connection задаем требуемые параметры подключения. В выпадающем списке пункта 1 выбираем имя сервера (сейчас здесь отображаются все доступные нам SQL сервера сети). Задаем UserName и Password (или, если доступ к серверу осуществляется по правам, предоставленным группе пользователей, входящих в некоторый домен, ставим точку в радиобоксе Use Windows NT Integrated security) и в пункте 3 - Select the database on the server выбираем имя базы данных, в которой располагается интересующая нас таблица. Далее проверяем TestConnection и нажимаем кнопку OK. В окошечке Data Adapter Configuration Wizard появляется имя нашего соединения. Нажимаем кнопку Next и в следующем окошечке выбираем Use SQLStatements (сохраненных процедур у нас пока нет). Нажимаем кнопочку Next и попадаем в окно, где можно задать SQL оператор для выборки данных. Воспользуемся QueryBuilder и в окне AddTable выберем нашу таблицу, выбираем интересующие нас поля и после нажатия кнопочки OK увидим сформированный нами запрос к таблице (его мы могли записать и без использования QueryBuilder).
SELECT
имя_базы.имя_таблицы.*
FROM
имя_базы.имя_таблицы
В данном запросе выбору будут подлежать все строки таблицы. Ограничение выборки будет рассмотрено в следующем параграфе.
Обратим внимание и на кнопочку Advanced Option. Адаптер данных создает четыре предложения SQL. Помимо Select создается Insert, Update, Delete. Если нам требуется только Select, то можно убрать галочку из первого пункта (в чекбокске). При наличии галочки команды Insert и Update могут быть модифицированы.
Рассмотрим пример работы таблицей autors, которая находится в учебной БД pubs(Обычно есть на всех MsSql если только ее не удалил администратор при конфигурировании). Вид таблицы можно посмотреть на Рис.3. В ней одно ключевое поле au_id. Команда SELECT будет, сформированная Query Builder, будет выглядеть следующим образом (dbo это своеобразный алиас, который присваивается базе данных):
SELECT dbo.authors.* FROM dbo.authors
Ниже показано действие модификаторов применительно к командам UPDATE, INSERT, DELETE. При снятых галочках в пунктах 2 и 3 они выглядит следующим образом:
DELETE FROM dbo.authors
WHERE (au_id = @Original_au_id)
INSERT INTO dbo.authors(au_id, au_lname, au_fname, phone,
address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone,
@address, @city, @state, @zip, @contract)
UPDATE dbo.authors
SET au_id = @au_id, au_lname = @au_lname,
au_fname = @au_fname, phone = @phone,
address = @address, city = @city,
state = @state, zip = @zip,
contract = @contract WHERE (au_id = @Original_au_id)
Заметим, что в SqlDataAdapter для доступа к SQL Server версии 7.0 и выше параметры описываются с помощью именованных переменных.
При галочке в чекбоксе 2 (Use Optimistic concurrency) команда Insert не изменится, а команды Delete и Update будут выглядеть следующим образом:
UPDATE dbo.authors SET au_id = @au_id, au_lname = @au_lname,
au_fname = @au_fname, phone = @phone, address = @address,
city = @city, state = @state, zip = @zip, contract = @contract
WHERE (au_id = @Original_au_id)
AND (address = @Original_address
OR @Original_address IS NULL
AND address IS NULL)
AND (au_fname = @Original_au_fname)
AND (au_lname = @Original_au_lname)
AND (city = @Original_city OR @Original_city IS NULL
AND city IS NULL)
AND (contract = @Original_contract)
AND (phone = @Original_phone)
AND (state = @Original_state OR @Original_state IS NULL
AND state IS NULL)
AND (zip = @Original_zip OR @Original_zip IS NULL AND zip IS NULL)
DELETE FROM dbo.authors
WHERE (au_id = @Original_au_id)
AND (address = @Original_address OR @Original_address IS NULL
AND address IS NULL)
AND (au_fname = @Original_au_fname)
AND (au_lname = @Original_au_lname)
AND (city = @Original_city OR @Original_city IS NULL
AND city IS NULL)
AND (contract = @Original_contract)
AND (phone = @Original_phone)
AND (state = @Original_state OR @Original_state IS NULL
AND state IS NULL)
AND (zip = @Original_zip OR @Original_zip IS NULL
AND zip IS NULL)
Это так называемый оптимистический параллелизм, который позволяет командам Update и Delete обнаружить наличие в база данных изменений после того как данные были загружены в набор данных. Это предотвращает конфликты параллелизма. Здесь @Original_ - загруженное в DataSet значение.
Галочка в последнем чекбоксе (Refresh the DataSet) добавляет select после insert и update, что позволяет проверить корректность значений идентификаторов, значений по умолчанию, и расчетных значений после внесения изменений:
INSERT INTO dbo.authors(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone,
@address, @city, @state, @zip, @contract);
SELECT au_id, au_lname, au_fname, phone, address, city,
state, zip, contract FROM dbo.authors
WHERE (au_id = @au_id)
UPDATE dbo.authors SET au_id = @au_id, au_lname = @au_lname,
au_fname = @au_fname, phone = @phone, address = @address,
city = @city, state = @state, zip = @zip, contract = @contract
WHERE (au_id = @Original_au_id)
AND (address = @Original_address
OR @Original_address IS NULL
AND address IS NULL)
AND (au_fname = @Original_au_fname)
AND (au_lname = @Original_au_lname)
AND (city = @Original_city OR @Original_city IS NULL
AND city IS NULL)
AND (contract = @Original_contract)
AND (phone = @Original_phone)
AND (state = @Original_state OR @Original_state IS NULL
AND state IS NULL)
AND (zip = @Original_zip OR @Original_zip IS NULL AND zip IS NULL)
SELECT au_id, au_lname, au_fname, phone, address, city,
state, zip, contract FROM dbo.authors
WHERE (au_id = @au_id)
После того как мы выбрали требуемые Advanced Options, нажимаем Ok и Next, Data Adapter Configuration Wizard генерирует заданные параметры и, после нажатия кнопки Finish, SqlDataAdapter и SQLConnection занимают свои места для невизуальных контролов ниже панели дизайнера формы (Рис. 2).
На данном этапе для каждого из них мы можем посмотреть свойства в окне Properties (правый клик на контроле, контекстное меню Properties). Обратим внимание, что для SQLConnection кроме параметра доступа свойства могут быть изменены только путем генерации нового подключения в том числе и ConnectionTimeOut и, поэтому возможно в некоторых случаях (когда значения по умолчанию нам не подходят) придется генерировать контролы раздельно. Напротив, свойства sqlDataAdapter доступны для редактирования и их всегда можно изменить (например, команды Select, Update, Insert, Delete) в том числе и динамически.
1.2. Создание набора данных
Чтобы создать набор данных DataSet выбираем в контексном меню sqlDataAdapter пункт Generate DataSet (тоже из меню Data). Будет запущен мастер Generate DataSet в котором оставим все установки по умолчанию (флажок Add this DataSet to the designer- добавить этот Dataset в конструктор должен быть установлен) и нажимаем кнопку ОК. DataSet отображается в панели для невизуальных контролов (Рис.2).
Рис.2 Форма проекта решения с компонентами для работы с БД
1.3. Отображения данных в DataGrid
Из Toolbox, закладка Windows Form поместим на форму контрол DataGrid. Из множества его свойств нас пока интересует только одно - DataSource, в выпадающем списке которого установим dataSet11.DayInfo. Правильнее правда будет устанавливать свойство DataSource в dataSet11, а значение свойства DataMember в значение DayInfo - этим мы расширяем возможности маневра переключения между таблицами, когда их будет создано несколько.
Если на данном этапе запустить приложение, то таблица останется не заполненной.
Для заполнения таблицы в Case 1 запишем следующий код (набор данных заполняется методом Fill адаптера данных):
case 1: this.dataSet11.Clear(); this.sqlDataAdapter1.Fill(this.dataSet11.authors); break;
При нажатии кнопочки 1 набор данных будет заполнен (Рис.3).
Рис.3 Приложение в работе. Таблица autors.
Заметим, что указатель this необязателен, но упрощает написание первых слов в строках кода, так как дает возможность воспользоваться контекстным выбором и первого слова (хотя тот же эффект достигается при написании первой буквы слова и нажатие Cntrl+пробел).
Данные в DataGrid можно редактировать, При редактировании содержимого в DataGrid изменения сохраняются в наборе данных, но не в базе. Для передачи данных в базу использунтся метод адаптера данных Update:
sqlDataAdapter1.Update(this.dataSet11.authors);
Параграф 2. Сортировка, фильтрация и доступ к данным
Данные, загруженные в DataSet и отображенные в DataGrid, упорядочены по ключам и могут быть вообще не упорядочены. Разработчику же чаще всего требуется свой порядок следования данных и своя фильтрация, а иногда и многократно меняющаяся за время работы с таблицей и сортировка и фильтрация.
2.1. Сортировка и фильтрация данных
Фильтрацию и сортировку можно выполнять с помощью представления данных. Оно может быть явно добавлено в форму (невизуальный компонент DataView). DataView позволяет устанавливать параметры сортировки и фильтрации, как на этапе проектирования, так и программно. Другой способ - использование представления данных по умолчанию. DefaultView создается автоматически для каждого DataSet при его заполнении. Рассмотрим пример использования сортировки и фильтра для нашей таблички с использованием DefaultView.
case 2: //Отменяем сортировку по умолчанию dataSet11.Tables[0].DefaultView.ApplyDefaultSort=false; //Сортируем по DayOfYear dataSet11.Tables[0].DefaultView.Sort="state"; //К чему будем применять фильтр (можно к исходным - по умолчанию, // измененным, неизмененным, добавленным, оригинальным, не применять) dataSet11.Tables[0].DefaultView.RowStateFilter = DataViewRowState.CurrentRows; //Устанавливаем фильтр для значений dataSet11.Tables["authors"].DefaultView.RowFilter = "state = \'CA\'"; break;
В коде показано и два способа доступа к представленной в DataSet таблице - через индекс номер и индекс имя.
Аналогично и если мы будем использовать контрол dataView1, поместив его в проект с вкладки Data панели Tools. Здесь мы можем указать теже значения, как и для DefaultView, задав их в его окне Properties или программно. Следующий код полностью аналогичен по эффективности предыдущему:
dataView1.ApplyDefaultSort=false; dataView1.Sort="state"; dataView1.RowStateFilter=DataViewRowState.CurrentRows; dataView1.RowFilter= "state = \'CA\'"; dataGrid1.DataSource = dataView1;
Отметим, что параметры DataViewRowState это флаги и их можно комбинировать обычным для флагов образом:
DataViewRowState eEnumeration;
eEnumeration = DataViewRowState.Added |
DataViewRowState.OriginalRows;
dataView1.RowStateFilter=eEnumeration;
2.2. Доступ к значениям полей данных
На данном этапе данные в DataGrid отсортированы как необходимо разработчику. Но собственно DataGrid нам необходим только для визуализации данных или если предполагается их правка прямо в таблице. Однако, в большинстве практических задач нам требуется прочитать какие либо данные (взять из таблицы) и, далее, выполнять с ними какие либо действия. Причем результаты не обязательно должны быть возвращены в эту же таблицу.
В силу этого, следующим шагом рассмотрим выборку данных, и, как бы не хотелось сказать из DataGrid, но данные хранят DataSet и представления, а DataGrid может быть совсем удален из приложения.
Итак, доступ к данным можно получить и через DataSet и через представление данных. Кроме того, при помощи представления данных можно обновлять, добавлять и удалять записи (при условии, что параметры представлений AllowEdit, AllowNew и AllowDelete установлены true).
Для дальнейшего выполнения примеров оставим в case 2 оба код сортировки и фильтрации для dataView1.
В отфильтрованных и отсортированных данных требуемую строку (запись) можно найти c помощью метода Find:
dataView1.ApplyDefaultSort=false;
dataView1.Sort="au_id";
dataView1.RowStateFilter = DataViewRowState.CurrentRows;
dataView1.RowFilter= "state = \'CA\'";
dataGrid1.DataSource = dataView1;
int viNomRow=dataView1.Find("427-17-2319");
Text=Convert.ToString(viNomRow)
Метод возвратит номер строки в отфильтрованных данных, для которой значение поля au_id равно 427-17-2319. Далее мы можем прочитать интересующую нас запись или поля записи:
if(viNomRow != -1) Text= Convert.ToString(dataView1[viNomRow]["au_lname"]) +" "+ Convert.ToString(dataView1[viNomRow]["au_fname"]) +" "+ Convert.ToString(dataView1[viNomRow]["phone"]);
Напомним, что мы используем свойство Text формы только из за простоты отображения данных. В заглавии формы будут выведены значения запрошенных нами полей. Следующий код использует представление DataRowView, для последовательного чтения данных из таблицы:
int i=0;
foreach(DataRowView datarowview in dataView1)
{
Text =
Convert.ToString(dataView1[i]["au_lname"])
+" "+
Convert.ToString(dataView1[i]["au_fname"])
+" "+
Convert.ToString(dataView1[i]["phone"]);
i++;
}
//Это позволит нам узнать общее число строк
Text = Convert.ToString(i);
Цикл по записям можно было организовать и так:
int i=0;
for(i=0; i < dataView1.Count; i++)
{
}
Кстати, мы можем отследить чтение записи в DataGrid, добавив в цикл следующий код;
dataGrid1.CurrentRowIndex+=1; dataGrid1.Select(dataGrid1.CurrentRowIndex);
Для чтения данных могут быть использовано не только представления, но и сами наборы данных. Повторим, что в общем случае, данные в таблицах могут быть не сортированы. Если таблица имеет первичный ключ или мы его можем определить сами, то возможно использования метода Find:
//Определяем первичный ключ для DataSet
DataColumn[] keys = new DataColumn[1];
DataTable datatable=dataSet11.Tables["authors"];
keys[0] = dataSet11.Tables["authors"].Columns["au_id"];
datatable.PrimaryKey = keys;
//Осуществляем поиск по DataSet в колонке первичного ключа
DataRow datarow=dataSet11.Tables["authors"].Rows.Find("427-17-2319");
//Выводим значение
if(datarow != null)
Text=datarow[0].ToString()+" "+datarow[1].ToString()+" "+
datarow[2].ToString();
Если первичный ключ состоит из нескольких колонок, то в метод Find() можно передать массив объектов. При невозможности назначить ключ придется либо выполнять поиск методом перебора строк записей в DataSet, но более широкие возможности дает использование метода SELECT. В следующем примере мы выведем все туже запись, что и ранее:
DataTable datatable=dataSet11.Tables["authors"];
DataRow[] datarows = datatable.Select("au_id=\'427-17-2319\'
AND state=\'CA\'");
foreach(DataRow datarow in datarows)
Text+=datarow[0].ToString()+" "+
datarow[1].ToString()+" "+
datarow[2].ToString();
Метод Select может иметь три параметра:
public DataRow[] Select( string filterExpression, string sortExpression, DataViewRowState recordStates );
Значение параметров:
filterExpression - Выражение для фильтрации. Может принимать только значения true или false.
sortExpression - Выражение для сортировки, имя столбца или вычисляемое значение.
recordStates - Значение, определяющее версию и состояние, по которым требуется фильтровать записи. Может принимать значения:
Added - Новая (добавленная) строка.
CurrentRows - Текущие строки, включая неизмененные, новые и измененные строки.
Deleted - Удаленная строка.
ModifiedCurrent - Текущая версия, которая является измененной версией исходных данных.
ModifiedOriginal - Исходная версия (хотя она может быть изменена и доступна как ModifiedCurrent).
None - Нет.
OriginalRows - Текущие строки, включая неизмененные и удаленные.
Unchanged - Неизмененная строка.
Метод Select имеет богатейшие возможности по работе с данными таблиц, практически не уступающие стандартному SQL. Так следующий пример позволяет выбрать и отсортировать по номеру телефона необходимые нам строки и вывести значения в контрол textBox (о работе с textBox см. параграф "Отображение текстовой информации" в разделе "Начала разработки Windows Application проектов на C#"):
DataTable datatable=dataSet11.Tables["authors"];
DataRow[] datarows = datatable.Select("state=\'CA\'","phone ASC");
textBox1.Text="";
foreach(DataRow datarow in datarows)
textBox1.Text+=datarow[1].ToString()+"\t\t"+
datarow[3].ToString()+"\r\n";
Отметим, что, так как в Select допустимы сортировка, выбор по нескольким условиям, возможно в условиях использовать агрегатные функции и то, что он применим к таблицам DataSet и таблицам представлений, делает его использование более предпочтительным при выборе данных, по отношению ко всем другим рассмотренным способам.
Пример использования агрегатных функций:
string sS = datatable.Compute("MAX(zip)","").ToString();
textBox1.Text+=sS;
Параграф 3. Изменение и сохранение данных
DataSet является копией реальных данных в БД, поэтому изменение данных в наборе данных и их запись в реальную таблицу разделены. Отметим, что в С# есть возможность и непосредственной работы с таблицами через DataCommand и об этом речь будет в следующем параграфе. Здесь мы рассмотрим два этапа ввода данных - ввод данных и их изменение в DataSet и перенос данных в таблицы БД.
Рассмотрим методы изменения данных в DataSet.
3.1. Вставка записи в DataSet
Для вставки может быть использован метод представления данных AddNew, который создает новую запись и возвращает объект DataRowView. При добавлении записи с использованием представлений данных необходимо устанолвить свойство представления AllowNew в значение true.
Пример вставки записи в DataSet:
//Устанавливаем свойство AllowNew dataView в true dataView1.AllowNew=true; dataGrid1.DataSource = dataView1; DataRowView datarowview = dataView1.AddNew(); datarowview["au_id"]="999-99-999"; datarowview["au_lname"]="MyLastName"; datarowview["au_fname"]="MyFirstName"; и.т.д.
Еще один способ добавления записей в DataSet - метод LoadDataRow() таблиц, позволяющий передать в строку таблицы DataSet массив значений, определяемый первым параметром. Параметры элементов массива должны соответствовать столбцам таблицы. Второй параметр метода позволяет управлять значением свойства RowState новой записи. При false значение этого свойства Added, как и при добавлении новой записи методом Add.
Пример вставки записи в DataSet:
//Устанавливаем свойство AllowNew dataView в true
dataView1.AllowNew=true;
dataGrid1.DataSource = dataView1;
dataView1.AllowNew=true;
dataGrid1.DataSource = dataView1;
object[] valuerow = {"111-11-11","MyLastName", "MyFirstName",
"555-55-55","309 63rd St. #411","Oacland",
"CA","99999","false"};
dataSet11.Tables[0].LoadDataRow(valuerow, false);
dataView1.LoadDataRow(valuerow, false);
Кроме того, для редактирования можно использовать свойство Rows таблиц DataSet и метод Add коллекции Rows таблиц.
dataGrid1.DataSource = dataSet11; //Создаем объект DataRow вне таблицы DataRow datarow = dataSet11.Tables[0].NewRow(); datarow[0]="111-11-11"; datarow[1]="MyLastName"; datarow[2]="MyFirstName"; datarow[3]="555-55-55"; datarow[4]="309 63rd St. #411"; datarow[5]="Oacland"; datarow[6]="CA"; datarow[7]="99999"; datarow[8]="false"; //Добавляем запись в набор dataSet11.Tables[0].Rows.Add(datarow);
3.2. Удаление записи из DataSet
Для удаления используется метод представления данных Delete. При удалении записи с использованием представлений данных необходимо устанолвить свойство представления AllowDelete в значение true. Метод Delete удаляет записи по их номеру, и, поэтому, запись перед удалением потребуется найти (как это делать - см. выше).
Пример вставки записи в DataSet:
dataView1.AllowDelete=true; dataView1.Delete(0); dataView1.Delete(0);
3.3. Редактирование записи из DataSet
Для редактирования записи можно использовать непосредственный доступ к полям данных в представлениях, который становится возможным при значении свойства представления AllowEdit равным true.
Пример вставки записи в DataSet:
dataView1.AllowEdit=true; dataView1[0]["au_id"] = "999-99-9999"; dataView1[0]["au_lname"]="MyLastName"; dataView1[0]["au_fname"]="MyFirstName";
Можно использовать метод ItemArray DataRow. Он позволяет изменить все содержимое строки:
dataGrid1.DataSource = dataSet11;
DataRow datarow = dataSet11.Tables[0].Rows[0];
object[] valuerow = {"111-11-11","MyLastName", "MyFirstName",
"555-55-55","309 63rd St. #411","Oacland",
"CA","99999","false"};
datarow.ItemArray=valuerow;
При работе с таблицами, у которых есть столбцы с автоинкрементном существуют некоторые особенности. Так, для столбца с автоинкерементом необходимо в DataSet задать значение свойства AutoIncrement равное true. Тогда значение счетчика при добавлении строк будет начинаться с AutoIncrementSeed и увеличиваться на значение AutoIncrementStep. Кроме того, рекомекндуется свойство ReadOnly столбца устанавливать в true.
DataColumn datacolumn = dataSet11.Tables[0].Columns.
Add("Имя_столбца", typeof(Int32));
column.AutoIncrement = true;
//Начнем счет с 1
column.AutoIncrementSeed = 1;
//Автоинкремент с шагом 1
column.AutoIncrementStep = 1;
3.4. Сохранение измененных данных
Для сохранения измененных данных используется метод sqlDataAdapter Update(). В период создания DataAdapter мы уже рассмотрели 3 настраиваемых свойства: InsertCommand, UpdateCommand и DeleteCommand. При вызове метода Update мы выполняем по существу три эти SQL предложения. Метод вызывает соответствующие инструкции INSERT, UPDATE или DELETE для каждой вставленной, обновленной или удаленной строки в DataSet. Список перегрузки позволяет применять метод не только к DataSet, но и к массиву объектов DataRow и к объекту DataTable
public override int Update( DataSet dataSet ); public int Update( DataSet dataSet, string srcTable ); public int Update( DataRow[] dataRows ); public int Update( DataTable dataTable ); protected virtual int Update( DataRow[] dataRows, DataTableMapping tableMapping );
sqlDataAdapter1.Update(dataSet11); sqlDataAdapter1.Update(dataSet11,"autors");
3.5. Отображение записей данных в текстовых полях
Поставим целью отобразить интересующие нас данные таблицы autors (Рис.3), относящиеся к одному автору, в виде текстовых полей. Пусть нас будут интересовать имя, фамилия и телефон. Кроме того, поставим целью выполнить перед выводом отбор авторов в определенном штате, условное наименование которого будем выбирать из выпадающего списка
Ясно, что в данном случае нам будет необходим отбор данных на основании запроса с параметрами, что позволит вместо переноса всей таблицы в DataSet, считывать только часть необходимых (отфильтрованных на сервере) данных.
Для выполнения поставленной задачи будем использовать существующий проект решения, который мы немного модернизируем (можно, конечно, пройти и все шаги параграфа 1 этого раздела, но это приведет к неизбежному повторению того, что и так уже написано, да и модернизация проекта это тоже задача, имеющая свои особенности).
Итак, по шагам:
Убираем из формы DataGrid;
Вызываем из контекстного меню sqlDataAdapter1 пункт Configure Data Adapter (правый клик мышки для выбора контекстного меню). Доходим в нем до пункта запуска QueryBuilder и нажимаем кнопочку QueryBuilder (пропуск предыдущих шагов означает, что мы оставили без изменения все, что было связано с созданием соединения). В QueryBuilder в табличке autors отбираем только интересующие нас поля, выделяя их галочками и сняв галочку у чекбокса All Columns. Это поля: au_id, au_lname, au_fname, phone, state. Поле au_id нам нужно лиш как ключевое, хотя и без него можно обойтись, но без него мы лишимся возможности внесения изменений в базу данных (SQL выражения для Insert, Update, Delete сформированы не будут).
В табличке QueryBuilder против полей au_lname, au_fname последовательно выбираем в выпадающем списке Ascending (это определит сортировку ORDER BY au_lname, au_fname). В строке state в столбце Criteria поставим =? - что означает подставляемый параметр.
Наш SQL запрос примет вид:
SELECT au_id, au_lname, au_fname, phone, state FROM dbo.authors WHERE (state = ?) ORDER BY au_lname , au_fnameЗавершаем конфигурирование SqlDataAdapter, нажав Next и Finish.
Если мы вновь посмотрим на предложение Select, например, в окне Properties, то увидим, что оно примет вид:
SELECT au_id, au_lname, au_fname, phone, state FROM dbo.authors WHERE (state = @Param1) ORDER BY au_lname, au_fname
Это означает, что для доступа к SQL Server версии 7.0 и выше параметры описываются с помощью именованных переменных.
Предложение SQL Select хранится в свойстве SelectCommand адаптера данных (окно Properties). Откроем данное свойство, кликнув мышкой на значке плюс и обратим внимание на коллекцию Parameters. Она содержит параметры, которые мы задали как подставляемые. В данном случае параметр только один, а именно значение столбца - имя штата (Рис.4).
Рис.4 Подставляемые параметры команды Select
Обратим внимания на свойство Direction где параметр при выборе в выпадпющем списке может быть определен как вводной (Input), выводной (Output), или InputOutput и как возвращаемое значение ReturnValue. Нас пока интересует этот параметр как Input.
убираем с формы DataSet и DataView и вновь генерируем DataSet (как это сделать см. выше).
Поместим на форму три контрола textBox и один ComboBox. Подробно о работе с данными контролами можно посмотреть в параграфах "Вывод текстовой информации" и "Работа со списками в C#" раздела "Начала разработки Windows Application решений для C#". В свойство Items ComboBox внесем значения (каждое на новой строчке):
CA KS TN OR MI IN MD UT TN
В значение Text для ComboBox запишем значение CA, значения Text для textBox сделаем пустой строкой.
В Case 1 напишем код для заполнения набора данных.
case 1: dataSet11.Clear(); //Определяем значение нашего единственного параметра @Param1 sqlDataAdapter1.SelectCommand.Parameters[0].Value = comboBox1.Text; //или так sqlDataAdapter1.SelectCommand.Parameters["@Param1"].Value = comboBox1.Text; //Загружаем адаптер sqlDataAdapter1.Fill(dataSet11.authors); break;
Выполняем привязку текстовых полей к набору данных. Для этого в окне Properties для каждлго textBox в свойстве DataBinding для его свойства Text в выпадающем списке выбираем DataSet. Далее выбираем таблицу и имя поля, соответственно для textBox1 - au_lname, textBox2 - au_fname, textBox3 - phone.
В case 2 добавляются код управления, обеспечивающий переход между записями. В примере при нажатии кнопки 2 будет выполняться "листание" по DataSet вперед. Аналогично можно добавить кнопки листания назад, перехода к первой и последней записи.
case 2: BindingContext[dataSet11, "authors"].Position +=1 ; break;
Добавляем код обеспечивающий смену заполнения DataSet при изменении имени штата. Для этого создаем обработчик события изменения состояния для comboBox1 (напомним, что самый простой способ создания обработчика основного события для контрола - двойной клик мышкой по самому контролу в дизайнере формы). Чтобы не повторять код воспользуемся программной имитацией нажатия кнопки 1 toolBar.
private void comboBox1_SelectedIndexChanged(object sender, System.EventArgs e) { ToolBarButton tb = new ToolBarButton(); ToolBarButtonClickEventArgs e1= new ToolBarButtonClickEventArgs(tb); e1.Button.Tag=1; toolBar1_ButtonClick(null,e1); }
На этом поставленная цель достигнута. Приложение в работе демонстрирует Рис.5.
Рис.5 Отображение записей данных в текстовых полях
Параграф 4. SQLCommand и выполнение операций непосредственно в базе данных
Альтернативным рассмотренным выше вариантом работы с базами данных является выполнение требуемых операций непосредственно в базе данных. Когда приложение проводит частые и объемные изменения содержания записей в БД такой способ является и более эффективным.
Для работы в таком режиме Microsoft Visual C# содержит набор объектов Command, позволяющих выполнять команды и возвращать результаты из источника данных.
Далее рассмотрим работу с базами данных с использованием sqlCommand. Все примеры будем выполнять, используя заготовку проекта решения, описанного в начале раздела (см. Рис.1).
Компоненты команд работают в своеобразной спарке с компонентом Connection, и, поэтому, предварительно поместим на форму компонент sqlConnection. В окне свойств (Properties) выбираем свойство ConnectionString и в его выпадающем окне - New Connection (если Вы выполняли все примеры этого раздела, то у Вас отобразится и соединение, использованное нами для работы с sqlDataAdapter, которое Вы также можете использовать). И мы перешли к знакомому окну Data Link Properties. На вкладке Provider выбираем Microsoft Ole DB Provider for SQL Server, на вкладке Connection задаем требуемые параметры подключения (cм. выше если забыли как). После выбора имени базы данных (договорились pubs), в которой располагается интересующая нас таблица и прохождения TestConnection и нажимаем кнопку OK. На этом соединение создано и sqlDataConnection разместится на своем месте для невизуальных контролов - в нижней части проекта решения.
Поместим на форму прооекта решения компонент sqlCommand (Панель ToolBox вкладка Data). Он сразу размещается на панельке для невизуальных контролов. Переходим к его свойствам в окне Properties. В свойстве Connection в выпадающем списке выбираем в узле Existing sqlConnection1. Мы могли начать создание подключения и в данном пункте, выбрав в качестве значения свойства New (предыдущие шаги повторятся с момента работы с Data Link Properties и завершатся созданием sqlConnection).
4.1. Методы SqlCommand
На данном этапе мы имеем все для начала работы с БД, но прежде остановимся на объекте sqlaCommand. Он содержит ряд методов Execute, которые можно использовать для выполнения нужного действия (выполнения SQL предложение, помещенного в свойство CommandText):
ExecuteReader - создает объект SqlDataReader, который может быть использован для чтения множественных данных из БД. SqlDataReader позволяет читать данные только в прямом направлении. Данные возвращаются по мере выполнения запроса и сохраняются в сетевом буфере на машине клиента до тех пор, пока они не будут запрошены с помощью метода Read объекта SqlDataReader.
ExecuteScalar - используется для возврата одиночного значения.
ExecuteNonQuery - используется в случаях, когда исполняемое предложение не возвращает данных.
ExecuteXmlReader - используется для работы с объектами XmalReader.
Рассмотрим несколько примеров.
Простейший пример выбора скалярного выражения из таблицы autors (Рис.3). Выбираем номер телефона с самым большим номером (конечно для примера):
case 1: sqlCommand1.CommandText="select max(phone) from authors"; sqlConnection1.Open(); string s=sqlCommand1.ExecuteScalar().ToString(); Text=s; break;
Пример создания таблицы:
case 1:
sqlCommand1.CommandText=
"create table EveryDayInfo(DayOfEear SMALLINT NOT NULL PRIMARY KEY,
Value INT)";
sqlConnection1.Open();
//Выполняем предложение SQL
int results=sqlCommand1.ExecuteNonQuery();
//При успешном выполнении значение равно -1
MessageBox.Show("Возвращенное значение = " + result.ToString());
Метод ExecuteNonQuery можно использовать для выполнения различных операций в том числе для операций с каталогами, изменения данных в базе данных (выполнения предложений UPDATE, INSERT или DELETE...). Для предложений UPDATE, INSERT и DELETE возвращаемым значением является количество измененных строк, для всех остальных предложений возвращается значение -1. Возможное исключение - InvalidOperationException - подключение не существует или подключение не открыто
Пример добавления записей в таблицу базы данных:
sqlCommand1.CommandText=
"insert into EveryDayInfo (DayOfEear,Value) Values(1,10)";
sqlConnection1.Open();
int result=sqlCommand1.ExecuteNonQuery();
if(result == 1)
MessageBox.Show("Строка добавлена в таблицу");
else
MessageBox.Show("Строка не добавлена в таблицу");
sqlConnection1.Close();
Обратим внимание на то, что при попытке повторного добавления той же строки мы вместо возвращаемого результата получим исключение со следующим сообщением:
Violation of PRIMARY KEY constraint 'PK__EveryDayInfo__778AC167'. Cannot insert duplicate key in object 'EveryDayInfo'. The statement has been terminated.
В данном случае первичный ключ не дал внести дубликат записи. Кроме того, понятно, что в операциях с БД целесообразно пользоваться конструкцией try catch, например так:
try
{
sqlCommand1.CommandText="insert into EveryDayInfo (DayOfEear,Value) Values(1,200)";
sqlConnection1.Open();
int result=sqlCommand1.ExecuteNonQuery();
if(result == 1)
MessageBox.Show("Строка добавлена в таблицу");
else
MessageBox.Show("Строка не добавлена в таблицу");
}catch(Exception ex1)
{
MessageBox.Show("Строка не добавлена в таблицу. Исключение:\n"
+ex1.Message);
}
sqlConnection1.Close();
4.2. Использование метода ExecuteReader
Используя метод ExecuteReader можно достичь многих из тех возможностей по доступу к данным, что и при использовании DataSet. Кроме того, можно выполнять действия и с хранимыми процедурами (для этого необходимо sqlCommand1.CommandType присвоить значение CommandType.StoredProcedure, по умолчанию оно равно Text).
Метод может использоваться без параметров или содержать параметр типа CommandBehavior, cодержащий описание результатов запроса и его воздействия на базу данных.
Значения CommandBehavior это флаги (Может быть использована поразрядная комбинация этих значений), которые могут быть:
CloseConnection - объект Connection закрывается, когда закрывается связанный с ним объект SqlDataReader.
Default - флаги не устанавливаются, аналог ExecuteReader().
KeyInfo - запрос возвращает затребованную SQL предложением информацию и значение первичного ключа. Запрос выполняется без блокировки выбранных строк.
SchemaOnly - запрос только возвращает затребованную SQL предложением информацию и не влияет на состояние базы данных.
SequentialAccess - позволяет SqlDataReader загрузить данные как поток и затем можно использовать метод GetBytes или метод GetChars, чтобы выбрать байт по его позиции в потоке в указанный буфер. При использовании SqlDataReader считать значение данных столбца можно только один раз (OleDbDataReader допускает повторное чтение данных).
SingleResult - запрос возвращает один набор результатов.
SingleRow - запрос должен вернуть только одну строку.
Запишем в созданную таблицу, как это было показано выше, несколько строк и выполним следующий код для прочтения записанных данных:
SqlDataReader myReader = null;
try
{
sqlCommand1.CommandType=CommandType.Text;
sqlCommand1.CommandText="select * from EveryDayInfo";
sqlConnection1.Open();
myReader =
sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
MessageBox.Show("Значение строки = "+
//Способ доступа к значению поля по индексу
myReader.GetValue(0).ToString()+" равно "+
//Способ доступа к значению поля по имени
myReader["Value"].ToString());
}
}catch(Exception ex1)
{
MessageBox.Show("Исключение при чтении из таблицы БД:\n"
+ex1.Message);
}
myReader.Close();
sqlConnection1.Close();
SqlDataReader имеет множество методов для работы с различными типами данных. (GetFloat, GetDecimal, GetDateTime, GetByte, GetBoolean, GetDouble, GetInt16 ...), которые целесообразно использовать при известном типе поля для исключения вспомогательных преобразований данных.
SqlDataReader при использовании метода GetValues позволяет читать в буфер сразу всю строку. Изменим немного предыдущий код и получим тот же результат:
//Объявляем буфер, причем значения полей типа object
//могут и не соответствовать объявлению в таблице
object[] buff = {0,""};
while(myReader.Read())
{
myReader.GetValues(buff);
MessageBox.Show("Значение строки = "+buff[0].ToString()+
" равно "+buff[1].ToString());
}
myReader.Close();
Примечание: в .NET Framework Windows Server 2003 SqlDataReader включают дополнительное свойство HasRows, позволяющее перед чтением данных определить, возвратил ли DataReader какие-либо результаты.
if(myReader.HasRows)
{
while (myReader.Read())
{
}
}
else
{
}
Перечислим также и другие методы SqlDataReader:
Close Закрывает SqlDataReader. Если SqlDataReader не закрыть объект Connection остается недоступным для других операций с источником данных.
GetDataTypeName Возвращает имя типа данных источника
GetFieldType Возвращает тип данных источника
GetName Возвращает имя столбца, заданного по номеру
GetOrdinal Возвращает номер столбца, заданного по имени
GetSchemaTable Возвращает объект DataTable, содержащий метаданные о столбцах
GetValue Возвращает значение столбца, заданного по номеру, во внутреннем формате
GetValues Возвращает все столбцы текущей строки
IsDBNull Возвращает значение, указывающее, содержит ли столбец несуществующее значение
NextResult Осуществляет переход на следующий результирующий набор
Read Осуществляет переход к следующей записи
4.3. SqlDataReader и получение сведений о схеме данных
Используя метод GetSchemaTable объекта SqlDataReader можно получить сведения о схеме, относящийся к выбранным данным. GetSchemaTable возвращает объект DataTable, содержащий по одной строке таблицы для каждого столбца выбранного набора результатов. Каждый столбец таблицы отображает одно свойство.
Для каждого .Net провайдера данных будет возвращена своя конкретная схема. Для .Net провайдера OleDb также возможно вызвать функцию GetOleDbShemaTable класса OleDbConnection, избегая создания экземпляра SqlDataReader.
В схеме могут присутствовать следующие столбцы: ColumnName, ColumnOrdinal, ColumnSize, NumericPrecision, NumericScale, IsUnique, IsKey, BaseServerName, BaseCatalogName, BaseColumnName, BaseSchemaName, BaseTableName, DataType, AllowDBNull, ProviderType, IsAliased, IsExpression, IsIdentity, IsAutoIncrement, IsRowVersion, IsHidden, IsLong, IsReadOnly.
Название полей несложно извлечь, используя следующий код:
sqlCommand1.CommandText="select phone from authors"; sqlConnection1.Open(); SqlDataReader myReader = sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection); DataTable schemaTable = myReader.GetSchemaTable(); for(int i=0; i < schemaTable.Columns.Count; i++) textBox1.Text+=schemaTable.Columns[i].ToString()+", ";
Зная имя поля, можно получить его значение:
sqlCommand1.CommandText="select phone from authors";
sqlConnection1.Open();
SqlDataReader myReader =
sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
DataTable schemaTable = myReader.GetSchemaTable();
foreach (DataRow myRow in schemaTable.Rows)
{
MessageBox.Show(myRow["ColumnName"].ToString());
MessageBox.Show(myRow["DataType"].ToString());
MessageBox.Show(myRow["ColumnSize"].ToString());
MessageBox.Show(myRow["IsReadOnly"].ToString());
}
myReader.Close();
sqlConnection1.Close();
Следующий, слегка измененный пример из MSDN Library, позволяет просмотреть значения для полей схемы столбца phone:
sqlCommand1.CommandText="select phone from authors";
sqlConnection1.Open();
SqlDataReader myReader =
sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
DataTable schemaTable = myReader.GetSchemaTable();
int i=1;
foreach(DataRow myRow in schemaTable.Rows)
{
foreach(DataColumn myCol in schemaTable.Columns)
{
MessageBox.Show("Значение поля "+Convert.ToString(i)+" "+ myRow[myCol]);
i++;
}
}
myReader.Close();
sqlConnection1.Close();
4.4. SqlDataReader и работа с несколькими наборами данных
Объект SqlCommand позволяет задавать для выполнения сразу несколько SQL предложений. Выбранные данные хранятся в буфере компьютера, а метод NextResult() позволяет выполнить переключение SqlDataReadera на следующий набор данных. Рассмотрим пример работы с табличками authors и EveryDayInfo:
sqlCommand1.CommandText
="select * from EveryDayInfo;select au_lname, phone from authors;";
sqlConnection1.Open();
SqlDataReader myReader =
sqlCommand1.ExecuteReader(CommandBehavior.CloseConnection);
//Работаем с первым набором данных:
while(myReader.Read())
{
MessageBox.Show("Значение строки = "+
myReader.GetValue(0).ToString()+" равно "+
myReader["Value"].ToString());
}
//Переключаем myReader на следующий набор данных
myReader.NextResult();
while(myReader.Read())
{
MessageBox.Show("Имя автора = "+
myReader.GetValue(0).ToString()+" телефон: "+
myReader.GetValue(1).ToString());
}
myReader.Close();
sqlConnection1.Close();
Отметим, что мы можем использовать и невизуальные компоненты SqlCommand, и SqlConnection, а задание SQL предложений может быть выполнено не только в свойстве, но и в конструкторе (как отмечалось уже неоднократно - можно всегда посмотреть как обойтись без визуального компонента в коде в секции глобальных объявлений и в функции InitializeComponent() решения):
SqlConnection sqlConnection2;
sqlConnection2 = new SqlConnection();
sqlConnection2.ConnectionString = "Прописываем ручками строку";
SqlCommand sqlCommand2=new SqlCommand("select * from EveryDayInfo;"
+"select au_lname, phone from authors;");
sqlCommand2.Connection = sqlConnection2;
sqlConnection2.Open();
SqlDataReader myReader =
sqlCommand2.ExecuteReader(CommandBehavior.CloseConnection);
myReader.NextResult();
while(myReader.Read())
{
MessageBox.Show("Имя автора = "+
myReader.GetValue(0).ToString()+" телефон: "+
myReader.GetValue(1).ToString());
}
И все же, когда есть визуальный компонент, то предпочтительнее использовать его, так как это просто удобнее.
Параграф 5. Работа с двоичными данными
До сих пор мы рассматривали работу DataReader и DataSet в режиме загрузки данных в виде строк. Однако при работе с некоторыми типами данных (например, рисунками), содержащих большие двоичные объекты (BLOB-объекты) обработка должна выполняться несколько иначе.
5.1. SqlDataReader и работа с двоичными данными
При работе с BLOB-объектами непосредственно в базе данных используется перегрузка метода ExecuteReader объектов DataReader , со значением параметра CommandBehavior SequentialAccess. Как результат данные интерпретируются как поток байт, который либо полностью, либо последовательно по мере поступления данных загружается в память.
В этом случае доступ к возвращаемым полям в произвольном порядке не возможен до тех пор, пока не будет считана следующая строка. И, кроме того, при использовании SequentialAccess доступ должен выполняться в определенном порядке. Например, если предложение SQL выбирает два столбца, второй из которых - BLOB-объект, то значение для первого столбца может быть получено только до получения данных из второго и не наоборот. Это объясняется тем, что в режиме SequentialAccess данные возвращаются последовательно.
При доступе к данным в поле BLOB следует использовать методы GetBytes или GetChars, заполняющие массив данными. Возможно, но нецелесообразно использование GetString, так как в этом случае придется загружать весь BLOB-объект в одну строковую переменную. Кроме того, с помощью GetBytes и GetChars можно определить размер данных (см. пример ниже).
Рассмотрим работу в данном режиме на примере работы с рисунками. Ее можно разделить на три этапа: создание таблицы, занесение рисунка в таблицу базу данных, чтение рисунка из таблиц БД и его отображение.
Создание таблицы:
Создаем таблицу с двумя полями idimage и pic, причем второе поле типа image или BLOB поле.
sqlCommand1.CommandText=
"create table images(idimage INT NOT NULL PRIMARY KEY,
pic image NOT NULL)";
sqlConnection1.Open();
//Выполняем предложение SQL
int result=sqlCommand1.ExecuteNonQuery();
//При успешном выполнении значение равно -1
MessageBox.Show("Возвращенное значение = " + result.ToString());
Занесение рисунка в таблицу базу данных:
sqlConnection1.Open();
//idimage должно меняться с шагом 1 при записи следующего рисунка
//@MyImage - подставляемое значение параметра
sqlCommand1.CommandText
="insert into images (idimage,pic) Values(1,@MyImage)";
//Заносим имя параметра в массив Parameters
sqlCommand1.Parameters.Add("@MyImage", SqlDbType.VarBinary);
//Создаем поток для нашего рисунка. Естественно в
//файл C:\a.bmp должен быть помещен рисунок
FileStream fsImage=new FileStream(@"C:\a.bmp",
FileMode.Open,FileAccess.Read );
int viImageSize = (int)fsImage.Length;
//Ассоциируем массив байт с потоком
byte[] bImage=new byte[viImageSize];
fsImage.Read(bImage,0,viImageSize);
//Подставляем значение параметра
sqlCommand1.Parameters["@MyImage"].Value = bImage;
try
{
int viResult=sqlCommand1.ExecuteNonQuery();
}
catch(Exception ex1)
{
MessageBox.Show("Исключение:\n"+ex1.Message);
}
fsImage.Close();
sqlConnection1.Close();
Чтение рисунка из таблиц БД и его отображение
Отображать рисунок будем в контроле PictureBox, который поместим на форму, установив соответствующие размеры (другие параметры менять не надо). В примере используется MemoryStream, как сохранять из него данные в файле мы подробно рассматривали в параграфе "Более подробно о потоках и работе с файлами" раздела "Основы работы с файлами и директориями на C#" и, поэтому, здесь не будем повторяться.
//Выбираем первый рисунок (пока мы записали один).
sqlCommand1.CommandText="select pic from images where idimage =1";
sqlConnection1.Open();
int viSize =0;
MemoryStream myStream=null;
SqlDataReader myReader =null;
try
{
myReader =
sqlCommand1.ExecuteReader(CommandBehavior.SequentialAccess);
myReader.Read();
//Определяем размер прочитанных данных
viSize = (int) myReader.GetBytes(0,0,null,0,0);
byte[] bImage=new byte[viSize];
myReader.GetBytes(0,0,bImage,0,viSize);
//Переносим данные в поток
myStream =new MemoryStream(bImage, true);
//Отображаем данные из потока
Bitmap myBitmap =new Bitmap(myStream);
pictureBox1.Image = myBitmap;
}
catch(Exception ex1)
{
MessageBox.Show("Исключение:\n"+ex1.Message);
}
myStream.Close();
myReader.Close();
sqlConnection1.Close();
Если мы теперь поместим коды, приведенные выше, то можем посмотреть результат на Рис.6:
Рис.6 Отображение рисунка, прочитанного из базы данных
И последнее, если мы использовали предложение SQL:
sqlCommand1.CommandText="select * from images";
То следующий код позволяет получить значение поля 1 только в указанном месте:
myReader.Read(); //Здесь можно получить значение поля idimage int idtableimage = (int)myReader.GetValue(0); viSize = (int) myReader.GetBytes(1,0,null,0,0); byte[] bImage=new byte[viSize]; myReader.GetBytes(1,0,bImage,0,viSize); myStream =new MemoryStream(bImage, true); Bitmap myBitmap =new Bitmap(myStream); pictureBox1.Image = myBitmap; //Здесь мы получим исключение: //"Invalid attempt to read from column ordinal '0'. //With CommandBehavior.SequentialAccess, you may //only read from column ordinal '2' or greater. " int idtableimage = (int)myReader.GetValue(0);
5.2. DataSet и работа с двоичными данными
Перед началом рассмотрения вопроса восстановим или создадим проект, описанный в первом параграфе этого раздела ("Отображения данных в DataGrid"). При формировании sqlDataAdapter воспользуемся имеющимся подключением (можно и повторить формирование). Доходим до пункта запуска QueryBuilder и нажимаем кнопочку QueryBuilder. В QueryBuilder в табличке images (создана нами в предыдущем параграфе) выбираем все поля, выделяя их галочками или поставив галочку у чекбокса All Columns. Это поля idimage и pic.
В табличке QueryBuilder против поля idimage в строке state в столбце Criteria поставим =? - что означает подставляемый параметр.
Завершаем конфигурирование SqlDataAdapter, нажав Next и Finish.
Если мы посмотрим на предложение Select, например, в окне Properties, то увидим, что оно примет вид:
SELECT
idimage,
pic
FROM
images
WHERE
(idimage = @Param1)
То есть - у нас один параметр подставляемый, что может понадобится для выбора картинки по ее номеру(ключу) в табличке базы данных (это можно сделать аналогично выбора по штату в табличке authors - см. параграф "Отображение записей данных в текстовых полях").
Аналогично описанному выше сгенерируем DataSet, далее поместим на проект формы PictureBox и убедимся, что при попытке выполнить привязку его поля Image к полю pic набору данных DataSet (свойстве DataBinding, пункт Advanced) Advanced Data Binding откажется это выполнить. Если мы попытаемся выполнить связь программно, то получим исключение. Это связано с несовпадением между желательным типом данных (System.Drawing.Image) и содержимым поля рисунка (System.Byte[]).
В силу этого добавим новый формат данных, для отображения рисунка. Для этого в функции Form1_Load (двойной клик по форме) запишем следующий код:
private void Form1_Load(object sender, System.EventArgs e)
{
//У PictureBox есть свойство Image,
//для которого будет выполняться привязка
Binding myBinding = new Binding("Image",dataSet11,"images.pic");
//При его использовании будет происходить преобразование
//данных по законам указанным в СonvertImage
myBinding.Format += new ConvertEventHandler(СonvertImage);
//Добавляем привязку PictureBox к полю pic
pictureBox1.DataBindings.Add(myBinding);
}
Функцию преобразования данных при привязке определим как:
private void ConvertImage ( Object sender, ConvertEventArgs e )
{
Byte[] b = ( Byte[] ) e.Value;
MemoryStream myStream=null;
myStream = new MemoryStream( );
myStream.Write (b,0,b.Length );
Bitmap bmp = new Bitmap(myStream);
myStream.Close () ;
e.Value = bmp;
}
Для отображения данных осталось загрузить данными DataSet
switch(Convert.ToInt32(e.Button.Tag))
{
case 1:
dataSet11.Clear();
//Если нам надо первая картинка
sqlDataAdapter1.SelectCommand.Parameters[0].Value = 1;
sqlDataAdapter1.Fill(dataSet11.images);
break;
//Как и ранее можно листать рисунки
case 2:
BindingContext[dataSet11, "images"].Position +=1 ;
break;
Результат можно посмотреть на Рис.6.
Параграф 6. О сопоставлении между таблицей источника и DataTable
Как было сказано выше, для обмена данными между объектом DataSet и источником данных применяется класс DataAdapter. Из полезных свойств этого класса следует отметить TableMappings. TableMappings сопоставляет данные, запрошенные из таблицы базы данных с данными, которые передаются в таблицы DataSet.
Для уяснения механизма сопоставления воспользуемся проектом, использованным нами в начале раздела в параграфе "Отображения записей данных в DataGrid ". Повторим все шаги, выполненные нами при отображении данных за исключением:
При формировании DataAdapter при запуске QueryBuilder в таблице authors укажем лиш поля: au_id, au_lname, au_fname, phone.
Предложения Select примет вид:
SELECT au_id, au_lname, au_fname, phone FROM dbo.authors
Для DataGrid свойства DataSource и DataMember устанавливать пока не будем.
Теперь посмотрим код TableMappings в секции инициализации (функция InitializeComponent). Это же можно посмотреть и в диалоговом окне Table Mappings в свойствах адаптера, нажав рядом со свойством TableMappings кнопку с многоточием (Рис.7). Но пока нас интересует именно код:
this.sqlDataAdapter1.TableMappings.AddRange
(
new System.Data.Common.DataTableMapping[]
{
new System.Data.Common.DataTableMapping
(
"Table", "authors",new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("au_id", "au_id"),
new System.Data.Common.DataColumnMapping("au_lname", "au_lname"),
new System.Data.Common.DataColumnMapping("au_fname", "au_fname"),
new System.Data.Common.DataColumnMapping("phone", "phone")
}
)
}
);
Этот код можно трактовать следующим образом - данные из некоторой "Table" базы данных, заданной в операции Select будут помещены в DataSet в таблицу с именем "authors", а имена столбцов будут совпадать с именами из исходной таблицы.
Рис.7 Диалоговое окно Table Mappings
Как непосредственно в коде, так и в диалоговом окне мы можем редактировать удалять и добавлять столбцы источника или набора данных.
Кроме того, возможно задать и условия обработки ошибок сопоставления (свойства MissingMappingAction и MissingSchemaAction). Значение свойств могут означать:
| MissingMappingAction | MissingSchemaAction | Результат |
| Passthrough | Add или AddWithKey | Если столбец имеется в источнике, но для него нет сопоставления, столбец сопоставления создается при заполнении набора данных (по умолчанию). |
| Ignore | Ignore | Если столбец не определен в схеме набора данных, или для него отсутствует сопоставление, данные не копируются из источника в набор данных. |
| Error | Error | Если адаптер не может сопоставить исходный столбец со столбцом набора данных, поскольку сопоставление отсутствует или схема набора данных не содержит столбец формируется Runtime Error. |
В некоторых случаях имена в источнике данных и наборе данных могут быть заданы различные (например, когда набор данных создан из существующей схемы, в которой используются другие имена, или требуются отображение названий колонок в DataGrid на языке пользователя). Это можно выполнить добавив или изменив сопоставление, например, так:
this.sqlDataAdapter1.TableMappings.AddRange
(
new System.Data.Common.DataTableMapping[]
{
new System.Data.Common.DataTableMapping
(
"Table", "authors", new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("au_id", "au_id"),
new System.Data.Common.DataColumnMapping("au_lname", "au_lname"),
new System.Data.Common.DataColumnMapping("au_fname", "au_fname"),
new System.Data.Common.DataColumnMapping("phone", "phone")
}
)
,
new System.Data.Common.DataTableMapping
(
"Table1", "Авторы", new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("au_id", "Номер"),
new System.Data.Common.DataColumnMapping("au_lname", "Фамилие"),
new System.Data.Common.DataColumnMapping("au_fname", "Имя"),
new System.Data.Common.DataColumnMapping("phone", "Телефон")
}
)
}
);
Посколько TableMappings это массив, то мы просто выполнили добавление еще одного элемента. Теперь, следующий код при нажатии кнопки 1 выводит в DataGrid данные с исходными именами, а при нажатии кнопки 2 с русскими.
private void toolBar1_ButtonClick(object sender,
System.Windows.Forms.ToolBarButtonClickEventArgs e)
{
dataSet11.Clear();
switch(Convert.ToInt32(e.Button.Tag))
{
case 1:
try
{
sqlDataAdapter1.Fill(this.dataSet11,"Table");
dataGrid1.DataSource = dataSet11.DefaultViewManager;
dataGrid1.DataMember ="authors";
}
catch(Exception ex1)
{
MessageBox.Show("Исключение:\n"
+ex1.Message);
}
break;
case 2:
try
{
sqlDataAdapter1.Fill(this.dataSet11,"Table1");
dataGrid1.DataSource = dataSet11.DefaultViewManager;
dataGrid1.DataMember ="Авторы";
}
catch(Exception ex1)
{
MessageBox.Show("Исключение:\n"
+ex1.Message);
}
break;
default:
Application.Exit();
break;
}
}
Нетрудно задать сопоставление и программно:
System.Data.Common.DataTableMapping newMapping=
sqlDataAdapter1.TableMappings.Add("Table2", "Мои авторы");
newMapping.ColumnMappings.Add("au_id", "au_id");
newMapping.ColumnMappings.Add("au_lname", "lastname");
newMapping.ColumnMappings.Add("au_fname", "Имя");
newMapping.ColumnMappings.Add("phone", "Телефон");
dataSet11.Clear();
try
{
sqlDataAdapter1.Fill(this.dataSet11,"Table2");
dataGrid1.DataSource = dataSet11.DefaultViewManager;
dataGrid1.DataMember ="Мои авторы";
}
catch(Exception ex1)
{
MessageBox.Show("Исключение:\n"
+ex1.Message);
}
//Не забудем удалить сопоставление, чтобы иметь возможность повтора кода
sqlDataAdapter1.TableMappings.RemoveAt("Table2");
Результаты выполнения представлены на Рис.8 (слева при использовании сопоставления по умолчанию, в центре при использовании добавленного в код сопоставления и справа при использовании в case 2 последнего фрагмента кода).
Рис.8 Использование TableMappings
Параграф 7. Работа с несколькими связанными таблицами
Мы уже останавливались в параграфе "SqlDataReader и работа с несколькими наборами данных" на том, что sqlCommand позволяет произвести выбор данных из нескольких таблиц. Однако, наборы данных, возвращаемые sqlCommand, в этом случае, не связаны друг с другом, даже если они имеют в БД связи по ключам.
Для работы со связанными данными хорошо подходит класс DataSet, который позволяет хранить в кэше несколько таблиц с описанием связей между ними. И, кроме того, для связей данных в DataSet может быть использован класс DataRelation.
При использовании выборки данных из разных таблиц одним SQL предложением, учитывающим и связи по ключам, отображение данных и доступ к ним практически ничем не отличается от методов, которые мы рассмотрели при работе с одной таблицей.
Повторим создание проекта решения, описанного в параграфе "Отображения данных в DataGrid", до пункта вызова QueryBuilder при формировании DataAdapter. В нем после выбора таблицы authors выберем еще таблицу titleauthor. В верхнем окне QueryBuilder таблицы сами обозначали свою связь по ключевым полям au_id. Соответственно сформировалось и Select предложение (Рис.9).
Рис.9 QueryBuilder и работа со связанными по ключам таблицами.
Далее мы можем отображать данные в DataGrid или в текстовых (равно как и в элементах для отображения Blob объектов), точно также как и при отображении данных одной таблицы. Отметим только две особенности:
Все поля данных в DataSet стали одной таблицей;
Поле au_id (ключевое) в схеме данных только одно;
При формировании DataAdapter не формируются предложения Insert, Update и Delete.
Рассмотрим случай, когда таблицы загружаются раздельно.
В приведенном ниже примере мы будем создавать два объекта SqlDаtaAdapter для главной и подчиненной таблицы, два объекта DataColumn для связываемых колонок этих таблиц, затем создаем объект DataRelation, используя эти объекты в качестве параметров его конструктора и добавляем его к коллекции dataSet11.Relations.
Повторим создание проекта решения, описанного в параграфе "Отображения данных в DataGrid". Однако, на этот раз, мы поместим на форму последовательно два sqlDataAdapter. При помещении (конфигурировании первого адаптера), повторим все, что мы делали в первом параграфе (для наглядности отображения QueryBuilder в таблице authors были выбраны поля au_id, au_lname, au_fname, phone).
Аналогично для второго sqlDataAdapter выполним теже действия, выбрав таблицу titleauthor и поля au_id, au_ord, royaltyper. Мастер предупредит, что мы не выбрали одно из улючевых полей, но так как оно нам не нужно мы подтвердим его невключение. В результате в дизайнере формы у нас появятся два sqlDataAdapter и один sqlConnection.
Слудующий шаг - генерирование DataSet, которое выполняется для первого адаптера. Как и ранее выбираем в контексном меню sqlDataAdapter пункт Generate DataSet (тоже из меню Data) и запускаем мастер Generate DataSet. На первой же странице помечаем галочками обе таблицы для помещения в DataSet.
Для контрола DataGrid установим свойство DataSource в dataSet11 DataMember в authors.
Осталось добавить связь, которую выполним программно во время выполнения программы:
case 1:
//Загружаем таблицы
sqlDataAdapter1.Fill(this.dataSet11.authors);
sqlDataAdapter2.Fill(this.dataSet11.titleauthor);
//Выделяем ключевые столбцы
DataColumn datacolumnmaster=dataSet11.Tables["authors"].Columns["au_id"];
DataColumn datacolumnslave=dataSet11.Tables["titleauthor"].Columns["au_id"];
System.Data.DataRelation myRelation=
new DataRelation("AuthorsTitleauthor",datacolumnmaster,
datacolumnslave,true);
//Добавляем связь
dataSet11.Relations.Add(myRelation);
break;
Результат наших действий можно видеть на Рис.10:
Рис.10 Использоваение класса DataRelations.
Доступ к полям имеет также некоторые особенности, связанные с отношением таблиц, которые демонстрирует следующий код:
string s=null;
string s1=null;
foreach(DataRow datarowmaster in dataSet11.Tables["authors"].Rows)
{
//Данные из таблицы мастер
s = datarowmaster[1]+" "+datarowmaster[2]+" "+datarowmaster[3]+" ";
//Можно и так - если myRelation определен глобально
//foreach(DataRow datarowslave in datarowmaster.GetChildRows(myRelation))
foreach(DataRow datarowslave in datarowmaster.GetChildRows(dataSet11.Relations[0]))
{
//Добавляем значение из таблицы titleauthor
s1=s+" "+datarowslave["royaltyper"];
MessageBox.Show(s1);
}
}
Молчанов Владислав 11.03.2005г.
Перепечатка статьи без разрешения автора не допускается.
Еcли Вы пришли с поискового сервера - посетите мою главную страничку
На главной странице Вы найдете программы комплекса Veles - для тех кто готовится к экзаменам на право управления автомобилем или мотоциклом, программу NumberPhoto, созданную для работы с фото, сделанными цифровым фотоаппаратом, программу Bricks - игрушку для детей и взрослых, программу записную книжку, теоретический материал по программированию в среде Borland C++ builder, C# (C .Net).