Querying data: Stored procedures or Select calls from C# class?

[VM]

I'm working on a win appication that is constantly querying a small-sized
DB. Until now, I've been using Selects from within my app but, all of a
sudden I remembered of an application I was working on a year ago (with
PowerBuilder) that queried the DB by invoking stored procedures that return
the data to the application. One of the main problems was that there were
over 100 SPs so it was pretty difficult to maintain. So my question: Is it
better to place my SQL queries into SPs and call them from within my
application or should I just retrieve the data by using queries from the
application? I created a class specifically to call methods that access the
DB so any maintenance would be here.

Thanks.

[William Ryan]

In general, SP's are much better. 1) You can fix the proc without
recompiling your app 2) SECURITY (which should probably be 1) 3) less code
4) Performance.

I have a web app that I load users into a table and corresponding stored
procs that go with their role in another table. So I can use a Generic proc
to find out all of the information I need to dynamically populate things for
them. using ExecuteScalar, I can return the proc name that I need at any
given time and use use that value in turn to run other procs. I can fulfill
user request changes while I'm on the phone with them totally transparent to
them. This could not be done with Dynamic SQL. Maintenance is simple and
it scales very well. THe same code set that originally had 25 users now
works for over 350...and it's not b/c my design was that great, it's just
that using procs to retrieve other ones, and in some cases create new ones,
is a very flexible approach.

One thing you may want to consider for Parameterized procs. You can have
the proc name and an arraylist with parameters in the function signature of
whatever your DataAccess layer is. If the ArrayList.COunt is 0, then don't
add any params. Otherwise, clear the existing parameters collection and
iteratively add the values to the params collection. If you use an array or
hashtable for instnace, you can still use an interative approach to use
named params with their corresponding values and DataTypes.

I know there are many out there who are dyed in the wool Dynamic SQL
advocates, but IMHO, using Procs is better in every important way.

Cheers,

Bill

I'm probably opening up a can of worms here
"VM" <vo******@yahoo .com> wrote in message
news:eH******** *****@TK2MSFTNG P09.phx.gbl...

I'm working on a win appication that is constantly querying a small-sized
DB. Until now, I've been using Selects from within my app but, all of a
sudden I remembered of an application I was working on a year ago (with
PowerBuilder) that queried the DB by invoking stored procedures that return the data to the application. One of the main problems was that there were
over 100 SPs so it was pretty difficult to maintain. So my question: Is it better to place my SQL queries into SPs and call them from within my
application or should I just retrieve the data by using queries from the
application? I created a class specifically to call methods that access the DB so any maintenance would be here.

Thanks.