To call a Stored Procedure in the Entity Framework Core there are two options in the method Fromsql and Database.Executesqlcommand. The Fromsql is used for return (SELECT
) and already the Database.Executesqlcommand to insert, change and delete data from a table, basically that’s it.
By the question code there is error in the creation of the parameters that the Stored Procedure
needed to work, so with a minimal example I’ll explain how to make the code:
Store Procedure
CREATE PROCEDURE [dbo].[Proc_Login]
@UserName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Login] WHERE [Login].[UserName] = @UserName;
END
To use the code is very simple, create a SqlParameter
with all the parameters you need to use and pass the corresponding value and then just call your Procedure
by the method Fromsql the same way it was created in your bank:
using (DatabaseContext db = new DatabaseContext())
{
var param = new SqlParameter("@UserName", "Us1");
var result = db.Login
.FromSql("Proc_Login @UserName", param)
.FirstOrDefault();
}
Database.ExecuteSqlCommand
Store Procedure
CREATE PROCEDURE Proc_Update_Passord_Login
@Password varchar(50),
@Id int
AS
BEGIN
UPDATE [Login] SET [Login].[Password] = @Password WHERE [Login].[Id] = @Id;
END
To use this procedure
update use in your DbContext
the Database.Executesqlcommand as follows:
using (DatabaseContext db = new DatabaseContext())
{
var parameters = new[]
{
new SqlParameter("@Password", "abcd"),
new SqlParameter("@Id", 1)
};
int count = db.Database
.ExecuteSqlCommand("Proc_Update_Passord_Login @Password, @Id", parameters);
}
where the variable count
returns the number of rows that were affected.
Returning your question based on this explanation is simple the solution:
var parameters = new[]
{
new SqlParameter("@chave", "abcd"),
new SqlParameter("@senha", null),
new SqlParameter("@fonteConfiavel", 0)
};
var status = _context.Set<Usuario>()
.FromSql("LoginUsuario @chave,@senha,@fonteConfiavel", parameters)
.ToList();
Note: I don’t know how yours is Stored Procedure, so the parameters were used equal in asking, may need some adjustment for some unexpected error, but, the example giving earlier is functional.
References:
I have a suspicion of what it is, but not sure, so I will add here in the comments, try as follows
var status = _context.Set<Usuario>().FromSql("exec LoginUsuario @p0, @p1, @p2", parameters: new[] {"ROBr", null, "0" }).ToList();
– Barbetta
By the error it seems that some parameter is missing in the
procedure
. Very specific it seems that theID
– Marconi
So it hasn’t worked yet, but I’ve captured the Procedure call : exec sp_executesql N'exec Loginusuario @chave,@password,@fonteConfiavel ',N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000)',@P0=N'Robr',@P1=NULL,@P2=N'true
– Robson Oliveira
var status = _context. Set<Usuario>(). Fromsql("exec Loginusuario @key,@password,@fonteDescover knowed", Parameters: new[] { "Robr", null, "true" }). Tolist();
– Robson Oliveira
Two things: i) Try removing "exec" from
FromSql
FromSql(" LoginUsuario....
; ii) Turningexec LoginUsuario "ROBr", null, 0
right in the database, of the right?– Barbetta
exec sp_executesql N'Loginusuario ',N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int',@P0=N'@key=Rob',@P1=NULL,@P2=0 ,this is called and tested in the database and gives the same error Msg 201, Level 16, State 4, Procedure Loginusuario, Line 73 Procedure or Function 'Loginusuario' expects Parameter '@chave', which was not supplied.
– Robson Oliveira
is missing passing parameters to your
procedure
– Barbetta
put the Procedure on your question!?
– novic