Page 1 of 1
Stored procedure with output parameters in linq to oracle
Posted: Tue 14 Jul 2009 11:29
by talsja
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
Posted: Tue 14 Jul 2009 13:42
by AndreyR
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).
Posted: Tue 14 Jul 2009 13:56
by talsja
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()
{
}
}
Posted: Tue 14 Jul 2009 13:58
by talsja
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
Posted: Tue 14 Jul 2009 14:21
by AndreyR
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.
Posted: Wed 15 Jul 2009 07:27
by talsja
Hi AndreyR,
Your example has solved my problem.
Thank u very much for sharing your knowledge.
Greatings Talsja
Posted: Wed 22 Jul 2009 19:09
by Vijay
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
Posted: Thu 23 Jul 2009 14:01
by AndreyR
There were no fixes associated with LINQ and stored procedures in the current build.
Have you tried the workaround?
Posted: Thu 23 Jul 2009 18:33
by Vijay
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
Posted: Fri 24 Jul 2009 08:33
by AndreyR
Please show the code you are using for the stored procedure call.
Maybe the problem is not the one described in this post.
Posted: Mon 27 Jul 2009 17:01
by Vijay
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]
Posted: Tue 28 Jul 2009 07:53
by AndreyR
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.