public List <Tuple <string, int> > GetGameCount()
{
List <Tuple <string, int> > list = new List <Tuple <string, int> >();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GameTagCount", conn)
{
CommandType = CommandType.StoredProcedure
};
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int count = Convert.ToInt32(reader["GAMECOUNT"]);
string name = Convert.ToString(reader["TagName"]);
list.Add(new Tuple <string, int>(name, count));
}
}
conn.Close();
}
}
return(list);
}
public List <Tuple <int, int> > GetAllGames()
{
List <Tuple <int, int> > list = new List <Tuple <int, int> >();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetOrderGames", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.ExecuteNonQuery();
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var gameId = Convert.ToInt32(reader["GameID"]);
var orderId = Convert.ToInt32(reader["OrderID"]);
list.Add(new Tuple <int, int>(gameId, orderId));
}
}
conn.Close();
}
}
return(list);
}
public Order GetOrder(int id)
{
Order order = new Order();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetOrder", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
order.Id = id;
order.Address = Convert.ToString(reader["OrderAdress"]);
order.OrderDate = Convert.ToDateTime(reader["OrderDate"]);
Enum.TryParse(Convert.ToString(reader["OrderStatus"]), out OrderStatus orderStatus);
order.OrderStatus = orderStatus;
}
}
conn.Close();
}
}
return(order);
}
public int GetOrderId()
{
int orderId;
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetOrderId", conn)
{
CommandType = CommandType.StoredProcedure
};
orderId = Convert.ToInt32(Command.ExecuteScalar());
}
return(orderId);
}
public void RemoveWishListItem(int gameId, int userId)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("RemoveWishListItem", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@gameId", SqlDbType.Int).Value = gameId;
Command.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
Command.ExecuteNonQuery();
conn.Close();
}
}
public void AddGame(Game game)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("AddGame", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = game.Name;
Command.Parameters.Add("@Price", SqlDbType.Decimal).Value = game.Price;
Command.Parameters.Add("@Description", SqlDbType.Text).Value = game.Description;
Command.Parameters.Add("@ReleaseDate", SqlDbType.Date).Value = game.ReleaseDate;
Command.Parameters.Add("@TagName", SqlDbType.NVarChar).Value = game.GameTag;
Command.ExecuteNonQuery();
}
}
public void AddWishListItem(int gameId, int userId)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("AddWishList", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@gameId", SqlDbType.Int).Value = gameId;
Command.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
Command.Parameters.Add("@dateAdded", SqlDbType.Date).Value = DateTime.Now.ToShortDateString();
Command.ExecuteNonQuery();
conn.Close();
}
}
public void AddOrder(Order order)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("AddOrder", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@orderDate", SqlDbType.Date).Value = order.OrderDate;
Command.Parameters.Add("@address", SqlDbType.NVarChar).Value = order.Address;
Command.Parameters.Add("@orderStatus", SqlDbType.NVarChar).Value = order.OrderStatus;
Command.Parameters.Add("@userId", SqlDbType.Int).Value = order.UserId;
Command.ExecuteNonQuery();
conn.Close();
}
}
public void UpdateUser(User user)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("UpdateAccount", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@id", SqlDbType.Int).Value = user.Id;
Command.Parameters.Add("@username", SqlDbType.NVarChar).Value = user.Username;
Command.Parameters.Add("@birthdate", SqlDbType.Date).Value = user.Birthday;
Command.ExecuteNonQuery();
conn.Close();
}
}
public dynamic GetUser(string email)
{
var user = new Account();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetAccountEmail", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
Command.ExecuteNonQuery();
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var id = Convert.ToInt32(reader["ID"]);
var name = Convert.ToString(reader["UserName"]);
email = Convert.ToString(reader["Email"]);
var date = Convert.ToDateTime(reader["Birthdate"]);
bool isAdmin = Convert.ToBoolean(reader["isAdmin"]);
if (isAdmin)
{
user = new Admin(id, email, isAdmin);
}
else
{
user = new User(id, email, name, date, isAdmin);
}
}
}
conn.Close();
}
}
return(user);
}
public void RegisterAccount(User account)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("RegisterAccount", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@username", SqlDbType.NVarChar).Value = account.Username;
Command.Parameters.Add("@password", SqlDbType.NVarChar).Value = account.Password;
Command.Parameters.Add("@email", SqlDbType.NVarChar).Value = account.Email;
Command.Parameters.Add("@birthdate", SqlDbType.Date).Value = account.Birthday;
Command.Parameters.Add("@isAdmin", SqlDbType.Bit).Value = account.IsAdmin;
Command.ExecuteNonQuery();
conn.Close();
}
}
public bool CheckAccountTaken(string email)
{
int account;
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("CheckAccountTaken", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
account = (int)Command.ExecuteScalar();
conn.Close();
}
return(account == 1);
}
public Game GetGame(int id)
{
var output = new Game();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetGame", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@id", SqlDbType.Int).Value = id;
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
id = Convert.ToInt32(reader["ID"]);
var name = Convert.ToString(reader["Name"]);
var price = Convert.ToDecimal(reader["Price"]);
var description = Convert.ToString(reader["Description"]);
var release = Convert.ToDateTime(reader["ReleaseDate"]);
var gameTag = Convert.ToString(reader["TagName"]);
output.Id = id;
output.Name = name;
output.Price = price;
output.Description = description;
output.ReleaseDate = release;
output.GameTag = gameTag;
}
}
conn.Close();
}
}
return(output);
}
public void AddGamesOrder(List <Game> games, int id)
{
int userId = id;
foreach (var item in games)
{
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("AddGamesOrder", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@gameId", SqlDbType.Int).Value = item.Id;
Command.Parameters.Add("@orderId", SqlDbType.Int).Value = userId;
Command.ExecuteNonQuery();
conn.Close();
}
}
}
public List <WishList> GetWishList(int userId)
{
List <WishList> list = new List <WishList>();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetWishList", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int gameId = Convert.ToInt32(reader["GameId"]);
string name = Convert.ToString(reader["Name"]);
DateTime dateAdded = Convert.ToDateTime(reader["dateAdded"]);
list.Add(new WishList()
{
Id = gameId, Game = new Game()
{
Id = gameId, Name = name
}, DateAdded = dateAdded
});
}
}
conn.Close();
}
}
return(list);
}
public List <Game> GetAllGames()
{
var output = new List <Game>();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetAllGames", conn)
{
CommandType = CommandType.StoredProcedure
};
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var id = Convert.ToInt32(reader["ID"]);
var name = Convert.ToString(reader["Name"]);
var price = Convert.ToDecimal(reader["Price"]);
var description = Convert.ToString(reader["Description"]);
var release = Convert.ToDateTime(reader["ReleaseDate"]);
var gameTag = Convert.ToString(reader["TagName"]);
output.Add(new Game()
{
Id = id, Name = name, Price = price, Description = description, ReleaseDate = release, GameTag = gameTag
});
}
}
conn.Close();
}
}
return(output);
}
public List <Order> GetAllOrdersById(int userId)
{
List <Order> orders = new List <Order>();
using (var conn = ConnectDb.GetConnection())
{
conn.Open();
Command = new SqlCommand("GetOrdersById", conn)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
Command.ExecuteNonQuery();
using (var reader = Command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var id = Convert.ToInt32(reader["ID"]);
Enum.TryParse(Convert.ToString(reader["OrderStatus"]), out OrderStatus orderStatus);
var date = Convert.ToDateTime(reader["OrderDate"]);
orders.Add(new Order()
{
Id = id, OrderStatus = orderStatus, OrderDate = date
});
}
}
conn.Close();
}
}
return(orders);
}