Início / dba / Perguntas / 142397
Accepted
Rainbolt
Asked: 2016-06-28 11:33:48 +0800 CST

Por que os padrões de coluna em meu tipo de tabela definida pelo usuário (UDTT) não estão sendo respeitados?

  • 772

Eu crio um tipo de tabela em SQL:

CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
    [Name] VARCHAR(MAX) DEFAULT '',
    [Index] VARCHAR(MAX) DEFAULT ''
)

Eu construo um DataTablee o preencho com um DataRow. Eu forneço essa tabela como um parâmetro para um procedimento armazenado:

// Set up connection and command variables (code omitted)
// ...

// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");

// Add one row that is missing an Index    
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);

// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType",
    Value = table
};

command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();

Eu esperava que dentro do procedimento armazenado, o valor do índice fosse padronizado para ''(vazio VARCHAR). Em vez disso, observei que o valor era nulo. Sei disso porque tentei mesclar o tipo de tabela em uma tabela real e a coluna de índice em minha tabela não permite nulos.

Por que o SQL Server não honra o padrão que coloquei no meu tipo de tabela? Existe uma maneira de forçar o SQL Server a honrá-lo?

Acho que posso contornar o problema usando a propriedade DataTable.DefaultValue , mas essa solução tem a desvantagem de precisar declarar os mesmos padrões em dois lugares diferentes. Eu quero evitar código redundante, se possível.

sql-server c# default-value

1 respostas

  1. Best Answer

    Por que o SQL Server não honra o padrão que coloquei no meu tipo de tabela?

    Na verdade, o SQL Server realmente honra[u]r esse padrão, como mostra o teste a seguir (usando o UDTT fornecido na pergunta):

    DECLARE @Test dbo.MyObject_TableType;
INSERT INTO @Test (Name) VALUES ('ObjectOne');
SELECT * FROM @Test;

    Retorna:

    Name        Index
ObjectOne              <--- empty string, not NULL

    A razão pela qual você está obtendo NULLem vez de uma string vazia é porque é isso que você está pedindo ao código do aplicativo para fornecer. Você até (sem saber, é claro ;-) identifica a causa raiz do problema ao afirmar (ênfase adicionada):

    Eu construo um DataTablee o preencho com um DataRow. Eu forneço essa tabela como um parâmetro para um procedimento armazenado:

    A DataTableé uma representação de uma Tabela real na memória, não de uma ou mais INSERTinstruções. Portanto, quando você cria essa única linha, a Indexcoluna não pode ser indefinida: é um NULLou algum valor. E quando você passa isso DataTablepara o SQL Server como um TVP, está passando o equivalente a uma tabela.

    Sim, você provavelmente poderia contornar isso especificando um valor padrão para isso DataColumnno arquivo DataTable. Mas concordo que fazer isso não é a melhor abordagem.

    Minha preferência é nunca usar DataTables, a menos que você já tenha um, independentemente de usar um TVP ou não. Não consigo pensar em um motivo para criar um simplesmente com o objetivo de passar dados para um TVP. Além desse problema específico, ele também duplica qualquer coleção que você esteja colocando nele simplesmente para ser um transporte temporário para o TVP (o que desperdiça memória e o tempo que leva para copiar esses dados, mesmo que não seja muito de nenhum desses) .

    Em vez disso, crie um método que pegue qualquer coleção que você já tenha e simplesmente transmita-a, um item/linha por vez, para o TVP. Você faz isso retornando IEnumerable<SqlDataRecord>desse método e, em seguida, especifica esse método como a Valuepropriedade da SqlParameterrepresentação do TVP.

    método para transmitir a coleção para o TVP:

    private static IEnumerable<SqlDataRecord> SendRows(List<DbObject> DbObjects)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[]
   {
      new SqlMetaData("Name", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 1),
      new SqlMetaData("Index", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 2)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   int _NumRows = DbObjects.Count;

   // read a row, send a row.
   // "for" should be faster than "foreach", right?
   for (int _Index = 0; _Index < _NumRows; _Index++)
   {
      _DataRecord.SetString(0, DbObjects[_Index].Name);
      _DataRecord.SetString(1, DbObjects[_Index].Index);
      yield return _DataRecord;
   }
}

    usando o método:

    private struct DbObject
{
    public string Name;
    public string Index;
}

List<DbObject> _DbObjects = new List<DbObject>();

// Add one row that is missing an Index    
_DbObjects.Add(new DbObject() { Name = "ObjectOne" });

// Make a parameter for the table
SqlParameter _ParamMyObjects = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType", // not required for CommandType.StoredProcedure
    Value = SendRows(_DbObjects)
};

command.Parameters.Add(_ParamMyObjects);
command.ExecuteNonQuery();

    O truque para obter o padrão definido no UDTT para preencher a Indexcoluna com uma string vazia é a sobrecarga específica do construtor SqlMetaData que usei acima. Tem um parâmetro booleano para useServerDefault. Passar truefaz com que a coluna não seja passada para a INSERTinstrução se não for "definida" no arquivo SqlDataRecord. Ou, pelo menos, é assim que sempre consegui IDENTITYcolunas em UDTTs para funcionar (nunca tentei em um DEFAULT, mas deve ser o mesmo comportamento).

    PS Por que você está usando VARCHAR(MAX)como tipo de dados essas duas colunas no UDTT? Se uma ou ambas as colunas precisarem conter identificadores do SQL Server (ou seja, nomes de tabelas, exibições, procedimentos armazenados, índices, etc.), todos sysnameeles são um alias para NVARCHAR(128). Portanto, você realmente precisa pelo menos estar usando NVARCHARpara evitar possíveis perdas de dados / bugs difíceis de rastrear.

    • 5

relate perguntas