public void Add(Scorer scorer)
{
string dateString = DateTime.Now.Ticks.ToString();
string teamIds = " ";
if (scorer.Teams.Count != 0)
{
string[] teamIdsList = new string[scorer.Teams.Count];
for (int i = 0; i < teamIdsList.Length; i++)
{
teamIdsList[i] = scorer.Teams[i].Id.ToString();
}
teamIds = string.Join(",", teamIdsList);
}
string sql = $"INSERT INTO Scorers (name, team_id_list, created_at) VALUES (\'{scorer.Name}\', \'{teamIds}\', {dateString})";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public Team GetTeamByName(string name)
{
Team t = new Team();
string sql = $"SELECT * FROM Teams WHERE name={name}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (var command = new SQLiteCommand(sql, _dbConn.Connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
reader.Read();
t.Id = (long)reader["id"];
t.Name = (string)reader["name"];
t.Year = (long)reader["year"];
t.Company = (string)reader["company"];
t.NumScorers = (long)reader["num_scorers"];
if (t.NumScorers > 0)
{
t.HasScorer = true;
}
else
{
t.HasScorer = false;
}
t.CreatedAt = new DateTime(long.Parse((string)reader["created_at"]));
}
}
}
return(t);
}
public Scorer GetScorerById(long id)
{
Scorer s = new Scorer();
string sql = $"SELECT * FROM Scorers WHERE id={id}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
reader.Read();
s.Id = id;
s.Name = (string)reader["name"];
s.Teams = new List <Team>();
string teamIdsString = (string)reader["team_id_list"];
if (teamIdsString != " ")
{
TeamRepository teamRepo = new TeamRepository();
long[] teamIds = Array.ConvertAll(teamIdsString.Split(','), x => long.Parse(x));
foreach (var t in teamIds)
{
Team team = teamRepo.GetTeamById(t);
s.Teams.Add(team);
}
}
s.CreatedAt = new DateTime(long.Parse((string)reader["created_at"]));
}
}
}
return(s);
}
public void Remove(Scorer scorer)
{
if (scorer.Teams.Count > 0)
{
TeamRepository teamRepo = new TeamRepository();
foreach (var t in scorer.Teams)
{
Team team = teamRepo.GetTeamById(t.Id);
team.NumScorers -= 1;
if (team.NumScorers == 0)
{
team.HasScorer = false;
}
teamRepo.Update(team);
}
}
long id = scorer.Id;
string sql = $"DELETE FROM Scorers WHERE id={id}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public void Remove(Team team)
{
if (team.HasScorer)
{
ScorerRepository scorerRepo = new ScorerRepository();
var scorers = scorerRepo.GetAllScorers();
List <Scorer> scorersToUpdate = new List <Scorer>();
foreach (var s in scorers)
{
if (s.Teams.Contains(team))
{
s.Teams.Remove(team);
scorersToUpdate.Add(s);
}
}
foreach (var toUpdate in scorersToUpdate)
{
scorerRepo.Update(toUpdate);
}
}
long id = team.Id;
string sql = $"DELETE FROM Teams WHERE id={id}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public void Update(Scorer scorer)
{
long id = scorer.Id;
string teamIds = " ";
if (scorer.Teams.Count != 0)
{
string[] teamIdList = new string[scorer.Teams.Count];
for (int i = 0; i < teamIdList.Length; i++)
{
teamIdList[i] = scorer.Teams[i].Id.ToString();
}
teamIds = string.Join(",", teamIdList);
}
string sql = $"UPDATE Scorers SET name=\'{scorer.Name}\', team_id_list=\'{teamIds}\' WHERE id={id}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, TriviaDbContext triviaDbContext)
{
app.UseCors(builder =>
builder.WithOrigins("http://localhost:3000", "https://localhost:3000", "https://test.vihaconsulting.com/", "http://test.vihaconsulting.com/")
.AllowAnyHeader()
.AllowAnyMethod()
.AllowCredentials());
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "TriviaDbApi v1"));
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
//Define endpoint routes here
endpoints.MapControllerRoute(
name: "default",
pattern: "{controller=Home}/{action=Index}/{id?}");
});
}
public ActionResult Index()
{
using (var ctx = new TriviaDbContext())
{
ViewData["questionscount"] = ctx.TriviaQuestions.Count();
}
return(View());
}
public TeamAddViewModel(TriviaDbContext dbConn)
{
if (DesignerProperties.GetIsInDesignMode(
new System.Windows.DependencyObject()))
{
return;
}
_dbConn = dbConn;
_teamRepo = new TeamRepository();
Team = new Team();
SaveCommand = new RelayCommand(OnSave, CanSave);
}
public void Add(string name)
{
string dateString = DateTime.Now.Ticks.ToString();
string sql = $"INSERT INTO Teams (name, year, company, num_scorers, created_at) VALUES (\'{name}\', 0, \'\', 0, {dateString})";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (var command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public void Add()
{
string dateString = DateTime.Now.Ticks.ToString();
string sql = $"INSERT INTO Scorers (name, team_id_list, created_at) VALUES (\'\', \'\', {dateString})";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public void Update(Team team)
{
string dateString = team.CreatedAt.Ticks.ToString();
string sql = $"UPDATE Teams SET name=\'{team.Name}\', year={team.Year}, company=\'{team.Company}\', num_scorers=\'{team.NumScorers}\', created_at={dateString} WHERE id={team.Id}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
command.ExecuteNonQuery();
}
}
}
public List <Team> FindTeamsByYear(long year)
{
List <Team> teamsByYear = new List <Team>();
string sql = $"SELECT * FROM Teams WHERE year={year}";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
teamsByYear.Add(GetTeamById((long)reader["id"]));
}
}
}
}
return(teamsByYear);
}
public List <Team> GetAllTeams()
{
List <Team> teamsList = new List <Team>();
string sql = $"SELECT * FROM Teams";
using (var _dbConn = new TriviaDbContext())
{
_dbConn.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConn.Connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Team t = new Team();
t.Id = (long)reader["id"];
t.Name = (string)reader["name"];
t.Year = (long)reader["year"];
t.Company = (string)reader["company"];
t.NumScorers = (long)reader["num_scorers"];
if (t.NumScorers > 0)
{
t.HasScorer = true;
}
else
{
t.HasScorer = false;
}
t.CreatedAt = new DateTime(long.Parse((string)reader["created_at"]));
teamsList.Add(t);
}
}
}
}
return(teamsList);
}
public QuestionsService(TriviaDbContext db)
{
this.db = db;
}
private static void SeedData(TriviaDbContext context)
{
// Deletes the database
context.Database.EnsureDeleted();
// Creates the database fresh
context.Database.EnsureCreated();
// Seed Models
var qq1 = new QuickstarterQuestion()
{
Id = 1,
Category = "Science & Technology",
Difficulty = "easy",
Question = "What does CPU stand for?",
CorrectAnswer = "Central Processing Unit",
Answers = @"[""Central Process Unit"", ""Computer Personal Unit"", ""Central Processor Unit"", ""Central Processing Unit""]"
};
var qq2 = new QuickstarterQuestion()
{
Id = 2,
Category = "Science & Technology",
Difficulty = "easy",
Question = "What is the longest bone in the human body?",
CorrectAnswer = "Femur",
Answers = @"[""Scapula"", ""Femur"", ""Fibula"", ""Ulna""]"
};
var qq3 = new QuickstarterQuestion()
{
Id = 3,
Category = "Science & Technology",
Difficulty = "easy",
Question = "The element involved in making human blood red is which of the following?",
CorrectAnswer = "Iron",
Answers = @"[""Copper"", ""Iridium"", ""Cobalt"", ""Iron""]"
};
var qq4 = new QuickstarterQuestion()
{
Id = 4,
Category = "Science & Technology",
Difficulty = "easy",
Question = "The biggest distinction between a eukaryotic cell and a prokaryotic cell is:",
CorrectAnswer = "The presence or absence of a nucleus",
Answers = @"[""The presence or absence of certain organelles"", ""The overall size"", ""The presence or absence of a nucleus"", ""The mode of reproduction""]"
};
var qq5 = new QuickstarterQuestion()
{
Id = 5,
Category = "General Knowledge",
Difficulty = "easy",
Question = "What type of animal was Harambe, who was shot after a child fell into it's enclosure at the Cincinnati Zoo?",
CorrectAnswer = "Gorilla",
Answers = @"[""Gorilla"", ""Tiger"", ""Panda"", ""Crocodile""]"
};
var qq6 = new QuickstarterQuestion()
{
Id = 6,
Category = "General Knowledge",
Difficulty = "easy",
Question = "Which candy is NOT made by Mars?",
CorrectAnswer = "Almond Joy",
Answers = @"[""Snickers"", ""M&M's"", ""Almond Joy"", ""Twix""]"
};
var qq7 = new QuickstarterQuestion()
{
Id = 7,
Category = "General Knowledge",
Difficulty = "easy",
Question = "The Flag of the European Union has how many stars on it?",
CorrectAnswer = "12",
Answers = @"[""10"", ""12"", ""14"", ""16""]"
};
var qq8 = new QuickstarterQuestion()
{
Id = 8,
Category = "General Knowledge",
Difficulty = "easy",
Question = "What is the Zodiac symbol for Gemini?",
CorrectAnswer = "Twins",
Answers = @"[""Fish"", ""Maiden"", ""Twins"", ""Scales""]"
};
var qq9 = new QuickstarterQuestion()
{
Id = 9,
Category = "Entertainment",
Difficulty = "easy",
Question = "What is the homeworld of the Elites from Halo?",
CorrectAnswer = "Sanghelios",
Answers = @"[""Te"", ""Sanghelios"", ""Dosaic"", ""Eayn""]"
};
var qq10 = new QuickstarterQuestion()
{
Id = 10,
Category = "Entertainment",
Difficulty = "easy",
Question = "What is the name of the world that the MMO RuneScape takes place in?",
CorrectAnswer = "Gielinor",
Answers = @"[""Glindor"", ""Zaros"", ""Azeroth"", ""Gielinor""]"
};
var qq11 = new QuickstarterQuestion()
{
Id = 11,
Category = "Entertainment",
Difficulty = "easy",
Question = "What is the name of the game developer who created Call Of Duty: Zombies?",
CorrectAnswer = "Treyarch",
Answers = @"[""Sledgehammer Games"", ""Treyarch"", ""Infinity Ward"", ""Activision""]"
};
var qq12 = new QuickstarterQuestion()
{
Id = 12,
Category = "Entertainment",
Difficulty = "easy",
Question = "Who is the creator of the Super Smash Bros. Series?",
CorrectAnswer = "Masahiro Sakurai",
Answers = @"[""Masahiro Sakurai"", ""Reggie Fils-Aime"", ""Bill Trinen"", ""Hideo Kojima""]"
};
var qq13 = new QuickstarterQuestion()
{
Id = 13,
Category = "World Knowledge",
Difficulty = "easy",
Question = "Who is the creator of Filip Bicki?",
CorrectAnswer = "Poland",
Answers = @"[""Poland"", ""Reggie Fils-Aime"", ""Bill Trinen"", ""Hideo Kojima""]"
};
IList <QuickstarterQuestion> quickstarterQuestions = new List <QuickstarterQuestion>()
{
qq1, qq2, qq3, qq4, qq5, qq6, qq7, qq8, qq9, qq10, qq11, qq12, qq13
};
context.QuickstarterQuestions.AddRange(quickstarterQuestions);
// Saves changes
context.SaveChanges();
}
public SessionController(TriviaDbContext context)
{
this.context = context;
}
public TriviaController(TriviaDbContext context)
{
this.context = context;
}
public TriviaController(TriviaDbContext context, IQuestionsService questionsService, IAnswersService answersService)
{
this.context = context;
this.questionsService = questionsService;
this.answersService = answersService;
}
public QuickstarterController(TriviaDbContext context)
{
this.context = context;
}
public CategoryController(TriviaDbContext db, HttpClient client)
{
this.db = db;
Client = client;
}
public AnswersService(TriviaDbContext db)
{
this.db = db;
}
public QnaController(TriviaDbContext db)
{
this.db = db;
}
