Sei que este tema é um pouco controverso mas... aqui vai :p

Existem dois modelos de design diferentes para o acesso a uma base de dados:

1 - Guardar todas as instruções SQL dentro de stored procedures (usando apenas o objecto SqlCommand para executar os stored procedures pelo nome);

2 - Guardar todas as instruções SQL como texto (usando os objectos SqlCommand).

Se forem daqueles (como eu)  que lêem muito, poderão ver muitas referências em que se diz que os stored procedures têm melhor performance porque são "pre-compilados" e residem na memória "cache in-memory" pelo SqlServer. Entretanto muitos artigos e livros esquecem-se de referir que isto também acontece com queries em SQL text (algo que, já alguns anos, foi adicionado ao SQL Server) desde que usem queries parametrizados (deverão fazer isto em todos os casos pois em termos de segurança reduz os ataques "SQL-injection")  !

A performance entre os Stored Procedures e os SQL text queries é similar em muitos casos.  No entanto não se esqueçam que o nome do Stored Procedure é bastante mais pequeno comparado com um query SQL text, então será sempre mais fácil para o "motor" do SQL Server encontra-lo no seu plano de execução "cache".... mas mesmo assim em muitos casos isto também não é muito significativo.

Existem muitos prós e contras nos dois casos.  Os stored procedures permitem um controlo maior em termos de segurança pois podemos dar permissões a um user (SQL user account) para executar apenas certos stored procedures, não dando um controlo total sobre as tabelas existentes. Outro argumento a favor é o facto de que usualmente os stored procedures têm um "batch" de instruções e não apenas uma instrução. Se tiverem que executar  usando SQL text queries então teremos que executar múltiplos comandos separados que faz fazer com que aumente o tráfico na rede. Quando executamos um Stored Procedure o tráfego é mínimo pois apenas enviamos um nome e alguns parâmetros :p

Apesar disto tudo (e apenas fiz uma simples abordagem) os stored procedures nem sempre são a melhor solução! A grande vantagem dos SQL text queries é que são mais flexíveis! Por vezes queremos implementar sistemas de filtros e busca no UI em que o utilizador pode "parcialmente" preencher alguns dados e escolher os campos. O query é diferente dependendo dos campos escolhidos e preenchidos pelo utilizador e até a sua ordenação. Se tivessem que fazer isto dentro de um Stored Procedure teriam de escrever muitas instruções IF...ELSE o que se poderia tornar elegível. Outro grande problema é se queremos ter a possibilidade de suportar diferentes tipos de base de dados. Neste caso temos de "converter" todos os stored procedures. Isto é mesmo necessário porque os vários RDBMSs têm uma linguagem diferente apesar do seu "SQL dialecto" ser muito próximo do standard das especificações do ANSI SQL.

Então, que solução utilizas ?

Por norma prefiro usar o "Provider Model Design pattern" e a criação de diferentes RDBMS - DAL classes. Usar o provider model design nem sempre é a solução mais fácil de implementar se existirem muitos RDBMS a suportar mas vantagens são superiores ás desvantagens.

O resultado destes argumentos é que por norma utilizo os stored procedures para trabalhar com os dados da base de dados, excepto quando o query  é demasiado dinâmico e complexo para usar nos stored procedures. Nessa situação uso sempre SQL text queries usando SqlCommand e SqlParameters, é claro :P