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