Where to put the SQL logic
I have an existing SQL Server database whose structure I cannot change, although I can add stored procedures or new tables if I want. I have to write a separate program to access the database, process data and generate some reports. I chose C # and Visual Studio as we are pretty much an MS store.
I started looking into using VS 2008 to create the said program. I am trying to decide where to put some SQL logic. My main goals are to simplify the development process as much as possible and get the job done quickly.
Should I put my SQL logic in a stored procedure and just call the stored procedure, while SQL Server work with grunt and pass the results? Or am I better off keeping the SQL query in my code by creating the appropriate command and executing it against SQL Server?
I have a feeling that the former could work better, but I had to manage the stored procedure separately before the rest of my codebase, right?
UPDATE: It was pointed out that the performance should be the same if it matches the SQL code in a C # program or stored procedure. If so, what's the easiest to maintain?
2009-10-02: I really needed to think about which answer to choose. At the time of this writing, there were 8 answers, mostly divided by 5-3 in favor of placing SQL logic in the application. On the other hand, there were 11 votes, 9-2 split in favor of placing SQL logic in stored procedures (along with a few warnings about how this happens). Therefore I am torn. In the end, I'm going with the votes. However, if I have any problems I will go back and change my selected answer :)
source to share
I suggest taking a look at LINQ to Entities, which provides a wrapper around object relational mapping around any SQL (CRUD) statements, abstracting the logic needed to write to the database and allowing you to write OO code instead of using SQLConnections and SQLCommands.
OO code (save method doesn't exist, but you get the gist of it):
// this adds a new car to the Car table in SQL, without using ANY SQL code
Car car = new Car();
Car.BrandName = "Audi";
Car.Save(); //save is called something else and is on the
// datacontext the car is in, but for brevity sake..
SQL code as string in SqlCommand:
// open sql connection in your app and
// create Command that inserts car
SqlConnection conn = new SqlConnection(connstring);
SQlCommand comm = new SqlCommand("INSERT INTO CAR...");
// execute
source to share
As far as your point of view regarding performance changes between nesting your code in a .NET source or SQL Server stored procedures, you don't actually see the difference between the two!
This is because the same execution plan will be generated by the SQL server if the access to the T-SQL data in two different sources is the same.
You can see this in action by running a SQL Server Profiler trace and comparing the execution plans that are generated by two different T-SQL query sources.
In light of this, and back to the main question of your question, then your choice of implementation should be determined by ease of development and your requirements for future extensibility. Since you are the only person who will be working on the project, move on to whichever you prefer, and I suspect that it should maintain centralized code, i.e. inside the Visual Data Access Layer (DAL).
Stored procedures can be part of their own, however, when you have separate development functions within your organization / team. For example, you might have database developers who can create data access code for you and do it independently from the application, freeing you from working with other modules of code.
source to share
Take a look at Entity Spaces . It's a code generation tool, but it will do more.
There is a small part of footwork in learning the instrument, but once you start working, you will never look back. Saves hours of work. (I don't work for them BTW!)
source to share
Should I put my SQL logic in a stored procedure
Well, that depends on what "SQL logic" is, doesn't it? If it is purely database related, a stored procedure might be most appropriate. If it's business logic, the rules that govern how your application works, it definitely belongs to your application.
which is the easiest to maintain?
Personally, I find the code on the application side easier because modern languages like C # are much more expressive than SQL. Doing any important processing in T-SQL quickly becomes tedious and difficult to read.
source to share