XML Built from Query Results

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