2
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ @Paparazzi One assessment has many questions. I don't really know what your point is. \$\endgroup\$ – Marthe Veldhuis Nov 28 '16 at 16:28
  • \$\begingroup\$ In general if you can reduce the number of times you hit up the DB to load data your code should perform better. Using tables should also allow you to parse your data to objects more efficiently than having to unpack nested lists. \$\endgroup\$ – yu_ominae Nov 28 '16 at 16:33
  • \$\begingroup\$ I think SQLite can use Entity framework. It might not be faster, but it should make your parsing to objects and dealing with the DB much easier and cleaner, so would be worth looking into. \$\endgroup\$ – yu_ominae Nov 28 '16 at 16:35
2
\$\begingroup\$

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));
\$\endgroup\$
  • \$\begingroup\$ Could you maybe explain the code of your join? \$\endgroup\$ – Marthe Veldhuis Nov 30 '16 at 7:22
  • \$\begingroup\$ @MartheVeldhuis well, it's just an ordinary join ;-) it partially replaces what you are doing with the two loops - the first one and the first part of the second loop. It would be too much to explain what a join is. You'll need to google for it. \$\endgroup\$ – t3chb0t Nov 30 '16 at 8:23
  • \$\begingroup\$ Yea thanks. With "Create new types" do you mean I should create a class to hold my Result? \$\endgroup\$ – Marthe Veldhuis Nov 30 '16 at 9:46
  • 1
    \$\begingroup\$ @MartheVeldhuis exactly! I find a class much easier to work with then a data table. You don't have to cast anything or remember column names etc. But it's only my opinion ;-] \$\endgroup\$ – t3chb0t Nov 30 '16 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.