// Method: Get List public List<lw_InvoiceItems_Model> Get_InvoiceItem_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, InvItemId, InvoiceID, Description, Cost, " + "InvID, istax " + "FROM lw_InvoiceItems " + "ORDER BY InvoiceID"; // create List List<lw_InvoiceItems_Model> iiMod_List = new List<lw_InvoiceItems_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_InvoiceItems_Model iiMod = new lw_InvoiceItems_Model(); iiMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; iiMod.InvItemId = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0; iiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; iiMod.Description = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; iiMod.Cost = (reader[4] != DBNull.Value) ? Convert.ToDecimal(reader[4]) : 0; iiMod.InvID = (reader[5] != DBNull.Value) ? Convert.ToInt64(reader[5]) : 0; iiMod.istax = (reader[6] != DBNull.Value) ? (string)reader[6] : ""; // add to list iiMod_List.Add(iiMod); } // 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_InvoiceItem_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // return List return iiMod_List; }
// constructor public Win_LWInvoiceItems(lw_InvoiceItems_Model iiMod) { InitializeComponent(); _iiMod = new lw_InvoiceItems_Model(); _iiMod = iiMod; // display data busy_Indicator.IsBusy = false; uc_InvItemDetail.DataContext = _iiMod; // LISTENER: CANCEL uc_InvItemDetail.OnInvItem_CANCEL += (o, e) => { // on a cancle close the window this.Close(); }; }
// Aysnc Update Method public void Update_InvoiceItem_Async(lw_InvoiceItems_Model iiMod) { // aysnc thread Task.Run(() => { // update Invoice Item record return IIWkr.Update_InvoiceItem_rec(iiMod); }) .ContinueWith(task => UpdateResult = task.Result, context); }
// Async Add Method public void Add_InvoiceItem_Async(lw_InvoiceItems_Model iiMod) { // async thread Task.Run(() => { // add Invoice Item record return IIWkr.Add_InvoiceItem_Rec(iiMod); }) .ContinueWith(task => AddResult = task.Result, context); }
// Reset Invoice Item Model private lw_InvoiceItems_Model Reset_InvoiceItem_Mod() { lw_InvoiceItems_Model iiMod = new lw_InvoiceItems_Model(); // reset the model iiMod.ID = 0; iiMod.InvItemId = 0; iiMod.InvoiceID = ""; iiMod.Description = ""; iiMod.Cost = 0; iiMod.InvID = 0; iiMod.istax = ""; // return the model return iiMod; }
// Method: update record public string Update_InvoiceItem_rec(lw_InvoiceItems_Model iiMod) { // 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_InvoiceItems " + "SET InvItemId=@InvItemId, " + "InvoiceID=@InvoiceID, " + "Description=@Description, " + "Cost=@Cost, " + "InvID=@InvID, " + "istax=@istax " + "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("@InvItemId", iiMod.InvItemId); command.Parameters.AddWithValue("@InvoiceID", iiMod.InvoiceID); command.Parameters.AddWithValue("@Description", iiMod.Description); command.Parameters.AddWithValue("@Cost", iiMod.Cost); command.Parameters.AddWithValue("@InvID", iiMod.InvID); command.Parameters.AddWithValue("@istax", iiMod.istax); // command.Parameters.AddWithValue("@ID", iiMod.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_InvoiceItem_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// ADD public string Add_InvoiceItem_Rec(lw_InvoiceItems_Model iiMod) { // 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_InvoiceItems (InvItemId, InvoiceID, Description, " + "Cost, InvID, istax) " + "VALUES (@InvItemId, @InvoiceID, @Description, @Cost, @InvID, @istax)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@InvItemId", iiMod.InvItemId); command.Parameters.AddWithValue("@InvoiceID", iiMod.InvoiceID); command.Parameters.AddWithValue("@Description", iiMod.Description); command.Parameters.AddWithValue("@Cost", iiMod.Cost); command.Parameters.AddWithValue("@InvID", iiMod.InvID); command.Parameters.AddWithValue("@istax", iiMod.istax); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Add_InvoiceItem_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// Method: get record data based on id public lw_InvoiceItems_Model Get_Specific_InvoiceItem_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, InvItemId, InvoiceID, Description, Cost, " + "InvID, istax " + "FROM lw_InvoiceItems " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on Equip Model (eMod) lw_InvoiceItems_Model iiMod = new lw_InvoiceItems_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()) { iiMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; iiMod.InvItemId = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0; iiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; iiMod.Description = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; iiMod.Cost = (reader[4] != DBNull.Value) ? Convert.ToDecimal(reader[4]) : 0; iiMod.InvID = (reader[5] != DBNull.Value) ? Convert.ToInt64(reader[5]) : 0; iiMod.istax = (reader[6] != DBNull.Value) ? (string)reader[6] : ""; } // the close reader.Close(); } catch (Exception e) { strMsg = e.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Get_Specific_InvoiceItem_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // the close connection.Close(); // return the Model return iiMod; }