ReadToDictionary and ReadAsDictionary extension methods

The ReadToDictionary and ReadToDictionary extension methods read result of a single select resultset with multiple records to a System.Collections.Generic.Disctionary where the first column of the resultset is used as a dictionary key. A dictionary value depends on the TValue type.

If the TValue type is a value (int, date, bool or string) then the second column of the resultset is used as a dictionary value.

If the TValue type is an object then the whole record of the resultset (starting again from the first column) is used as a dictionary value.

In case when the TValue type is an object:

• ReadToDictionary extension method uses Mappers prepared in advance,
• ReadAsDictionary extension method uses Auto-Mapping.

When the TValue type is a value (int, date, bool or string), it does not matter what to use ReadToDictionary or ReadAsDictionary.

	Sync method	Async method
Using mappers	ReadToDictionary<TKey,TValue>	ReadToDictionaryAsync<TKey,TValue>
Using automappers	ReadAsDictionary<TKey,TValue>	ReadAsDictionaryAsync<TKey,TValue>

If select returns nothing, then ReadToDictionary returns an empty Dictionary<TKey,TValue>.

The ReadToDictionary extension method works on Base, Command and Reader levels (read more about these levels).

On Base and Command levels the ReadToDictionary and ReadAsDictionary extension methods:

• Open connection;
• Execute SqlDataReader with CommandBehavior.SingleResult flag;
• Read all records of the first select;
• Convert and add every record to a dictionary;
• Close connection;
• Return result of Dictionary<TKey,TValue> type.

On Reader level the ReadToDictionary and ReadAsDictionary extension methods:

• Reads all records of the current resultset;
• Converts and adds every record to a dictionary;
• Returns result of Dictionary<TKey,TValue> type.

Why would you use ReadToDictionary and ReadAsDictionary methods

Newtonsoft Json.NET library serializes a dictionary of roles, for example, as:

{
    "1": {"Id":1,"Code":"Armorer","Name":"Armorer"},
    "2": {"Id":2,"Code":"Blacksmith","Name":"Blacksmith"},
    "3": {"Id":3,"Code":"Bladesmith","Name":"Bladesmith"},
    "4": {"Id":4,"Code":"Joiner","Name":"Joiner"},
    "5": {"Id":5,"Code":"Cooper","Name":"Cooper"}
}

A browser parses the above JSON string to a JavaScript associative array.

Arrays with named indexes are called associative arrays (or hashes) object

And the following JavaScript code will work:

var roles = JSON.parse('{"1":{"Id":1,"Code":"Armorer","Name":"Armorer"},"2":{"Id":2,"Code":"Blacksmith","Name":"Blacksmith"},"3":{"Id":3,"Code":"Bladesmith","Name":"Bladesmith"}}');

var roleId = 2;

var roleName = roles[roleId].Name;

alert(roleName);

See how it works in jsfiddle

Thus the Role associative array in JavaSctipt code allows us to search a role by Id.

Examples

Example of ReadToDictionary and ReadToDictionary extensions in repository methods on the Base level:

public class Repository: RepositoryBase
{
    // read Dictionary<byte, string> synchronously on the Base level

    public Dictionary<byte, string> GetNameDictionary()
    {
        return base.ReadToDictionary<byte, string>("select Id, Name from dbo.Roles");
    }

    // read Dictionary<byte, Role> synchronously on the Base level using existing mappers

    public Dictionary<byte, Role> GetRoleToDictionary()
    {
        return base.ReadToDictionary<byte, Role>("select * from dbo.Roles");
    }

    // read Dictionary<byte, Role> synchronously on the Base level using automappers

    public Dictionary<byte, Role> GetRoleAsDictionary()
    {
        return ReadAsDictionary<byte, Role>("dbo.GetRoles");
    }

    // read Dictionary<byte, Role> asynchronously on the Base level using existing mappers

    public async Task<Dictionary<byte,Role>> GetRoleToDictionaryAsync()
    {
        return await ReadToDictionaryAsync<byte, Role>("select * from dbo.Roles");
    }
}

Example of ReadToDictionary and ReadToDictionary extensions in repository methods on the Command level:

public class Repository: RepositoryBase
{
    // read Dictionary<byte, string> synchronously on the Command level

    public Dictionary<byte, string> GetNameDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select Id, Name from dbo.Roles");
            return cmd.ReadToDictionary<byte, string>();
        });
    }

    // read Dictionary<byte, Role> synchronously on the Command level using existing mappers

    public Dictionary<byte, Role> GetRoleToDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select * from dbo.Roles");
            return cmd.ReadToDictionary<byte, Role>();
        });
    }

    // read Dictionary<byte, Role> synchronously on the Command level using automappers

    public Dictionary<byte, Role> GetRoleAsDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseProcedure("dbo.GetRoles");
            return cmd.ReadAsDictionary<byte, Role>();
        });
    }

    // read Dictionary<byte, Role> asynchronously on the Command level using existing mappers

    public async Task<Dictionary<byte,Role>> GetRoleToDictionaryAsync()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select * from dbo.Roles");
            return cmd.ReadToDictionaryAsync<byte, Role>();
        });
    }
}

Example of ReadToDictionary and ReadToDictionary extensions in repository methods on the Reader level:

public class Repository: RepositoryBase
{
    // read Dictionary<byte, string> synchronously on the Reader level

    public Dictionary<byte, string> GetNameDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select Id, Name from dbo.Roles");
            return cmd.GetByReader(dr => {           
                return dr.ReadToDictionary<byte, string>();
            });
        });
    }

    // read Dictionary<byte, Role> synchronously on the Reader level using existing mappers

    public Dictionary<byte, Role> GetRoleToDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select * from dbo.Roles");
            return cmd.GetByReader(dr => {           
                return dr.ReadToDictionary<byte, Role>();
            });
        });
    }

    // read Dictionary<byte, Role> synchronously on the Reader level using automappers

    public Dictionary<byte, Role> GetRoleAsDictionary()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseProcedure("dbo.GetRoles");
            return cmd.GetByReader(dr => {           
                return dr.ReadAsDictionary<byte, Role>();
            });
        });
    }

    // read Dictionary<byte, Role> asynchronously on the Reader level using existing mappers

    public async Task<Dictionary<byte, Role>> GetRoleToDictionaryAsync()
    {
        return await GetByCommandAsync(cmd =>
        {
            cmd.UseSql("select * from dbo.Roles");
            return cmd.GetByReaderAsync(dr => {           
                return dr.ReadToDictionary<byte, Role>();
            });
        });
    }
}

See also:

• Tests with dictionary reading
• Example of Role mapper