Entity Framework Return Value from Stored Procedure - Getting Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm attempting to use the Entity Framework for data access, but I'm
running into a few issues with stored procedures. (VS 2008 SP1, 3.5
Framework SP1, SQL Server 2008)

I imported this stored procedure into the framework diagram and then
made it a function import. The stored procedure returns a value.


CREATE PROCEDURE SignOn(@UserName nvarchar(30) = '', @Password nvarchar
(30) = '')
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @COUNT AS INT;


OPEN SYMMETRIC KEY UsrPass123
DECRYPTION BY CERTIFICATE CertUserPassword456;

-- Insert statements for procedure here
SELECT @COUNT = COUNT(*)
FROM Ind A
WHERE A.UserName = @UserName
and CONVERT(nvarchar, DecryptByKey(A.Password)) = @Password;

IF @COUNT > 0
BEGIN
RETURN 1;
END
ELSE
BEGIN
RETURN 0;
END

END

The first thing I noticed is that the stored proc (annoyingly) wasn't
added to the EntityFramework context, so it looked like I had to write
my own method to call the stored proc that wasn't outputting data into
one of the EntityFramework table fields that I defined on my
diagram. So I added a method (below) to the Entity Framework's auto-
generated class to handle the call, but then decided this wasn't such
a great idea because if I had to refresh the diagram, the auto-gen
class got regenerated and wiped out anything I added to it. So I
ended up extending the auto-gen class with this method in another
class.

A few problems:

1.) The parameter names do not allow "@" sign in front of them, so I
can't match them to the stored proc parameter names. (Does it even
matter?)

2.) On the ExecuteNonQuery, I'm getting this error:

The data reader returned by the store data provider does not
have enough columns for the query requested.

So how do I get the return value from the stored proc?



public static class modelext
{

public static Int64 login(this AMModel.AMEntities md, string
username, string password)
{

using (EntityConnection connection = new EntityConnection
(ConfigurationManager.ConnectionStrings
["MyEntityConnection"].ConnectionString))
{

connection.Open();

EntityCommand command = connection.CreateCommand();

command.CommandText = "AMEntities.SignOn";

command.CommandType =
System.Data.CommandType.StoredProcedure;

EntityParameter user = command.Parameters.Add("UserName",
DbType.String);
user.Direction = ParameterDirection.Input;
user.Value = username;

EntityParameter pass = command.Parameters.Add("Password",
DbType.String);
pass.Direction = ParameterDirection.Input;
pass.Value = password;

EntityParameter rtnval = command.Parameters.Add
("ReturnValue",DbType.Int64);
rtnval.Direction = ParameterDirection.ReturnValue;

try
{

command.ExecuteNonQuery();

}

finally
{

connection.Close();

}

Int64 returnValue = (Int64)command.Parameters
["ReturnValue"].Value;

return returnValue;


}

}

}
 
Hi,

I hope I do not mislead you here because I have not done much (read as
nothing) with Entity Framework yet. From ADO and ADO.NET I do know that the
return parameter should be first in the list of parameters so I would assume
the same applies for the Entity Framework since it uses the ADO.NET as the
underlying infrastructure.

Hope this helps
--
Chris Taylor
http://taylorza.blogspot.com



Hello,

I'm attempting to use the Entity Framework for data access, but I'm
running into a few issues with stored procedures. (VS 2008 SP1, 3.5
Framework SP1, SQL Server 2008)

I imported this stored procedure into the framework diagram and then
made it a function import. The stored procedure returns a value.


CREATE PROCEDURE SignOn(@UserName nvarchar(30) = '', @Password nvarchar
(30) = '')
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @COUNT AS INT;


OPEN SYMMETRIC KEY UsrPass123
DECRYPTION BY CERTIFICATE CertUserPassword456;

-- Insert statements for procedure here
SELECT @COUNT = COUNT(*)
FROM Ind A
WHERE A.UserName = @UserName
and CONVERT(nvarchar, DecryptByKey(A.Password)) = @Password;

IF @COUNT > 0
BEGIN
RETURN 1;
END
ELSE
BEGIN
RETURN 0;
END

END

The first thing I noticed is that the stored proc (annoyingly) wasn't
added to the EntityFramework context, so it looked like I had to write
my own method to call the stored proc that wasn't outputting data into
one of the EntityFramework table fields that I defined on my
diagram. So I added a method (below) to the Entity Framework's auto-
generated class to handle the call, but then decided this wasn't such
a great idea because if I had to refresh the diagram, the auto-gen
class got regenerated and wiped out anything I added to it. So I
ended up extending the auto-gen class with this method in another
class.

A few problems:

1.) The parameter names do not allow "@" sign in front of them, so I
can't match them to the stored proc parameter names. (Does it even
matter?)

2.) On the ExecuteNonQuery, I'm getting this error:

The data reader returned by the store data provider does not
have enough columns for the query requested.

So how do I get the return value from the stored proc?



public static class modelext
{

public static Int64 login(this AMModel.AMEntities md, string
username, string password)
{

using (EntityConnection connection = new EntityConnection
(ConfigurationManager.ConnectionStrings
["MyEntityConnection"].ConnectionString))
{

connection.Open();

EntityCommand command = connection.CreateCommand();

command.CommandText = "AMEntities.SignOn";

command.CommandType =
System.Data.CommandType.StoredProcedure;

EntityParameter user = command.Parameters.Add("UserName",
DbType.String);
user.Direction = ParameterDirection.Input;
user.Value = username;

EntityParameter pass = command.Parameters.Add("Password",
DbType.String);
pass.Direction = ParameterDirection.Input;
pass.Value = password;

EntityParameter rtnval = command.Parameters.Add
("ReturnValue",DbType.Int64);
rtnval.Direction = ParameterDirection.ReturnValue;

try
{

command.ExecuteNonQuery();

}

finally
{

connection.Close();

}

Int64 returnValue = (Int64)command.Parameters
["ReturnValue"].Value;

return returnValue;


}

}

}
Click to expand...
 
Back
Top