csv file insert into MYSQL tables using c#

Question

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.

All the csv files have the same format:

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
....
....  

Grade12 table:

Id  StudentName  Score  DateTimeId

Datetime table:

Id  DateTime

When I import the csv file and insert into grade12 table, I want the outcome to be like:

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
....
....

and for the datetime table:

Id    DateTime
1     2012-04-26 12:20:00
2     2012-05-16 11:20:00
....
....

so far I've tried to

1. read the first line of csv (remove the first element "Name") and insert into StudentName column.
2. read the second line of csv (remove the first element "DateTime data ex: 4/26/2012") and insert into Score column.
3. 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.

my c# 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 < studentNames.Length; i++)
                {
                    MySqlParameter param = insertCommand.Parameters[0];
                    param.Value = studentNames[i];
                }

                for (int i = 0; i < 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 < datetime.Length; i++)
                {
                    MySqlParameter param = insertCommand.Parameters[2];
                    param.Value = datetime[i];
                }
                insertCommand.ExecuteNonQuery();
            }                
        }

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.

Answer 1

I'm afraid I don't have time to write out any concrete code samples, but my personal approach would be to split the procedure in 2 parts.

The first part will parse the CSV files and put their content into classes and lists of those classes to get the data in a format that can be iterated over. This allows you to use a solid data model in later steps, instead of having to construct the SQL query live while parsing data. This will also help with assuring that the CSV is in a correct format.

The second part runs after that and uses the objects made earlier to create and execute the SQL Queries. Since you have already parsed the data from the CSV in a more flexible format, this should be a lot simpler.