title: csv file insert into MYSQL tables using c#
body: <p>I am using c# to parse all .csv files in a directory and insert each file into separate MYSQL tables. Parsing each file-name works, but I have a problem with insertion.</p>
  
  <p>All the csv files have the same format:</p>
  
  <pre><code>Name,Mike,John,Parker,Joanne,Claire
  4/26/2012 12:20,88.1,98.2,87.4,92.5,78.2
  5/16/2012 11:20,78.1,78.2,76.4,86.5,97.2
  ....
  ....  
  </code></pre>
  
  <p>Grade12 table:</p>
  
  <pre><code>Id  StudentName  Score  DateTimeId
  </code></pre>
  
  <p>Datetime table:</p>
  
  <pre><code>Id  DateTime
  </code></pre>
  
  <p>When I import the csv file and insert into grade12 table, I want the outcome to be like:</p>
  
  <pre><code>Id  StudentName  Score  DateTimeId
  1      Mike       88.1      1
  2      John       98.2      1
  3      Parker     87.4      1
  4      Joanne     92.5      1
  5      Claire     78.2      1
  6      Mike       78.1      2
  7      John       78.2      2
  8      Parker     76.4      2
  9      Joanne     86.5      2
  10     Claire     97.2      2
  ....
  ....
  </code></pre>
  
  <p>and for the datetime table:</p>
  
  <pre><code>Id    DateTime
  1     2012-04-26 12:20:00
  2     2012-05-16 11:20:00
  ....
  ....
  </code></pre>
  
  <p>so far I've tried to </p>
  
  <ol>
  <li>read the first line of csv (remove the first element "Name") and insert into StudentName column.</li>
  <li>read the second line of csv (remove the first element "DateTime data ex: 4/26/2012") and insert into Score column.</li>
  <li>I'm supposed to use DateTime data of each row and insert into datetime Table and grade12 table foreign keys to it, but honestly I am quite lost. </li>
  </ol>
  
  <p>my c# code: </p>
  
  <pre><code>public void process(string csvFile, string grade)
      {
          using (MySqlCommand insertCommand = connection.CreateCommand())
          {
              insertCommand.CommandText =
                  @"INSERT INTO " + grade + "(StudentName, Score, DateTimeId) VALUES (@StudentName, @Score, @DateTimeId)";
  
              insertCommand.Parameters.Add(new MySqlParameter("@StudentName", "STRING"));
              insertCommand.Parameters.Add(new MySqlParameter("@Score", "DECIMAL"));
              insertCommand.Parameters.Add(new MySqlParameter("@DateTimeId", "INT32"));
  
              string[] lines = System.IO.File.ReadAllLines(csvFile);
  
              foreach (string line in lines)
              {
                  string trimedLine = line.Trim();
  
                  if (line.StartsWith("Name"))
                  {
                      studentNames = trimedLine.Split(new[] { ',' }).Skip(1).ToArray();
                      continue;
                  }                    
                      eachRow = trimedLine.Split(new[] { ',' }).Skip(1).ToArray();
  
                  for (int i = 0; i &lt; studentNames.Length; i++)
                  {
                      MySqlParameter param = insertCommand.Parameters[0];
                      param.Value = studentNames[i];
                  }
  
                  for (int i = 0; i &lt; eachRow.Length; i++)
                  {
                      MySqlParameter param = insertCommand.Parameters[1];
                      param.Value = eachRow[i];
                  }
  
                  //I am not sure about this part. This is random data for testing purpose. 
                  int[] datetime = { 1, 2, 3, 4, 5, 6, 7, 8 };
                  for (int i = 0; i &lt; datetime.Length; i++)
                  {
                      MySqlParameter param = insertCommand.Parameters[2];
                      param.Value = datetime[i];
                  }
                  insertCommand.ExecuteNonQuery();
              }                
          }
  </code></pre>
  
  <p>Spent quite a lot of times, but still wandering around. Data goes into the table, but they don't look good. Any Help is appreciated.</p>