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