// Method: Get List
public List<lwdom_JobType_Model> Get_JobType_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, JobType " +
"FROM lwdom_JobType " +
"ORDER BY JobType";
// create List
List<lwdom_JobType_Model> jtMod_List = new List<lwdom_JobType_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_JobType_Model jtMod = new lwdom_JobType_Model();
jtMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
jtMod.JobType = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
// add Equipment to List
jtMod_List.Add(jtMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "List Complete.";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
}
// return List
return jtMod_List;
}
// Configure Window to Allow ADD
private void menuAddJobType_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_JobType_Model();
ResetDisplayFields();
ADDButtonConfiguration();
labelStatus.Content = "";
}
// ADD Job Type record
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
lwdom_JobType_Model jtMod = new lwdom_JobType_Model();
string strMsg = "";
// load data
jtMod.JobType = txtJobType.Text.Trim();
// add the record
strMsg = JTWkr.Add_JobType_Rec(jtMod);
// display message
labelStatus.Content = strMsg;
ListModelsInGrid();
ResetDisplayFields();
InitialButtonConfiguration();
}
// UPDATE
private void buttonUpdate_Click(object sender, RoutedEventArgs e)
{
lwdom_JobType_Model jtMod = new lwdom_JobType_Model();
string strMsg = "";
jtMod = LoadDataForUpdate();
strMsg = JTWkr.Update_JobType_rec(jtMod);
// message
labelStatus.Content = strMsg;
ListModelsInGrid();
ResetDisplayFields();
InitialButtonConfiguration();
}
// Display data from grid
private void DisplayData(lwdom_JobType_Model jtMod)
{
lblID.Content = jtMod.ID;
txtJobType.Text = jtMod.JobType;
}
// load data for update
private lwdom_JobType_Model LoadDataForUpdate()
{
lwdom_JobType_Model jtMod = new lwdom_JobType_Model();
jtMod.ID = Convert.ToInt64(lblID.Content.ToString());
jtMod.JobType = txtJobType.Text.Trim();
// return model
return jtMod;
}
// Method: get record data based on id
public lwdom_JobType_Model Get_SpecificJobType_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, JobType " +
"FROM lwdom_JobType " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on LW Jobtype Model (jtMod)
lwdom_JobType_Model jtMod = new lwdom_JobType_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())
{
jtMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
jtMod.JobType = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
// the close
connection.Close();
// return the Model
return jtMod;
}
// Method: update record
public string Update_JobType_rec(lwdom_JobType_Model jtMod)
{
// 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_JobType " +
"SET JobType=@JobType " +
"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("@JobType", jtMod.JobType);
//
command.Parameters.AddWithValue("@ID", jtMod.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, "", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// ADD
public string Add_JobType_Rec(lwdom_JobType_Model jtMod)
{
// 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_JobType (JobType) " +
"VALUES (@JobType)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@JobType", jtMod.JobType);
command.ExecuteNonQuery();
strMsg = "Record was added.";
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
connection.Close();
return strMsg;
}
