ASP.NET VB跨表查询问题：按数据存在性选数据源失败求助

我有两张数据表FixedAssetMaster_ora和FixedAssetMaster_old，需求是先检查目标数据是否在FixedAssetMaster_old表中：存在就从该表查，不存在才从FixedAssetMaster_ora查。但现在代码不管怎样都从FixedAssetMaster_ora取数，以下是我的代码：

Dim reccount As String = 0 
Conn = New SqlConnection 
Conn.ConnectionString = ConnStr 
Conn.Open() 
cmd = New SqlCommand 
cmd.CommandText = "Select COUNT(*) FROM FixedAssetMaster_old WHERE ASSET_NUMBER=" & AssetTxt.Text & " And LOC_DEPT=" & DeptTxt.Text & " AND UNIT_NO='" & UnitNoTxt.Text & "' AND (DATEPART(MM, UPDATE_DATE) = " & nowMonth & ") AND (DATEPART(yyyy, UPDATE_DATE) =" & nowYear & ") ;" 
cmd.Connection = Conn 
rdmysql = cmd.ExecuteReader 
If rdmysql.Read = True Then 
    'reccount = rdmysql.GetString(0) 
    reccount = Val(rdmysql.GetInt32(0)) 
End If 
cmd.Dispose() 
rdmysql.Close() 

If reccount = 0 Then 
    strsql = "Select * FROM FixedAssetMaster_old WHERE ASSET_NUMBER=" & AssetTxt.Text & " AND LOC_DEPT=" & DeptTxt.Text & " AND UNIT_NO='" & UnitNoTxt.Text & "' And (DATEPART(MM, UPDATE_DATE) = " & nowMonth & ") And (DATEPART(yyyy, UPDATE_DATE) =" & nowYear & ") ;" 
    cmd.Connection = Conn 
    cmd.CommandText = strsql 
    rdmysql = cmd.ExecuteReader 
    If rdmysql.Read = True Then 
        If rdmysql.IsDBNull(rdmysql.GetOrdinal("ASSET_KEY_SEGMENT3")) = False Then 
            LocationTxt.Text = rdmysql.GetString(rdmysql.GetOrdinal("ASSET_KEY_SEGMENT3")) 
        'If rdmysql.IsDBNull(rdmysql.GetOrdinal("REMARKS")) = False Then 
            RemarksTxt.Text = rdmysql.GetString(rdmysql.GetOrdinal("REMARKS")) 
        'If rdmysql.IsDBNull(rdmysql.GetOrdinal("REMARKS")) = False Then 
            DDLRemarks.Text = rdmysql.GetString(rdmysql.GetOrdinal("REMARKS")) 
        End If 
        cmd.Dispose() 
        rdmysql.Close() 
ElseIf reccount = 1 Then 
    strsql = "Select * FROM FixedAssetMaster_Ora WHERE ASSET_NUMBER=" & AssetTxt.Text & " AND LOC_DEPT='" & DeptTxt.Text & " ';" 
    cmd.Connection = Conn 
    cmd.CommandText = strsql 
    rdmysql = cmd.ExecuteReader 
    If rdmysql.Read = True Then 
        If rdmysql.IsDBNull(rdmysql.GetOrdinal("ASSET_KEY_SEGMENT3")) = False Then 
            LocationTxt.Text = rdmysql.GetString(rdmysql.GetOrdinal("ASSET_KEY_SEGMENT3")) 
        'If rdmysql.IsDBNull(rdmysql.GetOrdinal("REMARKS")) = False Then 
            RemarksTxt.Text = rdmysql.GetString(rdmysql.GetOrdinal("REMARKS")) 
        'If rdmysql.IsDBNull(rdmysql.GetOrdinal("REMARKS")) = False Then 
            DDLRemarks.Text = rdmysql.GetString(rdmysql.GetOrdinal("REMARKS")) 
        End If 
        cmd.Dispose() 
        rdmysql.Close() 
    End If 

问题根源 #

嘿，我一眼就看到核心问题了——你的判断逻辑完全写反了！

• 当reccount = 0（也就是旧表没有找到数据）时，你却跑去查询FixedAssetMaster_old
• 当reccount = 1（旧表找到数据）时，反而去查FixedAssetMaster_ora
  这完全和你的需求背道而驰，自然会出现不符合预期的结果。

除此之外，代码还有几个坑：

