Charts in asp.net with C# using Database

Creating Charts in ASP.Net Application with C# using SQL Server ....

Column Chart

in .aspx page

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">   

    <script type="text/javascript">

        google.load('visualization', '1', { packages: ['corechart'] });

        google.load("visualization", '1', { packages: ["columnchart"] });

    </script>

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">

       <div id="ProductCurrentStatusChart"></div>

<script type="text/javascript">

    var pageUrl = '<%=ResolveUrl("~/ProductReport.aspx")%>'; //This page path only

         $(document).ready(function () {

            drawProductSummary();

        });

function drawProductSummary() {

//Ajax Calling to this page .cs Web methode

            $.ajax({

                type: "POST",

                url: pageUrl + '/GetChartProductSummary',

                data: '{ProductId: ' + $('#<%=ddlProduct.ClientID%>').val() + '}',

                contentType: "application/json; charset=utf-8",

                dataType: "json",

                success: drawProductSummaryCallBack

            });

        }

        function drawProductSummaryCallBack(prodList) {

            var data = new google.visualization.DataTable();

            var Prod = prodList.d[0];

            data.addColumn('string', 'ProducName');

            data.addColumn('number', 'Production');

            data.addColumn('number', 'Despatches');

            data.addColumn('number', 'Available');

            //data.addColumn('number', 'Commited');

            data.addRow([Prod.productName

                            ,Prod.currentProduction

                            ,Prod.currentDispatche

                            ,Prod.Available

                            //,Prod.Commited

                        ]);

            var PrdOptions = {

                width: 550,

                height: 400,

                legend: 'bottom',

                is3D: true,

                title: 'Product Performance',

                axisFontSize: 13, //'automatic'

                enableTooltip: true,

                legendFontSize: 14,

                focusBorderColor: { stroke: 'black', fill: '#eee', strokeSize: 1 }

                            };

            var chart = new google.visualization.ColumnChart(document.getElementById('ProductCurrentStatusChart'));

            chart.draw(data, PrdOptions);

            

        }

</asp:Content>

in .CS page

 

Create one web method and write the sql query and send the data from ArrayList to Ajax methode

 

public partial class _ProductReport : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

       

    }

[System.Web.Services.WebMethod] // For Tab 1

    public static System.Collections.ArrayList GetChartProductSummary(int ProductId)

    {

        System.Collections.ArrayList list = new System.Collections.ArrayList();

        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        String strQuery = "SELECT Id,Name,ISNULL(PR.Production,0)Production,ISNULL(DS.Dispatche,0)Dispatche,ISNULL(CurrentProduction,0) Available,ISNULL(OS.Commited,0) Commited From [Products]P LEFT JOIN(SELECT ProductId, ISNULL(sum(OutstandingQuantity),0) Commited from [Customers] GROUP BY ProductId) OS on P.Id=OS.ProductId LEFT JOIN (SELECT Productid, ISNULL(SUM(Quantity),0) Production From [Productions] group by ProductId) PR ON P.Id=PR.ProductId LEFT JOIN (SELECT Productid, ISNULL(SUM(Quantity),0) Dispatche FROM [Dispatches] GROUP BY ProductId) DS ON P.Id=DS.ProductId WHERE P.ParentId is not null and P.Id=" + ProductId;

        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strConnString))

        {

            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())

            {

                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText = strQuery;

                cmd.Connection = con;

                con.Open();

                System.Data.SqlClient.SqlDataReader prodReader = cmd.ExecuteReader();

                while (prodReader.Read())

                {

                    var prod = new

                    {

                        productId = prodReader["id"],

                        productName = prodReader["Name"],

                        currentProduction = prodReader["Production"],

                        currentDispatche = prodReader["Dispatche"],

                        Available = prodReader["Available"],

                        Commited = prodReader["Commited"]

                    };

                    list.Add(prod);

                }

                con.Close();

            }

        }

        return list;

    }

}