/// <summary>
/// Adapter function to get data from DB and load it into a From object.
/// </summary>
/// <param name="ID">ID of the form to get.</param>
/// <param name="Recursive">Defaults to true. If set to false, only gets the data for the form and nothing further (IE the sections).</param>
public Template GetTemplate(int ID, bool Recursive = true)
{
ClearData();
DataSet templateDS = new DataSet();
InternalTemplate = new Template();
comm.CommandText = "SELECT * FROM " + Config.TableNames["Templates"] + " WHERE ID=@ID;";
comm.Parameters.AddWithValue("@ID", ID);
try
{
da.Fill(templateDS);
var formMeta = templateDS.GetResults();
if (formMeta != null)
{
InternalTemplate.ID = ID;
InternalTemplate.Name = (string)formMeta[0]["Name"];
InternalTemplate.Description = (string)formMeta[0]["Description"];
InternalTemplate.Sections = new List<Section>();
if (Recursive)
{
GetSections(Recursive);
}
}
else
{
throw new Exception("No results found");
}
}
catch (Exception ex)
{
throw new Exception("Error in getFormStructure(): " + ex.Message);
}
//Close dat connection. No longer needed, bro!
conn.Close();
return InternalTemplate;
}
public TemplateCollection GetTemplates(int Number = 10)
{
DataSet templatesDS = new DataSet();
InternalTemplate = new Template();
TemplateCollection Forms = new TemplateCollection();
comm.CommandText = "SELECT TOP(@num) ID FROM " + Config.TableNames["Templates"];
comm.Parameters.AddWithValue("@num", Number);
try
{
da.Fill(templatesDS);
}
catch (Exception ex)
{
throw new Exception("Error trying to open database connection in GetTopForms: " + ex.Message, ex);
}
var ids = templatesDS.GetResults();
if (ids != null)
{
foreach (DataRow id in ids)
{
Template formRef = GetTemplate((int)id["ID"]);
Forms.Add(formRef);
}
}
else
{
//throw new Exception("No results found");
return null;
}
return Forms;
}
/// <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;
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
private List<Option> GetOptions(Field field)
{
DataSet optionDS = new DataSet();
comm.Parameters.Clear();
List<Option> Options = new List<Option>();
comm.CommandText = "SELECT * FROM " + Config.TableNames["Options"] + " WHERE FieldID=@FieldID";
comm.Parameters.AddWithValue("@FieldID", field.ID);
try
{
da.Fill(optionDS);
var optionData = optionDS.GetResults();
if (optionData != null)
{
foreach (DataRow option in optionData)
{
Option NewOption = new Option(option);
NewOption.FieldID = field.ID;
NewOption.ID = (int?)option["ID"];
NewOption.SortOrder = (int)option["SortOrder"];
NewOption.Value = (string)option["Value"];
Options.Add(NewOption);
}
}
}
catch (FormValidationException vEx)
{
//InternalForm._IsValid = false;
//InternalForm._InvalidFields.Add(vEx.InvalidField);
}
catch (Exception ex)
{
throw new Exception("Error in getOptionsStructure(): " + ex.Message);
}
return Options;
}
private void LoadValues()
{
ClearData();
DataSet ValueDS = new DataSet();
comm.CommandText = "SELECT * FROM " + Config.TableNames["Values"] + " WHERE FormID=@FormID";
comm.Parameters.AddWithValue("@FormID", InternalForm.ID);
da.Fill(ValueDS);
var valuesData = ValueDS.GetResults();
if (valuesData != null)
{
foreach (DataRow value in valuesData)
{
var FieldID = value["FieldID"];
Value v = new Value
{
ID = (int)value["ID"],
FormID = (int)value["FormID"],
OptionID = (int)value["OptionID"],
FieldID = (int)value["FieldID"],
Content = (string)value["Content"]
};
//Find the section that contains the field id currently being processed, then get the field that the ID matches
Field RelatedField = InternalForm.Sections.Where(sec => sec.Fields.Where(field => field.ID == v.FieldID).Count() >= 1).First().Fields.First(field => field.ID == v.FieldID);
//Find the specific Value that's been set up already and load the DB information into it.
Value RelatedValue = RelatedField.Values.First(val => val.FieldID == v.FieldID);
RelatedValue.FormID = v.FormID;
RelatedValue.FieldID = v.FieldID;
RelatedValue.OptionID = v.OptionID;
RelatedValue.Content = v.Content;
}
}
}
