Merging two SQLite database files (C# .NET)

Question

I'm using C#/.NET with the C# wrapper for SQLite. I'm attempting to merge two SQLite databases together while excluding duplicates.

I found this, which is referenced from a few different forum questions. http://old.nabble.com/Attempting-to-merge-large-databases-td18131366.html

I've tried the below queries, which I structured from the link I provided, but they result in exceptions, the databases are not merged at all, and the original database is not changed whatsoever.

attach 'c:\test\b.db3' as toMerge;   
insert into AuditRecords select * from toMerge.AuditRecords; 

Here is my query code.

public void importData(String fileLoc)
    {
        SQLiteTransaction trans;
        string SQL = "ATTACH '" + fileLoc + "' AS TOMERGE";
        SQLiteCommand cmd = new SQLiteCommand(SQL);
        cmd.Connection = connection;
        connection.Open();
        trans = connection.BeginTransaction();
        int retval = 0;
        try
        {
            retval = cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            trans.Rollback();
            MessageBox.Show("An error occurred, your import was not completed.");
        }
        finally
        {
            trans.Commit();
            cmd.Dispose();
            connection.Close();
        }

        SQL = "INSERT INTO SUBCONTRACTOR SELECT * FROM TOMERGE.SUBCONTRACTOR";
        cmd = new SQLiteCommand(SQL);
        cmd.Connection = connection;
        connection.Open();
        trans = connection.BeginTransaction();
        retval = 0;
        try
        {
            retval = cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            trans.Rollback();
            MessageBox.Show("An error occurred, your import was not completed.");
        }
        finally
        {
            trans.Commit();
            cmd.Dispose();
            connection.Close();
        }
    }

My question is, what am I doing wrong? And is anyone familiar with the insert command? I'm unsure if it will exclude duplicates as I need.

Solution

When you attach a database in SQLite, you need execute every statement (whether insert, update, delete) in a single Connection/Transaction. Dont Close the Connection in between. It should complete in a single Transaction.

try this

public void importData(String fileLoc)
        {
            string SQL = "ATTACH '" + fileLoc + "' AS TOMERGE";
            SQLiteCommand cmd = new SQLiteCommand(SQL);
            cmd.Connection = connection;
            connection.Open();
            int retval = 0;
            try
            {
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                MessageBox.Show("An error occurred, your import was not completed.");
            }
            finally
            {
                cmd.Dispose();
            }

            SQL = "INSERT INTO SUBCONTRACTOR SELECT * FROM TOMERGE.SUBCONTRACTOR";
            cmd = new SQLiteCommand(SQL);
            cmd.Connection = connection;
            retval = 0;
            try
            {
                retval = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                MessageBox.Show("An error occurred, your import was not completed.");
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }

Comments

[CODe]

Great! That worked, but it doesn't remove duplicates. Do you have any advice on how to handle that? Is there another command I can use besides insert? Thanks again for your help.

[Binil]

to avoid duplicates, you can compare two tables like. { SQL = "INSERT INTO SUBCONTRACTOR SELECT * FROM TOMERGE.SUBCONTRACTOR WHERE [YOUR_UNIQUE_ID] NOT IN (SELECT [YOUR_UNIQUE_ID] FROM SUBCONTRACTOR) }

[Jacob Cook]

@Binil I followed along with your answer but was unable to get it working. It kept saying that 'connection' was not defined. I have posted a question asking if there are any solutions that would be more modern or better. I would appreciate it if you could help me fix this issue or answer my question :) stackoverflow.com/questions/59115084/…

[C Perkins]

@JacobCook Many code answers on StackOverflow are not complete, instead they just demonstrate a solution for a specific problem. They will not always contain other necessary code. It is usually assumed that details surrounding the particular problem are familiar to the coder. Your question is about merging databases, so it is assumed that you know how to connect (i.e. open) a database. The connection variable is apparently not defined in this code, so it must be defined and opened elsewhere. If you do not understand why, you will need to study both C# variables and sqlite connections.