public static async Task <bool> UserExists(this IdapadDataAccess dataAccess, string userName) { User user = await dataAccess.QueryFirstOrDefaultAsync <User> ("select * from Appuser where username = @username", new { userName }); return((user != null) ? true : false); }
public static async Task <User> Login(this IdapadDataAccess dataAccess, UserRegister userRegister) { var userName = userRegister.UserName.ToLower(); UserToRegister userToRegister = await dataAccess.QueryFirstOrDefaultAsync <UserToRegister> ("select a.*,fu.FirmId, f.Name FirmName from Appuser a " + "left join FirmUsers fu On a.Id = fu.UserId " + "left join Firm f On f.Id = fu.firmId " + "where a.UserName = @UserName", new { userName }); if (userToRegister == null) { return(null); } if (!VerifyPasswordHash(userRegister.Password, userToRegister.PasswordHash, userToRegister.PasswordSalt)) { return(null); } var user = new User(); user.Id = userToRegister.Id; user.FirstName = userToRegister.FirstName; user.LastName = userToRegister.LastName; user.UserName = userToRegister.UserName; user.EmailAddress = userToRegister.EmailAddress; user.FirmName = userToRegister.FirmName; user.FirmId = userToRegister.FirmId; return(user); }
public static Task <User> GetUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName) { return(dataAccess.QueryFirstOrDefaultAsync <User> ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress " + "from Appuser a " + "where a.UserName = @UserName", new { userName })); }
private static async Task <int> DoestheFirmExists(this IdapadDataAccess dataAccess, Firm firm) { int firmId = await dataAccess.QueryFirstOrDefaultAsync <int>( "select * from Firm where Name = @Name", firm); return(firmId); }
private static async Task <int> DoestheFirmProductExists(this IdapadDataAccess dataAccess, FirmProduct firmProduct) { int firmProductId = await dataAccess.QueryFirstOrDefaultAsync <int>( "select Id from FirmProducts where ProductId = @ProductId and FirmId = @FirmId", firmProduct); return(firmProductId); }
public static Task <User> GetUserAsync(this IdapadDataAccess dataAccess, int id) { return(dataAccess.QueryFirstOrDefaultAsync <User> ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " + "f.Name FirmName, f.Type FirmType from Appuser a " + "left join FirmUsers fu On a.Id = fu.UserId " + "inner join Firm f On f.Id = fu.firmId " + "where a.Id = @id", new { id })); }
public static Task <User> GetFirmUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName) { return(dataAccess.QueryFirstOrDefaultAsync <User> ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " + "f.Name FirmName, f.Type FirmType from Appuser a " + "left join FirmUsers fu On a.Id = fu.UserId " + "left join Firm f On f.Id = fu.firmId " + "where a.UserName = @UserName", new { userName })); }
public static async Task <PagedResults <Product> > GetProductByAsync(this IdapadDataAccess dataAccess, int page, int pageSize, string sort, int?brandId, int?typeId, string search) { string sql = "SELECT p.[Id], p.[Name], p.[Description], p.[Price], p.[PictureUrl]," + "b.[Name] ProductBrand, t.[Name] ProductType " + "FROM Products p " + "INNER JOIN ProductBrands b on p.ProductBrandId = b.id " + "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " + "WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " + "AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " + "AND p.[Name] like '%' + isnull(@Search, P.Name) + '%' " + "ORDER BY " + "case when @Sort = 'priceAsc' THEN p.Price END ASC, " + "case when @Sort = 'priceDesc' THEN p.Price END DESC, " + "case when isnull(@Sort, 'name') = 'name' then p.[Name] end ASC, " + "case when @Sort = 'namedesc' then p.[Name] end DESC " + "OFFSET @Offset ROWS " + "FETCH NEXT @PageSize ROWS ONLY"; string countSql = "SELECT count(*) " + "FROM Products p " + "INNER JOIN ProductBrands b on p.ProductBrandId = b.id " + "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " + "WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " + "AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " + "AND p.[Name] like '%' + isnull(@Search, P.Name) + '%'"; var results = new PagedResults <Product>(); var TCparameters = new { BrandId = brandId, TypeId = typeId, Search = search }; var TotalCount = await dataAccess.QueryFirstOrDefaultAsync <int>(countSql, TCparameters); var parameters = new { Offset = (page - 1) * pageSize, PageSize = pageSize, Sort = sort, BrandId = brandId, TypeId = typeId, Search = search }; var products = await dataAccess.QueryAsync <Product>( sql, parameters); results.Items = products; results.TotalCount = TotalCount; return(results); }
private static async Task <int> DoestheAddressExists(this IdapadDataAccess dataAccess, FirmAddress firmaddress) { int firmAddressId = await dataAccess.QueryFirstOrDefaultAsync <int>( "select Id from FirmAddress where " + "StreetAddress = @StreetAddress " + "AND isnull(AptAddress, 'AptAddress') = ISNULL(@AptAddress, isnull(AptAddress, 'AptAddress')) " + "AND City = @City AND State =@State AND ZipCode= @ZipCode", firmaddress); return(firmAddressId); }
public static async Task <Product> GetProductByIdAsync(this IdapadDataAccess dataAccess, int id) { string sql = "SELECT p.[Id], p.[Name], p.[PictureUrl], p.[Price], p.[Description], " + "b.[Name] ProductBrand, t.[Name] ProductType " + "FROM[dbo].[Products] p INNER JOIN ProductBrands b on p.ProductBrandId = b.id " + "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " + "WHERE p.Id = @Id "; var parameters = new { Id = id }; var product = await dataAccess.QueryFirstOrDefaultAsync <Product>( sql, parameters, null, (int?)CommandType.StoredProcedure); return(product); }