// Method: Get List
public List<lwdom_StateName_Model> Get_StateNames_List()
{
// building the connection string
// get the provider, activeStatus database name, and path
connectionString = PSWkr.G_SQLDatabaseConnectionString;
string strMsg = "";
// create needed objects
SqlConnection connection = new SqlConnection(connectionString);
// building sql command, chemcode is the Key
string sqlStatement = "SELECT ID, StateName " +
"FROM lwdom_StateName " +
"ORDER BY StateName";
// create List
List<lwdom_StateName_Model> dsnMod_List = new List<lwdom_StateName_Model>();
try
{
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();
dsnMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
dsnMod.StateName = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
// add Equipment to List
dsnMod_List.Add(dsnMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "List Complete.";
}
catch (Exception errMsg)
{
connection.Close();
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_StateNames_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return dsnMod_List;
}
// load data model with data from window
private lwdom_StateName_Model Load_StateName_Model()
{
int _id = 0;
bool result = false;
lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();
result = int.TryParse(lblID.Content.ToString(), out _id);
dsnMod.ID = (result == true) ? _id : 0;
dsnMod.StateName = txtStateName.Text.Trim();
// return model
return dsnMod;
}
// display the data
private void DisplayData(lwdom_StateName_Model dsnMod)
{
lblID.Content = dsnMod.ID.ToString();
txtStateName.Text = dsnMod.StateName;
}
// UPDATE Record
private void buttonUpdate_Click(object sender, RoutedEventArgs e)
{
string strMsg = "";
lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();
// loads model with data from view
dsnMod = Load_StateName_Model();
// add the record
strMsg = DSNWkr.Update_StateName_rec(dsnMod);
// display the message
labelStatus.Content = strMsg;
// list data
ListModels_InGrid();
ResetDisplayFields();
InitialButtonConfiguration();
}
// ADD Record
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
string strMsg = "";
lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();
// loads model with data from view
dsnMod = Load_StateName_Model();
// add the record
if (dsnMod.StateName.Trim() != null)
{
strMsg = DSNWkr.Add_StateName_Rec(dsnMod);
}
else
{
strMsg = "ADD Canceled. No data entered.";
MessageBox.Show(strMsg, "Rate Name", MessageBoxButton.OK, MessageBoxImage.Information);
}
// display the message
labelStatus.Content = strMsg;
// list data
ListModels_InGrid();
ResetDisplayFields();
InitialButtonConfiguration();
}
// Method: get record data based on id
public lwdom_StateName_Model Get_SpecificStateName_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, StateName " +
"FROM lwdom_StateName " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on LW Rates Model (lwrMod)
lwdom_StateName_Model dsnMod = new lwdom_StateName_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())
{
dsnMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
dsnMod.StateName = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
}
// the close
reader.Close();
}
catch (Exception errMsg)
{
connection.Close();
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_SpecificStateName_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// the close
connection.Close();
// return the Model
return dsnMod;
}
// Reset state name Model
private lwdom_StateName_Model Reset_StateName_Model()
{
lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();
// reset the model
dsnMod.ID = 0;
dsnMod.StateName = "";
// return the model
return dsnMod;
}
// Method: update record
public string Update_StateName_rec( lwdom_StateName_Model dsnMod)
{
// 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_StateName " +
"SET StateName=@StateName " +
"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("@StateName", dsnMod.StateName);
//
command.Parameters.AddWithValue("@ID", dsnMod.ID); // must be in the order of the sqlstatement
command.ExecuteNonQuery();
connection.Close();
strMsg = "Record was updated.";
}
catch (Exception err)
{
strMsg = err.Message.ToString();
connection.Close();
System.Windows.MessageBox.Show(strMsg, "Method: Update_StateName_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
return strMsg;
}
// ADD
public string Add_StateName_Rec(lwdom_StateName_Model dsnMod)
{
// 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_StateName (StateName) " +
"VALUES (@StateName)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@StateName", dsnMod.StateName);
command.ExecuteNonQuery();
connection.Close();
strMsg = "Record was added.";
}
catch (Exception err)
{
strMsg = err.Message.ToString();
connection.Close();
System.Windows.MessageBox.Show(strMsg, "Method: Add_StateName_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
return strMsg;
}