How do I batch update a sqlite database with a single table

Question

I have some questions in my work.

I'm writing an information management system that displays information from a database.

One of the features in the software is the ability to export the displayed information as EXCEL, and then the user can modify the information in EXCEL, for example, in the name column, change Jack to Jason, Marry to Larry.Then save the Excel sheet, and then import it into the software, so that all the changes made by users are also modified in the database.

I've implemented most of this functionality, but ran into problems with the last batch update.I transformed the imported Excel into a table with the same structure as the database table, and then implemented it by using adapt.update (dt). However, I found that it did not update the user's changes in Excel to the database, but directly inserted the information of the imported Excel into the database, which also led to the duplication of information in the database.The implementation code for adapter. Update () is shown below.Where dt is the table transformed by the imported Excel.

public static bool BatchUpdate("select * from MYDATABASE",DataTable dt,ref string error)
{
    if (dt.GetChanges().Rows.Count == 0) return true;
    SQLiteConnection conn = SQLiteHelper.GetConnection();
    conn.Open();
    SQLiteTransaction t = conn.BeginTransaction();
    using (SQLiteCommand selectcommand = new SQLiteCommand(selectSql, conn, t))
    {
        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter())
        {
            adapter.SelectCommand = selectcommand;
            SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);                   
            adapter.Update(dt);
            dt1.AcceptChanges();          
        }
        t.Commit();
        return true;
    }
}

Thank you, everyone. I appreciate every suggestion.

Answer 1

Each DataRow has four possible states:

• Unchanged

• New

• Deleted

• Modified

When you insert the rows you get from the Excel sheet into an empty DataTable, all of the rows will have a state of New and will therefore be inserted into the database.

You have the following options:

• Select the data from the database into the DataTable, then update insert or delete from the DataTable according to what you read from your Excel sheet, then call your BatchUpdate function.

• Issue an update statement for all the rows that you have in the Excel sheet. For those rows who's number of rows affected is zero, issue an insert statement. Finally select all the primary keys from the database and delete all those that you do not have in your Excel sheet.