public List <IPlatformModel> SelectAll()
{
List <IPlatformModel> models = new List <IPlatformModel>();
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "SELECT * FROM Platforms";
comm = new SQLiteCommand(query, cnn);
SQLiteDataReader reader;
reader = comm.ExecuteReader();
while (reader.Read())
{
IPlatformModel model = new PlatformModel
{
platformId = reader.GetInt32(1),
name = reader.GetString(2)
};
models.Add(model);
}
return(models);
}
}
public void Insert(IGameDetailsModel model)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "INSERT INTO Games(Title,ReleaseDate,Summary)VALUES(@title,@date,@summary)";
comm = new SQLiteCommand(query, cnn);
comm.Parameters.Add(new SQLiteParameter("@title", model.Name));
comm.Parameters.Add(new SQLiteParameter("@date", model.FirstReleaseDate));
comm.Parameters.Add(new SQLiteParameter("@summary", model.Summary));
comm.ExecuteReader();
comm.Dispose();
cnn.Close();
if (model.Platforms != null)
{
foreach (int a in model.Platforms)
{
using (SQLiteConnection cnn2 = SqlConnectionInstance.GetSQLiteConnection())
{
cnn2.Open();
string secondQuery = "INSERT INTO GamePlatforms(GameId,PlatformId)VALUES((SELECT MAX(Id) FROM Games),@platformID)";
comm = new SQLiteCommand(secondQuery, cnn2);
comm.Parameters.Add(new SQLiteParameter("@gameID", 1));
comm.Parameters.Add(new SQLiteParameter("@platformID", a));
comm.ExecuteReader();
comm.Dispose();
cnn2.Close();
}
}
}
}
}
public void InsertAll(List <IDiscountedGamesModel> models)
{
if (models == null)
{
return;
}
foreach (IDiscountedGamesModel game in models)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "INSERT INTO SwitchEshopGames ( Title,OriginalPrice,DiscountPrice,Platform ) VALUES ( @title, @ogPrice,@dcPrice,@plat)";
comm = new SQLiteCommand(query, cnn);
comm.Parameters.Add(new SQLiteParameter("@title", game.Title));
comm.Parameters.Add(new SQLiteParameter("@ogPrice", game.OriginalPrice));
comm.Parameters.Add(new SQLiteParameter("@dcPrice", game.DiscountPrice));
comm.Parameters.Add(new SQLiteParameter("@plat", game.PlatformId));
comm.ExecuteReader();
comm.Dispose();
cnn.Close();
}
}
}
public void DeleteAll()
{
using (var cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand command;
cnn.Open();
string query = "DELETE FROM SwitchEshopGames";
command = new SQLiteCommand(query, cnn);
command.ExecuteReader();
command.Dispose();
cnn.Close();
}
}
public void Delete(IDiscountedGamesModel model)
{
using (var cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand command;
cnn.Open();
string query = "DELETE FROM SwitchEshopGames WHERE Title = '" + model.Title + "';";
command = new SQLiteCommand(query, cnn);
command.ExecuteReader();
command.Dispose();
cnn.Close();
}
}
public void Delete(IGameDetailsModel model)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "DELETE FROM Games WHERE Id = @id;";
comm = new SQLiteCommand(query, cnn);
comm.Parameters.Add(new SQLiteParameter("@id", model.Id));
comm.ExecuteReader();
comm.Dispose();
cnn.Close();
}
}
public void Insert(IPlatformModel model)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "INSERT INTO Platforms ( PlatformID,PlatformName ) VALUES ( @Id, @Name )";
comm = new SQLiteCommand(query, cnn);
comm.Parameters.Add(new SQLiteParameter("@Id", model.platformId));
comm.Parameters.Add(new SQLiteParameter("@Name", model.name));
comm.ExecuteReader();
comm.Dispose();
cnn.Close();
}
}
public void Insert(IDiscountedGamesModel model)
{
using (var cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand command;
cnn.Open();
string query = "INSERT INTO SwitchEshopGames ( Title,OriginalPrice,DiscountPrice,Platform ) VALUES ( @title, @ogPrice,@dcPrice,@plat)";
command = new SQLiteCommand(query, cnn);
command.Parameters.Add(new SQLiteParameter("@title", model.Title));
command.Parameters.Add(new SQLiteParameter("@ogPrice", model.OriginalPrice));
command.Parameters.Add(new SQLiteParameter("@dcPrice", model.DiscountPrice));
command.Parameters.Add(new SQLiteParameter("@plat", model.PlatformId));
command.ExecuteReader();
command.Dispose();
cnn.Close();
}
}
public void Update(IGameDetailsModel model)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "UPDATE Games SET Title = @title, ReleaseDate = @date, Summary = @summary, Status = @status, Rating = @rating, PlatformPlaying = @PlatformPlaying WHERE Id = @id;";
comm = new SQLiteCommand(query, cnn);
comm.Parameters.Add(new SQLiteParameter("@id", model.Id));
comm.Parameters.Add(new SQLiteParameter("@title", model.Name));
comm.Parameters.Add(new SQLiteParameter("@date", model.FirstReleaseDate));
comm.Parameters.Add(new SQLiteParameter("@summary", model.Summary));
comm.Parameters.Add(new SQLiteParameter("@status", Convert.ToInt32(model.GetStatus)));
comm.Parameters.Add(new SQLiteParameter("@rating", model.MyScore));
comm.Parameters.Add(new SQLiteParameter("@PlatformPlaying", model.PlatformPlaying));
comm.ExecuteReader();
comm.Dispose();
cnn.Close();
}
}
public List <IGameDetailsModel> SelectAll()
{
List <IGameDetailsModel> models = new List <IGameDetailsModel>();
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "SELECT * FROM Games";
comm = new SQLiteCommand(query, cnn);
SQLiteDataReader reader;
reader = comm.ExecuteReader();
while (reader.Read())
{
long firstReleaseDate = 0;
if (reader.GetInt64(2) != null)
{
firstReleaseDate = reader.GetInt64(2);
}
IGameDetailsModel model = new GameDetailsModel();
model.Id = reader.GetInt32(0);
model.Name = reader.GetString(1);
model.FirstReleaseDate = firstReleaseDate;
model.Summary = reader.GetString(3);
model.MyScore = reader.GetInt32(4);
if (reader.GetString(5) != null)
{
model.PlatformPlaying = reader.GetString(5);
}
GameDetailsModel.Status s = (GameDetailsModel.Status)reader.GetInt32(6);
model.GetStatus = s;
models.Add(model);
}
return(AddPlatformsToGames(models));
}
}
public IDiscountedGamesModel SelectBy(IDiscountedGamesModel model)
{
IDiscountedGamesModel foundModel = new DiscountedSwitchGames();
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "SELECT OriginalPrice,DiscountPrice FROM SwitchEshopGames WHERE SwitchEshopGames.Title = \"" + model.Title + "\";";
comm = new SQLiteCommand(query, cnn);
SQLiteDataReader reader;
reader = comm.ExecuteReader();
while (reader.Read())
{
foundModel = new DiscountedSwitchGames
{
OriginalPrice = reader.GetString(0),
DiscountPrice = reader.GetString(1),
};
}
return(foundModel);
}
}
//Bind Platforms to Games's Platform IDs.
private static List <IGameDetailsModel> AddPlatformsToGames(List <IGameDetailsModel> games)
{
using (SQLiteConnection cnn = SqlConnectionInstance.GetSQLiteConnection())
{
SQLiteCommand comm;
cnn.Open();
string query = "SELECT Games.Title, Platforms.PlatformName FROM((Games INNER JOIN GamePlatforms ON Games.Id = GamePlatforms.GameId) INNER JOIN Platforms ON GamePlatforms.PlatformId = Platforms.PlatformID);";
comm = new SQLiteCommand(query, cnn);
SQLiteDataReader reader;
reader = comm.ExecuteReader();
while (reader.Read())
{
foreach (IGameDetailsModel game in games)
{
if (reader.GetString(0).Contains(game.Name))
{
game.AllPlatforms.Add(reader.GetString(1));
}
}
}
return(games);
}
}