0
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Would there be value in using a List or Array and looping through that for simplicity going forward or is the above more "Robust"? (Maybe the wrong word but gets the thought across) \$\endgroup\$ – indofraiser Oct 15 '15 at 14:05
  • \$\begingroup\$ @indofraiser - For your purposes, there probably wouldn't be much difference performance wise, between an array and a List. Mostly it would depend on how static you want the collection to be. I believe a List would be easier to maintain if you have a reason to change which columns and/or their names you're using. \$\endgroup\$ – tinstaafl Oct 16 '15 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.