SqlDataReader Extentions - lobodava/artisan-orm GitHub Wiki

Word "Read" in extension name means reading of one result set from one select.

After the reading, a Read extension checks if there is the next result set and, if any, moves the cursor to it calling NextResult() method. This allows to read multiple result sets one by one:

var grandRecord   = reader.ReadTo<GrandRecord>();
var records       = reader.ReadToList<Record>();
var childRecords  = reader.ReadToList<ChildRecord>();

Every Read extension has optional parameter bool getNextResult = true, so if it is required to stay on the current result set after reading, the call of NextResult() can be cancelled.

SqlDataReader extentions for data reading:

• To extensions which use the Mappers:

  • reader.ReadTo<T>
  • reader.ReadToList<T>
  • reader.ReadToArray<T>
  • reader.ReadToObjectRow<T>
  • reader.ReadToObjectRows<T>
  • reader.ReadToDictionary<TKey,TValue>

• As extensions which use the Auto-Mapping:

  • reader.ReadAs<T>
  • reader.ReadAsList<T>
  • reader.ReadAsArray<T>
  • reader.ReadAsObjectRow
  • reader.ReadAsObjectRows
  • reader.ReadAsDictionary<TKey,TValue>

• Extension for reading data within action parameter:

  • reader.Read(Action)

Example:

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

        return cmd.GetByReader(reader =>
        {
            var user     = reader.ReadTo<User>();
            user.RoleIds = reader.ReadToArray<byte>();            
            
            return user;
        });
    });
}

Where the stored procedure dbo.GetUserById looks like this:

create procedure dbo.GetUserById
    @Id    int
as
begin
    set nocount on;

    -- read User

    select Id, [Login], Name, Email from dbo.Users where Id = @Id;

    -- read User RoleIds

    select RoleId from dbo.UserRoles where UserId = @Id;
end;

The class 'SqlDataReader' (of System.Data.SqlClient namespace) has standard Get methods like: GetInt32, GetBoolean or GetString. The Get methods are used to read column values.

Within Artisan.Orm the Get methods are used in Mappers and in manual object properties reading.

Artisan.Orm extends the list of Get methods by adding the Nullable versions: GetInt32Nullable, GetBooleanNullable or GetStringNullable, and other useful methods:

Examples:

public IList<User> GetUsersWithRoles()
{
    return GetByCommand(cmd =>
    {
        cmd.UseSql( "select * from dbo.vwUsers; " +
                    "select UserId, RoleId from dbo.UserRoles;" +
                    "select Id, Code, Name from dbo.Roles");

        return cmd.GetByReader(reader =>
        {
            var users = reader.ReadToList<User>();
            var userRoles = reader.ReadToList(r => new {UserId = r.GetInt32(0), RoleId = r.GetByte(1)});
            var roles = reader.ReadAsList<Role>();
                
            reader.Close();

            foreach (var user in users)
            {
                user.Roles = new List<Role>();

                foreach (var role in roles)
                    if (userRoles.Any(ur => ur.UserId == user.Id && ur.RoleId == role.Id))
                        user.Roles.Add(role);
            }

            return users;
        });
    });
}