// Method: Get List public List<lw_Client_Model> Get_Client_List(ref string strMsg, string _status) { // 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, AccNum, Name, ActiveStatus, Note, Comments " + "FROM lw_Client " + "WHERE ActiveStatus=@ActiveStatus " + "ORDER BY AccNum"; // create List List<lw_Client_Model> cMod_List = new List<lw_Client_Model>(); try { connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand(sqlStatement, connection); // add command parameter command.Parameters.AddWithValue("@ActiveStatus", _status); SqlDataReader reader = command.ExecuteReader(); // read table, populate model while (reader.Read()) { lw_Client_Model cMod = new lw_Client_Model(); cMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; cMod.AccNum = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0; cMod.Name = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; cMod.ActiveStatus = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; cMod.Note = (reader[4] != DBNull.Value) ? (string)reader[4] : ""; cMod.Comments = (reader[5] != DBNull.Value) ? (string)reader[5] : ""; // add Equipment to List cMod_List.Add(cMod); } // close reader, close connection reader.Close(); connection.Close(); strMsg = ""; } catch (Exception errMsg) { strMsg = errMsg.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "SQL Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } // return List return cMod_List; }
// ADD private void buttonADD_Click(object sender, RoutedEventArgs e) { lw_Client_Model lwclMod = new lw_Client_Model(); string strMsg = ""; string strNum = ""; bool result = false; Int64 intAccNum = 0; // get the data from the context and // add data to the model lw_Client_Model cMod = (lw_Client_Model)DataContext; // Instert record strMsg = LWCWkr.Add_Client_Rec(cMod); // add new rec strNum = LWCWkr.Get_LastIDKey_Used(); // get rec number to be used as AccNum // convert string to Int64 result = Int64.TryParse(strNum, out intAccNum); if (intAccNum > 0) { // load model cMod.ID = intAccNum; cMod.AccNum = intAccNum; // update the accnum in the client cVM.Update_Client_Async(cMod); // reset and reconfigur ResetDisplayFields(); InitialButtonConfiguration(); } else { strMsg = "AccNum NOT UPDATED."; } }
// ADD Enable - Blank, all fields start as blank 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_Client_Model(); ResetDisplayFields(); ADDButtonConfiguration(); // Add Enable Event if (OnClient_ADDEnable_Blank != null) OnClient_ADDEnable_Blank(sender, new RoutedEventArgs()); }
// load the Model // load model is used on an ADD private lw_Client_Model Load_Model() { lw_Client_Model lwcMod = new lw_Client_Model(); bool result = false; Int64 intNum = 0; lwcMod.ID = 0; // result = Int64.TryParse(lblAccNum.Content.ToString(), out intNum); lwcMod.AccNum = (result) ? intNum : 0; lwcMod.Name = txtName.Text.Trim(); lwcMod.ActiveStatus = cboActiveStatus.Text.Trim(); lwcMod.Note = txtNote.Text.Trim(); // returne the model return lwcMod; }
// update private void buttonUpdate_Click(object sender, RoutedEventArgs e) { lw_Client_Model cMod = new lw_Client_Model(); // load model cMod = (lw_Client_Model)this.DataContext; // async Update cVM.Update_Client_Async(cMod); // reset and reconfigure ResetDisplayFields(); InitialButtonConfiguration(); }
// Method: get record data based on id public lw_Client_Model Get_SpecificClient_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, AccNum, Name, ActiveStatus, Note, Comments " + "FROM lw_Client " + "WHERE ID=@ID"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); // Create object base on Equip Model (eMod) lw_Client_Model cMod = new lw_Client_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()) { cMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0; cMod.AccNum = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0; cMod.Name = (reader[2] != DBNull.Value) ? (string)reader[2] : ""; cMod.ActiveStatus = (reader[3] != DBNull.Value) ? (string)reader[3] : ""; cMod.Note = (reader[4] != DBNull.Value) ? (string)reader[4] : ""; cMod.Comments = (reader[5] != DBNull.Value) ? (string)reader[5] : ""; } // the close reader.Close(); } catch (Exception e) { strMsg = e.Message.ToString(); } // the close connection.Close(); // return the Model return cMod; }
// Reset Equipment Model private lw_Client_Model Reset_Client_Mod(lw_Client_Model cMod) { // reset the model cMod.ID = 0; cMod.AccNum = 0; cMod.Name = ""; cMod.ActiveStatus = ""; cMod.Note = ""; // return the model return cMod; }
// Method: update record public string Update_Client_rec(lw_Client_Model cMod) { // 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_Client " + "SET AccNum=@AccNum, " + "Name=@Name, " + "ActiveStatus=@ActiveStatus, " + "Note=@Note, " + "Comments=@Comments " + "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("@AccNum", cMod.AccNum); command.Parameters.AddWithValue("@Name", cMod.Name); command.Parameters.AddWithValue("@ActiveStatus", cMod.ActiveStatus); command.Parameters.AddWithValue("@Note", cMod.Note); command.Parameters.AddWithValue("@Comments", cMod.Comments); // command.Parameters.AddWithValue("@ID", cMod.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_Client_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
// ADD public string Add_Client_Rec(lw_Client_Model cMod) { // 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_Client (AccNum, Name, ActiveStatus, Note, Comments) " + "VALUES (@AccNum, @Name, @ActiveStatus, @Note, @Comments)"; // SqlCommand SqlCommand command = new SqlCommand(sqlStatement, connection); try { connection.Open(); // Adding parameters for the Insert Command command.Parameters.AddWithValue("@AccNum", cMod.AccNum); command.Parameters.AddWithValue("@Name", cMod.Name); command.Parameters.AddWithValue("@ActiveStatus", cMod.ActiveStatus); command.Parameters.AddWithValue("@Note", cMod.Note); command.Parameters.AddWithValue("@Comments", cMod.Comments); command.ExecuteNonQuery(); strMsg = "Record was added."; } catch (Exception e) { strMsg = e.Message.ToString(); System.Windows.MessageBox.Show(strMsg, "Method: Add_Client_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } connection.Close(); return strMsg; }
public void Update_Client_Async(lw_Client_Model cMod) { // Async thread Task.Run(() => { return CWkr.Update_Client_rec(cMod); }) .ContinueWith(task => UpdateResult = task.Result, context); }
public void Add_Client_Async(lw_Client_Model cMod) { // Aysnc thread Task.Run(() => { return CWkr.Add_Client_Rec(cMod); }) .ContinueWith(task => AddResult = task.Result, context); }