ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
 Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  How to use DBTable when using SQL Server Database?
Posted by liqipr on Oct-25-2005 16:57
| Reply | Monitor
Could someone help me?  Could someone give a SAMPLE for this issue?

  Re: How to use DBTable when using SQL Server Database?
Posted by Peter Kwan on Oct-25-2005 17:26
| Reply | Monitor
Hi liqipr,

In ChartDirector, there is some database sample code described in the section "Using Data Source with ChartDirector". May be you can use them as a reference.

The sample code is using MS Access as a sample database. Usng MS SQL Server is exactly the same, except you may need to change the connect string to fit your local environment. In .NET, may be you need to use SqlConnection instead of OleConnection.

If you need an example, please let me know of your programming language.

Regards
Peter Kwan

  Re: How to use DBTable when using SQL Server Database?
Posted by liqipr on Oct-26-2005 14:32
| Reply | Monitor
Thanks Peter,.Could you give me an example using C#?

  Re: How to use DBTable when using SQL Server Database?
Posted by Peter Kwan on Oct-26-2005 16:35
| Reply | Monitor
Attachments:
Hi liqipr,

I have attached a sample code for your reference. Basically, the sample code is the same as the "Simple Database Chart" sample code that comes with ChartDirector, except that I modified the OleConnection with SQLConnection.

Hope this can help.

Regards
Peter Kwan
CSharpSQL.aspx
<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="ChartDirector" %>
<%@ Register TagPrefix="chart" Namespace="ChartDirector" Assembly="netchartdir" %>
<%
    //
    //Open the database connection.
    //
    System.Data.IDbConnection dbconn = new System.Data.SqlClient.SQLConnection(
        "server=aaa;uid=bbb;pwd=ccc;database=ddd";);
    dbconn.Open();
    
    //
    //Create an SQL statement
    //
    System.Data.IDbCommand sqlCmd = dbconn.CreateCommand();
    sqlCmd.CommandText = "Select A, B, C From D Order By E";
    
    //
    //Read the data into the DBTable object to use them in the chart
    //
    DBTable table = new DBTable(sqlCmd.ExecuteReader());
    dbconn.Close();

    //
    //Now we obtain the data, we can start to draw the chart using ChartDirector
    //  

    //Create a XYChart of size 420 pixels x 240 pixels
    XYChart c = new XYChart(420, 240);

    //Set the chart background to pale yellow (0xffffc0) with a 2 pixel 3D border
    c.setBackground(0xffffc0, 0xffffc0, 2);

    //Set the plotarea at (70, 50) and of size 320 x 150 pixels. Set background 
    //color to white (0xffffff). Enable both horizontal and vertical grids by 
    //setting their colors to light grey (0xc0c0c0)
    c.setPlotArea(70, 50, 320, 150, 0xffffff, 0xffffff, 0xc0c0c0, 0xc0c0c0);

    //Add a title to the chart
    c.addTitle("Revenue for " + yearSelect.SelectedItem.Value, 
        "Times New Roman Bold Italic").setBackground(0xffff00);

    //Add a legend box at the top of the plotarea
    c.addLegend(70, 30, false, "", 8).setBackground(Chart.Transparent);

    //Add a stacked bar chart layer using the supplied data
    BarLayer layer = c.addBarLayer2(Chart.Stack);
    layer.addDataSet(table.getCol(0), -1, "Software");
    layer.addDataSet(table.getCol(1), -1, "Hardware");
    layer.addDataSet(table.getCol(2), -1, "Services");
    layer.setBorderColor(Chart.Transparent, 1);

    //Set the x axis labels. In this example, the labels must be Jan - Dec.
    string[] labels = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
        "Sept", "Oct", "Nov", "Dec"};
    c.xAxis().setLabels(labels);

    //Set the x-axis width to 2 pixels
    c.xAxis().setWidth(2);

    //Set the y axis title
    c.yAxis().setTitle("USD (K)");

    //Set the y-axis width to 2 pixels
    c.yAxis().setWidth(2);

    //Output the chart
    WebChartViewer1.Image = c.makeWebImage(Chart.PNG);
    
    //include tool tip for the chart
    WebChartViewer1.ImageMap = c.getHTMLImageMap("", "",
        "title='{dataSetName} Revenue for {xLabel} = USD {value}K'");
