How to query column data from table using sqlite and C#?

Question

I used DB Browser to create a database called Users.db and then I manually created a table called Users in that database and created some fields that I might need to query from C# code. I am using the library SQLite to do this. How do I query data from the email column of the users table, here is what I tried to get the password data using email as a query parameter?

try
{
  SQLiteConnection conn = new SQLiteConnection("users.db");
  //get the email entered into the text box by the user
  string email = textBox1.Text;
  //define the command text to run on the table
  string query = "SELECT password FROM users WHERE email=" + email;
  //define a new SQLiteCommand
  SQLiteCommand command = new SQLiteCommand(conn);
  //modify the query text of the command object
  command.CommandText = query;
  //how do I extract data from the returned row using command.executequery?
  var data = command.ExecuteQuery<TableMapping>();
  if (data != null)
     {
     //fetch the rows, except that SQLite throws an exception that I should not use TableMapping to query data
     }


}catch(SQLiteException exc){
  Console.WriteLine(exc.Message);
}

Do not use string concatenation to create an SQL command. Use parameterized statements. See why it's a bad idea and how to fix it. — gunr2171
– gunr2171, Commented Jul 8, 2022 at 12:09
You've shown your setup code, but can you show us your attempt at using command.ExecuteQuery? There are plenty of resources on the internet to instruct you on using Sqlite in C#. What actual problem are you having? — gunr2171
– gunr2171, Commented Jul 8, 2022 at 12:10
I had used TableMapping to query data but SQLite said I cannot use that to query, editing — TechGeek
– TechGeek, Commented Jul 8, 2022 at 12:13
It looks like your attempt is simply incomplete. What have you tried and what didn't work as expected? Did you look up any documentation or examples of how to use SQLite in C# and try anything at all? — David
– David, Commented Jul 8, 2022 at 12:15
You've updated the question... Now what specifically is the problem? What is TableMapping? What is the exact error message and which exact line of code produces that error? — David
– David, Commented Jul 8, 2022 at 12:19

Charlieface · Accepted Answer · 2022-07-08 12:33:39Z

1

You have a number of issues here:

TableMapping is not relevant here unless you want a custom mapping to a class. And for that you would not use ExecuteQuery<TableMapping>
You just want ExecuteQuery<string> which will return a List<string>.
Assuming the email is unique, you can just use ExecuteScalar which returns a single value.
You need to parameterize your query, do not inject data into the query.
You need using blocks to dispose the connection and command.
You probably shouldn't be storing plain-text passwords anyway, but I'll leave you to think about that

try
{
    string email = textBox1.Text;
    const string query = @"
SELECT password
FROM users
WHERE email = @email;
";
    using (var conn = new SQLiteConnection("users.db"))
    using (var command = new SQLiteCommand(query, conn))
    {
        command.Bind("@email", email);
        var data = command.ExecuteScalar<string>();
        if (data != null)
        {
          // do something with it
        }
    }
}
catch(SQLiteException exc)
{
    Console.WriteLine(exc.Message);
}

answered Jul 8, 2022 at 12:33

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

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

4 Comments

TechGeek Over a year ago

Its returning a white space with no data , the email is correct though

TechGeek Over a year ago

I debugged your code, it needs the following as a query SELECT password FROM users WHERE email='@email'. The argument after the equals operator needs single quotes

Charlieface Over a year ago

That's if you want to look for the literal text '@email', but if you are using bind-parameters then no it doesn't go in quotes

TechGeek Over a year ago

Your right I forgot the bind command code, Thanks for this answer

Collectives™ on Stack Overflow

How to query column data from table using sqlite and C#?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related