ISqlExecutor.SelectProcedure<TModel>(string procedureName, params object[] parameters) Method

.NET Standard 2.x

Executes a SQL stored procedure and returns an IList<TModel> object which contains the result set.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Syntax

  public IList<TModel> SelectProcedure<TModel>(string procedureName, params object[] parameters);

Type Parameters

TModel

The datatype of the model object which represents the data row in the result set.

You can also specify DynamicModel to TModel if you do not want to define a custom model class.

Parameters

procedureName System.String

The name of the SQL stored procedure.

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.

Returns

System.Collections.Generic.IList<TModel>

Returns an IList<TModel> object that contains the result set.

Examples

The following code example demonstrates how to use the ExecuteProcedure method to execute the specified procedure in the database and return the query result to a list of models.

using SnapObjects.Data;
using System;
using System.Data;
using System.Linq;

namespace Appeon.ApiDoc.ISqlExecutorExamples
{
    public class SelectProcedureExample
    {
        private readonly SchoolContext _context;

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

        public void Example()
        {
            // Specifies the procedure to be executed.
            // It inserts a person record to database and returns the new PersonID.
            var procedureName = "InsertPerson";

            /* StoredProcedure InsertPerson：

            CREATE PROCEDURE [dbo].[InsertPerson]
            @LastName nvarchar(50),
            @FirstName nvarchar(50),
            @HireDate datetime,
            @EnrollmentDate datetime,
            @Discriminator nvarchar(50)
            AS
            INSERT INTO dbo.Person (LastName,
            FirstName,
            HireDate,
            EnrollmentDate,
            Discriminator)
            VALUES (@LastName,
            @FirstName,
            @HireDate,
            @EnrollmentDate,
            @Discriminator);
            SELECT SCOPE_IDENTITY() as NewPersonID;

            GO
            */

            // Sets the parameter values.
            // The parameter direction defaults to ParameterDirection.Input,
            // so you can omit it.
            var lastName = ParamValue.New<string>("LastName", "Ruskin");
            var firstName = ParamValue.New<string>("FirstName", "Bob");
            var hireDate = ParamValue.New<DateTime?>("HireDate", null);
            var enrollmentDate =
                ParamValue.New<DateTime>("EnrollmentDate", new DateTime(2019, 1, 2));

            var discriminator = ParamValue.New<string>("Discriminator", "Student");

            // Executes the SQL statement and returns the new Person ID.
            var result = _context.SqlExecutor.SelectProcedure<DynamicModel>(
                procedureName,
                lastName,
                firstName,
                hireDate,
                enrollmentDate,
                discriminator);

            // Gets the new PersonID. 
            // It's a decimal type because the InsertPerson procedure returns result 
            // set by "SELECT SCOPE_IDENTITY() as NewPersonID". 
            var personId = result.FirstOrDefault().GetValue<decimal>("NewPersonID");

            Console.WriteLine("New Person ID: {0}", personId);

            /*This code produces the following output:

            New Person ID: 35
            */
        }
    }
}

Applies to

.NET Standard

2.x