Using recordsets

[Edward Sokolove]

I wanted to know how I would integrate a recordset from a sql server database?  I'm creating an org chart.  Also, how do I set options like collapsible =true?

[Edward Sokolove]

I'm really trying to find an example of how to connect a SQL Server DB to populate the orgChart.  I've looked and have not found a good working example.  I found a really good one for a pie chart but not exactly sure how to convert it to an orgchart.  Any help would be greatly appreciated.

[Sergey Grabkovsky]

We don't directly support this use case. If your code is in Java, it might be a little simpler to use our Data Source Library (https://code.google.com/p/google-visualization-java/) to create a Data Source that can be used with our queries, but beyond that we don't really do anything to make this easier.

You may want to search for "web application read from SQL", to get an overview of what to do, but the gist is that you will need a server, written in some language. That server will do the actual reading from the SQL datatable, and return the data in some format. Your JavaScript will fire a request to this server, parse the data, convert it into a DataTable, and use it in a chart.

If you would point to the pie chart example that you found, I could help you convert it to an org chart. But beyond that, there's a lot of non-visualization stuff that you need to figure out before you can actually get the visualization working with data from SQL.

As for your second question, about setting options, you would set them in the JavaScript, before you draw your chart. If you'll notice, our examples (located at https://developers.google.com/chart/interactive/docs/gallery/orgchart) have a chart.draw(...) call that takes data and an object. That object is where you need to put your options. For example, in the example on that page, the draw call would turn into chart.draw(data, {collapsible: true, allowHtml:true});

I hope that helps.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

[Edward Sokolove]

Here are the code files that I got to work with their examples for a graph but as you know I'd like it changed slightly to output to an orgchart.  Thanks for all of your help.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/7R83Swnufm0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Sergey Grabkovsky]

OK, so it looks like you'll mainly need to modify your Graph.aspx.cs file. Here are the things you'll need to change:

1. Line 41: This is the part that tells it which package(s) to load. You need to load the "orgchart" package, as per the documentation page I linked to in my last post. Change "corechart" to "orgchart" to load the orgchart package instead of the corechart package.
2. Line 46: These are the headers of your columns. Effectively, these are just the titles. I don't know if you're planning on using tooltips, but you'd have either two or three columns here. Look at the Data Format section of the documentation for that. Play around with the jsfiddle example linked from the documentation page to help you decide.
3. Lines 50-51: This is where you're reading the actual data from the SQL response. You'll need to replace "Month", "Bolivia", etc. with whatever your column names are. Make sure to only have as many items here as you have headers. Otherwise, you'll get an error. Pay special attention to the quotes (the single ones—"['"), since those will be surrounding your strings in the generated JavaScript, and if they're not there or are unmatched, that will cause errors. Note that there are no single quotes are row["Bolivia"]. This is because in this example, that datum is a number. You will need to put quotes there, since OrgChart only takes strings. So, for example, "['" + row["Month"] + "','" + row["Bolivia"] + "','" + ...
4. Line 56: Most of the pie chart options don't apply to the org chart. You would clear this object and put your options (i.e. collapsible: true) here. Make sure to close all the braces.
5. Line 57: Instead of "ComboChart", you will need to write "OrgChart", since you want to construct an OrgChart, and not a ComboChart.

All of that should get you started. I hope you have enough coding knowledge to not miss quotes and close all the braces, since computers are very picky about that.

In my opinion, this code is poorly structured. Your chart logic should be in a JavaScript file, instead of being generated by an ASPX file. The ASPX file should only return JSON, which should be fetched by a query. You could also insert the JSON directly into the file, though I would personally discourage that.

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.

[Edward Sokolove]

I'm a sql server developer getting my hands a little dirty with the front-end.  For the most part I can read the code but was unsure if it was as simple as changing a few things from a graph to an orgchart.  I'm not really understanding the whole JSON/javascript file and how the aspx talks to the js file...lots to learn.

[Edward Sokolove]

I think I have most of it but the only thing showing up is the table.  I've included the previous hardcoded orgchart (what I want to replace), the .cs page and  a picture of the website without the actual orgchart just the table, I want to eventually get rid of the table and just have the orgchart.

On Fri, Nov 14, 2014 at 1:46 PM, 'Sergey Grabkovsky' via Google Visualization API <google-visua...@googlegroups.com> wrote:

[Daniel Buttery]

I've been doing work with Google charts in ASP.Net. The easiest way I found was to create a web service (ASMX) that can gather and return the data which the charts then use.

Not in the office at the mo but will post some code in a bit. Essentially the Web Service connects to SQL and creates a data table as normal, then returns it as a JSON string. Will try to post some code later.

[Daniel Buttery]

Heres a sample web service - sorry for the VB.net... ;)

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Services


' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _


 

Public Class Lifecycle
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    <ScriptMethod(UseHttpGet:=True, ResponseFormat:=ResponseFormat.Json)> _
    Public Function GetAllData() As String
        Dim myDT As DataTable = GetData("SELECT * FROM mySQLTable", "mySQLTable")
        Return DataTableToJSON(myDT)
    End Function

    Private Function GetData(SQLCommand As String, TableName As String) As DataTable
        Dim myCMD As String = ""
        Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)
        conn.Open()
        Dim myDA As New SqlDataAdapter(SQLCommand, conn)
        Dim dsData As New DataSet(TableName)
        dsData.EnforceConstraints = False
        myDA.FillSchema(dsData, SchemaType.Source, TableName)
        myDA.Fill(dsData, TableName)
        Dim tblDT As DataTable
        tblDT = dsData.Tables(TableName)
        conn.Close()
        Return tblDT
    End Function

    Private Function DataTableToJSON(myDT As DataTable) As String
        Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
        serializer.MaxJsonLength = Int32.MaxValue
        Dim rows As New List(Of Dictionary(Of String, Object))
        Dim row As Dictionary(Of String, Object)
        For Each dr As DataRow In myDT.Rows
            row = New Dictionary(Of String, Object)
            For Each col As DataColumn In myDT.Columns
                row.Add(col.ColumnName, dr(col))
            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function
End Class

From your javascript setting up the Charts, its then simply a matter of calling this service to get the data....

function getAllData() {
    $.ajax({
        type: 'GET',
        dataType: 'json',
        contentType: 'application/json',
        url: '/Services/Lifecycle.asmx/GetAllData',
        success:
            function (response) {
                setTable(response.d);
            }
    });
}


function setTable(dataValues) {
    var result = JSON.parse(dataValues);
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'ColumnName');

    data.addColumn('datetime', 'ColumnName2');

    for (var i = 0; i < result.length; i++) {
        data.addRow([
            result[i].ColumnName,
            result[i].ColumnName2
        ]);
    }
    doCharts(data);
}




Hope that makes sense - in the web service you can manipulate the data, do anything .Net to it you deem fit, that then gets translated into JSON and served on request to your javascript file.