How to create Bar chart using HTML 5 canvas from database in asp.net mvc?

In this article I will show you how to create Bar chart using HTML 5 canvas from database in asp.net MVC c# implemented using chart.js.

It consists of two dropdownlist with list of countries and Html 5 canvas multis-series Bar chart. The chart will compare and display the order statics of two countries.  Create an object and fill it with distinct years for x-axis and y-axis create another list objects fill it with order counts of two countries for each year.

The jQuery ajax post call to a controller function GetData() and result the json response and bind the data in the chart using HTML canvas.

Database:

Here I am using Northwind database. You can download it from following link.

Download Northwind Database

Open Microsoft sql management studio and right click on the database and attach it.

Step 1: Create an ado.net entity data model using table orders and generate entity for that.

Step 2: Right clicks on the "Controllers" folder and add "BarChart" controller. Copy and paste the following code.

public class BarChartController : Controller
    {
        // GET: /LineChart/
        models db = new models();
        public ActionResult Index()
        {
            var countries = (from c in db.Orders
                             select new { c.ShipCountry }).Distinct().ToArray();
            ViewBag.ListCountry = new SelectList(countries, "shipcountry");
             var Listcountry = (from c in db.Orders
                               select new { c.ShipCountry }).Distinct().ToList();
 
            Order order = new Order();
            order.ListCountry = new SelectList(Listcountry, "shipcountry", "shipcountry");
            return View(order);
        }
        public ActionResult GetChart(string country1, string country2)
        {
            string connstring =db.Database.Connection.ConnectionString;
            List<object>chartData = new List<object>();
            string query = "SELECT DISTINCTDATEPART(YEAR, OrderDate) Year FROM Orders";
            DataTable dtYears = GetData(query,connstring);
            List<int> labels = new List<int>();
            foreach (DataRow row in dtYears.Rows)
            {
               labels.Add(Convert.ToInt32(row["Year"]));
            }
           chartData.Add(labels);
 
           query = "SELECTDATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate))TotalOrders";
           query += "FROM Orders WHERE ShipCountry = '" + country1 + "' GROUP BY DATEPART(YEAR, OrderDate)";
            DataTable dtCountry1 = GetData(query,connstring);
 
            List<int> series1 = new List<int>();
            foreach (DataRow row in dtCountry1.Rows)
            {
               series1.Add(Convert.ToInt32(row["TotalOrders"]));
            }
           chartData.Add(series1);
           query = "SELECTDATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate))TotalOrders";
           query += "FROM Orders WHERE ShipCountry = '" + country2 + "' GROUP BY DATEPART(YEAR, OrderDate)";
           DataTable dtCountry2 = GetData(query,connstring);
           List<int> series2 = new List<int>();
           foreach (DataRow row in dtCountry2.Rows)
            {
              series2.Add(Convert.ToInt32(row["TotalOrders"]));
            }
          chartData.Add(series2);
             return new JsonResult
            {
               Data = new
               {
                   success = chartData,
                   message = "Success",
               },
               JsonRequestBehavior = JsonRequestBehavior.AllowGet
            };
 
        }
 
        private static DataTable GetData(string query, string constr)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
               {
                   using (SqlDataAdapter sda = new SqlDataAdapter())
                   {
                       cmd.CommandType = CommandType.Text;
                       cmd.Connection = con;
                       sda.SelectCommand = cmd;
                       sda.Fill(dt);
                   }
               }
                return dt;
            }
        }
 
    } 

Step 3: Right click on the "Index" action method in the "BarChartController" and add "Index" view. Copy and paste the following code.

@model  MymvcApp.Models.Order
@using MymvcApp.Models;
@{
   ViewBag.Title = "Barchart using HTML 5 canvas from database";
}
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/flot/0.8.3/excanvas.js" type="text/javascript"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/1.0.2/Chart.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
       LoadChart();
        $("[id*=ddlCountries1],[id*=ddlCountries2]").bind("change", function () {
           LoadChart();
        });
    });
    function LoadChart() {
        var country1 = $("[id*=ddlCountries1]").val();
        var country2 = $("[id*=ddlCountries2]").val();
        var dataobject = {};
       dataobject.country1 = country1;
       dataobject.country2 = country2;
        $.ajax({
           type: "POST",
            url:"@Url.Action("GetChart", "BarChart", new { area = "" })",
           data: JSON.stringify(dataobject),
           contentType: "application/json;charset=utf-8",
           dataType: "json",
           success: function (r) {
                var labels = r.success[0];
                var series1 = r.success[1];
                var series2 = r.success[2];
                var data = {
                   labels: r.success[0],
                   datasets: [
                      {
                            label: country1,
                            fillColor: "#382765",
                            strokeColor: "#FCD209",
                            pointColor: "#FF8C00",
                           data: series1
                       },
                       {
                            label: country2,
                            fillColor: "#7BC225",
                            strokeColor: "#BCE8F1",
                            pointColor: "#0090CB",
                            data: series2
                        }
                   ]
               };
              $("#dvChart").html("");
                 var canvas = document.createElement('canvas');
               $("#dvChart")[0].appendChild(canvas);
 
                //Fix for IE 8
                if ($.browser.msie && $.browser.version == "8.0") {
                   G_vmlCanvasManager.initElement(canvas);
               }
                var ctx = canvas.getContext('2d');
               ctx.canvas.height = 300;
               ctx.canvas.width = 500;
                var lineChart = new Chart(ctx).Line(data, {
                   bezierCurve: false
               });
            },
           failure: function (response) {
               alert('Therewas an error.');
            }
        });
    }
</script>
<body style="border: 1px solid #DED8D8; width: 500px; height: 395px; font-family: Arial;">
    <div>
        <h2>Bar Chart inasp.net MVC</h2>
        @Html.DropDownList("ddlCountries1", Model.ListCountry)
        @Html.DropDownList("ddlCountries2", Model.ListCountry)
        <br />
        <div id="dvChart">
        </div>
    </div>

</body>

Output: