合并两个SQLite数据库文件（C# .NET）

Question

合并两个SQLite数据库文件（C# .NET）

6

我正在使用C#/.NET和SQLite的C#包装器。我尝试将两个SQLite数据库合并在一起，同时排除重复项。

我找到了这个链接，它被几个不同的论坛问题引用：http://old.nabble.com/Attempting-to-merge-large-databases-td18131366.html 我尝试了下面的查询，按照我提供的链接进行了结构化，但结果会导致异常，数据库根本没有合并，并且原始数据库没有改变。

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

这是我的查询代码。

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

我的问题是，我做错了什么？有人熟悉插入命令吗？我不确定它是否会排除重复项，这是我需要的。

- CODe

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Binil · Accepted Answer

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

尝试这样做

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