// 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);
}