C. How to batch insert large amounts of data into a database in NET

In the WEB project development process sometimes encountered batch insertion of data into the number or EXCEL files into the database. In order to facilitate the implementation of EXCEL can be imported into GRIDVIEW and then a batch insertion. Implementation code is as follows:

Foreground code

<asp:GridView ID="dgBom" runat="server" AutoGenerateColumns="false" CellPadding="1" CellSpacing="2">
<HeaderStyle BackColor="#ededed" />
  <Columns>
   <asp:TemplateField HeaderText=" Student number ">
    <ItemTemplate>
     <asp:TextBox ID="studentnumber" runat="server" Text='<%#Eval("studentnumber") %>' ></asp:TextBox>
    </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText=" Student name ">
    <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=" Import data " />
  <asp:Button ID="btninsert" runat="server" OnClick="btninsert_Click" Text=" Insert into the database "/>

Background code:

// First, add the following two lines to the namespace 
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 Connection of 
    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();// Get  Excel  The default value is the table name of sheet1
    string strSql = "select * from [" + tableName + "]";
    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
    OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
    myData.Fill(ds, tableName);// Fill in data 
    dgBom.DataSource =ds;
    dgBom.DataBind();
    objConn.Close();
    ExcelTable = ds.Tables[tableName];
    int iColums = ExcelTable.Columns.Count;// Number of columns 
    int iRows = ExcelTable.Rows.Count;// Number of rows 
    // Definition 2 Dimensional array storage  Excel  Data read from the table 
    string[,] storedata = new string[iRows, iColums];
    for(int i=0;i<ExcelTable.Rows.Count;i++)
      for (int j = 0; j < ExcelTable.Columns.Count; j++)
      {
        // Will Excel The data in the table is stored in an array 
        storedata[i, j] = ExcelTable.Rows[i][j].ToString();
      }
    int excelBom = 0;// Record the number of rows of useful information in the table. Useful information refers to removing the title and columns of the table. In this example, the useful information of the table is from the 3 Line start 
    // Determine the number of useful rows 
    for (int k = 2; k < ExcelTable.Rows.Count; k++)
      if (storedata[k, 1] != "")
        excelBom++;
    if (excelBom == 0)
    {
      Response.Write("<script language=javascript>alert(' The table you imported is out of format! ')</script>");
    }
    else
    {
      //LoadDataToDataBase(storedata , excelBom)// This function is mainly responsible for setting the  storedata  Write useful data to the database, which is not the key omission of the problem here  
    }
  }
  protected void btninsert_Click(object sender, EventArgs e)
  {
    foreach (GridViewRow gv in dgBom.Rows) 
    {
      // My connection string is written in WEB.CONFIG In .
      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();
      }
    }
  }

The above content is the whole description of this article, hoping to help you learn how to insert a large amount of data into the database in batches in C #. NET.