/// <summary>
/// Use this to add a scholar to the roster.
/// </summary>
/// <param name="scholar"></param>
/// <param name="division"></param>
public static void AddScholar(ScholarModel scholar)
{
using (SqliteConnection db = new SqliteConnection(GetFileName()))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand
{
Connection = db,
CommandText = "INSERT INTO " + scholar.Division +
"DatabaseManager VALUES (@ScupID, @Index, @Country, @School, @TeamID, " +
"@Pos, @First, @Last, @Sex)"
};
// Use parameterized query to prevent SQL injection attacks
insertCommand.Parameters.AddWithValue("@ScupID", scholar.ScupID);
insertCommand.Parameters.AddWithValue("@Index", scholar.Index);
insertCommand.Parameters.AddWithValue("@Country", scholar.Country);
insertCommand.Parameters.AddWithValue("@School", scholar.School);
insertCommand.Parameters.AddWithValue("@TeamID", scholar.TeamID);
insertCommand.Parameters.AddWithValue("@Pos", scholar.Position);
insertCommand.Parameters.AddWithValue("@First", scholar.FirstName);
insertCommand.Parameters.AddWithValue("@Last", scholar.LastName);
insertCommand.Parameters.AddWithValue("@Sex", scholar.Sex);
insertCommand.ExecuteReader();
db.Close();
}
}
/// <summary>
/// Use this to add a scholar to the roster.
/// </summary>
/// <param name="scholar"></param>
/// <param name="division"></param>
public static void AddToRoster(ScholarModel scholar, Division division)
{
using (SqliteConnection db = new SqliteConnection(GetFileName()))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
//(ScupID INTEGER PRIMARY KEY, " +
//"Index INTEGER, Country STRING, School STRING, TeamID INTEGER, Position INTEGER, " +
// "FirstName STRING, LastName STRING, DD INTEGER, MM INTEGER, YY INTEGER, Sex STRING)";
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO Roster VALUES (@ScupID, @Index, @Country, @School, @TeamID, " +
"@Pos, @First, @Last, @DD, @MM, @YY, @Sex)";
insertCommand.Parameters.AddWithValue("@ScupID", scholar.ScupID);
insertCommand.Parameters.AddWithValue("@Index", scholar.Index);
insertCommand.Parameters.AddWithValue("@Country", scholar.Country);
insertCommand.Parameters.AddWithValue("@School", scholar.School);
insertCommand.Parameters.AddWithValue("@TeamID", scholar.TeamID);
insertCommand.Parameters.AddWithValue("@Pos", scholar.Position);
insertCommand.Parameters.AddWithValue("@First", scholar.FirstName);
insertCommand.Parameters.AddWithValue("@Last", scholar.LastName);
insertCommand.Parameters.AddWithValue("@DD", scholar.DayOfBirth);
insertCommand.Parameters.AddWithValue("@MM", scholar.MonthOfBirth);
insertCommand.Parameters.AddWithValue("@YY", scholar.YearOfBirth);
insertCommand.Parameters.AddWithValue("@Sex", scholar.Sex);
insertCommand.ExecuteReader();
db.Close();
}
}
public static async Task <ObservableCollection <ScholarModel> > ParseForScholars(StorageFile file, Roster.Division div)
{
var roster = new ObservableCollection <ScholarModel>();
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook workbook = await application.Workbooks.OpenAsync(file);
IWorksheet sheet = workbook.Worksheets[div.ToString()];
if (sheet == null)
{
throw new Exception("No worksheet named " + div + " found. Try again, buddy.");
}
int row = 2;
// basically keep going until there's no text in the row
while (sheet[row, 2].DisplayText.Length > 1)
{
int ScupID = (int)sheet[row, 4].Number * 10 + (int)sheet[row, 5].Number;
int Index = (int)sheet[row, 1].Number;
string Country = sheet[row, 2].Text;
string School = sheet[row, 3].Text;
int TeamID = (int)sheet[row, 4].Number;
int Position = (int)sheet[row, 5].Number;
string FirstName = sheet[row, 6].Text;
string LastName = sheet[row, 7].Text;
string Sex = sheet[row, 12].Text;
var newScholar = new ScholarModel(ScupID, Index, Country, School, TeamID, Position, FirstName, LastName,
Sex, div);
roster.Add(newScholar);
row++;
}
return(roster);
}
