Stored procedure with output parameters in linq to oracle

Stored procedure with output parameters in linq to oracle

Postby talsja » Tue 14 Jul 2009 11:29

Hello,

I am building a asp.net /C# webapplication with oracle as underlaying database. I use linq to oracle to access de oracle db. The whole application is making use of exsisting stored procedures with input and output parameters. My experiance with oracle is zero. So now my question is how to deal with these output parameters.

My sp has 29 parameters consisting of input and output parameters
string i_inputParameter, ref object o_OutputParameter.

When i use linq to oracle i have to set all 29 parameters and cannot set only the input parameters because it expects all 29. So how can i do this?

i use context.MyOracleStoredProcedure(i_inputparameter, o_outputparameter);


Thanx in atvance

Talsja
talsja
 
Posts: 7
Joined: Tue 14 Jul 2009 11:11

Postby AndreyR » Tue 14 Jul 2009 13:42

If you need the values of out parameters, you'll at least have to pass into the generated method some empty parameters to obtain the values in.
In case you don't need the values of out parameters, you can write a wrapper method in the Datacontext.cs file calling the generated method with the set of input parameters only (inside the wrapper you can call the generated method with dummy out parameters).
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby talsja » Tue 14 Jul 2009 13:56

AndreyR

Thanks for your replay. My stored procedure returns a recordset and i need the output parameters.

So what i have is a personmanager.
Here i have
public Object getPersons(int personID, string PersonName, ?, ?)
{
var context = new myDBContext()
{
}
}
talsja
 
Posts: 7
Joined: Tue 14 Jul 2009 11:11

Postby talsja » Tue 14 Jul 2009 13:58

sorry something went wrong

where was i

{
var myquery = context.myStoredProcedure(PersonID, PersonName, ?,?}
my sp has parameter ref object personID, ref object PersonName

so what do i fill in the question marks.
I need the output parameters
talsja
 
Posts: 7
Joined: Tue 14 Jul 2009 11:11

Postby AndreyR » Tue 14 Jul 2009 14:21

Let you have int outpar1 and object outpar2.
The simplest way is to execute something like this:
Code: Select all
{
int outpar1 = null;
object outpar2 = null;
var myquery = context.myStoredProcedure(PersonID, PersonName, ref outpar1, ref outpar2);
}

After the procedure is called, the variables outpar1 and outpar2 contain the values of out parameters.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby talsja » Wed 15 Jul 2009 07:27

Hi AndreyR,

Your example has solved my problem.
Thank u very much for sharing your knowledge.

Greatings Talsja
talsja
 
Posts: 7
Joined: Tue 14 Jul 2009 11:11

Postby Vijay » Wed 22 Jul 2009 19:09

I am having the same issue with object outparam. I am using the new release. Still getting error message saying "The parameter at index 0 in the parameters array is null". Any suggestions?

Thanks
Vijay
 
Posts: 3
Joined: Wed 22 Jul 2009 19:04

Postby AndreyR » Thu 23 Jul 2009 14:01

There were no fixes associated with LINQ and stored procedures in the current build.
Have you tried the workaround?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Vijay » Thu 23 Jul 2009 18:33

Thank you for the prompt reply.

1) Can you please direct me to the work around?

2) Is the fix for LINQ and Stored procedures scheduled in future builds? if so any indication of when that would be available?

Thanks
Vijay
 
Posts: 3
Joined: Wed 22 Jul 2009 19:04

Postby AndreyR » Fri 24 Jul 2009 08:33

Please show the code you are using for the stored procedure call.
Maybe the problem is not the one described in this post.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Vijay » Mon 27 Jul 2009 17:01

Code: Select all
       Dim fi As New FIEntities()
            Dim outpar As Object = Nothing
            Dim p = fi.AssocRead(outpar)
            Dim j As ASSOCIATE = p.FirstOrDefault()


This was the code I was using when I get the "parameters array is null" error. [/code]
Vijay
 
Posts: 3
Joined: Wed 22 Jul 2009 19:04

Postby AndreyR » Tue 28 Jul 2009 07:53

I have just tried to execute a stored procedure with the out cursor parameter and everything succeeded.
Please post here or send me (support * devart * com with subject "LINQ SP: Out parameters")
the script of the procedure and of the entity the procedure is mapped to.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle