返回一个SQL变量的值从在Oracle SQL查询返回.NET code [英] Return an sql variable's value from within an Oracle Sql Query back to .NET code
问题描述
在过去的几个小时，我试图做的最简单的简单的事情（至少对于SQL Server）在Oracle数据库中，通过使用ADO.NET .NET应用程序。这似乎是不可能的。
For the past few hours I am trying to do the simplest of the simple things (at least for SQL SERVER) in an Oracle Data Base, through a .NET application using ADO.NET. It seems impossible.
有关SQL SERVER我会做这个简单的任务，假设我有一个SqlCommand对象
For SQL SERVER I would do this simple task, supposing I have an SqlCommand object
comm.CommandText = @"
DECLARE @next_id INT
SET @next_id = (SELECT ISNULL(MAX(id_col),0) FROM TABLE_1) + 1
INSERT INTO TABLE_1 (id_col, col1, ...) VALUES (@next_id, val1, ...)
SELECT @next_id";
int id = Convert.ToInt32(comm.ExecuteScalar());
这将插入一个新的记录表TABLE_1，我会收回新的ID在C＃code中的ID变量。
That would insert a new record to table TABLE_1 and I would take back the new id in the "id" variable in c# code.
四个简单的步骤
- 在声明一个变量
- 将它设置为下一个可用的ID
- 将纪录新变量
- 返回变量的值
好，我成功地申报Oracle查询变量。此外，我（想）我设法给它一个值（用SELECT INTO）
Ok I managed to declare the variable in Oracle query. Also I (think) I managed to give it a value (With SELECT INTO)
我怎样才能拿回这个变量的值早在C＃中？如何选择一个变量的值在Oracle SQL输出流？
How can I get back this variable's value back in c#? How can i SELECT a variable's value to the output stream in Oracle SQL?
我知道有更好的方法来实现取回一个标识列，但是这不是这里的问题。它可能是一个完全不同的例子。这个问题很简单的：我已经宣布，将从在.NET应用程序中执行一个Oracle SQL脚本中的变量。 如何我可以得到变量的值回C＃，从Oracle查询？什么是上面的code等效与Oracle ADO.NET查询？
I know that there are better ways to achieve getting back an identity column, but that's not the question here. It could be a totally different example. The question is simple.: I have declared a variable inside an oracle sql script that will be executed from within .net app. How can i get the variable's value back to c#, from an oracle query? What is the above code's equivalent with Oracle ADO.NET query?
推荐答案
您需要使用的 ODP.NET （Oracle的Oracle数据访问组件）：
You'll want to use ODP.NET (Oracle's Oracle Data Access Components):
这方面的一个例子是下面。请注意，在ODP.NET，你可以建立一个参数方向（输入，输入 - 输出，输出，返回值）与您正在运行的程序或语句的参数对应。在这个例子中，我抓住一个返回值，这是由DB通过序列和触发器生成一个ID（其创建自动的尽可能的.NET应用程序而言）：
An example of this is below. Note that in ODP.NET, you can establish a parameters direction (input, inputoutput, output, returnvalue) to correspond with the parameters of the procedure or statement you're running. In this example, I'm grabbing a returnvalue, which is an ID that is generated by the db via a sequence and trigger (its created automagically as far as the .NET app is concerned):
int event_id = 0;
using (OracleConnection oraConn = new OracleConnection(connStr))
{
string cmdText = @"insert into EVENT
(EVENT_NAME, EVENT_DESC)
values
(:EVENT_NAME, :EVENT_DESC)
RETURNING EVENT_ID INTO :EVENT_ID
";
using (OracleCommand cmd = new OracleCommand(cmdText, oraConn))
{
oraConn.Open();
OracleTransaction trans = oraConn.BeginTransaction();
try
{
OracleParameter prm = new OracleParameter();
cmd.BindByName = true;
prm = new OracleParameter("EVENT_NAME", OracleDbType.Varchar2); prm.Value = "SOME NAME"; cmd.Parameters.Add(prm);
prm = new OracleParameter("EVENT_DESC", OracleDbType.Varchar2); prm.Value = "SOME DESC"; cmd.Parameters.Add(prm);
prm = new OracleParameter("EVENT_ID", OracleDbType.Int32, ParameterDirection.ReturnValue); cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery();
trans.Commit();
// return value
event_id = ConvertFromDB<int>(cmd.Parameters["EVENT_ID"].Value);
}
catch
{
trans.Rollback();
throw;
}
finally
{
trans.Dispose();
}
oraConn.Close();
}
}
的ConvertFromDB仅仅是一个通用的返回值转换为它的等效.NET（在这种情况下，一个int）。
The ConvertFromDB is just a generic to cast the return value to its .NET equivalent (an int in this case).
希望有所帮助。
编辑：
您可以轻松地绑定值的数组（并检索返回值的数组）的ODP.NET：
You can easily bind an array of values (and retrieve an array of return values) in ODP.NET:
using (OracleConnection oraConn = new OracleConnection(connStr))
{
string cmdText = @"insert into TEST_EVENT
(EVENT_NAME, EVENT_DESC)
values
(:EVENT_NAME, :EVENT_DESC)
RETURNING EVENT_ID INTO :EVENT_ID
";
using (OracleCommand cmd = new OracleCommand(cmdText, oraConn))
{
oraConn.Open();
OracleTransaction trans = oraConn.BeginTransaction();
try
{
string[] event_names = new string[2];
string[] event_descs = new string[2];
int[] event_ids = new int[2];
event_names[0] = "Event1";
event_descs[0] = "Desc1";
event_names[1] = "Event2";
event_descs[1] = "Desc2";
OracleParameter prm = new OracleParameter();
cmd.Parameters.Clear();
cmd.ArrayBindCount = 2;
cmd.BindByName = true;
prm = new OracleParameter("EVENT_NAME", OracleDbType.Varchar2); prm.Value = event_names; cmd.Parameters.Add(prm);
prm = new OracleParameter("EVENT_DESC", OracleDbType.Varchar2); prm.Value = event_descs; cmd.Parameters.Add(prm);
prm = new OracleParameter("EVENT_ID", OracleDbType.Int32, ParameterDirection.ReturnValue); cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery();
trans.Commit();
// get return values
event_ids = (int[])(cmd.Parameters["EVENT_ID"].Value);
}
catch
{
trans.Rollback();
throw;
}
finally
{
trans.Dispose();
}
oraConn.Close();
}
}
这篇关于返回一个SQL变量的值从在Oracle SQL查询返回.NET code的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
