public bool InsertCommodity(ref Commodity comm)
{
bool retVal = false;
string sqlQuery = "INSERT INTO Commodities (";
StringBuilder sbColDefs = new StringBuilder();
StringBuilder sbColValues = new StringBuilder();
foreach (ColumnDefinition columnDef in comm.ColumnDefs())
{
if (!columnDef.ColumnName.Equals("InventoryID"))
{
if (sbColDefs.Length > 0)
{
sbColDefs.Append(", ");
sbColValues.Append(", ");
}
sbColDefs.Append(columnDef.ColumnName);
sbColValues.Append(comm.GetInsertableValue(columnDef));
}
}
sqlQuery += sbColDefs.ToString() + ") VALUES (" + sbColValues.ToString() + ")";
try
{
OleDbCommand sqlCommand = new OleDbCommand(sqlQuery, _accessConnection);
int result = sqlCommand.ExecuteNonQuery();
if (result == 1)
{
OleDbCommand identityCommand = new OleDbCommand("SELECT @@IDENTITY", _accessConnection);
int newMaxNumKey = (int)identityCommand.ExecuteScalar();
comm.InventoryID = newMaxNumKey;
retVal = true;
}
}
catch (Exception e)
{
//TODO: how to handle this?
}
return retVal;
}
public int UpdateCommodity(Commodity comm)
{
int result = 0;
string sqlQuery = "UPDATE Commodities SET ";
StringBuilder sbColValues = new StringBuilder();
StringBuilder sbWhereClause = new StringBuilder();
foreach (KeyValuePair<int, object> columnValue in comm.GetColumnValues())
{
ColumnDefinition columnDef = null;
foreach (ColumnDefinition colDef in comm.ColumnDefs())
{
if (colDef.ColumnIndex == columnValue.Key)
{
columnDef = colDef;
break;
}
}
if (columnDef == null)
{
//TODO: LOG THIS!
return result;
}
if (!columnDef.ColumnName.Equals("InventoryID"))
{
if (sbColValues.Length > 0)
{
sbColValues.Append(", ");
}
sbColValues.Append(columnDef.ColumnName + " = " + comm.GetInsertableValue(columnDef));
}
else
{
sbWhereClause.Append(" WHERE " + columnDef.ColumnName + " = " + comm.GetInsertableValue(columnDef));
}
}
if (sbColValues.Length > 0 && sbWhereClause.Length > 0)
{
try
{
sqlQuery += sbColValues.ToString() + sbWhereClause.ToString();
OleDbCommand sqlCommand = new OleDbCommand(sqlQuery, _accessConnection);
result = sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
//TODO: how to handle this?
}
}
return result;
}