Databases
StackOverflow2013
Postsdbo
POExecute SQLite Inserts in several foreach loops in C#?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExecute SQLite Inserts in several foreach loops in C#?
    text
    copied!<p>this is my code I have:</p> <p>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?</p> <p>Someone smart enough to refactor that code, that would be nice :)</p> <pre><code>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(); } </code></pre> <p>UPDATE: The solution that works!</p> <pre><code>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(); } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload