В данной статье рассмотрим подключение к Oracle, выполнение sql-запросов, хранимых процедур и функций.

Обращение к Oracle из asp.net возможно как средствами Oracle (odac), так и средствами Microsoft (System.Data.OracleClient). Но Microsoft заявила о прекращении поддержки OracleClient. В .NET Framework 4.0 компонент помечен как нежелательный, в более новых версиях по словам представителей Microsoft компонента не будет совсем. Поэтому рассмотрим именно Oracle Data Provider.

Сперва должен быть установлен Oracle и odac (который включает в себя Oracle Data Provider for .NET ). Ну и естественно потребуется .NET Framework и Visual Studio. Приведённые примеры тестировались в Oracle 11g, .NET Framework 3.5, Visual Studio 2008, Windows 2003 Server Enterprise Edition SP2.

Первым делом должен быть установлен сам Oracle. Далее устанавливаем odac.
Во избежании проблем, желательно устанавливать на англоязычную версию Windows. Если всё же ставите на русскую, то путь установки не должен содержать кириллицу, имя windows-пользователя – тоже.

Далее в Visual Studio создадим новый проект: Visual c# - Web – ASP NET Web Application, назовём его testasp.

Теперь займёмся Oracle. Работать будем через Oracle SQL Developer (не через asp.net), так как всё что нужно, в нём имеется и не требуется настройки odp.net в Visual Studio, по объёму работ которой можно написать целую статью и даже не одну.

Создадим web-пользователя, например "webuser", пароль "usrweb". Создать его можно как в SQL Developer (клик правой кнопкой мыши на Other Users – Create User), так и в web-интерфейсе - https://имя_компьютера:1158/em

Если https://имя_компьютера:1158/em не работает, проверьте службу OracleDBConsoleorcl. При проблемах с запуском данной службы добавьте в файл hosts (C:\WINDOWS\system32\drivers\etc\) строку вида – ip_адрес имя_компьютера.

В моём случае это –
[PHP]
https://shop:1158/em

Файл hosts:

192.168.1.2 shop
[/PHP]

Теперь в web.config добавим строку подключения –
[PHP]
<connectionStrings>
<add name="oracle_conn" connectionString="DATA SOURCE=;PASSWORD=usrweb;PERSIST SECURITY INFO=False;USER ID=WEBUSER" providerName="Oracle.DataAccess.Client" />
</connectionStrings>
[/PHP]

где "oracle_conn" – имя строки подключения.

Первым параметром указываем источник данных. Если база одна (по умолчанию orcl), то данный параметр оставляем пустым, если несколько, то указываем нужный (можно взять конкретный "data source alias" из файла tnsnames.ora). Второй параметр – пароль, значение третьего - false (не сохранять конфиденциальную информацию о подключении), четвёртый – имя пользователя, пятый – имя провайдера.

Далее в Visual Studio в меню выбираем "Project – Add Reference", ищем в открывшемся окне Oracle.DataAccess, жмём ok.
Теперь в Class View в Project References можем увидеть подключенный Oracle.DataAccess.

Далее в секцию <pages> добавляем неймспейсы -
[PHP]
<namespaces>
<add namespace="System.Web.Configuration"/>
<add namespace="Oracle.DataAccess.Client"/>
<add namespace="Oracle.DataAccess.Types"/>
<add namespace="System.Data"/>
</namespaces>
[/PHP]

Как видно из примера, дополнительно к оракловским неймспейсам указываем ещё два -
System.Web.Configuration – в данном случае это доступ к строке подключения, находящейся в web.config и
System.Data – ado.net (указание типов параметров - хранимая процедура / sql-запрос, их направление - входящие/исходящие и т.д.).

Можно конечно обойтись без неймспейсов, но гораздо удобней писать в коде -
[PHP]
CommandType.StoredProcedure
OracleDbType.RefCursor
[/PHP]

нежели полностью перечислять -
[PHP]
System.Data.CommandType.StoredProcedure
Oracle.DataAccess.Client.OracleDbType.RefCursor
[/PHP]

Установка сборки.

Открываем web.config, в секцию <assemblies> добавляем -
[PHP]<add assembly="Oracle.DataAccess" />[/PHP]

Копируем Oracle.DataAccess.dll в папку bin нашего проекта.

Также есть второй способ.

После установки odac, Oracle.DataAccess автоматически помещается в GAC (Global Assembly Cache) по адресу C:\WINDOWS\assembly\. Если по каким-то причинам его там не оказалось, то можно зарегистрировать вручную –


В моей конфигурации gacutil.exe находится в папке -

Флаг –i означает install (установку) сборки.

Теперь в папке GAC (C:\WINDOWS\assembly\) напротив Oracle.DataAccess смотрим зачения Public Key Token = 89b483f429c47342, Version = 2.111.6.20, Culture = neutral (если данное значение не указано, значит neutral). У вас эти параметры могут отличаться.

После этого в секцию <assemblies> добавляем наши данные -
[PHP]
<add assembly="Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
[/PHP]

Теперь нет необходимости копировать Oracle.DataAccess.dll в папку bin каждого проекта, достаточно лишь добавить сборку через web.config.

На этом процесс подключения окончен.

Выполнение sql-запросов, процедур и функций.

Рассмотрим несколько вариантов - простой запрос из asp.net, вызов хранимой процедуры с возвращаемым курсором, вызов хранимой процедуры с входящим (IN) и выходящим (OUT) параметрами, вызов функции с возвращаемым значением (Return).

Сперва в SQL Developer создадим пакет процедур и функций (packages). В одном пакете можно объединить несколько процедур и функций, связанных каким-либо общим назначением, что довольно удобно. Назовём его "test", создадим курсор (ref cursor), объявим две процедуры – getusers(), chk() и функцию retnum().
[PHP]
create or replace PACKAGE TEST AS

TYPE outcursor IS REF CURSOR;

PROCEDURE getusers(paramout OUT outcursor);

PROCEDURE chk(paramin IN DECIMAL, paramout OUT DECIMAL);

FUNCTION retnum(x DECIMAL) RETURN DECIMAL;

END;
[/PHP]
Далее создадим тело пакета (body package) и реализуем объявленное –
[PHP]
create or replace PACKAGE BODY TEST AS

PROCEDURE getusers(paramout OUT outcursor) AS
BEGIN
OPEN paramout
FOR
SELECT login, address FROM users;
END getusers;

PROCEDURE chk(paramin IN DECIMAL, paramout OUT DECIMAL) AS
BEGIN
SELECT 10 + paramin
INTO paramout
FROM dual;
END chk;

FUNCTION retnum(x DECIMAL) RETURN DECIMAL AS
BEGIN
RETURN 10 + x;
END retnum;

END TEST;
[/PHP]

1. Простой запрос, создаваемый на asp.net-странице.

[PHP]
<%
OracleConnection conn = new OracleConnection();

conn.ConnectionString = WebConfigurationManager.ConnectionStrings["oracle_conn"].ConnectionString;

OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT login, address FROM users";

cmd.Parameters.Add("param", OracleDbType.Varchar2, ParameterDirection.Output);

conn.Open();

OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
%>
<span><%=reader.GetString(0).Trim()%></span>
<span><%=reader.GetString(1).Trim()%></span><br>
<%
}
reader.Close();
cmd.Dispose();
conn.Close();
%>
[/PHP]
2. Вызов хранимой процедуры getusers(). Присутствует единственный параметр – возвращаемый курсор.

[PHP]
<%
OracleConnection con = new OracleConnection();
con.ConnectionString = WebConfigurationManager.ConnectionStrings["oracle_conn"].ConnectionString;
OracleCommand cmnd = new OracleCommand("test.getusers", con);
cmnd.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter();
param.OracleDbType = OracleDbType.RefCursor;
param.Direction = ParameterDirection.Output;
param.ParameterName = "param";
param.Size = 4000;
cmnd.Parameters.Add(param);
con.Open();
OracleDataReader readers = cmnd.ExecuteReader();

while (readers.Read()) {
%>
<span><%=readers.GetString(0).Trim()%></span>
<span><%=readers.GetString(1).Trim()%></span><br>
<%
}
readers.Close();
cmnd.Dispose();
con.Close();
%>
[/PHP]
3. Вызов хранимой процедуры chk(). Присутствует два параметра, входящий в процедуру – "paramin" и возвращаемый из процедуры – "paramout".

[PHP]
SELECT 10 + paramin
INTO paramout
FROM dual;
[/PHP]
В качестве примера к входящему десятичному параметру прибавляем 10 и возвращаем результат.

Код на стороне asp.net -
[PHP]
<%
OracleConnection cn = new OracleConnection();
cn.ConnectionString = WebConfigurationManager.ConnectionStrings["oracle_conn"].ConnectionString;
OracleCommand cm = new OracleCommand("test.chk", cn);
cm.CommandType = CommandType.StoredProcedure;

OracleParameter paramin = new OracleParameter();
paramin.ParameterName = "paramin";
paramin.OracleDbType = OracleDbType.Decimal;
paramin.Direction = ParameterDirection.Input;
paramin.Size = 50;
paramin.Value = 5;
cm.Parameters.Add(paramin);

OracleParameter paramout = new OracleParameter();
paramout.ParameterName = "paramout";
paramout.OracleDbType = OracleDbType.Decimal;
paramout.Direction = ParameterDirection.Output;
paramout.Size = 50;
cm.Parameters.Add(paramout);

