c# - Stored Procedure return -1 for all cases in entity framework

Question

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?

Answer 1

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.