public void PopulateTable(SurveyHelper.TableType table_type)
{
string table_name = null;
switch (table_type)
{
case SurveyHelper.TableType.BS5837:
table_name = "bs5837_table.txt";
break;
case SurveyHelper.TableType.GeneralSurvey:
table_name = "general_survey_table.txt";
break;
default:
return;
}
AssetManager assets = Application.Context.Assets;
Table temp_table = new Table();
TableCategory temp_cat = new TableCategory();
using (StreamReader reader = new StreamReader(assets.Open(table_name)))
{
while (!reader.EndOfStream)
{
string line = reader.ReadLine();
if (isFileStart(line))
{
while (reader.ReadLine() != "[End]")
{
}
}
else if (isTableStart(line))
{
temp_table.name = reader.ReadLine();
}
else if (isTableEnd(line))
{
temp_table.categories.Add(temp_cat);
//value_tables.Add(temp_table);
SurveyHelper.values_tables.Add(temp_table);
temp_table = new Table();
temp_cat = new TableCategory();
}
else if (isTableCategory(line))
{
if (temp_cat.items.Count() > 0)
{
temp_table.categories.Add(temp_cat);
temp_cat = new TableCategory();
}
temp_cat.category_name = line;
}
else if (isTableItem(line))
{
temp_cat.items.Add(line);
}
else if (line == "[Topic]")
{
break;
}
}
}
}
public static bool CreateSurveyTable(string table_name, SurveyHelper.TableType survey_type)
{
if (!File.Exists(db_path))
{
CreateApplicationDatabase();
}
if (CheckTableExistance(table_name))
{
return(false); // A table of the same name already exists
}
connection = new SqliteConnection(GetAppendedConnString());
connection.Open();
string create_table_statement = $"CREATE table [{table_name}] (";
string populate_table_statement = $"INSERT INTO [{SurveyHelper.ActiveSurvey.survey_name}](";
foreach (DataColumn col in SurveyHelper.ActiveSurvey.survey_datatable.Columns)
{
//create_table_statement += "[" + col.ColumnName + "]" + "nvarchar(255)" + ",";
create_table_statement += $"[{col.ColumnName}] nvarchar(255),";
populate_table_statement += $"[{col.ColumnName}], ";
}
create_table_statement = create_table_statement.TrimEnd(',') + ");";
SqliteCommand command = new SqliteCommand(create_table_statement, connection);
command.ExecuteNonQuery();
populate_table_statement = populate_table_statement.TrimEnd(' ', ',');
populate_table_statement += ") VALUES (";
//populate_table_statement = populate_table_statement.TrimEnd(' ', ',');
//populate_table_statement += ");";
//populate_table_statement += ") VALUES (";
using (var transaction = connection.BeginTransaction())
using (var populate_command = connection.CreateCommand())
{
foreach (DataRow dataRow in SurveyHelper.ActiveSurvey.survey_datatable.Rows)
{
string statement_values = null;
foreach (object obj in dataRow.ItemArray)
{
//populate_table_statement += $"'{obj}', ";
statement_values += $"'{obj}', ";
}
statement_values = statement_values.TrimEnd(' ', ',');
statement_values += ");";
populate_command.CommandText = populate_table_statement + statement_values;
populate_command.ExecuteNonQuery();
}
transaction.Commit();
}
connection.Close();
return(true);
}
