CODEWITHSUNDEEP
entity-framework-coreef-core-6.0
David
David

Reputation: 3355

How do I call stored procedures in EF Core 6 using named parameters?

A lot of code examples use either named parameters or execute stored procedures, but not both. How do I do so when I don't have a pre-defined entity type being selected by the stored proc? (Which means that .FromSqlRaw is out.)

Upvotes: 5

Views: 31622

Answers (5)

David
David

Reputation: 3355

The code below allows you to call a stored procedure and generate a list of named parameters, just from the list of SqlParameters.

    var sqlParams = new SqlParameter[] {
      new SqlParameter("p1", valOfP1),
      new SqlParameter("p2", valOfP2),
      new SqlParameter("pOut", SqlDbType.Int)
      {
        Direction = System.Data.ParameterDirection.Output
      }
    };

    // OK to use the same names as for the SqlParameter identifiers. Does not interfere.
    var sql = "myStoredProc " + String.Join(", ", sqlParams.Select(x =>
      $"@{x.ParameterName} = @{x.ParameterName}" +
      (x.Direction == ParameterDirection.Output ? " OUT" : "")
      ));

    myDbContext.Database.ExecuteSqlRaw(sql, sqlParams);

    var outputId = (int)(sqlParams.First(p => p.Direction == ParameterDirection.Output).Value);

Upvotes: 4

Darshan Balar
Darshan Balar

Reputation: 108

Here Product is class where you can define property whatever you want to retrieve from procedure

 public class DataBaseContext : DbContext
 {
      public DataBaseContext() : base("name=DataBaseContext")
      {
      }
        
      public DbSet<Product> Products { get; set; }
            
 }

-- // This below code you need to write where you want to execute 

 var context = new DataBaseContext();
 var products = context.Database.SqlQuery<Product>("EXEC GetProductsList @ProductId", 
                                                              new SqlParameter("@ProductId", "1")
                                                              ).ToList();

Upvotes: 0

Stuart
Stuart

Reputation: 119

Pretty much the same as SAEED said above. Add the code below to your DbContext class:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Berk>().HasNoKey();
    }

    [NotMapped]
    public DbSet<Berk> Berks { get; set; }

    public virtual List<Berk> CallSP(string berkberk) =>
        Berks.FromSqlRaw("exec dbo.berk @berkBerk = {0}", berkberk).ToList();

called with:

List<Berk> berk = = _whateverYouCalledTheDbContext.CallSP("berk berk berk!");

Will return a DbSet where Berk is just an object that matches the return values from the stored procedure. There is no Berks table in the database, but you have your stored procedure return values to play with as you wish.

Upvotes: 4

SAEED REZAEI
SAEED REZAEI

Reputation: 550

  1. Add DbSet as below code
    public DbSet ChartModels { get; set; }

  2. Set Dbset AS a HasNoKey() if it is use only for Query

    builder.Entity< ChartModel >().HasNoKey();

  3. Call Sp as below Code

    string sqlQuery = "EXECUTE dbo.GetDashboardChart";

    SqlParameter p = new SqlParameter("@name", "test");

    var lst = await ChartModels.FromSqlRaw(sqlQuery,p).ToListAsync();

Upvotes: 0

Sonu Sharma
Sonu Sharma

Reputation: 21

Try Below example code which lets you call sp and store result in a datatable.

using (var command = db.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "sp_name";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("key", "Value"));

    db.Database.OpenConnection();

    using (var result = command.ExecuteReader())
    {
        var dataTable = new DataTable();
        dataTable.Load(result);
        return dataTable;
    }
}

Upvotes: 2

Related Questions