Hi,
Here I have created sample that will help you out.
HTML
<div>
From :
<asp:DropDownList ID="ddlFromMonths" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlFromYears" runat="server" OnSelectedIndexChanged="FromDateChanged"
AutoPostBack="true">
</asp:DropDownList>
To:
<asp:DropDownList ID="ddlToMonths" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlToYears" runat="server">
</asp:DropDownList>
<br />
<asp:Button ID="Button1" Text="Submit" runat="server" ValidationGroup="val1" OnClick="GetChart" />
<asp:Chart ID="Chart1" runat="server" Height="300px" Width="600px" Palette="Berry">
<Series>
<asp:Series Name="Series1" YValueMembers="OrderCount" XValueMember="Month" BorderWidth="1"
IsValueShownAsLabel="True" ChartType="StackedColumn">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1">
<AxisX IsStartedFromZero="True" Title="Mese" TitleFont="Segoe UI, 10pt, style=Bold"
IsLabelAutoFit="False" LineColor="Gray" Minimum="0" Maximum="12">
<MajorGrid LineColor="LightGray" />
<LabelStyle Font="Segoe UI, 9pt" />
</AxisX>
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
</div>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
PopulateMonths(ddlFromMonths);
PopulateMonths(ddlToMonths);
PopulateYears(ddlFromYears);
PopulateYears(ddlToYears);
}
}
protected void FromDateChanged(object sender, EventArgs e)
{
ddlToYears.ClearSelection();
ddlToYears.Items.FindByValue(ddlFromYears.SelectedItem.Value).Selected = true;
}
private void PopulateMonths(DropDownList dropDownList)
{
var months = DateTimeFormatInfo.CurrentInfo.MonthNames.Select((m, i) => new { Month = m, Value = i + 1 }).Take(DateTimeFormatInfo.CurrentInfo.MonthNames.Length - 1);
dropDownList.DataSource = months;
dropDownList.DataTextField = "Month";
dropDownList.DataValueField = "Value";
dropDownList.DataBind();
dropDownList.Items.Insert(0, new ListItem("Select", "0"));
}
private void PopulateYears(DropDownList ddl)
{
var result = Enumerable.Range(1990, DateTime.Now.Year - 1990 + 1);
ddl.DataSource = result;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem("Select", "0"));
}
protected void GetChart(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("GetChartData", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FromMonth", ddlFromMonths.SelectedItem.Value);
cmd.Parameters.AddWithValue("@FromYear", ddlFromYears.SelectedItem.Value);
cmd.Parameters.AddWithValue("@ToMonth", ddlToMonths.SelectedItem.Value);
cmd.Parameters.AddWithValue("@ToYear", ddlToYears.SelectedItem.Value);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dt);
}
}
}
var data = from row in dt.AsEnumerable()
select new
{
OrderCount = row["OrderCount"],
OrderMonth = row["OrderMonth"],
Year = row["OrderYear"]
};
var months = DateTimeFormatInfo.CurrentInfo.MonthNames.Select((m, i) => new { Month = m, Value = i + 1 }).Take(DateTimeFormatInfo.CurrentInfo.MonthNames.Length - 1);
var result = from month in months
join d in data
on month.Value equals d.OrderMonth into g
from d in g.DefaultIfEmpty()
select new MonthChart
{
OrderMonth = month.Value,
OrderCount = d == null ? string.Empty : d.OrderCount.ToString(),
Month = string.Format(d == null ? "{0}" : "{0} - {1}", month.Month, d == null ? string.Empty : d.Year.ToString())
};
Chart1.DataSource = result;
Chart1.ChartAreas[0].AxisX.Interval = 1;
}
MonthChart Class
class MonthChart
{
public int OrderMonth { get; set; }
public string OrderCount { get; set; }
public string Month { get; set; }
public int Year { get; set; }
}
SQL
-- GetChartData 1,1997,12,1997
CREATE PROCEDURE [dbo].[GetChartData]
@FromMonth INT = NULL,
@FromYear INT,
@ToMonth INT = NULL,
@ToYear INT
AS
BEGIN
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SET @FromDate = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(YEAR,@FromYear - 1900,0))+ @FromMonth -1,0)
SET @ToDate = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(YEAR,@ToYear - 1900,0))+ @ToMonth -1,0)
SELECT COUNT(OrderId) OrderCount, DATEPART(MONTH,OrderDate) as OrderMonth,DATEPART(YEAR,OrderDate) OrderYear
FROM Orders
WHERE OrderDate >= @FromDate AND OrderDate <= @ToDate
GROUP BY
DATEPART(MONTH,OrderDate),DATEPART(YEAR,OrderDate)
END
Screenshot