All values taken from my sqlite database that are being added into my lists are just, "sqlitedatareader"

Question

0.00/5 (No votes)

See more:

I am trying to create a script that takes ALL the information from a SQLite database and store them into lists that are stored inside of dictionaries. I am able to add to my lists stored inside the dictionaries, however all my stored values just show up as, "System.Data.SQLite.SQLiteDataReader" instead of the actual values inside of the rows.

Variables:
tableNames = List of all the tables' names in the database
allTables = Dictionary of all tables that I will store data in the form of lists into
table = String that represents the name of a specific table

Link to picture of code and the results[^]

Any help or pointers would be greatly appreciated!

What I have tried:

C#

public static List<string> getSQLiteData(string liteConString, IList<string> tableNames)
        {
            SQLiteConnection liteCon = new SQLiteConnection(liteConString);
            liteCon.Open();

            /// Creates a dictionary where the "Key" is the name of the table
            /// and the "Values" are lists that represent the values in each table
            IDictionary<string, List<string>> allTables = new Dictionary<string, List<string>>();
            foreach (string table in tableNames)
            {
                string selectQuery = "SELECT * FROM " + table;
                allTables.Add(table, new List<string>());

                SQLiteCommand cmd = new SQLiteCommand(selectQuery, liteCon);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    allTables[table].Add(reader.ToString());
                }
                reader.Close();
            }

            return null;
        }

I'm not completely sure as to what's wrong with this bit of code and why it's not grabbing the actual information. I'm very new to C# and SQL so please forgive any egregious errors.

EDIT: I've now tried SQLiteDataAdapter instead of SQLiteDataReader and storing the results into DataTables instead of lists. However, now I'm running into a bug where the DataTable doesn't have the actual values of the SQLite tables, but rather has the values of its "properties". This is the code:

IDictionary<string, DataTable> allTables = new Dictionary<string, DataTable>();
foreach (string table in tableNames)
{
    string selectQuery = "SELECT * FROM " + table;
    allTables.Add(table, new DataTable());

    SQLiteCommand cmd = new SQLiteCommand(selectQuery, liteCon);
    SQLiteDataAdapter sqliteDAP = new SQLiteDataAdapter(cmd);
    sqliteDAP.Fill(allTables[table]);
}

Link to the results of this code[^]

Posted 18-Sep-20 5:14am

stevenlam505

Updated 18-Sep-20 7:20am

v2

Add a Solution

Comments

stevenlam505 18-Sep-20 11:16am

Here's another picture of what the table actually looks like: https://imgur.com/a/BkMoLG4

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-09-18T06:10:00

When you call ToString on anything the system looks at the instance you are calling it on, and goes back through the inheritance chain to find the "closest" implementation. If your class explicitly implements ToString then taht method will be used - but if it doesn't, then the class that you are directly derived from is checked, and so on. If it can't find an explicit implementation by the time it reaches the object class (and remember, everything is derived from object in .NET) then the default implementation is used, which returns the fully qualified name of the original type.

So when you do this:

C#

allTables[table].Add(reader.ToString());

the system checks if SQLiteDataReader implements ToString - which it doesn't. So it tries DbDataReader which SQLiteDataReader is derived from. No joy there, so back it goes - and quickly ends up at object, and uses the default implementation, which always returns "System.Data.SQLite.SQLiteDataReader"

Why doesn't SQLiteDataReader implement ToString? Because it has no idea what data you do and don't want retrieved, or how you want it: it doesn't even know what the columns contain other than the very basic SQL datatypes which mostly aren't much use!

If you want to add a list of strings to a dictionary entry from a DataReader, you will have to construct a collection, and populate that yourself:

C#

allTables[table].Add(reader["myColumn1"].ToString());
allTables[table].Add(reader["myColumn2"].ToString());

But even then, that's probably not going top do what you actually want since every column from every row will go in the same dictionary entry.

I think you want to think rather more carefully about what exactly you are trying to do with this code (and that's ignoring that you discard all the information at the end of the method anyway).

And BTW: it's considered a bad idea to use SELECT * FROM ... - you should always list the column names you are interested in rather than just saying "all of them".