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

.NET Standard 2.x

Asynchronously executes a SQL stored procedure and returns a Task<IList<TModel>> object which represents the result set.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Syntax

  public  Task<IList<TModel>> SelectProcedureAsync<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

Task<IList<TModel>>

Returns a task that represents the asynchronous operation.

Examples

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using SnapObjects.Data;

namespace Appeon.ApiDoc.ISqlExecutorExamples
{
    public class SelectProcedureAsyncExample
    {
        private readonly SchoolContext _context;
        
        public SelectProcedureAsyncExample(SchoolContext dataContext)
        {
            // Sets the data context
            _context = dataContext;
        }

        public async Task<IList<DynamicModel>> Example1()
        {
            // 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");
            
            // Asynchronously Executes the SQL statement and puts the query result
            // to a list of models.
            IList<DynamicModel> result = await _context.SqlExecutor
                .SelectProcedureAsync<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". 
            decimal personId = result.FirstOrDefault().GetValue<decimal>("NewPersonID");
            
            Console.WriteLine("New Person ID: {0}", personId);
            
            return result;
            
            /*This code produces the following output:
            
                New Person ID: 2002
            */
        }
    }
}

Applies to

.NET Standard

2.x