Suponha que você tenha uma lista na sua aplicação, onde cada um dos elementos desta lista representa  um Id na base de dados. Você precisa submeter esta lista para uma Stored Procedure para atualizar os respectivos registros. Aqui entra o problema: como você irá fazer isso?

Provavelmente você já deve ter passado por esse problema, e as possíveis soluções para isso seria percorrer a lista, elemento à elemento, configurar o(s) parâmetro(s) e, finalmente, invocar a Stored Procedure. Outra possibilidade seria formatar todos os Ids em uma única String, separados por uma vírgula, e na Stored Procedure criar um parâmetro do tipo Varchar(X) para receber tais Ids. O problema da primeira técnica é a quantidade de comandos que você executa, ou seja, se tiver 200 Ids, serão duzentas execuções da Stored Procedure; já a segunda nos obrigará a criar uma query dinâmica, o que poderá tornar o nosso código vulnerável à SQL Injection e, além disso, se a concatenação dos Ids na String resultar em uma String maior do que o comprimento do parâmetro, a query não executará como o esperado.

Uma forma elegante de resolver isso no SQL Server 2008, é utilizando um novo tipo de dado, chamado de Table-Valued Parameter. Com este tipo especial, é possível determinarmos uma estrutura de dados e, definí-lo como parâmetro de entrada em uma Stored Procedure. O código abaixo ilustra a criação do Table-Valued Parameter e, logo em seguida, a Stored Procedure que utiliza o mesmo:

CREATE TYPE ColecaoDeIds AS TABLE
(
    Id INT NOT NULL
)
GO

CREATE PROCEDURE AtualizarUsuarios
    @ColecaoDeIds As ColecaoDeIds READONLY,
    @Ativo As Bit
AS
BEGIN
    UPDATE Usuarios SET
        Ativo = @Ativo
    WHERE Id IN
    (
        SELECT Id FROM @ColecaoDeIds
    )
END
GO

Note que o tipo “ColecaoDeIds” contém apenas uma coluna chamada “Id”, mas você poderia adicionar quantas forem necessárias. Um detalhe importante é que, quando utilizamos um Table-Valued Parameter como parâmetro em uma Stored Procedure, ele deverá obrigatoriamente ser definido como ReadOnly; se precisar inserir, atualizar ou excluir registros dele, então você deve inserir os dados em uma tabela temporária e, depois disso, fazer a manipulação desejada. O interessante é que o Table-Valued Parameter nada mais é que uma espécie de “tabela”, permitindo efetuar queries em cima dela.

Já no código .NET, poucas mudanças são necessárias. Basicamente o que você precisa fazer é, criar uma instância da classe SqlParameter e definir a propriedade SqlDbType para SqlDbType.Structured. Há uma limitação na definição do tipo de objeto que você pode passar como valor para este parâmetro: deverá utilizar DataTable, DbDataReader ou IEnumerable<SqlDataRecord>. Independentemente de qual destes repositórios você irá utilizar, obrigatoriamente eles devem seguir a mesma estrutura do Table-Valued Parameter que, no nosso caso, possui apenas uma coluna chamada “Id” do tipo INT. O código abaixo mostra como podemos proceder para a configuração deste parâmetro:

using (SqlCommand cmd = new SqlCommand(“AtualizarUsuarios”, conn))
{
    cmd.Parameters.Add(
        new SqlParameter(“@ColecaoDeIds”, tuaDataTableAqui) { SqlDbType = SqlDbType.Structured });
}

Se você já utiliza DataTables para armazenamento e/ou manipulação dos dados, então tudo o que precisa fazer é criar uma nova DataTable com a mesma estrutura do Table-Valued Parameter e populá-la. Mas e se estivermos utilizando algum array ou coleção? Para facilitar,, eu criei um extension method chamado ToDataTable<T> que, como o próprio nome diz, transforma a coleção/array em uma DataTable. Como a estrutura da DataTable precisa seguir a mesma estrutura do parâmetro, eu criei um atributo chamado GenerateColumnAttribute, para que você decore as propriedades de sua entidade, para que elas sejam criadas como DataColumns no DataTable. Com essa estrutura, poderíamos fazer algo como:

List<Usuario> list = CarregarUsuarios();

using (SqlCommand cmd = new SqlCommand(“AtualizarUsuarios”, conn))
{
    cmd.Parameters.Add(
        new SqlParameter(
            “@ColecaoDeIds”,
            list.ToDataTable(typeof(GenerateColumnAttribute))) { SqlDbType = SqlDbType.Structured });

    Console.WriteLine(cmd.ExecuteNonQuery());
}

O método ExecuteNonQuery retornará a quantidade de registros afetados pela query. O código .NET de exemplo pode ser encontrado aqui.