主页 / dba / 问题 / 142397
Accepted
Rainbolt
Asked: 2016-06-28 11:33:48 +0800 CST

为什么我的用户定义的表类型 (UDTT) 上的列默认值没有得到遵守？

  • 772

我在 SQL 中创建了一个表类型：

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

我建立一个DataTable并用一个填充它DataRow。我将该表作为参数提供给存储过程：

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

我预计在存储过程内部，索引值将默认为''（空 VARCHAR）。相反，我观察到该值为空。我知道这一点是因为我试图将表类型合并到一个实际的表中，并且我表上的索引列不允许空值。

为什么 SQL Server 不遵守我放置在表类型上的默认值？有没有办法强制 SQL Server 兑现它？

我想我可以通过使用DataTable.DefaultValue属性来解决这个问题，但是该解决方案的缺点是我需要在两个不同的地方声明相同的默认值。如果可能的话，我想避免冗余代码。

sql-server c# default-value

1 个回答

  1. Best Answer

    为什么 SQL Server 不遵守我放置在表类型上的默认值？

    实际上，SQL Server 确实尊重 [u]r 的默认设置，因为以下测试（使用问题中提供的 UDTT）显示：

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

    回报：

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

    您得到NULL而不是空字符串的原因是因为这是您要求应用程序代码提供的内容。您甚至（当然是在不知不觉中；-）在陈述（强调添加）时确定了问题的根本原因：

    我建立一个DataTable并用一个填充它DataRow。我将该表作为参数提供给存储过程：

    ADataTable是内存中实际表的表示，而不是一个或多个INSERT语句的表示。因此，当您创建该单行时，该Index列不能未定义：它是一个NULL值或某个值。当你将它DataTable作为 TVP 传递给 SQL Server 时，你传递的是一个表的等价物。

    是的，您可以通过DataColumn在DataTable. 但我同意这样做不是最好的方法。

    我的偏好是永远不要使用DataTables ，除非你碰巧有一个，无论是否使用 TVP。我想不出任何理由仅仅为了将数据传递到 TVP 中而创建一个。除了这个特殊问题之外，它还会复制您放入其中的任何集合，只是为了作为 TVP 的临时传输（这会浪费内存和复制该数据所需的时间，即使它不是其中任何一个） .

    相反，创建一个方法，该方法将获取您已有的任何集合，并简单地将其流式传输，一次一个项目/行，到 TVP。为此，您可以IEnumerable<SqlDataRecord>从此方法返回，然后将此方法指定为表示 TVP的Value属性。SqlParameter

    将集合流式传输到 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;
   }
}

    使用方法：

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

    获取在 UDTT 上定义的 Default 以Index使用空字符串填充列的技巧是我在上面使用的SqlMetaData 构造函数的特定重载。它有一个布尔参数useServerDefault。如果true未在. 或者，至少这就是我一直让UDTT 中的列工作的方式（从未在实际的 .INSERTSqlDataRecordIDENTITYDEFAULT

    PS你为什么VARCHAR(MAX)在UDTT中使用这两列的数据类型？如果这些列中的一个或两个需要包含 SQL Server 标识符（即表、视图、存储过程、索引等的名称），那么这些sysname都是NVARCHAR(128). 所以你真的需要至少使用NVARCHAR来避免潜在的数据丢失/难以追踪的错误。

    • 5

相关问题