private void GetSections(bool Recurse) { if (Recurse) { comm.Parameters.Clear(); comm.CommandText = "SELECT * FROM " + Config.TableNames["Sections"] + " WHERE FormID=@ID;"; comm.Parameters.AddWithValue("@ID", InternalTemplate.ID); ds.Clear(); da.Fill(ds); var sectionData = ds.GetResults(); if (sectionData != null) { foreach (DataRow section in sectionData) { Section s = new Section(); s.ID = (int?)section["ID"]; s.Name = (string)section["Name"]; s.Fields = GetFields(s); InternalTemplate.Sections.Add(s); } } } }
private void SaveFields(Section section) { #region Delete DB field entries not in current model comm.Parameters.Clear(); //DELETE the fields that are currently not a part of the model comm.CommandText = "DELETE FROM " + Config.TableNames["Fields"] + " WHERE SectionID=@SectionID"; if (section.Fields.Count > 0) { string retainInQuery = " AND ID NOT IN ("; bool hasExistingID = false; foreach (Field field in section.Fields) { if (field.ID.HasValue) { retainInQuery += field.ID + ","; hasExistingID = true; } } if (hasExistingID) { //remove the last comma and then append a closing parenthesis. comm.CommandText += retainInQuery; comm.CommandText = comm.CommandText.Substring(0, comm.CommandText.Length - 1) + ");"; } comm.Parameters.AddWithValue("@SectionID", InternalTemplate.ID); } try { comm.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(comm.CommandText); throw ex; } #endregion //Cycle through each field and update/insert depending on if it matches existing field IDs for InternalForm form ID #region Field INSERT/UPDATE queries foreach (Field field in section.Fields) { comm.Parameters.Clear(); field.SectionID = section.ID; comm.Parameters.AddWithValue("SectionID", field.SectionID); comm.Parameters.AddWithValue("Label", field.Label); comm.Parameters.AddWithValue("Required", field.Required); comm.Parameters.AddWithValue("SortOrder", field.SortOrder); comm.Parameters.AddWithValue("Type", field.Type); if (field.ID.HasValue) { comm.CommandText = "UPDATE " + Config.TableNames["Fields"] + " SET SectionID=@SectionID,Label=@Label," + "Required=@Required,SortOrder=@SortOrder,Type=@Type WHERE ID=@ID"; comm.Parameters.AddWithValue("ID", field.ID); try { comm.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } } else { comm.CommandText = "INSERT INTO " + Config.TableNames["Fields"] + " (SectionID, Label, Required, SortOrder, Type) " + "VALUES (@SectionID, @Label, @Required, @SortOrder, @Type); SELECT SCOPE_IDENTITY();"; try { field.ID = Convert.ToInt32(comm.ExecuteScalar()); } catch (SqlException ex) { throw ex; } } SaveOptions(field); } #endregion }
/// <summary> /// /// </summary> /// <param name="comm">The SqlCommand currently being used</param> private List<Field> GetFields(Section section) { DataSet fieldDS = new DataSet(); List<Field> Fields = new List<Field>(); comm.Parameters.Clear(); comm.CommandText = "SELECT * FROM " + Config.TableNames["Fields"] + " WHERE SectionID=@SectionID ORDER BY SortOrder"; comm.Parameters.AddWithValue("@SectionID", section.ID); try { da.Fill(fieldDS); var fieldsData = fieldDS.GetResults(); if (fieldsData != null) { foreach (DataRow field in fieldsData) { try { Field newField = new Field(); newField.SectionID = section.ID; newField.ID = (int)field["ID"]; newField.Label = (string)field["Label"]; newField.Required = (bool)field["Required"]; newField.SortOrder = (int)field["SortOrder"]; newField.Type = (string)field["Type"]; newField.Options = GetOptions(newField); if (newField.Type == "select" || newField.Type == "radio" || newField.Type == "checkbox") { foreach (Option opt in newField.Options) { newField.Values.Add(new Value() { OptionID = opt.ID, FieldID = newField.ID }); } } else { newField.Values.Add(new Value() { FieldID = newField.ID }); } //If the mode requires field values, then get the values Fields.Add(newField); } catch (FormValidationException ex) { //InternalForm._IsValid = false; //InternalForm._InvalidFields.Add(ex.InvalidField); } catch (Exception ex) { throw ex; } } } } catch (FormValidationException ex) { //InternalForm._IsValid = false; //InternalForm._InvalidFields.Add(ex.InvalidField); } catch (Exception ex) { throw new Exception("Error in getFieldsStructure(): " + ex.Message); } return Fields; }