Opening and importing Multiple files into DataGridView + database

[M1kkelZU]

I'm trying to add a function now that lets the user open 1 or more files and import them to the database and dataGridView. Now the way it is now (should) work. But when it has finished with FILE1, it won't add FILE2 as it then gives me an error that the Column Date Already exists. Any ideas?


My code is below

private void btnOpenLog_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Log Files (*.log)|*.log";
            openFileDialog1.Multiselect = true;
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.RestoreDirectory = false;
 
            //string initDir = "C:/Logs";
 
            //openFileDialog1.InitialDirectory = !Directory.Exists(initDir) ? Path.GetPathRoot(Environment.SystemDirectory) : initDir;
 
            if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                foreach (String file in openFileDialog1.FileNames)
                {
                    try
                    {
                        dataGridView1.DataSource = LoadTable(file);
 
                        using (SQLiteConnection conn = new SQLiteConnection(connString))
                        {
                            using (SQLiteCommand command = new SQLiteCommand())
                            {
                                conn.Open();
 
                                command.Connection = conn;
                                command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
 
                                command.Parameters.Add("@Date", DbType.String);
                                command.Parameters.Add("@LogName", DbType.String);
                                command.Parameters.Add("@Channel", DbType.String);
                                command.Parameters.Add("@DateRecord", DbType.String);
                                command.Parameters.Add("@SizeInBytes", DbType.Int64);
 
                                foreach (DataGridViewRow row in dataGridView1.Rows)
                                {
                                    if (!row.IsNewRow)
                                    {
                                        command.Parameters["@Date"].Value = row.Cells[0].Value;
                                        command.Parameters["@LogName"].Value = row.Cells[1].Value;
                                        command.Parameters["@Channel"].Value = row.Cells[2].Value;
                                        command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
                                        command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
                                        command.ExecuteNonQuery();
                                    }
                                    command.CommandType = CommandType.Text;
                                }
                            }
                            conn.Close();
                        }
                    }
                    catch (Exception err)
                    {
                        MessageBox.Show("Error:  " + err.Message, "Program Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
 
        private DataTable LoadTable(string filename)
        {
            dTable.Columns.Add(new DataColumn("Date", typeof(string)));
            dTable.Columns.Add(new DataColumn("LogName", typeof(string)));
            dTable.Columns.Add(new DataColumn("Channel", typeof(string)));
            dTable.Columns.Add(new DataColumn("DateRecord", typeof(string)));
            dTable.Columns.Add(new DataColumn("SizeInBytes", typeof(float)));
 
            DataRow dr;
 
            String sLine = new StreamReader(filename).ReadToEnd();
 
            if (sLine != null)
            {
                foreach (string regel in Regex.Split(sLine, "\r\n"))
                {
                    if (!string.IsNullOrEmpty(regel))
                    {
                        dr = dTable.NewRow();
 
                        string[] splitregel = Regex.Split(regel.Replace("|", ";"), ";");
 
                        dr[0] = splitregel[0];
                        dr[1] = splitregel[1];
                        dr[2] = splitregel[2];
                        dr[3] = splitregel[3];
                        dr[4] = splitregel[4];
 
                        dTable.Rows.Add(dr);
                    }
                }
            }
            return dTable;
        }

[Rabbit]

That's because your LoadTable function runs again on your second file. Which means it tries to add the same columns it added the first time.

[M1kkelZU]

Ah ok. So what would be a way for me to keep running if there are multiple files? I was thinking of before reading the file that it would merge all the files into 1 file, but I'd think thats a huge workaround

[Rabbit]

Add those columns only once. Outside of the loop.

[M1kkelZU]

 if (sLine != null)
            {
                foreach (string regel in Regex.Split(sLine, "\r\n"))
                {
                    dr = dTable.NewRow();
                    if (!string.IsNullOrEmpty(regel))
                    {
 
                        string[] splitregel = Regex.Split(regel.Replace("|", ";"), ";");
 
                        dr[0] = splitregel[0];
                        dr[1] = splitregel[1];
                        dr[2] = splitregel[2];
                        dr[3] = splitregel[3];
                        dr[4] = splitregel[4];
                    }
                        dTable.Rows.Add(dr);
                }  
            }

So like this?

EDIT: Read it wrong. But if I look at it, my LoadTable method adds the Columns before the loop even starts, or am I looking at it in a wrong perspective?

[Rabbit]

No, it's in a loop.

In your original post, you call the function on line 19. It's in the loop that starts on line 15.

[M1kkelZU]

But if I get it out of the foreach loop it then says that file doesn't exist as I declare it in my foreach loop. So where would it have to move it?

[Rabbit]

I don't mean the entire function. In post #4 I said

Add those columns only once. Outside of the loop.

Just the lines that add the columns needs to be outside the loop.

[M1kkelZU]

Sorry for the late response, was away for the Easter weekend.

I still don't quite get what you mean.
EDIT I now see what you mean, in my LoadTable Method the section

dTable.Columns.Add(new DataColumn("Date", typeof(string)));
            dTable.Columns.Add(new DataColumn("LogName", typeof(string)));
            dTable.Columns.Add(new DataColumn("Channel", typeof(string)));
            dTable.Columns.Add(new DataColumn("DateRecord", typeof(string)));
            dTable.Columns.Add(new DataColumn("SizeInBytes", typeof(float)));

is being run through again which causes the addition of a second column that exists.

Now I have no clue how to edit this into a fact that it works.

[Rabbit]

Move those lines of code out before the loop.

[M1kkelZU]

I was thinking, if I add the columns once already wouldn't I just be able to use something like

dTable.Columns.Clear();

before starting the loop or at the end of the LoadTable method?

[M1kkelZU]

Fixed my issue lol.

It was a simple solution in the end:

 private DataTable LoadTable(string filename)
        {
            if (dTable.Columns.Count != 5)
            {
                dTable.Columns.Add(new DataColumn("Date", typeof(string)));
                dTable.Columns.Add(new DataColumn("LogName", typeof(string)));
                dTable.Columns.Add(new DataColumn("Channel", typeof(string)));
                dTable.Columns.Add(new DataColumn("DateRecord", typeof(string)));
                dTable.Columns.Add(new DataColumn("SizeInBytes", typeof(float)));
            }
            else
            {
                //huehue
            }
 
            DataRow dr;
 
            String sLine = new StreamReader(filename).ReadToEnd();
 
            if (sLine != null)
            {
                foreach (string regel in Regex.Split(sLine, "\r\n"))
                {
                    if (!string.IsNullOrEmpty(regel))
                    {
                        dr = dTable.NewRow();
 
                        string[] splitregel = Regex.Split(regel.Replace("|", ";"), ";");
 
                        dr[0] = splitregel[0];
                        dr[1] = splitregel[1];
                        dr[2] = splitregel[2];
                        dr[3] = splitregel[3];
                        dr[4] = splitregel[4];
 
                        dTable.Rows.Add(dr);
                    }
                }
            }
            return dTable;
        }

basically I check to see if the columns are made, if not it makes them. from there on it does the rest of its work.

[Rabbit]

That will work. But here's the thing, the check is unnecessary. In the original code, all you had to do was move lines 63-67 to before line 15.