# List<List<string>> vs DataTable

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");

}

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

"(SELECT m_answer_id FROM Results WHERE assessment_id = '" + assessmentId + "')",
{
}

"(SELECT c_answer_id FROM Results WHERE assessment_id = '" + assessmentId + "' AND question_id = '" + questions[i] + "')",
{
}

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

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 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");

}

foreach(DataRow row in results.Rows)
{
}

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?

• @Paparazzi One assessment has many questions. I don't really know what your point is. – Marthe Veldhuis Nov 28 '16 at 16:28
• 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. – yu_ominae Nov 28 '16 at 16:33
• 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. – yu_ominae Nov 28 '16 at 16:35

# 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

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;