Read Methods Understanding - lobodava/artisan-orm GitHub Wiki

In order to fetch data from a database the Artisan.Orm has a bunch of Read methods:

Note:

• To-methods use existing mappers for entity types; supported simple types such as int and string use the scalar path without a registered mapper.
• As-methods use automapping
• To-methods can also be used with inline mappers

Synchronous command scalar enumeration now returns every row, including the first row of one-row and nullable result sets. No initial Read() or skipped-row workaround is needed before the enumerable helper.

An explicitly supplied createFunc controls each row actually read, even when its result is int, int?, or string, and even when column zero is SQL NULL. It can read another column, substitute a value, or return null. The list, array, enumerable, async-stream, and multi-result factory paths follow that rule. Without a factory, simple types use the default scalar path. See Inline Mappers and read projection regressions.

These corrections do not change the lifetime and cancellation boundaries documented below. They describe the upcoming release's source contract, not a guarantee about an older installed package.

Read operations are available at three levels, with overloads and lifetime responsibilities that differ between them:

• Base level,
• Command level,
• Reader level.

If a repository is derived from the Artisan RepositoryBase then a repository method can use Read methods of the base:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public User? GetUserById(int id)
    {
        return base.ReadTo<User>("dbo.GetUserById", new SqlParameter("@Id", id));
    }
}

or even omit the base keyword:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public IList<User> GetAllUsers()
    {
        return ReadAsList<User>("dbo.GetAllUsers");
    }
}

New overloads from version 1.1.1:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public User? GetUserById(int id)
    {
        return ReadTo<User>("dbo.GetUserById", cmd => cmd.AddIntParam("@Id", id));
    }

    public async Task<User?> SaveUserAsync(User user, CancellationToken cancellationToken)
    {
        return await GetByCommandAsync((cmd, token) =>
        {
            cmd.UseProcedure("dbo.SaveUser");
            cmd.AddTableRowParam("@User", user);
            cmd.AddTableParam("@RoleIds", user.RoleIds);
            return cmd.GetByReaderAsync(reader =>
            {
                CheckForDataReplyException(reader);
                return reader.ReadTo<User>();
            }, token);
        }, cancellationToken);
    }

    public void DeleteUser(Int32 userId)
    {
        Execute("dbo.DeleteUser", cmd => cmd.AddIntParam("@UserId", userId) );
    }
}

The current sample's dbo.SaveUser returns a DataReply status before the saved user and expects both TVPs. The example consumes that header and then reads the payload; the callback's reader traversal is synchronous. A procedure that returns the user directly can use ReadToAsync<User> instead. See DataReply for the protocol.

For more convenient command configuration the RepositoryBase has GetByCommand and RunCommand methods, where the above Read methods can be called as extension methods from the command argument:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public User? GetUserById(int id)
    {
        return base.GetByCommand(cmd => 
        {
            cmd.UseProcedure("dbo.GetUserById");
            cmd.AddIntParam("@Id", id);

            return cmd.ReadTo<User>();
        });
    }
}

with omitting the base keyword:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public IList<User> GetAllUsers()
    {
        return GetByCommand(cmd => 
        {
            cmd.UseProcedure("dbo.GetAllUsers");
            return cmd.ReadAsList<User>();
        });
    }
}

When a procedure returns multiple result sets, call the Read methods on its SqlDataReader. This example assumes a user result set followed by a tinyint role-ID result set:

public class Repository: RepositoryBase
{
    public Repository(string connectionString) : base(connectionString) { }

    public User? GetUserById(int id)
    {
        return GetByCommand(cmd => 
        {
            cmd.UseProcedure("dbo.GetUserById");
            cmd.AddIntParam("@Id", id);

            return cmd.GetByReader(dr => 
            {
                var user = dr.ReadTo<User>();
                var roleIds = dr.ReadToArray<byte>();
                if (user != null)
                    user.RoleIds = roleIds;

                return user;
            });
        });
    }
}

Materializing reader operations such as ReadTo, ReadToList, and ReadToArray call NextResult() by default, including after an empty result set. Do not advance again between those calls. Use getNextResult: false when the caller needs to retain the current result set. Reader-level async streams (ReadToAsyncEnumerable / ReadAsAsyncEnumerable) do not advance to another result set; explicitly call NextResultAsync after enumeration when required.

Command-level read helpers dispose their reader and close the connection only if they opened it. A connection already opened by the caller remains open. Keep the repository and any caller-owned command/reader alive until reading or enumeration finishes.

In 4.1.0, repository ReadToEnumerable / ReadAsEnumerable shortcuts keep their command alive for enumeration and dispose it when enumeration finishes or its enumerator is disposed. Keep the repository alive and dispose a started enumerator promptly; foreach handles that even after break. When creating a command yourself, retain its scope for enumeration or materialize inside it:

public static IList<int> ReadIds(RepositoryBase repo)
{
    using var cmd = repo.CreateCommand("SELECT Id FROM dbo.Users ORDER BY Id;");
    return cmd.ReadToEnumerable<int>().ToList();
}

GetByCommandAsync / RunCommandAsync have overloads accepting a callback with CancellationToken; pass the token both to the wrapper and to the command operation inside it. ExecuteCommandAsync instead takes a synchronous configuration callback and executes the command itself.

GetByReaderAsync accepts a synchronous projection. It executes the command asynchronously, then invokes that projection while the reader is open. Do not return an asynchronous task or a deferred sequence from that projection: the reader is disposed when the callback returns. See cmd.GetByReader.

In 4.1.0, async command helpers forward cancellation to connection opening and command execution. Library-controlled materialization uses asynchronous row reads and result advancement with the token, including destination-list and multi-result overloads. Synchronous mapper/callback code remains responsible for its own cancellation checks; cancellation is cooperative. Check older package behavior before relying on these corrections.

Source: command reads, reader operations, and repository wrappers.

See also:

• Getting started
• About Connection Strings