private void insertPossibleValue(SqlConnection con, Position positon, PositionValue positonValue)
{
if (positonValue != null &&
!positonValue.getValue().Equals(""))
{
// try to find Value in DB
int newCharacteristicValueId = searchCharacteristicPossibleValue(con, positon, positonValue);
if (newCharacteristicValueId == -1)
{ // not found -> insert it
newCharacteristicValueId = getLastCharacteristicPossibleValueId(con, positon) + 1;
String sqlInsert = String.Format("INSERT INTO CharacteristicsValues (Id, CharacteristicsNameId, Value) VALUES ({0}, {1}, '{2}')", newCharacteristicValueId, positon.getId(), positonValue.getValue());
SqlCommand command = new SqlCommand(sqlInsert, con);
int numberOfinserted = command.ExecuteNonQuery();
}
// set inserted row Id to CharacteristicValue
positonValue.setId(newCharacteristicValueId);
}
}
private int searchCharacteristicPossibleValue(SqlConnection con, Position positon, PositionValue positonValue)
{
String sqlSelect = String.Format("SELECT Id FROM CharacteristicsValues WHERE CharacteristicsNameId = {0} AND Value = '{1}'", positon.getId(), positonValue.getValue());
SqlCommand command = new SqlCommand(sqlSelect, con);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
return(reader.GetInt32(0)); //id
}
return(-1); // not found in Db
}