I have found the solution..
To tackle the problem of difference in table structure, I am maintaining a separate table named dbShema which contains two columns "TableName" and "Columns"; In this table I maintain all the table names and all the column names separated by comma in "Columns" column.
I fetch all tables and its corresponding columns in SQLReader and loop through it to create a dynamic SQLite insert query. Then I execute this query to copy data from source to destiantion DB.
Code:
StringBuilder strSql = new StringBuilder();
strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Source\\TestDB.db' AS SourceDB;");
strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Destination\\TestDB.db' AS DestDB;");
using (SQLiteConnection con = new SQLiteConnection(SourceconnectionString))
{
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM dbSchema", con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
strSql.Append("INSERT INTO DestDB.");
strSql.Append(rdr["TableName"].ToString());
strSql.Append(" (");
strSql.Append(rdr["Columns"].ToString());
strSql.Append(") ");
strSql.Append("SELECT ");
strSql.Append(rdr["Columns"].ToString());
strSql.Append(" FROM SourceDB.");
strSql.Append(rdr["TableName"].ToString());
strSql.Append(";");
var tableName = rdr["TableName"].ToString();
var Columns = rdr["Columns"].ToString();
}
}
}
}
using (SQLiteConnection conn = new SQLiteConnection(DestinationConnectionString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = strSql.ToString();
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
MessageBox.Show("Data dumped successfully ...!!!");
}
}
}
But, I am using SQLReader and stringbuilder for looping and concatenating.
Is there more efficient way to achieve this. ????