C#SQLite#的查询数据问题，如何解决？

C#SQLite数据库如何查询表中是否已经存在这条数据？为什么我插入数据时，总是会报指定的值转换无效？查了所有数据类型和值都是没问题，只有未明显指示出id（主键）的值

SQLiteCommand cmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS My_table " +
                "(id INTEGER PRIMARY KEY NOT NULL, Volatage varchar(50) DEFAULT NULL," +
                "Load float DEFAULT NULL, Name varchar(50) DEFAULT NULL, " +
                "Min float DEFAULT NULL, Measure float DEFAULT NULL, Max float DEFAULT NULL, " +
                "Result varchar(50) DEFAULT NULL " +
                ")", conn);
            MessageBox.Show(Convert.ToString(Convert.ToInt32(cmd.ExecuteNonQuery())));

            SQLiteCommand sql = new SQLiteCommand($"select * from My_table where id = {id}", conn);
            MessageBox.Show(Convert.ToString(Convert.ToInt32(sql.ExecuteNonQuery())));

            if (Convert.ToInt32(sql.ExecuteNonQuery()) == 0)
            {
                SQLiteCommand sql2 = new SQLiteCommand($"update My_table set Volatage = '{volatage}',Load = {load}, Name = '{name}', Min = {min}, Measure = {measure}, Max ={max}, Result = '{result}' where id = '{id}'", conn);
                sql2.ExecuteNonQuery();
            }
            else
            {
                SQLiteCommand sql1 = new SQLiteCommand($"insert into My_table(Volatage,Load,Name,Min,Measure,Max,Result) Values('" + volatage + "'," + load + " ,'" + name + " '," + min + ", " + measure + ", " + max + ", '" + result + "')", conn);
                sql1.ExecuteNonQuery();
            }

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
在SQLite中查询表中是否已经存在这条数据，你可以使用以下代码片段：

SQLiteCommand cmd = new SQLiteCommand($"SELECT COUNT(*) FROM My_table WHERE id = '{id}'", conn);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
    // 数据已存在
}
else
{
    // 数据不存在
}

这里使用了 SELECT COUNT(*) 来获得符合条件的数据行数，如果返回的不是0，则表明数据已存在。

至于你提到的报错问题，我猜测可能是因为你使用了字符串来插入整型主键字段。修改代码如下：

if (count > 0)
{
    // 更新数据
    SQLiteCommand sql2 = new SQLiteCommand($"UPDATE My_table SET Volatage = '{volatage}', Load = {load}, Name = '{name}', Min = {min}, Measure = {measure}, Max ={max}, Result = '{result}' WHERE id = {id}", conn);
    sql2.ExecuteNonQuery();
}
else
{
    // 插入数据
    SQLiteCommand sql1 = new SQLiteCommand($"INSERT INTO My_table (id, Volatage, Load, Name, Min, Measure, Max, Result) VALUES ({id}, '{volatage}', {load}, '{name}', {min}, {measure}, {max}, '{result}')", conn);
    sql1.ExecuteNonQuery();
}

首先，将更新和插入操作中的id字段也加进去，其中更新操作中的id使用了整型不带引号的形式。在插入操作中，你需要为id、load和min这些字段指定具体的数据类型和值。
如果我的回答解决了您的问题，请采纳！

该回答引用chatgpt:
在您的代码中，您可以将以下代码

SQLiteCommand sql = new SQLiteCommand($"select * from My_table where id = {id}", conn);
MessageBox.Show(Convert.ToString(Convert.ToInt32(sql.ExecuteNonQuery())));
if (Convert.ToInt32(sql.ExecuteNonQuery()) == 0)

替换为：

SQLiteCommand checkExistenceCmd = new SQLiteCommand("SELECT COUNT(*) FROM My_table WHERE id = @id", conn);
checkExistenceCmd.Parameters.AddWithValue("@id", id);
int count = Convert.ToInt32(checkExistenceCmd.ExecuteScalar());
if (count == 0)

这样就可以检查表中是否已经存在该条数据了。注意，ExecuteScalar 方法将返回一个标量值（即一个单一的值），而不是一个数据读取器对象。

引用new bing部分回答作答：
在您的代码中，查询部分应该如下所示：

