OutOfMemoryException when loading large table into DataSet

Question

I would like retrieve data from a SQL Server table containing 2M records. I am using the Devexpress GridControl and have tried a simple code like this:

   DataTable dt = new DataTable();
   String sqlString = "select * from LARGETABLE left join TABLEB on LARGETABLE.ID=TABLEB.PARENTID";
   SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConnection);
   da.Fill(dt);
   bindingSource1.DataSource = dt;

...

   gridControl1.DataSource = bindingSource1

When line da.Fill(dt) is executed, an OutOfMemoryException error is raised after sometime.

Microsoft Server Studio does load the table without problems and shows all 2M+ rows in the grid. Is it possible to build such a data browser in c# and load large data sets without running out of memory?

score 1 · Answer 1 · answered Mar 20 '13 at 16:15

I believe binding of 2M records via the BindingSource and the 'DataTable' is not a good idea. Since you are using the DevExpress gridcontrol I suggest you use their server data-binding modes (synchronous or asynchronous). You can read more about this concepts in "Regular Binding Mode vs Regular Server Mode vs Instant Feedback Mode" article.
To start with Server-Mode please use the following How-To articles: Server Mode: Binding to Data Source Using 'LINQ to SQL Classes'

OutOfMemoryException when loading large table into DataSet

1 Answers1