// Method: Get List public List<lwdom_JobType_Model> Get_JobType_List() { // building the connection string // get the provider, activeStatus database name, and path connectionString = PSWkr.G_SQLDatabaseConnectionString; string strMsg = ""; // create needed objects SqlConnection connection; // building sql command, chemcode is the Key string sqlStatement = "SELECT ID, JobType " + "FROM lwdom_JobType " + "ORDER BY JobType"; // create List List<lwdom_JobType_Model> jtMod_List = new List<lwdom_JobType_Model>(); try { connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand(sqlStatement, connection); SqlDataReader reader = command.ExecuteReader(); // read table, populate model while (reader.Read()) { lwdom_JobType_Model jtMod = new lwdom_JobType_Model(); jtMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; jtMod.JobType = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; // add Equipment to List jtMod_List.Add(jtMod); } // close reader, close connection reader.Close(); connection.Close(); strMsg = "List Complete."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); } // return List return jtMod_List; }
// Configure Window to Allow ADD private void menuAddJobType_Click(object sender, RoutedEventArgs e) { // set the data context // Enable Add Button has been clicked, so release any data // context reference DataContext = null; // Create a new model object and bind // it to the dataContext DataContext = new lwdom_JobType_Model(); ResetDisplayFields(); ADDButtonConfiguration(); labelStatus.Content = ""; }
// ADD Job Type record private void buttonADD_Click(object sender, RoutedEventArgs e) { lwdom_JobType_Model jtMod = new lwdom_JobType_Model(); string strMsg = ""; // load data jtMod.JobType = txtJobType.Text.Trim(); // add the record strMsg = JTWkr.Add_JobType_Rec(jtMod); // display message labelStatus.Content = strMsg; ListModelsInGrid(); ResetDisplayFields(); InitialButtonConfiguration(); }
// UPDATE private void buttonUpdate_Click(object sender, RoutedEventArgs e) { lwdom_JobType_Model jtMod = new lwdom_JobType_Model(); string strMsg = ""; jtMod = LoadDataForUpdate(); strMsg = JTWkr.Update_JobType_rec(jtMod); // message labelStatus.Content = strMsg; ListModelsInGrid(); ResetDisplayFields(); InitialButtonConfiguration(); }
// Display data from grid private void DisplayData(lwdom_JobType_Model jtMod) { lblID.Content = jtMod.ID; txtJobType.Text = jtMod.JobType; }
// load data for update private lwdom_JobType_Model LoadDataForUpdate() { lwdom_JobType_Model jtMod = new lwdom_JobType_Model(); jtMod.ID = Convert.ToInt64(lblID.Content.ToString()); jtMod.JobType = txtJobType.Text.Trim(); // return model return jtMod; }
// Method: get record data based on id public lwdom_JobType_Model Get_SpecificJobType_Record(int recID) { string strMsg = ""; // get the connection string connectionString = PSWkr.G_SQLDatabaseConnectionString; // create connection object SqlConnection connection = new SqlConnection(connectionString); // building sql command string sqlStatement = "SELECT ID, JobType " + "FROM lwdom_JobType " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on LW Jobtype Model (jtMod) lwdom_JobType_Model jtMod = new lwdom_JobType_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()) { jtMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; jtMod.JobType = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; } // the close reader.Close(); } catch (Exception e) { strMsg = e.Message.ToString(); } // the close connection.Close(); // return the Model return jtMod; }
// Method: update record public string Update_JobType_rec(lwdom_JobType_Model jtMod) { // Method: update selected Hours Master recore // 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 lwdom_JobType " + "SET JobType=@JobType " + "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("@JobType", jtMod.JobType); // command.Parameters.AddWithValue("@ID", jtMod.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, "", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// ADD public string Add_JobType_Rec(lwdom_JobType_Model jtMod) { // 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 lwdom_JobType (JobType) " + "VALUES (@JobType)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@JobType", jtMod.JobType); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); } connection.Close(); return strMsg; }