Example #1
0
        public List <ItemUsageTbl> GetAllItemsUsed(long pCustomerID, string SortBy)
        {
            List <ItemUsageTbl> _DataItems = new List <ItemUsageTbl>();
            string _connectionStr          = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;

            using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
            {
                string _sqlCmd = CONST_SQL_SELECT + " WHERE CustomerID = " + pCustomerID.ToString();
                if (!String.IsNullOrEmpty(SortBy))
                {
                    _sqlCmd += " ORDER BY " + SortBy;                            // Add order by string
                }
                OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);            // run the query we have built
                _conn.Open();
                OleDbDataReader _DataReader = _cmd.ExecuteReader();
                while (_DataReader.Read())
                {
                    ItemUsageTbl _DataItem = new ItemUsageTbl();

                    _DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
                    _DataItem.CustomerID        = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
                    _DataItem.Date           = (_DataReader["Date"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["Date"]);
                    _DataItem.ItemProvided   = (_DataReader["ItemProvided"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemProvided"]);
                    _DataItem.AmountProvided = (_DataReader["AmountProvided"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["AmountProvided"]);
                    _DataItem.PrepTypeID     = (_DataReader["PrepTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PrepTypeID"]);
                    _DataItem.PackagingID    = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PackagingID"]);
                    _DataItem.Notes          = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItems.Add(_DataItem);
                }
            }
            return(_DataItems);
        }
Example #2
0
        public string InsertItemsUsed(ItemUsageTbl ItemUsageLine)
        {
            string errString      = "";
            string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;

            using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
            {
                string _sqlCmd = CONST_SQL_INSERT;

                OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
                #region Parameters
                // Add data sent
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.CustomerID
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.Date
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.ItemProvided
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.AmountProvided
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.PrepTypeID
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.PackagingID
                });
                _cmd.Parameters.Add(new OleDbParameter {
                    Value = ItemUsageLine.Notes
                });
                /// -> auto generater _cmd.Parameters.Add(new OleDbParameter { Value = pClientUsageLineNo });
                #endregion
                try
                {
                    _conn.Open();
                    if (_cmd.ExecuteNonQuery() != 0)
                    {
                        errString = "";
                    }
                    else
                    {
                        errString += " - error ";
                    }
                }
                catch (OleDbException oleErr)
                {
                    // Handle exception.
                    errString += " ERROR: " + oleErr.Message;
                }
                finally
                {
                    _conn.Close();
                }
                return(errString);
            }
        }