将Excel数据批量插入数据库的常用方法

一、结合EntityFramework进行的简单实现

        static void LoadExcel(){
            string filePath = @"E:\Excel目录\";
            LoadFileTree(filePath);
        }

        static void LoadFileTree(string filePath)
        {
            string connString = "server=localhost;uid=sa;pwd=qaz~123;database=MedicalSearch";

            System.IO.DirectoryInfo di = new DirectoryInfo(filePath);
            var _query = di.GetFiles();
            foreach (var _ary in _query)
            {
                TransferData(_ary.FullName, _ary.Name, "sheet1", connString);
            }
        }

        static void TransferData(string excelFile, string fileName, string sheetName, string connectionString)
        {
            DataSet ds = new DataSet();

            try
            {
                //获取全部数据
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                string strExcel = string.Empty;
                OleDbDataAdapter myCommand = null;
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    strExcel = string.Format("select * from [{0}$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);

                    var _source = ds.Tables[0];
                    int _count = ds.Tables[0].Rows.Count;

                    MedicalSearchEntities _msEntities = new MedicalSearchEntities();

                    for (int i = 0; i < _count; i++)
                    {
                        MS_Agencies _msA = new MS_Agencies();

                        _msA.Name = _source.Rows[i][0].ToString();
                        _msA.Levels = _source.Rows[i][1].ToString();
                        _msA.Nature = _source.Rows[i][2].ToString();
                        _msA.Address = _source.Rows[i][3].ToString();
                        _msA.Phone = _source.Rows[i][4].ToString();
                        _msA.Remark = _source.Rows[i][5].ToString();
                        _msA.Region = (fileName.Remove(6));

                        _msEntities.MS_Agencies.AddObject(_msA);
                        _msEntities.SaveChanges();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

 

二、用System.Data.SqlClient命名空间自带的方法SqlBulkCopy同步插入数据（官方说明此方法比较高效）

                    //如果目标表不存在则创建

                    string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);

                    foreach (System.Data.DataColumn c in ds.Tables[0].Columns)

                    {

                        strSql += string.Format("[{0}] varchar(255),", c.ColumnName);

                    }

                    strSql = strSql.Trim(',') + ")";

                    using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))

                    {

                        sqlconn.Open();

                        System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();

                        command.CommandText = strSql;

                        command.ExecuteNonQuery();

                        sqlconn.Close();

                    }

                    //用bcp导入数据

                    using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))

                    {

                        bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);

                        bcp.BatchSize = 100;//每次传输的行数

                        bcp.NotifyAfter = 100;//进度提示的行数

                        bcp.DestinationTableName = sheetName;//目标表

                        bcp.WriteToServer(ds.Tables[0]);

                    }