コード例 #1
0
        public List <ReoccuringOrderTbl> GetAll(bool pOnlyEnabled, string SortBy)
        {
            List <ReoccuringOrderTbl> _DataItems = new List <ReoccuringOrderTbl>();
            string _connectionStr = CONST_CONSTRING;

            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();
                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);
                }
            }
            return(_DataItems);
        }
コード例 #2
0
        public bool SetReoccuringItemsLastDate()
        {
            bool   _Success       = false;
            string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString;;
            List <ReoccuringOrderTbl> _ReoccuringOrderItems = new List <ReoccuringOrderTbl>();

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

            for (int i = 0; i < _ReoccuringOrderItems.Count; i++)
            {
                using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
                {
                    OleDbCommand _cmd = new OleDbCommand(CONST_UPDATE_ITEMSLASTDATE, _conn);
                    #region Parameters
                    // Add data SET data DateLastDone
                    _cmd.Parameters.Add(new OleDbParameter {
                        Value = _ReoccuringOrderItems[i].DateLastDone
                    });
                    // Where data
                    _cmd.Parameters.Add(new OleDbParameter {
                        Value = _ReoccuringOrderItems[i].CustomerID
                    });
                    _cmd.Parameters.Add(new OleDbParameter {
                        Value = _ReoccuringOrderItems[i].ItemRequiredID
                    });
                    #endregion
                    try
                    {
                        _conn.Open();
                        _Success = (_cmd.ExecuteNonQuery() >= 0);
                    }
                    catch (OleDbException oleErr)
                    { _Success = oleErr.ErrorCode != 0; }
                    finally
                    {
                        _conn.Close();
                        _conn.Dispose();
                        _cmd.Dispose();
                    }
                }
            }

            return(_Success);
        }