If any possibility connects database in amcharts (pie Chart) in asp.net?

3k views Asked by At

<---- This below code used for PieChart.aspx.cs ---->

  protected void Page_Load(object sender, EventArgs e)
    {
    }
    public chart ChartUser(string UserId)
    {
        chart c = new chart();
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=emp;Integrated Security=True");
        // SqlDataAdapter da = new SqlDataAdapter("select * from ChartPie where id= UserId + "'", con);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from ChartPie where id='" + UserId + "'", con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            c.UserId = dr["UserId"].ToString();
            c.Language = dr["Language"].ToString();
            c.percentage = dr["percentage"].ToString();
        }

        return c;
    }
    public class chart
    {
        public string UserId { get; set; }
        public string Language { get; set; }
        public string percentage { get; set; }

    }
}

<---- give below code used for script file ----->

var chart = AmCharts.makeChart("chartdiv", {
            "type": "pie",
            "theme": "none",
            "legend": {
                "markerType": "circle",
                "position": "right",
                "marginRight": 80,
                "autoMargins": false
            },
            "dataProvider": [{
                "Language": "Tamil",
                "percentage": 50
            }, {
                "Language": "English",
                "percentage": 40
            }, {
                "Language": "Hindi",
                "percentage": 30
            }],
            "valueField": "percentage",
            "titleField": "Language",
            colorField: "color",
            "balloonText": "[[title]]<br><span style='font-size:14px'><b>[[value]]</b> ([[percents]]%)</span>",
            "exportConfig": {
                "menuTop": "0px",
                "menuItems": [{
                    "icon": '/lib/3/images/export.png',
                    "format": 'png'
                }]
            }
        });

Kindly tell me how can I get value from database?

3

There are 3 answers

0
Saravana Kumar On

Modify the script like this. Send the Ajax request to the asp.net page and assign the result to the AmChars dataprovider.

<script type="text/javascript">
    $(document).ready(function () {

        $.ajax({

            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "PieChart.aspx/ChartUser",
            data: "{UserId : '123'}",
            dataType: "json",
            success: function (data) {

            AmCharts.makeChart("chartdiv", {
            "type": "pie",
            "theme": "none",
            "legend": {
                "markerType": "circle",
                "position": "right",
                "marginRight": 80,
                "autoMargins": false
            },
            "dataProvider": [data.chart],
            "valueField": "percentage",
            "titleField": "Language",
            colorField: "color",
            "balloonText": "[[title]]<br><span style='font-size:14px'><b>[[value]]</b> ([[percents]]%)</span>",
            "exportConfig": {
                "menuTop": "0px",
                "menuItems": [{
                    "icon": '/lib/3/images/export.png',
                    "format": 'png'
                }]
            }
           }); 
            },
            error: function (result) {
                alert("Error");
            }
        });
    });
</script>
0
pavan kumar On

Reverting data from database using Amchats and Asp.net. Here in this Example i am using Line chatsIt worked for me enter image description here

 <script src="https://www.amcharts.com/lib/3/amcharts.js"></script>
