我使用C＃/。NET使用C＃包装SQLite的。我试图合并两个SQLite数据库在一起，而排除重复。

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

我发现了这一点，这是从几个不同的论坛提问引用。 http://old.nabble.com/Attempting-to-merge - 大 - 数据库 - td18131366.html

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; 

下面是我的查询code。

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();
        }
    }

我的问题是，我究竟做错了什么？而且是任何人都熟悉的INSERT命令？我不能确定是否会排除重复我所需要的。

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.

推荐答案

在附上的SQLite数据库，则需要执行每个语句（无论是插入，更新，删除）在一个单一的连接/事务。不要关闭之间的连接。它应该完成一个单一的交易。

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.

试试这个

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();
            }
        }