calling oracle stored procedure from asp.net application

Question

in my asp.net application, i am calling a stored procedure (oracle) to get some values from database.

Following is the 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.

What provider are you using to connect to Oracle? Are you using ODP.net or the System.Data.OracleClient Namespace? — Jagmag
– Jagmag, Commented Jan 13, 2011 at 7:11

vc 74 · Accepted Answer · 2011-01-13 08:34:32Z

Using ODP, you'll can do something like the following:

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
        }
     }
  }
}

tawman · Accepted Answer · 2011-01-13 15:38:28Z

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.

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.

EntLib has a 'hidden' feature here. If your OUT SYSREFCURSOR parameter is named cur_out, this code will work without the call to DiscoverParameters.

Collectives™ on Stack Overflow

calling oracle stored procedure from asp.net application

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related