What is your best-practice advice on implementing SQL stored procedures (in a C# winforms application)?

Question

I have read these very good questions on SO about SQL stored procedures:

• When should you use stored procedures? and
• Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s?

I am a beginner on integrating .NET/SQL though I have used basic SQL functionality for more than a decade in other environments. It's time to advance with regards to organization and deployment. I am using .NET C# 3.5, Visual Studio 2008 and SQL Server 2008; though this question can be regarded as language- and database- agnostic, meaning that it could easily apply to other environments that use stored procedures and a relational database.

Given that I have an application with inline SQL queries, and I am interested in converting to stored procedures for organizational and performance purposes, what are your recommendations for doing so?

Here are some additional questions in my mind related to this subject that may help shape the answers:

• Should I create the stored procedures in SQL using SQL Management Studio and simply re-create the database when it is installed for a client?
• Am I better off creating all of the stored procedures in my application, inside of a database initialization method?
• It seems logical to assume that creating stored procedures must follow the creation of tables in a new installation. My database initialization method creates new tables and inserts some default data. My plan is to create stored procedures following that step, but I am beginning to think there might be a better way to set up a database from scratch (such as in the installer of the program). Thoughts on this are appreciated.
• I have a variety of queries throughout the application. Some queries are incredibly simple (SELECT id FROM table) and others are extremely long and complex, performing several joins and accepting approximately 80 parameters. Should I replace all queries with stored procedures, or only those that might benefit from doing so?
• Finally, as this topic obviously requires some research and education, can you recommend an article, book, or tutorial that covers the nuances of using stored procedures instead of direct statements?

Answer 1

Consider skipping stored procedures for an ORM. Consider using:

• LINQ To SQL
• Entity Framework

• SubSonic

• You'll be writing less boiler plate ListCustomer and GetCustomerByID code when you could be adding more value to your application.

• IMO, there isn't any real compelling reason to choose stored procedures with the modern toolset that we have in the Microsoft stack.

• The move away from inline SQL statements is good, and an ORM will help parameterize your queries for you. You don't have to think about it.

• You don't have to mess with ADO.NET objects at all. Code your data access in an object oriented fashion.

Answer 2

You missed one:

http://stackoverflow.com/questions/2734007/when-is-it-better-to-write-ad-hoc-sql-vs-stored-procedures/2734158#2734158

My answer is: don't use stored procedures at all.

Answer 3

There are several compelling reasons to avoid giving table access to very many logins, including application logins, and these drive the use of stored procedures. (I generally do not ascribe any importance to using SPs for performance reasons - SQL Server caches even adhoc query plans).

Stored procedures give your database much more capability in defining its interface boundaries. In many cases, views are not sufficient to control the interface.

Any framework built solely on tables and views (note that many frameworks can build on top of SP results) is going to be severely limited in letting your database protect itself and control itself.

As a simple example, neither tables nor views can be parameterized. If you have a very large table or view and you want to enforce all users to specify a certain set of filter criteria (for instance a snapshot date or effective date), there is no way to enforce this at the database call interface. The framework can submit queries for all time. If the table/view is not exposed, and the only interface is through an SP or table-valued UDF, then the parameters to that SP or UDF MUST be provided, thus satisfying your database's need to ensure that it is used properly.

Other examples, where views may or may not work, include hiding privacy information for certain users, hiding internal keys, hiding internal implementation details, and enforcing complex security rules.

As far as scripting the creation of your database schema, including objects in the correct dependency order, there are several tools to do this (and generate change scripts), including Red Gate SQL Compare and Apex SQLScript.

Answer 4

Use stored procedures if you really have a performance requeriment, particularly if one stored procedures will be called thousands of times per minute. This way sql engine avoids severals steps for processing the statement. GPS Tracking systems is an example. Say you have 10000 vehicles which reports a 3 positions per minute. In this case stored procedures helps performance.



If not, instead of CRUD sql statements, use ORM features.