c# - 使用 C# Windows 窗体将 csv 文件插入到 mysql 数据库表中

Question

我需要将该 excel 文件上传到 mysql 数据库表。

我的 Excel 文件包含如下列:

userid,password,first_name,last_name,user_group 和 MySql 数据库

我的表(aster_users)包含许多列，例如:userid、password、first_name、last_name、user_group、queue、active、created_date、created_by、role ..

我的代码是这样的:

 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)
        {

            MySqlConnection con = new MySqlConnection(MyCon);
            con.Open();
            string query = "SELECT userid as userid,password as password,first_name as first_name, last_name as last_name,user_group as user_group FROM aster_users";
            string strFilePath = textBox2.Text;
            MySqlCommand Cmd = new MySqlCommand(query, con);
            Cmd.CommandType = CommandType.Text;
            MySqlDataReader sdr = Cmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(strFilePath);
            DataTable Tablecolumns = new DataTable();
            for (int i = 0; i < sdr.FieldCount; i++)
            {
                Tablecolumns.Columns.Add(sdr.GetName(i));
            }
            sw.WriteLine(string.Join(",", Tablecolumns.Columns.Cast<datacolumn>().Select(csvfile => csvfile.ColumnName)));
            while (sdr.Read())
                strFilePath = (sdr[1].ToString() + "," + sdr[2].ToString() + "," + sdr[4].ToString() + "," + sdr[5].ToString() + "," + sdr[6].ToString() + ",");
            MessageBox.Show("Record saved");
            textBox2.Text="No Choose File";
            con.Close();
        }

Answer 1

试试这段代码:

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)
    {
        if (txtfileparth.Text != "")
        {
            string path = txtfileparth.Text;
            string userid = "";
            string password = "";
            string first_name = "";
            string last_name = "";
            string user_group = "";

            OleDbConnection my_con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
            my_con.Open();

            OleDbCommand icmd = new OleDbCommand("SELECT * FROM [dataGridView1_Data$]", my_con);

            OleDbDataReader dr = icmd.ExecuteReader();
            while (dr.Read())
            {
                userid = dr[0].ToString();
                password = dr[1].ToString();
                first_name = dr[2].ToString();
                last_name = dr[3].ToString();
                user_group = dr[4].ToString();

                MySqlConnection con = new MySqlConnection("SERVER=***.**.***.****;" +
                    "DATABASE=dbs;" +
                    "UID=uid;" +
                    "PASSWORD=pws;");
                con.Open();

                MySqlCommand icmmd = new MySqlCommand("INSERT INTO aster_users(userid,password,first_name,last_name,user_group)VALUES(@a,@b,@c,@d,@e)", con);
                icmmd.Parameters.AddWithValue("a", userid);
                icmmd.Parameters.AddWithValue("b", password);
                icmmd.Parameters.AddWithValue("c", first_name);
                icmmd.Parameters.AddWithValue("d", last_name);
                icmmd.Parameters.AddWithValue("e", user_group);
                icmmd.ExecuteNonQuery();
                con.Close();
            }
            MessageBox.Show("data Imported");
            txtfileparth.Text = "";
        }
        else if (txtfileparth.Text == "")
        {

        }
    }