20 
CREATE PROC spIsValidUser
     @UserName varchar(50),
     @Password varchar(50) 
AS
    IF  Exists(SELECT * FROM Users where UserName=@UserName and Password=@Password)
    BEGIN
        return 0

    END
    ELSE
    BEGIN
        return 1
    END
 GO

I have created this Stored Procedure and tring to call this Stored Procedure using entity framework. Below is code in written in C#.

MyBusEntities db = new MyBusEntities();
int empQuery = db.spIsValidUser("abc", "abc@123");

spIsValidUser Stored Procedure return -1 in all case. Please let me know error.

EDIT - According to given answer, Store procedure is not used return statement because Entity Framework cannot support Stored Procedure Return scalar values out of the box..Let me know how can I send scalar data from Stored Procedure?

Improve this question
6
  • Did you try to step into your code, enumerate the procedure or manually execute the procedure directly at the SQL Server? I am suspecting its with but it is not possible to say unless we quickly debug through this. Commented Aug 3, 2015 at 7:28
  • is db defined and instantiated properly. (when you enumerate)Does it have a spIsValidUser procedure listed. What does this procedure return when you manually call it on the SQL server. Commented Aug 3, 2015 at 7:34
  • Yes. I have executed spIsValidUser manually using below statement in sQL Server. EXEC spIsValidUser 'abc' 'abc@123' and it give me output 0. and I also checked Db. It is defined and instantiated properly. Commented Aug 3, 2015 at 7:37
  • Why would you want to return a 0 when a user is valid and 1 when the user is invalid. Kinda odd authentication logic here. Have you considered the industry standard practise? You could take one of the standard authentication codeprojects available online and customize it to your needs. Commented Aug 3, 2015 at 7:49
  • 1
    We will integrate authentication logic in this project later. This issue is facing on calling any store procedure which is returning integer. Commented Aug 3, 2015 at 9:04

3 Answers 3

Reset to default
10
+50

Your stored procedure is currently returns a scalar value. Use the following steps to solve this issue:

  • Change your stored procedure like this (Don't use the keyword return in the stored procedure to return the value, Entity Framework cannot support Stored Procedure Return scalar values out of the box. BUT there is a work around):

    ALTER PROC spIsValidUser
@UserName varchar(50),
@Password varchar(50) 
AS
SELECT Count(*) FROM Users where UserName= @UserName and Password= @Password
return

  • You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.

  • In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model, choose your procedure from the drop down list, and choose the return value of the procedure to be Scalar.

  • Finally write you code like this:

    MyBusEntities db = new MyBusEntities();
System.Nullable<int> empQuery = db.spIsValidUser("abc", "abc@123").SingleOrDefault().Value;
MessageBox.Show(empQuery.ToString());// show 1 if Exist and 0 if not Exist

Edit: I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'm curious. When we don't set Scalar as a complex type the stored procedure already return an int which isn't working. What is that int exactly?
6

Have you tried:

CREATE PROC spIsValidUser
     @UserName varchar(50),
     @Password varchar(50) 
AS
    IF  Exists(SELECT * FROM Users where UserName=@UserName and Password=@Password)
    BEGIN
        SELECT 0

    END
    ELSE
    BEGIN
        SELECT 1
    END
 GO
Improve this answer

1 Comment

it is working fine. it is returning ObjectResult<int?> and after applying SingleOrDefault() function it is giving correct result.
5

Have you imported your stored procedures in the EF model correctly? and are you setting correct return type to stored procedures??

There are 4 possible return type that you can set to your procedures The possible return types are:

  1. none
  2. Scalars
  3. Complex
  4. Entities

you need to set scalars return type.

if you dont know how to set the return type, then here is the full tutorial http://www.binaryintellect.net/articles/30738a7c-5176-4333-aa83-98eab8548da5.aspx

quick example. 

CREATE PROCEDURE CustOrderCount
    @CustomerID nchar(5)
AS
BEGIN
    SELECT COUNT(*) FROM ORDERS 
        WHERE CUSTOMERID=@CustomerID;
END


NorthwindEntities db = new NorthwindEntities();
var count = db.CustOrderCount("ALFKI");
int ordercount = count.SingleOrDefault().Value;

this will return int order count.

Improve this answer

2 Comments

I already try this thing. I have imported stored procedure in EF model And also set return type. let me know which return type I have to set in this case? Currently i have set "none" return type.Also I have tried to change scalars type(Int32) but it give me run time exeception.
Would you be able to put your problem as sample solution in github and provide us an access. Thats the onlyway i could tell you why it returns -1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.