/// <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); }