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]

Re: How to handle sql queries

Hi Mikael,[color=blue]
> 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[/color]
might[color=blue]
> affect some area of the application you didn't think about. Ofcourse you[/color]
get[color=blue]
> the speed advantage but I'd be happy to give that up for a better design.[/color]

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.
[color=blue]
> 2) Should I declare the sql query localy in the method that wants to[/color]
execute[color=blue]
> 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[/color]
know[color=blue]
> other parts of the application will not be affected.[/color]

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]

Re: How to handle sql queries

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.mach in AT dot.state.fl.us > skrev i meddelandet
news:uLBZey#ZDH A.440@tk2msftng p13.phx.gbl...[color=blue]
> Hi Mikael,[color=green]
> > 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[/color]
> might[color=green]
> > affect some area of the application you didn't think about. Ofcourse you[/color]
> get[color=green]
> > the speed advantage but I'd be happy to give that up for a better[/color][/color]
design.[color=blue]
>
> IMHO the SP are the best place to put the queries, if you need to change[/color]
a[color=blue]
> SP to return some other things, well then just create a new SP and call it
> from whre you need it.
>[color=green]
> > 2) Should I declare the sql query localy in the method that wants to[/color]
> execute[color=green]
> > it ? This is the most appealing method to me. This way I know exactly[/color][/color]
what[color=blue][color=green]
> > scope the query can be executed in. I can safetly modify the query and[/color]
> know[color=green]
> > other parts of the application will not be affected.[/color]
>
> 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
>
>[/color]

[Mikael Janers]

Re: How to handle sql queries

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.mach in AT dot.state.fl.us > skrev i meddelandet
news:OpAwxs$ZDH A.2632@TK2MSFTN GP12.phx.gbl...[color=blue]
> Hi Mikael,
>
> Ok, then imagine for one moment that many queries used let's say two[/color]
times[color=blue]
> 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[/color]
same[color=blue]
> DB.
> The sp works as the abstraction layer between the application and the
> physical structure of the data. your application should be abstracted of[/color]
how[color=blue]
> the data is keeped.
>
> I would like to hear the opinion of others but I always use SP , I[/color]
haven't[color=blue]
> 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" <NOSPAM__janers @home.se__SPAMF ILTER> wrote in message
> news:OTWkUf$ZDH A.1916@TK2MSFTN GP12.phx.gbl...[color=green]
> > Don't you find it hard to keep order of things with SP's ? The project[/color][/color]
I'm[color=blue][color=green]
> > working on now have over 1000 queries (at least) Just to come up with[/color][/color]
good[color=blue][color=green]
> > names for all of them is brainkilling.
> >
> > "Ignacio Machin" <ignacio.mach in AT dot.state.fl.us > skrev i meddelandet
> > news:uLBZey#ZDH A.440@tk2msftng p13.phx.gbl...[color=darkred]
> > > Hi Mikael,
> > > > 1) Should I use stored procedures ? I like them because I have all[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > > queries in one place, easy to find and update... But I dont like[/color][/color][/color]
them[color=blue][color=green][color=darkred]
> > > > because you never know where they are used from. If you change one[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > might
> > > > affect some area of the application you didn't think about. Ofcourse[/color][/color]
> you[color=green][color=darkred]
> > > get
> > > > the speed advantage but I'd be happy to give that up for a better[/color]
> > design.[color=darkred]
> > >
> > > IMHO the SP are the best place to put the queries, if you need to[/color][/color]
> change[color=green]
> > a[color=darkred]
> > > SP to return some other things, well then just create a new SP and[/color][/color][/color]
call[color=blue]
> it[color=green][color=darkred]
> > > 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[/color][/color][/color]
exactly[color=blue][color=green]
> > what[color=darkred]
> > > > scope the query can be executed in. I can safetly modify the query[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > know
> > > > other parts of the application will not be affected.
> > >
> > > this is the WORST method , in case you change the DB you have queries[/color][/color]
> all[color=green][color=darkred]
> > > over the system or systems , very bad design IMO. as I said before use[/color][/color]
> SP[color=green][color=darkred]
> > > when ever you can
> > >
> > >
> > > Hope this help,
> > >
> > > --
> > > Ignacio Machin,
> > > ignacio.machin AT dot.state.fl.us
> > > Florida Department Of Transportation
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Mikael Janers]

Re: How to handle sql queries

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.mach in AT dot.state.fl.us > skrev i meddelandet
news:OpAwxs$ZDH A.2632@TK2MSFTN GP12.phx.gbl...[color=blue]
> Hi Mikael,
>
> Ok, then imagine for one moment that many queries used let's say two[/color]
times[color=blue]
> 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[/color]
same[color=blue]
> DB.
> The sp works as the abstraction layer between the application and the
> physical structure of the data. your application should be abstracted of[/color]
how[color=blue]
> the data is keeped.
>
> I would like to hear the opinion of others but I always use SP , I[/color]
haven't[color=blue]
> 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" <NOSPAM__janers @home.se__SPAMF ILTER> wrote in message
> news:OTWkUf$ZDH A.1916@TK2MSFTN GP12.phx.gbl...[color=green]
> > Don't you find it hard to keep order of things with SP's ? The project[/color][/color]
I'm[color=blue][color=green]
> > working on now have over 1000 queries (at least) Just to come up with[/color][/color]
good[color=blue][color=green]
> > names for all of them is brainkilling.
> >
> > "Ignacio Machin" <ignacio.mach in AT dot.state.fl.us > skrev i meddelandet
> > news:uLBZey#ZDH A.440@tk2msftng p13.phx.gbl...[color=darkred]
> > > Hi Mikael,
> > > > 1) Should I use stored procedures ? I like them because I have all[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > > queries in one place, easy to find and update... But I dont like[/color][/color][/color]
them[color=blue][color=green][color=darkred]
> > > > because you never know where they are used from. If you change one[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > might
> > > > affect some area of the application you didn't think about. Ofcourse[/color][/color]
> you[color=green][color=darkred]
> > > get
> > > > the speed advantage but I'd be happy to give that up for a better[/color]
> > design.[color=darkred]
> > >
> > > IMHO the SP are the best place to put the queries, if you need to[/color][/color]
> change[color=green]
> > a[color=darkred]
> > > SP to return some other things, well then just create a new SP and[/color][/color][/color]
call[color=blue]
> it[color=green][color=darkred]
> > > 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[/color][/color][/color]
exactly[color=blue][color=green]
> > what[color=darkred]
> > > > scope the query can be executed in. I can safetly modify the query[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > know
> > > > other parts of the application will not be affected.
> > >
> > > this is the WORST method , in case you change the DB you have queries[/color][/color]
> all[color=green][color=darkred]
> > > over the system or systems , very bad design IMO. as I said before use[/color][/color]
> SP[color=green][color=darkred]
> > > when ever you can
> > >
> > >
> > > Hope this help,
> > >
> > > --
> > > Ignacio Machin,
> > > ignacio.machin AT dot.state.fl.us
> > > Florida Department Of Transportation
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]