Call a stored procedure from the Entity Framework

Question

Call a stored procedure from the Entity Framework

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)

3

I'm using stored procedure, SQL Server, EF (code first). I have a stored procedure created in the database and I want to call it from my code.

Stored procedure:

CREATE PROCEDURE uspCreateLocal
-- Add the parameters for the stored procedure here
@Direccion varchar(100),
@Nombre varchar(80),
@Fijo varchar(9),
@Celular varchar(9),
@Administrador int

AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO Locales (Direccion, Nombre, Fijo, Celular, Administrador) 
    VALUES(@Direccion, @Nombre, @Fijo, @Celular, @Administrador)
SELECT SCOPE_IDENTITY() AS  LocalId 
END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
GO

From my code I want to call it

public void InsertOrUpdate(Local entity)
    {
        using (var context = new PosContext())
        {
            if (entity.LocalId == default(int))
            {
                bool exist = Exist(o => o.Nombre == entity.Nombre);
                if(exist)
                    throw new ArgumentException("El Local, que intenta registrar ya existe.");
                else
                {
                    context.Locales.Add(entity);
                    context.SaveChanges();
                }
            }
            else
            {
                context.Entry(entity).State = EntityState.Modified;
                context.SaveChanges();
            }
        }
    }

I'm going to replace the Add part with the store. How should I call him and send him the parameters?

I'm doing it this way:

public void InsertOrUpdate(Local entity)
    {
        using (var context = new PosContext())
        {
            if (entity.LocalId == default(int))
            {
                bool exist = Exist(o => o.Nombre == entity.Nombre);
                if(exist)
                    throw new ArgumentException("El Local, que intenta registrar ya existe.");
                else
                {
                    //context.Locales.Add(entity);
                    //context.SaveChanges();
                    DbModelBuilder modelBuilder = new DbModelBuilder();
                    modelBuilder.Entity<Local>()
                        .MapToStoredProcedures(s =>
                        s.Insert(i => i.HasName("uspCreateLocal")
                        .Parameter(b => b.Direccion, "Direccion")
                        .Parameter(b => b.Nombre, "Nombre")
                        .Parameter(b => b.Fijo, "Fijo")
                        .Parameter(b => b.Celular, "Celular")
                        .Parameter(b => b.Administrador, "Administrador")));
                    context.SaveChanges();
                }
            }
            else
            {
                context.Entry(entity).State = EntityState.Modified;
                context.SaveChanges();
            }

I can not save the new record in the db because it is out of context. How can I solve it?

c# entity-framework

asked by Pedro Ávila 07.01.2017 в 18:41

source

3 answers

3

This is the way I call them, keep in mind that the Dbset is generic in your case you must call it for a specific dbset that is how it is used, it returns the queries in a Dictionary

  using System.Threading.Tasks;
  using MySql.Data.Entity;
  using System.Data.Common;
  using System.Data.Entity;
  using DbManager.Mensajeria;
  using DbManager.Shared;
  using ClassLibraryLog;
  using System.Data.Entity.Core.Objects;
  using System.Data.Entity.Infrastructure;
  using System.Data;
  using System.Data.SqlClient;
  using System.Collections;
  using System.Reflection;

  namespace DbManager
  {
   public class DbManager<T> : DbContext where T : class
  {
    public DbSet<T> listItems { get; set; }
    //private object objType;

    public DbManager(string context) : base(context)
    {
        Database.SetInitializer<DbManager<T>>(null);
    }

  public virtual Dictionary<string, object> ExecuteStoreProcedureWithResultDictionary(string NameStoreProcedure, Dictionary<string, object> Parameters)
    {
        Dictionary<string, object> Values = new Dictionary<string, object>();

        try
        {
            var cmd = Database.Connection.CreateCommand();
            cmd.CommandText = NameStoreProcedure;
            Database.Connection.Open();
            foreach (KeyValuePair<string, object> x in Parameters)
            {

                var param = cmd.CreateParameter();
                param.ParameterName = string.Format("@{0}", x.Key);
                param.Value = x.Value;
                cmd.Parameters.Add(param);

            }
            cmd.CommandType = CommandType.StoredProcedure;
            var reader = cmd.ExecuteReader();

            do
            {
                try
                {
                    //  values = ((IObjectContextAdapter)this).ObjectContext.Translate<T>(reader, "listItems", MergeOption.AppendOnly).ToList();
                    bool findValues = false;

                    while (reader.Read())
                    {
                        T data = Activator.CreateInstance<T>();
                        data.GetType().GetProperties().ToList().ForEach(x =>
                        {
                            if (!x.GetMethod.IsVirtual)
                            {
                                if (reader.GetOrdinal(x.Name) >= 0)
                                {
                                    object value = reader.GetValue(reader.GetOrdinal(x.Name));
                                    Values.Add(x.Name, value);
                                    findValues = true;
                                    //  values.Add(data);
                                }
                            }

                        });
                    }
                    if(findValues)
                        break;
                    // reader.

                }
                catch (Exception e)
                {
                    ClassLogger.LogMsg(LogLevel.ERROR, this.GetType().Name, MethodBase.GetCurrentMethod().Name, e.ToString(), true);
                    return null;
                }

            } while (reader.NextResult());


        }
        catch (Exception e)
        {
            ClassLogger.LogMsg(LogLevel.ERROR, this.GetType().Name, MethodBase.GetCurrentMethod().Name, e.ToString(), true);
            return null;
        }
        return Values;

    }
}

answered by 07.01.2017 в 19:24

1

In this way I implement an SP, from C #, using EF.

   private void btn_EliminarSeleccionDelGrid_Click(object sender, EventArgs e)
        {
            ProductosEF _bdVentas = new ProductosEF();

            if (MessageBox.Show("¿Desea eliminar el producto seleccionado?", "¡Advertencia!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                string EliminarSeleccionDelGrid = dgvProductos.Rows[rowIndex].Cells[1].Value.ToString();

                _bdVentas.DeleteProduct(EliminarSeleccionDelGrid);
            }

            _bdVentas.SaveChanges();

            CargarGrid();
        {

Please note: that the " DeleteProduct " is the name of the SP.

I hope it's helpful, for a future visit to this question.

answered by 23.02.2018 в 20:55

I do not recognize pattern in HTML5 input I can not clean the keyboard buffer in C

score 2 · Accepted Answer

When I've had to execute stored procedures from the Entity Framework, I've done it using context.Database.ExecuteSqlCommand() . In your case, it would look something like this:

context.Database.ExecuteSqlCommand(
    "exec uspCreateLocal @Direccion, @Nombre, @Fijo, @Celular, @Administrador",
    new SqlParameter("@Direccion", entity.Direccion),
    new SqlParameter("@Nombre", entity.Nombre),
    new SqlParameter("@Fijo", entity.Fijo),
    new SqlParameter("@Celular", entity.Celular),
    new SqlParameter("@Administrador", entity.Administrador));

Additional comment:

Regardless of how you execute the stored procedure, because it is not an EF entity, the execution is applied immediately to the database. There is no need to call SaveChanges() as it is done with the usual modifications to the entities.