CODEWITHSUNDEEP
c#sqlite
TechGeek
TechGeek

Reputation: 326

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

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);
}

Upvotes: 0

Views: 907

Answers (1)

Charlieface
Charlieface

Reputation: 71648

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);
}

Upvotes: 1

Related Questions