SQL时区转换函数迁移至C#逻辑失效问题排查

原SQL函数[dbo].[fnGetConvertDateTimeWithTimeZoneOffset]可正常实现组织时区转换，迁移到C#内联查询后功能失效，对比两者逻辑，以下是核心差异及问题点：

1. 位置存在性检查的逻辑不一致 #

原SQL中仅当@LocationID > 0时，才会检查该位置是否存在：

IF NOT EXISTS(SELECT 1 FROM dbo.Organization_TimeZone WHERE OrganizationID = @OrganizationID AND LocationId = @LocationID AND @LocationID > 0)

而C#的CheckIfLocationExists方法未添加locationId > 0的判断，当传入locationId = 0时，仍会执行查询，导致不符合原逻辑的locationId重置操作。

2. 缺失NOLOCK查询提示 #

原SQL查询Organization_TimeZone时使用WITH (NOLOCK)避免锁等待，C#代码的查询语句未添加该提示，高并发场景下可能出现查询阻塞或读取不一致数据，影响转换结果。

3. 多匹配行的处理逻辑差异 #

原SQL中如果Organization_TimeZone存在多条符合条件的记录，SELECT @TimeOffset = ...会将变量赋值为最后一行的结果；而C#的reader.Read()仅读取第一行数据，忽略后续匹配行，导致结果和原SQL不一致。

4. 连接字符串重复定义（代码缺陷） #

C#的GetTimeZoneAndDaylightSavingInfo和CheckIfLocationExists方法重复定义了连接字符串，后续维护易出现漏改问题。

修复后的C#代码 #

using System;
using System.Data.SqlClient;

public class TimeZoneConversion
{
    // 统一维护连接字符串
    private const string ConnectionString = "your_connection_string_here";

    public static DateTime ConvertDateTimeWithTimeZoneOffset(int organizationId, int locationId, DateTime inputDate)
    {
        int timeOffset = 0;              
        DateTime convertedDateTime = inputDate; 
        int dayLightSavingHour = 60;     
        char isDayLight = 'N';          

        // 仅当locationId>0时才检查位置是否存在
        if (locationId > 0 && !CheckIfLocationExists(organizationId, locationId))
        {
            locationId = 0;
        }

        GetTimeZoneAndDaylightSavingInfo(organizationId, locationId, ref timeOffset, ref isDayLight);

        if (timeOffset != 0)
        {
            if (isDayLight == 'Y')
            {
                timeOffset = AdjustForDaylightSaving(timeOffset, dayLightSavingHour);
            }

            convertedDateTime = convertedDateTime.AddMinutes(timeOffset);
        }

        return convertedDateTime;
    }

    private static void GetTimeZoneAndDaylightSavingInfo(int organizationId, int locationId, ref int timeOffset, ref char isDayLight)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // 添加NOLOCK提示，同时按主键排序确保取最后一行（模拟SQL赋值逻辑）
            string query = @"
                SELECT TOP 1 TimeOffsetValue, IsDayLight
                FROM dbo.Organization_TimeZone WITH (NOLOCK)
                WHERE OrganizationID = @OrganizationID
                AND (ISNULL(LocationId, 0) = @LocationID)
                AND IsActive = 'Y'
                ORDER BY [TimeZoneID] DESC"; // 替换为实际主键字段

            using (SqlCommand cmd = new SqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("@OrganizationID", organizationId);
                cmd.Parameters.AddWithValue("@LocationID", locationId);

                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        timeOffset = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
                        isDayLight = reader.IsDBNull(1) ? 'N' : reader.GetString(1)[0];
                    }
                    else
                    {
                        timeOffset = 0;
                        isDayLight = 'N';
                    }
                }
            }
        }
    }

    private static int AdjustForDaylightSaving(int timeOffset, int dayLightSavingHour)
    {
        if (timeOffset > 0)
        {
            return timeOffset - dayLightSavingHour;
        }
        else if (timeOffset < 0)
        {
            return timeOffset + dayLightSavingHour; 
        }
        return timeOffset; 
    }

    private static bool CheckIfLocationExists(int organizationId, int locationId)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            string query = @"
                SELECT 1
                FROM dbo.Organization_TimeZone WITH (NOLOCK)
                WHERE OrganizationID = @OrganizationID
                AND LocationId = @LocationID
                AND IsActive = 'Y'";

            using (SqlCommand cmd = new SqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("@OrganizationID", organizationId);
                cmd.Parameters.AddWithValue("@LocationID", locationId);

                connection.Open();
                var result = cmd.ExecuteScalar();
                return result != null;
            }
        }
    }
}

class Program
{
    static void Main()
    {
        DateTime inputDate = new DateTime(2024, 11, 18, 14, 0, 0);
        DateTime convertedDateTime = TimeZoneConversion.ConvertDateTimeWithTimeZoneOffset(811, 1, inputDate);
        Console.WriteLine("Converted DateTime: " + convertedDateTime);
    }
}

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