有两个数据库文件,**a.db** 和 b.db,**b.db** 的某几个表比** a.db** 相应的表多了一两个字段,b.db里面多了两个表,其余都一样。
现要将** a.db** 的数据复制到 b.db 中,即相应字段的数据替换,有什么好的办法?
目前由于是循环遍历**a.db**表里面的数据,然后写入**b.db**里面,所以速度很慢,有什么高效快速的办法吗?下面贴代码:
从**a**表取数据:
DataSet DS = new DataSet();
string sqlStr = "select TM,BZRQ,CBTM,CKH,KWH,WPH,PH,BZPH,DJP,PPH,MZ,JZ,XS,TMSD,RKZT,CKZT,WPLX,BZ from BZJL";
string filename = FileUrl;
string fullname;
string myAppPath;
fullname = System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase;
myAppPath = System.IO.Path.GetDirectoryName(fullname);
myAppPath = myAppPath.Replace("file:\\", "");
string connString = "Data Source=" + myAppPath + "\\DataBase\\" + filename;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DS = ds;
conn.Close();
}
然后写数据到**b.db**
using (SQLiteConnection conn = new SQLiteConnection(SQLLiteHelper.connString))
{
conn.Open();
SQLiteTransaction sqltran = conn.BeginTransaction();
List<string> sqlList = new List<string>();
int fff = 0;
DbTransaction trans = conn.BeginTransaction();
try
{
if (DS.Tables[0].Rows.Count >= 1)
{
foreach (DataRow dr in DS.Tables[0].Rows)
{
fff++;
List<System.Data.SQLite.SQLiteParameter> listPar = new List<System.Data.SQLite.SQLiteParameter>();
string TM = dr[0].ToString().Trim();
DateTime BZRQ = Convert.ToDateTime(dr[1].ToString());
string CBTM = dr[2].ToString().Trim();
string CKH = dr[3].ToString().Trim();
string KWH = dr[4].ToString().Trim();
string WPH = dr[5].ToString().Trim();
string PH = dr[6].ToString().Trim();
string BZPH = dr[7].ToString().Trim();
string DJP = dr[8].ToString().Trim();
string PPH = dr[9].ToString().Trim();
decimal MZ = decimal.Parse(dr[10].ToString());
decimal JZ = decimal.Parse(dr[11].ToString());
int XS = int.Parse(dr[12].ToString());
string TMSD = dr[13].ToString().Trim();
string RKZT = dr[14].ToString().Trim();
string CKZT = dr[15].ToString().Trim();
string WPLX = dr[16].ToString().Trim();
string BZ = dr[17].ToString().Trim();
string sql = "insert into BZJL (TM,BZRQ,CBTM,CKH,KWH,WPH,PH,BZPH,DJP,PPH,MZ,JZ,XS,TMSD,RKZT,CKZT,WPLX,BZ)" +
" VALUES (@TM,@BZRQ,@CBTM,@CKH,@KWH,@WPH,@PH,@BZPH,@DJP,@PPH,@MZ,@JZ,@XS,@TMSD,@RKZT,@CKZT,@WPLX,@BZ)";
sqlList.Add(sql);
System.Data.SQLite.SQLiteParameter[] par = {
new System.Data.SQLite.SQLiteParameter("@TM", TM),
new System.Data.SQLite.SQLiteParameter("@BZRQ", BZRQ),
new System.Data.SQLite.SQLiteParameter("@CBTM", CBTM),
new System.Data.SQLite.SQLiteParameter("@CKH", CKH),
new System.Data.SQLite.SQLiteParameter("@KWH", KWH),
new System.Data.SQLite.SQLiteParameter("@WPH", WPH),
new System.Data.SQLite.SQLiteParameter("@PH", PH),
new System.Data.SQLite.SQLiteParameter("@BZPH", BZPH),
new System.Data.SQLite.SQLiteParameter("@DJP", DJP),
new System.Data.SQLite.SQLiteParameter("@PPH", PPH),
new System.Data.SQLite.SQLiteParameter("@MZ", MZ),
new System.Data.SQLite.SQLiteParameter("@JZ", JZ),
new System.Data.SQLite.SQLiteParameter("@XS", XS),
new System.Data.SQLite.SQLiteParameter("@TMSD", TMSD),
new System.Data.SQLite.SQLiteParameter("@RKZT", RKZT),
new System.Data.SQLite.SQLiteParameter("@CKZT", CKZT),
new System.Data.SQLite.SQLiteParameter("@WPLX", WPLX),
new System.Data.SQLite.SQLiteParameter("@BZ", BZ)
};
help.ExecuteSqlListPar(conn, sql, par, sqltran);
}
sqltran.Commit();
conn.Close();
}
}
catch
{
sqltran.Rollback();
//throw;
MessageBox.Show("写入超时","温馨提示");
}
}