How to import csv file into mysql database table

Question

 

 

when i click choose file button i get file dialog box upper with only csv file .and i select some csv file the file path display in my textbox1 and when i click submit button i need to insert the file data into MySql database table .

i had a problem in bulkcopy .how i insert the csv file data into MySql database table. my table name is "aster_user"

here my code:

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Configuration;

using System.Data.OleDb;

using System.Data.SqlClient;

using MySql.Data.MySqlClient;

namespace SampleFileOpen

{

public partial class Form1 : Form

{

String MyCon = "SERVER=******;" +

"DATABASE=*****;" +

"UID=root;" +

"PASSWORD=*****;" + "Convert Zero Datetime = True";

public Form1()

{

InitializeComponent();

}

private void btnChooseFile_Click(object sender, EventArgs e)

{

OpenFileDialog dlg = new OpenFileDialog();

dlg.Filter = "Text files | *.csv";

if (dlg.ShowDialog() == DialogResult.OK)

{

string fileName;

fileName = dlg.FileName;

textBox1.Text = fileName;

}

}

private void btnSubmit_Click(object sender, EventArgs e)

{

string CSVpath = textBox1.Text;

var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.csv");

string File_Name = "aster_user";

string MyCon = ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString;

for (int i = 0; i < AllFiles.Length; i++)

{

File_Name = AllFiles[i].Name;

DataTable dt = new DataTable();

using (MySqlConnection con = new MySqlConnection(string.Format(MyCon, CSVpath)))

{

using (MySqlDataAdapter da = new MySqlDataAdapter("select * from [" + File_Name + "]", con))

{

da.Fill(dt);

}

}

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyCon))

{

bulkCopy.ColumnMappings.Add(1, "Column1");

bulkCopy.ColumnMappings.Add(2, "Column2");

bulkCopy.ColumnMappings.Add(4, "Column3");

bulkCopy.ColumnMappings.Add(5, "Column4");

bulkCopy.ColumnMappings.Add(8, "Column5");

bulkCopy.DestinationTableName = "aster_user";

bulkCopy.BatchSize = dt.Rows.Count;

bulkCopy.WriteToServer(dt);

bulkCopy.Close();

}

}

}

}

}

 

 

i had a problem in submit button i did some mistake in there ...

iam searching lot more please help me please.