CODEWITHSUNDEEP
c#oracle-databasestored-procedures
Jake
Jake

Reputation: 604

Issues calling Oracle stored procedure from C#

This is the first time that I have ever had to call an Oracle stored procedure from C# and I don't know why my code is not working. All that I am trying to do is return a datatable. My stored procedure compiles just fine - here is some pseudocode describing it:

CREATE OR REPLACE PROCEDURE SPROC_ONLINE
( Year IN NUMBER DEFAULT NULL
, Name IN VARCHAR2 DEFAULT NULL
, ID IN VARCHAR2 DEFAULT NULL
, refCursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN refCursor FOR
SELECT *
FROM TABLE
WHERE Field_Year = Year
  AND Field_Name = Name
  AND Field_ID = ID;  
END SPROC_ONLINE;

Here is some pseudocode describing my C#:

public static DataTable search(int? Year, string Name, string ID)
    {            
        try
        {
            OracleConnection conn = getConnectionString();                
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SPROC_ONLINE";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("Year", OracleDbType.Int32).Value = Year;
            cmd.Parameters.Add("Name", OracleDbType.Varchar2).Value = Name;
            cmd.Parameters.Add("ID", OracleDbType.Varchar2).Value = ID;                
            cmd.Parameters.Add("Output", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

            DataTable dt = new DataTable();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            getConnectionString().Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);

            return dt;                
        }
        catch (Exception e) { throw new Exception("Error: " + e.Message); }
        finally { getConnectionString().Close(); }
    }

No errors are being thrown; it's just that the datatable that is returned is null when it should not be (the parameters that I am testing with should be returning something).

What am I doing wrong here?

Upvotes: 0

Views: 576

Answers (2)

Jake
Jake

Reputation: 604

My issue ended up being two-fold; here is the code-behind that I ended up with:

public static DataTable search(int? Year, string Name, string ID)
{
    try
    {
        using (var cmd = new OracleCommand("SPROC_ONLINE", getConnectionString()))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            var da = new OracleDataAdapter(cmd);
            cmd.Parameters.Add("Year", OracleDbType.Int32).Value = Year;
            cmd.Parameters.Add("Name", OracleDbType.Varchar2).Value = Name;
            cmd.Parameters.Add("ID", OracleDbType.Varchar2).Value = ID;
            cmd.Parameters.Add("Output", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
    catch (Exception e) {throw new Exception("Error: " + e); }
    finally {}
}

My other issue was that I did not realize that string comparisons in Oracle were case-sensitive. Apparently between compiling my stored procedure in SQL Developer and trying to test it in Visual Studio, I was not consistently entering the "Name" parameter; this explains why a null datatable would be returned. I adjusted the WHERE clause of my stored procedure as follows:

WHERE Field_Year = Year
  AND UPPER(Field_Name) = UPPER(Name)
  AND Field_ID = ID;

Everything works fine now.

Upvotes: 0

Ingenioushax
Ingenioushax

Reputation: 718

I think you may need to use OracleDataReader.

using(OracleCommand cmd = new OracleCommand("SPROC_ONLINE", TheConnection))
{
    using(OracleDataReader reader = new OracleDataReader())
    {
        while(reader.Read())
        {
            // Extract the values
            var a = reader["Year"];
            var b = reader["Name"];
            var c = reader["ID"];
            ... etc ...
        }
    }
}

Upvotes: 1

Related Questions