// Method: Get List
public List<lw_InvoiceItems_Model> Get_InvoiceItem_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, InvItemId, InvoiceID, Description, Cost, " +
"InvID, istax " +
"FROM lw_InvoiceItems " +
"ORDER BY InvoiceID";
// create List
List<lw_InvoiceItems_Model> iiMod_List = new List<lw_InvoiceItems_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_InvoiceItems_Model iiMod = new lw_InvoiceItems_Model();
iiMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
iiMod.InvItemId = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0;
iiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
iiMod.Description = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
iiMod.Cost = (reader[4] != DBNull.Value) ? Convert.ToDecimal(reader[4]) : 0;
iiMod.InvID = (reader[5] != DBNull.Value) ? Convert.ToInt64(reader[5]) : 0;
iiMod.istax = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
// add to list
iiMod_List.Add(iiMod);
}
// 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_InvoiceItem_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return iiMod_List;
}
// constructor
public Win_LWInvoiceItems(lw_InvoiceItems_Model iiMod)
{
InitializeComponent();
_iiMod = new lw_InvoiceItems_Model();
_iiMod = iiMod;
// display data
busy_Indicator.IsBusy = false;
uc_InvItemDetail.DataContext = _iiMod;
// LISTENER: CANCEL
uc_InvItemDetail.OnInvItem_CANCEL += (o, e) =>
{
// on a cancle close the window
this.Close();
};
}
// Aysnc Update Method
public void Update_InvoiceItem_Async(lw_InvoiceItems_Model iiMod)
{
// aysnc thread
Task.Run(() =>
{
// update Invoice Item record
return IIWkr.Update_InvoiceItem_rec(iiMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
// Async Add Method
public void Add_InvoiceItem_Async(lw_InvoiceItems_Model iiMod)
{
// async thread
Task.Run(() =>
{
// add Invoice Item record
return IIWkr.Add_InvoiceItem_Rec(iiMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// Reset Invoice Item Model
private lw_InvoiceItems_Model Reset_InvoiceItem_Mod()
{
lw_InvoiceItems_Model iiMod = new lw_InvoiceItems_Model();
// reset the model
iiMod.ID = 0;
iiMod.InvItemId = 0;
iiMod.InvoiceID = "";
iiMod.Description = "";
iiMod.Cost = 0;
iiMod.InvID = 0;
iiMod.istax = "";
// return the model
return iiMod;
}
// Method: update record
public string Update_InvoiceItem_rec(lw_InvoiceItems_Model iiMod)
{
// 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_InvoiceItems " +
"SET InvItemId=@InvItemId, " +
"InvoiceID=@InvoiceID, " +
"Description=@Description, " +
"Cost=@Cost, " +
"InvID=@InvID, " +
"istax=@istax " +
"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("@InvItemId", iiMod.InvItemId);
command.Parameters.AddWithValue("@InvoiceID", iiMod.InvoiceID);
command.Parameters.AddWithValue("@Description", iiMod.Description);
command.Parameters.AddWithValue("@Cost", iiMod.Cost);
command.Parameters.AddWithValue("@InvID", iiMod.InvID);
command.Parameters.AddWithValue("@istax", iiMod.istax);
//
command.Parameters.AddWithValue("@ID", iiMod.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_InvoiceItem_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// ADD
public string Add_InvoiceItem_Rec(lw_InvoiceItems_Model iiMod)
{
// 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_InvoiceItems (InvItemId, InvoiceID, Description, " +
"Cost, InvID, istax) " +
"VALUES (@InvItemId, @InvoiceID, @Description, @Cost, @InvID, @istax)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@InvItemId", iiMod.InvItemId);
command.Parameters.AddWithValue("@InvoiceID", iiMod.InvoiceID);
command.Parameters.AddWithValue("@Description", iiMod.Description);
command.Parameters.AddWithValue("@Cost", iiMod.Cost);
command.Parameters.AddWithValue("@InvID", iiMod.InvID);
command.Parameters.AddWithValue("@istax", iiMod.istax);
command.ExecuteNonQuery();
strMsg = "Record was added.";
}
catch (Exception e)
{
strMsg = e.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Add_InvoiceItem_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// Method: get record data based on id
public lw_InvoiceItems_Model Get_Specific_InvoiceItem_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, InvItemId, InvoiceID, Description, Cost, " +
"InvID, istax " +
"FROM lw_InvoiceItems " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on Equip Model (eMod)
lw_InvoiceItems_Model iiMod = new lw_InvoiceItems_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())
{
iiMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
iiMod.InvItemId = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0;
iiMod.InvoiceID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
iiMod.Description = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
iiMod.Cost = (reader[4] != DBNull.Value) ? Convert.ToDecimal(reader[4]) : 0;
iiMod.InvID = (reader[5] != DBNull.Value) ? Convert.ToInt64(reader[5]) : 0;
iiMod.istax = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_Specific_InvoiceItem_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// the close
connection.Close();
// return the Model
return iiMod;
}
