System.OutOfMemoryException in DataAdapter

[C#Coder]

I am using a DataAdapter to collect the results of a stroed proc into a
DataSet. However when I run this application I get the following exception...
I know the data coming out of running the Stored Proc is hugh. Is there an
efficient way to get the data instead which is also permformant enough..

Exception of type 'System.OutOfMemoryException' was thrown.
at System.Data.Common.StringStorage.SetCapacity(Int32 capacity)
at System.Data.RecordManager.set_RecordCapacity(Int32 value)
at System.Data.RecordManager.GrowRecordCapacity()
at System.Data.RecordManager.NewRecordBase()
at System.Data.DataTable.NewRecordFromArray(Object[] value)
at System.Data.DataTable.LoadDataRow(Object[] values, Boolean
fAcceptChanges)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset,
DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32
startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object
parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

Code Snippet:
<<<<<<<<<<<<<<<<<<<<<<<<<<<
DataSet ds = new DataSet();
using (SqlConnection cn = new SqlConnection(cnnMasterString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.CommandText = strStoredProcName;
cmd.CommandTimeout = commandTimeOut;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

try
{
// Fill the DataSet.
adapter.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
}
}

return ds;

 

[Miha Markic]

Why don't you limit the amount of the data in stored proc? Why would you
need that much data?
Anyway, you might try using a datareader instead of filling a dataset.

 

[Patrice]

How much data re you trying to retrieve ? Do you need them all ?

Usually the idea is :
- if this to present each of those rows to the user, this is likely useless
as the user will filter them out to find out what he is interested in. so it
could be better to filter before getting the data rather than after.
- if this is to perform a somewhat compelx process it could be better to do
this using a stored procedure so that the all calculation takes place on the
server. This way you don"t have to retrieve all those details, you can just
get the needed results...

 

[Paul]

Simple.....

Don't use datasets.

Use DataReaders and model objects.

More performant and if you keep your model objects data specific (DTO/BLL
elsewhere) they will have a much smaller footprint than datasets by a fair
way.

On a serious note tho, this will only get worse as more data is added so you
really need to look at workflow solutions and why you need all the data at
once.

I am using a DataAdapter to collect the results of a stroed proc into a
DataSet. However when I run this application I get the following
exception...
I know the data coming out of running the Stored Proc is hugh. Is there an
efficient way to get the data instead which is also permformant enough..

Exception of type 'System.OutOfMemoryException' was thrown.
at System.Data.Common.StringStorage.SetCapacity(Int32 capacity)
at System.Data.RecordManager.set_RecordCapacity(Int32 value)
at System.Data.RecordManager.GrowRecordCapacity()
at System.Data.RecordManager.NewRecordBase()
at System.Data.DataTable.NewRecordFromArray(Object[] value)
at System.Data.DataTable.LoadDataRow(Object[] values, Boolean
fAcceptChanges)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset,
DataTable datatable, String srcTable, DataReaderContainer dataReader,
Int32
startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object
parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

Code Snippet:
<<<<<<<<<<<<<<<<<<<<<<<<<<<
DataSet ds = new DataSet();
using (SqlConnection cn = new SqlConnection(cnnMasterString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.CommandText = strStoredProcName;
cmd.CommandTimeout = commandTimeOut;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

try
{
// Fill the DataSet.
adapter.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
}
}

return ds;

 

[misfit815]

Does anyone have metrics on this? What *are* the limits? Is it running up
against a fixed constraint, or does it really exhaust system resources?

In our case, we have a large, random data set which is using SqlBulkCopy to
get into the database. Except for this particular constraint, DataSets really
are our most viable option.