public async Task <bool> IsUserNameExists(string userName) { using (var connection = dataBaseConnectionProvider.GetConnection()) { connection.Open(); var command = new SqlCommand($"SELECT * FROM Users WHERE UserName = '******'", connection); var reader = await command.ExecuteReaderAsync().ConfigureAwait(false); var result = reader.HasRows; reader.Close(); return(result); } }
public async Task <Guid> CreateSessionOnUser(Guid userId) { using (var connection = dataBaseConnectionProvider.GetConnection()) { connection.Open(); var newSessionId = Guid.NewGuid(); var command = new SqlCommand($"INSERT INTO USessions VALUES('{newSessionId.ToString()}','{userId.ToString()}')", connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); return(newSessionId); } }
public async Task <IEnumerable <MovieApi.ClientModels.Movies.Movie> > GetMoviesByDate(DateTime dateTime) { using (var connection = dataBaseConnectionProvider.GetConnection()) { connection.Open(); var command = new SqlCommand($"SELECT * FROM Movies WHERE MovieDate='{dateTime.Date}'", connection); var movies = new List <MovieApi.ClientModels.Movies.Movie>(); var reader = await command.ExecuteReaderAsync().ConfigureAwait(false); while (reader.Read()) { movies.Add(new MovieApi.ClientModels.Movies.Movie() { Id = reader.GetInt32(0), FilmName = reader.GetString(1), Date = reader.GetDateTime(2), Time = reader.GetTimeSpan(3), }); } reader.Close(); return(movies); } }
public async Task <CustomerDetails> Ask(FindByIdQuery <CustomerDetails> query) { using (var connection = _provider.GetConnection()) { connection.Open(); return(await connection.QuerySingleOrDefaultAsync <CustomerDetails>( @"SELECT Id, Name, Email FROM Customers WHERE Id = @Id", new { query.Id })); } }
public async Task <string> GetUserId() { using (var connection = dataBaseConnectionProvider.GetConnection()) { connection.Open(); var command = new SqlCommand($"SELECT * FROM USessions WHERE SessionId = '{sessionId}'", connection); var reader = await command.ExecuteReaderAsync().ConfigureAwait(false); if (reader.Read()) { return(reader.GetString(1)); } } return(null); }
public async Task <bool> Ask(CheckExistingCustomerByEmailQuery query) { using (var connection = _provider.GetConnection()) { connection.Open(); return(await connection.ExecuteScalarAsync <bool>( @"SELECT count(1) FROM Customers WHERE Email = @Email", new { query.Email })); } }
public async Task <IEnumerable <Customer> > Ask(GetManyQuery <Customer> query) { using (var connection = _provider.GetConnection()) { connection.Open(); return(await connection.QueryAsync <Customer>( @"SELECT Id FROM Customers ORDER BY Id DESC OFFSET (@SKIP) ROWS FETCH NEXT (@Top) ROWS ONLY;", new { query.Top, query.Skip })); } }
public async Task ExecuteAsync(CreateCustomerCommand command) { using (var connection = _provider.GetConnection()) { connection.Open(); using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { await connection.ExecuteAsync( @"INSERT INTO Customers (Name, Email) VALUES(@Name, @Email)", new { command.Name, command.Email }, transaction); transaction.Commit(); } } }
public async Task <IEnumerable <Order> > Ask(GetManyOrdersQuery query) { using (var connection = _provider.GetConnection()) { connection.Open(); return(await connection.QueryAsync <Order>( @"SELECT o.Id, o.Price, o.CreatedDate FROM Orders o Where o.CustomerId=@CustomerId ORDER BY Id DESC OFFSET (@SKIP) ROWS FETCH NEXT (@Top) ROWS ONLY;", new { query.CustomerId, query.Top, query.Skip })); } }
public async Task ExecuteAsync(CreateOrderCommand command) { using (var connection = _provider.GetConnection()) { connection.Open(); using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { await connection.ExecuteAsync( @"INSERT INTO Orders (Price, CustomerId) VALUES(@Price, @CustomerId)", new { command.Price, command.CustomerId }, transaction); transaction.Commit(); } } }