c# 从 Oracle 存储过程返回结果集并填充数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1305214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
c# return resultset from Oracle store procedure and fill a DataTable
提问by m3ntat
I'm a c# SQL Server developer new to Oracle programming. The code below works for setting cmdText to: "select * from myTable". I'd like to now put this in a stored procedure, I need help with how to write this Oracle stored procedure and get the results into a DataTable in my c# code. Thanks. Code:
我是 ac# SQL Server 开发人员，不熟悉 Oracle 编程。下面的代码用于将 cmdText 设置为：“select * from myTable”。我现在想把它放在一个存储过程中，我需要关于如何编写这个 Oracle 存储过程并将结果放入我的 c# 代码中的 DataTable 的帮助。谢谢。代码：
private DbProviderFactory DbFactory
{
get
{
dbProviderFactory = dbProviderFactory ?? DbProviderFactories.GetFactory(providerInvariantName);
return dbProviderFactory;
}
}
public DataTable ExecDataTable(string cmdText, params IDataParameter[] cmdParams)
{
DataTable resultDT = new DataTable();
using (DbConnection dbConn = DbFactory.CreateConnection())
{
dbConn.ConnectionString = connectionString;
using (DbCommand dbCmd = DbFactory.CreateCommand())
{
dbCmd.CommandText = cmdText;
dbCmd.Connection = dbConn;
try
{
dbConn.Open();
if (cmdParams != null)
{
dbCmd.Parameters.AddRange(cmdParams);
}
using (DbDataAdapter dbDA = DbFactory.CreateDataAdapter())
{
dbDA.SelectCommand = dbCmd;
dbDA.Fill(resultDT);
}
}
finally
{
dbConn.Close();
}
}
}
return resultDT;
}
Note: connectionString, providerInvariantName are set previously in code.
注意：connectionString、providerInvariantName 是之前在代码中设置的。
Also any advice on refactoring my code is appreciatedthis is the approach I've taken to support ODP.net and also ODBC connections to Oracle which is required on site.
也感谢任何有关重构我的代码的建议，这是我为支持 ODP.net 以及现场所需的 Oracle ODBC 连接所采用的方法。
Update
更新
I can get this to working using:
我可以使用以下方法使其正常工作：
new Oracle.DataAccess.Client.OracleParameter("result", Oracle.DataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output);
However I can't seem to get a generic solution to work using DbParameter or IDataParameterhow do I do this to support ODBC and ODP.net?
但是，我似乎无法使用DbParameter 或 IDataParameter获得通用解决方案，我该如何执行此操作以支持 ODBC 和 ODP.net？
采纳答案by softveda
Do you really have to support ODBC. Otherwise just use only ODP.Net, it has optimised access to Oracle. The most efficient way to get data from Oracle is using ref cursors, you should learn to use it.
你真的必须支持ODBC。否则只使用 ODP.Net，它优化了对 Oracle 的访问。从 Oracle 获取数据的最有效方法是使用引用游标，您应该学会使用它。
create or replace
PROCEDURE SP_GET_TBL (o_rc OUT sys_refcursor) AS
open o_rc for
select Col1, Col2, Col3 from Tbl;
END SP_GET_TBL;
回答by adatapost
Set CommandType property,
设置 CommandType 属性，
dbCmd.CommandType=StoredProcedure;
Fill method opens and close database connection implicitly so no need to open & close database connection.
Fill 方法隐式打开和关闭数据库连接，因此无需打开和关闭数据库连接。
Use cmd.Parameter.AddWithValue()method to push both, parameter and value.
使用cmd.Parameter.AddWithValue()方法来推送参数和值。
