none
Call and insert stored procedure API core 2.1 RRS feed

  • Question

  • Hi 

    1- code stored procedure

    ALTER PROCEDURE [dbo].[SP_InsertFollow]
    	
    (
    	@School_Id int,
        @Employee int ,
    	@No_teachers int ,
    	@No_Ma int ,
    	@No_us int
    AS
    BEGIN 
    
    INSERT INTO Follow_Basic
                             ( School_Id, Employee_Id, No_teachers, No_Ma, No_us)
    VALUES        (@School_Id,@Employee,@No_teachers,@No_Ma,@No_us)
    
     END

    Class Name SP_InsertFollow

        public class SP_InsertFollow
        {
            public int School_Id { get; set; }
            [Key]
            public int Employee { get; set; }
            public int No_teachers { get; set; }
            public int No_Ma { get; set; }
            public int No_us { get; set; }
        }

    3- Class DbContext

    using Microsoft.EntityFrameworkCore;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace WebApiOfficeEdu.Models
    {
        public class MyTestDBContext : DbContext
        {
    
            public  MyTestDBContext(DbContextOptions<MyTestDBContext> options)
             : base(options)
            { }
           
            public virtual DbSet<SP_InsertFollow> SP_InsertFollow { get; set; }
        }
    
    }
        

    Controllers Class

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using Microsoft.AspNetCore.Http;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.EntityFrameworkCore;
    using WebApiOfficeEdu.Models;
    
    namespace WebApiOfficeEdu.Controllers
    {
        [Route("api/[controller]")]
        [ApiController]
        public class InsertDataController : ControllerBase
        {
            private readonly MyTestDBContext _context;
    
            public InsertDataController(MyTestDBContext context)
            {
                _context = context;
            }
    
            [HttpPost]
            public void PostSP_InsertFollow()
            {
            _context.Query<SP_InsertFollow>().AsNoTracking().FromSql("SP_InsertFollow @School_Id," +
                            "@Employee,@No_teachers,@No_Ma,@No_us", 1001, 100, 500, 200, 100);
    
            }
        }
    }

    If run Error

    System.InvalidOperationException: 'Cannot create a DbQuery for 'SP_InsertFollow' because it is not a query type. Use the DbContext.Set method to create a DbSet instead.'

    Pleas Help me Sq Linq API

    • Moved by CoolDadTx Tuesday, January 29, 2019 2:46 PM EF related
    Tuesday, January 29, 2019 10:05 AM

All replies

  • Hello,

    See the following Microsoft documentation for how to use a SP with EF and at the bottom of this page which shows an SP performing a INSERT using the same syntax as in the first link.

    CREATE PROCEDURE usp_InsertCategory
     @CategoryName Varchar(300)
    AS
    BEGIN
        SET NOCOUNT ON;
        Insert into Categories Values (@CategoryName)
    END
    GO

    Code for above.

    var catName = "Personal Care";
    dataContext.Database
               .ExecuteSqlCommand("usp_InsertCategory @p0", catName);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 29, 2019 10:45 AM
  • You have Entity Framework Core issues that can be addressed at the EF forum.

    https://www.asp.net/downloads

    ASP.NET WebAPI Core issues can addressed at the ASP.NET forums.

    https://forums.asp.net/

    Tuesday, January 29, 2019 10:53 AM
  • Hi AL3ANEED,

    As Karen mentioned, the stored procedure does return ResultSet, we could use ExecuteSqlCommand to achieve it. for multiple parameters, please refer to the following code. 

     [HttpPost]
    public void PostSP_InsertFollow()
    {
                _context.Database.ExecuteSqlCommand("SP_InsertFollow @School_Id = {0}," +
                                "@Employee = {1}, @No_teachers = {2},@No_Ma = {3}, @No_us = {4}", 1001, 100, 500, 200, 100);
    
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 30, 2019 8:39 AM
    Moderator