如何使用c＃windows窗体将csv文件插入到mysql数据库表中 [英] how to insert csv file into mysql database table using c# windows form

问题描述

当我点击提交按钮。我需要选择csv文件上传到mysql数据库表

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";
          
        }

我的编码错了。我需要使用sqlbulk上传iam搜索该代码

only



请帮帮我。

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

help me please.

推荐答案

互联网上有很多解决方案。我找到了一个易于理解的解决方案。请确保CSV文件中的列与MySql数据库列相同。





方法1：使用SqlBulkCopy

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

 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;
    }
}

方法2：使用MySqlBulkLoader

Method 2: Using MySqlBulkLoader

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!

这篇关于如何使用c＃windows窗体将csv文件插入到mysql数据库表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！