예제 #1
0
        /// <summary>
        /// Retrieves the list of games that the business has posted.
        /// Uses the id stored in UserTempStorage in query to get games for logged in user
        /// </summary>
        public static List <ShowGameItem> GetGamesList(string filter)
        {
            SetupUserServiceConnection();
            int id = UserTempStorage.id;
            List <ShowGameItem> gamesList = new List <ShowGameItem>();

            DataSet ds = new DataSet();

            using (SqlConnection sqlConnection = new SqlConnection(connectionString.GetSection("ConnectionStrings").GetSection("NinersConnection").Value))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();

                sqlConnection.Open();

                // Command will get all the games from GameInfo where GameInfo.GameID == BusinessGame.GameID
                // AND BusinessGame.BusinessID == currently signed in ID
                SqlCommand filteredQueryCommand = new SqlCommand("");

                //The filter is handled by a select input on the show games page
                //Based on the value selected, different order bys will be used
                switch (filter)
                {
                case "price_hl":

                    filteredQueryCommand = new SqlCommand("SELECT * FROM (" +
                                                          "SELECT GI.gameTitle, GI.gameDescription, GI.releaseDate, GI.gamePlatform, GI.gameCategory, GI.gamePrice," +
                                                          "BG.gameID, BG.businessID FROM dbo.GamingInfo GI " +
                                                          "JOIN dbo.BusinessGames BG ON GI.gameID = BG.gameID)" +
                                                          "BusinessInfo where BusinessInfo.businessID = '" + id + "' ORDER BY gamePrice desc;",
                                                          sqlConnection);

                    break;

                case "price_lh":

                    filteredQueryCommand = new SqlCommand("SELECT * FROM (" +
                                                          "SELECT GI.gameTitle, GI.gameDescription, GI.releaseDate, GI.gamePlatform, GI.gameCategory, GI.gamePrice," +
                                                          "BG.gameID, BG.businessID FROM dbo.GamingInfo GI " +
                                                          "JOIN dbo.BusinessGames BG ON GI.gameID = BG.gameID)" +
                                                          "BusinessInfo where BusinessInfo.businessID = '" + id + "' ORDER BY gamePrice asc;",
                                                          sqlConnection);

                    break;

                case "name_desc":

                    filteredQueryCommand = new SqlCommand("SELECT * FROM (" +
                                                          "SELECT GI.gameTitle, GI.gameDescription, GI.releaseDate, GI.gamePlatform, GI.gameCategory, GI.gamePrice," +
                                                          "BG.gameID, BG.businessID FROM dbo.GamingInfo GI " +
                                                          "JOIN dbo.BusinessGames BG ON GI.gameID = BG.gameID)" +
                                                          "BusinessInfo where BusinessInfo.businessID = '" + id + "' ORDER BY gameTitle asc;",
                                                          sqlConnection);

                    break;

                case "name_asc":

                    filteredQueryCommand = new SqlCommand("SELECT * FROM (" +
                                                          "SELECT GI.gameTitle, GI.gameDescription, GI.releaseDate, GI.gamePlatform, GI.gameCategory, GI.gamePrice," +
                                                          "BG.gameID, BG.businessID FROM dbo.GamingInfo GI " +
                                                          "JOIN dbo.BusinessGames BG ON GI.gameID = BG.gameID)" +
                                                          "BusinessInfo where BusinessInfo.businessID = '" + id + "' ORDER BY gameTitle desc;",
                                                          sqlConnection);

                    break;
                }

                filteredQueryCommand.CommandType = CommandType.Text;

                adapter.SelectCommand = filteredQueryCommand;

                filteredQueryCommand.ExecuteNonQuery();

                adapter.Fill(ds);

                sqlConnection.Close();
            }

            //Iterating through and mapping table values to object properties before adding to list
            foreach (DataTable dt in ds.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ShowGameItem tempGame = new ShowGameItem();
                    tempGame.gameId      = (int)dr["gameID"];
                    tempGame.Title       = (string)dr["gameTitle"];
                    tempGame.Description = (string)dr["gameDescription"];
                    tempGame.releaseDate = (DateTime)dr["releaseDate"];
                    tempGame.Platform    = EnumParser.GetEnumValue((int)dr["gamePlatform"], "Platform");
                    tempGame.Category    = EnumParser.GetEnumValue((int)dr["gameCategory"], "Category");
                    tempGame.Price       = (Int64)dr["gamePrice"];
                    gamesList.Add(tempGame);
                }
            }

            return(gamesList);
        }
예제 #2
0
        public static List <ShowGameItem> GetGamesListForWishList()
        {
            SetupUserServiceConnection();
            // int id = UserTempStorage.id;

            int wishListId = UserTempStorage.wishID;
            List <ShowGameItem> gamesList = new List <ShowGameItem>();

            DataSet ds2 = new DataSet();

            using (SqlConnection sqlConnection = new SqlConnection(connectionString.GetSection("ConnectionStrings").GetSection("NinersConnection").Value))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();

                sqlConnection.Open();

                // Command will get all the games from GameInfo where GameInfo.GameID == BusinessGame.GameID
                // AND BusinessGame.BusinessID == currently signed in ID
                SqlCommand filteredQueryCommand = new SqlCommand("");

                filteredQueryCommand = new SqlCommand("SELECT * FROM(" +
                                                      "SELECT wish.id,wish.wishListId, game.gameID, game.gameTitle, game.gameDescription, game.gamePlatform, game.gameCategory, game.gamePrice, game.releaseDate " +
                                                      "FROM WishListItems wish " +
                                                      "INNER JOIN GamingInfo game ON game.gameID = wish.ProductId) myresult " +
                                                      "WHERE myResult.wishListId = " + wishListId + " ;",
                                                      sqlConnection);


                ;


                filteredQueryCommand.CommandType = CommandType.Text;

                adapter.SelectCommand = filteredQueryCommand;

                filteredQueryCommand.ExecuteNonQuery();

                adapter.Fill(ds2);

                sqlConnection.Close();
            }

            //Iterating through and mapping table values to object properties before adding to list
            foreach (DataTable dt in ds2.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ShowGameItem tempGame = new ShowGameItem();

                    tempGame.gameId      = (int)dr["gameID"];
                    tempGame.Title       = (string)dr["gameTitle"];
                    tempGame.Description = (string)dr["gameDescription"];
                    tempGame.releaseDate = (DateTime)dr["releaseDate"];
                    tempGame.Platform    = EnumParser.GetEnumValue((int)dr["gamePlatform"], "Platform");
                    tempGame.Category    = EnumParser.GetEnumValue((int)dr["gameCategory"], "Category");
                    tempGame.Price       = (Int64)dr["gamePrice"];
                    gamesList.Add(tempGame);
                }
            }

            return(gamesList);
        }