public List<DesignOption> getExistingDesignOptions(String projectName)
{
List<DesignOption> results = new List<DesignOption>();
using (OleDbConnection conn = this.getConnection())
{
conn.Open();
using (OleDbCommand comm = conn.CreateCommand())
{
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT OptionName, Description FROM DesignOption " +
"WHERE ProjectID = (Select ID from Project WHERE ProjectName = ?)";
comm.Parameters.AddWithValue("@name", projectName);
using (OleDbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
DesignOption opt = new DesignOption();
opt.Name = reader["OptionName"].ToString();
opt.Description = reader["Description"].ToString();
results.Add(opt);
}
}
}
conn.Close();
}
return results;
}
private int getDesignOptionID(DesignOption designOp, String projectName)
{
int id = -1;
using (OleDbConnection conn = this.getConnection())
{
conn.Open();
using (OleDbCommand comm = conn.CreateCommand())
{
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT ID FROM DesignOption WHERE OptionName = ? and " +
"ProjectID = (SELECT ID FROM Project where Projectname = ?);";
comm.Parameters.AddWithValue("@optName", designOp.Name.Trim());
comm.Parameters.AddWithValue("@pName", projectName.Trim());
using (OleDbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
id = (int)reader["ID"];
}
}
conn.Close();
}
return id;
}
public List<BuildingComponent> getDesignOptionComponents(DesignOption dOpt)
{
List<BuildingComponent> results = new List<BuildingComponent>();
using (OleDbConnection conn = this.getConnection())
{
conn.Open();
using (OleDbCommand comm = conn.CreateCommand())
{
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT ComponentName, Description FROM BuildingComponent WHERE OptionID =" +
" (SELECT id FROM DesignOption WHERE OptionName = ?);";
comm.Parameters.AddWithValue("@name", dOpt.Name);
using (OleDbDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
BuildingComponent bComp = new BuildingComponent();
bComp.Name = reader["ComponentName"].ToString();
bComp.Description = reader["Description"].ToString();
results.Add(bComp);
}
}
}
conn.Close();
}
return results;
}
public Boolean saveDesignOption(DesignOption designOp, String projectName)
{
Boolean saved = false;
using (OleDbConnection conn = this.getConnection())
{
int DesignOptID = -1;
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
using (OleDbCommand comm = conn.CreateCommand())
{
DesignOptID = this.getDesignOptionID(designOp, projectName);
ClearCommandData(comm);
//No design option found. Add a new one
if (DesignOptID < 0)
{
comm.CommandText = "INSERT INTO DesignOption (OptionName, Description, ProjectID)" +
" values (?, ?, (Select ID FROM Project WHERE Project Name = ?));";
comm.Parameters.AddWithValue("@optName", designOp.Name);
comm.Parameters.AddWithValue("@desc", designOp.Description);
comm.Parameters.AddWithValue("@pName", projectName);
comm.ExecuteNonQuery();
}
}
//DesignOption inserted.
trans.Commit();
}
using (OleDbTransaction trans = conn.BeginTransaction())
{
DesignOptID = this.getDesignOptionID(designOp, projectName);
using (OleDbCommand comm = conn.CreateCommand())
{
comm.CommandType = CommandType.Text;
for (int i = 0; i < designOp.Components.Count(); i++)
{
BuildingComponent bComp = designOp.Components[i];
ClearCommandData(comm);
if (bComp.ToDelete)
{
comm.CommandText = "DELETE FROM BuildingComponent WHERE ComponentName = ?";
comm.Parameters.AddWithValue("@name", bComp.Name);
comm.ExecuteNonQuery();
}
else
{
comm.CommandText = "UPDATE BuildingComponent SET Description = ? WHERE ID =" +
"(SELECT ID FROM BuildingComponent WHERE ComponentName = ?);";
int updated = comm.ExecuteNonQuery();
//component not found; add it.
if (updated == 0)
{
ClearCommandData(comm);
comm.CommandText = "INSERT INTO BuildingComponent (ComponentName, Description, OptionID) " +
"values (?, ?, ?);";
comm.Parameters.AddWithValue("@name", bComp.Name);
comm.Parameters.AddWithValue("@desc", bComp.Description);
comm.Parameters.AddWithValue("@name", DesignOptID);
comm.ExecuteNonQuery();
}
}
}
}
trans.Commit();
saved = true;
}
conn.Close();
}
return saved;
}