// Method: Get List
public List<lw_InvoiceExpenseItems_Model> Get_InvExpensItems_List(ref string strMsg)
{
// 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, WOID, InvoiceID, Category, ExpName, " +
"Rate, Cost, TotHours " +
"FROM lw_InvoiceExpenseItems " +
"ORDER BY InvoiceID, Category";
// create List
List<lw_InvoiceExpenseItems_Model> ieiMod_List = new List<lw_InvoiceExpenseItems_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())
{
lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_Model();
ieiMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0;
ieiMod.WOID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
ieiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
ieiMod.Category = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
ieiMod.ExpName = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
ieiMod.Rate = (reader[5] != DBNull.Value) ? Convert.ToDecimal(reader[5]) : 0;
ieiMod.Cost = (reader[6] != DBNull.Value) ? Convert.ToDecimal(reader[6]) : 0;
ieiMod.TotHours = (reader[7] != DBNull.Value) ? Convert.ToInt16(reader[7]) : 0;
// add Equipment to List
ieiMod_List.Add(ieiMod);
}
// 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_InvExpensItems_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return ieiMod_List;
}
// Aysnc Update Method
public void Update_InvExpItems_Async(lw_InvoiceExpenseItems_Model ieiMod)
{
// aysnc thread
Task.Run(() =>
{
// update Invoice Item record
return IEIWkr.Update_InvExpensItems_rec(ieiMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
// Async Add Method
public void Add_InvExpItems_Async(lw_InvoiceExpenseItems_Model ieiMod)
{
// async thread
Task.Run(() =>
{
// add Invoice Item record
return IEIWkr.Add_InvExpensItems_Rec(ieiMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// ADD Enable - with all blank fields
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_InvoiceExpenseItems_Model();
// reset and add configuration
ResetDisplayFields();
ADDButtonConfiguration();
// Add enabled event
if (OnInvExpItems_ADDEnable_Blank != null) OnInvExpItems_ADDEnable_Blank(sender, new RoutedEventArgs());
}
// UPDATE
private void buttonUpdate_Click(object sender, RoutedEventArgs e)
{
lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_Model();
// load the model
ieiMod = (lw_InvoiceExpenseItems_Model)this.DataContext;
// MVVM async Update
iieVM.Update_InvExpItems_Async(ieiMod);
// reset fields
ResetDisplayFields();
// use inital button configuration
InitialButtonConfiguration();
}
// Method: get record data based on id
public lw_InvoiceExpenseItems_Model Get_SpecificInvExpensItem_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, WOID, InvoiceID, Category, ExpName, " +
"Rate, Cost, TotHours " +
"FROM lw_InvoiceExpenseItems " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on model
lw_InvoiceExpenseItems_Model ieiMod = new lw_InvoiceExpenseItems_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())
{
ieiMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0;
ieiMod.WOID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
ieiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
ieiMod.Category = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
ieiMod.ExpName = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
ieiMod.Rate = (reader[5] != DBNull.Value) ? Convert.ToDecimal(reader[5]) : 0;
ieiMod.Cost = (reader[6] != DBNull.Value) ? Convert.ToDecimal(reader[6]) : 0;
ieiMod.TotHours = (reader[7] != DBNull.Value) ? Convert.ToInt16(reader[7]) : 0;
}
// the close
reader.Close();
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_SpecificInvExpensItem_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// the close
connection.Close();
// return the Model
return ieiMod;
}
// Method: update record
public string Update_InvExpensItems_rec(lw_InvoiceExpenseItems_Model ieiMod)
{
// 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_InvoiceExpenseItems " +
"SET WOID=@WOID, " +
"InvoiceID=@InvoiceID, " +
"Category=@Category, " +
"ExpName=@ExpName, " +
"Rate=@Rate, " +
"Cost=@Cost, " +
"TotHours=@TotHours " +
"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("@WOID", ieiMod.WOID);
command.Parameters.AddWithValue("@InvoiceID", ieiMod.InvoiceID);
command.Parameters.AddWithValue("@Category", ieiMod.Category);
command.Parameters.AddWithValue("@ExpName", ieiMod.ExpName);
command.Parameters.AddWithValue("@Rate", ieiMod.Rate);
command.Parameters.AddWithValue("@Cost", ieiMod.Cost);
command.Parameters.AddWithValue("@TotHours", ieiMod.TotHours);
//
command.Parameters.AddWithValue("@ID", ieiMod.ID); // must be in the order of the sqlstatement
command.ExecuteNonQuery();
strMsg = "Record was updated.";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Update_InvExpensItems_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// ADD
public string Add_InvExpensItems_Rec(lw_InvoiceExpenseItems_Model ieiMod)
{
// 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_InvoiceExpenseItems (WOID, InvoiceID, " +
"Category, ExpName, Rate, Cost, TotHours) " +
"VALUES (@WOID, @InvoiceID, @Category, @ExpName, @Rate, @Cost, @TotHours)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@WOID", ieiMod.WOID);
command.Parameters.AddWithValue("@InvoiceID", ieiMod.InvoiceID);
command.Parameters.AddWithValue("@Category", ieiMod.Category);
command.Parameters.AddWithValue("@ExpName", ieiMod.ExpName);
command.Parameters.AddWithValue("@Rate", ieiMod.Rate);
command.Parameters.AddWithValue("@Cost", ieiMod.Cost);
command.Parameters.AddWithValue("@TotHours", ieiMod.TotHours);
command.ExecuteNonQuery();
strMsg = "Record was added.";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Add_InvExpensItems_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}