Harcoding SQL statements VS calling DB functions/procedures with the result set

Question

Is there any reason why one would define queries within the application code, hardcoded - instead of at the DB, as procedures/functions?

I recently started working in a ASP.NET project and all the queries that populate the pages are hardcoded in the C# code-behind files.

I asked what is the reason the application was designed like that - why we do not define all the queries as DB (in our case, Oracle) objects, such as procedures returning ref_cursors, instead.

Honestly I did understand the reply I got - but since I am not sure of the advantages of following my suggestion either - I left it as it is.

Could you please help in providing insights as to what is the best practice here?

I would say that my approach is better because if any business logic changes - meaning the logic in the way we retrieve data (queries) changes - no changes are required in the client side, no DLL fixes/new compilations/deployments are needed. The changes would be transparent to the application, happening at the DB server.

Another advantage I see is that it supports reusability, isolating and defining the query itself as an object allows us to reuse it wherever else we want (although an "atomic" object which sole purpose is to return the results of such query could also be created at the application side...)

Is there any advantage in doing the other way (hardcoded queries)?

Thanks!

Answer 1

Good question and let's not repeat again. Refer  (1) & (2).

---

If this post answers your question, please click Mark As Answer. If this post is helpful please click Mark as Helpful.

Regards,
Nair S

Answer 2

I would say inline query or procedure is depend on scenario... size of data to be returned by query, query complexity etc.

I think they can't use stored procedures for database because they can't have stored procedures for the Read Only database.

Please get the pros and cons of the Inline VS Stored procedure from the Nair S reply

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 3

Hi Veverke

If I understand it correct. The code in the C# Code behind files execute at Web Server. So even if the Hard Coded SQL Queries change

the Web UI will not be affected. (Provided SQL queries were in a separate DLL).

I may not be contrasting these 2 approaches completely but this is what I think:

1. Hard Coded Queries in Code.

Adv:-

Queries aren't exposed

Mostly SQL syntaxes are similar (across RDBMS's) but ADO.NET supported stored procedure calls is compatible

only with SQL Server

2. Stored Procedures

Adv:- 

Less data sent over wire to query

Single change will reflect updates to all dependent applications

---

.NET Maniac -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Answer 4

Thanks for replying, Prateek,

but it is not a Web Server and I do not think you are right in saying that ADO.NET supports stored procedure calls only from SQL Server (unless you mean that doing that with Oracle depends in a different dll, which is System.Data.OracleClient. I did just that today (returned a ref cursor from a stored procedure into a data table).

Answer 5

Hi Veverke,

For asp.net please use this forum: http://forums.asp.net/

---

Bob Shen [MSFT]
MSDN Community Support | Feedback to us