Best way to connect gridview to data source

Question

My DAL is created using Linq and all is fine. However, on one page, I'm to display a table (gridview) which pulls data from a SQL Server database table. However, I've heard many warnings about staying away from using any of the built in controls (ObjectDataSource and SQLDataSource). Is there any truth in this, regarding scalability/efficiency? I posted a similar problem before but, in this case, I have direct access to the server. What would be the best way to bind the data to gridview? I can't cache the data as is it tailored to individuals and I'd rather not store them in sessions as there could be between 100-200 custom objects being called. I haven't really dabbled in SQL, but with a bit of reading, I'm sure I could create my own custom server side paging/filtering/sorting. Would it be efficient if I created a stored procedure for each of these functions and called them from an objectdatasource, or should I call the SP using Linq to SQL (in my DAL) and then display that information directly to the gridview?

Thanks for any advice

Answer 1

There is nothing wrong in using a grid view, or custom object data source, if it is only for some hundreds of records. Scalability is an issue for thousands of records. Fastest Access possible would be via a DataReader filling a Datatable. Binding gridviews to a Datatable is always a good idea for a large number of records, as binding is fast and sorting also.

I have therefore written a library called modelshredder that can translate any IEnumerable collection of objects into a DataTable. It uses dynamically emitted code to do its Job, that's why it's fast enough for thousands of records. You can use plain linq-to-sql to write and execute your query, prefetably project into an annonymous type and call .ToDataTable() on the result.