// Method: get record data based on id // ID will always be = 1 public ApplicationValue_Model Get_SpecificAppValue_Record(ref string strMsg) { strMsg = ""; Int64 recID = 1; // Force Sql to always return record ID = 1. // get the connection string connectionString = PSWkr.G_SQLDatabaseConnectionString; // create connection object SqlConnection connection = new SqlConnection(connectionString); // building sql command string sqlStatement = "SELECT ID, NextWorkOrderID, NextBidID " + "FROM Application_Values " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on Equip Model (eMod) ApplicationValue_Model avMod = new ApplicationValue_Model(); try { // open the connection connection.Open(); command.Parameters.AddWithValue("@ID", recID); // execute the reader SqlDataReader reader = command.ExecuteReader(); // populate the invoice list if (reader.Read()) { avMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; avMod.NextWorkOrderID = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0; avMod.NextBidID = (reader[2] != DBNull.Value) ? (Int64)reader[2] : 0; } // the close reader.Close(); strMsg = "ID = " + avMod.ID.ToString(); } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Get_SpecificAppValue_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // the close connection.Close(); // return the Model return avMod; }
// UPDATE private void buttonUpdate_Click(object sender, RoutedEventArgs e) { string strMsg = ""; ApplicationValue_Model avMod = new ApplicationValue_Model(); avMod = (ApplicationValue_Model)DataContext; strMsg = AVWkr.Update_ApplicationValues_rec(avMod); sender = strMsg; // add evet if (OnAV_UPDATE != null) OnAV_UPDATE(sender, new RoutedEventArgs()); }
// ADD Record private void buttonADD_Click(object sender, RoutedEventArgs e) { string strMsg = ""; ApplicationValue_Model avMod = new ApplicationValue_Model(); avMod = (ApplicationValue_Model)DataContext; strMsg = AVWkr.ADD_ApplicationValues_Record(avMod); sender = strMsg; // add evet if (OnAV_ADD != null) OnAV_ADD(sender, new RoutedEventArgs()); }
// Update the Next Bid ID Number by 1 private void UpdateNext_BidsID() { ApplicationValue_Model avMod = new ApplicationValue_Model(); Application_Values_Worker AVWkr = new Application_Values_Worker(); string strMsg = ""; // get the Application Values Model avMod = AVWkr.Get_SpecificAppValue_Record(ref strMsg); // increment next Bids ID number by 1 avMod.ID = 1; avMod.NextBidID = avMod.NextBidID + 1; AVWkr.Update_ApplicationValues_rec(avMod); }
// create the data context private void SetDataContext() { DataContext = null; DataContext = new ApplicationValue_Model(); }
// Get Application Value Record private void Get_ApplicationValue_Rec() { string strMsg = ""; ApplicationValue_Model avMod = new ApplicationValue_Model(); // get specific Application Value will always // get the first record. Hard wired. avMod = AVWkr.Get_SpecificAppValue_Record(ref strMsg); if (avMod != null) { // load data DataContext = (ApplicationValue_Model)avMod; // on recID = 1, turn off Add if (avMod.ID == 1) { buttonADD_Enable.IsEnabled = false; } } }
// ADD public string ADD_ApplicationValues_Record(ApplicationValue_Model avMod) { // Method: Create new record // update the database string strMsg = ""; // get the connection string connectionString = PSWkr.G_SQLDatabaseConnectionString; // create connection object SqlConnection connection = new SqlConnection(connectionString); // building sql command string sqlStatement = "INSERT INTO Application_Values (NextWorkOrderID, NextBidID) " + "VALUES (@NextWorkOrderID, @NextBidID)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@NextWorkOrderID", avMod.NextWorkOrderID); command.Parameters.AddWithValue("@NextBidID", avMod.NextBidID); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: ADD_ApplicationValues_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// Method: update record public string Update_ApplicationValues_rec(ApplicationValue_Model avMod) { // Method: update selected Application Values record // update the database string strMsg = ""; // get the connection string connectionString = PSWkr.G_SQLDatabaseConnectionString; // create connection object SqlConnection connection = new SqlConnection(connectionString); // building sql command string sqlStatement = "UPDATE Application_Values " + "SET NextWorkOrderID=@NextWorkOrderID, " + "NextBidID=@NextBidID " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { // update the database connection.Open(); // use of command.parameters... prevents sql injection command.Parameters.AddWithValue("@NextWorkOrderID", avMod.NextWorkOrderID); command.Parameters.AddWithValue("@NextBidID", avMod.NextBidID); // command.Parameters.AddWithValue("@ID", avMod.ID); // must be in the order of the sqlstatement command.ExecuteNonQuery(); strMsg = "Record was updated."; } catch (Exception e) { strMsg = e.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Update_ApplicationValues_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// Update the Next WorkOrder Number by 1 private void UpdateNext_WONum() { ApplicationValue_Model avMod = new ApplicationValue_Model(); Application_Values_Worker AVWkr = new Application_Values_Worker(); string strMsg = ""; // get application Value model avMod = AVWkr.Get_SpecificAppValue_Record(ref strMsg); // Update, Increment Next Work Order ID by 1 avMod.ID = 1; avMod.NextWorkOrderID = avMod.NextWorkOrderID + 1; AVWkr.Update_ApplicationValues_rec(avMod); }
// Get the next Work Order Number to use on an ADD private Int64 GetNext_WorkOrder_Number() { Application_Values_Worker AVWkr = new Application_Values_Worker(); ApplicationValue_Model avMod = new ApplicationValue_Model(); string strMsg = ""; // get the application Value model // Hard wired to always get record 1 avMod = AVWkr.Get_SpecificAppValue_Record(ref strMsg); // return Next Work Order Number return avMod.NextWorkOrderID; }