/// <summary>
/// Read user's authorization.
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public UserEntity ReadAuthorization(Guid userId)
{
lock (Locker)
{
Object parameters = new
{
@UserID = userId
};
Open();
UserEntity user = null;
using (SqlMapper.GridReader results = Connection.QueryMultiple("[users].[Users_Read_Authorization]", parameters, commandType: CommandType.StoredProcedure))
{
user = results.ReadSingleOrDefault <UserEntity>();
if (user != null)
{
user.Email_Address = results.ReadSingleOrDefault <EmailAddressEntity>();
user.Password = results.ReadSingleOrDefault <PasswordEntity>();
user.Roles = results.Read <UserRoleEntity>().ToList();
}
}
Close();
return(user);
}
}
/// <summary>
/// Get PagedResultSet from a execute query with not nested object with/without TotalCount using the IncludeMetada
/// </summary>
/// <param name="fullSqlQuery">Sql Query to execute</param>
/// <param name="filter">The SearchFilter </param>
/// <param name="param">Parameters to blind with the sql query</param>
/// <param name="buffered"></param>
/// <returns></returns>
public IPaginatedList <TReturn> ExecutePagedQuery <T1, T2, T3, T4, T5, T6, T7, T8, T9, TReturn>(Func <T1, T2, T3, T4, T5, T6, T7, T8, T9, TReturn> func, string fullSqlQuery, ISearchFilter filter, object param = null, string splitOn = "id", bool buffered = true)
{
// Paging construct helper
string sqlGetQuery = PagedQueryBuilder.PagedQuery(fullSqlQuery, filter, ref param);
SqlMapper.GridReader reader = DbConnection.QueryMultiple(sqlGetQuery, param, CurrentTransaction);
Type[] types =
{
typeof(T1),
typeof(T2),
typeof(T3),
typeof(T4),
typeof(T5),
typeof(T6),
typeof(T7),
typeof(T8),
typeof(T9)
};
IList <TReturn> entity = (IList <TReturn>)reader.Read(types, objects => func((T1)objects[0],
(T2)objects[1],
(T3)objects[2],
(T4)objects[3],
(T5)objects[4],
(T6)objects[5],
(T7)objects[6],
(T8)objects[7],
(T9)objects[8]), splitOn, buffered);
int?totalCount = filter.IncludeMetadata ? reader.ReadSingleOrDefault <int?>() : null;
return(new PaginatedList <TReturn>(entity, filter, totalCount));
}
public override IPageResult <TKey, TEntity> SelectPage(IDbConnection conn, int pageNumber, int pageSize, IEnumerable <Expression <Func <TEntity, object> > > selectFields,
WhereClauseResult whereClause = null, string sqlOrderbyClause = null, IDbTransaction tr = null)
{
if (conn == null)
{
throw new ArgumentNullException(nameof(conn));
}
var result = this.ConstructMsSqlSelectPage(pageNumber, pageSize, selectFields, whereClause, sqlOrderbyClause);
try
{
SqlMapper.GridReader reader = conn.QueryMultiple(result.sql, result.dynParms, tr);
int totalNumberOfRecords = reader.ReadSingleOrDefault <int>();
IEnumerable <TEntity> entities = reader.Read <TEntity>();
return(new PageResult <TKey, TEntity>(pageNumber, pageSize, totalNumberOfRecords).AddItems(entities));
}
catch (Exception ex)
{
IDictionary <string, object> parameters = new Dictionary <string, object>();
foreach (var item in result.dynParms.ParameterNames)
{
parameters.Insert(item, result.dynParms.Get <object>(item));
}
throw new ExecuteSqlErrorException(result.sql, parameters, ex.Message, ex);
}
}
/// <summary>
/// Get PagedResultSet from a execute query with not nested object with/without TotalCount using the IncludeMetada
/// </summary>
/// <param name="fullSqlQuery">Sql Query to execute</param>
/// <param name="filter">The SearchFilter </param>
/// <param name="param">Parameters to blind with the sql query</param>
/// <param name="buffered"></param>
/// <returns></returns>
public IPaginatedList <TReturn> ExecutePagedQuery <TReturn>(string fullSqlQuery, ISearchFilter filter, object param = null, bool buffered = true)
{
// Paging construct helper
string sqlGetQuery = PagedQueryBuilder.PagedQuery(fullSqlQuery, filter, ref param);
SqlMapper.GridReader reader = DbConnection.QueryMultiple(sqlGetQuery, param, CurrentTransaction);
IEnumerable <TReturn> entity = reader.Read <TReturn>(buffered);
int?totalCount = filter.IncludeMetadata ? reader.ReadSingleOrDefault <int?>() : null;
return(new PaginatedList <TReturn>(entity, filter, totalCount));
}
/// <summary>
/// Get PagedResultSet from a execute query with not nested object with/without TotalCount using the IncludeMetada
/// </summary>
/// <param name="fullSqlQuery">Sql Query to execute</param>
/// <param name="filter">The SearchFilter </param>
/// <param name="param">Parameters to blind with the sql query</param>
/// <param name="buffered"></param>
/// <returns></returns>
public IPaginatedList <TReturn> ExecutePagedQuery <TReturn>(Type[] types, Func <object[], TReturn> map, string fullSqlQuery, ISearchFilter filter, object param = null, string splitOn = "id", bool buffered = true)
{
// Paging construct helper
string sqlGetQuery = PagedQueryBuilder.PagedQuery(fullSqlQuery, filter, ref param);
SqlMapper.GridReader reader = DbConnection.QueryMultiple(sqlGetQuery, param, CurrentTransaction);
IList <TReturn> entity = (IList <TReturn>)reader.Read(types, map, splitOn, buffered);
int?totalCount = filter.IncludeMetadata ? reader.ReadSingleOrDefault <int?>() : null;
return(new PaginatedList <TReturn>(entity, filter, totalCount));
}
/// <summary>
/// Executes query sql text or stored procedure for item with list.
/// </summary>
/// <typeparam name="TFirst">Type of first item</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <param name="sqlQuery">SQL Query</param>
/// <param name="commandType">SQL Query command type</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
protected virtual QueryMultipleSingleAndListReturnItem <TFirst, TSecond> ExecuteQueryMultipleSingleWithListByCommandType <TFirst, TSecond>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryMultipleSingleAndListReturnItem <TFirst, TSecond> returnItem = new QueryMultipleSingleAndListReturnItem <TFirst, TSecond>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
using (SqlMapper.GridReader gridReader = ExecuteQueryMultiple(sqlQuery, commandType, parameters))
{
returnItem.FirstItem = gridReader.ReadSingleOrDefault <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
}
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
