/// <summary>
/// Function used to select one worker from database, uses workerId as the primary key
/// </summary>
/// <param name="id">an id representing workers stored in the database</param>
/// <returns>a worker object</returns>
internal static PieceworkWorker GetOneRow(int workerId)
{
// Declare new worker object
PieceworkWorker returnWorker = new PieceworkWorker();
// Declare new SQL connection
SqlConnection dbConnection = new SqlConnection(GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT TOP 1 * FROM [tblEntries] WHERE [EntryId] = " + workerId, dbConnection);
// Try to connect to the database, create a datareader. If successful, read from the database and fill created row
// with information from matching record
try
{
dbConnection.Open();
IDataReader reader = command.ExecuteReader();
if (reader.Read())
{
returnWorker = new PieceworkWorker(reader.GetString(1), reader.GetString(2), reader.GetString(3));
returnWorker.Id = workerId;
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("A database error has been encountered: " + Environment.NewLine + ex.Message, "Database Error");
}
finally
{
dbConnection.Close();
}
// Return the populated row
return(returnWorker);
}
/// <summary>
/// Function to add a new worker to the worker database
/// </summary>
/// <param name="insertWorker">a worker object to be inserted</param>
/// <returns>true if successful</returns>
internal static bool InsertNewRecord(PieceworkWorker insertWorker)
{
// Create return value
bool returnValue = false;
// Declare the connection
SqlConnection dbConnection = new SqlConnection(GetConnectionString());
// Create new SQL command and assign it paramaters
SqlCommand command = new SqlCommand("INSERT INTO tblEntries VALUES(@firstName, @lastName, @messages, @pay, @entryDate)", dbConnection);
command.Parameters.AddWithValue("@firstName", insertWorker.FirstName);
command.Parameters.AddWithValue("@lastName", insertWorker.LastName);
command.Parameters.AddWithValue("@messages", insertWorker.Messages);
command.Parameters.AddWithValue("@pay", insertWorker.Pay);
command.Parameters.AddWithValue("@entryDate", insertWorker.EntryDate);
// Try to insert the new record, return result
try
{
dbConnection.Open();
returnValue = (command.ExecuteNonQuery() == 1);
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("A database error has been encountered: " + Environment.NewLine + ex.Message, "Database Error");
}
finally
{
dbConnection.Close();
}
// Return the true if this worked, false if it failed
return(returnValue);
}
/// <summary>
/// Function to update an existing worker in the worker database; if they don't exist, it instead attempts to add this worker as a new worker
/// </summary>
/// <param name="updateWorker">a worker object to be updated</param>
/// <returns>true if successful</returns>
internal static bool UpdateExistingRow(PieceworkWorker updateWorker)
{
// Create return value
bool returnValue = false;
// If the worker exists, create dbConnection
if (updateWorker.Id > 0)
{
// Declare the connection
SqlConnection dbConnection = new SqlConnection(GetConnectionString());
// Create new SQL command and assign it paramaters
SqlCommand command = new SqlCommand("UPDATE tblEntries Set FirstName = @firstName, LastName = @lastName, Messages = @messages, Pay = @pay WHERE EntryId = @entryId", dbConnection);
command.Parameters.AddWithValue("@workerId", updateWorker.Id);
command.Parameters.AddWithValue("@firstName", updateWorker.FirstName);
command.Parameters.AddWithValue("@lastName", updateWorker.LastName);
command.Parameters.AddWithValue("@messages", updateWorker.Messages);
command.Parameters.AddWithValue("@pay", updateWorker.Pay);
command.Parameters.AddWithValue("@entryDate", updateWorker.EntryDate);
// Try to open a connection to the database and update the record. Return result.
try
{
dbConnection.Open();
if (command.ExecuteNonQuery() > 0)
{
returnValue = true;
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("A database error has been encountered: " + Environment.NewLine + ex.Message, "Database Error");
}
finally
{
dbConnection.Close();
}
}
// If the worker does not exist, attempt to insert it instead
else
{
if (InsertNewRecord(updateWorker))
{
returnValue = true;
}
}
// Returns true if the query executed; always false if the row is invalid
return(returnValue);
}
/// <summary>
/// This is event handler for the calculate button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnCalculatePay_Click(object sender, RoutedEventArgs e)
{
// Calling piece worker with name and message as input
try
{
PieceworkWorker pieceworkWorker = new PieceworkWorker(txtWorkerFirstName.Text, txtWorkerLastName.Text, txtMessageSent.Text);
//Show the output in labels
lblResultTotalPay.Content = pieceworkWorker.Pay.ToString("c");
//updating the status
UpdateStatus("Worker " + pieceworkWorker.FirstName + " " + pieceworkWorker.LastName + " has been entered with " + pieceworkWorker.Messages + " messages and pay of " + pieceworkWorker.Pay.ToString("c"));
// disable calculate button and entry fields
btnCalculatePay.IsEnabled = false;
txtMessageSent.IsEnabled = false;
txtWorkerFirstName.IsEnabled = false;
txtWorkerLastName.IsEnabled = false;
// focus on the clear button
btnClearFields.Focus();
}
catch (ArgumentNullException ex)
{
if (ex.ParamName == "FirstName")
{
lblWorkerFirstNameError.Content = ex.Message;
txtWorkerFirstName.Background = Brushes.Red;
txtWorkerFirstName.Focus();
}
else if (ex.ParamName == "LastName")
{
lblWorkerLastNameError.Content = ex.Message;
txtWorkerLastName.Background = Brushes.Red;
txtWorkerLastName.Focus();
}
else if (ex.ParamName == "Message")
{
lblMessageSentError.Content = ex.Message;
txtMessageSent.Background = Brushes.Red;
txtMessageSent.Focus();
}
}
catch (ArgumentOutOfRangeException ex)
{
if (ex.ParamName == "FirstName")
{
lblWorkerFirstNameError.Content = ex.Message;
txtWorkerFirstName.Background = Brushes.Red;
txtWorkerFirstName.Focus();
}
if (ex.ParamName == "LastName")
{
lblWorkerLastNameError.Content = ex.Message;
txtWorkerLastName.Background = Brushes.Red;
txtWorkerLastName.Focus();
}
if (ex.ParamName == "Message")
{
lblMessageSentError.Content = ex.Message;
txtMessageSent.Background = Brushes.Red;
txtMessageSent.Focus();
}
}
catch (ArgumentException ex)
{
if (ex.ParamName == "FirstName")
{
lblWorkerFirstNameError.Content = ex.Message;
txtWorkerFirstName.Background = Brushes.Red;
txtWorkerFirstName.Focus();
}
if (ex.ParamName == "Last")
{
lblWorkerLastNameError.Content = ex.Message;
txtWorkerLastName.Background = Brushes.Red;
txtWorkerFirstName.Focus();
}
if (ex.ParamName == "Message")
{
lblMessageSentError.Content = ex.Message;
txtMessageSent.Background = Brushes.Red;
txtMessageSent.Focus();
}
}
catch (Exception excep)
{
// showing the message that cause interruption
MessageBox.Show(excep.Message);
//focus back on the buttons
btnClearFields.Focus();
}
}