I have a project that uses the Entity Framework without a edmx
template, we have registered entities manually create a class and insert it into the context.
Does anyone use this format and know how to register a Stored Procedure this way?
I have a project that uses the Entity Framework without a edmx
template, we have registered entities manually create a class and insert it into the context.
Does anyone use this format and know how to register a Stored Procedure this way?
From version 5 of the Entity Framework there is a simple way to execute a Stored Procedure based on the instance of DbContext
, using DataBase
property, see:
using(MeuContexto context = new MeuContexto())
{
SqlParameter param = new SqlParameter("@idCliente", 1);
context.Database.ExecuteSqlCommand("sp_ExcluirCliente @idCliente", param);
}
Or even execute Stored Procedures with return, whose return can be an entity of its context:
public List<Cliente> Clientes()
{
using(MeuContexto context = new MeuContexto())
{
return context.Database.SqlQuery<Cliente>("exec sp_Clientes).ToList();
}
}
Complementing the above information:
To execute a stored procedure you can do the following code:
using (var conn = new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand("[dbo].[nome_da_procedure]", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@PROC_PARAMETRO", SqlDbType.Int)).Value = 100;
command.Parameters.Add(new SqlParameter("@PROC_PARAMETRO1", SqlDbType.VarChar)).Value = 'valor';
conn.Open();
command.ExecuteNonQuery();
}
}
Some details regarding the procedure execution are: You can assign the result of a procedure to a variable:
var returnValue = command.ExecuteReader(); //Retorna a linha que foi executada
Taking the returned values:
string variavel = "";
while (returnValue.Read())
{
variavel = returnValue["COLUNA_TABELA"].ToString();
}