SQLiteCommand sql = new SQLiteCommand($"select count(*) from My_table where id = {id}", conn);
int count = Convert.ToInt32(sql.ExecuteScalar());
if (count > 0)
{
    SQLiteCommand sql2 = new SQLiteCommand($"update My_table set Volatage = '{volatage}',Load = {load}, Name = '{name}', Min = {min}, Measure = {measure}, Max ={max}, Result = '{result}' where id = '{id}'", conn);
    sql2.ExecuteNonQuery();
}
else
{
    SQLiteCommand sql1 = new SQLiteCommand($"insert into My_table(Volatage,Load,Name,Min,Measure,Max,Result) Values('" + volatage + "'," + load + " ,'" + name + " '," + min + ", " + measure + ", " + max + ", '" + result + "')", conn);
    sql1.ExecuteNonQuery();
}

在这里，我们使用了ExecuteScalar方法来查询数据是否存在。ExecuteScalar方法返回查询结果的第一行第一列，因此我们可以使用count(*)来计算匹配查询条件的行数。如果行数大于0，则该行已经存在，应该使用UPDATE语句来更新它。否则，我们将使用INSERT INTO语句将新行插入到表中。

请注意，我们在UPDATE和INSERT INTO语句中使用了参数化查询。这可以避免 SQL 注入攻击，并使代码更加清晰和易于维护。要使用参数化查询，请使用问号 ? 或者 @ 开头的占位符来代替实际值。然后，使用 SQLiteCommand.Parameters.AddWithValue 方法将实际值添加到查询中。

sqlite支持 “INSERT OR REPLACE", 可以实现不存在则新增、已存在则更新的效果，例如

INSERT OR REPLACE INTO user(id, name) values ('123','zhangsan') 

可以借鉴下

sqlite 判断某条数据是否存在
1、判断某条数据是否存在

复制代码
bool DataBaseManager::WriteConfigure(QString configureName, QString configureVal)
{
    QString sql = QString("select 1 from Configure where configureName = '%1' limit 1;").arg(configureName);
    QSqlQuery query(m_DBZHdb);
    if (query.exec(sql))
    {
        int nIndex = query.record().indexOf("1");
        query.next();
        int nCount = query.value(nIndex).toInt();
        
        if (nCount == 1) {
            //存在则更新
            sql = QString("update Configure set configureVal = '%1' where configureName = '%2'").arg(configureVal).arg(configureName);
        }
        else
        {
            //不存在，则插入
            sql = QString("insert into Configure(configureName,configureVal) Values('%1','%2')").arg(configureName).arg(configureVal);
        }
        bool b = query.exec(sql);
        if (b) {
            ReadConfigure();//重新加载
        }
        return b;
    }
    else
    {
        return false;
    }

}
复制代码
 2022年10月18日更新：又发现一个判断记录是否存在的方法

复制代码
bool OperateSqlite::IsRecordExist1(const QString& tbName, const QString& field, const QString& value)
{
    QString strQuery;

    strQuery = QString("SELECT * FROM %1 WHERE %2='%3';").arg(tbName).arg(field).arg(value);

    QSqlQuery query(strQuery, m_sqlDataBase);
    return query.next();
}
复制代码
 

2、获取某条数据有多少条

复制代码
int DataBaseManager::getTrackPointCount(const QString & taskName, const QString & personNo)
{
    QSqlQuery query(m_chatMsgdb);
    QString sql = QString("SELECT COUNT(id) FROM taskState_%1 WHERE targetId='%2';").arg(taskName).arg(personNo);

    if (query.exec(sql))
    {
        if (query.next())
        {
            int count = query.value(0).toInt();
            return count;
        }
    }
    return 0;
}

• 你可以参考下这个问题的回答, 看看是否对你有帮助, 链接: https://ask.csdn.net/questions/753483
• 这篇博客也不错, 你可以看下C#使用SQLite查询时间段内数据
• 除此之外, 这篇博客: C# SQLite使用举例查询数据库所有表所有列，插入表和插入的方法中的 主窗体类 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

