Click here to Skip to main content
15,436,489 members
Please Sign up or sign in to vote.
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
Updated 16-Aug-21 18:38pm
v2
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

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!
 
Share this answer
 
Comments
akshaykekre 29-Sep-18 5:46am    
I use MySqlBulkLoader to import CSV into Mysql database..... It throws an exception while loading data.... at Load() call..... Exception: "Fatal error encountered during command execution" Please give solution for this
Damith Asanka 17-Aug-21 0:39am    
how to open and close DB using Using SqlBulkCopy

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900