C#读取Excel和txt文件数据并把数据分别一次性添加到Mysql数据库

C#简单文件读写和简单操作数据库

1.连接Mysql数据库和简单操作数据库类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace mysqlToFile
{
    class Connector
    {
        /// <summary>
        ///  建立MySql数据库连接.
        /// </summary>
        /// <returns>返回MySqlConnection对象</returns>
        public MySqlConnection getmysqlcon()
        {
            string M_str_sqlcon = "server=127.0.0.1;user=root;"+
                "database=tt;port=3306;password=;"; //根据自己的设置
            MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
            return myCon;
        }

        //  执行MySqlCommand命令
        /// <summary>
        /// 执行MySqlCommand命令
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        public void getmysqlcom(string M_str_sqlstr)
        {
            MySqlConnection mysqlcon = this.getmysqlcon();
            mysqlcon.Open();
            MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
            MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
            mysqlcom.ExecuteNonQuery();
            mysqlcom.Dispose();
            mysqlcon.Close();
            mysqlcon.Dispose();
        }

        /// <summary>
        /// 创建一个MySqlDataReader对象,以便用于控制台输出表的内容
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        public MySqlDataReader getmysqlread(string M_str_sqlstr)
        {
            MySqlConnection mysqlcon = this.getmysqlcon();
            MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
            mysqlcon.Open();
            MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
            return mysqlread;
        }

        /// <summary>
        /// 创建一个DataTable对象,以便用于控制台及dataGridView控件输出表的内容
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        public DataTable getsqlread(string M_str_sqlstr)
        {
            MySqlConnection mysqlcon = this.getmysqlcon();
            mysqlcon.Open();
            MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
            MySqlDataAdapter mda = new MySqlDataAdapter(M_str_sqlstr, mysqlcon);
            DataTable dt = new DataTable();
            mda.Fill(dt);
            return dt;
        }

        
    }
}

2.读写文件内容及批量写入数据库中

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace mysqlToFile
{
    public partial class MainOpereteForm : Form
    {
        public MainOpereteForm()
        {
            InitializeComponent();
        }

        Connector connector = new Connector();

        /// <summary>
        ///  读取excel文件内容并存放在DataSet中.
        /// </summary>
        /// <returns>返回DataSet对象</returns>
        public DataSet ExcelToDS(string path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            DataTable table1 = new DataTable();
            ds = new DataSet();
            myCommand.Fill(table1);
            myCommand.Fill(ds);
            return ds;
        }

        private void btnInsertXls_Click(object sender, EventArgs e)
        {
            InsertXls();
        }

        /// <summary>
        ///  读取List_Class1_Submit.xls文件的内容并一次性把内容插入到Mysql数据库tt的students表中.
        /// </summary>
        private void InsertXls()
        {
            // 调用文件对话框，选择需要文件
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*|工作薄(*.xlsx)|*.xlsx";
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
            {
                DataSet dataSet = ExcelToDS(openfile.FileName);
                String sqlSearch = "select * from students";
                int count = 0;
                string[] str = new string[4];
                string sql;
                foreach (DataTable table in dataSet.Tables)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        count = 0;
                        for (int i = 0; i < 4; i++)
                        {
                            str[i] = "*";
                        }
                        foreach (DataColumn column in table.Columns)
                        {
                            if (row[column] != null)
                                str[count] = row[column].ToString();
                            count++;
                        }
                        sql = string.Format("insert into students values('{0}','{1}','{2}','{3}')",
                            str[0], str[1], str[2], str[3]);
                        if (!str[0].Equals("*"))
                        {
                            connector.getmysqlcom(sql);
                        }
                    }
                }
                Reflash(sqlSearch);
            }
        }

        private void btnInsertTxt_Click(object sender, EventArgs e)
        {
            InsertTxt();

        }

        /// <summary>
        ///  读取List_Class1.txt文件的内容并一次性把内容插入到Mysql数据库tt的studentss表中.
        /// </summary>
        private void InsertTxt()
        {
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "文本文件(*.txt)|*.txt|所有文件(*.*)|*.*|word文档(*.doc)|*.doc";
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
            {
                String sqlSearch = "select * from studentss";
                string[] str = new string[5];
                string sql;
                using (StreamReader sr = new StreamReader(openfile.FileName, Encoding.Default, false))
                {
                    string line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        line.Trim();
                        str = line.Split(new char[] { ' ' });
                        sql = string.Format("insert into studentss values('{0}','{1}','{2}','{3}','{4}')",
                                str[0], str[1], str[2], str[3], str[4]);
                        connector.getmysqlcom(sql);
                    }
                }
                Reflash(sqlSearch);
            }
        }

        private void selectBtn_Click(object sender, EventArgs e)
        {
            String sqlSearch = "select studentss.stuName as 未提交作业网址学生 from studentss "+
                "where studentss.stuNo not in (select students.stuNo from students)";
            Reflash(sqlSearch);
        }

        /// <summary>
        ///  读取Grade_FirstExercise.xls文件的内容并一次性把内容插入到Mysql数据库tt的grades表中.
        /// </summary>
        private void btnInsertGrade_Click(object sender, EventArgs e)
        {
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*|工作薄(*.xlsx)|*.xlsx";
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
            {
                DataSet dataSet = ExcelToDS(openfile.FileName);
                String sqlSearch = "select * from grades";
                int count = 0;
                double grade = 0;
                string[] str = new string[3];
                string sql;
                    foreach (DataTable table in dataSet.Tables)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            for (int i = 0; i < 3; i++)
                            {
                                str[i] = "*";
                            }
                            foreach (DataColumn column in table.Columns)
                            {
                                if (column.ColumnName == "学号")
                                {
                                    str[0] = row[column].ToString();
                                }
                                if (column.ColumnName == "互评学生姓名")
                                {
                                    str[1] = row[column].ToString();
                                 
                                }
                                count = 1;
                                if (column.ColumnName == "互评学生分数")
                                {
                                    str[2] = row[column].ToString();
                                    grade = Convert.ToDouble(str[2]);
                                }
                            }
                            sql = string.Format("insert into grades values('{0}','{1}',{2},{3})",
                                str[0], str[1], count, grade);
                            if (!str[0].Equals("*") && !str[2].Equals("*"))
                            {
                                connector.getmysqlcom(sql);
                            }
                        }
                    }
                    
                }
            }