How to handle sql queries

[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 ?

[Ignacio Machin]

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.
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.

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Mikael Janers]

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.

"Ignacio Machin" <ignacio.machin AT dot.state.fl.us> skrev i meddelandet
news:uL*************@tk2msftngp13.phx.gbl...

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.
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.

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Mikael Janers]

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.
"Ignacio Machin" <ignacio.machin AT dot.state.fl.us> skrev i meddelandet
news:Op**************@TK2MSFTNGP12.phx.gbl...

Hi Mikael,

Ok, then imagine for one moment that many queries used let's say two times each , now if the DB change you may have to track !!! and change all those
queries, recompile the system and finally and most important, test it.
The horizon get darker if you have more than one system accessing the same DB.
The sp works as the abstraction layer between the application and the
physical structure of the data. your application should be abstracted of how the data is keeped.

I would like to hear the opinion of others but I always use SP , I haven't used queries in my code since my first steps in ASP.

Hope this help,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Mikael Janers" <NO************@home.se__SPAMFILTER> wrote in message
news:OT**************@TK2MSFTNGP12.phx.gbl...

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.

"Ignacio Machin" <ignacio.machin AT dot.state.fl.us> skrev i meddelandet
news:uL*************@tk2msftngp13.phx.gbl...

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.

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.

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Mikael Janers]

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.
"Ignacio Machin" <ignacio.machin AT dot.state.fl.us> skrev i meddelandet
news:Op**************@TK2MSFTNGP12.phx.gbl...

Hi Mikael,

Ok, then imagine for one moment that many queries used let's say two times each , now if the DB change you may have to track !!! and change all those
queries, recompile the system and finally and most important, test it.
The horizon get darker if you have more than one system accessing the same DB.
The sp works as the abstraction layer between the application and the
physical structure of the data. your application should be abstracted of how the data is keeped.

I would like to hear the opinion of others but I always use SP , I haven't used queries in my code since my first steps in ASP.

Hope this help,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Mikael Janers" <NO************@home.se__SPAMFILTER> wrote in message
news:OT**************@TK2MSFTNGP12.phx.gbl...

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.

"Ignacio Machin" <ignacio.machin AT dot.state.fl.us> skrev i meddelandet
news:uL*************@tk2msftngp13.phx.gbl...

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.

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.

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation