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