private void SaveOptions(Field field)
{
if ((field.Type == "select" || field.Type == "checkbox" || field.Type == "radio" || field.Type == "text") && field.Options.Count > 0)
{
this.ClearData();
if (field.Options.Count > 0)
{
string retainInQuery = " AND ID NOT IN (";
bool hasExistingID = false;
comm.CommandText = "DELETE FROM " + Config.TableNames["Options"] + " WHERE FieldID=@FieldID";
foreach (Option option in field.Options)
{
//TODO: Parameterize InternalForm
if (option.ID.HasValue)
{
retainInQuery += option.ID + ",";
hasExistingID = true;
}
}
if (hasExistingID)
{
comm.CommandText += retainInQuery;
comm.CommandText = comm.CommandText.Substring(0, comm.CommandText.Length - 1) + ");";
}
comm.Parameters.AddWithValue("@FieldID", field.ID);
}
try
{
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
//Cycle through each option and update/insert depending on if it matches existing option IDs for InternalForm field ID
foreach (Option option in field.Options)
{
option.FieldID = field.ID;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("FieldID", field.ID);
comm.Parameters.AddWithValue("Value", option.Value);
comm.Parameters.AddWithValue("SortOrder", option.SortOrder);
if (option.ID.HasValue)
{
comm.CommandText = "UPDATE " + Config.TableNames["Options"] + " SET " +
" FieldID=@FieldID," +
" Value=@Value," +
" SortOrder=@SortOrder" +
" WHERE ID=@ID";
comm.Parameters.AddWithValue("ID", option.ID);
try
{
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
else
{
comm.CommandText = "INSERT INTO " + Config.TableNames["Options"] +
" (FieldID, Value, SortOrder) " +
"VALUES (@FieldID, @Value, @SortOrder); SELECT SCOPE_IDENTITY();";
try
{
option.ID = Convert.ToInt32(comm.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
}
}
}
}
/// <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 saveOptions(Field field, SqlCommand comm)
{
if (field.Type == "select" || field.Type == "checkbox" || field.Type == "radio")
{
if (field.Options.Count > 0)
{
//Get List of non-updated
List<int> optionUpdatedIDs = new List<int>();
comm.CommandText = "DELETE FROM @OptionTable WHERE FieldID=@FieldID AND ID NOT IN (";
//TODO: Parameterize this
foreach (Option option in field.Options)
{
comm.CommandText += option.ID + ",";
}
comm.CommandText = comm.CommandText.Substring(0, comm.CommandText.Length - 2) + ");";
comm.Parameters.AddWithValue("@OptionTable", Config.TableNames["OptionTableName"]);
comm.Parameters.AddWithValue("@FieldID", field.ID);
try
{
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
//Cycle through each option and update/insert depending on if it matches existing option IDs for this field ID
foreach (Option option in field.Options)
{
if (field.ID.HasValue && optionUpdatedIDs.Contains(field.ID.Value))
{
comm.CommandText = "UPDATE @OptionsTable SET ID=@ID, FieldID=@FieldID, Value=@Value, SortOrder=@SortOrder";
}
else
{
comm.CommandText = "INSERT INT @OptionsTable (ID, FieldID, Value, SortOrder) "
+ "VALUES (@ID, @FieldID, @Value, @SortOrder);";
}
comm.Parameters.AddWithValue("field_table", Config.DefaultSettings["FormTableName"]);
comm.Parameters.AddWithValue("ID", option.ID);
comm.Parameters.AddWithValue("FieldID", field.ID);
comm.Parameters.AddWithValue("Value", option.Value);
comm.Parameters.AddWithValue("SortOrder", option.SortOrder);
comm.ExecuteNonQuery();
}
}
else
{
comm.CommandText = "DELETE FORM @option_table WHERE ID=@ID";
}
comm.Parameters.AddWithValue("@option_table", Config.DefaultSettings["OptionsTableName"]);
comm.Parameters.AddWithValue("@ID", field.ID);
try
{
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
}
}
