Topic:   How to execute a non-query SQL stored procedure in asp.net core razor pages
Feb 16, 2021 11:09 2 Replies 42 Views TEJA

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?

Prev Next
Topic Replies (2)
  1. 1
    idnkx user

    PARTH

    You can try ExecuteSqlCommad -

    context.Database.ExecuteSqlCommand

    1
    idnkx user

    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;

Leave a Reply
Guest User

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