C# CommandType.Text and CommandType.StoredProcedure

[Christopher Stratmann]

I have a SQL Server 2000 database which my C# application is storing data into. I would like to know if there is any real benefit using stored procedures from my C# code than using straight SQL text. I know there is probably a lot of benefit using a stored procedure if what I want to accomplish is very complicated. So here is my questions.... Is there any real performance gain to use a stored procedure if my SQL text is a simple 'select', 'update', 'insert', or 'delete' statement(meaning the SQL text only involves one table and is very basic)? Is there any real performance gain to use a stored procedure if my SQL text is a 'select' statement that has around 3 'inner joins'? Thanks Chris

[TJoe]

There are two things to consider: performance and flexibility. For performance, you may see a slight improvement with SPs because SQL Server can "compile" them ahead of time. When performing ad-hoc queries, SQL Server must "compile" them every time (with some exceptions). If you are running simple selects then you probably would never notice an improvement. When doing simply selects, your main benefit comes from flexibility. SPs allow you to quickly and easily change the underlying T-SQL code (whether it's a simple select or something more complex) *without* having to re-compile your .Net code. It is possible to "store" the T-SQL code in a config file. This would still allow you to change your code without recompiling your binaries, but SPs are generally the preferred method.

Take care, Tom ----------------------------------------------- Check out my blog at http://tjoe.wordpress.com

[Not Active]

In addition to what has already been stated, security is another reason to use stored procs over in-line SQL. Unless you have strong, bullet-proof validation, using in-line SQL opens your app to SQL injection attacks. Stored procs can also be secured at the server so if your app uses Windows authentication to grant access to SQL Server a user who is not authorized won't be able to run the proc, even if not using Windows authentication, different users, or groups of users can be secured.

---

only two letters away from being an asset