/// <summary>
/// Inserts the specified book into cart.
/// </summary>
/// <param name="cart">The cart.</param>
/// <param name="userId">The user identifier.</param>
/// <returns>added cart item information</returns>
public async Task <CartResponseDto> Insert(CartRequestDto cart, int userId)
{
CartResponseDto cartItem = null;
using (SqlConnection connection = _dbContext.GetConnection())
{
SqlCommand command = new SqlCommand("sp_cart_insert", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@userId", userId);
command.Parameters.AddWithValue("@bookId", cart.BookId);
command.Parameters.AddWithValue("@quantity", cart.Quantity);
await connection.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
cartItem = MapReaderToCartDtoAsync(reader);
}
}
return(cartItem);
}
}
/// <summary>
/// Authenticates the user.
/// </summary>
/// <param name="loginDto">The login dto.</param>
/// <returns>user and password or null</returns>
public async Task <(UserResponseDto, string)> AuthenticateUser(string email)
{
UserResponseDto user = null;
string password = null;
SqlConnection _conn = _dBContext.GetConnection();
SqlCommand command = new SqlCommand("sp_users_getByEmail", _conn)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@email", email);
await _conn.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
user = MapUserFromReader(reader);
password = (string)reader["password"];
}
}
await _conn.CloseAsync();
return(user, password);
}
public async Task <FeatureCollection> GetGeoData(DateTime startTime, DateTime endTime, FilterOptions filter)
{
var query = @" SELECT Start_Lat AS latitude, Start_Lng AS longitude, ID as Id, Serverity
FROM AccidentEvents
WHERE Start_Time BETWEEN @StartTime AND @EndTime AND
(NOT @Amenity OR Amenity) AND
(NOT @Bump OR Bump) AND
(NOT @Crossing OR Crossing) AND
(NOT @GiveWay OR Give_Way) AND
(NOT @Junction OR Junction) AND
(NOT @NoExit OR No_Exit) AND
(NOT @Railway OR Railway) AND
(NOT @Roundabout OR Roundabout) AND
(NOT @Station OR Station) AND
(NOT @Stop OR Stop) AND
(NOT @TrafficCalming OR Traffic_Calming) AND
(NOT @TrafficSignal OR Traffic_Signal) AND
(NOT @TurningLoop OR Turning_Stop);";
var parameters = new DynamicParameters();
parameters.Add("@StartTime", startTime);
parameters.Add("@EndTime", endTime);
parameters.Add("@Amenity", (filter & FilterOptions.Amenity) == FilterOptions.Amenity, System.Data.DbType.Binary);
parameters.Add("@Bump", (filter & FilterOptions.Bump) == FilterOptions.Bump, System.Data.DbType.Binary);
parameters.Add("@Crossing", (filter & FilterOptions.Crossing) == FilterOptions.Crossing, System.Data.DbType.Binary);
parameters.Add("@GiveWay", (filter & FilterOptions.GiveWay) == FilterOptions.GiveWay, System.Data.DbType.Binary);
parameters.Add("@Junction", (filter & FilterOptions.Junction) == FilterOptions.Junction, System.Data.DbType.Binary);
parameters.Add("@NoExit", (filter & FilterOptions.NoExit) == FilterOptions.NoExit, System.Data.DbType.Binary);
parameters.Add("@Railway", (filter & FilterOptions.Railway) == FilterOptions.Railway, System.Data.DbType.Binary);
parameters.Add("@Roundabout", (filter & FilterOptions.Roundabout) == FilterOptions.Roundabout, System.Data.DbType.Binary);
parameters.Add("@Station", (filter & FilterOptions.Station) == FilterOptions.Station, System.Data.DbType.Binary);
parameters.Add("@Stop", (filter & FilterOptions.Stop) == FilterOptions.Stop, System.Data.DbType.Binary);
parameters.Add("@TrafficCalming", (filter & FilterOptions.TrafficCalming) == FilterOptions.TrafficCalming, System.Data.DbType.Binary);
parameters.Add("@TrafficSignal", (filter & FilterOptions.TrafficSignal) == FilterOptions.TrafficSignal, System.Data.DbType.Binary);
parameters.Add("@TurningLoop", (filter & FilterOptions.TurningLoop) == FilterOptions.TurningLoop, System.Data.DbType.Binary);
try
{
using (var conn = await _dbContext.GetConnection())
{
if (conn == null)
{
return(null);
}
List <Feature> points = conn.Query <PointWrapper>(query, parameters).Select(pos => {
var f = new Feature(pos.Point);
f.Properties.Add("Id", pos.Id);
f.Properties.Add("Serverity", pos.Serverity);
return(f);
}).ToList();
return(new FeatureCollection(points));
}
}
catch (Exception e)
{
_logger.LogWarning("AHHHH: " + e);
throw;
}
}
/// <summary>
/// Gets the paginated books from database.
/// </summary>
/// <param name="field">The field.</param>
/// <param name="limit">The limit.</param>
/// <param name="lastItemValue">The last item value.</param>
/// <param name="sortby">The sortby.</param>
/// <returns>List of book</returns>
public async Task <List <BookResponseDto> > Get(string field, int limit, string lastItemValue, string sortby)
{
List <BookResponseDto> bookList = new List <BookResponseDto>();
using (SqlConnection connection = _dBContext.GetConnection())
{
SqlCommand command = new SqlCommand("sp_books_get_paginated_result", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@limit", limit);
command.Parameters.AddWithValue("@lastItemValue", lastItemValue);
command.Parameters.AddWithValue("@field", field);
command.Parameters.AddWithValue("@sortby", sortby);
await connection.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
bookList.Add(MapReaderTobook(reader));
}
}
}
return(bookList);
}
public async Task <IEnumerable <CoronaStatistic> > GetCoronaStatistic()
{
var query = "SELECT * FROM CoronaStatistic";
try
{
using (var con = await _dbContext.GetConnection())
{
var result = con.Query <CoronaStatistic>(query);
return(result);
}
}
catch (Exception ex)
{
_logger.LogError("SQL Connection and Query failed!", ex);
return(null);
}
}
//Bulk Insert data
public void BulkInsertIntoTable(IDBContext dbContext, DataTable sourceDataTable, List <SqlBulkCopyColumnMapping> columnMappings, String destinationTableName)
{
using (var bulkCopy = new SqlBulkCopy(dbContext.GetConnection()))
{
bulkCopy.DestinationTableName = destinationTableName;
foreach (var columnMapping in columnMappings)
{
bulkCopy.ColumnMappings.Add(columnMapping);
}
bulkCopy.WriteToServer(sourceDataTable);
}
}
public async Task <OrderResponseDto> Add(int userId, int bookId, int quantity, int addressId, string guid)
{
OrderResponseDto orderResponse = null;
using (SqlConnection connection = _dBContext.GetConnection())
{
await connection.OpenAsync();
var transaction = await connection.BeginTransactionAsync();
try {
SqlCommand command = new SqlCommand("sp_orders_create", connection, (SqlTransaction)transaction)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@userId", userId);
command.Parameters.AddWithValue("@bookId", bookId);
command.Parameters.AddWithValue("@quantity", quantity);
command.Parameters.AddWithValue("@addressId", addressId);
command.Parameters.AddWithValue("@guid", guid);
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
orderResponse = new OrderResponseDto();
orderResponse.Book = _booksRepository.MapReaderTobook(reader);
orderResponse.User = _userRepository.MapUserFromReader(reader, "userId");
orderResponse.Address = MapReaderToAddressDto(reader);
orderResponse.OrderedDate = Convert.ToDateTime(reader["ordertime"]);
orderResponse.Quantity = (int)reader["orderedQuantity"];
orderResponse.OrderId = (string)reader["orderId"];
}
}
await transaction.CommitAsync();
await connection.CloseAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
finally
{
await connection.CloseAsync();
}
}
return(orderResponse);
}
/// <summary>
/// Gets the wishlist for user id.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <returns>Wishilist for loggedin user</returns>
public async Task <List <WishlistResponseDto> > Get(int userId)
{
List <WishlistResponseDto> wishlists = new List <WishlistResponseDto>();
SqlConnection _conn = _dbContext.GetConnection();
SqlCommand command = new SqlCommand("sp_wishlist_get", _conn)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@userId", userId);
await _conn.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
wishlists.Add(MapReaderToWishlist(reader));
}
}
await _conn.CloseAsync();
return(wishlists);
}
public async Task <AddressResponseDto> GetAddress(int userId)
{
AddressResponseDto address = null;
SqlConnection connection = _dBContext.GetConnection();
SqlCommand command = new SqlCommand("sp_address_get", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@userId", userId);
await connection.OpenAsync();
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
address = MapReaderToAddressResponseDto(reader);
}
}
await connection.CloseAsync();
return(address);
}
