So I have been trying to optimize a situation. Essentially, I have a WEB API service that queries a SQL Server Database and then inserts it into a SQLite database on a client machine.
After running some tests. I found that it took 0.50% of the time to load the data from SQL Server into an array of objects in C# and the insert to all of the other time.
Is my insert incredibly inefficient?
Here is my method:
public void addDataToTable()
{
using (SQLiteConnection SQLconn = new SQLiteConnection(SQLiteConnectionString))
{
SQLconn.Open();
SQLiteCommand cmd = SQLconn.CreateCommand();
foreach (Step row in allSteps)
{
cmd.CommandText = "INSERT INTO Step (StepID, EWPID, StepNum, Description, MTOQty, ToDateQty, CraftCode, OriginPkg, SF01, SF10, Updated) VALUES " +
"(@param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param9, @param10, @param11);";
cmd.Parameters.Add(new SQLiteParameter("@param1", row.StepID));
cmd.Parameters.Add(new SQLiteParameter("@param2", row.EWPID));
cmd.Parameters.Add(new SQLiteParameter("@param3", row.StepNum));
cmd.Parameters.Add(new SQLiteParameter("@param4", row.Description));
cmd.Parameters.Add(new SQLiteParameter("@param5", row.MTOQty));
cmd.Parameters.Add(new SQLiteParameter("@param6", row.ToDateQty));
cmd.Parameters.Add(new SQLiteParameter("@param7", row.CraftCode));
cmd.Parameters.Add(new SQLiteParameter("@param8", row.OriginPkg));
cmd.Parameters.Add(new SQLiteParameter("@param9", row.SF01));
cmd.Parameters.Add(new SQLiteParameter("@param10", row.SF10));
cmd.Parameters.Add(new SQLiteParameter("@param11", row.Updated));
cmd.ExecuteNonQuery();
}
}
}
allSteps is an array of objects that coincides with the columns from SQL I'm getting:
public class Step
{
public int StepID { get; set; }
public int EWPID { get; set; }
public int StepNum { get; set; }
public string Description { get; set; }
public double MTOQty { get; set; }
public double ToDateQty { get; set; }
public string CraftCode { get; set; }
public string OriginPkg { get; set; }
public string SF01 { get; set; }
public string SF10 { get; set; }
public string Updated { get; set; }
}
Is there a way to dump an entire Select statement into a SQLite table without the looping?
Aucun commentaire:
Enregistrer un commentaire