CODEWITHSUNDEEP
c#visual-studio-2010sqlitesystem.data.sqlite
Patch
Patch

Reputation: 2507

SQLiteDataReader.read() empties results?

I have a method in my C# class that calls a single result from an SQLite database. My database connection opens successfully, but after running breakpoints at different places, I noticed something odd. This is my code:

Dictionary<string, string> getResult(string id)
{
    dataConn.Open();
    SQLiteCommand comm = dataConn.CreateCommand();
    comm.CommandText = "SELECT * FROM [tableName] WHERE id='" + id + "'";
    SQLiteDataReader result = comm.ExecuteReader();
    Dictionary<string, string> resultDict = new Dictionary<string, string>();

    /*
    This doesn't work.
    while(result.Read())
    {
        resultDict.Add(result.GetName(0), result.GetString(0));
    }
    */

    result.Read();
    for (int i = 0; i < result.FieldCount; i++)
    {
        resultDict.Add(result.GetName(i), result.GetString(i));
    }
    result.Dispose();
    comm.Dispose();
    dataConn.Close();
    return resultDict;
}

I know that I can use SQLiteParameters instead of inline concatenation, but for some reason they were breaking my query.

In my first loop, if I place a breakpoint just before it starts, I can see that my query has returned a single result, exactly what I need. But, when I go into my loop, before I execute any code other than reader.Read(), VS2010 says "Enumeration yielded no results." but they were there moments before?

So, I tried the second loop. Executing reader.Read(), and then looping through returned fields. When I do that, VS2010 throws the error "No current row selected.".

I'm completely lost with this, I have no idea what's wrong, because the rest of my SQLite connections for other database files work fine. I've tested my query in SQLite Administrator, and my query executes perfectly fine. All I'm aiming for is to end up with a dictionary where the key is the name of the field, and the value is the result of said field. Any ideas on what's wrong? Or, is there a way I can make this code simpler, in the process of fixing it?

Upvotes: 2

Views: 7240

Answers (3)

mlatu
mlatu

Reputation: 159

I had a similar problem, in my case the table was empty, and i was asking for MAX() of some collumn. I solved it by first checking that COUNT(*) is not 0.

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65554

Its because GetValue(0) didn't return a string.

Casting it to a string allowed you to add it to the Dictionary<string,string>.

Upvotes: 0

Patch
Patch

Reputation: 2507

Solved. For some reason, using .GetString(0) or .GetString(i) was breaking it. However, when I turned it into .GetValue(0).ToString() it worked fine. I have no idea why this is the case, it's beyond me, but it works.

Upvotes: 4

Related Questions