使用SqlBulkCopy实现Excel批量导入数据库

引言：   

    导入Excel数据时候，因为数据量大，使用 linq 操作插入的时候，很慢。考虑使用ado.net操作数据库方式导入数据。由于数据量比较大使用SqlBulkCopy来实现批量导入。

一.代码解释：

1.将要导入的excel上传保存到服务器。

2.使用OleDbDataAdapter获取excel中的数据插入tab中。

3.在内存中创建一个表newDT，将tab表做修改后更新到newDT中。

4.使用SqlBulkCopy批量将newDT内存表数据插入到数据库表Sys_StuUser中。

 

二.代码如下：

 string FileUrl = string.Empty;
                foreach (HttpPostedFileBase file in fileToUpload)
                {
                    string path = System.IO.Path.Combine(Server.MapPath("~/Models/stu/UpLoadExc"), System.IO.Path.GetFileName(DateTime.Now.ToString("yyyymmddhhMMss") + file.FileName));
                    file.SaveAs(path);//SaveAs 将上传的文件内容保存在服务器上
                    FileUrl = path;
                }
                string result = string.Empty;
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileUrl + "; " + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);

                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet, "ExcelInfo");
                System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
                var majordata = MajorInfoService.LoadEntities(u => true).ToList();
                var schooldata = UniversityInfoService.LoadEntities(u => true).ToList();
                // ...用foreach把tab中数据添加到数据库
                DataTable newDT = new DataTable();
                //newDT.Columns.Add("LoginID", typeof(string));
                newDT.Columns.Add("Pwd", typeof(string));
                newDT.Columns.Add("UserName", typeof(string));
                newDT.Columns.Add("Education", typeof(int));
                newDT.Columns.Add("Major", typeof(int));
                newDT.Columns.Add("StuOrigin", typeof(string));
                newDT.Columns.Add("Gender", typeof(int));
                newDT.Columns.Add("Destination", typeof(int));
                newDT.Columns.Add("HomeTel", typeof(string));
                newDT.Columns.Add("Tel", typeof(string));
                newDT.Columns.Add("Emai", typeof(string));
                newDT.Columns.Add("StudentID", typeof(string));
                newDT.Columns.Add("School", typeof(int));
                newDT.Columns.Add("GradYear", typeof(string));
                newDT.Columns.Add("IdentityID", typeof(string));
                //newDT.Columns.Add("IfJoin", typeof(string));
                //newDT.Columns.Add("Vcode", typeof(string));
                //newDT.Columns.Add("Flag", typeof(int));
                //newDT.Columns.Add("SID", typeof(string));

                for (int i = 0; i < tab.Rows.Count - 1; i++)
                {
                    DataRow dr = tab.Rows[i];
                    string SchoolTag = dr["学校代码"].ToString().Trim();
                    string MajorID = dr["专业代码"].ToString().Trim();
                    string DestinationID = dr["毕业去向"].ToString().Trim();
                    string EducationID = dr["学历代码"].ToString().Trim();
                    var schoolid = (from s in schooldata where s.UCode == SchoolTag select new { s.UID }).
                        FirstOrDefault();
                    var majorid =
                        (from m in majordata where m.MajorCode == MajorID select new { m.MajorID }).FirstOrDefault();
                    DataRow newRow = newDT.NewRow();
                    newRow["Pwd"] = "0B946149D7DDE1A273F26402A344008E";
                    newRow["UserName"] = dr["姓名"].ToString().Trim();
                    switch (EducationID)
                    {
                        case "01": newRow["Education"] = 4; break;
                        case "11": newRow["Education"] = 3; break;
                        case "31": newRow["Education"] = 2; break;
                        case "41": newRow["Education"] = 1; break;
                        default: newRow["Education"] = 0; break;
                    }
                    newRow["Major"] = majorid == null ? 0 : majorid.MajorID;
                    newRow["StuOrigin"] = dr["生源地"].ToString().Trim();
                    newRow["Gender"] = dr["性别代码"] == null ? 0 : Convert.ToInt32(dr["性别代码"].ToString().Trim());
                    switch (DestinationID)
                    {
                        case "01": newRow["Destination"] = 1; break;
                        case "02": newRow["Destination"] = 2; break;
                        case "03": newRow["Destination"] = 3; break;
                        case "04": newRow["Destination"] = 4; break;
                        default: newRow["Destination"] = 0; break;
                    }
                    
                    newRow["HomeTel"] = dr["家庭电话"].ToString().Trim();
                    newRow["Tel"] = dr["手机号码"].ToString().Trim();
                    newRow["Emai"] = dr["电子邮箱"].ToString().Trim();
                    newRow["StudentID"] = dr["学号"].ToString().Trim();
                    newRow["School"] = schoolid == null ? 0 : schoolid.UID;
                    newRow["GradYear"] = dr["毕业年份"].ToString().Trim();
                    newRow["IdentityID"] = dr["身份证号"].ToString().Trim();


                    newDT.Rows.Add(newRow);

                }
                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(PubConstant.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                sqlbulkcopy.DestinationTableName = "Sys_StuUser";//数据库中的表名
                sqlbulkcopy.ColumnMappings.Add("Pwd", "Pwd");
                sqlbulkcopy.ColumnMappings.Add("UserName", "UserName");
                sqlbulkcopy.ColumnMappings.Add("Education", "Education");
                sqlbulkcopy.ColumnMappings.Add("Major", "Major");
                sqlbulkcopy.ColumnMappings.Add("StuOrigin", "StuOrigin");
                sqlbulkcopy.ColumnMappings.Add("Gender", "Gender");
                sqlbulkcopy.ColumnMappings.Add("Destination", "Destination");
                sqlbulkcopy.ColumnMappings.Add("HomeTel", "HomeTel");
                sqlbulkcopy.ColumnMappings.Add("Tel", "Tel");
                sqlbulkcopy.ColumnMappings.Add("Emai", "Emai");
                sqlbulkcopy.ColumnMappings.Add("StudentID", "StudentID");
                sqlbulkcopy.ColumnMappings.Add("School", "School");
                sqlbulkcopy.ColumnMappings.Add("GradYear", "GradYear");
                sqlbulkcopy.ColumnMappings.Add("IdentityID", "IdentityID");  
                sqlbulkcopy.WriteToServer(newDT);
                return Content("上传成功！");
            }