Hi Waghmare,
Check this example. Now please take its reference and correct your code.
SQL
CREATE PROCEDURE GetCarsYearWiseSale
@Year INT = NULL
AS
BEGIN
DECLARE @tblSell AS TABLE (
Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Year] INT NULL,
Car VARCHAR(50) NULL,
Total INT NULL
)
INSERT INTO @tblSell VALUES(2016,'Hyundai',530)
INSERT INTO @tblSell VALUES(2016,'Scoda',465)
INSERT INTO @tblSell VALUES(2016,'Tata',680)
INSERT INTO @tblSell VALUES(2016,'Mahindra',500)
INSERT INTO @tblSell VALUES(2017,'Hyundai',600)
INSERT INTO @tblSell VALUES(2017,'Scoda',450)
INSERT INTO @tblSell VALUES(2017,'Tata',608)
INSERT INTO @tblSell VALUES(2017,'Mahindra',670)
INSERT INTO @tblSell VALUES(2018,'Hyundai',700)
INSERT INTO @tblSell VALUES(2018,'Scoda',300)
SELECT [Year],ISNULL([Hyundai],0) [Hyundai],ISNULL([Scoda],0) [Scoda],ISNULL([Tata],0) [Tata],ISNULL([Mahindra],0) [Mahindra]
FROM (SELECT [Year],Car,Sum(Total)Total
FROM @tblSell
GROUP BY [Year],Car) AS s
PIVOT (SUM(Total)
FOR [Car] IN ([Hyundai],[Scoda],[Tata],[Mahindra])
)AS pvt
WHERE [Year] = @Year OR @Year IS NULL
END
HTML
Year:<asp:DropDownList ID="ddlYear" runat="server">
<asp:ListItem Text="Select Year" Value="0" />
<asp:ListItem Text="2016" Value="2016" />
<asp:ListItem Text="2017" Value="2017" />
<asp:ListItem Text="2018" Value="2018" />
</asp:DropDownList>
<br />
<div id="dvChart">
</div>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="https://code.highcharts.com/highcharts.js"></script>
<script type="text/javascript" src="https://code.highcharts.com/modules/exporting.js"></script>
<script type="text/javascript">
$(function () {
$('[id*=ddlYear]').change(function () {
var year = $(this).find('option:selected').val();
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetCarDetails",
data: "{year:" + year + "}",
dataType: "json",
success: function (response) {
response = response.d;
var series = []; var year = []; var Hyundai = []; var Scoda = []; var Tata = [];
var Mahindra = [];
for (var i = 0; i < response.length; i++) {
year.push(response[i].Year);
Hyundai.push(response[i].Hyundai);
Scoda.push(response[i].Scoda);
Tata.push(response[i].Tata);
Mahindra.push(response[i].Mahindra);
}
series.push(
{ name: 'Hyundai', data: Hyundai },
{ name: 'Scoda', data: Scoda },
{ name: 'Tata', data: Tata },
{ name: 'Mahindra', data: Mahindra });
DrawChart(year, series);
},
error: function (xhr) {
alert('Request Status: ' + xhr.status + ' Status Text: ' + xhr.statusText + ' ' + xhr.responseText);
}
});
});
});
function DrawChart(year, series) {
$('[id*=dvChart]').highcharts({
chart: { type: 'column' },
title: { text: 'Cars year wise sale' },
xAxis: {
categories: year,
labels: {
style: { color: 'black', fontWeight: 'bold', fontSize: '14px' }
}
},
legend: {
enabled: true,
floating: false,
verticalAlign: 'xbottom',
align: 'right',
layout: 'vertical',
verticalAlign: 'middle',
itemStyle: { fontSize: '15px', font: '15pt Trebuchet MS, Verdana, sans-serif', color: '#0000FF' }
},
yAxis: {
min: 0,
title: { text: 'Total' },
labels: {
style: { color: 'black', fontWeight: 'bold', fontSize: '12px' }
},
stackLabels: {
enabled: true,
style: { fontWeight: 'bold', fontSize: '15px', color: 'black' }
}
},
tooltip: {
formatter: function () {
return '<b>' + this.x + '</b><br/>' + this.series.name + ': ' + this.y + '<br/>' + 'Total: ' + this.point.stackTotal;
}
},
plotOptions: {
column: {
stacking: 'normal',
dataLabels: {
enabled: true,
color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white',
style: { textShadow: '0 0 3px black' }
}
}
},
series: series
});
}
</script>
Namespaces
C#
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
VB.Net
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration
Code
C#
[WebMethod]
public static List<CarDetails> GetCarDetails(int year)
{
List<CarDetails> carDetails = new List<CarDetails>();
string str = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetCarsYearWiseSale";
if (year > 0)
{
cmd.Parameters.AddWithValue("@Year", year);
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
carDetails.Add(new CarDetails
{
Year = dr["Year"].ToString(),
Hyundai = Convert.ToInt32(dr["Hyundai"]),
Scoda = Convert.ToInt32(dr["Scoda"]),
Tata = Convert.ToInt32(dr["Tata"]),
Mahindra = Convert.ToInt32(dr["Mahindra"])
});
}
}
}
return carDetails;
}
public class CarDetails
{
public string Year { get; set; }
public int Hyundai { get; set; }
public int Scoda { get; set; }
public int Tata { get; set; }
public int Mahindra { get; set; }
}
VB.Net
<WebMethod>
Public Shared Function GetCarDetails(ByVal year As Integer) As List(Of CarDetails)
Dim carDetails As List(Of CarDetails) = New List(Of CarDetails)()
Dim str As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(str)
Using cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "GetCarsYearWiseSale"
If year > 0 Then
cmd.Parameters.AddWithValue("@Year", year)
End If
Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
For Each dr As DataRow In dt.Rows
carDetails.Add(New CarDetails With {
.Year = dr("Year").ToString(),
.Hyundai = Convert.ToInt32(dr("Hyundai")),
.Scoda = Convert.ToInt32(dr("Scoda")),
.Tata = Convert.ToInt32(dr("Tata")),
.Mahindra = Convert.ToInt32(dr("Mahindra"))
})
Next
End Using
End Using
Return carDetails
End Function
Public Class CarDetails
Public Property Year As String
Public Property Hyundai As Integer
Public Property Scoda As Integer
Public Property Tata As Integer
Public Property Mahindra As Integer
End Class
Screenshot
