Работа с базой данных в .Net

Статьи -> Программирование

Работа с базой данных в .Net

v:1.0 28.02.2007

Вся информация для этой статьи взята из Microsoft MSDN Library for Visual Studio 2005.
Работать будем с СУБД Oracle, а программу будем писать на языке C#.

Часть 1

Вступление

.NET Framework предоставляет набор классов для работы с СУБД Oracle.
Эти классы содержатся в пространстве имен System.Data.OracleClient, в сборке System.Data.OracleClient.dll.
Чтобы использовать эти средства, в проект необходимо включить System.Data.dll и System.Data.OracleClient.dll,
а текст программы надо добавить пространства имен:

using System.Data;
using System.Data.OracleClient;

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

Первым шагом работы с базой данных является - установка соединения. Для этой цели предназначен класс OracleConnection, входящий в пространство имен System.Data.OracleClient. Для установки соединения необходимо определить "Строку соедиения" - это указание с какой базой устанваливать соедиение и какими должны быть параметры этого соединения. Строка может быть, например, такой:

string vConnectionString; vConnectionString = "Data Source="+DBname.Text+ ";User Id="+UserName.Text+ ";Password="+UserPassword.Text+ ";Pooling=false";

Где Data Source - имя базы данных, User Id - имя пользователя, Password - пароль пользователя. А вот о параметре Pooling стоит сказать особо. Установка этого параметра позволяет устранить появление ошибки: 'ResourcePool' is not supported on the current platform. Ошибка эта весьма странная и почему появляется не понятно. В моей практике был случай, когда стабильно работавшая программа почему-то стала выдавать такую ошибку. Точную причину выявить не удалось, на каком-то форуме подсказали установить параметр Pooling, ошибка пропала.
"Строку соедиинения" в класс можно передать двумя способами или как аргумент конструктора или как свойство ConnectionString.
Давайте посмотрим, что получилось:

private void connectionUp() { OracleConnection vConnection = new OracleConnection(); string vConnectionString; vConnectionString = "Data Source="+DBname.Text+ ";User Id="+UserName.Text+ ";Password="+UserPassword.Text+ ";Pooling=false"; vConnection.ConnectionString = vConnectionString; try { vConnection.Open(); } catch (Exception msg) { MessageBox.Show(msg.ToString(), "Ошибка соединения", MessageBoxButtons.OK, MessageBoxIcon.Error); } }

Я формирую "строку соединения", используя данные, полученные через форму. Потом инициализирую свойство ConnectionString и устанавливаю соединение. Т.к. этот процесс чреват множеством ошибок, желательно вставить обработчик исключений.
После запуска функции можно посмотреть результат. Для этого можно воспользоваться, например, SQL Navigator'ом.
Запрос:

select * from v$session where UserName='имя пользователя'

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

Давайте рассмотрим еще один интересный член класса OracleConnection. Свойство State - состояние соединение. Например, чтобы убедиться, что соединение действительно установлено, можно использовать следующий код:

if (vConnection.State == ConnectionState.Open) MessageBox.Show("Соедиение установлено", "Сообщение", MessageBoxButtons.OK,MessageBoxIcon.Information);

Обратите внимание, Miscosort советует самостоятельно закрывать соединение после того как оно больше не нужно, не надеяться на сборщик мусора. Это можно сделать методом Close.
Рассмотрим пример:

vConnection.Close(); if (vConnection.State == ConnectionState.Closed) MessageBox.Show("Соедиение не установлено", "Сообщение", MessageBoxButtons.OK, MessageBoxIcon.Information);

Я закрыл соединение и проверил его статус.

Обработка исключительных ситуаций

В работе любой программы могут появиться ошибки. Под ошибками я подразумеваю события, не предусмотренные логикой программы. Например, это могут быть некорректно введенные данные. В нашем случае такими данными могут быть имя пользователя и пароль. Собственно, в возникновении таких ситуаций нет ничего страшного, если разработчик предусмотрел адекватную реакцию на них. В примере выше для отлова этих ситуаций я использую стандартный механизм обработки исключений try - catch (Exception msg). При неправильном вводе пароля вывалится здоровая портянка с кучей текста, увидев этот "мусор", пользователь, скорее всего, подумает что программа сглючила. Чтобы придать сообщению об ошибке более осознанный вид, можно строку

MessageBox.Show(msg.ToString(), "Ошибка соединения", MessageBoxButtons.OK, MessageBoxIcon.Error);

заменить на что-нибудь, вроде:

MessageBox.Show("Имя пользователя/пароль введены с ошибкой", "Ошибка соединения", MessageBoxButtons.OK, MessageBoxIcon.Error);

Однако в этом случае при появлении любой ошибки будет выводиться одно и то же сообщение, что, конечно же, не верно. Лучшей практикой является определение, какая именно ошибка появилась и что делать с этой ошибкой. При работе с СУБД Oracle наиболее простым является анализ кода, возвращаемого СУБД. Значение этого кода можно получить, используя класс OracleException. Для его использования надо следующим образом исправить блок try-catch:

try { vConnection.Open(); } catch (OracleException msg) { if (msg.Code == 1017) { MessageBox.Show("Неверное имя пользователя/пароль", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Information); } else MessageBox.Show(msg.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Information); }

Я анализирую код ошибки и, если он равен 1017 (ошибка идентификации пользователя), вывожу соответствующее сообщение. Если произошла какая-то другая ошибка, сработает обработчик по умолчанию (в этом примере блок else). Используя этот подход можно предусмотреть реакцию программы на различные ошибки, которые могут появиться в ходе работы с базой данных.

Часть 2

Рассмотрим подробнее параметр Pooling, входящем в строку соединения с базой. Если не установить этот параметр в false, то может появиться неприятная ошибка 'ResourcePool' is not supported on the current platform.
Давайте разберемся, что же это за параметр такой.

В MSDN написано, что если установить значение 'true' или 'yes' (а это значения по умолчанию), то объект, т.е. соединение с базой данных, будет извлечен из пула соединений или, если там его еще нет, будет создан и добавлено в пул.

Итак, мы столкнулись с интересным понятием - пул соединений с базой данных (Connection Pool).

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

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

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

За управление пулом соединений отвечает Driver Manager. Соединение извлекается из пула, когда приложение выполняет OracleConnection.Open() и возвращается обратно в пул, когда соединение закрывается OracleConnection.Close().

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

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

Обратите внимание, в MSDN красной строкой подчеркивается, что нельзя вызывать OracleConnection.Close() или удалять любой другой управляемый объект из стандартного метода класса Finalize. В методе Finalize можно закрывать только неуправляемые объекты, если таковых нет, то это метод вообще не нужен в классе.

Настроить параметры пула можно через свойство ConnectionString класса OracleConnection. Среди наиболее интересных можно отметить - Connection Lifetime (TimeOut соедиения) и Max Pool Size (максимальное количество соединений в пуле), ну и, конечно, Pooling (помещать соедиение в пул или нет).

В самых общих чертах я рассмотрел, что такое пул соединений с базой данных. А что касается ошибки, то это может быть следствием некорректных настроек как со стороны клиента .Net, так и на стороне Oracle (там тоже есть механизм "пулирования"), с эти предстоит еще разобраться.

Часть 3

Выборка из базы.

В .Net многие действия можно сделать разными способами и выборка данных не исключение. Например, выборку можно реализовать следующим образом: извлечь данные с помощью класса OracleDataReader в коллекцию и работать с ней. А можно воспользоваться интересным классом DataGridView. Класс DataGridView я сейчас рассмотрю.

Обратите внимание на примечание в MSDN: Этот класс новый, появился в .NET Framework начиная с версии 2.0.

Класс DataGridView наиболее полезен, если данные извлекаются в первую очередь для отображения пользователю.

Итак, помещаем на форму контрол System.Windows.Forms.DataGridView и называем его PrjList. При запуске приложения он отобразится как List (таблица).

В конструктор формы прописываем следующий код инициализации:

