McLuv McLuv - 4 months ago 23
SQL Question

C# SQL - Loading a DataTable in DataGridView alters SQL query's data

I am making an SQLite query generator in C# for a Cross-Country timer project. It generates queries according to information you select in ComboBoxes.

From the very beginning of my project, I have been using the following function

private static DataTable ExecuteTableQuery(String query)
{
DataTable dt = new DataTable();
using (SQLiteCommand cmd = new SQLiteCommand(query, CONNECTION))
{
SQLiteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return dt;
}
}


in order to set the DataSource of my DataGridView Form.

But, the more complex my generated queries were getting, the more I started to run into odd situations.

Query example:

SELECT "No. Arrived", "Status", "Time", "Time Total", "No. Runner" // [A]
FROM (
SELECT `Runner_no` AS "No. Runner", // Runner_no is a PK
`Result_time` AS "Time",
`Result_totaltime` AS "Time Total",
`Result_status` AS "Status",
`Result_registered` AS "No. Arrived" // Result_registered is a PK
// [B]
FROM `Result`, `Runner`, `City`
WHERE `Runner_City_id` = `City_id` // City_id is a PK
AND `Result_id` = `Runner_no`
AND (`Runner_gender` = "F" OR `Runner_gender` = "M")
AND (`Result_status` = "hurt" OR `Result_status` = "disq" OR `Result_status` = "OK" )

UNION // Some people may have not reached the finish line.

SELECT `Runner_no` AS "No. Runner",
NULL AS "Time",
NULL AS "Time Total",
NULL AS "Status",
NULL AS "No. Arrived"
FROM `Result`, `Runner`, `City`
WHERE `Runner_City_id` = `City_id`
AND `Runner_no` NOT IN (SELECT `Result_id` FROM `Result` )
AND (`Runner_gender` = "F" OR `Runner_gender` = "M" )
GROUP BY "Runner_no"
)
ORDER BY "No. Arrived" ASC


Please note that my application is made to generate variable SQL queries so it is normal for my example to look simplifiable.

MY PROBLEM: Most of the time, (I think) when my generated queries feature primary keys in some clauses (like in my example), some results are omitted, ordering gets neglected and sometimes, selecting another row in [A] could make the query work again.

In my example:
No. Arrived
column does not get ordered, some row disappear and adding the column
City_id
into [A] and [B] solves everything.

NOTA BENE: There are no error whatsoever in any query generated by my application. Executing them in any external SQLite Software (e.g. http://sqlitebrowser.org/) is always successful.

MY QUESTIONS: Why does this happen when I am loading data in a DataTable? How can I avoid this? How can I get in C# the same result I would get using c++ based softwares like SQLiteBrowser?

Answer

I got my answer! Thanks for the help. It seems like (in my case) I cannot use SQLiteCommand since it will omit some basic SQL rules (not sure what kind exactly) while reading in order to reduce overheat.

For complex queries like mine, using an SQLiteDataAdapter works fine.

private static DataTable ExecuteTableQuery(String Query)
{
    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(Query, CONNECTION))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }
}