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

Question

I want to insert the data from ASP.NET Web API to my data in SQL Server in pre defined table. First I am getting response after calling REST API and want to insert the response in table in database.

API Controller

[HttpGet]
public async Task<IActionResult> GetOutput(int id)
{
    string CheckJob = "exec sp_GetJob "+ "@id= " + id;

    var result = _context.Job.FromSqlRaw(CheckJob).ToList().FirstOrDefault();
    if(result == null){
        string InsertResult = "exec sp_add" + "@result = " + result;
        _context.FromSqlRaw(InsertResult);  <-- Don't know how to call stored procedure
    }

    return Ok(result);
}

Stored Procedure

create procedure sp_add @result int
as
begin 
    insert into School(id, created_date)
        values (@result, GetDate());
end

I am facing issue in calling stored procedure to insert data. Can somebody please help me out?

Thank you

Your Web API won't be using SSMS - thats purely a client UI. — Dale K
– Dale K, Commented Mar 29, 2022 at 7:12
There seem to be a lot of examples of how to do this out there. What resources have you checked out? Where are you stuck? — Dale K
– Dale K, Commented Mar 29, 2022 at 7:15
@DaleK Yes _context I tried using Entity Framework. But I know it is not correct implementation. — blac040
– blac040, Commented Mar 29, 2022 at 7:21
Calling an sp is pretty much the same regardless of whether it returns data. You just don't get anything back — Dale K
– Dale K, Commented Mar 29, 2022 at 7:24

Serge · Accepted Answer · 2022-03-29 13:47:16Z

1

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);

edited Mar 29, 2022 at 13:47

answered Mar 29, 2022 at 13:27

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blac040 Over a year ago

my intelligence is not showing ExecuteSqlCommand

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related