<script src="https://www.amcharts.com/lib/3/serial.js"></script>
<script src="https://www.amcharts.com/lib/3/themes/light.js"></script>
    <script src="JSfiles/jquery-1.10.2.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var chartDataResults = new Array();
            $.ajax({
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json',
                url: 'sampleLine.aspx/GetDataonload',
                data: {},
                success:
                    function (response) {

                        drawVisualization(response.d);
                    }
            });
            function drawVisualization(dataValues) {
                for (var i = 0; i < dataValues.length; i++) {
                    var dataitem = dataValues[i];
                    var date = dataitem.date;

                    var cpa = dataitem.cpacount;
                    //alert(cpa);
                    var cpi = dataitem.cpicount;
                    var cpm = dataitem.cpmcount;
                    chartDataResults.push({
                        date: date,
                        cpacount: cpa,
                        cpicount: cpi,
                        cpmcount: cpm
                    });

                    var chart = AmCharts.makeChart("chartdiv", {
                        "type": "serial",
                        "theme": "light",
                        "marginRight": 30,
                        "legend": {
                            "equalWidths": false,
                            "periodValueText": "total: [[value.sum]]",
                            "position": "top",
                            "valueAlign": "left",
                            "valueWidth": 100
                        },
                        "dataProvider":chartDataResults,
                        //"dataProvider": [{
                        //    "year": 1994,
                        //    "cars": 1587,
                        //    "motorcycles": 650,
                        //    "bicycles": 121
                        //}],
                        "valueAxes": [{
                            "stackType": "regular",
                            "gridAlpha": 0.07,
                            "position": "left",
                            "title": "Traffic incidents"
                        }],
                        "graphs": [{
                            "balloonText": "<img src='images/icons-02.jpg' style='vertical-align:bottom; margin-right: 10px; width:28px; height:21px;'><span style='font-size:14px; color:#000000;'><b>[[value]]</b></span>",
                            "fillAlphas": 0.6,
                            "hidden": true,
                            "lineAlpha": 0.4,
                            "title": "CPA Count",
                            "valueField": "cpacount"
                        }, {
                            "balloonText": "<img src='images/icons-03.jpg' style='vertical-align:bottom; margin-right: 10px; width:28px; height:21px;'><span style='font-size:14px; color:#000000;'><b>[[value]]</b></span>",
                            "fillAlphas": 0.6,
                            "lineAlpha": 0.4,
                            "title": "CPI Count",
                            "valueField": "cpicount"
                        }, {
                            "balloonText": "<img src='images/icons-04.jpg' style='vertical-align:bottom; margin-right: 10px; width:28px; height:21px;'><span style='font-size:14px; color:#000000;'><b>[[value]]</b></span>",
                            "fillAlphas": 0.6,
                            "lineAlpha": 0.4,
                            "title": "CPM Count",
                            "valueField": "cpmcount"
                        }],
                        "plotAreaBorderAlpha": 0,
                        "marginTop": 10,
                        "marginLeft": 0,
                        "marginBottom": 0,
                        "chartScrollbar": {},
                        "chartCursor": {
                            "cursorAlpha": 0
                        },
                        "categoryField": "date",
                        "categoryAxis": {
                            "startOnAxis": true,
                            "axisColor": "#DADADA",
                            "gridAlpha": 0.07,
                            "title": "Year",
                            "guides": [{
                                category: "2001",
                                toCategory: "2016",
                                lineColor: "#CC0000",
                                lineAlpha: 1,
                                fillAlpha: 0.2,
                                fillColor: "#CC0000",
                                dashLength: 2,
                                inside: true,
                                labelRotation: 90,
                                label: "Increased Count"
                            }, {
                                category: "2016",
                                lineColor: "#CC0000",
                                lineAlpha: 1,
                                dashLength: 2,
                                inside: true,
                                labelRotation: 90,
                                label: "Count"
                            }]
                        },
                        "export": {
                            "enabled": true
                        }
                    });
                }
            }
        });
    </script>

C# code:

 [WebMethod]
        public static List<ChartDetails> GetDataonload()
        {
            string Constring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
            using (SqlConnection con = new SqlConnection(Constring))
            {

                List<ChartDetails> dataList = new List<ChartDetails>();
                SqlCommand cmd = new SqlCommand("Usp_TotalcountCPA_new_usingfunction", con);
                cmd.CommandTimeout = 50;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iAdvertiserid", "1000120");
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                con.Close();

                foreach (DataRow dtrow in dt.Rows)
                {
                    ChartDetails details = new ChartDetails();
                    string date = dtrow[4].ToString();
                    details.date = date.Substring(3, 7);
                    details.cpacount = dtrow[7].ToString();
                    details.cpicount = dtrow[10].ToString();
                    details.cpmcount = dtrow[14].ToString();
                    details.cvpcount = dtrow[16].ToString();
                    dataList.Add(details);
                }
                return dataList;
            }
        }
        public class ChartDetails
        {
            public string date { get; set; }
            public string cpacount { get; set; }
            public string cpicount { get; set; }
            public string cpmcount { get; set; }
            public string cvpcount { get; set; }
            // public string CountryCode { get; set; }
        }
