Using Entity framework to call Oracle Stored Procedure in ASP.NET MVC3

This tutorial I will intro about how to using Entity framework to call Oracle Stored Procedure in ASP.NET MVC3. In this tutorial I use oracle database with username is SCOTT.

Requirements:

  • Oracle client 11g
  • ODAC 11.2.0.3 for visual studio.

you can download them from website of oracle.

1. Create a new ASP.NET MVC3 project with razor view engine and set named MvcApplications.EFCallOracleProcedure

image

2. Create a new Stored Procedure to return list of EMP

CREATE or REPLACE
PROCEDURE EMP_GETLIST
(
o_cursor out SYS_REFCURSOR
)
IS
BEGIN
open o_cursor for
SELECT * FROM EMP;
END EMP_GETLIST;

3. Create a new ADO.NET Entity Data Model and set named ScottModel under Models folder

image

Click Next button

image

Choose Generate  from database and click Next button

image

Click New Connection… and enter parameter to connect to database after that click OK button.

image

image

Click Next button

image

Click Finish.

4. Add Function Import

In ScottModel.Edmx right click and choose Model Browser. In Model Brower right click EMP_GETLIST so choose Add Function Import…

image

In Return a Collection Of choose Entities is EMP entity.

image

You can see function is imported in ScottModel.Designer.cs

image

5. Open web.config and add the following code in <configuration/> section

<oracle.dataaccess.client>
    <settings>
      <add name="SCOTT.EMP_GETLIST.RefCursor.o_cursor" value="implicitRefCursor bindinfo='mode=Output'" />
    </settings>
  </oracle.dataaccess.client>

6. Create a new action in Home controller

public ActionResult Emp()
        {
            using (ScottEntities context = new ScottEntities())
            {
                ObjectResult<EMP> emps = context.EMP_GETLIST();
                List<EMP> emplist = new List<EMP>();
                foreach (var emp in emps)
                {
                    emplist.Add(emp);
                }
                return View(emplist);
            }
        }

7. Create a view to show list of EMP

image

8. Build and run website

image

Happy coding!

Advertisements
This entry was posted in ASP.NET MVC. Bookmark the permalink.

4 Responses to Using Entity framework to call Oracle Stored Procedure in ASP.NET MVC3

  1. Nani says:

    Hi,
    Thanks for providing the solution. When i go through these steps, I am getting error like
    ORA-06550: line 1, column 8:
    PLS-00306: wrong number or types of arguments in call to ‘EMP_GETLIST’
    ORA-06550: line 1, column 8:
    PL/SQL: Statement ignored

    Here , I am using EF 5, VS11 and ODAC 11.2.03

    Thanks,
    Nani

  2. Ravi says:

    The problem is with the config entries. Generate the entries You can do that by using ODP.NET provided by Oracle.

    In Visual Studio Open Server Explorer/Data Connections//Procedures and select the Stored Procedure. Right Click and click Run.

    Check the “Select for Config” checkbox for the output cursor and click “Add Config”
    This will generate the Complex Type for the cursor in the Web.config.

    One more change:
    In Step 4 in this post, Select Complex instead of Entities and click Get Column Information. This will populate the window with the column definitions. Then click Create New Complex Type and then Ok.

    You should be good to go from here.

  3. lav says:

    Very help full article… Thanks…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s