- Extract SqlDataReader, use it to create a table schema (select all the required column properties), then use a loop to fill the table, copying rows from SqlDataReader
- Retrieve the SqlDataReader, pass it as a parameter to the .Load () method for the SqlDataTable
What is the difference between these methods and what is the best way to do it?
In the first case, you can control the process more fully, for example, do not load some columns, or load columns that do not exist in the original selection with some kind of virtual data, generating them along the way, based, for example, on data from other columns and / or some kind of external data, or perform some tricky type conversions, skip some unwanted lines, etc. I think, however, that in practice this method (direct reading from the
SqlDataReader ) is often used not to load the
DataTable , but to load data directly into various objects, lists, dictionaries, etc.
In the second case, the table is loaded from the
SqlDataReader "as is". This can be useful when the
SqlDataReader returns multiple results, some of which you need to load into the
DataTable and others to handle in some other way. Also note that in the 1st case, you need to call
NextResult() to advance to the next result, whereas after
Load() table from the
NextResult() is called automatically.
If you don't need any data transformations, you can also populate the table using the
var dt = new DataTable(); SqlCommand cmd = ...; var da = new SqlDataAdapter(cmd); da.Fill(dt);