I have an application which uses SQLite. In that database I have several tables that I need to be able to read and update in my application. Often I need to ‘cross-reference’ these tables to get the correct values.

For instance, I load an interview with answers someone has previously selected. To do so, I have a Results table in which each entry represents a question and the answer given. This question and answer are stored by their IDs. The questions and answers both have their own tables in which they are stored by an ID, the value and a standard. Therefore, if I want to reload the answers into my interview, I have to find the answers that correspond to the answers’ id in my Results table (because I want to answer values in my interview, not their IDs).

When I first started to implement this, I used List<List<string>> to store the results. However, this caused me to use a for-loop to get all the answer values by sending a query to the database in each loop. This was very slow.

var questions = new List<string>();

for(int i = 0; i < questionsStandard.Count; i++)
{
    var temp = _databaseController.QueryDatabase("SELECT * FROM Questions WHERE question_standard = '" +
    questionsStandard[i] + "'", "question_id");

    questions.AddRange(temp);
}

for (int i = 0; i < questions.Count; i++)
{
    results.Add("");

    var tempMaturityAnswers = _databaseController.QueryDatabase("SELECT * FROM MaturityAnswers WHERE m_answer_id = " +
        "(SELECT m_answer_id FROM Results WHERE assessment_id = '" + assessmentId + "')",
        "m_answer_value");
    if (tempMaturityAnswers.Any())
    {
        results.Add(tempMaturityAnswers[0]);
    }
    else results.Add("");

    var tempCompliacenAnswers = _databaseController.QueryDatabase("SELECT * FROM ComplianceAnswers WHERE c_answer_id = " +
        "(SELECT c_answer_id FROM Results WHERE assessment_id = '" + assessmentId + "' AND question_id = '" + questions[i] + "')",
        "c_answer_value");
    if (tempCompliacenAnswers.Any())
    {
        results.Add(tempCompliacenAnswers[0]);
    }
    else results.Add("");

    var tempNotes = _databaseController.QueryDatabase("SELECT * FROM Results WHERE assessment_id = '" + assessmentId + "' AND question_id = '" + questions[i] + "'",
        "notes");
    if (tempNotes.Any())
    {
        results.Add(tempNotes[0]);
    }
    else results.Add("");
}

return results;

That’s why I am starting to look into DataTables, which seems to be working quite well.

DataColumn[] keyColumn1 = new DataColumn[1];
DataColumn[] keyColumn2 = new DataColumn[1];

var results = new DataTable();
results = _databaseController.QueryTable("Results", "WHERE assessment_id = '" + assessmentId + "'");

var maturityAnswers = new DataTable();
maturityAnswers = _databaseController.QueryTable("MaturityAnswers");

var complianceAnswers = new DataTable();
complianceAnswers = _databaseController.QueryTable("ComplianceAnswers");

keyColumn1[0] = maturityAnswers.Columns["m_answer_id"];
keyColumn2[0] = complianceAnswers.Columns["c_answer_id"];
maturityAnswers.PrimaryKey = keyColumn1;
complianceAnswers.PrimaryKey = keyColumn2;


var questions = new List<string>();

for(int i = 0; i < questionsStandard.Count; i++)
{
    var temp = _databaseController.QueryDatabase("SELECT * FROM Questions WHERE question_standard = '" +
    questionsStandard[i] + "'", "question_id");

    questions.AddRange(temp);
}

foreach(DataRow row in results.Rows)
{
    row["m_answer_id"] = maturityAnswers.Rows.Find(row["m_answer_id"])["m_answer_value"];
    row["c_answer_id"] = complianceAnswers.Rows.Find(row["c_answer_id"])["c_answer_value"];
}

return results;

Does anyone have any tips/opinions about this? What should I use? Is there an even better/faster option? Do you have any general tips?

Don’t loop – join

Often I need to ‘cross-reference’ these tables to get the correct values.

That’s what joins are for. Use them instead of iterating with two loops to get the results e.g.

SELECT 
    * -- or select the columns you need
FROM
    Questions q
    JOIN MaturityAnswers ma on m.m_answer_id = q.m_answer_id

WHERE
    q.question_standard = @question_standard
    AND m.assessment_id = @assessment_id

It’s just an example. This is probably not 100% correct as I don’t know the exact model of your database.

POCO

For instance, I load an interview with answers someone has previously
selected. To do so, I have a Results table in which each entry
represents a question and the answer given. This question and answer
are stored by their IDs. The questions and answers both have their own
tables in which they are stored by an ID, the value and a standard.

Create new types to hold the results. Personally I don’t like DataTables because they cannot be easily queried with linq and require disposing so I advise you against them.

Preventing SQL Injection

Don’t concatenate strings – use the SQLiteParameter.

command.CommandText = "SELECT * FROM Questions WHERE question_standard = @question_standard";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SQLiteParameter("@question_standard", question_standard));