Hi to all,
I'm basically trying to copy a set of tables from a datasource to a sqlite db.
In order to do that, I created a piece of code that dynamically build a parametrized insert
for each table passed and
and uses it.
The code is working, but, even for a beginner like me, doesn't look
very good, especially in the way parameters are added.
//get the number of colums for tablename
NumParams = getNumPars(tablename)
//get a string containing the appropriate select for the source table
sqls_select = getSqlSelect(tablename)
//get a string containing the appropriate insert for the destination table ,i.e. something like
// insert into tablename [field1],[field2].... VALUES(@param1, @param2, ....)
sqll_insert = getSqInsert(tablename)
SqlCommand mySqlServerCommand = new SqlCommand(sqls_select, mySqlServerConnection);
mySqlServerConnection.Open();
// Create the DataReader to retrieve data
using (SqlDataReader dr = mySqlServerCommand.ExecuteReader())
{
//start transaction
mySqLiteConnection.Open();
myCommandL.CommandText = "BEGIN;";
myCommandL.ExecuteNonQuery();
myCommandL.CommandText = sqll_insert;
while (dr.Read())
{
for (int i = 1; i < NumParams + 1; i++)
{
myCommandL.Parameters.AddWithValue("@param" + i, dr[i - 1]);
}
myCommandL.ExecuteNonQuery();
}
//end transaction
myCommandL.CommandText = "END;";
myCommandL.ExecuteNonQuery();
mySqLiteConnection.Close();
}
mySqlServerConnection.Close();
Besides that, the code isa bit slow.... 10000 record per minute on windows ce 5.0
Any suggestion/comments from the experts?
Thank you.