c# – What's the best way to populate the DataTable?

Question:

  1. 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
  2. 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?

Answer:

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 SqlDataReader NextResult() is called automatically.

If you don't need any data transformations, you can also populate the table using the SqlDataAdapter :

var dt = new DataTable();

SqlCommand cmd = ...;
var da = new SqlDataAdapter(cmd);
da.Fill(dt);
Scroll to Top