%>
<html>
    <body topmargin="5" leftmargin="5" rightmargin="0">
        <div style="font-size:18pt; font-family:verdana; font-weight:bold">
            Database Integration Demo (1)
        </div>
        <hr color="#000080">
        <div style="font-size:9pt; font-family:verdana; margin-bottom:1.5em">
            <a href='viewsource.aspx?file=<%=Request["SCRIPT_NAME"]%>'>View Source Code</a>
        </div>
        <div style="font-size:10pt; font-family:verdana; margin-bottom:1.5em; width:500px;">
            This example demonstrates creating a chart using data from a database.
        </div>
        <form id="Form1" method="post" runat="server">
            <div style="font-size:9pt; font-family:verdana; margin-bottom:1.5em">
                I want to obtain the revenue data for the year
                <asp:DropDownList id="yearSelect" runat="server">
                    <asp:ListItem Value="1990">1990</asp:ListItem>
                    <asp:ListItem Value="1991">1991</asp:ListItem>
                    <asp:ListItem Value="1992">1992</asp:ListItem>
                    <asp:ListItem Value="1993">1993</asp:ListItem>
                    <asp:ListItem Value="1994">1994</asp:ListItem>
                    <asp:ListItem Value="1995">1995</asp:ListItem>
                    <asp:ListItem Value="1996">1996</asp:ListItem>
                    <asp:ListItem Value="1997">1997</asp:ListItem>
                    <asp:ListItem Value="1998">1998</asp:ListItem>
                    <asp:ListItem Value="1999">1999</asp:ListItem>
                    <asp:ListItem Value="2000">2000</asp:ListItem>
                    <asp:ListItem Value="2001" Selected="True">2001</asp:ListItem>
                </asp:DropDownList>
                <asp:Button id="OKPB" runat="server" Text="OK"></asp:Button>
            </div>
            <chart:WebChartViewer id="WebChartViewer1" runat="server" />
        </form>
    </body>
</html>

  Re: How to use DBTable when using SQL Server Database?
Posted by Mani on Oct-28-2005 11:46
| Reply | Monitor
Im using VB 6 with MSAccess.
I would like to know how to connect Chat Director with MSAccess or VB6.
Please guide if there any user information how to use the Chat Director with "Trend Line" for XY scatter for the charts.

Your reply is greatly appreciated

Regards,
Mani

Peter Kwan wrote:
> Hi liqipr,
>
> In ChartDirector, there is some database sample code described in the section "Using Data Source with ChartDirector". May be you can use them as a reference.
>
> The sample code is using MS Access as a sample database. Usng MS SQL Server is exactly the same, except you may need to change the connect string to fit your local environment. In .NET, may be you need to use SqlConnection instead of OleConnection.
>
> If you need an example, please let me know of your programming language.
>
> Regards
> Peter Kwan

  Re: How to use DBTable when using SQL Server Database?
Posted by Peter Kwan on Oct-28-2005 12:31
| Reply | Monitor
Hi Mani,

In ChartDirector, there is some database sample code described in the section "Using Data Source with ChartDirector". May be you can use them as a reference.

Basically, you just need to query your MS Access database as you would normally do in VB6. (There are several methods to do this. You may refer to VB6 documentation for details.)

