Calling stored procedure with cursor parameter

Calling stored procedure with cursor parameter

Postby JerryCho » Thu 28 Jan 2010 18:16

Hi~

I'm writing a C# code with dotConnect for Oracle.
There is a stored procedure with cursor.

At first, I created datacontext following this link.
http://www.devart.com/dotconnect/oracle/articles/tutorial_linq.html

Then, I added only one stored procedure.
DataContext Class was generated.

mmm.. This is my code.
Code: Select all
   EDatacontext dc;

   dc = new EDatacontext();
   dc.Connection.Open();
   
   OracleParameter p;
   p = new OracleParameter("v_cursor", OracleDbType.RefCursor);
   OracleDataAdapter oda = new OracleDataAdapter();
   
   DataSet ds = new DataSet();

   System.Data.Linq.ISingleResult sr = dc.DsGetUserinfo(p.Value, "demo01", "companyid");
   oda.Fill(ds, (OracleRefCursor)p.Value);


But I couldn't get any data.

Can anybody show a example code?
I want call stored procedure with datacontext. not using OracleCommand.

Thank you.
JerryCho
 
Posts: 1
Joined: Thu 28 Jan 2010 18:05

Postby AndreyR » Fri 29 Jan 2010 12:21

Here is a short tutorial. Let me have a procedure like this:
Code: Select all
   PROCEDURE get_employee(cur OUT sys_refcursor) IS
   BEGIN
      OPEN cur FOR
    SELECT *
    FROM scott.emp
    ORDER BY empno;
   END get_employee;

So, I add this procedure as a method to the context (dragging it to the designer surface).
And call it like in the following code snippet:
Code: Select all
using (DataContext1.DataContext1 db = new DataContext1.DataContext1()) {
  var q = db.GetEmployee();
  List list = q.ToList();
}

The list variable contains the list of employees.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby galmond » Tue 16 Mar 2010 15:09

When i drag a similar procedure onto the designer a method is created that requires me to pass the output parameter to the method in my C# - I can fix this by deleting the parameter within the designer but is this the correct method?
galmond
 
Posts: 1
Joined: Tue 16 Mar 2010 15:04

Postby AndreyR » Wed 17 Mar 2010 11:54

Yes, this method is correct.
Take a look at our blog post here:
http://www.devart.com/blogs/dotconnect/?p=5/
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle