Writing datatable to database file, one record at a time

Question

I want to write a C# program that will read a row from a datatable (named loadDT) and update a database file (named Forecasts.mdb).

My datatable looks like this (each day's value is a number representing kilowatts usage forecast):

Hour  Day1  Day2  Day3  Day4  Day5  Day6  Day7
   1   519   520   524   498   501   476   451 

My database file looks like this:

Day   Hour  KWForecast
  1      1         519
  2      1         520
  3      1         524

... and so on.

Basically, I want to be able to read one row from the datatable, and then extrapolate that out to my database file, one record at a time. Each row from the datatable will result in seven records written to the database file. Any ideas on how to go about this? I can connect to my database, the connection string works, and I can update and delete from the database. I just can't wrap my head around how to do this one record at a time.

Answer 1

Have you tried looping through the records in the input database and for each record performing seven inserts into the output database?

Depending a lot on what APIs you're using, it will have this kind of pattern:

var inputRecordSet = // connect somehow
var outputRecordSet = // ditto

while (!inputRecordSet.EOF)
{
    outputRecordSet.Hour = inputRecordSet.Hour;

    outputRecordSet.Day1 = inputRecordSet.Day;
    outputRecordSet.KWForecast = inputRecordSet.Day1;
    outputRecordSet.Insert();

    outputRecordSet.Day2 = inputRecordSet.Day;
    outputRecordSet.KWForecast = inputRecordSet.Day2;
    outputRecordSet.Insert();

    // and so on... for seven days

   inputRecordSet.MoveNext();
}

Answer 2

One way is to put the values in an array, and the loop over it:

while (reader.Read()) {

  int hour = reader.ReadInt32(0);
  int[] days = {
    reader.ReadInt32(1),
    reader.ReadInt32(2),
    reader.ReadInt32(3),
    reader.ReadInt32(4),
    reader.ReadInt32(5),
    reader.ReadInt32(6),
    reader.ReadInt32(7)
  };

  for (int day = 0; day < days.Length; day++) {
    // insert day+1, hour and days[day] in the database
  }

}