What is the best strategy to avoid hard coded SQL statements

Question

The other day I was showing a colleague some code I was working on, and in the passing he commented on the fact that I have hard coded SQL statements. Now these SQL Statements are extremely static and the ones that do tend to change I made into views on the database and have a hardcoded "select columns from view where ''" kind of affair.

So my question is what is the best practice in these circumstances?

Is it to add the SQL statements as resources to the project and then call the resource in code ? Is there a better approach?

EDIT: In this case I interact with both SQL Server 2005 and Oracle Databases using .Net2.0

Answer 1

1. Stored procedures. Prevents all sorts of possible issues with injection, maintenance. Put database code where database code belongs.

2. ORM tool (like Hibernate, Subsonic, etc.) means you never see a line of T-SQL in the first place. Steeper learning curve, but a great practice to start sooner rather than later.

Answer 2

You really should build applications for a particular data model.

If your underlying data model changes, which is about the only thing that should require a SQL statement change, there's really no way to avoid doing a code change as well.

I don't think there's anything wrong with hard-coded SQL Statements, myself. There are some code generators for handling databases, but you still have to design it around a particular known data model, so you still have the same problem if the model changes -- code has to change.

Answer 3

I avoid harder coded SQL at all costs. I prefer to call Store Procedures.

If you are using .NET 3.5 and MS SQL Server you might look at LINQ, which then your SQL statements are in code.

Answer 4

It really depends on your data model and what permissions you have to the database. I've been to companies who do not allow their .Net developers to write Stored Procs and only allow a select group of database developers to do such.

Like Frakkle mentioned, if your data model doesn't change often, then most likely the hard coded statements won't be a problem.

There are ways to parametrize your hard coded sql statements if you don't use Stored Procedures or an ORM tool. This is a recommended practice.

If you are using MSSql 2005+ then, from what I've seen and tested, your hard coded statements will have their execution plans still cached. There are also hints that can be used (like OPTIMIZE FOR UNKNOWN) that will help it out the performance even more.

Evaluate your environment and the amount of change at the data layer and you should be ok either way you go.

Answer 5

Some ideas:

1. At the database level, restrict the application user from executing objects other than stored procedures.
2. Use a database library that either binds SPs to classes, or only works with SPs.
3. Coding policy combined with Code Reviews/Walkthroughs.

These may not be the friendliest approaches, but they do have a value in certain environments.