In this article I will explain with an example, how to
calculate Sum of Column Values in
WebGrid in
ASP.Net MVC.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Model
The Model class consists of following properties.
public class Product
{
public int OrderID { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
}
Namespaces
You will need to import the following namespaces.
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
Controller
The Controller consists of the following Action method.
Action method for handling GET operation
Inside this Action method, the records are fetched from the
Orders and
Product Tables using
SqlDataReader and then using
WHILE Loop, the records are copied into the Generic List collection of
Product class objects.
Finally, the Generic List collection of Product class objects is returned to the View.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
string sql = "SELECT TOP 10 OrderID,";
sql += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName,";
sql += "(Quantity * UnitPrice) Price";
sql += " FROM [Order Details] details";
sql += " ORDER BY OrderID";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
List<Product> products = new List<Product>();
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
products.Add(new Product
{
OrderID = Convert.ToInt32(sdr["OrderID"]),
ProductName = sdr["ProductName"].ToString(),
Price = Convert.ToDecimal(sdr["Price"])
});
}
}
con.Close();
return View(products);
}
}
}
}
View
HTML Markup
Inside the View, in the very first line the Product class is declared as Model for the View.
The
WebGrid is initialized with the Model.
The
WebGrid is created using the
GetHtml method with the following parameters.
htmlAttributes – It is used to set the
HTML attributes to the
HTML Table generated by
WebGrid such as id, name, class, etc.
columns – It is used to specify the columns to be displayed in
WebGrid and it also used to set specific
Header Text for the columns.
The last column of
WebGrid will display the
Price hence it is formatted into two
Decimal places using the
ToString(“N2”) function.
Below the
WebGrid, an
HTML Table has been added which will act as Footer for the
WebGrid. Inside the
HTML Table, the Grand Total i.e. the Sum of the
Price of all the Products is calculated using
Lambda Expression and displayed.
@using Calculate_Total_WebGrid_MVC.Models
@model List<Product>
@{
Layout = null;
WebGrid webGrid = new WebGrid(source: Model, canSort: false);
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<div style="width:300px">
@webGrid.GetHtml(
htmlAttributes:new { @id = "WebGrid", @class = "Grid" },
columns: webGrid.Columns(
webGrid.Column("OrderID", "Order ID"),
webGrid.Column("ProductName", "Product Name"),
webGrid.Column("Price", "Price",
format: @<text>@item.Price.ToString("N2")</text>)))
<table class="Grid" style="border-collapse:collapse; width:100%">
<tfoot>
<tr>
<td style="width:250px" align="right">Total</td>
<td>@Model.Sum(p => p.Price).ToString("N2")</td>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
Screenshot
![ASP.Net MVC WebGrid: Calculate Sum of Column Values]()
Downloads
