CODEWITHSUNDEEP
c#wpfmemory-managementdatatable
Uthistran Selvaraj.
Uthistran Selvaraj.

Reputation: 558

Reduce memory usage while read data from datatable

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);
    }
}

Upvotes: 4

Views: 2001

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460108

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);
            }
        }
    }
}

Upvotes: 5

Spencer Ruport
Spencer Ruport

Reputation: 35117

Are you asking if there's a better approach or are you asking if there's a more efficient memory storage option?

I'm very skeptical any other memory storage option is going to give you a noticeable difference. Lists and Dictionary may not be the most efficient storage option but they're not hogs either.

If you want to know if there's a better approach that depends on what you're trying to do. If this data is to be displayed to the user typically what's done is the visible size of the table is calculated (so that the scroll bars behave as expected) and only some of the immediately previous and next records just out of the viewing area are cached. As the user moves around looking at the table the data is lazy loaded as needed.

Upvotes: 3

Related Questions