从 asp.net 应用程序调用 oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4677324/
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
calling oracle stored procedure from asp.net application
提问by Ankit
in my asp.net application, i am calling a stored procedure (oracle) to get some values from database.
在我的 asp.net 应用程序中，我正在调用一个存储过程（oracle）来从数据库中获取一些值。
Following is the sp:
以下是sp：
create or replace PROCEDURE GetUserData(
--SQLWAYS_EVAL# ARCHAR(100)
UserName IN NVARCHAR2, v_refcur OUT SYS_REFCURSOR)
as
BEGIN
BEGIN --SQLWAYS_EVAL# =@Password;
open v_refcur for SELECT StaffId,
UserName,
Password,
Pin,
LastUpdateId,
LastUpdateDate,
FullName,
PinFailedAttempts,
PinFailedDate
FROM UserData
WHERE UserName = UserName;
END;
RETURN;
END;
Can anyone help me how to call this sp from my asp.net code.
任何人都可以帮助我如何从我的 asp.net 代码中调用这个 sp。
采纳答案by vc 74
Using ODP, you'll can do something like the following:
使用 ODP，您可以执行以下操作：
make your stored procedure a function that takes the user name in parameter and returns a refcursor
使您的存储过程成为一个函数，该函数在参数中采用用户名并返回一个引用
create or replace FUNCTION GetUserData(UserName IN NVARCHAR2) RETURN SYS_REFCURSOR;
and then
进而
using (var connection = new OracleConnection(connectionString))
{
using (var command = new OracleCommand("GetUserData", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
// Return value parameter has to be added first !
var returnValueParameter = new OracleParameter();
returnValueParameter.Direction = ParameterDirection.ReturnValue;
returnValueParameter.OracleDbType = ParameterDirection.RefCursor;
command.Parameters.Add(returnValueParameter);
var userNameParameter = command.Parameters.Add("UserName", userName);
returnValueParameter.Direction = ParameterDirection.In;
using (OracleDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Read the current record's fields
}
}
}
}
回答by tawman
The Microsoft Enterprise Library simplifies the discovery and binding of Oracle Stored Procedures. It is not too difficult to build a Data Access Layer between your Business Objects and the Oracle database. I am more a fan of ORM tools these days like DevExpress's XPO, which in the latest release supports calling stored procedures. However, the Microsoft Entlib is free whereas DevExpress is not.
Microsoft Enterprise Library 简化了 Oracle 存储过程的发现和绑定。在您的业务对象和 Oracle 数据库之间构建数据访问层并不难。我现在更喜欢像 DevExpress 的 XPO 这样的 ORM 工具，它在最新版本中支持调用存储过程。但是，Microsoft Entlib 是免费的，而 DevExpress 不是。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Your.BusinessObjects;
namespace DataAccess
{
public class UserDataDAL
{
public static Database dataBase = DatabaseFactory.CreateDatabase(); ///< Use default connection string configured in web.config
public static List<UserInfo> GetData(string userName)
{
List<UserInfo> listOfUserInfo = new List<UserInfo>();
UserInfo userInfo;
DbCommand cmd = dataBase.GetStoredProcCommand("SCHEMA.GETUSERDATA");
dataBase.DiscoverParameters(cmd);
dataBase.SetParameterValue(cmd, "USERNAME", userName);
using (IDataReader dr = dataBase.ExecuteReader(cmd))
{
while (dr.Read())
{
userInfo = new UserInfo();
userInfo.StaffId = dr["STAFFID"] != DBNull.Value ? Convert.ToInt32(dr["STAFFID"]) : 0;
userInfo.UserName = dr["USERNAME"] != DBNull.Value ? Convert.ToString(dr["USERNAME"]) : String.Empty;
userInfo.Password = dr["PASSWORD"] != DBNull.Value ? Convert.ToString(dr["PASSWORD"]) : String.Empty;
userInfo.LastUpdateId = Convert.ToInt32(dr["LASTUPDATEID"]);
userInfo.LastUpdateDate = dr["LASTUPDATEDATE"] != null ? Convert.ToDateTime(dr["LASTUPDATEDATE"]) : new DateTime();
listOfUserInfo.Add(userInfo);
}
}
return listOfUserInfo;
}
}
}
If you only ever expect one row to be returned from the procedure, then you can return the first item in the list if present etc.
如果您只希望从过程中返回一行，那么您可以返回列表中的第一项（如果存在）等。
