从数据表自动生成变量

Question

我正在调整Fast以创建一些报告，并且我已经创建了一个循环，该循环遍历存储在datatable中的查询结果，并将每个TenantName分配给一个变量。但是，我编写的代码看起来很笨重，如果需要更多的变量，就会失控。

是否有更明智的方法来做到这一点？

If cmbReports.Text = "Conditions Of Tenancy" Then
        Dim ListReport = New FastReport.Report
        ListReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx")
        ListReport.SetParameterValue("CRMConnectionString", "Data ..... ")
        Dim strSql As String = "Select  TenantForename + ' ' + TenantSurname as FullName From Tenants Where '" & lblLeaseIDValue.Text & "' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
        Dim dtb As New DataTable
        Dim ten1 As String
        Dim ten2 As String
        Dim ten3 As String
        Dim ten4 As String
        Dim ten5 As String
        Dim ten6 As String
        Dim ten7 As String
        Dim ten8 As String
        Dim ten9 As String
        Dim ten10 As String
        Dim ten11 As String
        Dim IntTenantID As Integer
        Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
            cnn.Open()
            Using dad As New SqlDataAdapter(strSql, cnn)
                dad.Fill(dtb)
            End Using
            Dim Ten As Integer = dtb.Rows.Count
            Do Until Ten = 0
                For Each row As DataRow In dtb.Rows
                    If Ten = 11 Then Ten1 = row("FullName")
                    If Ten = 10 Then Ten2 = row("FullName")
                    If Ten = 9 Then Ten3 = row("FullName")
                    If Ten = 8 Then Ten4 = row("FullName")
                    If Ten = 7 Then Ten5 = row("FullName")
                    If Ten = 6 Then Ten6 = row("FullName")
                    If Ten = 5 Then Ten7 = row("FullName")
                    If Ten = 4 Then Ten8 = row("FullName")
                    If Ten = 3 Then Ten9 = row("FullName")
                    If Ten = 2 Then Ten10 = row("FullName")
                    If Ten = 1 Then Ten11 = row("FullName")
                    Ten = Ten - 1T
                Next row
            Loop
            Dim strSql2 As String = "Select Top 1 TenantID From Tenants Where '" & lblLeaseIDValue.Text & "' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
            Dim datatableTenantID As New DataTable
            Using Tenant As New SqlDataAdapter(strSql2, cnn)
                Tenant.Fill(datatableTenantID)
            End Using
            For Each IntTenant As DataRow In datatableTenantID.Rows
                IntTenantID = IntTenant("TenantID")
            Next
            cnn.Close()
        End Using
        Dim TenantList As String = Ten11 & " " & Ten10 & " " & Ten9 & " " & Ten8 & " " & Ten7 & " " & Ten6 & " " & Ten5 & " " & Ten4 & " " & Ten3 & " " & Ten2 & " " & Ten1
        ListReport.SetParameterValue("PropertyID", Val(lblPropertyIDValue.Text))
        ListReport.SetParameterValue("LeaseID", Val(lblLeaseIDValue.Text))
        ListReport.SetParameterValue("TenantID", IntTenantID)
        ListReport.SetParameterValue("TenantList", TenantList)
        ListReport.Show()
    End If

Answer 1

