SqlExecutorExtensions.ExecuteProcedure(string sqlText, out DbResultSet resultSet, params object[] parameters) Method

.NET Standard 2.x

Executes a stored procedure which returns a result set.

Namespace: PowerScript.Bridge

Assembly: PowerScript.Bridge.dll

Syntax

public static void ExecuteProcedure(this ISqlExecutor sqlExecutor, string sqlText, out DbResultSet resultSet, params object[] parameters)

Parameters

sqlText System.String

The SQL statement which executes the stored procedure.

resultSet PowerScript.Bridge.DbResultSet

The data result set and the execution result.

parameters System.Object[]

(Optional) The parameters for executing the SQL stored procedure.

One or more ParamValue objects, containing the parameter direction and values, which correspond to the SQL stored procedure's parameters. See ParamValue for more info.

Examples

The following code example demonstrates how to use the ExecuteProcedure method to execute a stored procedure and return the result set which is stored in the DbResultSet object.

using System;
using SnapObjects.Data;
using PowerScript.Bridge;

namespace Appeon.ApiDoc.ISqlExecutorExtensionsExamples
{
    public class ExecuteProcedureExample
    {
        private readonly SchoolContext _context;

        public ExecuteProcedureExample(SchoolContext dataContext)
        {
            // Sets the data context
            _context = dataContext;
        }

        public void Example()
        {
            // Specifies the stored procedure to be executed which gets the data of StudentGrade table according to StudentID
            string sql = "GetStudentGrades";

            /* StoredProcedure GetStudentGrades：
            
            CREATE PROCEDURE [dbo].[GetStudentGrades]
            @StudentID int
            AS
            SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
            WHERE StudentID = @StudentID

            GO
            */

            // Specifies the parameter to be passed to the stored procedure
            int studentID = 2;

            // Executes the stored procedure and stores the result set in the DbResultSet object.
            _context.SqlExecutor.ExecuteProcedure(sql,
                out DbResultSet resultSet,
                ParamValue.New<int>("StudentID", studentID));

            resultSet.Next();

            // Shows the result set of the stored procedure
            while (resultSet.SqlCode == 0)
            {
                Console.WriteLine($"EnrollmentID: {resultSet.GetValue<int>("EnrollmentID")}, " +
                    $"Grade: {resultSet.GetValue<decimal>("Grade")}, " +
                    $"CourseID: {resultSet.GetValue<int>("CourseID")}, " +
                    $"StudentID: {resultSet.GetValue<int>("StudentID")}");

                resultSet.Next();
            }

            resultSet.Close();

            /*This code produces the following output:
            
            EnrollmentID: 1, Grade: 4.00, CourseID: 2021, StudentID: 2
            EnrollmentID: 2, Grade: 3.50, CourseID: 2030, StudentID: 2
            */
        }
    }
}

Applies to

.NET Standard

2.x