3

I have to update multiple records in SQL Server table from C#. Below are the steps I have to follow and below is the code. The code is working but the process is taking much longer than expected. I need a quick way to update 10000 records, not sure whether Bulk Copy would work for Update.

I have seen the other answers which has Bulk insert to temp and then update..But that update has a single statement and here I need to update the records in DB based on Excel data and for this I have to loop each excel record.So how can I achieve faster update.

1) Read the Excel Data and copied the data into a data table

string strDirectory = string. Empty;
strDirectory = System.IO.Directory.GetCurrentDirectory() + "\\" + "Filename.xlsx";
string Connection String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strDirectory + "; Extended Properties = \"Excel 12.0;HDR=YES;IMEX=1\"";
using (OleDbConnection conn = new OleDbConnection(Connection String))
{    
    conn.Open();

    DataTable schemaTable = conn.GetOleDbSchemaTableOleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

    DataRow schemaRow = schemaTable. Rows[0];
    string sheet = schemaRow["TABLE_NAME"].ToString();
    string query = "SELECT * FROM [" + sheet + "]";
    OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
    daexcel.Fill(dt);
    conn.Close();
}

2) Doing some manipulations on datatable data before updating into table.

string strsqlst = string. Empty;
using (SqlConnection sqlConn = new SqlConnection(Connectionstring))
{
    sqlConn.Open();
    SqlCommand cmd;

    StringBuilder sb = new StringBuilder();
    sb.AppendLine("DataTable content:");
    foreach (DataRow row in dt.Rows)
    {
        if (row.ItemArray[0].ToString() == "")
            break;

        strsqlst = "Update  table Set col1= " + row.ItemArray[4].ToString() + "  ,col2= " + row.ItemArray[5].ToString() + " where <Condition>'";

        cmd = new SqlCommand(strsqlst, sqlConn);

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();         
    }
    sqlConn.Close();
}
Drag and Drop
  • 2,672
  • 3
  • 25
  • 37
Lavin
  • 45
  • 1
  • 5
  • Version of your SQL Server? Look like Nothing but few Tsql keyword like [`MERGE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017) can be usefull. – Drag and Drop Sep 11 '18 at 07:12
  • 2
    Possible duplicate of [Bulk Update in C#](https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp) – Drag and Drop Sep 11 '18 at 07:17
  • Hi, you state _"But that update has a single statement"_, thats wrong it's an update of a table based on an other table, not a single row update. You have N column from your excel that are use full if they where in SQL temp table the update will be easier. – Drag and Drop Sep 11 '18 at 07:40
  • With something like: `var tempData = dt.AsEnumerable().Select (row => new { col1 = row.Field("ColumnName"), col2 = row.Field("ColumnName"), conditionID = row.Field("ColumnName") }).ToList();` you can insert that in temp table and simply `UPDATE DestinationTable SET ( DestinationTable.Column = TempTable.Column, ... ) FROM DestinationTable Dest INNER JOIN TempTable Temp ON TempTable.SomethingID = Temp.ID WHERE DestinationTable.Other condition` – Drag and Drop Sep 11 '18 at 07:52

1 Answers1

1

The SqlCommand can be a whole SQL batch and is not limited to a single statement. So you can create a single large batch with 10,000 UPDATE statements, or divide it into for example 20 batches of 500 each.

In other words, you can create a single command with CommandText like this:

UPDATE [T] SET Col1='Value1', Col2='Value2' WHERE [Id] = 1;
...
UPDATE [T] SET Col1='Value999', Col2='Value1000' WHERE [Id] = 500;

That said, you should use parameters for all data values (to ensure SQL injection is not possible).

If you want to handle any errors (updates failing due to invalid data) you will need something a bit more sophisticated.

Dojo
  • 59
  • 1
  • 7