Use DataReaders instead of DataSets

Ok so I'm working on a fairly large application. When we first started we were using DataSets for ease of use. However as the app has gotten large we're finding DataSets are very heavy in memory and filling them from the database takes a lot of time. So after some research I found DataReaders are the best thing to use. We use the data access blocks (3.1) from microsoft and swithing from ExecuteDataSet to ExecuteDataReader saved almost 2 seconds. It was a huge savings. Switching to datareaders isn't without some problems. In some instances we need hierarchical presentation and datasets work very well for that. Also some of our classes expose dataviews as properties and that creates some problems. However most of this can be overcome with some better OOD. And when using datareaders always always always close them! So use the using statement in c# which closes the datareader when done. So using the data acccess blocks with a stored procedure here is some sample code of what we do. This is from hand so it might not compile straight out, but it should be pretty close.

IDataReader reader;

// create the database and stored procedure
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcedureCommand("ProcName");

// add input parameters to the stored procedure
db.AddInputParameter( command, "@pInputParam", DBType.Int32, 1 );

// get the data reader, trhe using statement closes the reader when done
using( reader = db.ExecuteDataReader( command ) )
while( reader.Read() )
// do stuff with reader
}//end while
}//end using


Popular Posts