wake-up-neo.com

Ausführen gespeicherter Prozeduren in Entity Framework Core

Ich verwende EF7 (Entity Framework Core) in einer asp.net Core App. Zeigen Sie mir bitte, wie gespeicherte Prozeduren ordnungsgemäß ausgeführt werden können. Die alte Methode mit ObjectParameters und ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction funktioniert nicht.

46
eadam

Die Unterstützung für gespeicherte Prozeduren in EF7 wurde behoben. Dies unterstützt auch die Zuordnung mehrerer Ergebnismengen.

Überprüfen Sie hier die Fixdetails

Und du kannst es so nennen - var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");

60
Arvin

Die Unterstützung für gespeicherte Prozeduren ist in EF7 (ab 7.0.0-beta3) noch nicht implementiert. Sie können den Fortschritt dieser Funktion mit issue # 245 verfolgen.

Sie können dies vorerst auf die altmodische Weise mit ADO.NET tun.

var connection = (SqlConnection)context.Database.AsSqlServer().Connection.DbConnection;

var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "MySproc";
command.Parameters.AddWithValue("@MyParameter", 42);

command.ExecuteNonQuery();
19
bricelam

Verwenden Sie zum Ausführen der gespeicherten Prozeduren die Methode FromSql , mit der RAW-SQL-Abfragen ausgeführt werden

z.B.

var products= context.Products
    .FromSql("EXECUTE dbo.GetProducts")
    .ToList();

Zur Verwendung mit Parametern

var productCategory= "Electronics";

var product = context.Products
    .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
    .ToList();

oder

var productCategory= new SqlParameter("productCategory", "Electronics");

var product = context.Product
    .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
    .ToList();

Für die Ausführung von RAW-SQL-Abfragen oder gespeicherten Prozeduren gelten bestimmte Einschränkungen. Sie können sie nicht für INSERT/UPDATE/DELETE verwenden. Wenn Sie INSERT-, UPDATE- und DELETE-Abfragen ausführen möchten, verwenden Sie den ExecuteSqlCommand

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);
15
Rohith

"(SqlConnection)context" - Diese Typumwandlung funktioniert nicht mehr. Sie können tun: "SqlConnection context;

".AsSqlServer()" - Existiert nicht.

"command.ExecuteNonQuery();" - Gibt keine Ergebnisse zurück. reader=command.ExecuteReader() funktioniert.

Mit dt.load (reader) ... müssen Sie das Framework von 5.0 auf 4.51 umstellen, da 5.0 noch keine Datatables/Datasets unterstützt. Hinweis: Dies ist VS2015 RC.

4
Rich

Die Unterstützung für gespeicherte Prozeduren in EF Core ähnelt zunächst den früheren Versionen von EF Code.

Sie müssen Ihre DbContext-Klasse erstellen, indem Sie die DbContext-Klasse von EF inhertieren. Die gespeicherten Prozeduren werden mithilfe von DbContext ausgeführt.

Der erste Schritt besteht darin, eine Methode zu schreiben, die einen DbCommand aus dem DbContext erstellt.

public static DbCommand LoadStoredProc(
  this DbContext context, string storedProcName)
{
  var cmd = context.Database.GetDbConnection().CreateCommand();
  cmd.CommandText = storedProcName;
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  return cmd;
}

Verwenden Sie die folgende Methode, um Parameter an die gespeicherte Prozedur zu übergeben.

public static DbCommand WithSqlParam(
  this DbCommand cmd, string paramName, object paramValue)
{
  if (string.IsNullOrEmpty(cmd.CommandText))
    throw new InvalidOperationException(
      "Call LoadStoredProc before using this method");
  var param = cmd.CreateParameter();
  param.ParameterName = paramName;
  param.Value = paramValue;
  cmd.Parameters.Add(param);
  return cmd;
}

Verwenden Sie zum Zuordnen des Ergebnisses zu einer Liste benutzerdefinierter Objekte die MapToList-Methode.

private static List<T> MapToList<T>(this DbDataReader dr)
{
  var objList = new List<T>();
  var props = typeof(T).GetRuntimeProperties();

  var colMapping = dr.GetColumnSchema()
    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
    .ToDictionary(key => key.ColumnName.ToLower());

  if (dr.HasRows)
  {
    while (dr.Read())
    {
      T obj = Activator.CreateInstance<T>();
      foreach (var prop in props)
      {
        var val = 
          dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
          prop.SetValue(obj, val == DBNull.Value ? null : val);
      }
      objList.Add(obj);
    }
  }
  return objList;
}

