/// <summary>
/// Delete the ownership record
/// </summary>
private void DeleteOwnership()
{
ownerID = Convert.ToInt32(cmbOwners.SelectedValue);
dogID = Convert.ToInt32(lstOwnership.SelectedValue.ToString().Split(':')[1]);
startDate = lstOwnership.SelectedValue.ToString().Split(':')[0];
string dogName = DatabaseHelper.ExecuteScaler($"SELECT Name FROM Dogs WHERE DogID = {dogID}").ToString();
string ownerName = DatabaseHelper.ExecuteScaler
(
$@"
SELECT FirstName + ' ' + COALESCE(MiddleName + ' ', '') + LastName
FROM Owners
WHERE OwnerID = {ownerID}"
).ToString();
if (UIMethods.ConfirmationPrompt($"Are you sure you wish to delete the ownership record for {ownerName} starting owning {dogName} on {startDate}"))
{
string sql = $@"
DELETE DogOwnership
WHERE OwnerID = {ownerID}
AND DogID = {dogID}
AND StartOfOwnership = '{startDate}';
";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Load all form info
/// </summary>
public override void Reload()
{
UIMethods.FillListControl(cmbSelectOwner, "OwnerName", "OwnerID", LoadFormData.OwnerNamesCombined());
currentID = Convert.ToInt32(OwnerNavigation()["FirstID"]);
LoadOwnerDetails();
UIMethods.DisplayStatusMessage(((MDIParent)MdiParent).GetStatusLabel(), "Owners loaded");
}
private void DeleteDog()
{
int id = Convert.ToInt32(txtID.Text);
string errorMsg = "";
if (DatabaseHelper.ValueExists("DogID", id.ToString(), "DogShowDetails"))
{
errorMsg += "Cannot remove a dog that is in a dog show results\r\n";
}
if (DatabaseHelper.ValueExists("DogID", id.ToString(), "DogOwnership"))
{
errorMsg += "Cannot remove a dog that is in an ownership record";
}
if (errorMsg.Length != 0)
{
errorProvider.SetError(btnDeleteDog, errorMsg.Trim());
}
else
{
string sql = $"DELETE FROM Dogs WHERE DogID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Load info on the selected class
/// </summary>
private void GetClassInfo()
{
int id = Convert.ToInt32(lstClasses.SelectedValue);
string sql = $"SELECT Class FROM Classes WHERE ClassID = {id}";
txtClassName.Text = DatabaseHelper.ExecuteScaler(sql).ToString();
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Class info loaded");
}
/// <summary>
/// Load all form info
/// </summary>
public override void Reload()
{
UIMethods.FillListControl(cmbClass, "Class", "ClassID", LoadFormData.ClassNames());
UIMethods.FillListControl(cmbPrimary, "Colour", "ColourID", LoadFormData.ColourNames());
UIMethods.FillListControl(cmbSecondary, "Colour", "ColourID", LoadFormData.ColourNames(), true);
UIMethods.FillListControl(lstBreeds, "Breed", "BreedID", LoadFormData.BreedNames());
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Breeds info loaded");
}
/// <summary>
/// Load all form info
/// </summary>
public override void Reload()
{
UIMethods.FillListControl(cmbBreed, "Breed", "BreedID", LoadFormData.BreedNames());
UIMethods.FillListControl(cmbSearchBreed, "Breed", "BreedID", LoadFormData.BreedNames(), true);
UIMethods.FillListControl(cmbOwner, "OwnerName", "OwnerID", LoadFormData.OwnerNamesCombined());
UIMethods.FillListControl(lstDogs, "Name", "DogID", LoadFormData.DogNames());
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Dogs info loaded");
}
/// <summary>
/// Remove the dog show from the database
/// </summary>
private void RemoveDogShowDog()
{
string sql = $@"DELETE DogShowDetails
WHERE DogID = {currentDogID} AND DogShowID = {dogShowID};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Update the dog show
/// </summary>
private void UpdateDogShow()
{
GetUserData();
if (VerifyFields())
{
string sql = $"UPDATE DogShows SET Name = '{name}', StartDate = '{startDate}', EndDate = '{endDate}', NumDogs = {numDogs} WHERE DogShowID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Insert the dog show
/// </summary>
private void InsertDogShowDog()
{
string sql = $@"INSERT INTO DogShowDetails
(DogID, DogShowID, [Rank], Disqualified)
VALUES
({assignDogID}, {dogShowID}, {rank}, {disqualified});" ;
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Insert the owner info into the database
/// </summary>
private void InsertOwner()
{
string sql = $@"INSERT INTO Owners
(FirstName, MiddleName, LastName, DOB, DateOfRetirement, Retired)
VALUES
('{fName}', {mName}, '{lName}', '{dob}', {dateOfRetirement}, {isRetired});" ;
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
UIMethods.ClearControls(Controls);
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
private void DeleteOwner()
{
if (DatabaseHelper.ValueExists("OwnerID", currentID.ToString(), "DogOwnership"))
{
MessageBox.Show("Cannot remove an owner which is listed owning dogs.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string sql = $"DELETE FROM Owners WHERE OwnerID = {currentID};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((MDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
}
/// <summary>
/// Update the class
/// </summary>
private void UpdateClass()
{
int id = Convert.ToInt32(lstClasses.SelectedValue);
if (ValidateFields())
{
string sql = $"UPDATE Classes SET Class = '{className}' WHERE ClassID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Insert the breed info into the database
/// </summary>
private void AddBreed()
{
string sql = $@"
INSERT INTO Breeds
(Breed, PrimaryCoatColour, SecondaryCoatColour, [Classification])
Values
('{breedName}', {primaryId}, {secondaryId}, {classId});" ;
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
UIMethods.ClearControls(Controls);
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
private void CreateOwnership()
{
LoadUserInfo();
string sql = $@"
INSERT INTO DogOwnership
(OwnerID, DogID, StartDate, EndDate)
VALUES
({Owner}, {dogID}, '{startDate}', {endDate})
";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((MDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
}
private void cmbDogShows_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
GetDogs();
GetNumDogs();
GetDates();
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Dog show details loaded");
}
catch (Exception ex)
{
UIMethods.ErrorHandler(ex);
}
}
/// <summary>
/// Insert the colour into the database
/// </summary>
private void InsertNewColour()
{
string colourName = DatabaseHelper.SanitizeUserInput(txtColour.Text);
string sql = $@"
INSERT INTO Colours
(Colour)
VALUES
('{colourName}');";
int rowsAffected = DatabaseHelper.SendData(sql);
txtColour.Clear();
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Update the owner
/// </summary>
private void UpdateOwner()
{
GetUserData();
if (VerifyFields())
{
string sql = $@"UPDATE Owners
SET FirstName = '{fName}', MiddleName = {mName}, LastName = '{lName}', DOB = '{dob}',
DateOfRetirement = {dateOfRetirement}, Retired = {retired}
WHERE OwnerID = {id} ";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Insert dog info into database
/// </summary>
private void InsertDog()
{
string sql = $@"INSERT INTO Dogs
([Name], Sex, [Weight], Height, DOB, DateOfRetirement, Retired, DateOfChampionship,
Champion, DateOfDisqualification, PermanentlyDisqualified, Breed)
VALUES
('{name}', {sex}, {weight}, {height}, '{dob}', {dateRetired}, {retired},
{dateChampionship}, {champion}, {dateBanned}, {banned}, {breedId});";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
UIMethods.ClearControls(Controls);
rdoMale.Checked = true;
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Insert the ownership record
/// </summary>
private void InsertOwnership()
{
LoadUserData();
if (VerifyUserData())
{
string sql = $@"
INSERT INTO DogOwnership
(OwnerID, DogID, StartOfOwnership, EndOfOwnership)
VALUES
({ownerID},{dogID},'{startDate}', {endDate});" ;
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) inserted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Update the dog breed
/// </summary>
private void UpdateDog()
{
GetValues();
if (ValidateFields())
{
string sql =
$@"UPDATE Dogs
SET [Name] = '{name}', Sex = {sex}, [Weight] = {weight}, Height = {height}, DOB = '{dob}',
DateOfRetirement = {dateRetired}, Retired = {retired}, DateOfChampionship = {dateChampionship},
Champion = {champion}, DateOfDisqualification = {dateBanned}, PermanentlyDisqualified = {banned},
Breed = {breedId}
WHERE DogID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Insert dog show info into database
/// </summary>
private void InsertDogShow()
{
string name = DatabaseHelper.SanitizeUserInput(txtDogShowName.Text);
string start = dtpStartDate.Value.ToString("yyyy-MM-dd");
string end = dtpEndDate.Value.ToString("yyyy-MM-dd");
int numDogs = Convert.ToInt32(nudNumDogs.Value);
string sql = $@"INSERT INTO DogShows
([Name], StartDate, EndDate, NumDogs)
VALUES
('{name}', '{start}', '{end}', {numDogs});" ;
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) added");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Delete the dog show from the database
/// </summary>
private void DeleteDogShow()
{
int id = Convert.ToInt32(cmbDogShows.SelectedValue);
errorProvider.Clear();
if (DatabaseHelper.ValueExists("DogShowID", id.ToString(), "DogShowDetails"))
{
errorProvider.SetError(btnDeleteDogShow, "Cannot remove a dog show that is has dogs in the results");
return;
}
string sql = $"DELETE FROM DogShows WHERE DogShowID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Get the information on the selected dog
/// </summary>
private void GetDogDetails()
{
int id = Convert.ToInt32(lstDogs.SelectedValue);
string sql = $"SELECT * FROM Dogs WHERE DogID = {id};";
DataRow row = DatabaseHelper.GetDataRow(sql);
string name = row["Name"].ToString();
bool isMale = Convert.ToBoolean(row["Sex"]);
double weight = Convert.ToDouble(row["Weight"]);
double height = Convert.ToDouble(row["Height"]);
DateTime dob = Convert.ToDateTime(row["DOB"]);
UIMethods.PickDateTimePicker(dtpDateOfRetirement, row["DateOfRetirement"]);
bool retired = Convert.ToBoolean(row["Retired"]);
bool champion = Convert.ToBoolean(row["Champion"]);
UIMethods.PickDateTimePicker(dtpChampionshipDate, row["DateOfChampionship"]);
bool banned = Convert.ToBoolean(row["PermanentlyDisqualified"]);
UIMethods.PickDateTimePicker(dtpDateBanned, row["DateOfDisqualification"]);
int breedID = Convert.ToInt32(row["Breed"]);
int ownerID = LoadFormData.GetCurrentOwnerOfDog(id);
txtID.Text = id.ToString();
txtName.Text = name.ToString();
rdoMale.Checked = isMale;
rdoFemale.Checked = !isMale;
txtWeight.Text = weight.ToString("N1");
txtHeight.Text = height.ToString("N1");
dtpDateOfBirth.Value = dob;
chkRetired.Checked = retired;
chkChampion.Checked = champion;
chkBanned.Checked = banned;
cmbBreed.SelectedValue = breedID;
cmbOwner.SelectedValue = ownerID;
PopulateDogShows();
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Dog info loaded");
}
/// <summary>
/// Load the specific information on the breed
/// </summary>
private void LoadBreedInfo()
{
int breedID = Convert.ToInt32(lstBreeds.SelectedValue);
string sql = $"SELECT Breed, [Classification], PrimaryCoatColour, SecondaryCoatColour FROM Breeds WHERE BreedID = {breedID};";
DataRow row = DatabaseHelper.GetDataRow(sql);
string breed = row["Breed"].ToString();
int classID = Convert.ToInt32(row["Classification"]);
int primaryColourID = Convert.ToInt32(row["PrimaryCoatColour"]);
_ = int.TryParse(row["SecondaryCoatColour"].ToString(), out int secondaryColourID);
txtID.Text = breedID.ToString();
txtName.Text = breed;
cmbClass.SelectedValue = classID;
cmbPrimary.SelectedValue = primaryColourID;
cmbSecondary.SelectedValue = secondaryColourID;
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Breed info loaded");
}
/// <summary>
/// Update the ownership record
/// </summary>
private void ModifyOwnership()
{
LoadUserData(true);
if (VerifyUserData(true))
{
LoadUserData(true);
string sql = $@"
UPDATE DogOwnership
SET DogID = {dogID},
StartOfOwnership = '{startDate}',
EndOfOwnership = {endDate}
WHERE OwnerID = {ownerID}
AND DogID = {originalDogID}
AND StartOfOwnership = '{originalStartDate}';
";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) updated");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Update the breed with the user provided info
/// </summary>
private void UpdateBreed()
{
string name = DatabaseHelper.SanitizeUserInput(txtName.Text);
int id = Convert.ToInt32(lstBreeds.SelectedValue);
if (DatabaseHelper.ValueChanged("Breed", $"'{name}'", "Breeds", "BreedID", id.ToString()) && DatabaseHelper.ValueExists("Breed", $"'{name}'", "Breeds"))
{
errorProvider.SetError(txtName, "A breed already exists with that name");
return;
}
int classID = Convert.ToInt32(cmbClass.SelectedValue);
int primaryCoatID = Convert.ToInt32(cmbPrimary.SelectedValue);
string secondaryCoatID = cmbSecondary.SelectedIndex <= 0 ? "NULL" : Convert.ToInt32(cmbSecondary.SelectedValue).ToString();
string sql = $"UPDATE Breeds SET Breed = '{name}', [Classification] = {classID}, PrimaryCoatColour = {primaryCoatID}, SecondaryCoatColour = {secondaryCoatID} WHERE BreedID = {id};";
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) affected");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
/// <summary>
/// Delete the breed from the database
/// </summary>
private void DeleteBreed()
{
int id = Convert.ToInt32(lstBreeds.SelectedValue);
if (DatabaseHelper.ValueExists("Breed", id.ToString(), "Dogs"))
{
MessageBox.Show("Cannot delete breed that is referenced by a dog", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string sqlBreedName = $"SELECT [Breed] FROM Breeds WHERE BreedID = {id}";
string breedName = DatabaseHelper.ExecuteScaler(sqlBreedName).ToString();
string sql = $"DELETE Breeds WHERE BreedID = {id};";
if (UIMethods.ConfirmationPrompt($"Are you sure you want to delete {breedName} breed?"))
{
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Delete the class
/// </summary>
private void DeleteClass()
{
int id = Convert.ToInt32(lstClasses.SelectedValue);
if (DatabaseHelper.ValueExists("Classification", id.ToString(), "Breeds"))
{
MessageBox.Show("Cannot delete class that is referenced by a breed", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string sql = $"DELETE Classes WHERE ClassID = {id}";
string sqlClassName = $"SELECT [Class] FROM Classes WHERE ClassID = {id}";
string className = DatabaseHelper.ExecuteScaler(sqlClassName).ToString();
if (UIMethods.ConfirmationPrompt($"Are you sure you want to delete {className} class?"))
{
int rowsAffected = DatabaseHelper.SendData(sql);
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), $"{rowsAffected} row(s) deleted");
((frmMDIParent)MdiParent).ReloadAllChildForms();
}
}
/// <summary>
/// Load the info on the selected owner
/// </summary>
private void LoadOwnerDetails()
{
string sql = $@"SELECT FirstName, COALESCE(MiddleName, '') AS MiddleName, LastName, DOB,
DateOfRetirement, Retired FROM Owners WHERE OwnerID = {currentID};";
DataRow row = DatabaseHelper.GetDataRow(sql);
string fName = row["FirstName"].ToString();
string mName = row["MiddleName"].ToString();
string lName = row["LastName"].ToString();
DateTime dob = Convert.ToDateTime(row["DOB"]);
UIMethods.PickDateTimePicker(dtDateOfRetirement, row["DateOfRetirement"]);
bool retired = Convert.ToBoolean(row["Retired"]);
txtFName.Text = fName;
txtMName.Text = mName;
txtLName.Text = lName;
dtpDOB.Value = dob;
chkRetired.Checked = retired;
GetOwnership();
UIMethods.DisplayStatusMessage(((MDIParent)MdiParent).GetStatusLabel(), "Owner info loaded");
}
/// <summary>
/// Load form Data
/// </summary>
public override void Reload()
{
UIMethods.FillListControl(lstClasses, "Class", "ClassID", LoadFormData.ClassNames());
UIMethods.DisplayStatusMessage(((frmMDIParent)MdiParent).GetStatusLabel(), "Classes loaded");
}