Stored Procedure in the Entity Framework without model edmx

6

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?

    
asked by anonymous 29.01.2014 / 19:02

2 answers

7

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();
    }
}
    
29.01.2014 / 19:14
3

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();
}
    
30.01.2014 / 17:02