public void UpdateDataTables()
{
ContentsVM.UpdateDataList(SearchBar);
TransactionVM.UpdateDataList(SearchBar);
NewTransactionVM.UpdateDataList(SearchBar);
string commandText = "SELECT DISTINCT [Job number] FROM [Sheet1$] " +
"";
JobNumbers = DbContext.GetDataTable(commandText, searchBar);
}
public int ExecuteScalar(string commandText, ContentsVM vm)
{
using (OleDbCommand cmd = new OleDbCommand()) {
cmd.Parameters.AddWithValue("partNumber", vm.PartNumber);
cmd.Parameters.AddWithValue("quantity", vm.Quantity);
cmd.Parameters.AddWithValue("condition", vm.Condition);
cmd.CommandText = commandText;
using (OleDbConnection conn = new OleDbConnection(GetConnectionString())) {
cmd.Connection = conn;
conn.Open();
int integer = (int)cmd.ExecuteScalar();
return(integer);
}
}
}
public NewTransactionVM(DbContext dbContext, ContentsVM contentsVM)
{
//NOTE: newTransactionsVM is useless without ContentsVM,
//SO it should be passed a ContentsVM!
//BETTER THIS WAY, coz it looks after itself!
DbContext = dbContext;
ContentsVM = contentsVM;
CurrentSelection = 0;
CommandText = "SELECT [Part number], Manufacturer, Description, SUM(Quant) AS Quant, Condition, Shelf FROM [Sheet1$] " +
"WHERE FALSE " +
"GROUP BY [Part number], Manufacturer, Description, Condition, Shelf " +
"";
DbContext.GetDataTable(CommandText);
//Subscribe
ContentsVM.PropertyChanged += ContentsVM_PropertyChanged;
}
//public ICollection<T> GetCollection<T>(string commandText, string searchBar) {
// ICollection<T> collection;
// using (OleDbCommand cmd = new OleDbCommand()) {
// OleDbParameter searchBarParam =
//new OleDbParameter("searchBar", "%" + searchBar + "%");//Note; can specify data type if want to.
// cmd.Parameters.Add(searchBarParam);
// cmd.CommandText = commandText; //@ not working!
// using (OleDbConnection conn = new OleDbConnection(GetConnectionString())) {
// cmd.Connection = conn;
// conn.Open();
// OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
// //NOTE: adapter is for linking DataSet (returned from query?) to DataSource (the DataTable that bind to?)
// //Not sure about this though.
// DataTable table = new DataTable();
// }
// }
// return collection;
//}
//NOTE: when wondering if should use close or dispose on connection:
//SHOULD use dispose, unless CERTAIN the SAME instance of the connection will be opened again.
//BECAUSE it is IDisposable, so NEED to call dispose on it...
//AND it is better to use "using", because it is syntactic sugar for a try and finally block.
//(WHICH is nec because a connection COULD cause an exception, if SQL is down, and crash your program).
//NOTE: DOES it actually crash though? Or just not do what you asked it to do?
//SO THERE! so, for each user ACTION, should be wrapped in a USING block. Fair?
//DO NOT just leave the connection in RAM, JUST because program still running.
//BECAUSE it HAS unmanaged resources, that might not be closed for AGES otherwise (don't get this, but ok, accept it.
//ENOUGH PEOPLE have suggested wrapping it in using. SO i am happy.
//Takes NO EFFORT to instantiate a connection. So do it.
//AND I've seen enough examples, of 3 tier using statements. Conn, command, reader. so do it!
public DataTable GetDataTable(string commandText, ContentsVM vm)
{
using (OleDbCommand cmd = new OleDbCommand()) {
cmd.Parameters.AddWithValue("partNumber", vm.PartNumber);
cmd.Parameters.AddWithValue("condition", vm.Condition);
cmd.CommandText = commandText; //@ not working!
using (OleDbConnection conn = new OleDbConnection(GetConnectionString())) {
cmd.Connection = conn;
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
//NOTE: adapter is for linking DataSet (returned from query?) to DataSource (the DataTable that bind to?)
//Not sure about this though.
DataTable table = new DataTable();
adapter.Fill(table);
return(table);
}
}
}
public void Search()
{
ContentsVM.Search(SearchBar);
TransactionVM.Search(SearchBar);
}