The followings is an example for your reference. It is modified from the standard Scatter Trend Chart sample code, but with the hard coded data replaced with database code.

    Dim cd As New ChartDirector.API

    'Connect to and query database using ADO as usual
    Dim rs
    Set rs = CreateObject("ADODB.RecordSet")
    Call rs.Open("Select X, Y From MyTable", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=aaa.mdb")

    'Read the record set object into DBTable
    Dim db As DBTable
    Set db = cd.DBTable(rs)
    Call rs.Close

    'Can get the data now
    Dim dataX0
    Dim dataY0
    dataX0 = db.getCol(0)
    dataY0 = db.getCol(1)

     'Create a XYChart object of size 450 x 420 pixels
    Dim c As XYChart
    Set c = cd.XYChart(450, 420)

    'Set the plotarea at (55, 65) and of size 350 x 300 pixels, with white background
    'and a light grey border (0xc0c0c0). Turn on both horizontal and vertical grid
    'lines with light grey color (0xc0c0c0)
    Call c.setPlotArea(55, 65, 350, 300, &HFFFFFF, -1, &HC0C0C0, &HC0C0C0, -1)

    'Add a legend box at (50, 30) (top of the chart) with horizontal layout. Use 12
    'pts Times Bold Italic font. Set the background and border color to Transparent.
    Call c.addLegend(50, 30, False, "timesbi.ttf", 12).setBackground(cd.Transparent)

    'Add a title to the chart using 18 point Times Bold Itatic font.
    Call c.addTitle("Server Performance", "timesbi.ttf", 18)

    'Add a title to the y axis using 12 pts Arial Bold Italic font
    Call c.yAxis().setTitle("Response Time (sec)", "arialbi.ttf", 12)

    'Set the y axis line width to 3 pixels
    Call c.yAxis().setWidth(3)

    'Set the y axis label format to show 1 decimal point
    Call c.yAxis().setLabelFormat("{value|1}")

    'Add a title to the x axis using 12 pts Arial Bold Italic font
    Call c.xAxis().setTitle("Server Load (TPS)", "arialbi.ttf", 12)

    'Set the x axis line width to 3 pixels
    Call c.xAxis().setWidth(3)

    'Add a scatter layer using (dataX0, dataY0)
    Dim scatter1 As scatterLayer
    Set scatter1 = c.addScatterLayer(dataX0, dataY0, "Server AAA", _
        cd.DiamondSymbol, 11, &H8000)

    'Add a trend line layer for (dataX0, dataY0)
    Dim trend1 As trendLayer
    Set trend1 = c.addTrendLayer2(dataX0, dataY0, &H8000)
    Call trend1.setLineWidth(3)

    'Add a scatter layer for (dataX1, dataY1)
    'output the chart
    Set viewer.Picture = c.makePicture()

    'Include tool tip for the chart.
    Dim scatterTip As String
    scatterTip = "title='{dataSetName}: Response time at {x} TPS: {value} sec'"
    Dim trendTip As String
    trendTip = "title='Slope = {slope|4} sec/TPS; Intercept = {intercept|4} sec'"

    viewer.ImageMap = scatter1.getHTMLImageMap("clickable", "", scatterTip) + _
        trend1.getHTMLImageMap("clickable", "", trendTip)

Hope this can help.

Regards
Peter Kwan

  Re: How to use Getslope with TrendLayer
Posted by vetri on Nov-07-2005 15:21
| Reply | Monitor
Hi,

How to use Getslope with TrendLayer, pls provide some sample VB code.

Thank you,
RM.Mani

  Re: How to use Getslope with TrendLayer
Posted by Peter Kwan on Nov-07-2005 16:32
| Reply | Monitor
Hi vetri,

I think you just call the getSlope method. For example:

Dim trend1 As trendLayer
Set trend1 = c.addTrendLayer2(dataX0, dataY0, &H8000)

Dim mySlope
mySlope = trend1.getSlope()

Hope this can help.

Regards
Peter Kwan

  Re: How to use DBTable when using SQL Server Database?
Posted by Hoang Vinh on Apr-14-2019 08:09
| Reply | Monitor
hi you ! Im using VB.net with Access datasource
I would like to know how to connect Chat Director with Access datasource with VB.net.
I only know  a language vb.net . other languages i can not use ! please help me ! thank you very much !

  Re: How to use DBTable when using SQL Server Database?
Posted by Hoang Vinh on Apr-14-2019 08:16
| Reply | Monitor
hi you ! Im using VB.net with Access datasource
I would like to know how to connect Chat Director with Access datasource with VB.net.
I only know  a language vb.net for windows applications. other languages i can not use ! please help me ! thank you very much !

  Re: How to use DBTable when using SQL Server Database?
Posted by Peter Kwan on Apr-16-2019 01:19
| Reply | Monitor
Hi Hoang,

There is an example at:

https://www.advsofteng.com/doc_v51/cdnet.htm#dbsample1.htm

Note that you need to ensure you use the proper database driver. The above sample code was written a long time ago. (We no longer include it in recent versions of ChartDirector.) It is using an old database driver oleDB 4.0. Microsoft no longer includes MS Access database drivers in Visual Studio. For recent versions of MS Access, we suggest to use the
Microsoft.ACE.OLEDB.12.0 driver that comes with MS Access 2010 or later. If you do not have the driver, you can download it from:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Hope this can help.

Regards
Peter Kwan

  Re: How to use DBTable when using SQL Server Database?
Posted by liqipr on Oct-27-2005 10:20
| Reply | Monitor
Thank you Peter! It work!!!