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