public async Task <User> ValidateUser(string email, string password, UserType userTypeId) { SqlQuery proc = new SqlQuery(@" select * from users where email=@email and password =HashBytes('SHA2_256', @password) and userTypeId=@userTypeId; ", 30, System.Data.CommandType.Text); proc.AddInputParam("email", SqlDbType.NVarChar, email); proc.AddInputParam("password", SqlDbType.NVarChar, password); proc.AddInputParam("userTypeId", SqlDbType.Int, (int)userTypeId); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { return new UserResponse() { UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"), Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "userTypeId"), IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "isActive") }; } return null; })); }
public async Task <List <TeamPhotoResponse> > GetTeamImageDocs(int teamId) { SqlQuery proc = new SqlQuery(@" select * from team_images where teamId=@teamId; ", 30, System.Data.CommandType.Text); proc.AddInputParam("teamId", SqlDbType.Int, teamId); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { List <TeamPhotoResponse> ltsd = new List <TeamPhotoResponse>(); while (dataReader.Read()) { ltsd.Add(new TeamPhotoResponse() { TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"), ImageGuid = SqlQueryResultParser.GetValue <String>(dataReader, "ImageGuid"), Description = SqlQueryResultParser.GetValue <String>(dataReader, "Description"), Created = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created") }); } return ltsd; })); }
public async Task <User> GetUserFromAPIKey(string apiKey) { SqlQuery proc = new SqlQuery(@" select u.* from user_api_keys uak inner join users u on u.userId=uak.userId where uak.ApiKeyValue=@apikey; ", 30, System.Data.CommandType.Text); proc.AddInputParam("apiKey", SqlDbType.NVarChar, apiKey); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { return (new User() { UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"), Email = SqlQueryResultParser.GetValue <String>(dataReader, "email"), IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "IsActive"), Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "UserTypeId") }); } return null; })); }
private byte[] GetImageAsyncResult(IDataReader oReader) { while (oReader.Read()) { return(SqlQueryResultParser.GetValue <byte[]>(oReader, "ImageData")); } return(new Byte[] { }); }
/// <summary> /// Returns single column and row value. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="oReader"></param> /// <param name="isResultRequired"></param> /// <returns></returns> protected T GetReturnValue <T>(IDataReader oReader, bool isResultRequired = true) { T val = default(T); if (oReader.Read()) { val = SqlQueryResultParser.GetReturnValue <T>(oReader, isResultRequired: isResultRequired); } return(val); }
public async Task <List <User> > GetUserList() { SqlQuery proc = new SqlQuery(@" select userId, userTypeId, isActive, email from users; ", 30, System.Data.CommandType.Text); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { List <User> users = new List <User>(); while (dataReader.Read()) { users.Add(new User() { UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"), Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "userTypeId"), IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "isActive"), Email = SqlQueryResultParser.GetValue <String>(dataReader, "email") }); } return users; })); }
//TODO add user from Cognito public async Task <User> GetUserInfo(int userId) { SqlQuery proc = new SqlQuery(@" select * from users where UserId=@userId; ", 30, System.Data.CommandType.Text); proc.AddInputParam("userId", SqlDbType.Int, userId); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { return new User() { UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"), Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "userTypeId"), IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "isActive"), Email = SqlQueryResultParser.GetValue <String>(dataReader, "email") }; } return null; })); }
public async Task <TeamAdminResponse> GetTeamInfo(int teamId) { SqlQuery proc = new SqlQuery(@" select t.teamId, t.name, t.AdminUserId, t.IsTrial, t.IsHomePay, t.CreditNotes, u.Email, tc.Credits from team t inner join users u on t.AdminUserId=u.UserId left join team_credit tc on tc.TeamId=t.teamId where t.teamId=@teamId; ", 30, System.Data.CommandType.Text); proc.AddInputParam("teamId", SqlDbType.Int, teamId); return(await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { return (new TeamAdminResponse() { TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"), AdminEmail = SqlQueryResultParser.GetValue <String>(dataReader, "Email"), AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId"), TeamName = SqlQueryResultParser.GetValue <String>(dataReader, "Name"), IsTrial = SqlQueryResultParser.GetValue <Boolean>(dataReader, "IsTrial") }); } return null; })); }
public async Task <List <TeamInfo> > GetAllTeams(string search, bool sortasc, TeamSort sortby, int limit) { string whereSql = ""; if (!string.IsNullOrEmpty(search)) { whereSql = " where Name like @search or u.email like @search"; } string selectSql = @" t.*, tc.credits, u.email, u.Created "; string sortbySql; switch (sortby) { case TeamSort.Updated: sortbySql = "t.Updated"; break; case TeamSort.TeamName: sortbySql = "t.Name"; break; default: throw new ArgumentOutOfRangeException("sortby argument is invalid"); } string sortAscSql = ""; if (!sortasc) { sortAscSql = " desc "; } List <TeamInfo> teams = new List <TeamInfo>(); SqlQuery proc = new SqlQuery($@" select top {limit} {selectSql} from team t inner join users u on u.userId=t.adminUserId left outer join team_credit tc on tc.TeamId=t.TeamId {whereSql} order by {sortbySql} {sortAscSql}", 30, System.Data.CommandType.Text); proc.AddInputParam("@search", SqlDbType.NVarChar, "%" + search + "%", false); await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { TeamInfo tm = new TeamInfo() { TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"), TeamName = SqlQueryResultParser.GetValue <String>(dataReader, "Name"), AdminEmail = SqlQueryResultParser.GetValue <String>(dataReader, "Email"), Created = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created"), Updated = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Updated"), IsTrial = SqlQueryResultParser.GetValue <bool>(dataReader, "IsTrial"), AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId") }; teams.Add(tm); } return(teams); }); return(teams); }
public async Task <List <TeamInfo> > GetTeamInfoList(string searchName, string searchEmail, bool sortAsc, TeamSort teamSort) { string whereName = ""; if (!string.IsNullOrEmpty(searchName)) { whereName = " where Name like @searchName "; } string whereEmail = ""; if (!string.IsNullOrEmpty(searchEmail)) { whereEmail = " where u.email like @searchEmail "; } string sortSql = " t.Name "; if (teamSort == TeamSort.TeamName) { sortSql = " t.Name "; } else if (teamSort == TeamSort.Updated) { sortSql = " t.Updated "; } string sortAscSql = ""; if (!sortAsc) { sortAscSql = " desc "; } List <TeamInfo> lt = new List <TeamInfo>(); SqlQuery proc = new SqlQuery($@" select top 50 t.*, tc.credits, u.email, u.Created from team t inner join users u on u.userId=t.adminUserId left outer join team_credit tc on tc.TeamId=t.TeamId {whereEmail} {whereName} order by {sortSql} {sortAscSql}", 30, System.Data.CommandType.Text); proc.AddInputParam("@searchName", SqlDbType.NVarChar, "%" + searchName + "%", false); proc.AddInputParam("@searchEmail", SqlDbType.NVarChar, "%" + searchEmail + "%", false); await _queryExecutor.ExecuteAsync(proc, dataReader => { while (dataReader.Read()) { TeamInfo tm = new TeamInfo() { TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"), TeamName = SqlQueryResultParser.GetValue <String>(dataReader, "Name"), AdminEmail = SqlQueryResultParser.GetValue <String>(dataReader, "Email"), Created = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created"), Updated = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Updated"), IsTrial = SqlQueryResultParser.GetValue <bool>(dataReader, "IsTrial"), AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId") }; lt.Add(tm); } return(lt); }); return(lt); }
private string AddImageAsyncResult(IDataReader oReader) { oReader.Read(); return(SqlQueryResultParser.GetValue <string>(oReader, "imageGUID")); }