如何使用sqlite和c＃从表查询列数据？

发布于 2025-02-13 21:40:45 字数 972 浏览 1 评论 0原文

我使用数据库浏览器创建一个名为users.db的数据库，然后我手动创建了该数据库中的名为用户的表，并创建了一些我可能需要从C＃查询的字段。代码。我正在使用库sqlite来做到这一点。如何从用户表的电子邮件列中查询数据，这是我尝试使用电子邮件作为查询参数获取密码数据的内容？

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

老旧海报 2025-02-20 21:40:45

您在这里有许多问题：

TableMapping在这里无关紧要，除非您要自定义映射到类。为此，您将不使用executeQuery＆lt; tablemapping＆gt;
您只需要executeQuery＆lt; string＆gt;，它将返回list＆lt; string＆lt; string＆gt;。
假设电子邮件是唯一的，则可以使用executesCalar返回单个值。
您需要参数化查询，不要将数据注入查询中。
您需要使用块来处理连接和命令。
无论如何，您可能不应该存储普通文本密码，但我会让您考虑一下

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

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

回复收藏 0 原文

~没有更多了~