Is it a good practice to avoid ad hoc sql altogether in ASP.NET applications?

Instead create only stored pro开发者_高级运维cedures and call them from from the code?

There is a place for dynamic SQL and/or ad hoc SQL, but it needs to be justified based on the particular usage needs.

Stored procedures are by far a best practice for almost all situations and should be strongly considered first.

This issue is a little bigger than just procs or ad hoc, because the database has a wide variety of tools to define its interface, including tables, views, functions and procedures.

People here have mentioned the execution plans and parameterization but, by far, the most important thing in my mind is that any technique which relies on exposed base tables to users means that you lose any ability for the database to change its underlying implementation or control security vertically or horizontally. At the very least, I would expose only views to a typical application/user/role.

In a scenario where the application or user's account only has access to EXEC SPs, then there is no possibility of that account being able to even have a hope of using a SQL injection of the form: "; SELECT name, password from USERS;" or "; DELETE FROM USERS;" or "; DROP TABLE USERS;" because the account doesn't have anything but EXEC (and certainly no DDL). You can control column visibility at the SP level and not have to deny select on an employee salary column, for example.

In other words, unless you are comfortable granting db_datareader to public (because that's effectively what you are doing when you LINQ-to-tables), then you need some sort of realistic security in your application, and SPs are the only way to go, with LINQ-to-views possibly being acceptable.

Depends entirely on what you're doing.

As a general rule a stored proc will have it's query plan cached better than a dynamically generated SQL statement. It will also be slightly easier to maintain indexes for.

However, dynamically generated SQL statements can have their query plans cached, so the difference is marginal.

Dynamically generated SQL statement can also introduce security risk - always parameterise them.

That said sprocs are a pain to maintain and update, they separate DB-logic and .Net code in a way that makes it harder for developers to piece together what a data access method is doing.

Also, to fix or update a SQL string you just change code. To fix or update a sproc you have to change the database - often a much messier option.

So I wouldn't recommend that as a 'one size fits all' best practice.

There is no right or wrong answer here. There are benefits to both which can be easily obtained through a google search. Different projects with different requirements may lead you to different solutions. It's not as black or white as you might want it to be. You might as well throw ORMs into the mix. If you prefer sql queries in your data layer as opposed to stored procs, make sure you use parametrized queries.

sql in sp- easy to maintain, sql in app- pain in the butt ot maintain.

it's so much faster and easier to hop onto a sql instance, modify an sp, test it, then deploy the sp, instead of having to modify the code in the app, test it, then deploy the app.

It depends on the data distribution in your table. Prepared query plans and stored procedures get cached, and the plan itself depends on the table statistics.

Suppose you've building a blog and that your posts table has a user_id. And that you're frequently doing stuff like:

select posts.* from posts where user_id = ? order by published desc limit 20;

Suppose indexes on posts (user_id) and posts (published desc).

Additionally suppose that you've two authors, author1 which wrote 3 posts a long time ago, and author2 who has written 10k posts since.

In this case, the query plan of the ad hoc query will be very different depending on whether you're fetching the author1 posts or the author2 posts:

• For author1, the database will decide to use the index on user_id and sort the results.
• For author2, the database will read the first 20 rows using the index on published.

If you prepare the statement, the planner will pick either of the two. Suppose the second (which I think is likely): applied to author1, this means going through the whole table by way of the index -- which is much slower than the optimal plan.

If simplicity is your goal, then an ORM would be a good practice for your simple database operations

ORMs like Entity Framework, nHibernate, LINQ to SQL, etc. will manage the code creation of the data access and repository layers and provide you with strongly typed objects representing your tables. This can lead to a cleaner, more maintainable architecture.

Save the stored procedures for your more complex queries. This is where you can take advantage of advanced SQL and cached query plans.

1. Dynamic SQL - Bad
2. Stored Procedures - Better
3. Linq-To-SQL or Linq-to-EF (or ORM tools) - Best

You do not want dynamic SQL inside your application since you do not have compile-time checking. Stored procedures will at least be checked, but it is still not part of a cohesive usnit and removes business logic to the database layer. Linq-To-EF will allow business logic to stay inside your application and allow you to have compile-time checking of syntax.