Jetzt können Sie die gespeicherte Prozedur mit der ExecuteStoredProc-Methode ausführen und sie einer Liste zuordnen, deren Typ als T übergeben wurde.

public static async Task<List<T>> ExecuteStoredProc<T>(this DbCommand command)
{
  using (command)
  {
    if (command.Connection.State == System.Data.ConnectionState.Closed)
    command.Connection.Open();
    try
    {
      using (var reader = await command.ExecuteReaderAsync())
      {
        return reader.MapToList<T>();
      }
    }
    catch(Exception e)
    {
      throw (e);
    }
    finally
    {
      command.Connection.Close();
    }
  }
}

Um beispielsweise eine gespeicherte Prozedur mit dem Namen "StoredProcedureName" mit zwei Parametern namens "firstparamname" und "secondparamname" auszuführen, ist dies die Implementierung.

List<MyType> myTypeList = new List<MyType>();
using(var context = new MyDbContext())
{
  myTypeList = context.LoadStoredProc("StoredProcedureName")
  .WithSqlParam("firstparamname", firstParamValue)
  .WithSqlParam("secondparamname", secondParamValue).
  .ExecureStoredProc<MyType>();
}
3
Marco Barbero

Derzeit unterstützt EF 7 oder EF Core nicht die alte Methode, gespeicherte Prozeduren in Designer zu importieren und direkt aufzurufen. Sie können einen Blick auf die Roadmap werfen, um zu sehen, was in Zukunft unterstützt wird: EF-Kern-Roadmap .

Daher ist es im Moment besser, SqlConnection zu verwenden, um gespeicherte Prozeduren oder unformatierte Abfragen aufzurufen, da Sie nicht die gesamte EF für diesen Job benötigen. Hier sind zwei Beispiele:

Rufen Sie eine gespeicherte Prozedur auf, die einen einzelnen Wert zurückgibt. String in diesem Fall.

CREATE PROCEDURE [dbo].[Test]
    @UserName nvarchar(50)
AS
BEGIN
    SELECT 'Name is: '[email protected];
END

Rufen Sie eine gespeicherte Prozedur auf, die eine Liste zurückgibt.

CREATE PROCEDURE [dbo].[TestList]
AS
BEGIN
    SELECT [UserName], [Id] FROM [dbo].[AspNetUsers]
END

Um diese gespeicherte Prozedur aufzurufen, ist es besser, eine statische Klasse zu erstellen, die alle diese Funktionen enthält. Ich habe sie beispielsweise wie folgt als DataAccess-Klasse bezeichnet:

public static class DataAccess

    {
        private static string connectionString = ""; //Your connection string
        public static string Test(String userName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.Test", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@UserName", userName));

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        return rdr[0].ToString();
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }

        public static IList<Users> TestList()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.TestList", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    IList<Users> result = new List<Users>();
                    //3. Loop through rows
                    while (rdr.Read())
                    {
                        //Get each column
                        result.Add(new Users() { UserName = (string)rdr.GetString(0), Id = rdr.GetString(1) });
                    }
                    return result;
                }
            }

        }
    }

Und die Users-Klasse sieht so aus:

public class Users
{
     public string UserName { set; get; }
     public string Id { set; get; }
}

Übrigens müssen Sie sich nicht um die Leistung des Öffnens und Schließens einer Verbindung für jede Anfrage an sql kümmern, da asp.net sich darum kümmert, diese für Sie zu verwalten. Und ich hoffe das war hilfreich.

3
Said Al Souti

Ich habe alle anderen Lösungen ausprobiert, aber bei mir nicht funktioniert. Aber ich bin zu einer richtigen Lösung gekommen und es kann für jemanden hier hilfreich sein.

Um eine gespeicherte Prozedur aufzurufen und das Ergebnis in einer Modellliste in EF Core anzuzeigen, müssen Sie drei Schritte ausführen.

