How to use Stored Procedures w/ ASP.NET DataGrid

Question

Question can also be:

What is your preferred way of invoking stored procedures to fill a DataGrid?

I am currently developing an ASP.NET Page, and I would like to know if Linq is the right way to go for use with my SQL Server DB.

ADO seems nice too, so I would just like to have feedback on what is the most appropriate in general terms..

LINQ sure makes it easy to call a stored procedure, but I am kind of stuck in trying to find out the right way to put the result set from the stored procedure to a datagrid.

Don't think I want to do a foreach there..but all examples seem to point that way.. or I'm just confused

I have read this blog post, and this one, it seems the ways to go are foreach and Ienumerable methods.. and I am a bit confused about the whole thing.

I am also thinking about the tutorial for creating a Movie DataBase where the DataGrid simply became unnecessary in that case.

What is the simplest, most to the point way of accessing a simple SQL Procedure and returning a populated datagrid, or datagrid like data presentation model?

Answer 1

Using a SqlDataSource is the most straightforward way. If you are isolating all of your DB calls in a business layer then you could simply create an ObjectDataSource that called the stored procedure. While it may feel like overkill here, this is a useful skill and very easy to implement once you get the hang of it.

You really don't need LINQ or a foreach, etc. if you'll be using a data-bound control since you won't need to fill it manually (this is what data binding is all about).

One more thing - I would strongly recommend that you use a GridView rather than the DataGrid; it is newer, has many more features automated, and is a more capable and attractive control. Let me know if you need any sample code.

Answer 2

I'd suggest using Linq to SQL. You are correct in thinking that you should not be using any foreach loops - all you need is databinding.

eg. In your code behind, you might have a DataBind event that does something like this:

protected void GridView1_DataBanding(object sender, EventArgs e)
{
            // Get an instance of our generated DataContext class.
            DAL.AdventureWorks db =
                new DAL.AdventureWorks(
                    WebConfigurationManager.ConnectionStrings["AdventureWorksCS"].ConnectionString);

            // Get a strongly typed List from the Stored Procedure output
            List<DAL.MySPResult> myData = db.MySP().ToList();

            GridView1.DataSource = myData;
}

You will need to have setup your Stored Procedure in your Linq to SQL Classes by dragging it onto the design surface.

Hope that helps.