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