How to get stored procedure return value using linq with C#?

Question

have a stored Procedure that do some operation and return 1 or 0 as below.

CREATE PROCEDURE dbo.UserCheckUsername11
(
    @Username nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;

IF Exists(SELECT UserID FROM User WHERE username =@Username) 
    return 1
ELSE
    return 0
END

Using linq I tried to get return value. But i am getting the output as -1 always.

Below is my Linq code :

 using (PlanGenEntities3 entity2 = new PlanGenEntities3())
 {                
    var result = entity2.Entity_test_GetHoliday();
    string output = result.ToString();
 }

How to solve this ?

have a look here http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx — ozhug, Jun 29 '15 at 05:32
It seems like you're calling the wrong stored procedure. In your SQL it's called "UserCheckUsername1" but in code you're calling "Entity_test_GetHoliday"; unless you manually mapped it to a different name... — Arian Motamedi, Jun 29 '15 at 05:34
@PoweredByOrange Entity_test_GetHoliday is my EntityContainer Stored procedure name. Just for sample i tried to put the code. Can you please help me to solve how to get stored procedure retun values. — Dhamo, Jun 29 '15 at 05:45
Any one plese help me to pass some sample codes to solve this issues — Dhamo, Jun 29 '15 at 05:48
Hi @Dhamo you can check this [link](https://msdn.microsoft.com/en-us/library/vstudio/bb399357(v=vs.100).aspx) — Arun Gairola, Jun 29 '15 at 06:15
@ArunGairola, Thanks for posting. I would like to do and get the retrun values from stored procedure without using any custome functions. How can i do it & using Entity Framework. — Dhamo, Jun 29 '15 at 06:20
Maybe, you can try [this](http://stackoverflow.com/a/14735479/4715873) — Heinz Siahaan, Jun 29 '15 at 06:48
Try to use Output parameter of Store procedure then Linq will understand what you want to return. — KuldipMCA, Jun 29 '15 at 07:10
Solution found : using (PlanGenEntities3 entity2 = new PlanGenEntities3()) { // name is an output parameter. ObjectParameter name = new ObjectParameter("Ret", typeof(int)); entity2.Entity_test_GetHoliday(name); //Get stored procedure return values: string getOutput = name.Value.ToString(); } — Dhamo, Jun 29 '15 at 07:20

score 1 · Answer 1 · answered Jun 30 '15 at 00:17

Just declare your scalar variable as a output parameter for SQL query, then in the code you could retrieve its value this way: ```

using (PlanGenEntities3 myContext = new PlanGenEntities3())            
{                
   var outputParameter = new System.Data.Objects.ObjectParameter("OutputParameterName", typeof(int));                
   myContext.Entity_test_GetHoliday(outputParameter );                
   Console.WriteLine(outputParameter.Value);            
}

```

I suppose there is way to access the default scalar output of your SP in the similar manor.

score 0 · Answer 2 · answered Aug 06 '15 at 20:40

0

If he had it as an output parameter it would automatically show as a reference parameter of the proper corresponding data type in the LINQ call.

He wouldn't really need to create a parameter object to contain that.

answered Aug 06 '15 at 20:40

Underground

127
5

How to get stored procedure return value using linq with C#?

2 Answers2