| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
2006 г.
Руководство по работе с БД Firebird с использованием библиотеки ADO .Net 2.0Меркулов Андрей Александрович, http://www.ibprovider.com/
ПредисловиеВ данном обзоре будет описан один из способов работы с базой данных Firebird в среде .Net при помощи управляемого Ole Db провайдера. Несомненным преимуществом сервера баз данных Firebird является его бесплатность в сравнении с существующими, не очень дешевыми аналогами (прежде всего MS SQL Server и ORACLE). Firebird можно использовать для систем практически любого уровня, начиная от однопользовательских настольных приложений со встроенной базой данных (Embed Database), до клиент-серверных приложений уровня корпорации. Средства и технологии, используемые в статье:
Что такое OLE DB Provider?Для доступа к базам данных в ADO (а теперь эта возможность есть и в ADO.Net) используются OLE DB провайдеры. Ole Db Provider представляет собой драйвер для доступа к базе данных при помощи OLE DB интерфейсов. Для взаимодействия с OLE DB провайдером в .Net реализовано пространство имен System.Data.OleDb. При работе с Firebird я использую IBProvider (www.ibprovider.com) и в своем повествовании буду опираться, прежде всего, на его функциональность. Разработчики IBProvider поддерживают три коммерческих версии драйвера. Так же есть бесплатная. Для написания примеров применялся IBProvider третьей версии и только для примера управляющих ODBC последовательностей использовался IBProvider v2. Список основных различий в версиях IBProvider:
Наиболее современным решением является IBProvider третьей версии. В его основе лежит абсолютно новое ядро и в нем реализованы технологии управления данными, которые явились результатом 5-тилетних исследований в данной области, а так же вобрали в себя опыт разработки крупных программных проектов и библиотек доступа к данным. На момент написания статьи в третьей версии не была реализована технология обновляемых множеств, а так же не поддерживались управляющие последовательности ODBC. Но, насколько мне известно, поддержка ODBC Escape Sequences уже планируется разработчиками в ближайших версиях провайдера. Методы подключения к базе данныхПараметры строки подключенияДля использования Ole Db провайдера необходимо подключить соответствующее пространство имен к нашему проекту: using System.Data.OleDb; Управление подключением к Ole Db источникам данных осуществляется с помощью класса OleDbConnection. Самый простой способ подключения к базе данных – прямое указание строки подключения в конструкторе этого класса: OleDbConnection con = new OleDbConnection(connectionString); con.Open(); con.Close(); Для формирования строки подключения в Net 2.0 появился класс OleDbConnectionStringBuilder:
OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
cb.Provider = "LCPI.IBProvider";
cb.Add("Location",@"localhost:d:\Program Files\Firebird\examples\EMPLOYEE.FDB");
cb.Add("User ID", "sysdba");
cb.Add("Password", "masterkey");
cb.Add("ctype", "win1251");
Console.WriteLine(cb.ToString())
Существует определенный набор свойств инициализации IBProvider-a, который необходимо установить перед выполнением соединения с БД: Обязательные свойства инициализации (параметры подключения) IBProvider:
Некоторые необязательные свойства инициализации IBProvider:
Более подробно о свойствах инициализации IBProvider-а можно прочитать здесь
Способы хранения строк подключенияДля хранения параметров подключения в Windows существует специальный тип файлов Microsoft Data Link – это файл с расширением udl. С этим расширением ассоциирован универсальный редактор подключений. IBProvider поддерживает свои собственные табы, которые предоставляют удобный интерфейс для формирования параметров соединения. Для того чтобы использовать udl файл в своем приложении, выполните следующие шаги:
Теперь для того, чтобы использовать подключение, описанное udl файлом, достаточно явно или через OleDbConnectionStringBuilder задать свойство File Name OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder(); cb.FileName = AppDomain.CurrentDomain.BaseDirectory + @"\employee.udl"; OleDbConnection con = new OleDbConnection(cb.ToString()); con.Open(); Второй способ хранения строки подключения – это поместить её в конфигурационный файл приложения: В свойствах проекта выберите вкладку Settings и создайте новое свойство с именем ConnectionString и типом (Connection string):
При редактировании свойства запустится встроенный в VS 2005 редактор строки подключения:
Для того, чтобы прочитать строку подключения из файла конфигурации, необходимо создать экземпляр класса настроек вашего приложения: Properties.Settings s = new Properties.Settings(); //чтение свойства с именем ConnectionString Console.WriteLine(s.ConnectionString); Для облегчения написания примеров был создан класс ConnectionProvider, который инкапсулирует в себе все, описанные методы подключения. Шифрование строки подключения. Data Protection APIДопустим, нам необходимо зашифровать данные, хранящиеся в секции connectionStrings. Для этого мы воспользуемся классом DataProtectionConfigurationProvider: Подключим к нашему проекту сборку System.Configuration.dll и используем следующий код:
public void DataProtectionAPITest()
{
try
{
//открываем секцию connectionStrings из App.config
Configuration config = ConfigurationManager.OpenExeConfiguration(
System.Reflection.Assembly.GetExecutingAssembly().Location);
ConnectionStringsSection section =
config.GetSection("connectionStrings") as ConnectionStringsSection;
if (!section.SectionInformation.IsProtected)
{
// выполняем шифрование секции
section.SectionInformation.ProtectSection(
"DataProtectionConfigurationProvider");
// Сохраняем конфигурацию
config.Save();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// получаем строку подключения из зашифрованной секции
Console.WriteLine(Properties.Settings.Default.ConnectionString);
}
Данный пример как раз подходит для этих целей. При установке приложения мы помещаем в папку с программой ещё не зашифрованный App.Config. При первом запуске приложения данная процедура его зашифрует и, в последствии, программа будет работать уже с защищенной секцией. Можно так же вызывать процедуру шифрования во время установки приложения. К сожалению, нет стандартных средств для защиты UDL файлов, поэтому стоит воспользоваться способом хранения защищенных строк подключения в файле конфигурации. КомандыДля того чтобы выполнить запрос к базе данных необходимо выполнить следующую последовательность действий:
ExecuteScalarВозвращает единственное значение первой колонки первой строки. Остальные результаты игнорируются. Этот метод полезен для запросов, которые, к примеру, считают количество записей в таблице – соответственно возвращают только одно значение:
public void ExecuteScalarTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("select count(*) from employee", con, trans);
Console.WriteLine("Record count:" + cmd.ExecuteScalar().ToString());
trans.Commit();
con.Close();
}
ExecuteReaderНавигация по строкам результирующего множества осуществляется при помощи метода Read(), который возвращает true в случае, если ещё остались строки и false в противном случае. При вызове метода команды ExecuteReader(), созданный им объект OleDbDataReader не спозиционирован на первой строке результирующего множества и для её прочтения необходимо сначала вызвать метод Read(). Наиболее удобным способом чтения данных из результирующего множества является использование метода Read() совместно с конструкцией while:
public void ExecuteReaderTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
//Испольуем метод CreateCommand для создания команды
OleDbCommand cmd = con.CreateCommand();
cmd.Transaction = con.BeginTransaction();
cmd.CommandText = "select * from employee";
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//чтение данных
while (rdr.Read())
{
string tmp ="";
for(int i=0; i<rdr.FieldCount -1;i++)
{
if (tmp != "") tmp += "\t";
tmp += rdr[i].ToString();
}
Console.WriteLine(tmp);
}
rdr.Close();
//после вызова OleDbDataReader.Close() подключение к БД будет закрыто
Assert.AreEqual(ConnectionState.Closed,con.State);
}
ExecuteNonQuery
public void ExecuteNonQueryTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
//INSERT
OleDbCommand cmd = new OleDbCommand(
"insert into country (country,currency) values(:country,:currency) ",
con, trans);
cmd.Parameters.AddWithValue("country", "Russia");
cmd.Parameters.AddWithValue("currency", "Kopec");
// количество обработанных строк
Assert.AreEqual(1, cmd.ExecuteNonQuery());
//UPDATE
cmd = new OleDbCommand(
"update country set currency=:currency where country =:country", con, trans);
cmd.Parameters.AddWithValue("currency", "Rouble");
cmd.Parameters.AddWithValue("country", "Russia");
// количество обработанных строк
Assert.AreEqual(1, cmd.ExecuteNonQuery());
//DELETE
cmd = new OleDbCommand(
"delete from country where country =:country", con, trans);
cmd.Parameters.AddWithValue("country", "Russia");
// количество обработанных строк
Assert.AreEqual(1, cmd.ExecuteNonQuery());
trans.Commit();
con.Close();
}
Параметры командВ большинстве случаев при выполнении команды требуется задать её параметры. Параметры добавляются в коллекцию Parameters. Они могут быть именованные и позиционные. Пример команды с позиционными параметрами: insert into country (country,currency) values(?,?) С именованными: insert into country (country,currency) values(:country,:currency) IBProvider сам умеет формировать список параметров, производя анализ SQL выражения. Но, к сожалению, в ADO .Net необходимо вручную добавлять эти параметры, т.к. команда не запрашивает их описание у Ole Db провайдера. Если вспомнить ADO, то в нем список параметров прекрасно формировался без необходимости вмешиваться в этот участок кода. Для того, чтобы добавить параметр, нужно воспользоваться:
Если не указан тип параметра, он будет добавлен с Ole Db типом VarWChar, что соответствует .Net типу string, что кажется разумным. Об этом не стоит беспокоиться, т.к. IBProvider корректно обрабатывает приведение любых типов Firebird. Нельзя не сказать о существующих ограничениях при использовании именованных параметров совместно с OleDbCommand. В MSDN написано, что именованные параметры поддерживаются только для поставщиков данных MSSQL и Oracle, а для поставщиков данных Ole Db и ODBC поддерживаются только позиционные параметры. Использовать именованные параметры все же можно, но их добавление в коллекцию Parameters необходимо осуществлять в том же порядке, в каком они следуют в запросе. К примеру, если текст команды: update country set currency=:currency where country =:country то сначала необходимо добавить параметр currency, а потом country:
cmd.Parameters.AddWithValue("currency", "Rouble");
cmd.Parameters.AddWithValue("country", "Russia");
Задавать значения параметров можно уже в произвольном порядке: cmd.Parameters["country"].Value = "Latvia"; cmd.Parameters["currency"].Value = "Lat"; Вызов хранимых процедур
Для первого способа используется обычная SQL-инструкция: select * from stored_procedure_name(…) Результат её выполнения обрабатывается при помощи объекта OleDbDataReader:
public void StoredProcedureResultSetTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
//select stored procedure in params
OleDbCommand cmd_in_params =
new OleDbCommand("select cust_no from CUSTOMER", con, trans);
//select mail label through the stored procedure
OleDbCommand cmd_stored_proc =
new OleDbCommand("select * from mail_label(:cust_no)", con, trans);
//add one IN parameter
cmd_stored_proc.Parameters.Add("cust_no", OleDbType.Integer);
//execure reader
using (OleDbDataReader rdr = cmd_in_params.ExecuteReader(CommandBehavior.CloseConnection))
{
//for each customer No
while (rdr.Read())
{
cmd_stored_proc.Parameters["cust_no"].Value = rdr["cust_no"];
using (OleDbDataReader rdr_out = cmd_stored_proc.ExecuteReader())
{
Console.WriteLine("Customer №" + rdr["cust_no"]);
while (rdr_out.Read())
for (int i = 0; i < rdr_out.FieldCount; i++)
Console.WriteLine(rdr_out.GetName(i) + "=" + rdr_out[i]);
Console.WriteLine();
}
}
}
}
Второй способ – вызов хранимой процедуры через инструкцию: execute procedure stored_procedure_name Результат выполнения помещается в OUT параметры команды, которые предварительно необходимо создать:
public void StoredProcedureOUTParamsTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
//select in params
OleDbCommand cmd_in_params =
new OleDbCommand("select cust_no from CUSTOMER", con, trans);
//STORED PROCEDURE
OleDbCommand cmd_stored_proc =
new OleDbCommand("execute procedure mail_label(:cust_no)", con, trans);
//IN parameter
cmd_stored_proc.Parameters.Add("cust_no", OleDbType.BSTR);
//OUT parameters
cmd_stored_proc.Parameters.Add("line1", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
cmd_stored_proc.Parameters.Add("line2", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
cmd_stored_proc.Parameters.Add("line3", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
cmd_stored_proc.Parameters.Add("line4", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
cmd_stored_proc.Parameters.Add("line5", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
cmd_stored_proc.Parameters.Add("line6", OleDbType.BSTR)
.Direction = ParameterDirection.Output;
//execure reader
using (OleDbDataReader rdr = cmd_in_params.ExecuteReader())
{
//for each customer No
while (rdr.Read())
{
cmd_stored_proc.Parameters["cust_no"].Value = rdr["cust_no"];
cmd_stored_proc.ExecuteNonQuery();
Console.WriteLine("Customer №" + rdr["cust_no"]);
Console.WriteLine(cmd_stored_proc.Parameters["line1"].Value);
Console.WriteLine(cmd_stored_proc.Parameters["line2"].Value);
Console.WriteLine(cmd_stored_proc.Parameters["line3"].Value);
Console.WriteLine(cmd_stored_proc.Parameters["line4"].Value);
Console.WriteLine(cmd_stored_proc.Parameters["line5"].Value);
Console.WriteLine(cmd_stored_proc.Parameters["line6"].Value);
Console.WriteLine("");
}
}
trans.Commit();
con.Close();
}
Работа с массивамиСледующий пример демонстрирует чтение и запись массива из 5 элементов:
public void ArrayReadWriteTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(
"select job_code, job_grade, job_country, job_title, language_req from job",
con, trans);
OleDbCommand cmd_upd = new OleDbCommand(
"update job set language_req=:language_reg where \n" +
"job_code=:job_code and job_grade=:job_grade and job_country=:job_country",
con, trans);
cmd_upd.Parameters.Add("language_req", OleDbType.Variant);
cmd_upd.Parameters.Add("job_code", OleDbType.BSTR);
cmd_upd.Parameters.Add("job_grade", OleDbType.BSTR);
cmd_upd.Parameters.Add("job_country", OleDbType.BSTR);
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine("JOB TITLE:" + rdr["job_title"].ToString());
//чтение массива
object lang_obj_arr = rdr["language_req"];
if (lang_obj_arr != DBNull.Value)
{
//преобразование к массиву
//используем Array.CreateInstance для создания массива
//из 5 элементов, с адресацией начиная с 1-го элемента, а не с 0
short arr_lower_bound = 1;
Array lang_str_arr = Array.CreateInstance(typeof(string), new int[] {5}, new int[] { arr_lower_bound });
//копирование элементов в массив
((Array)lang_obj_arr).CopyTo(lang_str_arr, arr_lower_bound);
for (int i = arr_lower_bound; i < lang_str_arr.Length + arr_lower_bound; i++)
{
//усечение символа \n на концах элементов массива
string trimmed_value =
lang_str_arr.GetValue(i).ToString().Replace("\n", "");
lang_str_arr.SetValue(trimmed_value, i);
//вывод значения
if (lang_str_arr.GetValue(i).ToString() != "")
Console.WriteLine(lang_str_arr.GetValue(i));
}
//запись новых значений элементов массива без символа \n
cmd_upd.Parameters["language_req"].Value = lang_str_arr;
cmd_upd.Parameters["job_code"].Value = rdr["job_code"];
cmd_upd.Parameters["job_grade"].Value = rdr["job_grade"];
cmd_upd.Parameters["job_country"].Value = rdr["job_country"];
//передача изменений в БД
Assert.IsTrue(cmd_upd.ExecuteNonQuery() == 1);
}
else
Console.WriteLine("No language specified");
Console.WriteLine("");
}
}
//откат сделанных изменений
trans.Rollback();
con.Close();
}
Работа с BLOB полямиIBProvider поддерживает работу с двумя типами BLOB полей: содержащих текст и бинарные данные. Не могу не заметить, что при использовании этого провайдера работа с BLOB полями происходит так же, как и с обычными типами данных:
public void BLOBReadWriteTest()
{
OleDbConnection con = ConnectionProvider.CreateConnection();
con.Open();
OleDbTransaction trans = con.BeginTransaction();
//BLOB Read command
OleDbCommand cmd = new OleDbCommand(
"select proj_id, proj_name,proj_desc from project", con, trans);
//BLOB write command
OleDbCommand cmd_update = new OleDbCommand(
"update project set proj_desc=:proj_desc where proj_id=:proj_id", con, trans);
//create parameters with BSTR type
cmd_update.Parameters.Add("proj_desc", OleDbType.BSTR);
cmd_update.Parameters.Add("proj_id", OleDbType.BSTR);
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//чтение BLOB
Console.WriteLine("PROJECT: " + rdr["proj_name"].ToString());
Console.WriteLine(rdr["proj_desc"].ToString());
//запись BLOB
cmd_update.Parameters["proj_id"].Value = rdr["proj_id"];
//каждый раз меняем регистр данных в BLOB поле
string new_project_description = rdr["proj_desc"].ToString();
if (new_project_description.ToUpper() != new_project_description)
new_project_description = new_project_description.ToUpper();
else
new_project_description = new_project_description.ToLower();
cmd_update.Parameters["proj_desc"].Value = new_project_description;
Assert.AreEqual(1, cmd_update.ExecuteNonQuery());
}
}
trans.Commit();
con.Close();
}
Здесь тип параметров команды обновления установлен в OleDbType.BSTR. В этом случае провайдер корректно распознает тип параметров и произведет их преобразование к типам базы данных.
MARS - Multiple Active Result SetsВ ADO .Net 2.0 появилась «новая» технология, которая получила название MARS. В Net Framework 1.1. в одном контексте транзакции было невозможно держать открытый OleDbDataReader и параллельно выполнять дополнительные запросы к базе данных или открывать ещё один OleDbDataReader. При попытке выполнить этот трюк мы получали исключение вида: «There is already an open DataReader associated with this Connection which must be closed first.» Предыдущий пример работы с BLOB полями как раз и показывает применение технологии MARS. В нем демонстрируется последовательное чтение данных и их одновременное их обновление. Если обратиться к истории, то мы обнаружим, что технология эта совсем не новая, да и технологией назвать это сложно. Если сравнить вторую версию ADO .Net с первой, то, конечно, разработчики добились определенных успехов. Но возможность использовать несколько RecordSet в одной транзакции была реализована ещё в классическом ADO. Скажу больше, там можно было использовать несколько RecordSet, связанных с одной командой. Это достигалось за счет клонирования команды внутри себя, если обнаруживалось, что уже есть связанное с ней множество. В ADO .NET команда тоже умеет клонировать саму себя. Для этого есть метод Clone(), который необходимо вызывать явно, если вы хотите связать несколько OleDbDataReader с одной командой. Таким образом, применение MARS возможно не только для MS SQL Server, как пишут во многих источниках информации, но и для других баз данных.
|
 | |||||||||
| |||||||||
|
CITForum © 1997–2025