Bob Beaucheamin, who works at SQLSkills, has an excellent new article in MSDN Magazine that explains how different methods of writing data access code in ADO.NET can have a huge effect on performance and security.

Here is the executive summary of the article:

1. Concatenating an ad-hoc SQL statement and submitting it without specifying the CommandType is extremely bad from a performance and security standpoint.

2. Parameterizing an ad-hoc SQL statement and using Parameters.AddWithValue is not as evil, but still bad. Parameters.AddWithValue does implicit type conversion of the parameter.

3. Using a parameterized stored procedure is best. It is more secure, easier to maintain, and will not bloat the procedure cache with single-use plans.

4. It usually makes sense to have multiple stored procedures instead of large, generic stored procedures.