【039】U9私有字段自定义值集的多语言表的SQL更新

更新销售订单行第18个扩展字段（ZDY016配送点值集）的示例SQL，A.ID=1002111020110059为销售订单行示例ID

-- 查询销售订单行的扩展字段18（配送点自定义值集，非枚举，使用Fun_Cust_Table_DefineValue存储过程关联）
SELECT A.ID,ISNULL(def.Code, '') Code,ISNULL(def.Name, '') Name,'zh-CN' as SysMLFlag INTO #TempDataID FROM dbo.SM_SOLine A
LEFT JOIN dbo.Fun_Cust_Table_DefineValue('ZDY016') def ON def.Code=A.DescFlexField_PrivateDescSeg18
WHERE A.ID=1002111020110059
EXEC dbo.P_Samuel_CombineNameUpdate 'SM_SOLine','#TempDataID',68 -- 68=50+18
DROP TABLE #TempDataID

存储过程：Fun_Cust_Table_DefineValue

/****** Object:  UserDefinedFunction [dbo].[Fun_Cust_Table_DefineValue]    Script Date: 2021/11/2 15:50:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER FUNCTION [dbo].[Fun_Cust_Table_DefineValue]
(@code NVARCHAR(50))
RETURNS @t TABLE(ID BIGINT, Code NVARCHAR(50),Name NVARCHAR(50)) 
AS
BEGIN

    INSERT INTO @t  
    SELECT c.ID,c.Code,C2.Name 
    FROM dbo.Base_DefineValue C 
    INNER JOIN dbo.Base_ValueSetDef C1 ON C1.ID=C.ValueSetDef 
    INNER JOIN dbo.Base_DefineValue_Trl AS C2 ON  C.ID=C2.ID 
    WHERE C1.Code=@code

  RETURN 
END
GO

C# 方法封装

/// <summary>
/// 更新表中扩展字段是自定义值集的多语言
/// </summary>
/// <param name="tbName">要更新的表名</param>
/// <param name="define">要更新的自定义值集编码</param>
/// <param name="fieldIdx">要更新的表的扩展字段索引</param>
/// <param name="ids">要更新的表的id集合，null或者长度为0则更新全部</param>
public static void UpdateTrlByDefineValue(string tbName, string define, int fieldIdx, List<long> ids)
{
    string updTrlSql = string.Format(@"SELECT A.ID,ISNULL(def.Code, '') Code,ISNULL(def.Name, '') Name,'zh-CN' as SysMLFlag INTO #TempDataID FROM dbo.{0} A
        LEFT JOIN dbo.Fun_Cust_Table_DefineValue('{1}') def ON def.Code=A.DescFlexField_PrivateDescSeg{2}
        WHERE A.ID in ({4})
        EXEC dbo.P_Samuel_CombineNameUpdate '{0}','#TempDataID',{3}
        DROP TABLE #TempDataID", tbName, define, fieldIdx, 50 + fieldIdx
        , ids != null && ids.Count > 0 ? string.Format("WHERE A.ID in ({0})", string.Join(",", ids)) : "");

    DataAccessor.RunSQL(DataAccessor.GetConn(), updTrlSql, null);
}