C#.NET中如何批量插入大量数据到数据库中

在web项目开发过程中有时会碰到批量插入数据到数或者是将excel文件据入到数据库中.为了方便实现可以先将excel导入到gridview中然后一次批量插入.实现代码如下:

前台代码

<asp:gridview id="dgbom" runat="server" autogeneratecolumns="false" cellpadding="1" cellspacing="2">
<headerstyle backcolor="#ededed" />
  <columns>
   <asp:templatefield headertext="学号">
    <itemtemplate>
     <asp:textbox id="studentnumber" runat="server" text='<%#eval("studentnumber") %>' ></asp:textbox>
    </itemtemplate>
   </asp:templatefield>
   <asp:templatefield headertext="学生姓名">
    <itemtemplate>
     <asp:textbox id="studentname" runat="server" text='<%#eval("studentname") %>'></asp:textbox>
    </itemtemplate>
   </asp:templatefield>
  </columns>
</asp:gridview>
  <asp:fileupload id="fileupload1" runat="server" font-italic="false" />
  <asp:button id="btn2" runat="server" onclick="btn2_click" text="导入数据" />
  <asp:button id="btninsert" runat="server" onclick="btninsert_click" text="插入到数据库中"/>

后台代码:

//首先在命名空间中加入以下两行
using system.data.sqlclient;
using system.data.oledb;
protected void btn2_click(object sender, eventargs e)
  {
    string filepath = fileupload1.postedfile.filename;
    readexcel(filepath, dgbom);
  }
  public void readexcel(string sexcelfile, gridview dgbom)
  {
    datatable exceltable;
    dataset ds = new dataset();
    //excel的连接
    oledbconnection objconn = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source=" + sexcelfile + ";" + "extended properties=excel 8.0;");
    objconn.open();
    datatable schematable = objconn.getoledbschematable(system.data.oledb.oledbschemaguid.tables, null);
    string tablename = schematable.rows[0][2].tostring().trim();//获取 excel 的表名，默认值是sheet1
    string strsql = "select * from [" + tablename + "]";
    oledbcommand objcmd = new oledbcommand(strsql, objconn);
    oledbdataadapter mydata = new oledbdataadapter(strsql, objconn);
    mydata.fill(ds, tablename);//填充数据
    dgbom.datasource =ds;
    dgbom.databind();
    objconn.close();
    exceltable = ds.tables[tablename];
    int icolums = exceltable.columns.count;//列数
    int irows = exceltable.rows.count;//行数
    //定义二维数组存储 excel 表中读取的数据
    string[,] storedata = new string[irows, icolums];
    for(int i=0;i<exceltable.rows.count;i++)
      for (int j = 0; j < exceltable.columns.count; j++)
      {
        //将excel表中的数据存储到数组
        storedata[i, j] = exceltable.rows[i][j].tostring();
      }
    int excelbom = 0;//记录表中有用信息的行数，有用信息是指除去表的标题和表的栏目，本例中表的用用信息是从第三行开始
    //确定有用的行数
    for (int k = 2; k < exceltable.rows.count; k++)
      if (storedata[k, 1] != "")
        excelbom++;
    if (excelbom == 0)
    {
      response.write("<script language=javascript>alert('您导入的表格不合格式！')</script>");
    }
    else
    {
      //loaddatatodatabase(storedata，excelbom)//该函数主要负责将 storedata 中有用的数据写入到数据库中，在此不是问题的关键省略 
    }
  }
  protected void btninsert_click(object sender, eventargs e)
  {
    foreach (gridviewrow gv in dgbom.rows) 
    {
      //我的连接字符串是写在web.config中的.
      string con = system.configuration.configurationmanager.appsettings["connectionstring1"].tostring();
      sqlconnection conn = new sqlconnection(con);
      sqlcommand cmd = conn.createcommand();
      cmd.commandtype = commandtype.text;
      cmd.commandtext = "insert into student (studentnumber,studentname) values(@studentnumber,@studentname)";
      cmd.parameters.add("@studentnumber", sqldbtype.nvarchar, 20);
      cmd.parameters.add("@studentname", sqldbtype.nvarchar, 10);
      cmd.parameters["@studentname"].value = ((textbox)gv.findcontrol("studentname")).text;
      cmd.parameters["@studentnumber"].value = ((textbox)gv.findcontrol("studentnumber")).text;
      try
      {
        conn.open();
        cmd.executenonquery();
        conn.close();
      }
      finally
      {
        if (conn != null)
          conn.dispose();
      }
    }
  }

以上内容就是本文的全部叙述，希望对大家学习c#.net中如何批量插入大量数据到数据库中有所帮助。