ADO.NET mass update data

ADO.NET mass data update

Important reference

public static void SqlBulkUpdate()
    DataTable dt = ExcelHelper.ImportExcelFile("d://User1.xls "; / / import the data in Excel into 10000 data tables (as the data source for updating data)
    DateTime begin = DateTime.Now;
    string connectionString = connStr;
    using (SqlConnection conn = new SqlConnection(connectionString))

        SqlDataAdapter sd = new SqlDataAdapter();
		//These 200 pieces of data are only used as templates (equivalent to a temporary table, which is used to place the data to be updated in time)
        sd.SelectCommand = new SqlCommand("select top 200 Id,Name,Age,Email,Date from T_Test", conn);

        DataSet dataset = new DataSet();
        Random r = new Random(1000);
        sd.UpdateCommand = new SqlCommand("Update T_Test set Name=@name,Age=@age,Email=@email,Date=@date where id=@id", conn);
        sd.UpdateCommand.Parameters.Add("@name", SqlDbType.Char, 100, "name");
        sd.UpdateCommand.Parameters.Add("@age", SqlDbType.Int, 8, "age");
        sd.UpdateCommand.Parameters.Add("@email", SqlDbType.VarChar, 100, "email");
        sd.UpdateCommand.Parameters.Add("@date", SqlDbType.VarChar, 100, "date");
        sd.UpdateCommand.Parameters.Add("@id", SqlDbType.VarChar, 100, "id");
        sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        sd.UpdateBatchSize = 0;

		//What's really updated is this paragraph
        for (int count = 0; count < dt.Rows.Count-1; count++)
            for (int i = 0; i < 200; i++, count++)
                if (count > dt.Rows.Count - 1) break;
                dataset.Tables[0].Rows[i].BeginEdit(); //Start editing the System.Data.DataRow object.
                dataset.Tables[0].Rows[i]["Name"] = dt.Rows[count][1];
                dataset.Tables[0].Rows[i]["Age"] = dt.Rows[count][2];
                dataset.Tables[0].Rows[i]["Email"] = dt.Rows[count][3];
                dataset.Tables[0].Rows[i]["Date"] = dt.Rows[count][4];
                dataset.Tables[0].Rows[i]["Id"] = dt.Rows[count][0];//It is necessary to ensure that the Id in dt exists in the data table, otherwise an error will be reported. For example, if the Id in dt is 1000, then a piece of data with Id=1000 is also required in the data table
                dataset.Tables[0].Rows[i].EndEdit();//Terminates edits that occur in that row.
			//UPDATE: calls the corresponding INSERT, UPDATE, or DELETE statement for each inserted, updated, or deleted row in the specified System.Data.DataTable.


    TimeSpan ts = DateTime.Now - begin;//Update 10000 pieces of data in about 2 seconds

Note that the above update operation means that I need to constantly update the records in one hundred thousand, one million, ten million records. These records to be updated are not from scratch
In the end order, I just keep updating any records according to the conditions. I can't Fill millions of records into ds first and then Select them into ds
This record is then updated, so every 200 update operations are filled in the DataTable and submitted, and JDBC's addBat and executeBat operations are implemented

Tags: Excel JDBC

Posted on Mon, 04 May 2020 03:41:56 -0700 by danrah