// Method: Get List public List<lwdom_LocCat_Model> Get_LocCat_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, LocCatDesc " + "FROM lwdom_LocCat " + "ORDER BY LocCatDesc"; // create List List<lwdom_LocCat_Model> lcMod_List = new List<lwdom_LocCat_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_LocCat_Model lcMod = new lwdom_LocCat_Model(); lcMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; lcMod.LocCatDesc = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; // add Equipment to List lcMod_List.Add(lcMod); } // close reader, close connection reader.Close(); connection.Close(); strMsg = "List Complete."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); } // return List return lcMod_List; }
// UPDATE Record private void buttonUpdate_Click(object sender, RoutedEventArgs e) { lwdom_LocCat_Model lcMod = new lwdom_LocCat_Model(); string strMsg = ""; // load data lcMod = LoadDataForUpdate(); // Update the record strMsg = LCWkr.Update_LocCat_rec(lcMod); // display message labelStatus.Content = strMsg; ListModelsInGrid(); ResetDisplayFields(); InitialButtonConfiguration(); }
// Display data from grid private void DisplayData(lwdom_LocCat_Model lcMod) { lblID.Content = lcMod.ID; txtLocCatDesc.Text = lcMod.LocCatDesc; }
// load data for update private lwdom_LocCat_Model LoadDataForUpdate() { lwdom_LocCat_Model lcMod = new lwdom_LocCat_Model(); bool result = false; Int64 iNum = 0; // try Parse result = Int64.TryParse(lblID.Content.ToString(), out iNum); lcMod.ID = (result) ? iNum : 0; lcMod.LocCatDesc = txtLocCatDesc.Text.Trim(); // return model return lcMod; }
// Method: get record data based on id public lwdom_LocCat_Model Get_SpecificLocCat_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, LocCatDesc " + "FROM lwdom_LocCat " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on LW LocCat Model (lcMod) lwdom_LocCat_Model lcMod = new lwdom_LocCat_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()) { lcMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; lcMod.LocCatDesc = (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 lcMod; }
// Method: update record public string Update_LocCat_rec(lwdom_LocCat_Model lcMod) { // 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_LocCat " + "SET LocCatDesc=@LocCatDesc " + "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("@LocCatDesc", lcMod.LocCatDesc); // command.Parameters.AddWithValue("@ID", lcMod.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_LocCat_Rec(lwdom_LocCat_Model lcMod) { // 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_LocCat (LocCatDesc) " + "VALUES (@LocCatDesc)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@LocCatDesc", lcMod.LocCatDesc); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); } connection.Close(); return strMsg; }