Justins Fat Tire

Ok I posted how to get data from an excel file a bit ago. I have some update code that reads all files into a dataset, or reads from a specific file. You could easily modify this code to read from a specific sheet as well. public static DataSet GetAllSheetsFromExcelFile( string filename ) { DataSet ds; try { ds = new DataSet(); DataTable dtSheets = new DataTable(); // get a datatable with the worksheet name(s) OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0" ); con.Open(); // get a datatable of the sheetnames in this file DataTable dtNames = con.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" } ); // add each sheet as a new table to our dataset foreach( DataRow row in dtNames.Rows ) { DataTable dtTemp = new DataTable(); ...

Ok so I'm working on a fairly large asp.net 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...