Display record on Tree Chart node click from Database in ASP.Net using C# and VB.Net

vereato
 
on Dec 15, 2021 10:03 PM
544 Views

Hi there

How i can have a database using MySQL or SQL for movies  

like this you have on the link  

Create Family Tree Hierarchy Chart from Database in ASP.Net using C# and VB.Net

i want to have a list of all actors  like 

Jean Claude Van Dame 

Prianka Chopra 

Jaquline Fernandez  

on click  on Jean Claude Van Dame name it will show all movies like this  

Kickboxer 

12 bullets 

kill the all 

conquest 

etc etc etc 

can you explain to me how i can do this using  family tree and database   

thanks 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Dec 15, 2021 10:45 PM

I am working on it, Will get back to you within 3 hours.

dharmendr
 
on Dec 17, 2021 03:02 AM
on Jan 04, 2022 11:37 PM

Hi vereato,

You need to make ajax call in tree node click. Based on the selected value fetch the record from database and display.

Refer below example.

Database

I have used the following table FamilyHierarchy with the schema as follows.

I have already inserted few records in the table.

The Parent of each Family Member can be determined using the ParentId column which stores the ParentId of the parent of a Member.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["orgchart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetChartData",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = new google.visualization.DataTable();
                data.addColumn('string', 'Entity');
                data.addColumn('string', 'ParentEntity');
                data.addColumn('string', 'ToolTip');
                for (var i = 0; i < r.d.length; i++) {
                    var memberId = r.d[i][0].toString();
                    var memberName = r.d[i][1];
                    var parentId = r.d[i][2] != null ? r.d[i][2].toString() : '';
                    data.addRows([[{
                        v: memberId,
                        f: memberName + '<div><img src = "Pictures/' + memberId + '.jpg" /></div>'
                    }, parentId, memberName]]);
                }
                var chart = new google.visualization.OrgChart($("#chart")[0]);
                chart.draw(data, { allowHtml: true });

                google.visualization.events.addListener(chart, 'select', function () {
                    $('#dvDetails').empty();
                    var selection = chart.getSelection();
                    if (selection.length > 0) {
                        var id = data.getValue(selection[0].row, 0);
                        $.ajax({
                            type: "POST",
                            url: "Default.aspx/GetDetails",
                            data: '{memberId:' + id + '}',
                            contentType: "application/json; charset=utf-8",
                            dataType: "json",
                            success: function (r) {
                                $('#dvDetails').append("<b>Name</b><br/>");
                                $.each(r.d, function (index, item) {
                                    $('#dvDetails').append(item[1] + "<br/>")
                                });
                            },
                            error: function (r) {
                                alert(r.responseText);
                            }
                        });
                    }
                });
            },
            error: function (r) {
                alert(r.responseText);
            }
        });
    }
</script>
<div id="chart">
</div>
<hr />
<div id="dvDetails"></div>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<object> GetChartData()
{
    List<object> chartData = new List<object>();
    string query = "SELECT MemberId, Name, ParentId FROM FamilyHierarchy";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    chartData.Add(new object[]
                    {
                        sdr["MemberId"], sdr["Name"], sdr["ParentId"]
                    });
                }
            }
            con.Close();
            return chartData;
        }
    }
}

[WebMethod]
public static List<object> GetDetails(int memberId)
{
    List<object> memberData = new List<object>();
    string query = "SELECT MemberId, Name, ParentId FROM FamilyHierarchy WHERE MemberId = @Id";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Id", memberId);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    memberData.Add(new object[]
                    {
                        sdr["MemberId"], sdr["Name"], sdr["ParentId"]
                    });
                }
            }
            con.Close();

            return memberData;
        }
    }
}

VB.Net

<WebMethod>
Public Shared Function GetChartData() As List(Of Object)
    Dim chartData As List(Of Object) = New List(Of Object)()
    Dim query As String = "SELECT MemberId, Name, ParentId FROM FamilyHierarchy"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    chartData.Add(New Object() {sdr("MemberId"), sdr("Name"), sdr("ParentId")})
                End While
            End Using
            con.Close()

            Return chartData
        End Using
    End Using
End Function

<WebMethod>
Public Shared Function GetDetails(ByVal memberId As Integer) As List(Of Object)
    Dim memberData As List(Of Object) = New List(Of Object)()
    Dim query As String = "SELECT MemberId, Name, ParentId FROM FamilyHierarchy WHERE MemberId = @Id"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Id", memberId)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    memberData.Add(New Object() {sdr("MemberId"), sdr("Name"), sdr("ParentId")})
                End While
            End Using
            con.Close()

            Return memberData
        End Using
    End Using
End Function

Screenshot