how to perform this in oracle and asp.net,c# code? RSS

• • Reply
  dotnetnepal

  None

  0 Points

  16 Posts

  how to perform this in oracle and asp.net,c# code?

  Jun 23, 2010 03:15 AM|dotnetnepal|LINK

  hello evrerybody..

  how to perform this query as a oracle strored procedure and call it from asp.net ,C# code and

  dataset using parameters?

  SELECT * FROM EMP where deptno like nvl(pDeptNo,'%') AND hiredate = pDate;

   

   

   

   

• • Reply
  sirdneo

  Star

  10444 Points

  2463 Posts

  Re: how to perform this in oracle and asp.net,c# code?

  Jul 08, 2010 08:28 AM|sirdneo|LINK

  I Generally create packages in Oracle rather than just SP, because they are easy to maintain here is sample:-

  CREATE OR REPLACE PACKAGE Lookup_Data_Pkg IS
  	TYPE Lookup_Data_Ref IS REF CURSOR;
  
  
  	PROCEDURE Get_Record(p_Lookup_Id       NUMBER,
  											 p_Lookup_Code     VARCHAR2,
  											 p_Lookup_Data_Ref OUT Lookup_Data_Ref);
  
  
  END;
  
  
  
  
  CREATE OR REPLACE PACKAGE BODY Lookup_Data_Pkg IS
  
  
  	PROCEDURE Get_Record(p_Lookup_Id       NUMBER,
  											 p_Lookup_Code     VARCHAR2,
  											 p_Lookup_Data_Ref OUT Lookup_Data_Ref) IS
  	BEGIN
  		OPEN p_Lookup_Data_Ref FOR
  			SELECT Ld.Lookup_Id, Ld.Description, 0 Selected
  			FROM   Lookup_Data Ld
  			WHERE  Lower(Ld.Lookup_Code) = Lower(p_Lookup_Code)
  			AND    (Ld.Active = 1 OR Ld.Lookup_Id = p_Lookup_Id)
  			ORDER  BY Ld.Sort_Order;
  	END;
  END;

  
  And in C# you can call it like this , note i m using enterprise library for this:-

   public static void GetRecord(decimal? LookupId, string LookupCode, out DataTable LookupDataRef)
          {
              OracleCommand command = null;
              try
              {
                  command = db.GetStoredProcCommand("LOOKUP_DATA_PKG.GET_RECORD") as OracleCommand;
                  command.Parameters.Add("P_LOOKUP_ID", OracleDbType.Decimal, LookupId, ParameterDirection.Input);
                  command.Parameters.Add("P_LOOKUP_CODE", OracleDbType.Varchar2, LookupCode, ParameterDirection.Input);
                  command.Parameters.Add("P_LOOKUP_DATA_REF", OracleDbType.RefCursor, ParameterDirection.Output);
                  DataSet dataSet = db.ExecuteDataSet(command);
                  LookupDataRef = dataSet.Tables[0];
              }
              finally
              {
                  if (command != null)
                  {
                      DBUtilitiy.disposeCommand(command);
                  }
              }
          }

  
  
  

  Thanks,
  Zeeshan Umar
  ~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~
• • Reply
  sirdneo

  Star

  10444 Points

  2463 Posts

  Re: how to perform this in oracle and asp.net,c# code?

  Oct 01, 2010 12:32 AM|sirdneo|LINK

  Is your problem solved, if yes then mark one or many posts as answer which helped you in solving your problem. Or if you figured out solution in a different way then kindly share the solution so it can be usefull for others.

  Thanks,
  Zeeshan Umar
  ~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~