修改目标表字段长度后仍出现数据截断问题求助

问题背景 #

• 数据来源：含varchar(150)字段的存储过程
• 目标表：原字段为varchar(50)，已扩容至varchar(150)
• 异常现象：
  1. 通过CreateAgeingReportTvp生成DataTable作为Table-Valued Parameter(TVP)插入时，仍触发数据截断报错
  2. 手动设置DataTable字段最大长度后，报错消失但数据插入失效
  3. 在SQL Server中手动插入相同数据可正常执行

相关代码 #

private DataTable CreateAgeingReportTvp(List<AgeingReportItem> reportItems, Guid reportId)
{
    DataTable ageingTvp = new DataTable();
    ageingTvp.Columns.Add("Id", typeof(Guid));
    ageingTvp.Columns.Add("ReportId", typeof(Guid));
    ageingTvp.Columns.Add("PrincipalId", typeof(string));
    ageingTvp.Columns.Add("PrincipalName", typeof(string));
    ageingTvp.Columns.Add("Department", typeof(string));
    ageingTvp.Columns.Add("BusinessArea", typeof(string));
    ageingTvp.Columns.Add("AmtLessThan60", typeof(decimal));
    ageingTvp.Columns.Add("Amt61To90", typeof(decimal));
    ageingTvp.Columns.Add("Amt91To180", typeof(decimal));
    ageingTvp.Columns.Add("Amt181To365", typeof(decimal));
    ageingTvp.Columns.Add("AmtGreaterThan365", typeof(decimal));
    ageingTvp.Columns.Add("TotalAR", typeof(decimal));
    ageingTvp.Columns.Add("AdvanceReceived", typeof(decimal));
    ageingTvp.Columns.Add("Provision", typeof(decimal));
    ageingTvp.Columns.Add("NetReceivable", typeof(decimal));
    ageingTvp.Columns.Add("PrevMonthBalance", typeof(decimal));
    ageingTvp.Columns.Add("Remarks", typeof(string));
    foreach (var item in reportItems)
    {
        DataRow row = ageingTvp.NewRow();
        row["Id"] = item.Id;
        row["ReportId"] = item.ReportId = reportId;
        row["PrincipalId"] = item.PrincipalId;
        row["PrincipalName"] = item.PrincipalName.Trim();
        row["Department"] = item.Department;
        row["BusinessArea"] = item.BusinessArea;
        row["AmtLessThan60"] = item.AmtLessThan60 ?? 0;
        row["Amt61To90"] = item.Amt61To90;
        row["Amt91To180"] = item.Amt91To180;
        row["Amt181To365"] = item.Amt181To365;
        row["AmtGreaterThan365"] = item.AmtGreaterThan365;
        row["TotalAR"] = item.TotalAR;
        row["AdvanceReceived"] = item.AdvanceReceived ?? 0;
        row["Provision"] = item.Provision;
        row["NetReceivable"] = item.NetReceivable;
        row["PrevMonthBalance"] = item.PrevMonthBalance;
        row["Remarks"] = item.Remarks;
    }

    return ageingTvp;
}

using (TransactionScope tran = new TransactionScope())
{
    var ageingReportId = SaveAgeingReport(columnAggregates, arAggregates, filter);

    using (DataTable myTvpTable = CreateAgeingReportTvp(reportItems, ageingReportId))
    {
        SqlParameter parameter = new SqlParameter
        {
            ParameterName = "@tvpValues",
            Value = myTvpTable,
            SqlDbType = SqlDbType.Structured,
            TypeName = "AgeingReportTvp"
        };
        sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItem SELECT * FROM @tvpValues;", new List<SqlParameter> { parameter });
    }
    var jobList = reportItems.SelectMany(x => x.JobList).ToList();

    using (DataTable detailsTvpTable = CreateAgeingReportDetailsTvp(jobList))
    {
        SqlParameter detailsParameter = new SqlParameter
        {
            ParameterName = "@detailsTvpValues",
            Value = detailsTvpTable,
            SqlDbType = SqlDbType.Structured,
            TypeName = "AgeingReportDetailsTvp"
        };
        sqlHelper.ExecuteNonQuery("INSERT INTO AgeingReportItemDetails SELECT * FROM @detailsTvpValues;", new List<SqlParameter> { detailsParameter });
    }

    tran.Complete();
}

报错截图 #

问题根源 #

使用TVP时，SQL Server会优先以DataTable定义的列属性进行数据校验，而非目标表的字段属性。原代码中创建字符串列时未指定MaxLength，默认会被设为varchar(50)，导致即使目标表字段已扩容，TVP传递的数据仍被截断。

