Execute SQLite Inserts in several foreach loops in C# ?

Question

Hello everybody,

this is my code I have:

I have read that setting up the CommandText should happen only once and not in a loop... but how do I then get the individually item data from the foreach?

Someone smart enough to refactor that code, that would be nice :)

using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction())
{  
    using (SQLiteCommand com = new SQLiteCommand(DataAccess.ConnectionManager))
    {
        // How can I add the parameters here if they are only known in the foreach loop?
        com.Parameters.Add(new SQLiteParameter("@date", day.SchooldayDate));
        com.Parameters.Add(new SQLiteParameter("@periodnumber", period.PeriodNumber));
        com.Parameters.Add(new SQLiteParameter("@schoolclasscode", period.SchoolclassCode));


        foreach (var week in weekList)
        {
            foreach (var day in week.Days)
            { 
                foreach (var period in day.Periods)
                {
                    com.CommandText = "Insert into tablename (date,periodnumber,schoolclasscode) Values (@date,@periodnumber,@schoolclasscode)";

                }
            }
        }

        com.ExecuteNonQuery();
    }
    trans.Commit();                
}

UPDATE: The solution that works!

using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction())
            {
                using (SQLiteCommand com = new SQLiteCommand(DataAccess.ConnectionManager))
                {
                    com.CommandText = "Insert into lessonday (lessondate,lessonnumber,schoolclasscode) VALUES (@lessondate,@lessonnumber,@schoolclasscode)";

                    SQLiteParameter p1 = new SQLiteParameter("@lessondate", DbType.DateTime);
                    SQLiteParameter p2 = new SQLiteParameter("@lessonnumber", DbType.Int32);
                    SQLiteParameter p3 = new SQLiteParameter("@schoolclasscode", DbType.String);

                    com.Parameters.Add(p1);
                    com.Parameters.Add(p2);
                    com.Parameters.Add(p3);

                    foreach (var week in weekList)
                    {
                        foreach (var day in week.Days)
                        {
                            p1.Value = day.SchooldayDate;
                            foreach (var period in day.Periods)
                            {
                                p2.Value = period.PeriodNumber;
                                p3.Value = period.SchooclassCode;

                                com.ExecuteNonQuery();
                            }
                        }
                    }
                }
                trans.Commit();
            }
        }

Answer 1

Well, you could just move the loops:

using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction()) 
{  
  foreach (var week in weekList)
  {
    foreach (var day in week.Days)
    { 
      foreach (var period in day.Periods)
      {
        using (SQLiteCommand com = new SQLiteCommand(...))
        {   
          com.Parameters.Add(new SQLiteParameter(...));
          com.Parameters.Add(new SQLiteParameter(...));
          com.Parameters.Add(new SQLiteParameter(...));
          com.CommandText = "Insert into ...";
          com.ExecuteNonQuery();
        }
      }
    }
  }
  trans.Commit();                
} 

However, that seems a little ugly to me. I don't know how the SQLite provider will behave, but I think it would be at least worth trying to create the SQLiteCommand as per your original code, adding the parameters but not their values... and then moving the ExecuteNonQuery code into the innermost foreach loop. Something like this:

using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction())
{  
  using (SQLiteCommand com = new SQLiteCommand(DataAccess.ConnectionManager))
  {   
    com.CommandText = "Insert into ...";
    SQLiteParameter p1 = com.Parameters.Add(new SQLiteParameter(...));
    SQLiteParameter p2 = com.Parameters.Add(new SQLiteParameter(...));
    SQLiteParameter p3 = com.Parameters.Add(new SQLiteParameter(...));
    foreach (var week in weekList)
    {
      p1.Value = week;
      foreach (var day in week.Days)
      { 
        p2.Value = day;
        foreach (var period in day.Periods)
        {
          p3.Value = period;               
          com.ExecuteNonQuery();
        }
      }
    }
  }
  trans.Commit();                
}

Just to reiterate, I don't know if that will work - but I'd hope it would.