cn.Open();
cm.ExecuteNonQuery();
decimal result = (decimal)((OracleDecimal)(cm.Parameters["paramout"].Value)).Value;
Response.Write(result.ToString());
cm.Dispose();
cn.Close();
%>
[/PHP]

SqlDataSource.

Сделаем два первых варианта с помощью SqlDataSource. Сам по себе этот компонент не предназначен для работы с Oracle и разрабатывался специально для MS SQL Server, поэтому его использование нежелательно.

а) Первый запрос у нас достаточно прост и его можно создать прямо в дизайнере Visual Studio, перетащив на форму и настроив SqlDataSource-компонент, также добавим GridView.
[PHP]
<asp:GridView ID="DirectGrid" runat="server" DataSourceID="DirectSQL">
</asp:GridView>

<asp:SqlDataSource ID="DirectSQL" runat="server"
ConnectionString="<%$ ConnectionStrings:oracle_conn %>"
ProviderName="<%$ ConnectionStrings:oracle_conn.ProviderName %>"
SelectCommand="SELECT [LOGIN], [ADDRESS] FROM [USERS]"></asp:SqlDataSource>
[/PHP]
б) Со вторым запросом и хранимой процедурой всё гораздо сложнее. Visual Studio генерирует неверный с точки зрения Oracle код. Имя процедуры вида "TEST#0#GETUSERS" будет ошибочным, поэтому вручную его изменяем на "TEST.GETUSERS". Далее нам нужен параметр ref cursor, которого естественно нет в данном компоненте. Но выход есть, открываем codebehind(*.cs) и дописываем следующие строки –
[PHP]
protected void UserSourse_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
((Oracle.DataAccess.Client.OracleParameter)e.Command.Parameters[0]).OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor;
}
[/PHP]
Ну а дальше добавляем GridView для показа полученных данных и SqlDataSource.
[PHP]
<asp:GridView ID="UserTable" runat="server" DataSourceID="UserSourse">
</asp:GridView>

<asp:SqlDataSource ID="UserSourse" runat="server"
ConnectionString="<%$ ConnectionStrings:oracle_conn %>"
ProviderName="<%$ ConnectionStrings:oracle_conn.ProviderName %>"
SelectCommand="TEST.GETUSERS" SelectCommandType="StoredProcedure"
onselecting="UserSourse_Selecting">
<SelectParameters>
<asp:Parameter Name="param" Direction="Output" />
</SelectParameters>
</asp:SqlDataSource>
[/PHP]
Более полный вид codebehind –
[PHP]
namespace testasp
{
public partial class _Default : System.Web.UI.Page
{

protected void UserSourse_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
((Oracle.DataAccess.Client.OracleParameter)e.Command.Parameters[0]).OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor;
}

protected void Page_Load(object sender, EventArgs e)
{

}
}
}
[/PHP]

Функции.

Функция должна возвратить какое-либо значение, соответственно первым параметром будет ParameterDirection.ReturnValue. Важно отметить, что в данном случае в коде программы возвращаемый параметр объявляется первым, иначе получим исключение –
[PHP]ORA-22060: аргумент [{0}] является недопустимым или неинициализированным целым[/PHP]

[PHP]
<%
OracleConnection ccn = new OracleConnection();
ccn.ConnectionString = WebConfigurationManager.ConnectionStrings["oracle_conn"].ConnectionString;
OracleCommand ccm = new OracleCommand("test.retnum", ccn);
ccm.CommandType = CommandType.StoredProcedure;

OracleParameter paramf = new OracleParameter();
paramf.ParameterName = "paramf";
paramf.OracleDbType = OracleDbType.Decimal;
paramf.Direction = ParameterDirection.ReturnValue;
paramout.Size = 50;
ccm.Parameters.Add(paramf);

OracleParameter paramc = new OracleParameter();
paramc.ParameterName = "paramc";
paramc.OracleDbType = OracleDbType.Decimal;
paramc.Direction = ParameterDirection.Input;
paramc.Size = 50;
paramc.Value = 10;
ccm.Parameters.Add(paramc);

ccn.Open();

ccm.ExecuteNonQuery();
decimal res = (decimal)((OracleDecimal)(ccm.Parameters["paramf"].Value)).Value;
Response.Write(res.ToString());

ccm.Dispose();
ccn.Close();

%>
[/PHP]
Вот собственно и всё по основам подключения.

© Автор Alcorn.

Замечание модератора: Эта тема была закрыта автоматически ввиду отсутствия активности в ней на протяжении 100+ дней. Если Вы считаете ее актуальной и хотите оставить сообщение, то воспользуйтесь кнопкой или обратитесь к любому из модераторов.