Wpf Clickonce Dpi Awareness Per-Monitor V2

How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5

I hope that I correctly understand your problem. You have existing STORED PROCEDURE, for example dbo.spGetSomeData, in the database, which returns the list of some items with some fields and you need to provide the data from Web API method.

The implementation could be about the following. You can define an empty DbContext like:

public class MyDbContext : DbContext
{
}

and to define appsettings.json with the connection string to the database

{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
  }
}

You should use Microsoft.Extensions.DependencyInjection to add MyDbContext to the

public class Startup
{
    // property for holding configuration
    public IConfigurationRoot Configuration { get; set; }

    public Startup(IHostingEnvironment env)
    {
        // Set up configuration sources.
        var builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
            .AddEnvironmentVariables();
        // save the configuration in Configuration property
        Configuration = builder.Build();
    }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddMvc()
            .AddJsonOptions(options => {
                options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
            });

        services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MyDbContext>(options => {
                options.UseSqlServer(Configuration["ConnectionString"]);
            });
    }
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        ...
    }
}

Now you can implement your WebApi action as the following:

[Route("api/[controller]")]
public class MyController : Controller
{
    public MyDbContext _context { get; set; }

    public MyController([FromServices] MyDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async IEnumerable<object> Get()
    {
        var returnObject = new List<dynamic>();

        using (var cmd = _context.Database.GetDbConnection().CreateCommand()) {
            cmd.CommandText = "exec dbo.spGetSomeData";
            cmd.CommandType = CommandType.StoredProcedure;
            // set some parameters of the stored procedure
            cmd.Parameters.Add(new SqlParameter("@someParam",
                SqlDbType.TinyInt) { Value = 1 });

            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            var retObject = new List<dynamic>();
            using (var dataReader = await cmd.ExecuteReaderAsync())
            {
                while (await dataReader.ReadAsync())
                {
                    var dataRow = new ExpandoObject() as IDictionary<string, object>;
                    for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++) {
                        // one can modify the next line to
                        //   if (dataReader.IsDBNull(iFiled))
                        //       dataRow.Add(dataReader.GetName(iFiled), dataReader[iFiled]);
                        // if one want don't fill the property for NULL
                        // returned from the database
                        dataRow.Add(
                            dataReader.GetName(iFiled),
                            dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
                        );
                    }

                    retObject.Add((ExpandoObject)dataRow);
                }
            }
            return retObject;
        }
    }
}

The above code just execute using exec dbo.spGetSomeData and use dataRader to read all results and save there in dynamic object. If you would make $.ajax call from api/My you will get the data returned from dbo.spGetSomeData, which you can directly use in JavaScript code. The above code is very transparent. The names of the fields from the dataset returned by dbo.spGetSomeData will be the names of the properties in the JavaScript code. You don't need to manage any entity classes in your C# code in any way. Your C# code have no names of fields returned from the stored procedure. Thus if you would extend/change the code of dbo.spGetSomeData (rename some fields, add new fields) you will need to adjust only your JavaScript code, but no C# code.

Ho can I execute multiple stored procedure calls in one query?

You can execute multiple statements in one SQL:

var sql = @"
   EXEC InsertFile @id1, @FileName1;
   EXEC InsertFile @id2, @FileName2;
   EXEC DeleteFile @id3;
   EXEC InsertFile @id4, @FileName4;
";

await _db.Database.ExecuteSqlRawAsync(sql, 
     new SqlParameter("@Id1", 100),
     new SqlParameter("@FileName1", "file1"),
     new SqlParameter("@Id2", 101),
     new SqlParameter("@FileName2", "file2"),
     new SqlParameter("@Id3", 5),
     new SqlParameter("@Id4", 108),
     new SqlParameter("@FileName4", "file8")
);

How to import stored procedure using code first ASP.NET MVC and Entity Framework

I usually use stored procedures in the following way:

• add a EF code first migration (https://www.entityframeworktutorial.net/code-first/code-based-migration-in-code-first.aspx)
• add the stored procedure to your migration through Sql()
• configure the stored procedure with the model builder or in your entity
• use it in your code :-)

Example migration:

public partial class yourmigration: DbMigration
{
    protected override void Up()
    {
        Sql("CREATE PROCEDURE SQL HERE");
    }

    //dont forget the down.
}

For EF 6, here's a good article about it: https://www.entityframeworktutorial.net/entityframework6/code-first-insert-update-delete-stored-procedure-mapping.aspx

Call stored procedure to input data in database from ASP.NET Web API?

since you dont expect any data to return, try this

_context.Database.ExecuteSqlCommand(result);

or async with params

var resultParam = new SqlParameter("@result", result);
await   context.Database.ExecuteSqlCommandAsync("EXEC  sp_add @result", resultParam);

how to run stored procedure in Entity framework

As per my understanding, you want to execute a stored procedure that run on multiple tables, and then return Json Data to View. You can actually do something like below:

Approach 1: (Using ExecuteSqlCommand)

SqlParameter param1 = new SqlParameter("@query", query);        
var result = db.Database.ExecuteSqlCommand("SP_DynamicCtrl @query", 
                              param1);

Approach 2: (Using Object Type on SqlQuery)

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<Object>("exec SP_DynamicCtrl @query", param1);

Approach 3: (Cleaner Approach)

1.) Create a model as per your return parameters from stored procedure, let's call it YourType class.
2.) Use the below code to call stored pocedure:

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<YourType>("exec SP_DynamicCtrl @query", param1);

After you get the result from above query, you can convert it to JSON befor returning in controller:

return Json(result, JsonRequestBehavior.AllowGet); //Typecast the result as per your need

Please modify code as per your need.

ASP.Net Core - Entity Framework - Call Stored Procedure With No Return Data (in a void method)

To get data better to use this:

 var result = _dbContext.MyModel
                  .FromSqlRaw("ustp_ProcedureName {0}", param1)
                  .ToList();

if you want to run sp that doesnt return any data try this:

_dbContext.Database.ExecuteSqlCommand("ustp_ProcedureName {0}", param1);

How to run stored procedures in Entity Framework Core?

Support for stored procedures in EF Core 1.0 is resolved now, this also supports the mapping of multiple result-sets.

Check here for the fix details

And you can call it like this in c#

var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");

ASP.NET Core Entity Framework call stored procedure

In addition to the ExecuteSqlCommand method, the DbContext.Database property provides an API that allows you to perform ADO.NET operations directly. The GetDbConnection method returns a DbConnection object representing the context's underlying connection. From that point, you can revert to the familiar ADO.NET APIs:

using (var command = _context.Database.GetDbConnection ().CreateCommand ())
{
   command.CommandText = "SP_NAME";
   command.CommandType = CommandType.StoredProcedure;
   _context.Database.OpenConnection ();
   using (var result = command.ExecuteReader ())
   {
      if (result.HasRows)
      {
         result.Read ();
         var x = result.GetInt32 (0); // x = your sp count value
      }
   }
}

EDIT (extension example):

public static class EfCoreExtensions
{
    public static int? Execute_SingleValue_SP_AsInt(this AppDbContext context,string SpName)
    {
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = SpName;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            context.Database.OpenConnection();
            using (var result = command.ExecuteReader())
            {
                if (result.HasRows)
                {
                    result.Read();
               var x = result.GetInt32(0); // x = your sp count value
                    return x;
                }
            return null;
            }
        
        }
    }
}

---

Related Topics