Microsoft KB Archive/303016

= How to use a DataSet with the Office XP Chart Component and ASP.NET =

Article ID: 303016

Article Last Modified on 3/9/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Office Web Components
 * Microsoft .NET Framework 1.1 Service Pack 1

-



This article was previously published under Q303016





IN THIS TASK

 * SUMMARY
 * Sources of data for the Chart Component
 * Use the XML persistence format
 * Create an HTTP handler to create a DataSet and transform it into an XML data source
 * Bind the Office XP Chart Component to the XML data source
 * Use literal data
 * Considerations
 * REFERENCES



SUMMARY
This step-by-step article describes how to create a chart using the Office XP Chart Component with data from a DataSet. The solutions that this article presents use Visual Basic .NET and ASP.NET.

back to the top

Sources of data for the Chart Component
The Office XP Chart Component supports different types of data sources, such as the following:
 * The Chart Component can bind to a data source that implements the IDataSource interface, a standard COM interface for data source controls. The other Office XP Web Components (that is, the PivotTable Component, the Spreadsheet Component, and the DataSource controls) implement IDataSource, and may be potential data sources for a chart.
 * The Chart Component can bind to an ADO Recordset object.
 * The Chart Component can bind to an XML stream if the data is persisted to the stream in the XML persistence format.
 * A Chart Component can use literal data in the form of a delimited string or array; a chart that uses literal data is not considered bound.

The DataSet class in ADO.NET does not implement IDatasource, and the .NET Framework does not support a direct means to convert an ADO.NET DataSet object to an ADO Recordset object. If you have a DataSet from which you want to create a chart, you can use one of the two solutions that are presented in this article. You can either transform the DataSet into a stream that uses the XML persistence format for ADO, or you can generate literal data from the DataSet.

back to the top

Use the XML persistence format
This solution describes how to transform a DataSet into an XML stream to which the Chart Component can bind. This solution has two parts: a server-side HTTP handler that creates a DataSet and transforms it into an XML stream, and client-side script that binds the Chart Component to the XML stream.

Create an HTTP handler to create a DataSet and transform it into an XML data source
 On the File menu in Visual Studio .NET, click New, and then click Project. Click Visual Basic Projects, click the Empty Web Project template, and then name it ChartDemo1. On the Project menu, click Add Reference, click to select the following assemblies, and then click OK:  System.Data.Dll System.Dll System.Web.Dll</li> System.XML.Dll</li></ul>

</li> On the Project menu, click Add Class, name the class MakeData.vb, and then click Open.</li>  Replace the code in MakeData.vb with the following.

Note You must change User ID and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Imports System.Web Imports System.Xml Imports System.Xml.Xsl Imports System.Data Imports System.Data.SqlClient

Public Class MakeData Implements IHttpHandler

Public ReadOnly Property IsReusable As Boolean _ Implements IHttpHandler.IsReusable Get Return False End Get End Property

