I am exporting large set of records from database to Dataset which may be a cause for System.OutofMemory exception. To prevent this, as first step I have decided to use SQL Datareader. My concern is presentation should not be changed and there should be minimal code change in BL, I should write a method in DL which should retrieve data using SQL reader and fill the dataset and return to BL.
6
-
have you thought about using SqlDataAdapter.Fill(DataTable)?Dani– Dani2011-09-23 07:52:47 +00:00Commented Sep 23, 2011 at 7:52
-
Currently I am using DataAdapter only to fill records to dataset!banupriya– banupriya2011-09-23 07:56:26 +00:00Commented Sep 23, 2011 at 7:56
-
I see, are you trying to get all the data from the table or only records matching a specific criteria?Dani– Dani2011-09-23 07:58:07 +00:00Commented Sep 23, 2011 at 7:58
-
Only records matching a specified search criteriabanupriya– banupriya2011-09-23 07:58:49 +00:00Commented Sep 23, 2011 at 7:58
-
about how many records are returned then?Dani– Dani2011-09-23 08:00:46 +00:00Commented Sep 23, 2011 at 8:00
|
Show 1 more comment
2 Answers
You can use DataTable.Load() method.
2 Comments
kbvishnu
I am trying to get data using DataReader instead of DataAdapter, because I need to handle large amount of data. After getting using ExecuteReader(), i am loading it to datatable. So whether this is a good approach. ?
KV Prajapati
@Harie - I suggest the Load() method. Fill() especially used to populates DataSet (adding one or more tables), creates relations and so on. Have a look at article by Scott Mitchell- 4guysfromrolla.com/articles/050405-1.aspx
Whether you or the Framework fills the DataSet you'll have the OutOfMemoryException. You have to return an IEnumerable and change the BL code to handle it.
Alternatively, you could try to set the DataTable.MinimumCapacity to avoid memory fragmentation.
1 Comment
Chris
+1 because I agree - how does going via a datareader help reduce memory usage? Presumably though it may be possible to do crazy things with an object that looks like a DataTable but actually has an ienumerabe inside it to return row data. Would be tricky to do but could prevent problems with recoding the BL.