我正在使用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(); } }
我的问题是,我做错了什么?是否有人熟悉insert命令?我不确定它是否会根据需要排除重复项.
在sqlite中附加数据库时,需要在单个Connection / 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(); } }