Источник: habrahabr
Когда-то давно, очень давно...
Компания купила лицензию на БД Oracle. Затем в эту компанию трудоустроился я. Соответсвенно начав продвигать .Net "в массы". БД Oracle используются не во многих организациях, но используется.
Как же осуществить взаимодействие с БД Oracle, используя C#?
Оговорюсь заранее, я не гуру Oracle; а так же не гуру красивого использования паттернов, но я стараюсь и
Но оставим философствования. Займёмся поставленной задачей.
Перво-наперво необходимо подготовить "среду":
1. Поставить клиент Oracle, без которого взаимодействие с БД не осуществить.
2. Настроить TNS под свои нужды.
3. Создать проект в Visual Studio.
4. Добавить reference на сборку System.Data.OracleClient.dll, посредством которой и будем "манипулировать" БД.
Подготовим почву, пожалуй сделаем БД военкомата. В простейшем случае, для этого хватит одной таблицы:
create table CONscript_INFO (
ID NUMBER not null,
FIRST_NAME VARCHAR2(128),
LAST_NAME VARCHAR2(128),
AGES NUMBER,
GROWTH FLOAT,
BIOGRAPHY CLOB,
constraint PK_CONscript_INFO primary key (ID)
);
Идентификатор, Имя, Фамилия, Возраст, Рост, Биография. Вес нас не интересует.
Определим процедуры для добавления, на мой взгляд самое интересное, далее я объясню почему:
PROCEDURE ADD_CONscript
(FirstNameIn IN VARCHAR2, LastNameIn IN VARCHAR2,
AgesIn IN NUMBER, GrowthIn IN FLOAT, BiographyIn IN CLOB)
IS
BEGIN
INSERT INTO CONscript_INFO
(
ID,
FIRST_NAME,
LAST_NAME,
AGES,
GROWTH,
BIOGRAPHY
)
VALUES
(
CONscript_INFO_SEQ.NEXTVAL,
FirstNameIn,
LastNameIn,
AgesIn,
GrowthIn,
BiographyIn
);
END;Удаление:
PROCEDURE DELETE_CONscript
(ConscriptIDIn IN NUMBER)
IS
BEGIN
DELETE
FROM CONscript_INFO
WHERE ID = ConscriptIDIn;
END;Получение данных:
PROCEDURE GET_CONscriptS
(ConscriptsOut OUT sys_refcursor)
IS
BEGIN
OPEN ConscriptsOut FOR
SELECT *
FROM CONscript_INFO;
END;Теперь я объясню что интересного в процедуре добавления нового пользователя - объект типа CLOB. Это тип способен хранить строковые данные, размером до 4 гигабайт, в отличие от Varchar, способного оперировать строками до 4000 байт. Т.е. если вы попытаетесь создать VARCHAR поле размером 5000, то получите суровый "облом". Но те кто работал с типом CLOB из C# знают, что это очень по "челябенски". Но я забегаю вперёд, обо всём попорядку.
При работе с Oracle есть один неприятный момент, если явно не закрывать соединение, то количество курсоров будет после SELECT`a будет рости в геометрической прогрессии. Данная проблема решается "в лоб":
Создание и открытие соединения
using (OracleConnection connection = new OracleConnection())
{
...
}таким образом, за вас всю черновую работу сделает IDisposable.
Если обратить внимание на конструктор OracleConnection, то увидите что есть 2 варианта, "пустой" и с использованием строки, описывающей соединение с БД.
Строка имеет примерно такой вид:
Data Source=out_database_name;Password=our_password;User ID=our_username
Я думаю в объяснениях, что здесь что, данная строчка не нуждается.
Следующим шагом стоит открыть соединение:
connection.Open();Создание команды
Теперь мы готовы исполнять команду. После использования команды её тоже необходимо "освободить", не будем отходить от принципов и воспользуемся всё той же замечательной конструкцией using:
using (OracleConnection connection = new OracleConnection())
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "GET_CONscriptS";
}
}Давайте разберёмся что делает данный "кусок" кода: вначале создаётся команда, ей присваивается соединение, в рамках которого будет работать наша команда. Затем указывается тип команды, всего выделяется 3 типа:
1. StoredProcedure - Имя хранимой процедуры.
2. TableDirect - Имя таблицы.
3. Text - Текстовая команда SQL. (По умолчанию).
Следующий параметр - текст, непосредственно имя хранимой процедуры или команда. В нашем случае будет использована хранимая процедура с именем "GET_CONscriptS"
Отлично перейдём
Использование передаваемых параметров
Оговорюсь сразу, использовать параметры можно и с CommandType.Text, ниже я покажу как это делается.
Пока же вернёмся с нашим параметрам:
OracleParameter ConscriptsOut = new OracleParameter()
{
ParameterName = "ConscriptsOut",
Direction = System.Data.ParameterDirection.Output,
OracleType = OracleType.Cursor
};
command.Parameters.Add(ConscriptsOut);Что происходит здесь: создали параметр, указали что он работает на "выход" и указали тип параметра ( более подробную информацию о используемых типах а так же их совместимости со стандартными типами .Net можно узнать из MSDN ). Если бы параметр работал на "вход", то нужно было бы указать и значение - Value. Прикрепили параметр к команде и…
Выполнение команды
command.ExecuteNonQuery();команда выполнена… теперь можно "выгребсти" данные например так:
DataTable table = new DataTable();
table.Load(command.Parameters["ConscriptsOut"].Value as OracleDataReader);Возвращаемый параметр, представляет собой курсор, который можно прочитать, как OracleDataReader. Как разбирать параметр оставим в качестве "домашнего задания" ;).
На посошок
В заключении, я хочу рассказать про то как передать параметр в текст, а точнее я покажу:
using (OracleConnection connection = new OracleConnection())
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"SELECT *
FROM CONscript_INFO
WHERE AGES < :max_ages;";
OracleParameter maxAges = new OracleParameter()
{
ParameterName = "max_ages",
Direction = ParameterDirection.Input,
OracleType = OracleType.Number,
Value = 27
};
command.Parameters.Add(maxAges);
OracleDataReader reader = command.ExecuteReader();
...
}
}Как видите вся хитрость заключается в использовании знака - :.
В следующей статье я расскажу про работу с CLOB и приведу свой код, заточенный под меня.