Calling stored procedure with cursor parameter

[JerryCho]

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 ... _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.

[AndreyR]

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.

[galmond]

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?

[AndreyR]

Yes, this method is correct.
Take a look at our blog post here:
http://www.devart.com/blogs/dotconnect/?p=5/