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;
}
}
}
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;
}
}
}