excel 万级别的数据 导入sqlserver,该如何处理

excel 万级别的数据 导入sqlserver
我是用程序写的导入

C# code

  protected void btnOK_Click(object sender, EventArgs e)    {        if (fuPath.HasFile)        {            string path = fuPath.PostedFile.FileName.ToString();            string[] pathArray = path.Split('\\');            string name = pathArray[pathArray.Length - 1];            string endName = DateTime.Now.ToString("yyMMddHHmmss") + name;            string endPath = HttpContext.Current.Server.MapPath("~/Excel/") + endName;            fuPath.PostedFile.SaveAs(endPath);            DataSet ds = ExcelToDataSet(endPath);            if (SqlHelper.HasRow(ds))            {                string error = "";                #region for                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)                {                    #region Check                    string typeNo = ds.Tables[0].Rows[i]["xx"].ToString().Trim();                    string typeName = ds.Tables[0].Rows[i]["ccc"].ToString().Trim();                    string pTypeNo = ds.Tables[0].Rows[i]["dd"].ToString().Trim();                    string pTypeName = ds.Tables[0].Rows[i]["eee"].ToString().Trim();                    string remark = ds.Tables[0].Rows[i]["fff"].ToString().Trim();                    if (typeNo.Length > 100)                        typeNo = typeNo.Substring(0, 100);                    if (typeName.Length > 100)                        typeName = typeName.Substring(0, 100);                    if (pTypeName.Length > 50)                        pTypeName = pTypeName.Substring(0, 50);                    if (remark.Length > 100)                        remark = remark.Substring(0, 100);                    #endregion                    #region Linq                    TabAssetsType p = db.TabAssetsType.FirstOrDefault(c => c.TypeNo == typeNo);                    if (p == null)                    {                                            TabAssetsType tabAsstype = new TabAssetsType();                           tabAsstype.TypeNo = FormatCom.String(typeNo);                        tabAsstype.TypeName = FormatCom.String(typeName);                        tabAsstype.PTypeNo = FormatCom.String(pTypeNo);                        tabAsstype.PTypeName = FormatCom.String(pTypeName);                        tabAsstype.IsState = 0;                        tabAsstype.Remark = FormatCom.String(remark);                                            db.TabAssetsType.InsertOnSubmit(tabAsstype);                    }                    else                    {                           //error += @"xx(" + typeNo + ")已存在！\r\n";                    }                    #endregion                }                #endregion                try                {                    db.SubmitChanges();                    db.ExecuteCommand("update TabAssetsType set TypeNo = TypeNo");                    error += @"导入成功！\r\n";                    MessageBox(error);                }                catch (Exception ex)                {                    MessageBox("导入失败！");                }                          }            else            {                MessageBox("该文件中没有xx信息，请检查模板是否正确！");                return;            }        }        else        {            MessageBox("请选择要导入的文件！");            return;        }    }    public DataSet ExcelToDataSet(string Path)    {        DataSet ds = new DataSet();        try        {            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";            OleDbConnection conn = new OleDbConnection(strConn);            conn.Open();            OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);            myCommand.Fill(ds, "TabAssetsType");            return ds;        }        catch        {            MessageBox("支持Excel2003的导入不支持2007！");                  }        return ds;    }  
 

HTML code

<table border="0" cellpadding="0" cellspacing="0" class="TitleTable">        <tr>            <th>            </th>            <td>               资产分类数据            </td>        </tr>    </table>    <div class="tablebase">        <table cellpadding="0" cellspacing="0" class="midTable1">            <tr>                <td class="td1">                    <i style="color: #FF0000">*&nbsp; </i>文件路径：                </td>                <td colspan="3">                    &nbsp; &nbsp;                    <asp:FileUpload ID="fuPath" runat="server" Width="471px" />                </td>            </tr>            <tr>                <td class="tdbtn" colspan="4">                    <asp:Button ID="btnOK" runat="server" Text="导入" CssClass="btn23" OnClick="btnOK_Click" />                </td>            </tr>        </table> 

这样导入一个有几万条的excel文件里面的数据需要话费5分钟左右，TMD太慢了，请教高手如果能让这性能提升一下啊，我用的数据库操作是LINQ 是不是和这个有关？希望那个懂优化的高手指教，谢谢，谢谢，太谢谢了!

[解决办法]
[color=#0000FF]
//SqlConnection sc = new SqlConnection("Server=(local);uid=nan;pwd=sa;database=PDSSC");
string connectionString = "Data Source=10.1.16.77;Initial Catalog=IndbTest;user id=sa;password=sa";
SqlConnection sc = new SqlConnection(connectionString);
sc.Open();
//声明 SqlBulkCopy对象
//没有这个对象，批量导入就没戏
SqlBulkCopy sbc = new SqlBulkCopy(sc);
sbc.DestinationTableName = "TabExcelNet";

//sbc.ColumnMappings.Add(ds.Tables[0].Columns[0].ColumnName, 0);
//sbc.ColumnMappings.Add(ds.Tables[0].Columns[1].ColumnName, 1);
//sbc.ColumnMappings.Add(ds.Tables[0].Columns[2].ColumnName, 2);

// 列映射

for (int q = 0; q < ds.Tables[0].Columns.Count; q++)
{
sbc.ColumnMappings.Add(ds.Tables[0].Columns[q].ColumnName, q);
}

try
{
sbc.WriteToServer(ds.Tables[0]);
MessageBox("导入完成。");
}
catch (Exception ex)
{
MessageBox(ex.Message);
MessageBox("导入失败。");
}
finally
{
ds.Dispose();
sc.Close();
sbc.Close();
}[/color]

查看更多 下一篇