I believe LINQ is a very nice advancement in creating database oriented applications as it allows us to separate application logic from the database. I have been using LINQ to SQL in my recent reporting application and have used various queries with ease.
With the power of Anonymous Types, I was able to create a number of reports with different grouping senarios. Anonymous Types as defined in the C# programming guide:
Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to first explicitly define a type. The type name is generated by the compiler and is not available at the source code level. The type of the properties is inferred by the compiler.
Let’s look at a few ways of Grouping using Anonymous Types. Before we start, displayed below is the sample data used in the queries below:
Simple Group by Anonymous Type – Grouping by Month and Year in a Date
The purpose of this query is to group transactions in the table and retrieve a list of unique months and years in a simple list.
var months = from t in db.TransactionData
group t by new { month = t.Date.Month, year = t.Date.Year } into d
select new { t.Key.month, t.Key.year };
Group by Database Column and then by Anonymous Type – Grouping by Expense Category and Monthly Total
The purpose of this query is to group all database records by Category first and then display a Total Expense based on the month in a simple list.
var categoryExpense = from t in db.TransactionData
group t by t.Category into g
select new
{
Category = g.Key,
Items = from i in g
group i by new { month = i.Date.Month, year = i.Date.Year } into d
select new { Date = String.Format("{0}/{1}", d.Key.month, d.Key.year ), Amt = d.Sum(s => s.Amount) }
};
Group by Anonymous Type and then by Database Column – Grouping by Month and then by Expense Category and Category Total
The purpose of this query is the opposite of the group query 2. We will first group by the Month and then group by the Category Total:
var monthlyExpenses = from t in db.TransactionData
group t by new { month = y.Date.Month, year = y.Date.Year } into g
select new
{
Month = String.Format("{0}/{1}", g.Key.month, g.Key.year),
Items = from i in g
group i by new { i.Category } into d
select new
{
Amt = d.Sum(s => s.Amount)
}
};
With SQL you can seamlessly do all of the above on XML, CSV files or .NET Objects as well.
Recommend Resources to Learn more about LINQ to SQL
I had been looking for a light weight charting library that I could use with my ASP.NET application for a while now. There are a number of charting libraries with jQuery but I found the jQuery wrapper for Google Charts by Massimiliano Balestrieri to be efficient and simple to use.
In this tutorial I will show you how to integrate jQuery Google Charts 1.0 with your ASP.NET application.
Let us start by downloading the libraries required for this to work. We need two JavaScript files to be downloaded:
- jQuery 1.3.2 Minified Version
- jgcharts Library
Next is to create or open an existing ASP.NET web page that will display the chart, and include these libraries in the HEAD section of the page.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Chart.aspx.cs" Inherits="Chart" %>
<head runat="server">
<title>jQuery Google Chart Demo</title>
<script src="jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="jgcharts.pack.js" type="text/javascript"></script>
</head>
<body>
<form id="dform" runat="server">
<div></div>
</form>
</body>
</html>
Within the BODY tags of this page, Let us create two DIV’s, one to display the chart (bar_chart) and another one to display the data (bar_chart_data). A Hidden field is also defined that will hold the JavaScript Array of the GridView data. This will be used by the Charting Library on the client side to display the Bar Chart.
<body>
<form id="dform" runat="server">
<h4>Income and Expense Overview</h4>
<div id="bar_chart"></div>
<div id="bar_chart_data">
<asp:HiddenField ID="hidChartData" runat="server" />
<asp:GridView ID="grvIncomeExp" runat="server">
<Columns>
<asp:BoundField DataField="Month" HeaderText="Month" DataFormatString="{0:c}" />
<asp:BoundField DataField="Income" HeaderText="Income" DataFormatString="{0:c}" />
<asp:BoundField DataField="Expense" HeaderText="Expense" DataFormatString="{0:c}" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
Within the HEAD tag of this page, you will need to create a jQuery call to initiate the Charting api’s and create graph based on data available in “hidChartData” hidden field.
<head runat="server">
<title>jQuery Google Chart Demo</title>
<script src="JQuery/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="JQuery/jgcharts.pack.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function()
{
var api = new jGCharts.Api();
var opt =
{
data : eval($("[id$='hidChartData']").val()),
axis_labels : ['01','02','03','04','05','06','07','08','09','10','11','12'],
legend : ['Income', 'Expense'],
size : '600x335',
bar_width : 15
};
jQuery('<img>').attr('src', api.make(opt)).appendTo("#bar_chart");
});
</script>
</head>
f you now look at the page in the browser – you should see a blank bar chart without any data in it. This is because we have not bound the data to the Hidden Field (hidChartData) and the GridView.
Lets now look add a Page_Load function in the code behind. This event handler will bind data to the GridView and assign the value to the HiddenField with a JavaScript array of data.
protected void Page_Load(object sender, EventArgs e)
{
DataTable dTable = GetSampleData();
grvIncomeExp.DataSource = dTable;
grvIncomeExp.DataBind();
ArrayList chartData = new ArrayList();
foreach (DataRow dr in dTable.Rows)
{
chartData.Add(String.Format("[{0}, {1}]", dr[1], dr[2].ToString().Replace("-", "")));
}
//Convert .NET Array to JS Array
string ReturnValue = String.Empty;
for (int i = 0; i < chartData.Count; i++)
{
ReturnValue += chartData[i];
if (i != chartData.Count - 1)
ReturnValue += ",";
}
//Data is returned in the following format:
//[[1000, 3003.55],[1000, 72.65],[1000, 760.89],[1000, 354.55],[1000, 180.52],[1000, 408.54],[0, 0],[0, 0],[0, 0],[0, 0],[0, 0],[0, 0]]
hidChartData.Value = String.Format("[{0}]", ReturnValue);
}
This will create the chart displayed in the first Image. NOTE: I have removed all CSS styles to make the code more readable.
In one of my recent project, I had to retrieve Minimum and Maximum date from the result set using LINQ to SQL. I was really surprised how easy it was:
//Retrieve Minimum Date
var MinDate = (from d in dataRows select d.Date).Min();
//Retrieve Maximum Date
var MaxDate = (from d in dataRows select d.Date).Max();
In its simplest form, all you have to do is have .Min() or .Max() at the end of your LINQ query. That’s it.