使用C#Windows窗体将CSV文件插入到mysql数据库表中 [英] To insert csv file into mysql database table using c# windows form

问题描述

我需要将该Excel文件上传到mysql数据库表中.

i need to upload that excel file to mysql database table.

我的Excel文件包含以下列:

My Excel File Contains columns like:

用户名，密码，名字，姓氏，用户组和MySql数据库

userid,password,first_name,last_name,user_group AND MySql Database

我的表(aster_users)包含许多列，例如:userid，密码，名字，姓，用户组，队列，活动，创建日期，创建者，角色..

My table(aster_users) Contains many columns like: 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();
        }

推荐答案

尝试以下代码:

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

        }
    }

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