private List <PerformerLocalDto> RetrievePerformersFromDatabase()
{
List <PerformerLocalDto> allPerformers = new List <PerformerLocalDto>();
try {
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet resultsetFromDb = retriever.GetPerformers();
DataTable performersTable = resultsetFromDb.Tables[0];
foreach (DataRow row in performersTable.Rows)
{
int?id = GetStringValueAsInt(row, "ID");
PerformerLocalDto performer = new PerformerLocalDto()
{
Id = (int)id,
Name = row["Performer"].ToString()
};
allPerformers.Add(performer);
}
Log.Information("Read {0} performers from database.", allPerformers.Count);
}
catch (Exception e) {
Log.Error(e, "Could not retrieve performers from database");
}
return(allPerformers);
}
public void Add(List <FileLocalDto> fileDtos)
{
AccessRetriever retriever = new AccessRetriever(databasePath);
DataSet dataSet = RetrieveFileEntriesFromDatabase(retriever);
DataTable filenamesTable = dataSet.Tables[0];
foreach (FileLocalDto file in fileDtos)
{
DataRow newRow = filenamesTable.NewRow();
newRow["Filename"] = file.Filename;
newRow["Genre"] = file.Genre;
newRow["Disc"] = file.Disc;
if (file.Series is null)
{
newRow["Series"] = DBNull.Value;
}
else
{
newRow["Series"] = file.Series;
}
if (file.Notes is null)
{
newRow["Notes"] = DBNull.Value;
}
else
{
newRow["Notes"] = file.Notes;
}
filenamesTable.Rows.Add(newRow);
}
retriever.UpdateFileEntries(dataSet);
}
private List <PerformerLocalDto> RetrievePerformers()
{
List <PerformerLocalDto> allPerformers = new List <PerformerLocalDto>();
try {
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet resultsetFromDb = retriever.GetPerformers();
DataTable performersTable = resultsetFromDb.Tables[0];
foreach (DataRow row in performersTable.Rows)
{
string idStr = row["ID"].ToString();
bool result = int.TryParse(idStr, out int id);
PerformerLocalDto performer = new PerformerLocalDto()
{
Id = id,
Name = row["Performer"].ToString()
};
allPerformers.Add(performer);
}
}
catch (Exception e) {
Log.Error(e, "Could not retrieve performers from database");
throw new ArgumentException("Could not retrieve performers from database", e);
}
return(allPerformers);
}
private List <SeriesLocalDto> RetrieveSeries()
{
List <SeriesLocalDto> allSeries = new List <SeriesLocalDto>();
AccessRetriever retriever = new AccessRetriever(DatabasePath);
try {
var dataSet = retriever.GetAllSeries();
DataTable seriesTable = dataSet.Tables[0];
foreach (DataRow row in seriesTable.Rows)
{
string idStr = row["ID"].ToString();
bool result = int.TryParse(idStr, out int id);
SeriesLocalDto series = new SeriesLocalDto()
{
Id = id,
Name = row["Series"].ToString()
};
allSeries.Add(series);
}
}
catch (Exception e) {
Log.Error(e, "Could not retrieve all Series in database");
}
return(allSeries);
}
private List <AliasLocalDto> RetrieveAliasesFromDatabase()
{
List <AliasLocalDto> aliases = new List <AliasLocalDto>();
try {
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet ds = retriever.GetAliases();
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
int? id = GetStringValueAsInt(row, "ID");
int? performerId = GetStringValueAsInt(row, "Performer");
AliasLocalDto aliasDto = new AliasLocalDto()
{
Id = (int)id,
Performer = (int)performerId,
Alias = row["Alias"].ToString()
};
aliases.Add(aliasDto);
}
Log.Information("Read {0} aliases from database", aliases.Count);
}
catch (Exception e) {
Log.Error(e, "Could not retrieve aliases from database");
}
return(aliases);
}
public void GetSeries_Temp_ActuallyGetsSeries()
{
string dbLocation = @"c:\temp\Files on Dvd.accdb";
AccessRetriever myRetriever = new AccessRetriever(dbLocation);
DataTable dt = myRetriever.GetSeries();
string seriesToLookUp = "in the vIP";
DataRow[] selectedRows = dt.Select($"Series = '{seriesToLookUp}'");
}
public void JoinPerformerToFile(int performerId, int filenameId)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet dataSet = retriever.GetPerformersFilenamesJoinTable();
DataRow newPerfFileJoin = dataSet.Tables[0].NewRow();
newPerfFileJoin["PerformerID"] = performerId;
newPerfFileJoin["FilenameID"] = filenameId;
dataSet.Tables[0].Rows.Add(newPerfFileJoin);
retriever.UpdateJoinTable(dataSet);
}
public void UpdateDiscs_HardCodingDiscColumns_ActuallyUpdateDiscs()
{
string dbLocation = @"c:\temp\Files on Dvd.accdb";
AccessRetriever myRetriever = new AccessRetriever(dbLocation);
DataSet dataSet = myRetriever.GetDiscs();
DataRow newDisc = dataSet.Tables[0].NewRow();
newDisc["DiscName"] = "TestDisc22";
newDisc["Wallet"] = 1;
newDisc["Notes"] = "I hope this works22";
dataSet.Tables[0].Rows.Add(newDisc);
myRetriever.UpdateDiscs(dataSet);
Console.WriteLine("Debug this line");
}
public void GetFileEntriesByDisc_DiscExists_GetsCorrectFileEntries()
{
string dbLocation = @"c:\temp\Files on Dvd.accdb";
AccessRetriever myRetriever = new AccessRetriever(dbLocation);
int discId = 220;
DataSet dataSet = myRetriever.GetFileEntriesByDiscId(discId);
DataTable dt = dataSet.Tables[0];
int expectedCount = 4;
Assert.AreEqual(expectedCount, dt.Rows.Count);
Console.WriteLine("Debug this line and examine the dt object");
}
public void Add(PerformerLocalDto performer)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet dataSet = retriever.GetPerformers();
DataTable performersTable = dataSet.Tables[0];
DataRow newRow = dataSet.Tables[0].NewRow();
newRow["Performer"] = performer.Name;
// newRow[2] = 1; // this is supposed to be Performer Type. Can use the column number instead
performersTable.Rows.Add(newRow);
var changes = dataSet.GetChanges();
retriever.UpdateAccessTableFromDataSet(dataSet, "tblPerformers", "ID, Performer");
}
public void JoinPerformerToFile(List <PerformerFilenameJoinDto> joins)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
DataSet dataSet = retriever.GetPerformersFilenamesJoinTable();
DataTable joinTable = dataSet.Tables[0];
foreach (PerformerFilenameJoinDto join in joins)
{
DataRow newPerfFileJoin = joinTable.NewRow();
newPerfFileJoin["PerformerID"] = join.PerformerId;
newPerfFileJoin["FilenameID"] = join.FilenameId;
joinTable.Rows.Add(newPerfFileJoin);
}
retriever.UpdateJoinTable(dataSet);
}
public int GetDiscIdByName(string discName)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
int discId = -1;
try {
DataSet dataSet = retriever.GetDiscs();
DataTable discsTable = dataSet.Tables[0];
DataRow[] selectedDiscsRows = discsTable.Select($"DiscName = '{discName}'");
string discIdStr = selectedDiscsRows[0]["ID"].ToString();
bool isInt = int.TryParse(discIdStr, out discId);
}
catch (Exception e) {
Log.Error(e, "Could not retrieve Disc {0}", discName);
}
return(discId);
}
public void AddDisc(DvdFolderToImport dvd)
{
AccessRetriever retriever = new AccessRetriever(DatabasePath);
try {
DataSet dataSet = retriever.GetDiscs();
DataRow newDisc = dataSet.Tables[0].NewRow();
newDisc["DiscName"] = dvd.DiscName;
newDisc["Wallet"] = dvd.WalletType;
newDisc["Notes"] = dvd.Notes;
dataSet.Tables[0].Rows.Add(newDisc);
retriever.UpdateDiscs(dataSet);
}
catch (Exception e) {
Log.Error(e, "Error adding disc {0} to database", dvd.DiscName);
}
}
public int Add(DiscLocalDto discDto)
{
AccessRetriever retriever = new AccessRetriever(databasePath);
DataSet dataSet;
int? id = 0;
try {
dataSet = retriever.GetDiscs();
}
catch (Exception e) {
Log.Error(e, "Could not retrieve discs from database");
throw new ArgumentException("Could not retrieve discs from database", e);
}
DataTable discsTable = dataSet.Tables[0];
PopulateDiscsFromDatabase(discsTable);
if (discs.Any(d => d.DiscName == discDto.DiscName))
{
Log.Error("Disc {0} already exists in database", discDto.DiscName);
throw new ArgumentOutOfRangeException(discDto.DiscName, "Disc name already exists in database!");
}
else
{
DataRow newRow = discsTable.NewRow();
newRow["DiscName"] = discDto.DiscName;
newRow["Wallet"] = discDto.Wallet;
newRow["Notes"] = discDto.Notes;
// newRow[2] = 1; // this is supposed to be Performer Type. Can use the column number instead
discsTable.Rows.Add(newRow);
var changes = dataSet.GetChanges();
id = retriever.UpdateDiscs(dataSet);
if (id is null)
{
throw new InvalidOperationException("New DiscId is null. Check Access DB to see if it was entered.");
}
else
{
Log.Information("Added Disc {0} to database with ID {1}", discDto.DiscName, id);
}
}
return((int)id);
}
public void GetSeriesAndPerformersAndAliases_Temp_ActuallyGetsSeries()
{
string dbLocation = @"c:\temp\Files on Dvd.accdb";
AccessRetriever myRetriever = new AccessRetriever(dbLocation);
DataSet resultSet = myRetriever.GetSeriesAndPerformersAndAliases();
DataTable seriesTable = resultSet.Tables[0];
string seriesToLookUp = "doctor who podshock";
DataRow[] selectedSeriesRows = seriesTable.Select($"Series = '{seriesToLookUp}'");
DataTable performersTable = resultSet.Tables[1];
string performerToLookUp = "christine mendoza";
DataRow[] selectedPerformersRows = performersTable.Select($"Performer = '{performerToLookUp}'");
DataTable aliasTable = resultSet.Tables[2];
string aliasToLookUp = "lucky";
DataRow[] selectedAliasRows = aliasTable.Select($"Alias = '{aliasToLookUp}'");
Console.WriteLine("debug this line");
}
private DataSet RetrieveFileEntriesFromDatabase(AccessRetriever retriever, int?discId = null)
{
DataSet dataSet;
try {
if (discId == null)
{
dataSet = retriever.GetFileEntries();
}
else
{
dataSet = retriever.GetFileEntriesByDiscId((int)discId);
}
}
catch (Exception e) {
Log.Error(e, "Could not retrieve filenames from Access DB");
throw new InvalidOperationException("Could not retrieve filenames from Access DB", e);
}
if (dataSet is null)
{
throw new InvalidOperationException("Filenames dataSet is null");
}
return(dataSet);
}
public List <FileLocalDto> GetByDisc(int discId)
{
AccessRetriever retriever = new AccessRetriever(databasePath);
DataSet dataSet = RetrieveFileEntriesFromDatabase(retriever, discId);
DataTable fileTable = dataSet.Tables[0];
List <FileLocalDto> filesInDisc = new List <FileLocalDto>();
foreach (DataRow row in fileTable.Rows)
{
string idStr = row["ID"].ToString();
_ = int.TryParse(idStr, out int id);
FileLocalDto fileDto = new FileLocalDto()
{
// all we care about retrieving here are the Id and Filename,
// so the Ids can be copied into the FileToImport objects
Id = id,
Filename = row["Filename"].ToString()
};
filesInDisc.Add(fileDto);
}
return(filesInDisc);
}
public void UpdateFileEntries_Temp_ActuallyUpdateFileEntries()
{
string dbLocation = @"c:\temp\Files on Dvd.accdb";
AccessRetriever myRetriever = new AccessRetriever(dbLocation);
DataSet dataSet = myRetriever.GetFileEntries();
DataRow newEntry = dataSet.Tables[0].NewRow();
newEntry["Filename"] = "Succession - 1.01 - Pilot";
newEntry["Genre"] = 3;
newEntry["Disc"] = 224;
newEntry["Series"] = 14;
dataSet.Tables[0].Rows.Add(newEntry);
DataRow newEntry2 = dataSet.Tables[0].NewRow();
newEntry2["Filename"] = "Buffy the Vampire Slayer - 1.01 - Welcome to the Hellmouth";
newEntry2["Genre"] = 3;
newEntry2["Disc"] = 224;
newEntry2["Series"] = 15;
dataSet.Tables[0].Rows.Add(newEntry2);
myRetriever.UpdateFileEntries(dataSet);
Console.WriteLine("Debug this line");
}
