How can I get data from a Select query as a String data type from a SQLite database?

Question

I'm using this query:

string query = "SELECT CAST(FullName AS STRING) FROM Register WHERE ID = 1;

DataTable result = SQLiteDataAccess.MakeQuery(query);

from this SQLite Table. I'm using System.Data and Dapper to access the SQLite database. The following code is used to access the database:

public static DataTable MakeQuery(string query)
{
            SQLiteDataReader result;
            DataTable datatable = new DataTable();

        using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString()))
        {
            cnn.Open();

            SQLiteCommand command = new SQLiteCommand(query, cnn);
            result = command.ExecuteReader();
            datatable.Load(result);

            cnn.Close();
        }

        return datatable;
    }

When I try to use the result, for example with this code:

        foreach (DataRow record in result.Rows)
        {
            names.Add((string)record["FullName"]);
        }

I get the error "System.ArgumentException: 'Column 'FullName' does not belong to table .'" The following locals window screenshot shows that the name of the column is "CAST(FullName AS STRING)" and not "FullName", and the data type is int64, not string.

It seems that I'm using the wrong syntax for casting data types. It must be different in SQLite because it works in SQL Server. What's the correct query in SQLite? Thanks

Edit: Tried a suggestion in a comment and got this error showing the data type is still Int64.

I'm not sure why you need the cast (perhaps the FullName column contains something like a Date??). Try changing your select to start SELECT CAST(FullName AS STRING) as FullName. As it is, that column has no name, so it's picking the SQL goop as the name — Flydog57
– Flydog57, Commented Feb 19, 2020 at 23:25
SQLite only supports the TEXT data type. Because C# is a very tightly typed language I think I need to be specific when retrieving data. When I'm trying to retrieve other data types such as boolean values which are not supported by SQLite, I think I will need to cast them too. I tried what you said but the data type is still Int64. — Piano Telope
– Piano Telope, Commented Feb 19, 2020 at 23:47

GMB · Accepted Answer · 2020-02-19 23:58:27Z

2

I don't see the point for casting, since your data seems to be stored in a string-like datatype already. So why not just do:

SELECT FullName FROM Register WHERE ID = 1;

If, for some reason, you do nee to cast, then you would need to alias the expression, so you can later on access it from your code:

SELECT CAST(FullName AS TEXT) FullName FROM Register WHERE ID = 1;

edited Feb 19, 2020 at 23:58

answered Feb 19, 2020 at 23:23

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Piano Telope Over a year ago

I agree. I shouldn't need to cast because the data is stored as a string in the database and I want to use it in C# as a string. However, the data is being stored in the "result" datatable as int64. Somewhere along the line it's being converted to int64, and my approach to stop this from happening was to use a cast. That approach is not working for me at the moment, and casting may not be the right way to resolve the issue.

GMB Over a year ago

@PianoTelope: actually I think sqlite wants TEXT, not STRING. I am unsure this will be sufficient to solve your problem though.

Jacobm001 · Accepted Answer · 2020-02-20 03:57:16Z

1

If you don't define the name of a requested column, your database has to go with some kind of default. Normally, this is the name of your column. If you start throwing functions around though, things get much more complicated for such an idea. After all, you could include two columns in the function.

Luckily this is very easily fixed using the AS keyword. Change your query to:

string query = "SELECT CAST(FullName AS TEXT) as FullName FROM Register WHERE ID = 1;

Note: I chose to use FullName here, as you seem to want to. This is fine as long as you don't duplicate column names in the call.

I've also changed the STRING type to TEXT since that's sqlite3's actual type.

answered Feb 20, 2020 at 3:57

Jacobm001

4,5595 gold badges32 silver badges52 bronze badges

Collectives™ on Stack Overflow

How can I get data from a Select query as a String data type from a SQLite database?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related