Partilhar via

Parâmetros com valor de tabela

Baixar ADO.NET

Os parâmetros com valores de tabela fornecem uma forma fácil de transmitir múltiplas linhas de dados de uma aplicação cliente para o SQL Server. Não requerem múltiplas viagens de ida e volta nem lógica especial do lado do servidor para processar os dados. Pode usar parâmetros de tabela para encapsular linhas de dados numa aplicação cliente e enviar os dados para o servidor num único comando parametrizado. As linhas de dados recebidas são armazenadas numa variável de tabela que pode depois ser operada através do Transact-SQL.

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

Observação

Não pode devolver dados num parâmetro com valores de tabela. Os parâmetros com valores de tabela são apenas de entrada; a palavra-chave OUTPUT não é suportada.

Para mais informações sobre parâmetros com valores em tabelas, consulte os seguintes recursos.

Resource Description
Usar parâmetros de Table-Valued (Mecanismo de Banco de Dados) Descreve como criar e usar parâmetros com valores de tabela.
Criação de um tipo de tabela definido pelo utilizador Descreve tipos de tabela definidos pelo utilizador que são usados para declarar parâmetros com valores de tabela.
Tipos de Tabelas Definidos pelo Usuário Descreve tipos de tabela definidos pelo utilizador que são usados para declarar parâmetros com valores de tabela.

Passar várias linhas em versões anteriores do SQL Server

Antes da introdução dos parâmetros de valores em tabelas, as opções para passar várias linhas de dados para um procedimento armazenado ou para um comando SQL parametrizado eram limitadas. Um programador pode escolher entre as seguintes opções para passar várias linhas ao servidor:

  • Use uma série de parâmetros individuais para representar os valores em múltiplas colunas e linhas de dados. A quantidade de dados que pode ser passada através deste 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 estes valores individuais numa variável de tabela ou numa tabela temporária para processamento.

  • Agrupe múltiplos valores de dados em cadeias delimitadas ou documentos XML e depois passe esses valores de texto para um procedimento ou instrução. Este método exige que o procedimento ou instrução inclua lógica para validar as estruturas de dados e desagregar os valores.

  • Crie uma série de instruções SQL individuais para modificações de dados que afetem múltiplas linhas, como aquelas criadas ao chamar o Update método de um SqlDataAdapter. As alterações podem ser submetidas ao servidor individualmente ou agrupadas em grupos. No entanto, mesmo quando submetidos em lotes que contêm múltiplas instruções, cada instrução é executada separadamente no servidor.

  • Use o bcp programa utilitário ou o SqlBulkCopy objeto para carregar muitas linhas de dados numa tabela. Embora esta técnica seja eficiente, não suporta processamento do lado do servidor a menos que os dados sejam carregados numa tabela ou variável de tabela temporária.

Criação de tipos de parâmetros com valores de tabela

Os parâmetros de tabela baseiam-se em estruturas de tabela fortemente tipadas que são definidas usando as instruções CREATE TYPE do Transact-SQL. Deve criar um tipo de tabela e definir a estrutura no SQL Server antes de poder usar parâmetros de tabela nas suas aplicações clientes. Para mais informações sobre a criação de tipos de tabelas, consulte Parâmetros com Valor de Tabela (Motor de Base de Dados).

A seguinte instrução cria um tipo de tabela chamado CategoryTableType que consiste nas colunas CategoryID e CategoryName:

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

Depois de criar um tipo de tabela, pode declarar parâmetros de tabela com base nesse tipo. O seguinte fragmento Transact-SQL demonstra como declarar um parâmetro com valores de tabela numa 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)

Modificação de dados com parâmetros de tabela (Transact-SQL)

Parâmetros com valores de tabela podem ser usados em modificações de dados baseadas em conjuntos que afetam múltiplas linhas ao executar uma única instrução. Por exemplo, pode selecionar todas as linhas de um parâmetro com valor de tabela e inseri-las numa tabela de base de dados, ou pode criar uma instrução de atualização juntando um parâmetro com valor de tabela à tabela que pretende atualizar.

A seguinte instrução Transact-SQL UPDATE demonstra como usar um parâmetro com valores de tabela ao associá-lo à tabela Categorias. Quando usa um parâmetro de tabela com um JOIN numa cláusula FROM, deve também dar-lhe um alias, como mostrado aqui, onde o parâmetro de tabela é apelidado 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 Transact-SQL demonstra como selecionar linhas de um parâmetro com valores de tabela para realizar um INSERT numa única operação baseada em conjuntos.

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

Limitações dos parâmetros com valores de tabela

Existem várias limitações aos parâmetros de valores de tabela:

  • Não podes passar parâmetros de tabela para funções CLR definidas pelo utilizador.

  • Os parâmetros de tabela só podem ser indexados para suportar restrições ÚNICAS ou CHAVE PRIMÁRIA. O SQL Server não mantém estatísticas sobre parâmetros com valores de tabela.

  • Os parâmetros com valores de tabela são de somente leitura no código Transact-SQL. Não podes atualizar os valores das colunas nas linhas de um parâmetro de tabela e não podes inserir ou eliminar linhas. Para modificar dados passados para um procedimento armazenado ou uma instrução parametrizada num parâmetro com valores de tabela, deve inserir os dados numa tabela temporária ou numa variável de tabela.

  • Não podes usar instruções ALTER TABLE para modificar o desenho dos parâmetros com valores de tabela.

Configuração de um exemplo de SqlParameter

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

No exemplo seguinte, a addedCategories variável contém um DataTable. Para ver como a variável é preenchida, consulte os exemplos na secção seguinte, Passagem de 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 pode usar qualquer objeto derivado de DbDataReader para transferir linhas de dados para um parâmetro com valores de tabela, como 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;

Passar um parâmetro com valores de tabela para um procedimento armazenado

Este exemplo demonstra como passar dados de parâmetros com valores de tabela para um procedimento armazenado. O código extrai as linhas adicionadas para um novo DataTable utilizando o método GetChanges. O código define então um SqlCommand, definindo a CommandType propriedade para StoredProcedure. O SqlParameter é povoado usando o AddWithValue método e o SqlDbType é definido como Structured. O SqlCommand é então executado usando o ExecuteNonQuery método.

// 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();
}

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

O exemplo seguinte demonstra como inserir dados na tabela dbo.Categories, utilizando uma instrução INSERT com uma subconsulta SELECT que tem um parâmetro tabela-valorizado como origem de dados. Ao passar um parâmetro com valor de tabela para uma instrução SQL parametrizada, deve especificar um nome de tipo para o parâmetro com valor de tabela usando a nova TypeName propriedade de um SqlParameter. Isto TypeName deve corresponder ao nome de um tipo compatível previamente criado no servidor. O código neste exemplo usa esta TypeName propriedade para referenciar a estrutura de tipos definida no dbo. CategoriaTipoTabela.

Observação

Se fornecer um valor para uma coluna identidade num parâmetro com valores de tabela, deve 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();
}

Transmissão de linhas com um DataReader

Também pode usar qualquer objeto derivado de DbDataReader para transferir linhas de dados para um parâmetro com valor de tabela. O seguinte fragmento de código demonstra a obtenção de dados de uma base de dados Oracle usando um OracleCommand e um OracleDataReader. O código então configura um SqlCommand para invocar um procedimento armazenado com um único parâmetro de entrada. A SqlDbType propriedade do SqlParameter é definida como Structured. AddWithValue passa o conjunto de resultados 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óximos passos

  • Last updated on