Click here to Skip to main content
14,328,470 members
Rate this:
Please Sign up or sign in to vote.
In this case I'd like to import data from csv file to mysql database. For now i've written method which inserts that file:

1) Select file with extension "*.csv"

2) After select it loads data by dividing commas and continuing if are empty cells.

private DataTable ImportFile()
       {
           DataTable imported_data = new DataTable();

           OpenFileDialog ofd = new OpenFileDialog();
           ofd.Title = "Open csv file";
           ofd.DefaultExt = "*.csv";
           ofd.Filter = "Documents (*.csv)|*.csv";
           ofd.ShowDialog();

           FileInfo fi = new FileInfo(ofd.FileName);
           string FileName1 = ofd.FileName;
           string excel = fi.FullName;

           using(StreamReader sr = new StreamReader(excel))
           {
               string header = sr.ReadLine();
               if (string.IsNullOrEmpty(header))
               {
                   MessageBox.Show("Not found or loaded not correct file.");
                   return null;
               }

               string[] header_columns = header.Split(',');
               foreach(string header_column in header_columns)
               {
                   imported_data.Columns.Add(header_column);
               }

               while (!sr.EndOfStream)
               {
                   string line = sr.ReadLine();

                   if (string.IsNullOrEmpty(linia)) continue;

                   string[] fields = line.Split(',');
                   DataRow imported_row = imported_data.NewRow();

                   for (int i = 0; i < fields.Count(); i++)
                   {
                       imported_row[i] = fields[i];
                   }

                   imported_data.Rows.Add(imported_row);
               }
           }
           return imported_data;
       }


Then the second method is when it has a connection with mysql database it inserts values to that database:

private void save_modules(DataTable imported_data)
{
    string connection = "datasource=localhost;port=3306;username=root;password=";
    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        conn.Open();
        foreach (DataRow importRow in imported_data.Rows)
        {
            string query3 = @"INSERT IGNORE INTO try1.modules (NAME, ID_PROJECT) SELECT @NAME, projekty.ID 
                FROM try1.projects WHERE projects.PROJECT_NAME = @PROJECT_NAME;";

            MySqlCommand cmd = new MySqlCommand(query3, conn);

            cmd.Parameters.AddWithValue("@NAME", importRow["NAME"]);
            cmd.Parameters.AddWithValue("@PROJECT_NAME", importRow["PROJECT_NAME"]);

            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }  
    MessageBox.Show("Imported to database");
}


And event when clicks to btn_import_projects.

private void btn_import_projects_Click(object sender, EventArgs e)
{
        DataTable imported_data = ImportFile();
        save_modules(imported_data);
        frm2.loaddataalldatagridview();
}


when I compiled connected from 3 codes lines to 1 code i had an exception error:

Quote:
System.ArgumentException: „Column NAME does not belong to table .”


And shows at the line of code:

cmd.Parameters.AddWithValue("@NAME", importRow["NAME"]);


I have a question how to fix this error? Should i change the code lines form
cmd.Parameters.AddWithValue
to
cmd.Parameters.Add?
Any ideas? Thx in advance.

What I have tried:

There is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace ControlDataBase
{
    public partial class Import_data_mysql : Form
    {
        public Import_data_mysql()
        {
            InitializeComponent();
        }
        New_Tables frm2 = (New_Tables)Application.OpenForms["New_Tables"];

        private DataTable ImportFile()
        {
            DataTable imported_data = new DataTable();

            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Open csv file";
            ofd.DefaultExt = "*.csv";
            ofd.Filter = "Documents (*.csv)|*.csv";
            ofd.ShowDialog();

            FileInfo fi = new FileInfo(ofd.FileName);
            string FileName1 = ofd.FileName;
            string excel = fi.FullName;

            using(StreamReader sr = new StreamReader(excel))
            {
                string header = sr.ReadLine();
                if (string.IsNullOrEmpty(header))
                {
                    MessageBox.Show("Not found or loaded not correct file.");
                    return null;
                }

                string[] header_columns = header.Split(',');
                foreach(string header_column in header_columns)
                {
                    imported_data.Columns.Add(header_column);
                }

                while (!sr.EndOfStream)
                {
                    string line = sr.ReadLine();

                    if (string.IsNullOrEmpty(linia)) continue;

                    string[] fields = line.Split(',');
                    DataRow imported_row = imported_data.NewRow();

                    for (int i = 0; i < fields.Count(); i++)
                    {
                        imported_row[i] = fields[i];
                    }

                    imported_data.Rows.Add(imported_row);
                }
            }
            return imported_data;
        }

        private void save_modules(DataTable imported_data)
        {
            string connection = "datasource=localhost;port=3306;username=root;password=";
            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                conn.Open();
                foreach (DataRow importRow in imported_data.Rows)
                {
                    string query3 = @"INSERT IGNORE INTO try1.modules (NAME, ID_PROJECT) SELECT @NAME, projekty.ID 
                        FROM try1.projects WHERE projects.PROJECT_NAME = @PROJECT_NAME;";

                    MySqlCommand cmd = new MySqlCommand(query3, conn);

                    cmd.Parameters.AddWithValue("@NAME", importRow["NAME"]);
                    cmd.Parameters.AddWithValue("@PROJECT_NAME", importRow["PROJECT_NAME"]);

                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }  
            MessageBox.Show("Imported to database");
        }

        private void btn_import_projects_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable imported_data = ImportFile();
                save_modules(imported_data);
                frm2.loaddataalldatagridview();
            }

            catch
            {
                MessageBox.Show("Select invalid file to import data.");
            }            
        }
    }
}
Posted
Updated 14-Jul-19 8:04am
Rate this:
Please Sign up or sign in to vote.

Solution 1

PROJECT_NAME is a "parameter" in this context; however, NAME is not; NAME is simply a column name in this case.

SELECT @NAME, projekty.ID FROM try1.projects ...
SELECT NAME, projekty.ID FROM try1.projects ...


cmd.Parameters.AddWithValue("@NAME", importRow["NAME"]);
cmd.Parameters.AddWithValue("@PROJECT_NAME", importRow["PROJECT_NAME"]);
   
v2
Comments
Member 10696161 9-Jul-19 14:45pm
   
But how? After changing form @NAME to NAME in sql command and commenting cmd.Parameters.AddWithValue("@NAME", importRow["NAME"]);
then shows the same exception: this time System.ArgumentException: „Column PROJECT_NAME does not belong to table.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Allright in yesterday i was checking about how in csv file values are imported into datatable. I realized that there seperated by semicolons for example: "LOVE;" not by commas "LOVE,".

So the problem was about two lines of codes:

string[] header_columns = header.Split(',');
string[] fields = line.Split(',');


I had to modify to semicolons:

string[] header_columns = header.Split(';');
string[] fields = line.Split(';');


And it have been worked. Thx for any help anyway. :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100