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:
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.
UPDATE
for the case for only 1 column the code:
private void save_projects(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 query2 = "INSERT IGNORE INTO try1.projects(PROJECT_NAME) VALUES (@PROJECT_NAME);";
MySqlCommand cmd = new MySqlCommand(query2, conn);
cmd.Parameters.AddWithValue("@PROJECT_NAME", importRow["PROJECT_NAME"]);
cmd.ExecuteNonQuery();
}
conn.Close();
}
MessageBox.Show("Imported to database");
}
It works without any small error.
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(line)) 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.");
}
}
}
}
Ok. Responding to @soohoonigan advice I had to change only two lines of code:
string[] header_columns = header.Split(',');
string[] fields = line.Split(',');
After finding out that in .csb file is seperated with these ";" signs so i've changed these lines to this:
string[] header_columns = header.Split(';');
string[] fields = line.Split(';');
And works correctly. Thanks a alot mates. :)