Public Sub ProcessRequest(ByVal context As HttpContext) _ Implements IHttpHandler.ProcessRequest Dim sConn As String = &quot;User ID= ;Password= ;Initial Catalog=Northwind;Data Source=YourSQLServer;&quot; Dim sSQL As String = _ &quot;SELECT LastName, Sum([UnitPrice]*[Quantity]*(1-[Discount])) AS OrderTotal &quot; & _ &quot;FROM Employees INNER JOIN (Orders INNER JOIN [Order Details] ON &quot; & _           &quot;Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID = &quot; & _ &quot;Orders.EmployeeID GROUP BY LastName&quot;

'Connect to the data source. Dim nwindConn As SqlConnection = New SqlConnection(sConn) nwindConn.Open

'Build a dataSet for Employee Sales. Dim ds As DataSet ds = New DataSet(&quot;MyDataset&quot;) Dim da As SqlDataAdapter = New SqlDataAdapter(sSQL, nwindConn) da.Fill(ds)

'Transform the dataSet using the stylesheet. Dim xmlDoc_ds As XmlDataDocument = New XmlDataDocument(ds) Dim xslTran As XslTransform = New XslTransform xslTran.Load(context.Server.MapPath(&quot;Rowset.xslt&quot;))

'Return the persisted recordset. context.Response.ContentType = &quot;text/xml&quot; context.Response.Charset = &quot;&quot; xslTran.Transform(xmlDoc_ds, Nothing, context.Response.Output)

End Sub

End Class NOTE: In the code, change the connection string in sConn to a valid connection string for a computer that is running Microsoft SQL Server and that contains the Northwind sample database.

</li> On the Project menu, click Add New Item, click the Web Configuration File template, and then click Open.</li>  Replace the contents of Web.Config with the following: <?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?> <system.web> <httpHandlers> <add verb=&quot;*&quot; path=&quot;MakeData.aspx&quot; type=&quot;ChartDemo1.MakeData, ChartDemo1&quot; /> </httpHandlers> </system.web> </li> On the Project menu, click Add New Item, click the XSLT File template, name the file Rowset.xslt, and then click Open.</li>  Replace the contents of Rowset.xslt with the following: <xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot;> <xsl:output method=&quot;xml&quot; omit-xml-declaration = &quot;yes&quot; indent=&quot;yes&quot;/> <xsl:template match=&quot;/&quot;> <xml xmlns:s=&quot;uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882&quot; xmlns:dt=&quot;uuid:C2F41010-65B3-11d1-A29F-00AA00C14882&quot; xmlns:rs=&quot;urn:schemas-microsoft-com:rowset&quot; xmlns:z=&quot;#RowsetSchema&quot;> <s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'> <s:AttributeType name=&quot;LastName&quot; rs:number=&quot;1&quot; rs:writeunknown=&quot;true&quot;> <s:datatype dt:type=&quot;string&quot; dt:maxLength=&quot;10&quot; rs:maybenull=&quot;false&quot; /> </s:AttributeType> <s:AttributeType name='OrderTotal' rs:number='2' rs:nullable='true'> <s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/> </s:AttributeType> <s:extends type='rs:rowbase'/> </s:ElementType> </s:Schema> <rs:data> <xsl:for-each select=&quot;MyDataset/Table&quot;> <xsl:element name=&quot;z:row&quot; namespace=&quot;#RowsetSchema&quot;> <xsl:attribute name=&quot;LastName&quot;> <xsl:value-of select=&quot;LastName&quot;/> </xsl:attribute> <xsl:attribute name=&quot;OrderTotal&quot;> <xsl:value-of select=&quot;OrderTotal&quot;/> </xsl:attribute> </xsl:element> </xsl:for-each> </rs:data> </xsl:template> </xsl:stylesheet> </li> Follow these steps to test the MakeData HTTP handler: <ol style="list-style-type: lower-alpha;"> On the Build menu, click Build Solution.</li> Start Microsoft Internet Explorer and then browse to http:// /ChartDemo1/MakeData.aspx

(where  is the name of your Web server).</li> Examine the XML that the browser displays. The XML uses the schema that is defined for the XML persistence format.</li></ol> </li></ol>

back to the top

Bind the Office XP Chart Component to the XML data source
<ol> Open the ChartDemo1 Web Application project in Visual Studio .NET.</li> On the Project menu, click Add HTMLPage.</li> Name the new page Default.htm, and then click Open. Default.htm appears in Design view.</li> Right-click Default.htm, click Properties, change the Target Schema to Internet Explorer 5.0, and then click Apply.</li> Change Default Scripting Language to VBScript, and then click OK.</li> <li>Follow these steps to add an Office XP Chart Component to Default.htm: <ol style="list-style-type: lower-alpha;"> <li>On the Tools menu, click Customize Toolbox.</li> <li>In the COM Components list, click Microsoft Office Chart 10.0, and then click OK.</li> <li>Drag a Chartspace control onto Default.htm.</li></ol> </li> <li>Follow these steps to add a Button control to Default.htm: <ol style="list-style-type: lower-alpha;"> <li>In the Toolbox window, click HTML.</li> <li>Drag a Button control onto Default.htm.</li> <li>Change the Value property of the button to Make Chart.</li></ol> </li> <li>Right-click the button that you added to Default.htm, and then click View Client Script.</li> <li> Add the following code to the Button1_onclick event: 'Bind the chart to the recordset. Dim c  Set c = Chchartspace1.Constants Chchartspace1.ConnectionString = &quot;Provider=MSPersist&quot; Chchartspace1.CommandText = &quot;http://YourWebServer/ChartDemo1/MakeData.aspx&quot; Chchartspace1.HasMultipleCharts = False Chchartspace1.PlotAllAggregates = c.chPlotAggregatesSeries Chchartspace1.SetData c.chDimCategories, c.chDataBound, &quot;LastName&quot; Chchartspace1.SetData c.chDimValues, c.chDataBound, &quot;OrderTotal&quot; NOTE: Change  in the URL to the name of your Web server.

</li> <li>On the File menu, click Save Default.htm.</li> <li>Follow these steps to test the solution: <ol style="list-style-type: lower-alpha;"> <li>Start Internet Explorer and browse to http:// /ChartDemo1/Default.htm

(where  is the name of your Web server).</li> <li>Click Make Chart. Note that a column chart with data from the DataSet appears.</li></ol> </li></ol>

back to the top

Use literal data
This solution describes how to generate literal data from a DataSet, and then use that literal data to build a chart. This solution uses an ASP.NET Web Form. The code that runs server-side generates a DataSet, and then returns the DataSet to the client as an XML data island; a PlaceHolder server control is used for the data island. Client-side script traverses the nodes in the data island to build arrays for the categories and values that are used to build the chart. <ol> <li>In Visual Basic ASP.NET, create a new Web Application project named ChartDemo2. Webform1.aspx is created by default and is shown in Design view.</li> <li>Right-click WebForm1, and then click View HTML Source.</li> <li> Replace the following element <form id=&quot;Form1&quot; method=&quot;post&quot; runat=&quot;server&quot;> with the following: <form id=&quot;Form1&quot; method=&quot;post&quot; runat=&quot;server&quot;> <asp:PlaceHolder id=&quot;PlaceHolder1&quot; runat=&quot;server&quot;></asp:PlaceHolder> <OBJECT id=&quot;CSpace&quot; classid=&quot;clsid:0002E556-0000-0000-C000-000000000046&quot; style=&quot;Z-INDEX: 101; LEFT: 9px; WIDTH: 576px; POSITION: absolute; TOP: 43px; HEIGHT: 348px&quot; VIEWASTEXT></OBJECT>

<button id=&quot;Mybtn&quot; style=&quot;Z-INDEX: 103; LEFT: 604px; POSITION: absolute; TOP: 46px&quot; type=&quot;button&quot;>Chart Data <script language=&quot;vbscript&quot;>

Function Mybtn_OnClick

'Extract the contents of the XML Data island and insert it into two 'arrays: one array for the chart categories and one array for the 'chart values. Dim nCount, nodes, i   Set nodes = dsXML.XMLDocument.childNodes.item(0).childNodes nCount = nodes.length Redim aNames(nCount) Redim aTotals(nCount) For i=1 to nCount aNames(i) = nodes.item(i-1).ChildNodes.item(0).text aTotals(i) = nodes.item(i-1).ChildNodes.item(1).text Next 'Create a chart from the array data. 'Bind the chart to the recordset. Dim c   Set c = CSpace.Constants Dim oChart, oSer CSpace.Clear Set oChart = CSpace.Charts.Add Set oSer = oChart.SeriesCollection.Add oSer.SetData c.chDimCategories, c.chDataLiteral, aNames oSer.SetData c.chDimValues, c.chDataLiteral, aTotals End Function </li> <li>On the View menu, click Design to return to Design view.</li> <li>On the View menu, click Code to view the code that is behind the Web form (Webform1.aspx.vb).</li> <li> Add the following code to the top of Webform1.aspx.vb: Imports System.Data.SqlClient Imports System.Xml Imports System.Xml.Xsl </li> <li> Add the following code to the Page_Load function in Webform1.aspx.vb.

Note You must change User ID and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Dim sConn As String = &quot;User ID= ;Password= ;Initial Catalog=Northwind;Data Source=YourSQLServer;&quot; Dim sSQL As String = _ &quot;SELECT LastName, Sum([UnitPrice]*[Quantity]*(1-[Discount])) AS OrderTotal &quot; & _ &quot;FROM Employees INNER JOIN (Orders INNER JOIN [Order Details] ON &quot; & _   &quot;Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID = &quot; & _ &quot;Orders.EmployeeID GROUP BY LastName&quot;

'Connect to the data source. Dim nwindConn As SqlConnection = New SqlConnection(sConn) nwindConn.Open

'Build a DataSet for Employee Sales. Dim ds As DataSet ds = New DataSet(&quot;MyDataset&quot;) Dim da As SqlDataAdapter = New SqlDataAdapter(sSQL, nwindConn) da.Fill(ds)

'Add the DataSet as XML to a data island in the PlaceHolder control. Dim sw As System.IO.StringWriter = New System.IO.StringWriter ds.WriteXml(sw) Dim lc As LiteralControl = New LiteralControl( _   &quot;<XML ID=&quot;&quot;dsXML&quot;&quot;>&quot; & sw.ToString & &quot;</XML>&quot;) PlaceHolder1.Controls.Add(lc) NOTE: In the code, change the connection string in sConn to a valid connection string for a computer that is running SQL Server and that contains the Northwind sample database.

</li> <li>Follow these steps to test the Web application: <ol style="list-style-type: lower-alpha;"> <li>Press F5 to build and run the application. The Web application loads in Internet Explorer.</li> <li>On the View menu in Internet Explorer, click View Source. Note that an XML data island with the ID &quot;dsXML&quot; exists in the HTML source; the data island was generated by the code in the Page_Load event of the Web form.</li> <li>On the Web form, click Chart Data to build the chart from the data in the XML data island.</li></ol> </li></ol>

back to the top

Considerations
You can create and use ADO Recordset objects in the .NET Framework through the COM Interop layer. Whether you use ADO Recordset objects or ADO.NET DataSets for your solution is a design choice. If you have existing code that creates DataSets and you want to add charting functionality, you can use one of the solutions that are discussed in this article. However, if you access data solely to create charts, Microsoft recommends that you use ADO through COM Interop so that you can bind directly to the data without conversion.

back to the top

<div class="references_section">