另外，手动设置DataTable长度后插入失效，大概率是SQL Server端的TVP自定义表类型未同步更新，或者事务未正常提交。

修复方案 #

1. 修正DataTable字符串列的长度限制 #

修改CreateAgeingReportTvp方法，为所有字符串列设置匹配目标表的最大长度（150）：

private DataTable CreateAgeingReportTvp(List<AgeingReportItem> reportItems, Guid reportId)
{
    DataTable ageingTvp = new DataTable();
    ageingTvp.Columns.Add("Id", typeof(Guid));
    ageingTvp.Columns.Add("ReportId", typeof(Guid));
    // 为字符串列指定MaxLength
    var principalIdCol = ageingTvp.Columns.Add("PrincipalId", typeof(string));
    principalIdCol.MaxLength = 150;
    var principalNameCol = ageingTvp.Columns.Add("PrincipalName", typeof(string));
    principalNameCol.MaxLength = 150;
    var departmentCol = ageingTvp.Columns.Add("Department", typeof(string));
    departmentCol.MaxLength = 150;
    var businessAreaCol = ageingTvp.Columns.Add("BusinessArea", typeof(string));
    businessAreaCol.MaxLength = 150;
    ageingTvp.Columns.Add("AmtLessThan60", typeof(decimal));
    ageingTvp.Columns.Add("Amt61To90", typeof(decimal));
    ageingTvp.Columns.Add("Amt91To180", typeof(decimal));
    ageingTvp.Columns.Add("Amt181To365", typeof(decimal));
    ageingTvp.Columns.Add("AmtGreaterThan365", typeof(decimal));
    ageingTvp.Columns.Add("TotalAR", typeof(decimal));
    ageingTvp.Columns.Add("AdvanceReceived", typeof(decimal));
    ageingTvp.Columns.Add("Provision", typeof(decimal));
    ageingTvp.Columns.Add("NetReceivable", typeof(decimal));
    ageingTvp.Columns.Add("PrevMonthBalance", typeof(decimal));
    var remarksCol = ageingTvp.Columns.Add("Remarks", typeof(string));
    remarksCol.MaxLength = 150;
    
    // 后续行赋值逻辑保持不变
    foreach (var item in reportItems)
    {
        DataRow row = ageingTvp.NewRow();
        row["Id"] = item.Id;
        row["ReportId"] = item.ReportId = reportId;
        row["PrincipalId"] = item.PrincipalId;
        row["PrincipalName"] = item.PrincipalName.Trim();
        row["Department"] = item.Department;
        row["BusinessArea"] = item.BusinessArea;
        row["AmtLessThan60"] = item.AmtLessThan60 ?? 0;
        row["Amt61To90"] = item.Amt61To90;
        row["Amt91To180"] = item.Amt91To180;
        row["Amt181To365"] = item.Amt181To365;
        row["AmtGreaterThan365"] = item.AmtGreaterThan365;
        row["TotalAR"] = item.TotalAR;
        row["AdvanceReceived"] = item.AdvanceReceived ?? 0;
        row["Provision"] = item.Provision;
        row["NetReceivable"] = item.NetReceivable;
        row["PrevMonthBalance"] = item.PrevMonthBalance;
        row["Remarks"] = item.Remarks;
    }

    return ageingTvp;
}

2. 同步更新SQL Server的TVP自定义表类型 #

执行以下SQL，更新AgeingReportTvp类型的字段长度：

-- 注意：执行前需确保没有正在使用该类型的会话
DROP TYPE IF EXISTS AgeingReportTvp;
GO

CREATE TYPE AgeingReportTvp AS TABLE (
    Id UNIQUEIDENTIFIER,
    ReportId UNIQUEIDENTIFIER,
    PrincipalId VARCHAR(150),
    PrincipalName VARCHAR(150),
    Department VARCHAR(150),
    BusinessArea VARCHAR(150),
    AmtLessThan60 DECIMAL,
    Amt61To90 DECIMAL,
    Amt91To180 DECIMAL,
    Amt181To365 DECIMAL,
    AmtGreaterThan365 DECIMAL,
    TotalAR DECIMAL,
    AdvanceReceived DECIMAL,
    Provision DECIMAL,
    NetReceivable DECIMAL,
    PrevMonthBalance DECIMAL,
    Remarks VARCHAR(150)
);
GO

3. 验证事务与插入逻辑 #

• 检查tran.Complete()是否被正常执行，若代码在执行到此处前抛出异常，事务会自动回滚，导致插入失效
• 可以在sqlHelper.ExecuteNonQuery后打印返回的受影响行数，确认数据是否被正常插入

内容的提问来源于stack exchange，提问作者Ognjen Nikolic