What is your best-practice advice on implementing SQL stored procedures (in a C# winforms applicatio
- by JYelton
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?
