// Before this method is called, the user is given a confirmation dialog
// Upon acceptance, this method will remove the selected dog from the database
// The fields are then reloaded to show as if no dog has been selected
protected void Delete_Dog_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection
(WebConfigurationManager.ConnectionStrings["SPCAConnectionString"].ConnectionString);
using (SqlCommand cmd = new SqlCommand("DELETE FROM Dogs WHERE Dog_Name=@Name", conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@Name", dogName.Value);
cmd.ExecuteNonQuery();
conn.Close();
}
// This refreshes the dropdown menu
DogDropdown.Items.Clear();
DogDropdown.Items.Add("Select Dog");
DogDropdown.DataBind();
DogDropdown.SelectedValue = "Select Dog";
Set_All_Blank();
}
// When the save button is clicked, the page reads all the field data and inserts or updates into the database accordingly
protected void Save_Dog_Click(object sender, EventArgs e)
{
// Connect to SPCA database
SqlConnection conn = new SqlConnection
(WebConfigurationManager.ConnectionStrings["SPCAConnectionString"].ConnectionString);
// SqlDataAdapter is used for inserting, updating, or deleting
SqlDataAdapter adap = new SqlDataAdapter();
// Open db connection
conn.Open();
// Initialize variable for the query string
string sql;
// If we are adding a new dog, insert; else update the dog under the selected value's ID
if (titleText.InnerText.Equals("Adding New Dog"))
{
sql = $"INSERT INTO Dogs (Dog_Name, Kennel_ID, Breed, Age, Weight, Notes) VALUES (@Name, @Kennel, @Breed, @Age, @Weight, @Notes);";
}
else if (titleText.InnerText.Equals("Editing Current Dog"))
{
int dogID = Get_Dog_ID(DogDropdown.SelectedValue);
sql = $"UPDATE Dogs SET Dog_Name=@Name, Kennel_ID=@Kennel, Breed=@Breed, Age=@Age, Weight=@Weight, Notes=@Notes WHERE Dog_ID={dogID};";
}
else
{
return;
}
SqlCommand cmd;
// Create executable SQL command
using (cmd = new SqlCommand(sql))
{
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@Name", dogName.Value);
cmd.Parameters.AddWithValue("@Kennel", int.Parse(dogKennelID.Value));
cmd.Parameters.AddWithValue("@Breed", dogBreed.Value);
cmd.Parameters.AddWithValue("@Age", float.Parse(dogAge.Value));
cmd.Parameters.AddWithValue("@Weight", float.Parse(dogWeight.Value));
cmd.Parameters.AddWithValue("@Notes", notes.Value);
}
// Pass the command to the adapter object
adap.InsertCommand = cmd;
// Execute the statement against out database
adap.InsertCommand.ExecuteNonQuery();
// If an image has been uploaded, save it
if (imageUpload.HasFile)
{
int dogID = Get_Dog_ID(DogDropdown.SelectedValue);
byte[] imgBytes;
using (BinaryReader br = new BinaryReader(imageUpload.PostedFile.InputStream))
{
imgBytes = br.ReadBytes(imageUpload.PostedFile.ContentLength);
}
sql = $"UPDATE Dogs SET Picture=@Image WHERE Dog_ID={dogID}";
cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Image", imgBytes);
adap.InsertCommand = cmd;
adap.InsertCommand.ExecuteNonQuery();
}
sql = "SELECT Picture FROM Dogs WHERE Dog_ID = " + Get_Dog_ID(DogDropdown.SelectedValue);;
cmd = new SqlCommand(sql, conn);
byte[] dbImage;
object binaryData = cmd.ExecuteScalar();
if (!binaryData.Equals(System.DBNull.Value))
{
dbImage = (byte[])binaryData;
string base64String = Convert.ToBase64String(dbImage, 0, dbImage.Length);
dogPic.ImageUrl = "data:image/png;base64," + base64String;
}
else
{
dogPic.ImageUrl = "/Content/paw_print.png";
}
// Dispose all objects
adap.Dispose();
cmd.Dispose();
conn.Close();
// Now, to leave "editing mode"
selectDogArea.Visible = true;
optionsArea.Visible = true;
saveCancelArea.Visible = false;
imageUpload.Visible = false;
Set_All_Disabled(true);
titleText.InnerText = Title;
// This refreshes the dropdown menu and resets the selected value to the new/edited dog
DogDropdown.Items.Clear();
DogDropdown.Items.Add("Select Dog");
DogDropdown.DataBind();
DogDropdown.Items.FindByText(dogName.Value).Selected = true;
}