Best Practices Web.config + SQL Statements

[Adam Wimsatt]

I've done a lot of searching on this subject and so far I have come up support for both sides of the issue, but I want to know what you have found that works the best. Is it better to put SQL statements into Web.Config or to use Stored Procedures / Packages (Oracle=Packages, MS SQL Server=Stored Produces). I have to develop with both database types and I have tried both methods. One of my Web.Config files has over 1,500 lines of SQL code in it. The other database has over 200 Stored Procedures. So which method do you think is better or do you have a different solution then the two mentioned above? From my experience I prefer to add to the Web.Config file rather then creating Stored Procedures and then havening ( George Momment[^] ) to set permissions on it. --> side note: Anyone else just love using the word havening rather then having? Thanks, Adam

---

Adam Wimsatt www.liquidneon.com

[Member 96]

Many of the sources of info on this I've seen since starting asp.net say to use stored procedures as being more secure, AFAIK performance should be better with a sp as well shouldn't it? ------------ Tomorrow is the same day as today was yesterday.

[Adam Wimsatt]

You are correct. I decided I would see what Microsoft had to say and they had a very nice writeup about it... --Clip--_

Stored Procedures vs. Direct SQL

Most code fragments shown in this document use SqlCommand objects to call stored procedures to perform database manipulation. In some cases, you will not see the SqlCommand object because the stored procedure name is passed directly to a SqlDataAdapter object. Internally, this still results in the creation of a SqlCommand object.

You should use stored procedures instead of embedded SQL statements for a number of reasons:

• Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
• Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
• Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
• Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
• Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

The SQL Server online documentation strongly recommends that you do not create any stored procedures using "sp_" as a name prefix because such names have been designated for system stored procedures. SQL Server always looks for stored procedures beginning with sp_ in this order:

_

_* Look for the stored procedure in the master database.

• Look for the stored procedure based on any qualifiers provided (database name or owner)._2. Look for the stored procedure, using dbo as the owner if an owner is not specified The Article[^]

  ---

  Adam Wimsatt www.liquidneon.com

[Not Active]

Good article, thanks for pointing it out.

[Teo]

Does any one know performance decreases if the number of stored procedures increases? For example: A DB with 300 stored procedures runs slower than a DB with the same tables and data but with no stored procedure. I have not enough data to run this test. Thanks