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