1. SQL注入风险：直接拼接用户输入到SQL语句里，非常危险
2. 资源管理混乱：手动调用Dispose()和Close()容易遗漏，导致资源泄漏
3. 字段引号不一致：查旧表时LOC_DEPT没加引号，查新表时加了，可能导致字符串类型字段查询失败
4. 日期查询效率低：用DATEPART函数会导致UPDATE_DATE的索引失效，影响查询速度

修复后的代码 #

我帮你重构了代码，解决了逻辑问题，同时修复了上述潜在风险：

Dim recordCount As Integer = 0

' 使用Using语句自动管理数据库资源，无需手动Dispose/Close
Using conn As New SqlConnection(ConnStr)
    conn.Open()

    ' 第一步：检查旧表中是否存在目标数据（参数化查询避免注入）
    Using checkCmd As New SqlCommand(
        "SELECT COUNT(*) FROM FixedAssetMaster_old 
         WHERE ASSET_NUMBER = @AssetNumber 
           AND LOC_DEPT = @LocDept 
           AND UNIT_NO = @UnitNo 
           AND UPDATE_DATE >= DATEFROMPARTS(@Year, @Month, 1) 
           AND UPDATE_DATE < DATEADD(month, 1, DATEFROMPARTS(@Year, @Month, 1))", conn)
        
        checkCmd.Parameters.AddWithValue("@AssetNumber", AssetTxt.Text)
        checkCmd.Parameters.AddWithValue("@LocDept", DeptTxt.Text)
        checkCmd.Parameters.AddWithValue("@UnitNo", UnitNoTxt.Text)
        checkCmd.Parameters.AddWithValue("@Year", nowYear)
        checkCmd.Parameters.AddWithValue("@Month", nowMonth)
        
        recordCount = Convert.ToInt32(checkCmd.ExecuteScalar())
    End Using

    ' 第二步：根据检查结果选择查询表
    Dim querySql As String
    If recordCount > 0 Then
        ' 旧表有数据，查询旧表
        querySql = "SELECT ASSET_KEY_SEGMENT3, REMARKS 
                    FROM FixedAssetMaster_old 
                    WHERE ASSET_NUMBER = @AssetNumber 
                      AND LOC_DEPT = @LocDept 
                      AND UNIT_NO = @UnitNo 
                      AND UPDATE_DATE >= DATEFROMPARTS(@Year, @Month, 1) 
                      AND UPDATE_DATE < DATEADD(month, 1, DATEFROMPARTS(@Year, @Month, 1))"
    Else
        ' 旧表无数据，查询新表
        querySql = "SELECT ASSET_KEY_SEGMENT3, REMARKS 
                    FROM FixedAssetMaster_Ora 
                    WHERE ASSET_NUMBER = @AssetNumber 
                      AND LOC_DEPT = @LocDept"
    End If

    ' 执行查询并赋值到控件
    Using queryCmd As New SqlCommand(querySql, conn)
        ' 共享参数
        queryCmd.Parameters.AddWithValue("@AssetNumber", AssetTxt.Text)
        queryCmd.Parameters.AddWithValue("@LocDept", DeptTxt.Text)
        
        ' 旧表查询需要额外参数
        If recordCount > 0 Then
            queryCmd.Parameters.AddWithValue("@UnitNo", UnitNoTxt.Text)
            queryCmd.Parameters.AddWithValue("@Year", nowYear)
            queryCmd.Parameters.AddWithValue("@Month", nowMonth)
        End If

        Using reader As SqlDataReader = queryCmd.ExecuteReader()
            If reader.Read() Then
                ' 处理位置字段
                If Not reader.IsDBNull(reader.GetOrdinal("ASSET_KEY_SEGMENT3")) Then
                    LocationTxt.Text = reader.GetString(reader.GetOrdinal("ASSET_KEY_SEGMENT3"))
                End If
                ' 处理备注字段
                If Not reader.IsDBNull(reader.GetOrdinal("REMARKS")) Then
                    RemarksTxt.Text = reader.GetString(reader.GetOrdinal("REMARKS"))
                    DDLRemarks.Text = reader.GetString(reader.GetOrdinal("REMARKS"))
                End If
            End If
        End Using
    End Using
End Using

修复说明 #

1. 修正核心逻辑：当旧表存在数据时优先查旧表，否则查新表，完全符合你的需求
2. 参数化查询：彻底消除SQL注入风险，同时统一字段处理方式，避免引号问题
3. Using语句：自动释放连接、命令、阅读器资源，避免内存泄漏
4. 优化日期查询：用范围查询代替DATEPART，能利用UPDATE_DATE的索引，查询更高效
5. 精简查询字段：只查询需要的字段，减少不必要的数据传输

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