BindingSource bindingSource1 = new BindingSource(); try { bindingSource1.DataSource = GetData("Select PrjType as Тип, PrjName as Имя, "+ "PrjPriority as Приоритет from tPrjm_refrencePrj"); PrjList.DataSource = bindingSource1; } catch (OracleException msg) { MessageBox.Show(msg.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }

BindingSource - класс, осуществляющий привязку данных, он задает источник, откуда возьмутся данные для отображения в контроле. В моем случае это выборка из таблицы tPrjm_refrencePrj. Обратите внимание на конструкцию as… в выборке, так я задаю имена столбцов для отображения в контроле.

Теперь осталось только описать метод GetData. Он может быть таким.

private static DataTable GetData(string vCommandStr) { string vConnectionString = "Data Source=" + "<Имя базы>" + ";User Id=" + "<Пользователь>" + ";Password=" + "<Пароль>"; OracleConnection vConnection = new OracleConnection(vConnectionString); OracleCommand vCommand = new OracleCommand(vCommandStr,vConnection); OracleDataAdapter adapter = new OracleDataAdapter(); adapter.SelectCommand = vCommand; DataTable vTable = new DataTable(); DataColumn vColumn; // ColumnName and add to DataTable. vColumn = new DataColumn(); vColumn.DataType = System.Type.GetType("System.Int32"); vColumn.ColumnName = "Тип"; vTable.Columns.Add(vColumn); // ColumnName and add to DataTable. vColumn = new DataColumn(); vColumn.DataType = System.Type.GetType("System.String"); vColumn.ColumnName = "Имя"; vTable.Columns.Add(vColumn); // ColumnName and add to DataTable. vColumn = new DataColumn(); vColumn.DataType = System.Type.GetType("System.Int32"); vColumn.ColumnName = "Приоритет"; vTable.Columns.Add(vColumn); vTable.Locale = System.Globalization.CultureInfo.InvariantCulture; adapter.Fill(vTable); return vTable; }

Я делаю следующее:

1. Устанавливаю соединение с базой (OracleConnection).
2. Создаю команду выборки (OracleCommand).
3. Создаю DataAdapter. Это представление набора команд, данных и соединения с базой данных, предназначено для заполнения данными класса DataSet и обновления базы.
4. Создаю DataTable - представление таблицы данных в памяти, таким образом я задаю форму выдачи данных на экран. Если это не сделать то по умолчанию DataGridView покажет таблицу, полученную командой select, причем имена столбцов возьмутся из конструкции as…. С помощью DataTable я настраиваю DataGridView нужным мне образом. Установка DataTable не представляет особых затруднений, нужно лишь описать необходимые столбцы, задав имя и тип данных.
5. После того как DataTable готова, ее можно заполнить, используя OracleDataAdapter.

Выбранные данные, будут автоматически переданы в DataGridView и выведены в форму.

Описанный выше способ выборки и отображения данных работоспособен, но как-то немного коряв. Например, имена столбцов должны задаваться и в функции select через as… и при описании DataTable. Если опустить описание в DataTable, то имена отобразятся БОЛЬШИМИ БУКВАМИ, что не очень красиво. Кроме того, если структура таблицы изменится, то придется дополнительно изменять описание DataTable, что опять же не гибко. Можно, конечно, задавать имена столбцов коллекцией и потом вычитывать их при необходимости. Но должен быть и более элегантный способ.
Давайте его искать…

Часть 4

Выше я разбирал приемы работы с компонентом .Net Framework DataGridView. Я отметил, что предложенный способ имеет ряд существенных недостатков. Сейчас я хочу вам предложить более интересное решение.

Итак, помещаем в форму компонент DataGridView и называем его PrjList. Вызываем окно свойств (Properties) этого элемента и открываем свойство Columns категории Misc. Появится диалоговое окно Edit Columns. Нажимаем кнопку Add… и заполняем свойства каждой колонки. Здесь нас интересуют следующие параметры: HeaderText - видимое название колонки, Width - ширина колонки и самый интересный параметр DataPropertyName - этот параметр определяет из какого столбца выборки будет заполнен это столбец таблицы DataGridView. Таким образом мы связываем таблицу базы данных и ее отражение в форме. Ниже предлагаю текст программы.

public partial class ReferencePrj : Form { //Соедиение с базой OracleConnection conn; //Механизм заполения DataSet OracleDataAdapter productsAdapter; // DataSet Кэш данных в оперативной памяти DataSet productsDataSet; public ReferencePrj() { InitializeComponent(); Connect(); FillList(); } private void Connect() { try { //Строка соедиения string connectionString = "Data Source=" + "<имя базы данных>" + ";User Id=" + "<имя пользователя>" + ";Password=" + "<пароль пользователя>"; //Установление соедиения conn = new OracleConnection(connectionString); conn.Open(); } // обработка исключений catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void FillList() { try { //создание OracleDataAdapter productsAdapter = new OracleDataAdapter(); //выборка из базы productsAdapter.SelectCommand = new OracleCommand("Select PrjType, PrjName, "+ "PrjPriority from tPrjm_refrencePrj", conn); //cоздание кэша данных productsDataSet = new DataSet(); //заполнение выбранными данными кэша productsAdapter.Fill(productsDataSet,"tPrjm_RefrencePrj"); //связывания кэша данных и компанента визуализации. PrjList.DataSource = productsDataSet; PrjList.DataMember = "tPrjm_RefrencePrj"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }

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

Описанный выше способ выборки и отображения данных вполне можно применять на практике.

Часть 5

В предыдущей части мы рассмотрели как создавать соединение с базой, как извлекать из нее данные и выводить их на экран. При этом во главу угла ставилась максимальная скорость разработки приложения, возможно, со снижением производительности.
Ниже я покажу, как еще больше упростить разработку приложения для СУБД.

Предположим, что шаблон стандартного windows-приложения создан и перед вам готовая форма. Помещаем на эту форму объект DataGridView для вывода данных пользователю и две кнопки, одну - сохранение изменений DataGridView, другую для выборки данных из базы.
Рассмотрим код формы.

public partial class ReferencePrj : Form { OracleDataAdapter sRefPrjAdapter; OracleCommandBuilder sRefPrjCommandBuilder; DataSet sRefPrjDataSet; //Конструртор формы public ReferencePrj() { InitializeComponent(); Init(); FillList(); } //Инициализация "инфраструктурных классов" private void Init() { try { sRefPrjAdapter = new OracleDataAdapter("Select PrjType, PrjName, " + "PrjPriority from tPrjm_refrencePrj", "Data Source=" + "<имя базы>" + ";User Id=" + "<имя пользователя>" + ";Password=" + "<пароль пользователя>" ); sRefPrjCommandBuilder = new OracleCommandBuilder(sRefPrjAdapter); sRefPrjDataSet = new DataSet(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } //Выборка из базы и вывод данных на экран private void FillList() { try { sRefPrjDataSet.Clear(); sRefPrjAdapter.Fill(sRefPrjDataSet, "tPrjm_RefrencePrj"); PrjList.DataSource = sRefPrjDataSet; PrjList.DataMember = "tPrjm_RefrencePrj"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } //Обработка события нажатия на кнопку "Выборка из базы" private void select_but_Click(object sender, EventArgs e) { FillList(); } //Обработка события нажатия на кнопку "Сохранить изменения в базу" private void commit_but_Click(object sender, EventArgs e) { sRefPrjAdapter.Update(sRefPrjDataSet,"tPrjm_RefrencePrj"); } }

Классы OracleDataAdapter и DataSet я уже рассматривал. Обратите внимание на способ инициализации OracleDataAdapter, в этот раз объект соединения с базой данных не создается как самостоятельная единица. Строка соединения передается непосредственно в OracleDataAdapter.

Сейчас давайте разберемся с классом OracleCommandBuilder. Этот класс разработан для совместного использования с DataSet, его назначение - автоматическое формирование запросов к базе данных, для переноса изменений из DataSet в базу. Строчка
sRefPrjCommandBuilder = new OracleCommandBuilder(sRefPrjAdapter);
именно это и делает, т.е. создает набор запросов для отображения изменений в sRefPrjAdapter. OracleCommandBuilder умеет создавать запросы только для одной таблицы.
Обратите внимание еще на одну особенность автоматического формирования запросов. Если для таблицы не определен первичный ключ, то запрос delete не будет сформирован автоматически и его надо будет добавить вручную, определив свойство DeleteCommand, класс OracleDataAdapter. Если же таблица содержит первичный ключ (а как же иначе!), то автоматически созданный запрос insert не будет работать должным образом (откуда OracleCommandBuilder знает правило формирования ключа?). Вариант переложить задачу задания первичного ключа на плечи пользователя я не рассматриваю как реальную. Поэтому, надо предпринять дополнительные меры, описанные в статье "Automatically Generating Commands" MSDN или самому инициализировать свойство insert класса OracleDataAdapter, мне кажется, что это более правильное решение.

Остальная часть приведенного листинга интуитивно понятна и не требует пояснений. Связывание DataGridView источником данных я рассматривал раньше. А действие функции
sRefPrjAdapter.Update(sRefPrjDataSet,"tPrjm_RefrencePrj");
весьма прозрачно - это сохранение в базу, всего, что было изменено.

Используя средства автоматизации .Net можно очень быстро получить простейшее приложение для просмотра и редактирования содержимого базы данных. Фактически, описанное выше приложение создает в форме "зеркало" таблицы базы данных, все изменения в "зеркале" переносятся в базу. Однако, если говорить о нормальной работе с базой, то у автоматического подхода "по умолчанию" появляются существенные недостатки. Оптимальным видится использование класса OracleDataAdapter с ручным заданием команд update, insert, delete. Такой подход позволяет и ускорить разработку, избежав рутинных операций, и написать эффективные надежные запросы.

Петрелевич Сергей
petrelevich@yandex.ru

Метки: Oracle   .Net  

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

Аноним	Sep 4, 2010 7:12:32 PM
Мне очень понравился ваш блог, большое спасибо что вы есть!
Аноним	Sep 4, 2010 11:13:12 PM
Отличная статья, большое спасибо!Обожаю ваш блог!!!
Аноним	Jun 19, 2010 6:40:30 PM
Спасибо за отличную статью ;) Прочитал с удовольствием.

Пожалуйста авторизуйтесь или зарегистрируйтесь.