Schritt 1. Sie müssen genau wie Ihre Entitätsklasse eine neue Klasse hinzufügen. Welches sollte Eigenschaften mit allen Spalten in Ihrem SP haben. Wenn Ihr SP zwei Spalten mit dem Namen Id und Name zurückgibt, sollte Ihre neue Klasse ungefähr so ​​aussehen

public class MySPModel
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Schritt 2.

Anschließend müssen Sie Ihrer DBContext-Klasse für Ihren SP eine DbQuery -Eigenschaft hinzufügen.

public partial class Sonar_Health_AppointmentsContext : DbContext
{
        public virtual DbSet<Booking> Booking { get; set; } // your existing DbSets
        ...

        public virtual DbQuery<MySPModel> MySP { get; set; } // your new DbQuery
        ...
}

Schritt 3.

Jetzt können Sie aufrufen und das Ergebnis von Ihrem SP aus Ihrem DBContext abrufen.

var result = await _context.Query<MySPModel>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();

Ich verwende ein generisches UnitOfWork & Repository. Also meine Funktion zum Ausführen des SP ist

/// <summary>
/// Execute function. Be extra care when using this function as there is a risk for SQL injection
/// </summary>
public async Task<IEnumerable<T>> ExecuteFuntion<T>(string functionName, string parameter) where T : class
{
    return await _context.Query<T>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
}

Hoffe es wird jemandem weiterhelfen !!!

1

Ich hatte große Probleme mit den Parametern ExecuteSqlCommand und ExecuteSqlCommandAsync. Die IN-Parameter waren einfach, die OUT-Parameter jedoch sehr schwierig.

Ich musste wieder DbCommand wie folgt verwenden -

DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

cmd.CommandText = "dbo.sp_DoSomething";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

Ich schrieb mehr darüber in dieser Post .

1
Bryan

Ich fand diese Erweiterung sehr nützlich: StoredProcedure EFCore

Dann ist die Verwendung so

List<Model> rows = null;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 300L)
   .AddParam("limitOut", out IOutParam<long> limitOut)
   .Exec(r => rows = r.ToList<Model>());

long limitOutValue = limitOut.Value;

ctx.LoadStoredProc("dbo.ReturnBoolean")
   .AddParam("boolean_to_return", true)
   .ReturnValue(out IOutParam<bool> retParam)
   .ExecNonQuery();

bool b = retParam.Value;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 1L)
   .ExecScalar(out long l);
0
Manfred Wippel

Nichts zu tun ... Wenn Sie dbcontext für den Code-First-Approach erstellen, initialisieren Sie den Namespace unterhalb des fließenden API-Bereichs.

public partial class JobScheduleSmsEntities : DbContext
{
    public JobScheduleSmsEntities()
        : base("name=JobScheduleSmsEntities")
    {
        Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
    public virtual DbSet<RoleMaster> RoleMasters { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Types().Configure(t => t.MapToStoredProcedures());

        //modelBuilder.Entity<RoleMaster>()
        //     .HasMany(e => e.Customers)
        //     .WithRequired(e => e.RoleMaster)
        //     .HasForeignKey(e => e.RoleID)
        //     .WillCascadeOnDelete(false);
    }
    public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
    {
        //return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
        //  this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
        using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
        {
           return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();

        }

    }

}

}

public partial class Sp_CustomerDetails02
{
    public long? ID { get; set; }
    public string Name { get; set; }
    public string CustomerID { get; set; }
    public long? CustID { get; set; }
    public long? Customer_ID { get; set; }
    public decimal? Amount { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public int? CountDay { get; set; }
    public int? EndDateCountDay { get; set; }
    public DateTime? RenewDate { get; set; }
    public bool? IsSMS { get; set; }
    public bool? IsActive { get; set; }
    public string Contact { get; set; }
}
0
SHUBHASIS

Verwenden von MySQL Connector und Entity Framework Core 2.0

Mein Problem war, dass ich eine Ausnahme wie FX bekam. Ex.Message = "Die erforderliche Spalte 'body' war in den Ergebnissen einer 'FromSql'-Operation nicht vorhanden." Um also Zeilen über eine gespeicherte Prozedur auf diese Weise abzurufen, müssen Sie alle Spalten für den Entitätstyp zurückgeben, dem das DBSet zugeordnet ist, auch wenn Sie für Ihre aktuelle Anforderung nicht auf alle zugreifen müssen.

var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

ODER mit Parametern

var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList(); 
0
chri3g91