none
Delete all values in SQLite Database that are not in DataTable RRS feed

  • Question

  • 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. 

    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.

    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()
    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 
    Monday, August 6, 2018 1:56 PM

All replies

  • 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)
    Monday, August 6, 2018 1:58 PM
  • DataTable's are in memory representation of a table. In you're code you're selecting a bunch of rows. Then you're calling a magic DeleteFromDataTable method that I assume deletes rows from the table? You then enumerate the remaining rows and delete them from the table.

    If you delete a row from a DataTable and then save the changes back to the DB then it'll delete those rows from the DB automatically. If you want to go the other way (don't delete them) then swap the query logic so it deletes the rows you want gone. Then update back to the DB. 

    If you need to run custom delete logic then I'd recommend you build up the list of PK rows that you want to delete and then modify your DELETE clause to delete where PK IN (list of values). It would be a single query instead of multiples.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, August 6, 2018 2:58 PM
    Moderator
  • DataTable's are in memory representation of a table. In you're code you're selecting a bunch of rows. Then you're calling a magic DeleteFromDataTable method that I assume deletes rows from the table? You then enumerate the remaining rows and delete them from the table.

    If you delete a row from a DataTable and then save the changes back to the DB then it'll delete those rows from the DB automatically. If you want to go the other way (don't delete them) then swap the query logic so it deletes the rows you want gone. Then update back to the DB. 

    If you need to run custom delete logic then I'd recommend you build up the list of PK rows that you want to delete and then modify your DELETE clause to delete where PK IN (list of values). It would be a single query instead of multiples.


    Michael Taylor http://www.michaeltaylorp3.net

    The DeleteFromDataTable method deletes all rows in the DataTable that I don't need. Then, I am left to delete all rows in the DataBase that are not in the DataTable. My question is, how should I go about this? What would you recommend I change in my current existing code? I don't know how I would pick and choose what rows I want to delete, because it is everything that I don't have in my DataTable. We are talking maybe 2 million rows of data. I am very new to SQLite in general. 

    Would you recommend changing my DeleteFromDataTable method to instead keep all rows that I need to delete and not store the ones I need? That way I could compare the Database to delete all rows that match those in the DataTable. I didn't do this however because I felt it would be slower because of the storage of so much more data. This is the DeleteFromDataTable method:

            public void DeleteFromDataTable(DataTable table)
            {
                var rows = table.Rows.OfType<DataRow>();
                var stores = rows.GroupBy(r => r.Field<string>(0));
    
                foreach (var store in stores)
                {
                    //Enumerate the rows associated with the store
                    DataRow previousRow = null;
                    var autoFreezeRow = false;
    
                    //Track the rows we don't want to remove
                    var frozenRows = new List<DataRow>();
    
                    foreach (var row in store)
                    {
                        //If the current row should be auto frozen then do that now
                        if (autoFreezeRow)
                        {
                            frozenRows.Add(row);
                            autoFreezeRow = false;
                        }
                        else if (previousRow != null)
                        {
                            //If the previous and current rows don't have the same value then we'll freeze the previous, current and next rows                
                            //so they won't get removed later
                            if (previousRow.Field<double>(1) != row.Field<double>(1) && previousRow.Field<string>(0).Equals(row.Field<string>(0)))
                            {
                                frozenRows.Add(previousRow);
                                frozenRows.Add(row);
                                autoFreezeRow = true;
                            };
                        };
    
                        //Next
                        previousRow = row;
                    };
    
                    //Make another pass through and any rows that aren't frozen are marked for deletion
                    var rowsToDelete = store.Except(frozenRows);
                    foreach (var row in rowsToDelete)
                        row.Delete();
                }
            }

    Monday, August 6, 2018 3:05 PM
  • If you're loading that much data then I question the use of a DataTable at all. But assuming your dt variable, after your DeleteFromDataTable call, contains the rows you want to keep then use a DELETE clause with the NOT IN option. Inside the list specify the rows not to delete. I assume this is a smaller subset of data then those that you do want to delete.

    DELETE FROM MyTable
    WHERE MyPrimaryKey NOT IN (@ids)

    For this to work you'll need to capture a unique ID for each row. This eliminates the need for the StoreNum/Dt parameter you're passing now. If you cannot do that then your query is going to get a lot more complicated. At that point a subquery or join may be better. It depends on your table structure.

    Alternatively, this may be better suited for all being done in the DB. You could create a temporary table if needed to manage the rows. If you're having to process a lot of data then keeping it in the DB would be more efficient.


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, August 6, 2018 4:01 PM
    Moderator