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

Question

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.

if you are only checking for s_id, why are you doing a select * for starters... 2nd where are you storing the connection string.. if it's in a .config file ...why not just pass that value in your Connection Object...? 3rd.. wrap this code around a Try Catch{} so that if you have an exception ...you can also post it here... — MethodMan
– MethodMan, Commented Feb 13, 2012 at 21:24
You can search the stored data values.. I am wondering what are you using the string user variable for.. if you are to store it, then you are overriding it because you are setting the string user = ..... in the while loop.. if you want to store it in 2 different variables then you need to add a second variable to store it in within the while loop — MethodMan
– MethodMan, Commented Feb 13, 2012 at 21:28
@DJKRAZE: The problem here isn't an exception but the problem that C# can't cast "user1" (and other strings) to int, what sqlite happily does, and therefore the returned value is always 0 — Nuffin
– Nuffin, Commented Feb 13, 2012 at 21:57
@DJKRAZE I posted only the code that concerns the problem I need to solve. Of course I do not declare string user inside the loop in my actual code, I just see no sense in complicating the code posted here. — rayonnante
– rayonnante, Commented Feb 14, 2012 at 9:37
@Tobias Thanks, you are absolutely right. I get no exceptions. — rayonnante
– rayonnante, Commented Feb 14, 2012 at 9:38

Nuffin · Accepted Answer · 2012-02-13 22:00:07Z

4

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;

edited Feb 13, 2012 at 22:00

answered Feb 13, 2012 at 21:48

Nuffin

3,97020 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rayonnante Over a year ago

If it was my database, of course I wouldn't have used integer field to store string. But it is not mine. Thanks, it solves the problem - I actually didn't think of using a different query string.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related