// ADD Record
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
lwdom_chemlist_Model lwdclMod = new lwdom_chemlist_Model();
lwdom_chemlist_Woker LWDCLWkr = new lwdom_chemlist_Woker();
// load the model
lwdclMod = Load_Model();
// async ADD
clVM.Add_ChemList_Async(lwdclMod);
// reset fields
ResetDisplayFields();
// use inital button configuration
InitialButtonConfiguration();
}
public void Update_ChemList_Async(lwdom_chemlist_Model clMod)
{
// Async thread
Task.Run(() =>
{
return CLWkr.Update_ChemList_rec(clMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
public void Add_ChemList_Async(lwdom_chemlist_Model clMod)
{
// Aysnc thread
Task.Run(() =>
{
return CLWkr.Add_ChemList_Rec(clMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// UPDATE ChemList Record
private void buttonUpdate_Click(object sender, RoutedEventArgs e)
{
lwdom_chemlist_Model lwdclMod = new lwdom_chemlist_Model();
lwdom_chemlist_Woker LWDCLWkr = new lwdom_chemlist_Woker();
// load the model
lwdclMod = (lwdom_chemlist_Model)this.DataContext;
// async Update
clVM.Update_ChemList_Async(lwdclMod);
// reset fields
ResetDisplayFields();
// use inital button configuration
InitialButtonConfiguration();
}
// load the Model
private lwdom_chemlist_Model Load_Model()
{
lwdom_chemlist_Model lwdclMod = new lwdom_chemlist_Model();
bool result = false;
decimal decNum = 0;
lwdclMod.ChemCode = txtChemCode.Text;
lwdclMod.EPANum = txtEPANum.Text;
lwdclMod.ActiveIngr = txtActiveIngr.Text;
lwdclMod.Manufacturer = txtManufacturer.Text;
//
// Retail
result = decimal.TryParse(txtRetail.Text.Trim(), out decNum);
lwdclMod.retail = decNum;
// Cost
result = decimal.TryParse(txtcost.Text.Trim(), out decNum);
lwdclMod.cost = decNum;
//
lwdclMod.Comment = cboComment.Text;
lwdclMod.Units = cboUnits.Text;
lwdclMod.activeStatus = cboactiveStatus.Text.Trim();
// returne the model
return lwdclMod;
}
// Method: get record data based on id
public lwdom_chemlist_Model Get_SpecificChemList_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 OBJECTID, chemcode, epanum, activeingr, manufacturer, retail, cost, comment, units, activeStatus " +
"FROM lwdom_chemlist " +
"WHERE chemcode=@chemcode";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on Equip Model (eMod)
lwdom_chemlist_Model lwdcMod = new lwdom_chemlist_Model();
try
{
// open the connection
connection.Open();
command.Parameters.AddWithValue("@OBJECTID", recID);
// execute the reader
SqlDataReader reader = command.ExecuteReader();
// populate the invoice list
if (reader.Read())
{
lwdcMod.OBJECTID = (reader[0] != DBNull.Value) ? (Int32)reader[0] : 0;
lwdcMod.ChemCode = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
lwdcMod.EPANum = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
lwdcMod.ActiveIngr = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
lwdcMod.Manufacturer = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
//
lwdcMod.retail = (reader[5] != DBNull.Value) ? (decimal)reader[5] : 0;
lwdcMod.cost = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0;
//
lwdcMod.Comment = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
lwdcMod.Units = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
lwdcMod.activeStatus = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
// the close
connection.Close();
// return the Model
return lwdcMod;
}
// Method: Get List using SQL LIKE
public List<lwdom_chemlist_Model> Get_ChemList_LIKEChemCode_List(string _chemCode)
{
// 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 OBJECTID, chemcode, epanum, activeingr, manufacturer, retail, cost, comment, units, activeStatus " +
"FROM lwdom_chemlist " +
"WHERE chemcode LIKE @chemcode " +
"ORDER BY chemcode";
// create List
List<lwdom_chemlist_Model> lwdcMod_List = new List<lwdom_chemlist_Model>();
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
// insert command
command.Parameters.AddWithValue("@chemcode", _chemCode+'%');
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lwdom_chemlist_Model lwdcMod = new lwdom_chemlist_Model();
lwdcMod.OBJECTID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
lwdcMod.ChemCode = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
lwdcMod.EPANum = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
lwdcMod.ActiveIngr = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
lwdcMod.Manufacturer = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
//
lwdcMod.retail = (reader[5] != DBNull.Value) ? (decimal)reader[5] : 0;
lwdcMod.cost = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0;
//
lwdcMod.Comment = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
lwdcMod.Units = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
lwdcMod.activeStatus = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
// add Equipment to List
lwdcMod_List.Add(lwdcMod);
}
// 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_ChemList_usingLike_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return lwdcMod_List;
}
// Reset Equipment Model
private lwdom_chemlist_Model Reset_ChemList_Mod(lwdom_chemlist_Model lwdcMod)
{
// reset the model
lwdcMod.ChemCode = "";
lwdcMod.EPANum = "";
lwdcMod.ActiveIngr = "";
lwdcMod.Manufacturer = "";
lwdcMod.retail = 0;
lwdcMod.cost = 0;
lwdcMod.Comment = "";
lwdcMod.Units = "";
lwdcMod.activeStatus = "";
// return the model
return lwdcMod;
}
// Method: update record
public string Update_ChemList_rec(lwdom_chemlist_Model lwdcMod)
{
// 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_chemlist " +
"SET chemcode=@chemcode, " +
"epanum=@epanum, " +
"activeingr=@activeingr, " +
"manufacturer=@manufacturer, " +
"retail=@retail, " +
"cost=@cost, " +
"comment=@comment, " +
"units=@units, " +
"activeStatus=@activeStatus " +
"WHERE OBJECTID=@OBJECTID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
// update the database
connection.Open();
// use of command.parameters... prevents sql injection
command.Parameters.AddWithValue("@chemcode", lwdcMod.ChemCode);
command.Parameters.AddWithValue("@epanum", lwdcMod.EPANum);
command.Parameters.AddWithValue("@activeingr", lwdcMod.ActiveIngr);
command.Parameters.AddWithValue("@manufacturer", lwdcMod.Manufacturer);
command.Parameters.AddWithValue("@retail", lwdcMod.retail);
command.Parameters.AddWithValue("@cost", lwdcMod.cost);
command.Parameters.AddWithValue("@comment", lwdcMod.Comment);
command.Parameters.AddWithValue("@units", lwdcMod.Units);
command.Parameters.AddWithValue("@activeStatus", lwdcMod.activeStatus);
//
command.Parameters.AddWithValue("@OBJECTID", lwdcMod.OBJECTID); // 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;
}
// ADD
public string Add_ChemList_Rec(lwdom_chemlist_Model lwdcMod)
{
// 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_chemlist (chemcode, epanum, activeingr, manufacturer, retail, cost, comment, units, activeStatus) " +
"VALUES (@chemcode, @epanum, @activeingr, @manufacturer, @retail, @cost, @comment, @units, @activeStatus)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@chemcode", lwdcMod.ChemCode);
command.Parameters.AddWithValue("@epanum", lwdcMod.EPANum);
command.Parameters.AddWithValue("@activeingr", lwdcMod.ActiveIngr);
command.Parameters.AddWithValue("@manufacturer", lwdcMod.Manufacturer);
command.Parameters.AddWithValue("@retail", lwdcMod.retail);
command.Parameters.AddWithValue("@cost", lwdcMod.cost);
command.Parameters.AddWithValue("@comment", lwdcMod.Comment);
command.Parameters.AddWithValue("@units", lwdcMod.Units);
command.Parameters.AddWithValue("@activeStatus", lwdcMod.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;
}