• using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Windows.Forms;
  using System.Data.SQLite;
  using System.IO;
  
  namespace SqlitTest
  {
      public partial class Form1 : Form
      {
          static  string DbName = "testc";
          static string CurTable = "表2";
          static string CurColNum = "列1";
          static string filePath = "db";
          public Form1()
          {
              InitializeComponent();
              UpdateDbname();
          }
          private void UpdateDbname()
          {
              string[] files = Directory.GetFiles(filePath, "*.db");
              foreach (string i in files)
              {
                  string str = i.Replace($"{filePath}\\","");
                  str = str.Replace(".db", "");
                 if( !comboBoxDbNameList.Items.Contains(str))
                  comboBoxDbNameList.Items.Add(str);
              }
              if (comboBoxDbNameList.Items.Contains(DbName))
              {
                  int m = 0;
                  foreach (string j in comboBoxDbNameList.Items)
                  {
                      if (DbName == j)
                      {
                        //  comboBoxDbNameList.SelectedIndex = m;
                      }
                      m++;
                  }
              }
              UpdateTbName2();
          }
          //显示表信息的第一中方法
          private void UpdateTbName()
          {
              DataTable dt = new DataTable();
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  if (connection.State != ConnectionState.Open)
                      connection.Open();
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      command.CommandText = "select * from sqlite_master where type = 'table' order by name; ";                 
                      using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                      {
  
                          sda.Fill(dt);//将数据库中表信息显示
                      }
                      comboBoxTbNameList.Items.Clear();
                      if (dt.Rows.Count > 0)
                      {
                          DataRow[] tbs = dt.Select("type = 'table'");
                          foreach (var tb in tbs)
                          comboBoxTbNameList.Items.Add(tb["name"]);
                      }
                      if (comboBoxTbNameList.Items.Contains(CurTable))
                      {
                          DataTable dt2 = new DataTable();
                          command.CommandText = $"select * from {CurTable}; ";//读取表中元素
                          using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                          {
                              sda.Fill(dt2);
                          }
                          dataGridView1.DataSource = dt2;//将表中所有信息显示
                          int m = 0;
                          foreach (string j in comboBoxTbNameList.Items)
                          {
                              if (CurTable == j)
                              {
                                //  comboBoxTbNameList.SelectedIndex = m;
                              }
                              m++;
                          }
                      }
                      
  
                  }
                  connection.Close();
                 
              }
  
             
  
  
  
          }
          //显示表信息的第二种方法
          private void UpdateTbName2()
          {
              DataTable dt = new DataTable();
              List<string> tableNameList = new List<string>();
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  connection.Open();
                  string sqlTableNames = "select name from sqlite_master where type='table' order by name;";
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      command.CommandText = sqlTableNames;
                      using (SQLiteDataReader dr = command.ExecuteReader())
                      {
                          while (dr.Read())
                           tableNameList.Add((string)dr["Name"]);
  
                      }
                      comboBoxTbNameList.Items.Clear();
                      if (tableNameList.Count > 0)
                      {
                          foreach (var tb in tableNameList)
                          {
                              comboBoxTbNameList.Items.Add(tb);
                          }
                      }
                      if (comboBoxTbNameList.Items.Contains(CurTable))
                      {
                          DataTable dt2 = new DataTable();
                          command.CommandText = $"select * from {CurTable}; ";//读取表中元素
                          using (SQLiteDataAdapter sda = new SQLiteDataAdapter(command))
                          {
                              sda.Fill(dt2);
                          }
                          dataGridView1.DataSource = dt2;//将表中所有信息显示
                          int m = 0;
                          foreach (string j in comboBoxTbNameList.Items)
                          {
                              if (CurTable == j)
                              {
                                  //  comboBoxTbNameList.SelectedIndex = m;
                              }
                              m++;
                          }
                      }
                      else
                          dataGridView1.DataSource = null;
  
  
                  }
                  connection.Close();
  
              }
              UpdateColmnu();
          }
          //显示列元素的方法
          private void UpdateColmnu( )
          {
              DataTable dt = new DataTable();
              List<string> ColNameList = new List<string>();
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  if (connection.State != ConnectionState.Open)
                      connection.Open();
                  // 获取表中的所有字段名
                  string sqlfieldName = $"Pragma Table_Info( {CurTable}  )"; 
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      command.CommandText = sqlfieldName;
                      using (SQLiteDataReader dr = command.ExecuteReader())
                      {
                          while (dr.Read())
                              ColNameList.Add((string)dr["Name"]);
  
                      }
                      comboBoxColmunList.Items.Clear();
                      if (ColNameList.Count > 0)
                      {
                          
                          foreach (var tb in ColNameList)
                                  comboBoxColmunList.Items.Add(tb);
                      }
                      if (comboBoxColmunList.Items.Contains(CurColNum))
                      {                                             
                          int m = 0;
                          foreach (string j in comboBoxColmunList.Items)
                          {
                              if (CurColNum == j)
                              {
                               //   comboBoxColmunList.SelectedIndex = m;
                              }
                              m++;
                          }
                      }
  
  
                  }
                  connection.Close();
  
              }
  
  
  
  
          }
  
          private void button1_Click(object sender, EventArgs e)
          {
  
            string   dbname = textBoxDbName.Text;
              if (dbname == ""|| dbname == "请输入数据库名")
              {
                  MessageBox.Show("请输入数据库名");
                  return;
              }
              DbName = dbname;
            
              string[] files = Directory.GetFiles(filePath, "*.db");
              foreach(string i in files)
              {
                  string str = $"{filePath}\\{dbname}.db";
                  if (str == i)
                  {
                      MessageBox.Show("已经包含这个数据库");
                      return;
                  }
              }
              SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{dbname}.db");
              connection.Open();
              connection.Close();
              
          }
  
          private void button2_Click(object sender, EventArgs e)
          {
              string mtableName = textBoxTableName.Text;
              if (mtableName == ""|| mtableName == "请输入表名")
              {
                  MessageBox.Show("请输入表名");
                  return;
              }         
              if (comboBoxTbNameList.Items.Contains(mtableName))
              {
                  MessageBox.Show("已经包含该表");
                  return;
              }
              CreatCol mform = new CreatCol();
              mform.TopMost = true;
              mform.ShowDialog();
              if (mform.DialogResult == DialogResult.Cancel)
                  return;
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  if (connection.State != ConnectionState.Open)
                      connection.Open();
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      SqLiteHelper mm = new SqLiteHelper(command);
                      SqLiteTable mtable = new SqLiteTable (mtableName);
                      mtable.Columns.Add(mform.mclo);
                      mm.CreateTable(mtable);              
                  }
                  connection.Close();
              }          
              CurTable = mtableName;
              UpdateDbname();
          }
  
          private void button3_Click(object sender, EventArgs e)
          {
              CreatCol mform = new CreatCol();
              mform.TopMost = true;
              mform.ShowDialog();
              if (mform.DialogResult == DialogResult.Cancel)
                  return;
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  if (connection.State != ConnectionState.Open)
                      connection.Open();
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      SqLiteHelper mm = new SqLiteHelper(command);                       
                      mm.AddColmnu(CurTable, mform.mclo);
                  }
                  connection.Close();
              }
              UpdateDbname();
          }
  
          private void comboBoxDbNameList_SelectedValueChanged(object sender, EventArgs e)
          {
              DbName=  comboBoxDbNameList.Text;
              UpdateDbname();
          }
  
          private void textBoxTableName_TextChanged(object sender, EventArgs e)
          {
  
          }
  
          private void button4_Click(object sender, EventArgs e)
          {
              using (SQLiteConnection connection = new SQLiteConnection($"Data Source=db/{DbName}.db"))
              {
                  if (connection.State != ConnectionState.Open)
                      connection.Open();
                  using (SQLiteCommand command = new SQLiteCommand(connection))
                  {
                      string sql = $"INSERT INTO {CurTable}({CurColNum}) VALUES(@ID1) ";
                      command.CommandText = sql;
                      command.Parameters.Add(new SQLiteParameter("ID1", textBoxColValue.Text));
                      command.ExecuteNonQuery();
                  }
                  connection.Close();
               }
              UpdateDbname();
          }
  
          private void comboBoxTbNameList_SelectedIndexChanged(object sender, EventArgs e)
          {
              CurTable = comboBoxTbNameList.Text;
              UpdateDbname();
          }
  
          private void comboBoxColmunList_SelectedIndexChanged(object sender, EventArgs e)
          {
              CurColNum = comboBoxColmunList.Text;
              UpdateDbname();
          }
  
          private void timer1_Tick(object sender, EventArgs e)
          {
             
          }
      }
  }