Is it better to use Stored Procedures always when possible?

[cesark]

Hi !

I usually use Stored Procedures inside Sql Server from my aspx pages, but for data that is retrieved to fill a dropdownlist and other form controls I use this structure:

Dim Cmd1 As New SqlCommand("Select Field1, Field2 From Table1", strConnection)
   strConnection.Open()
   Dim var1 As SqlDataReader = Cmd1.ExecuteReader(CommandBehavior.CloseConnection)

   Control1.DataSource = var1
   Control2.DataBind()

Is it better that I use stored procedures always where possible in operations with the database? Including my code example?

Thank you,
Cesar

 

[LV]

Yes, it is better. Stored procedures are precompiled and sitting directly in the database, performance is better. Easier to maintain, too. Just edit the procedure, without having to change the hard coded SQL on the page and then recomplile the project.

 

[cesark]

Ok, thank you for clarify my little doubt

Cesar

 

[cappmgr]

I know you did not mention input controls but I thought I'd throw this out. Another advantage to SP is you stop SQL injection and you do not have to check user input for ticks, .... I would also put all of your database operations in a data access layer, this being a seperate class file. In your case this comes into play if you have the same control on more than one page. You would then be able to use one method to populate the controls. Another maintaince plus.
Marty

 

[dragonwell]

In the past I used stored proceudres for everything, but lately I have been experimenting with using NO stored procedures. Here's why:

[ul square]
[li]It is one more place that needs to be changed if the the table changes. If you add a column to the table, typically you also need to add a member and or property to an object in code, the stored procedure(s) for accessing the table, plus the code that calls the stored produre(s). I know you don't make changes like that late in production, but during the development process it is good to be able to make changes easily to the model without the hassle of having to maintain code in several places.[/li]
[li]Stored procedures do not execute any faster than ad hoc queries on MS SQL Server 2000. (based on my own tests)[/li]
[li] The risk of Sql injection attacks is eliminated by using parameterized queries.[/li]
[li]without stored procedures, you end up with an application that could easily run on another database system such as Access, MySql or Oracle.[/li]
[li] if you use a persistence framework (O/R Mapper) you can simply design your objects and your tables and let the framework deal with the sql.[/li]
[/ul]

Just something to think about!