0
pavan kumar On

enter image description hereRetrieving data from database using pi charts in Amcharts

<script src="https://www.amcharts.com/lib/3/amcharts.js"></script>
<script src="https://www.amcharts.com/lib/3/pie.js"></script>
<script src="https://www.amcharts.com/lib/3/themes/light.js"></script>
 <script src="JSfiles/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        var chartDataResults = new Array();
        $.ajax({
            type: 'POST',
            dataType: 'json',
            contentType: 'application/json',
            url: 'pichartssimple.aspx/GetDataonload',
            data: {},
            success: function (response) {
                drawchart(response.d); // calling method
            },

            error: function () {
                alert("Error:Something went wrong.Contact the Adminstrator");
                alert(response);
            }
        });

        function drawchart(dataValues) {

            for (var i = 0; i < dataValues.length; i++) {
                // data.addRow([dataValues[i].Accounts, dataValues[i].Accountvalues]);
                var dataitem = dataValues[i];

                var Account = dataitem.Accounts;

                var Accountvalues = dataitem.Accountvalues;
                // alert(Accountvalues);

                chartDataResults.push({
                    Account: Account,
                    Accountvalues: Accountvalues
                });

                var chart = AmCharts.makeChart("chartdiv", {
                    "type": "pie",
                    "theme": "light",
                    "dataProvider": chartDataResults,
                    "valueField": "Accountvalues",
                    "titleField": "Account",
                    "balloon": {
                        "fixedPosition": true
                    },
                    "export": {
                        "enabled": true
                    }
                });
            }

            // Instantiate and draw our chart, passing in some option
        }
    });
 </script>
C#code
 [WebMethod(EnableSession = true)]
        public static List<Chatdata> GetDataonload()
        {
            List<Chatdata> dataList = new List<Chatdata>();
            using (SqlConnection con = new SqlConnection("Data Source=203.115.195.52;Initial Catalog=mcom_ad_engine;Persist Security Info=True;User ID=appl;Password=mcom007;"))
            {

                //string StartDate = DateTime.Now.AddDays(-180).ToString("yyyy-MM-dd");
                string StartDate = DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd");
                string EndDate = DateTime.Now.ToString("yyyy-MM-dd");
                SqlCommand cmd = new SqlCommand("Sp_Advertiser_Monthly_payout_pichat", con);
                cmd.CommandTimeout = 50;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iAdvertiser", "1000120");
                cmd.Parameters.AddWithValue("@istartdate", StartDate);
                cmd.Parameters.AddWithValue("@ienddate", EndDate);
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                con.Close();


                foreach (DataRow dtrow in dt.Rows)
                {
                    if (dtrow[0].ToString() != "Spent")
                    {
                        Chatdata details = new Chatdata();

                        details.Accounts = dtrow[0].ToString();
                        // details.spent = Convert.ToInt64(dtrow[2].ToString());
                        if (dtrow[1].ToString().StartsWith("-"))
                        {
                            string bal = dtrow[1].ToString();
                            bal = bal.Substring(1, bal.Length - 1);
                            details.Accountvalues = Convert.ToInt64(bal);
                        }
                        else
                        {
                            details.Accountvalues = Convert.ToInt64(dtrow[1].ToString());
                        }
                        dataList.Add(details);
                    }
                }

                return dataList;
            }

        }
        public class Chatdata
        {
            public string Accounts { get; set; }
            public Int64 Accountvalues { get; set; }
        }