locked
XML Built from Query Results RRS feed

  • Question

  • User-883855585 posted

    I have this piece of code that works if I iterate through the query multiple times and use a different result with each iteration.  That takes too long.  I need to be able to run the query once and pull out each result to the XML.  Is that possible?  if so, how?

    connectionString10 = "Data Source=server;Initial Catalog=DB;User ID=User;Password=password"
            stringxml10.Append("<graph caption='Lab Order Turnaround Time' SubCaption='Current Day' bgimage='lab1.png' bgimagealpha='20' bgimagedisplaymode='stretch' outCnvBaseFontSize='12' baseFontColor='#198119' showValues='1' bgcolor='1BAFE0,FFFFFF' canvasbgColor='#F0F8FF' animate='1' PxAxisName='Time (minutes)' SyAxisName='Total Orders' autoScaling='1'  >")
            Using conn10 As New SqlConnection(connectionString10)
    
                conn10.Open()
                Dim qry10 As New SqlCommand("select thedate, thehour, ordercount, " & _
                " avgtat " & _
                "from lab_order_tat_vw " & _
                "where thedate = CONVERT(varchar,getdate(),101)", conn10)
                Dim rst10 As SqlDataReader = qry10.ExecuteReader()
                stringxml10.Append("<categories> ")
                While rst10.Read()
    
                    stringxml10.Append("<category label='" & rst10("thehour").ToString() & "' />")
    
                End While
                rst10.Close()
                conn10.Close()
                stringxml10.Append("</categories>")
    
                conn10.Open()
                Dim qry11 As New SqlCommand("select thedate, thehour, ordercount, " & _
                " avgtat " & _
                "from lab_order_tat_vw " & _
                "where thedate = CONVERT(varchar,getdate(),101)", conn10)
    
                Dim rst11 As SqlDataReader = qry11.ExecuteReader()
                stringxml10.Append("<dataset seriesName='TurnTime' showvalue='1'  renderas='Column' color='#afe3fc'>")
                While rst11.Read()
    
                    stringxml10.Append("<set value='" & rst11("avgtat").ToString() & "' />")
                End While
                rst11.Close()
                conn10.Close()
                stringxml10.Append("</dataset>")
    
                conn10.Open()
                Dim qry13 As New SqlCommand("select thedate, thehour, ordercount, " & _
                " avgtat " & _
                "from lab_order_tat_vw " & _
                "where thedate = CONVERT(varchar,getdate(),101)", conn10)
                Dim rst13 As SqlDataReader = qry11.ExecuteReader()
                stringxml10.Append("<dataset seriesName='OrderCount' showvalue = '1' parentYaxis='S' renderAs='Line' style='Anim1' >")
                While rst13.Read()
    
                    stringxml10.Append("<set value='" & rst13("ordercount").ToString() & "' />")
                End While
                rst13.Close()
                conn10.Close()
                stringxml10.Append("</dataset>")
    
                conn10.Open()
    
                stringxml10.Append("<styles>  <definition> <style name='B' type='animation' easing='bounce' duration='0' /> <style name='Bevel1' type='bevel' angle='0' /> <style name='Anim1' type='animation' param='_xScale' start='0' duration='1' />  <style name='Anim2' type='animation' param='_alpha' start='0' duration='1' easing='bounce' /> </definition> <application> <apply toObject='CANVAS' styles='Anim1, Anim2, Bevel1' /> ")
                stringxml10.Append("<apply toObject='DATAPLOT' styles='Anim1, Anim2, Bevel1, B' />")
                stringxml10.Append("</application> </styles>")
    
                stringxml10.Append("</graph>")
    
                rst10.Close()
                conn10.Close()
    
    
            End Using
            Literal1.Text = FusionCharts.RenderChart("../FusionCharts/MSColumnline3D.swf", "", stringxml10.ToString(), "AvgTurnTime", "700", "300", _
             False, True)
    

    Basically this works but is very inefficient.  I need it to run much faster.  The query itself can run in less than 2 seconds from a query window.

    thanks

    Tuesday, March 21, 2017 4:05 PM

Answers

  • User-271186128 posted

    Hi Mongol648,

    Since you are using the same query, I suggest you to get your all data at first time.
    Then generate the xml with the data in behind code.

    For example:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim connectionString10 As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Northwind.MDF;Integrated Security=True;Connect Timeout=30"
            Dim stringxml10 As New StringBuilder()
            stringxml10.Append("<graph caption='Lab Order Turnaround Time' SubCaption='Current Day' bgimage='lab1.png' bgimagealpha='20' bgimagedisplaymode='stretch' outCnvBaseFontSize='12' baseFontColor='#198119' showValues='1' bgcolor='1BAFE0,FFFFFF' canvasbgColor='#F0F8FF' animate='1' PxAxisName='Time (minutes)' SyAxisName='Total Orders' autoScaling='1'  >")
            Dim dt As New DataTable
    
            Using conn10 As New SqlConnection(connectionString10)
    
                conn10.Open()
                Dim qry10 As New SqlCommand("select * from Customers ", conn10)
                Using sda As New SqlDataAdapter()
    
                    qry10.CommandType = CommandType.Text
                    qry10.Connection = conn10
                    sda.SelectCommand = qry10
                    sda.Fill(dt)
                End Using
                conn10.Close()
                stringxml10.Append("<categories> ")
                For Each row As DataRow In dt.Rows
                    stringxml10.Append("<category label='" & row("CompanyName").ToString() & "' />")
                Next
                stringxml10.Append("</categories>")
    
                stringxml10.Append("<dataset seriesName='TurnTime' showvalue='1'  renderas='Column' color='#afe3fc'>")
    
                For Each row As DataRow In dt.Rows
                    stringxml10.Append("<category label='" & row("ContactName").ToString() & "' />")
                Next
                stringxml10.Append("</dataset>")
    
    
                stringxml10.Append("<dataset seriesName='OrderCount' showvalue = '1' parentYaxis='S' renderAs='Line' style='Anim1' >")
    
                For Each row As DataRow In dt.Rows
                    stringxml10.Append("<category label='" & row("ContactTitle").ToString() & "' />")
                Next
    
                stringxml10.Append("</dataset>")
    
                stringxml10.Append("<styles>  <definition> <style name='B' type='animation' easing='bounce' duration='0' /> <style name='Bevel1' type='bevel' angle='0' /> <style name='Anim1' type='animation' param='_xScale' start='0' duration='1' />  <style name='Anim2' type='animation' param='_alpha' start='0' duration='1' easing='bounce' /> </definition> <application> <apply toObject='CANVAS' styles='Anim1, Anim2, Bevel1' /> ")
                stringxml10.Append("<apply toObject='DATAPLOT' styles='Anim1, Anim2, Bevel1, B' />")
                stringxml10.Append("</application> </styles>")
    
                stringxml10.Append("</graph>")
            End Using
        End Sub
    

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 22, 2017 8:15 AM