CSV Creation from two tables with URL for user to click on

Question

The code works but with an increase in data it's now slower so needs a tweak so looking for pointers.

'To import into Module  
    Dim sConnString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("SQL").ConnectionString
        Dim vDateNow As Date = Now()
    vDateNow = vDateNow.ToString("dd-MMMM-yyyy")
    Dim vDateNowStr As String = Replace(vDateNow, "/", "-")
    Dim vURL As String = "C:\Sites\Website\www\Reports\"



Private Sub PrcBespokeReports(vDateNowStr As String, vURL As String)
        '"#######################################################
        Dim vReportName As String = "Overall Risk Category"
        '"#######################################################
        '"CSV create code 
        '"######## Define the string
        Dim str As New StringBuilder
        '"######## Define the string
        '"########Connection code
         Dim dsNames As New SqlDataSource        
        dsNames.ConnectionString = sConnString
        Dim sSQL As String = ""
        Dim sSQLAttach As String = ""

'Main code

        sSQL = "SELECT a.ID AS ID,  a.UPRN,p.BuildingNo,p.Street,p.Postcode ,a.ItemRef,a.SurveyDate ,a.OverallRiskCategory ,a.SurveyorsComments FROM TblAsbestos AS a JOIN TblProperty As p on a.UPRN = p.UPRN ORDER BY a.OverallRiskCategory"

        dsNames.SelectCommand = sSQL
        '"########Connection code
        '"########Loop code to get data 
        str.Append(Chr(34) & "Report By Site to show ACM: - " & vDateNowStr & Chr(34) & ",")
        str.Replace(",", vbNewLine, str.Length - 1, 1) ' go to next line

        Dim arrNames As New ArrayList
        With arrNames
            .Add("UPRN")
            .Add("BuildingNo")
            .Add("Street")
            .Add("Postcode")
            .Add("ItemRef")
            .Add("SurveyDate")
            .Add("OverallRiskCategory")
            .Add("SurveyorsComments")
        End With

        For i As Integer = 0 To arrNames.Count - 1
            str.Append("=" & Chr(34) & arrNames(i).ToString() & Chr(34) & ",")
        Next

        str.Replace(",", vbNewLine, str.Length - 1, 1) ' go to next line

    'The above sets the headers and the array

        For Each datarow As Data.DataRowView In dsNames.Select(DataSourceSelectArguments.Empty)

            'Create the string / string builder and bring in the datarows
            For i As Integer = 0 To arrNames.Count - 1
                str.Append("=" & Chr(34) & Trim(Replace(If(TryCast(datarow(arrNames(i).ToString).ToString, String), ""), vbTab, " ") & Chr(34) & ","))
            Next
            '"########
            str.Replace(",", vbNewLine, str.Length - 1, 1) ' go to next line
        Next
    'Completed
        str.Append("Searched:" & sSQLAttach)

        '"Create CSV file or totally overwrites all data in CSV file
        My.Computer.FileSystem.WriteAllText((vURL & vDateNowStr & "_" & vReportName & ".csv"), str.ToString & "Created " & vDateNowStr, False)
        'Show CSV Link
        Dim vHyperLinkPath As String
        vHyperLinkPath = "http://Website.Net/Reports/" & vDateNowStr & "_" & vReportName & ".csv"
        CSVLinkName.Visible = vHyperLinkPath
        CSVLinkName.Visible = True

    End Sub

Answer 1

One thing that will help is to replace the ArrayList with a List(Of String). This will eliminate all the calls to ToString every time you want to get the value of an item in the list. Also you can build the output string as you add the items, rather than iterating over it again.

Dim arrNames As New List(Of String)
With arrNames
    .Add("UPRN")
    str.Append("=" & Chr(34) & "UPRN" & Chr(34) & ",")
    .Add("BuildingNo")
    str.Append("=" & Chr(34) & "BuildingNo" & Chr(34) & ",")
    .Add("Street")
    str.Append("=" & Chr(34) & "Street" & Chr(34) & ",")
    .Add("Postcode")
    str.Append("=" & Chr(34) & "Postcode" & Chr(34) & ",")
    .Add("ItemRef")
    str.Append("=" & Chr(34) & "ItemRef" & Chr(34) & ",")
    .Add("SurveyDate")
    str.Append("=" & Chr(34) & "SurveyDate" & Chr(34) & ",")
    .Add("OverallRiskCategory")
    str.Append("=" & Chr(34) & "OverallRiskCategory" & Chr(34) & ",")
    .Add("SurveyorsComments")
    str.Append("=" & Chr(34) & "SurveyorsComments" & Chr(34) & ",")
End With

Also in this line:

str.Append("=" & Chr(34) & Trim(Replace(If(TryCast(datarow(arrNames(i).ToString).ToString, String), ""), vbTab, " ") & Chr(34) & ","))

The TryCast is redundant since calling the ToString method will always return some sort of a string.