How to handle sql queries

  • Thread starter Thread starter Mikael Janers
  • Start date Start date
M

Mikael Janers

Hello!

I have a question that is not realy only C# but I didn't find any better
group...

What is the most common/best way to handle sql queries in C#

1) Should I use stored procedures ? I like them because I have all my
queries in one place, easy to find and update... But I dont like them
because you never know where they are used from. If you change one it might
affect some area of the application you didn't think about. Ofcourse you get
the speed advantage but I'd be happy to give that up for a better design.

2) Should I declare the sql query localy in the method that wants to execute
it ? This is the most appealing method to me. This way I know exactly what
scope the query can be executed in. I can safetly modify the query and know
other parts of the application will not be affected.

3) I saw some other method to declare the queries as static class members.
To me it seems like a mixture of the above 2. The scope for the queries are
well defined (similar to 2), but it might be huge . You can have your
queries in a region in the class that is easy to find and update (similar to
1).

There are probably as many methods to use as there are developers... but
please give me comments, what do you perfer and why ?
 
Hi Mikael,
1) Should I use stored procedures ? I like them because I have all my
queries in one place, easy to find and update... But I dont like them
because you never know where they are used from. If you change one it might
affect some area of the application you didn't think about. Ofcourse you get
the speed advantage but I'd be happy to give that up for a better design.
Click to expand...

IMHO the SP are the best place to put the queries, if you need to change a
SP to return some other things, well then just create a new SP and call it
from whre you need it.
2) Should I declare the sql query localy in the method that wants to execute
it ? This is the most appealing method to me. This way I know exactly what
scope the query can be executed in. I can safetly modify the query and know
other parts of the application will not be affected.
Click to expand...

this is the WORST method , in case you change the DB you have queries all
over the system or systems , very bad design IMO. as I said before use SP
when ever you can


Hope this help,
 
Don't you find it hard to keep order of things with SP's ? The project I'm
working on now have over 1000 queries (at least) Just to come up with good
names for all of them is brainkilling.
 
Hello Ignacio !

Thanks for your feedback, its very appreciated =)

In all the systems I have build all the queries are most often used only
once (otherwise you should have a look at your database layer design).
Ofcourse some of them are used more than once but I would say thats just a
small procentage of the queries, and since you dont know which queries are
used more than once you get the problem I wrote about earlier.

I know it was tough in the old ASP world to make a clean separation of
database layer, buisness layer and interface layer. But now with C# and .NET
its much easier (I think anyway). So I was just wondering if .NET developers
still use SP's. And why ? I dont buy the argument that SP's is a good
abstraction layer to the DB. In my world that's the job for the database
layer.
 
Back
Top