Parâmetros com valor de tabela

  • Artigo

Baixar ADO.NET

Os parâmetros com valor de tabela oferecem uma forma fácil de realizar marshaling em várias linhas de dados de um aplicativo cliente para o SQL Server. Eles não exigem várias viagens de ida e volta nem lógica especial do lado do servidor para processar os dados. Você pode usar parâmetros com valor de tabela para encapsular linhas de dados em um aplicativo cliente e enviar os dados para o servidor em um único comando parametrizado. As linhas de dados de entrada são armazenadas em uma variável de tabela, as quais você poderá operar usando o Transact-SQL.

Os valores de coluna em parâmetros com valor de tabela podem ser acessados usando instruções SELECT padrão do Transact-SQL. Os parâmetros com valor de tabela são fortemente tipados e a estrutura deles é validada automaticamente. O tamanho dos parâmetros com valor de tabela é limitado somente pela memória do servidor.

Observação

Não é possível retornar dados em um parâmetro com valor de tabela. Os parâmetros com valor de tabela são somente entrada; não há suporte para a palavra-chave OUTPUT.

Para obter mais informações sobre os parâmetros com valor de tabela, confira os recursos a seguir.

Recurso Descrição
Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados) Descreve como criar e usar parâmetros com valor de tabela.
Criando um tipo de tabela definido pelo usuário Descreve os tipos de tabela definidos pelo usuário usados para declarar parâmetros com valor de tabela.
Tipos de tabela definidos pelo usuário Descreve os tipos de tabela definidos pelo usuário usados para declarar parâmetros com valor de tabela.

Como passar várias linhas nas versões anteriores do SQL Server

Antes de os parâmetros de valores de tabela terem sido introduzidos, as opções para passar várias linhas de dados para um procedimento armazenado ou um comando SQL parametrizado eram limitadas. Um desenvolvedor pode escolher entre as seguintes opções para passar várias linhas para o servidor:

  • Usar uma série de parâmetros individuais para representar os valores em várias colunas e linhas de dados. A quantidade de dados que pode ser passada usando esse método é limitada pelo número de parâmetros permitidos. Os procedimentos do SQL Server podem ter, no máximo, 2100 parâmetros. A lógica do lado do servidor é necessária para montar esses valores individuais em uma variável de tabela ou uma tabela temporária para processamento.

  • Agrupe múltiplos valores de dados em cadeias de caracteres delimitadas ou em documentos XML e, em seguida, passe esses valores de texto para um procedimento ou uma instrução. Esse método requer que o procedimento ou a instrução inclua lógica para validar as estruturas de dados e desagrupar os valores.

  • Criar uma série de instruções SQL individuais para modificações de dados que afetam várias linhas, como aquelas criadas ao chamar o método Update de um SqlDataAdapter. As alterações podem ser enviadas ao servidor individualmente ou em lotes em grupos. No entanto, mesmo quando enviados em lotes que contêm várias instruções, cada instrução é executada separadamente no servidor.

  • Usar o programa utilitário bcp ou o objeto SqlBulkCopy para carregar várias linhas de dados em uma tabela. Embora essa técnica seja eficiente, ela não dá suporte ao processamento do lado do servidor, a menos que os dados sejam carregados em uma tabela temporária ou variável de tabela.

Como criar tipos de parâmetro com valor de tabela

Os parâmetros com valor de tabela são baseados em estruturas de tabela fortemente tipadas definidas usando instruções CREATE TYPE do Transact-SQL. Você precisa criar um tipo de tabela e definir a estrutura no SQL Server antes de usar parâmetros com valores de tabela em aplicativos cliente. Para obter mais informações sobre como criar tipos de tabela, confira Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados).

A seguinte instrução cria um tipo de tabela chamado CategoryTableType que é composto pelas colunas CategoryID e CategoryName:

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

Depois de criar um tipo de tabela é possível declarar os parâmetros com valor de tabela baseados nesse tipo. O fragmento do Transact-SQL a seguir demonstra como declarar um parâmetro com valor de tabela em uma definição de procedimento armazenado. A palavra-chave READONLY é necessária para declarar um parâmetro com valor de tabela.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Como modificar dados com parâmetros com valor de tabela (Transact-SQL)

Os parâmetros com valor de tabela podem ser usados em modificações de dados baseadas em conjunto que afetam várias linhas executando uma instrução. Por exemplo, você pode selecionar todas as linhas em um parâmetro com valor de tabela e inseri-las em uma tabela de banco de dados ou pode criar uma declaração de atualização unindo um parâmetro com valor de tabela à tabela que você deseja atualizar.

A instrução UPDATE do Transact-SQL a seguir demonstra como usar um parâmetro com valor de tabela unindo-o à tabela Categorias. Ao usar um parâmetro com valor de tabela com JOIN em uma cláusula FROM, você também deverá usar o alias, conforme mostrado aqui, em que o parâmetro com valor de tabela tem um alias como "ec":

UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;

Este exemplo do Transact-SQL demonstra como selecionar linhas de um parâmetro com valor de tabela para executar um INSERT em uma operação baseada em conjunto.

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Limitações de parâmetros com valor de tabela

Há várias limitações para parâmetros com valor de tabela:

  • Você não pode passar parâmetros com valor de tabela para Funções CLR definidas pelo usuário.

  • Os parâmetros com valor de tabela podem ser indexados somente para dar suporte às restrições UNIQUE ou PRIMARY KEY. O SQL Server não mantém estatísticas sobre parâmetros com valor de tabela.

  • Os parâmetros com valor de tabela são somente leitura no código Transact-SQL. Não é possível atualizar os valores de coluna nas linhas de um parâmetro com valor de tabela nem inserir ou excluir linhas. Para modificar os dados passados para um procedimento armazenado ou para uma instrução parametrizada no parâmetro com valor de tabela, você precisa inserir os dados em uma tabela temporária ou em uma variável de tabela.

  • Não é possível usar instruções ALTER TABLE para modificar o design dos parâmetros com valor de tabela.

Como configurar um exemplo de SqlParameter

Microsoft.Data.SqlClient é compatível com o preenchimento de parâmetros com valor de tabela dos objetos DataTable, DbDataReader ou IEnumerable<T> \ SqlDataRecord. Especifique um nome de tipo para o parâmetro com valor de tabela usando a propriedade TypeName de um SqlParameter. O TypeName deve corresponder ao nome de um tipo compatível criado anteriormente no servidor. O fragmento de código a seguir demonstra como configurar o SqlParameter para inserir dados.

No exemplo a seguir, o nome da variável addedCategories contém um DataTable. Para ver como a variável é preenchida, confira os exemplos na próxima seção, Como passar um parâmetro com valor de tabela para um procedimento armazenado.

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

Também é possível usar qualquer objeto derivado de DbDataReader para transmitir linhas de dados a um parâmetro com valor de tabela, conforme mostrado neste fragmento:

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;

Passando um parâmetro com valor de tabela para um procedimento armazenado

Este exemplo demonstra como passar dados de parâmetro com valor de tabela para um procedimento armazenado. O código extrai linhas adicionadas em um novo DataTable usando o método GetChanges. Em seguida, o código define um SqlCommand, configurando a propriedade CommandType como StoredProcedure. O SqlParameter é preenchido usando o método AddWithValue e o SqlDbType é definido como Structured. Em seguida, o SqlCommand é executado usando o método ExecuteNonQuery.

// Assumes connection is an open SqlConnection object.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Configure the SqlCommand and SqlParameter.
    SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Como passar um parâmetro com valor de tabela para uma instrução SQL parametrizada

O exemplo a seguir demonstra como inserir dados na tabela dbo.Categories usando uma instrução INSERT com uma consulta aninhada SELECT que tem um parâmetro com valor de tabela como fonte de dados. Ao passar um parâmetro com valor de tabela para uma instrução SQL parametrizada, será necessário especificar um nome de tipo para ele usando a nova propriedade TypeName de um SqlParameter. Esse TypeName deve corresponder ao nome de um tipo compatível criado anteriormente no servidor. O código nesse exemplo usa a propriedade TypeName para fazer referência à estrutura de tipo definida em dbo.CategoryTableType.

Observação

Caso forneça um valor para uma coluna de identidade em um parâmetro com valor de tabela, você deverá emitir a instrução SET IDENTITY_INSERT para a sessão.

// Assumes connection is an open SqlConnection.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Define the INSERT-SELECT statement.
    string sqlInsert =
        "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
        + " SELECT nc.CategoryID, nc.CategoryName"
        + " FROM @tvpNewCategories AS nc;"

    // Configure the command and parameter.
    SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.CategoryTableType";

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Como transmitir linhas com um DataReader

Também é possível usar qualquer objeto derivado de DbDataReader para transmitir linhas de dados a um parâmetro com valor de tabela. O fragmento de código a seguir demonstra como recuperar dados de um Oracle Database usando um OracleCommand e um OracleDataReader. Em seguida, o código configura um SqlCommand para invocar um procedimento armazenado com um parâmetro de entrada. A propriedade SqlDbType do SqlParameter é definida como Structured. O AddWithValue passa o conjunto de resultados do OracleDataReader para o procedimento armazenado como um parâmetro com valor de tabela.

// Assumes connection is an open SqlConnection.
// Retrieve data from Oracle.
OracleCommand selectCommand = new OracleCommand(
    "Select CategoryID, CategoryName FROM Categories;",
    oracleConnection);
OracleDataReader oracleReader = selectCommand.ExecuteReader(
    CommandBehavior.CloseConnection);

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam =
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", oracleReader);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();

Próximas etapas