• 使用数组，这将大大减少变量的数量。
• 正确命名变量(“10”应该是“租户”)
• 变量在.net中以小写开头
• 不要连接查询字符串，这将增加注射的机会。
• 在您的情况下，datatable有点无用，您可以使用数据处理机直接写入变量
• 您的listReport只需要在最后，我想您可以在那里初始化它。
• 我会把它分成多个函数。返回列表的"GetTenants“、"GetTenantID”和"ShowReport“。这样，这些函数就可以退出UI，并在需要时重用。
• 您的报告使用多个租户名称，但只有一个ID，这似乎很奇怪。如果cmbReports.Text =“租赁条件”那么Dim租户作为新列表(字符串) Dim tenantID As integer Dim propertyId As integer Dim leaseId As Integer Dim leaseId As Int32.TryParse(lblPropertyIDValue.Text，propertyId)那么‘如果不是整数，这不是一个错误结束，如果不是Int32.TryParse(lblLeaseIDValue.Text，leaseId)’则不是一个整数，如果租户= GetTenants(propertyId，leaseId) tenantID = GetTenantID(propertyId，leaseId) ShowReport(GetTenants，leaseId)tenantID=GetTenantID(propertyId，leaseId)ShowReport，则这不是一个错误结束。SqlConnection(My.Settings.BMSSouthSideConnectionString) tenantID，propertyId，leaseId)如果函数GetTenants(ByVal PropertyID As Integer，ByVal leaseId作为Integer)结束为List( String)，Dim租户作为新列表(Of String)，使用cnn作为新的ByVal cnn.Open() Dim命令作为新的SqlCommand(“选择TenantForename +‘’+ TenantSurname作为FullName，从租户中选择LeaseID = @LeaseID和propertyId =@PropertyID，cnn) command.Parameters.Add("@LeaseID"，( SqlDbType.Int).Value = leaseId command.Parameters.Add("@ propertyId "，SqlDbType.Int).Value = propertyId使用读取器作为command.ExecuteReader()，而reader.Read() tenants.Add(reader.GetString("FullName"))循环端使用cnn.Close()结束使用返回租户端函数GetTenantID(ByVal propertyId作为整数，使用cnn作为新SqlConnection(My.Settings.BMSSouthSideConnectionString) ByVal () cnn.Open() Dim命令作为新SqlCommand(“从租户中选择LeaseID = @LeaseID和PropertyID =@PropertyID”、PropertyID=@PropertyID) command.Parameters.Add("@ leaseId "，SqlDbType.Int).Value = leaseId command.Parameters.Add("@PropertyID"，( SqlDbType.Int).Value = propertyId使用读取器作为command.ExecuteReader()如果reader.Read()则tenantID = reader.GetInt32(" tenantID ")如果结束使用cnn.Close()结束使用返回tenantID结束函数Sub ShowReport(ByVal租户作为列表(字符串)，ByVal tenantID作为Integer，ByVal propertyId作为Integer，listReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx") listReport.SetParameterValue("CRMConnectionString"，ByVal leaseId As Integer) Dim listReport = New FastReport.Report listReport.Dim tenantList As String = String.Join(“"，tenants.ToArray()) ListReport.SetParameterValue("PropertyID"，propertyId) ListReport.SetParameterValue("LeaseID"，tenantID) ListReport.SetParameterValue("TenantID"，tenantID) ListReport.SetParameterValue("TenantList"，tenantList) ListReport.Show() End Sub

Answer 2

这里有几件事。

• DataTable也使用IDisposable接口，所以您也应该使用using块
• 不需要显式关闭连接，因为IDisposable接口在Using语句中这样做

看起来你的Where条件是向后的，我将假设在剩下的回顾中，并将它扭转过来。

我不明白你为什么要写这个

Where '" & lblLeaseIDValue.Text & '" = LeaseID

这在我看来不太对。是否允许用户输入Where条件的列名？就像我说的，我认为这是这次审查的倒退。

另外，Select语句参数应该是我稍后将展示的实际参数。

最好使用SqlCommand并插入带有参数的Select语句，然后执行ExecuteNonQuery并将命令传递给DataAdapter。通过这种方式，您可以参数化输入，使它们在Sql注入时更加安全。

下面是我在没有将代码分解成方法/函数的情况下得出的结果，您应该像@the_lotus说的那样做，但是使用更多的语句

If cmbReports.Text = "Conditions Of Tenancy" Then
    Dim ListReport = New FastReport.Report
    ListReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx")
    ListReport.SetParameterValue("CRMConnectionString", "Data ..... ")
    Dim dtb As New DataTable
    Dim ten1 As String
    Dim ten2 As String
    Dim ten3 As String
    Dim ten4 As String
    Dim ten5 As String
    Dim ten6 As String
    Dim ten7 As String
    Dim ten8 As String
    Dim ten9 As String
    Dim ten10 As String
    Dim ten11 As String
    Dim IntTenantID As Integer
    Dim strSql As String = "Select  TenantForename + ' ' + TenantSurname as FullName From Tenants Where LeaseID = @lblLeaseIDValue and PropertyID = @lblPropertyIDValue"

    Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
        Using cmd1 As New SqlCommand(strSql, cnn)
            cmd1.Parameters.AddWithValue("@lblLeaseIDValue", lblLeaseIDValue.Text)
            cmd1.Parameters.AddWithValue("@lblPropertyIDValue", lblPropertyValueID.Text)
            cmd1.ExecuteNonQuery()
            Using dad As New SqlDataAdapter(cmd1, cnn)
                Using dtb As New DataTable()
                    cnn.Open()
                    dad.Fill(dtb)
                    Dim Ten As Integer = dtb.Rows.Count
                    Do Until Ten = 0
                        For Each row As DataRow In dtb.Rows
                            If Ten = 11 Then Ten1 = row("FullName")
                            If Ten = 10 Then Ten2 = row("FullName")
                            If Ten = 9 Then Ten3 = row("FullName")
                            If Ten = 8 Then Ten4 = row("FullName")
                            If Ten = 7 Then Ten5 = row("FullName")
                            If Ten = 6 Then Ten6 = row("FullName")
                            If Ten = 5 Then Ten7 = row("FullName")
                            If Ten = 4 Then Ten8 = row("FullName")
                            If Ten = 3 Then Ten9 = row("FullName")
                            If Ten = 2 Then Ten10 = row("FullName")
                            If Ten = 1 Then Ten11 = row("FullName")
                            Ten = Ten - 1T
                        Next row
                    Loop
                End Using
            End Using
        End Using


        Dim strSql2 As String = "Select Top 1 TenantID From Tenants Where LeaseID = @lblLeaseIDValue and PropertyID = @lblPropertyIDValue"
        Using datatableTenantID As New DataTable
            Using cmd As New SqlCommand(strSql2, cnn)
                cmd.Parameters.AddWithValue("@lblLeaseIDValue", lblLeaseIDValue.Text)
                cmd.Parameters.AddWithValue("@lblPropertyIDValue", lblPropertyValueID.Text)
                cmd.ExecuteNonQuery()
                Using Tenant As New SqlDataAdapter(cmd, cnn)
                    Tenant.Fill(datatableTenantID)
                    For Each IntTenant As DataRow In datatableTenantID.Rows
                        IntTenantID = IntTenant("TenantID")
                    Next
                    cnn.Close()
                End Using
            End Using
        End Using
    End Using
    Dim TenantList As String = Ten11 & " " & Ten10 & " " & Ten9 & " " & Ten8 & " " & Ten7 & " " & Ten6 & " " & Ten5 & " " & Ten4 & " " & Ten3 & " " & Ten2 & " " & Ten1
    ListReport.SetParameterValue("PropertyID", Val(lblPropertyIDValue.Text))
    ListReport.SetParameterValue("LeaseID", Val(lblLeaseIDValue.Text))
    ListReport.SetParameterValue("TenantID", IntTenantID)
    ListReport.SetParameterValue("TenantList", TenantList)
    ListReport.Show()
End If

您的循环奇怪地类似于for循环，我确信，如果要为租户列表使用其他变量类型，您可以相当多地清理该循环。

Answer 3

可以使用动态sql生成查询。只有11个租户，这并不能使代码更短，但是它可以扩展到您想要的任何租户。

declare @minten int;
declare @maxten int;
declare @query varchar(8000);
declare @dims varchar(8000);
declare @ifs varchar(8000);
declare @TenList varchar(8000);
set @minten = 1;
set @maxten = 11;

with tmp(t) as (
    select @minten
    union all
    select t+1
    from tmp
    where t< @maxten)
Select * into #tmp from tmp

Select @dims = coalesce(@dims + 'dim Ten' + cast(t as varchar(3)) + ' as String
', 'dim Ten' + cast(t as varchar(3)) + ' as String
') from #tmp
order by t
Select @ifs = coalesce(@ifs + 'If Ten = ' + cast(t as varchar) + ' Then Ten' + cast(t as varchar(3)) + ' = row("FullName")
','If Ten = ' + cast(t as varchar) + ' Then Ten' + cast(t as varchar(3)) + ' = row("FullName")
') from #tmp
order by t
Select @TenList = coalesce(@TenList + ' & " " & Ten' + cast(t as varchar(3)),'Ten' + cast(t as varchar(3)))
from #tmp
order by t


set @query = 'If cmbReports.Text = "Conditions Of Tenancy" Then
        Dim ListReport = New FastReport.Report
        ListReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx")
        ListReport.SetParameterValue("CRMConnectionString", "Data .....
        Dim strSql As String = "Select  TenantForename + '' '' + TenantSurname as FullName From Tenants Where ''" & lblLeaseIDValue.Text & "'' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
        Dim dtb As New DataTable
        ' + @dims + '
        Dim IntTenantID As Integer
        Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
            cnn.Open()
            Using dad As New SqlDataAdapter(strSql, cnn)
                dad.Fill(dtb)
            End Using
            Dim Ten As Integer = dtb.Rows.Count
            Do Until Ten = 0
                For Each row As DataRow In dtb.Rows
        ' + @ifs + '
        Ten = Ten - 1T
                Next row
            Loop
            Dim strSql2 As String = "Select Top 1 TenantID From Tenants Where ''" & lblLeaseIDValue.Text & "'' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
            Dim datatableTenantID As New DataTable
            Using Tenant As New SqlDataAdapter(strSql2, cnn)
                Tenant.Fill(datatableTenantID)
            End Using
            For Each IntTenant As DataRow In datatableTenantID.Rows
                IntTenantID = IntTenant("TenantID")
            Next
            cnn.Close()
        End Using
        Dim TenantList As String = ' + @TenList + '
        ListReport.SetParameterValue("PropertyID", Val(lblPropertyIDValue.Text))
        ListReport.SetParameterValue("LeaseID", Val(lblLeaseIDValue.Text))
        ListReport.SetParameterValue("TenantID", IntTenantID)
        ListReport.SetParameterValue("TenantList", TenantList)
        ListReport.Show()
    End If'

    Select @query
    --Execute (@query)