how to insert csv file into mysql database table using c# windows form

Question

5.00/5 (1 vote)

See more:

When i click Submit button .I need to get chosen csv file are upload in to mysql database table

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

                "DATABASE=*****;" +
                "UID=root;" +
                "PASSWORD=******;" + "Convert Zero Datetime = True";

private void btnchoose_Click_1(object sender, EventArgs e)

{

            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Text files | *.csv";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                string fileName;
                fileName = dlg.FileName;
                textBox2.Text = fileName;

  }
private void btnsubmit_Click(object sender, EventArgs e)
        {
            DataTable dtExcel = new DataTable();
            dtExcel.TableName = textBox2.Text;
            string conn = MyCon;
            MySqlConnection con = new MySqlConnection(MyCon);
            con.Open();
            string query = "select userid, password,first_name,last_name,user_group from aster_users";
            MySqlCommand cmd = new MySqlCommand(query, con);
            MySqlDataAdapter data = new MySqlDataAdapter(cmd);
            MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(data);
            data.InsertCommand = cmdBuilder.GetInsertCommand();
            data.Fill(dtExcel);
            MessageBox.Show("data import Sucessfully");
            con.Close();
            textBox2.Text = "   No File Chosen";
          
        }

my coding is wrong. i need to use sqlbulk upload iam searching for that coding
only

help me please.

Posted 20-Nov-15 20:08pm

Member 12078840/divya

Updated 16-Aug-21 18:38pm

v2

Add a Solution

Comments

Anisuzzaman Sumon 21-Nov-15 2:47am

Is your CSV file header consists of the following column name userid, password,first_name,last_name,user_group?If yes then it is too easy.Reply and wait for getting complete solution :)

Member 12078840/divya 21-Nov-15 2:59am

thank you sir for reply .. yes sir it contain userid, password,first_name,last_name,user_group. iam waiting...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sarath kumar.N · Accepted Answer · 2015-11-20T22:29:00

There are lot of solutions on the internet. I found one solution for easy understanding. Please make sure your columns in the CSV file as same as MySql database columns.

Method 1: Using SqlBulkCopy

C#

 const string CSV_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
string CSVpath = "C:\\CSVFiles";  // CSV file Path you can use file choose control
var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;
string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

for (int i = 0; i < AllFiles.Length; i++)
{
    try
    {
        File_Name = AllFiles[i].Name;
        DataTable dt = new DataTable();
        using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
        {
            using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + File_Name + "]", con))
            {
                da.Fill(dt);
            }
        }
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConStr))
        {
            bulkCopy.ColumnMappings.Add(0, "Column1");
            bulkCopy.ColumnMappings.Add(1, "Column2");
            bulkCopy.ColumnMappings.Add(2, "Column3");
            bulkCopy.DestinationTableName = "myTable";
            bulkCopy.BatchSize = dt.Rows.Count;
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }                   
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Method 2: Using MySqlBulkLoader

C#

static void Main(string[] args)
        {
            string connStr = "server=localhost;user id=user id;password=password;database=database";
            // MySql Connection Object
            MySqlConnection conn = new MySqlConnection(connStr);

            //  csv file path
            string file = @"filepath";

            // MySQL BulkLoader
            MySqlBulkLoader bl = new MySqlBulkLoader(conn);
            bl.TableName = "tablename";
            bl.FieldTerminator = "|"; This can be {comma,tab,semi colon, or other character}
            bl.LineTerminator = "\n";
            bl.FileName =file; 

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                // Upload data from file
                int count = bl.Load();
                Console.WriteLine(count + " lines uploaded.");

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
            Console.ReadLine();
         
        }


Conclusion:
         
         MySqlBulkLoader is very faster than the SqlBulkCopy choice is yours!