Reduce memory usage while read data from datatable

Question

I have tried this way to read data from data table is there any other better way to store data in list (to reduce memory usage ).

Code :

foreach (System.Data.DataRow row in table.Rows)
{
    Myclassdef data = new Myclassdef();
    data.Data = new Dictionary<string, object>();

    foreach (DataColumn columninfo in table.Columns)
    {
        object value = row[columninfo.ColumnName];
        Myclassdef.Data.Add(columninfo.ColumnName, value);
    }
}

Answer 1

Are you sure your memory problem is caused by the DataTable? You are mapping all into a custom class with a dictionary for every row. So you're required memory is more than twice than with the DataTable alone.

However, if you are reading the values from database and you are loading all into a DataTable first just to be able to loop all to create your custom class with the dictionary, then there is a better way in terms of memory consumption:

• use a DataReader to read those values from database. That streams the data without needing to store anything in memory

  var list = new List<Myclassdef>();
  using (var con = new SqlConnection(Settings.Default.ConnectionString))
  {
      using (var cmd = new SqlCommand("SELECT ... WHERE Col1=@param1", con))
      {
          cmd.Parameters.AddWithValue("@param1", "value1");
          // ...
          con.Open();
          using (var reader = cmd.ExecuteReader())
          {
              while (reader.Read())
              {
                  Myclassdef data = new Myclassdef();
                  data.Data = new Dictionary<string, object>();
                  for (int i = 0; i < reader.FieldCount; i++)
                  {
                      data.Data.Add(reader.GetName(i), reader[i]);
                  }
                  list.Add(data);
              }
          }
      }
  }