CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
rayonnante
rayonnante

Reputation: 53

Retrieve string data stored in integer field from sqlite 3 database using C#

I have an sqlite3 database with several tables. One of them has two fields: s_id and user_id, the first is integer, the second is integer primary key. I can watch the table contents with SQLite Data Browser,and there are two rows in the table.

user_id values are 1 and 2.

s_id values are, however, strings (like "user1" and "user2"), and sqlite data browser shows these strings.

I am trying to retreive the information using System.Data.SQLite and the following code in C#:

using (SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0};Legacy Format=True;", path)))
{
  connection.Open();
  using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM users", connection))
  {
    using (SQLiteDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
       string user = reader["s_id"].ToString();
      }
    }
  }
}

I searched the internet and found that sqlite can store string data in int fields. But I cannot read these strings using C#. The result is always "0". Even if I expand the reader in the Watch till I'll be able to see the objects, the value there is 0 too. (The second object corresponding to user_id has value as it should have, 1 or 2).

Do you know how this string value in integer field can be retrieved in C#?

Thank you.

Upvotes: 3

Views: 2594

Answers (1)

Nuffin
Nuffin

Reputation: 3972

First things first: If you're going to store TypeX in a database column, don't declare it as TypeY, just because you can. Storing only values of the appropriate type is (as far as sqlite is concerned) essential for being technology independent (as you can see).

If, for some weird reason, you aren't able to change the database itself, you should cast the value in your query, like this:

SELECT CAST(s_id AS VARCHAR(255)) AS s_id FROM users;

Upvotes: 4

Related Questions