How can I improve the loading of my datasets?
Datasets maintain some internal indexes to improve performs for things like finds and selects.
When you are loading more than 1 row into a DataTable - with a DataAdapter.Fill or other technique, you can turn this index maintenance off by doing a
MyTable.BeginLoadData()
Where MyTable is the reference to your DataTable, which could be
MyDataSet.Tables[0].BeginLoadData()
When you are done loading the data, don't forget to turn the index maintenance back on with.
MyDataSet.Tables[0].EndLoadData()
When loading multiple tables, you should turn on/off 1 table at a time.
Another thing that Datasets do while having rows added to tables is to validate them. Things like primary key uniqueness, foreign key referential integrity and nulls in columns with AllowDBNull = false are some examples of things that must be checked. Again, you can save some cycles by turning this off during loading of a dataset and turning it back on afterward. This can be done with:
MyDataSet.EnforceConstraints = false
And of course when you are done loading, you can perform a:
MyDataSet.EnforceConstraints = true
Of course you may get a “ConstraintException” on this last line if there are problems with your data. Otherwise, you'd get this exception as soon as the offending row is loaded. On a related note, you can check DataSet.HasErrors and each DataTable.HasErrors for any errors. For each table you can call DataTable.GetErrors() to get a list of rows with errors. Each row has a RowError property that contains any error text related to the entire row, and also a GetColumnError() method that you can use to test each column for a column specific error message.