Início / coding / Perguntas / 78699617
Accepted
Joshua
Asked: 2024-07-03 07:57:12 +0800 CST

Importe várias linhas para uma tabela temporária em uma viagem de ida e volta via SqlDataRecord

  • 772

Este problema está apenas começando a aparecer no criador de perfil. Temos algo parecido com isto:

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids VALUES (@0);";
    cmd.Parameters.Add(cmd.CreateParameter() { ParameterName = "@0", DbType = GetDbType() });

    cmd.Prepare();

    foreach (var value in values)
    {
        ((IDbDataParameter)cmd.Parameters[0]).Value = value;
        cmd.ExecuteNonQuery();
    }
}

Estamos estudando a possibilidade de acelerar isso diminuindo o número de viagens de ida e volta. Usar DataTable aqui seria uma monstruosidade, mas existe esse tipo SqlDataRecordque parece se encaixar, então deveríamos ser capazes de fazer algo assim:

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param  = cmd.CreateParameter();
    param.ParameterName = "@0";
    // FIXME more properties
    cmd.Parameters.Add(param);
    param.Value = values.Cast<T>().Select((x) => {
                             // FIXME Create new SqlDataRecord
                         }.ToList();

    cmd.ExecuteNonQuery();
}

A principal dificuldade aqui que o diferencia das outras amostras é a T; esse código é dinâmico e recebe um grande número de tipos possíveis; portanto CREATE TYPEnão funcionará. Não há como criar um tipo para cada comprimento VARCHAR possível.

Como preencho os comentários FIXME acima?

Pergunta relacionada, sem boas respostas: Como criaria uma tabela temporária no SQL Server quando tenho uma grande lista de IDs

Nos comentários há um pedido para uma visão geral. Realmente não há uma maneira muito boa de obter mais informações diminuindo o zoom aqui. Há exatamente uma cópia da rotina que define argumentos para todas as chamadas SQL (leia-se: procedimentos armazenados) na base de código, e é isso.

O fator de escala para o número de linhas é de cauda grossa; o número médio de linhas é inferior a 10, mas o tamanho máximo é muito grande e chamadas com mais de mil acontecem regularmente. Assim carregamos uma tabela temporária; e na conversão para fazer algo diferente de um foreachloop, ainda carregamos uma tabela temporária.

c#

2 respostas

  1. Um exemplo totalmente funcional.

    using var connection = new SqlConnection(@"...");
connection.Open();
int[] values = [1, 2, 3, 4, 5];

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "CREATE TYPE dbo.Ids AS TABLE (Id INT );";
    cmd.ExecuteNonQuery();    
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "CREATE TABLE #Ids (Id INT);";
    cmd.ExecuteNonQuery();    
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param = cmd.CreateParameter();
    param.ParameterName = "@0";
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.Ids";

    cmd.Parameters.Add(param);

    var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));

    param.Value = values.Cast<int>()
        .Select(x =>
        {
            //var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));
            record.SetInt32(0, x);
            return record;
        })
        //.ToList()
        ;

    var affectedRows = cmd.ExecuteNonQuery();
    Console.WriteLine($"Rows inserted: {affectedRows}");
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "SELECT COUNT(*) FROM #Ids;";
    var count = cmd.ExecuteScalar();
    Console.WriteLine($"Count: {count}");
}
    • 1
  2. Best Answer

    Parece que um TVP pode não ser a opção certa para você.

    Mas você pode usar uma tabela temporária com SqlBulkCopy, que será muito mais rápida que inserções individuais.

    Você pode passar SqlBulkCopya DataTableou a DbDataReader. O primeiro é simples de criar, mas pode exigir muita memória. Você pode criar um leitor simplesmente usando a biblioteca FastMember gratuita .

    using var conn = new SqlConnection(GetConnectionStringFromSomewhere());
await conn.OpenAsync();
var createTable = $"CREATE TABLE #ids (Id {columnTypeHere} PRIMARY KEY);";
using (var cmd = new SqlCommand(createTable, conn)) 
{
    // do NOT pass any parameters for this command, otherwise it gets wrapped as sp_executesql and the table will be dropped immediately
    // cmd.Prepare() is unnecessary and counter-productive

    await cmd.ExecuteNonQueryAsync();
}

using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock))
using (var reader = ObjectReader.Create(data)) 
{
    bulk.ColumnMappings.Add("Id", "Id");
    bulk.DestinationTableName = "#ids";
    await bulk.WriteToServerAsync(reader);
}

using (var cmd = new SqlCommand("someStoredProcedure", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(  // etc
    await cmd.ExecuteNonQueryAsync();
}
    • 1

relate perguntas