删除SQLite数据库中不在DataTable中的所有值 [英] Delete all values in SQLite Database that are not in DataTable

问题描述

所以，我有一个DataTable包含我需要保留的不同数据条目。我现在需要做的是删除SQLite数据库中不在DataTable中的所有行/条目（我不知道正确的术语）。 

So, I have a DataTable containing different Data entries that I need to keep. What I now need to do, is delete all of the Rows/Entries (I don't know the proper term) from the SQLite Database that aren't in the DataTable. 

再次，澄清一下，我需要删除SQLT数据库中DataTable中不 t的所有行。 DataTable值是
keep 所需的值。

Again, to clarify, I need to delete all rows in the SQLite Database that are not in the DataTable. The DataTable values are the values I need to keep.

这就是我所拥有的far：

Here is what I have so far:

public void RemoveSystemOnlineHistory()
        {
            string query = "SELECT StoreNum, percent, dt FROM StoreOnlineHistory WHERE dt < date('now', '-14 days')";
            DataTable dt = fillDataTable(query);
            DataView dv = dt.DefaultView;
            dv.Sort = "StoreNum asc";
            dt = dv.ToTable();
            DeleteFromDataTable(dt);

            //For every row in the DataTable, delete it from the Database if there is an entry that matches
            string sql = "Delete from StoreOnlineHistory where StoreNum=@StoreNum and dt=@dt";

            using (SQLiteConnection c = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand { Connection = c })
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(new SQLiteParameter()
                    {
                        ParameterName = "@StoreNum",
                        DbType = DbType.String
                    });
                    cmd.Parameters.Add(new SQLiteParameter()
                    {
                        ParameterName = "@dt",
                        DbType = DbType.String
                    });

                    try
                    {
                        c.Open();
                        Console.WriteLine("--- Connection Open ---");

                        foreach (DataRow entry in dt.Rows)
                        {
                            cmd.Parameters["@StoreNum"].Value = entry.ItemArray[0].ToString();
                            cmd.Parameters["@dt"].Value = entry.Field<string>("dt");
                            cmd.ExecuteNonQuery();

                            Console.WriteLine("\nDeleted - " + entry.ItemArray[0].ToString() + " / " + entry.ItemArray[2].ToString());
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error");
                    }
                    c.Close();
                    Console.WriteLine("\n--- Connection Closed ---");
                    Console.ReadKey();
                }
            }
        } //end RemoveSystemOnlineHisotry()

但问题是，我正在删除我需要保留的值（谢天谢地，我正在使用测试数据库和表，真实的副本）。我很困惑，我应该如何翻转它，并保持DataTable中的行而不是删除它们。
任何帮助都将受到赞赏 

But the problem is, I was deleting the values that I need to KEEP (thankfully I was using a testing DataBase and table, a copy of the real thing). I am puzzled as to how I should flip this around, and keep the rows in the DataTable instead of deleting those. Any help would be appreciated 

推荐答案

我的第一印象可能是尝试更改我的sqlite声明？我可以说从DB中删除值不在DataTable中吗？ （显然用SQLite术语，但你知道我的意思）

My first impression was maybe try changing my sqlite statement? Can I say Delete from DB where values are not in DataTable? (obviously in SQLite terms, but you know what I mean)

这篇关于删除SQLite数据库中不在DataTable中的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！