Topic: How to execute a non-query SQL stored procedure in asp.net core razor pages
I'm currently going through tutorials to help me learn Razor pages after many years of webforms.
I currently have the following code that returns data from an sql stored procedure when a button is pushed:
public IEnumerable SaveDeviationRequestResult(string SUB_ID)
{
var parameters = new List();
parameters.Add(new SqlParameter("@SUB_ID", SUB_ID));
//return context.Employees.Find(id); //If you want to use pure sql
return context.apiDeviationRequests.FromSqlRaw("CV2.Get_WorkstationDeviationRequests @SUB_ID", parameters: parameters.ToArray())
.ToList();
}
I did this just to ensure the button was being triggered but I want to run a stored procedure CV2.Save_DeviationAuth that has 2 parameters. @DeviationID int and @DeviationResult varchar(10)
This is a non-query stored procedure that doesn't return a result set. How do I do this please as the above code is only for stored procedures that return data?
Author: TEJA
Not sure what course is right for you?
Choose the right course for you.Get the help of our experts and find a course that best suits your needs.
Let`s Connect
PARTH
You can try ExecuteSqlCommad -
context.Database.ExecuteSqlCommand
FRAUSKY
You can try ExecuteSqlCommad
();
context.Database.ExecuteSqlCommand
I've now changed the code to a very basic form below using the tutorial thank you:
public void SaveDeviationRequestResult(int DeviationLogID, string Authorisation, string SUB_ID)
{
var parameters = new List
parameters.Add(new SqlParameter("@DeviationID", DeviationLogID));
parameters.Add(new SqlParameter("@DeviationResult", Authorisation));
parameters.Add(new SqlParameter("@SUB_ID", SUB_ID));
context.Database.ExecuteSqlCommand("CV2.Save_DeviationAuth @DeviationID, @DeviationResult, @SUB_ID", parameters: parameters.ToArray());
return;
}
The only thing is it underlines "ExecuteSQLCommand" and says:
DatabaseFacade does not contain a definition for ExecuteSQLCommand and no accessible extension method ExecuteSQLCommand accepting a first argument of type DatabaseFacade could be found.
I've got the following using declarations:
using System;
using System.Collections.Generic;
using System.Text;
using CV2.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading.Tasks;
using System.Data;