// Method: Get List public List<lwdom_Rates_Model> Get_Rates_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, RateID, Category, Code, RateYear, Description, " + "RetailRate, CostRate, ActiveStatus " + "FROM lwdom_Rates " + "ORDER BY RateYear, Category"; // create List List<lwdom_Rates_Model> lwrMod_List = new List<lwdom_Rates_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_Rates_Model lwrMod = new lwdom_Rates_Model(); lwrMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; lwrMod.RateID = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; lwrMod.Category = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; lwrMod.Code = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; lwrMod.RateYear = (reader[4] != DBNull.Value) ? (int)reader[4] : 0; lwrMod.Description = (reader[5] != DBNull.Value) ? (string)reader[5] : ""; lwrMod.RetailRate = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0; lwrMod.CostRate = (reader[7] != DBNull.Value) ? (decimal)reader[7] : 0; lwrMod.ActiveStatus = (reader[8] != DBNull.Value) ? (string)reader[8] : ""; // add Equipment to List lwrMod_List.Add(lwrMod); } // close reader, close connection reader.Close(); connection.Close(); strMsg = "List Complete."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); } // return List return lwrMod_List; }
// Method: get record data based on id public lwdom_Rates_Model Get_SpecificRates_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, RateID, Category, Code, RateYear, Description, " + "RetailRate, CostRate, ActiveStatus " + "FROM lwdom_Rates " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on LW Rates Model (lwrMod) lwdom_Rates_Model lwrMod = new lwdom_Rates_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()) { lwrMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; lwrMod.RateID = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; lwrMod.Category = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; lwrMod.Code = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; lwrMod.RateYear = (reader[4] != DBNull.Value) ? (int)reader[4] : 0; lwrMod.Description = (reader[5] != DBNull.Value) ? (string)reader[5] : ""; lwrMod.RetailRate = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0; lwrMod.CostRate = (reader[7] != DBNull.Value) ? (decimal)reader[7] : 0; lwrMod.ActiveStatus = (reader[8] != DBNull.Value) ? (string)reader[8] : ""; } // the close reader.Close(); } catch (Exception e) { strMsg = e.Message.ToString(); } // the close connection.Close(); // return the Model return lwrMod; }
// Reset lwdom_Rates Model private lwdom_Rates_Model Reset_Rates_Model(lwdom_Rates_Model lwrMod) { // reset the model lwrMod.ID = 0; lwrMod.RateID = ""; lwrMod.Category = ""; lwrMod.Code = ""; lwrMod.RateYear = 0; lwrMod.Description = ""; lwrMod.RetailRate = 0; lwrMod.CostRate = 0; lwrMod.ActiveStatus = ""; // return the model return lwrMod; }
// ADD public string Add_Rates_Rec(lwdom_Rates_Model lwrMod) { // 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_Rates (RateID, Category, Code, RateYear, " + "Description, RetailRate, CostRate, ActiveStatus) " + "VALUES (@RateID, @Category, @Code, @RateYear, " + "@Description, @RetailRate, @CostRate, @ActiveStatus)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@RateID", lwrMod.RateID); command.Parameters.AddWithValue("@Category", lwrMod.Category); command.Parameters.AddWithValue("@Code", lwrMod.Code); command.Parameters.AddWithValue("@RateYear", lwrMod.RateYear); command.Parameters.AddWithValue("@Description", lwrMod.Description); command.Parameters.AddWithValue("@RetailRate", lwrMod.RetailRate); command.Parameters.AddWithValue("@CostRate", lwrMod.CostRate); command.Parameters.AddWithValue("@ActiveStatus", lwrMod.ActiveStatus); //lwdcMod.activeStatus = true; //command.Parameters.AddWithValue("@activeStatus", lwdcMod.activeStatus); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); } connection.Close(); return strMsg; }
// Method: update record public string Update_Rates_rec(lwdom_Rates_Model lwrMod) { // 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_Rates " + "SET RateID=@RateID, " + "Category=@Category, " + "Code=@Code, " + "RateYear=@RateYear, " + "Description=@Description, " + "RetailRate=@RetailRate, " + "CostRate=@CostRate, " + "ActiveStatus=@ActiveStatus " + "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("@RateID", lwrMod.RateID); command.Parameters.AddWithValue("@Category", lwrMod.Category); command.Parameters.AddWithValue("@Code", lwrMod.Code); command.Parameters.AddWithValue("@RateYear", lwrMod.RateYear); command.Parameters.AddWithValue("@Description", lwrMod.Description); command.Parameters.AddWithValue("@RetailRate", lwrMod.RetailRate); command.Parameters.AddWithValue("@CostRate", lwrMod.CostRate); command.Parameters.AddWithValue("@ActiveStatus", lwrMod.ActiveStatus); // command.Parameters.AddWithValue("@ID", lwrMod.ID); // must be in the order of the sqlstatement command.ExecuteNonQuery(); strMsg = "Record was updated."; } catch (Exception e) { strMsg = e.Message.ToString(); } connection.Close(); return strMsg; }
/// <summary> /// Create a copy of this instance /// </summary> /// <param name="original"></param> /// <returns></returns> public lwdom_Rates_Model Copy() { // create the model copy lwdom_Rates_Model modelCopy = new lwdom_Rates_Model(); // copy the data modelCopy.ID = ID; modelCopy.RateID = RateID; modelCopy.Category = Category; modelCopy.Code = Code; modelCopy.RateYear = RateYear; modelCopy.Description = Description; modelCopy.RetailRate = RetailRate; modelCopy.CostRate = CostRate; modelCopy.ActiveStatus = ActiveStatus; // return the copy return modelCopy; }
public void Update_Rates_Async(lwdom_Rates_Model rMod) { // Async thread Task.Run(() => { return RWkr.Update_Rates_rec(rMod); }) .ContinueWith(task => UpdateResult = task.Result, context); }
public void Add_Rate_Async(lwdom_Rates_Model rMod) { // Aysnc thread Task.Run(() => { return RWkr.Add_Rates_Rec(rMod); }) .ContinueWith(task => AddResult = task.Result, context); }
// ADD Record private void buttonADD_Click(object sender, RoutedEventArgs e) { lwdom_Rates_Model rMod = new lwdom_Rates_Model(); // load the model rMod = (lwdom_Rates_Model)this.DataContext; // async ADD viewModel.Add_Rate_Async(rMod); // reset fields ResetDisplayFields(); // use inital button configuration InitialButtonConfiguration(); }
// ADD Enable - Blank display fields private void buttonADD_EnableBlank_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_Rates_Model(); ResetDisplayFields(); ADDButtonConfiguration(); }