// Method: Get List public List<lw_InvoiceExpenseItems_Model> Get_InvExpensItems_List(ref string strMsg) { // building the connection string // get the provider, activeStatus database name, and path connectionString = PSWkr.G_SQLDatabaseConnectionString; // create needed objects SqlConnection connection; // building sql command string sqlStatement = "SELECT ID, WOID, InvoiceID, Category, ExpName, " + "Rate, Cost, TotHours " + "FROM lw_InvoiceExpenseItems " + "ORDER BY InvoiceID, Category"; // create List List<lw_InvoiceExpenseItems_Model> ieiMod_List = new List<lw_InvoiceExpenseItems_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()) { lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_Model(); ieiMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0; ieiMod.WOID = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; ieiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; ieiMod.Category = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; ieiMod.ExpName = (reader[4] != DBNull.Value) ? (string)reader[4] : ""; ieiMod.Rate = (reader[5] != DBNull.Value) ? Convert.ToDecimal(reader[5]) : 0; ieiMod.Cost = (reader[6] != DBNull.Value) ? Convert.ToDecimal(reader[6]) : 0; ieiMod.TotHours = (reader[7] != DBNull.Value) ? Convert.ToInt16(reader[7]) : 0; // add Equipment to List ieiMod_List.Add(ieiMod); } // close reader, close connection reader.Close(); connection.Close(); strMsg = "List Complete."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Get_InvExpensItems_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // return List return ieiMod_List; }
// Aysnc Update Method public void Update_InvExpItems_Async(lw_InvoiceExpenseItems_Model ieiMod) { // aysnc thread Task.Run(() => { // update Invoice Item record return IEIWkr.Update_InvExpensItems_rec(ieiMod); }) .ContinueWith(task => UpdateResult = task.Result, context); }
// Async Add Method public void Add_InvExpItems_Async(lw_InvoiceExpenseItems_Model ieiMod) { // async thread Task.Run(() => { // add Invoice Item record return IEIWkr.Add_InvExpensItems_Rec(ieiMod); }) .ContinueWith(task => AddResult = task.Result, context); }
// ADD Enable - with all blank fields private void buttonADD_Enable_Blank_Click(object sender, RoutedEventArgs e) { // 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 lw_InvoiceExpenseItems_Model(); // reset and add configuration ResetDisplayFields(); ADDButtonConfiguration(); // Add enabled event if (OnInvExpItems_ADDEnable_Blank != null) OnInvExpItems_ADDEnable_Blank(sender, new RoutedEventArgs()); }
// UPDATE private void buttonUpdate_Click(object sender, RoutedEventArgs e) { lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_Model(); // load the model ieiMod = (lw_InvoiceExpenseItems_Model)this.DataContext; // MVVM async Update iieVM.Update_InvExpItems_Async(ieiMod); // reset fields ResetDisplayFields(); // use inital button configuration InitialButtonConfiguration(); }
// Method: get record data based on id public lw_InvoiceExpenseItems_Model Get_SpecificInvExpensItem_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, WOID, InvoiceID, Category, ExpName, " + "Rate, Cost, TotHours " + "FROM lw_InvoiceExpenseItems " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on model lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_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()) { ieiMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0; ieiMod.WOID = (reader[1] != DBNull.Value) ? (string)reader[1] : ""; ieiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; ieiMod.Category = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; ieiMod.ExpName = (reader[4] != DBNull.Value) ? (string)reader[4] : ""; ieiMod.Rate = (reader[5] != DBNull.Value) ? Convert.ToDecimal(reader[5]) : 0; ieiMod.Cost = (reader[6] != DBNull.Value) ? Convert.ToDecimal(reader[6]) : 0; ieiMod.TotHours = (reader[7] != DBNull.Value) ? Convert.ToInt16(reader[7]) : 0; } // the close reader.Close(); } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Get_SpecificInvExpensItem_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // the close connection.Close(); // return the Model return ieiMod; }
// Method: update record public string Update_InvExpensItems_rec(lw_InvoiceExpenseItems_Model ieiMod) { // Method: update selected Client 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 lw_InvoiceExpenseItems " + "SET WOID=@WOID, " + "InvoiceID=@InvoiceID, " + "Category=@Category, " + "ExpName=@ExpName, " + "Rate=@Rate, " + "Cost=@Cost, " + "TotHours=@TotHours " + "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("@WOID", ieiMod.WOID); command.Parameters.AddWithValue("@InvoiceID", ieiMod.InvoiceID); command.Parameters.AddWithValue("@Category", ieiMod.Category); command.Parameters.AddWithValue("@ExpName", ieiMod.ExpName); command.Parameters.AddWithValue("@Rate", ieiMod.Rate); command.Parameters.AddWithValue("@Cost", ieiMod.Cost); command.Parameters.AddWithValue("@TotHours", ieiMod.TotHours); // command.Parameters.AddWithValue("@ID", ieiMod.ID); // must be in the order of the sqlstatement command.ExecuteNonQuery(); strMsg = "Record was updated."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Update_InvExpensItems_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// ADD public string Add_InvExpensItems_Rec(lw_InvoiceExpenseItems_Model ieiMod) { // 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 lw_InvoiceExpenseItems (WOID, InvoiceID, " + "Category, ExpName, Rate, Cost, TotHours) " + "VALUES (@WOID, @InvoiceID, @Category, @ExpName, @Rate, @Cost, @TotHours)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@WOID", ieiMod.WOID); command.Parameters.AddWithValue("@InvoiceID", ieiMod.InvoiceID); command.Parameters.AddWithValue("@Category", ieiMod.Category); command.Parameters.AddWithValue("@ExpName", ieiMod.ExpName); command.Parameters.AddWithValue("@Rate", ieiMod.Rate); command.Parameters.AddWithValue("@Cost", ieiMod.Cost); command.Parameters.AddWithValue("@TotHours", ieiMod.TotHours); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Add_InvExpensItems_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }