I am having some performance issues updating a large datatable to SQL server using the table adapter functionality.
I am doing exactly what this examples is doing:
I am making 10,000 or so records in a datatable. And I'm ready to persist it to the database.
So I run:
this.customersTableAdapter.Update(this.northwindDataSet.Customers);
It is very slow.
Do I need to use SqlBulkInsert? It does appear to perform much better.
Or am I just doing it wrong? Kind of seems like a strange thing to have this method available if it's just insanely slow to anyone who uses it. I prefer to use this .Update method because its error messages are much more readable. But perhaps it's behind the scenes inserting 1-record-at-a-time.
CodePudding user response:
I found this: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/performing-batch-operations-using-dataadapters
It does appear to make it faster.
MyDataSetTableAdapters.TableAdapterManager tableAdapterManager = new MyDataSetTableAdapters.TableAdapterManager();
tableAdapterManager.Connection = sqlConnection;
tableAdapterManager.MyDataTableAdapter = new MyDataSetTableAdapters.MyDataTableAdapter();
tableAdapterManager.MyDataTableAdapter.Connection = sqlConnection;
Console.WriteLine("Saving data set " MyDataSet.MyData.TableName);
tableAdapterManager.MyDataTableAdapter.Adapter.UpdateBatchSize = 1000;
tableAdapterManager.MyDataTableAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
tableAdapterManager.MyDataTableAdapter.Adapter.Update(MyDataSet.MyData);