ISqlExecutor.SelectLazyAsync<TModel>( string sqlText, object[] parameters, CancellationToken cancellationToken) Method

.NET Standard 2.1 or higher

Asynchronously executes the SQL SELECT statement and returns an IAsyncEnumerable<TModel> object that can be used to load the result set lazily.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Syntax

public IAsyncEnumerable<TModel> SelectLazyAsync<TModel>(string sqlText, object[] parameters, CancellationToken cancellationToken);

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

sqlText System.String

A raw SQL Statement which contains the parameter placeholders.

The proper syntax of a parameter placeholder is specific to the data source (e.g., @newFirstName for SQL Server). To make a parameterized SQL ( e.g., update Person set FirstName = @newFirstName where PersonId = 1), these placeholders are filled in with the actual parameter values when the SQL statement is executed.

parameters System.Object[]

One or more objects which contain the values corresponding to the SQL parameter identifier names.

cancellationToken CancellationToken

A cancellation token that can be used by other objects or threads to receive notice of cancellation.

Returns

IAsyncEnumerable<TModel>

Returns an IAsyncEnumerable<TModel> object which can load the result set lazily.

Remarks

This method uses command behavior of CommandBehavior.SequentialAccess internally, which provides a way for the DataReader to handle rows that contain columns with large binary values.

It creates each model object and reads data from database to this model object only when the data is asked for. You can use this method to save resources and improve performance by deferring the initialization of expensive objects until they are requested.

After obtaining the required data, you need to call the ILazyQueryable <Model>.Dispose method to release the resources occupied by the database connection and DataReader. Before the returned ILazyQueryable <Model> object is disposed, the current DataContext cannot be used for other data operations. Otherwise, an exception will be thrown.

However, in some common cases, you can benefit from C# and do not have to explicitly call the ILazyQueryable <Model>.Dispose method, for example, when using the C# using statement or the C# foreach statement, it can implicitly dispose ILazyQueryable <Model>.

Examples

The following code example demonstrates how to use the SelectLazyAsync<TModel> (String, params object[], CancellationTokenSource) method to execute the SQL statement lazily. It uses a CancellationTokenSource type parameter to make this operation cancelable.

using SnapObjects.Data;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Appeon.ApiDoc.Models.School;

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

        public async void Example4(CancellationTokenSource cts)
        {
            // Defines the SQL statement.
            var sql = @"SELECT CourseID, Title
                        FROM Course
                        WHERE CourseID = @id";
                        
            int id = 1050;
            
            try
            {
                // Asynchronously Executes the SQL statement.
                // If a task has been cancelled, the call to the 
                // SqlExecutor.SelectLazyAsync method throws an OperationCanceledException.
                IAsyncEnumerable<DynamicModel> result = _context.SqlExecutor
                    .SelectLazyAsync<DynamicModel>(sql, new object[] { id }, cts.Token);
                                            
                await foreach (var model in result)
                {
                    Console.WriteLine("Result: {0}.", model.GetValue<string>(1));
                }
                
            }
            catch (OperationCanceledException e)
            {
                // The operation was cancelled before completion.
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally 
            {
                cts.Dispose();
            }
            
            /*This code produces the following output:
            
                A task was canceled.
            */
        }
    }
}

Applies to

.NET Standard

2.1 or higher