calling a procedure or handling in code behind - which is better?

Question

Hi,

I am working on ASP.NET 2.0 application with c# language.

Can anyone tell me which is the best way of the following : scenario: I need to get data from the database and bind it to the grid view.

case 1: I can use a stored procedure(for iteraing the result which is obtained from the basic query and do operations on this result set) for getting the final result.

case 2: I can get the result ( obtained from the basic query) and do the iteration and operations in the code behind file.

Please let me know which case is best way of good programming.

Thanks Rupa

Answer 1

You can use a DataSet object for this purpose. Call the stored procedure and fill the DataSet object using DataAdapter.Fill method and

you can assign the datasource of the gridview using

DataSource roperty.

If you need to iterate through the datatable it can be done using the corresponding datatable in your dataset.

Answer 2

What type of operations will you perform to get the "final result?" Performing the bulk of the work on the database side is generally faster rather than bringing over loads of data then performing some manipulation (or worse, major filtration) that could've been handled on the database end.

Answer 3

I think that would depend on the situation, such as ammount of data being returned from your basic query, if you are heavilly filtering the data you would want that done as close to the database as possible.

Also a stored proc would allow you to change the query without redeploying your application.

However you might feel more comfortable doing your filtering in code or on the dataset.

If the database server was loaded then the the application would probably be faster at filtering.

Performance would probably dictate that you would be better off at the database in most circumstances.

Answer 4

My option is better to do in stored procedure for iteration.Because if the data goes high ,proc is the better option .

Answer 5

I'm sure this question might be better answered by reading a little about best practices. I think in your situation you might look into a programming model that will help you define some practices that best suit you and what you're trying to achieve.

In this case there are a few questions like are you going to write unit tests for your code? Is Seperation of Concerns an issue for you? How large is your program going to be?

Why not look to a best practice like approach like the Model View Presenter, sometimes called the Supervising Controller or Passive View or a more structured framework for ASP.NET like ASP.NET MVC where you might find that best practices are a lot easier to follow.

Answer 6

I would use case 1, but I also would try to avoid iterating the data and do all operations in one or a couple of queries. Iterating against an SQL server database is not quick, and the further away from the data, the slower it gets (like iterating in the gui and call a business layer that calls the databas).

This is how i would try to do it.

1. Create a temprary table
2. Get the data from your basic query with a filter into the temporary table and do all possible operations.
3. If i cant do all operations in your basic query, then I would perform some set operation on the temporary table
4. Return the temporary table to the calling application

Like this

CREATE PROC myProc @myParam int
AS
CREATE TABLE #temp1(myCol int) 
--only get the required data and apply any operations
INSERT INTO #temp1 (myCol) 
SELECT myCol FROM myTable WHERE myCol2=@myParam 


UPDATE #temp1 --do any operation on the temporary table

SELECT myCol FROM #temp1 --apply any additional filter or operations
GO