public List <ReoccuringOrderTbl> GetAll(bool pOnlyEnabled, string SortBy) { List <ReoccuringOrderTbl> _DataItems = new List <ReoccuringOrderTbl>(); // string _connectionStr = CONST_CONSTRING; TrackerDb _TDB = new TrackerDb(); //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ string _sqlCmd = CONST_SQL_SELECT; if (pOnlyEnabled) { _sqlCmd += " WHERE ([Enabled] = true) "; // add where if required } if (!String.IsNullOrEmpty(SortBy)) { _sqlCmd += " ORDER BY " + SortBy; // Add order by string } //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built //_conn.Open(); //OleDbDataReader _DataReader = _cmd.ExecuteReader(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl(); _DataItem.ID = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]); _DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]); _DataItem.ReoccuranceType = (_DataReader["ReoccuranceType"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReoccuranceType"]); _DataItem.ReoccuranceValue = (_DataReader["Value"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Value"]); _DataItem.ItemRequiredID = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]); _DataItem.QtyRequired = (_DataReader["QtyRequired"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["QtyRequired"]); _DataItem.DateLastDone = (_DataReader["DateLastDone"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["DateLastDone"]); _DataItem.NextDateRequired = (_DataReader["NextDateRequired"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextDateRequired"]); _DataItem.RequireUntilDate = (_DataReader["RequireUntilDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["RequireUntilDate"]); _DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); _DataItems.Add(_DataItem); } _DataReader.Close(); _TDB.Close(); // } return(_DataItems); }
public bool SetReoccuringItemsLastDate() { bool _Success = false; //string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString; ; List <ReoccuringOrderTbl> _ReoccuringOrderItems = new List <ReoccuringOrderTbl>(); TrackerDb _TDB = new TrackerDb(); //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ string _sqlCmd = CONST_SQL_GETITEMSLASTDATE; // not needed if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy; // Add order by string //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built //_conn.Open(); // read all the data from the query and post it into the DataList //OleDbDataReader _DataReader = _cmd.ExecuteReader(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl(); _DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]); _DataItem.ItemRequiredID = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]); _DataItem.DateLastDone = (_DataReader["LastDatePerItem"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastDatePerItem"]); _ReoccuringOrderItems.Add(_DataItem); } // while //now update the table //_conn.Close(); //_conn.Dispose(); //_cmd.Dispose(); _DataReader.Close(); _TDB.Close(); //} for (int i = 0; i < _ReoccuringOrderItems.Count; i++) { //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ _TDB = new TrackerDb(); //OleDbCommand _cmd = new OleDbCommand(CONST_UPDATE_ITEMSLASTDATE, _conn); #region Parameters // Add data SET data DateLastDone //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].DateLastDone }); _TDB.AddParams(_ReoccuringOrderItems[i].DateLastDone, DbType.Date); // Where data //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].CustomerID }); //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].ItemRequiredID }); _TDB.AddWhereParams(_ReoccuringOrderItems[i].CustomerID, DbType.Int32); _TDB.AddWhereParams(_ReoccuringOrderItems[i].ItemRequiredID, DbType.Int32); #endregion _Success = _TDB.ExecuteNonQuerySQL(CONST_UPDATE_ITEMSLASTDATE) == string.Empty; //try //{ // _conn.Open(); // _Success = (_cmd.ExecuteNonQuery() >= 0); //} //catch (OleDbException oleErr) //{ _Success = oleErr.ErrorCode != 0; } //finally //{ // _conn.Close(); // _conn.Dispose(); // _cmd.Dispose(); //} _TDB.Close(); } return(_Success); }