CODEWITHSUNDEEP
.netoracle-databaseentity-frameworkstored-proceduresodp.net
ahmed naguib
ahmed naguib

Reputation: 145

How to call a stored procedure in Oracle from .net framework?

I am working on an Asp.net MVC web application dealing with an Oracle database, and I have a statistics dashboard page.

I am using Entity Framework code-first with ODP.Net, but I am facing a performance issue on this page because Entity Framework queries are so complex.

So I have to convert my Entity Framework queries to stored procedures.

For example: I created the following stored procedure in Toad 

CREATE OR REPLACE PROCEDURE Schema.DashBoardStatistics 
    (
     -- Add the parameters for the stored procedure here
     p_YearId number, cur OUT SYS_REFCURSOR
    )
AS
BEGIN
    -- select top 5 Fields By rating Rating 
    Open cur FOR Select * FROM   Schema."Logs" where Schema."Logs"."Id"= p_YearId ;

END;
/

How to add another select to this Sp to return a dataset to my code?

And how can I call this from my code and receive this dataset in model classes?

Notice : i want to use entity framework query to execute stored procedure not ADO.NET To be able to map return dataset to my entities

Thanks!

Upvotes: 0

Views: 2494

Answers (1)

Christian Shay
Christian Shay

Reputation: 2635

See the example in the middle of this walkthrough:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/2015/entityframework_linq_modelfirst/Entity%20Framework%20LINQ%20and%20Model%20First.html

The key is that you need a config file for the REF CURSOR metadata. You should install Oracle Developer Tools for Visual Studio and then "Run" the stored procedure using the menu off Server Explorer. This will give you an option to auto generate the required config file that contains the metadata for the ref cursor.

You then follow the walkthrough to fill in the Import Function dialog which maps the SP to a Entity Function.

Upvotes: 1

Related Questions