Monday, January 29, 2018

How To Read XML File Using DataTable.ReadXml()

Good afternoon friends!
In this tutorial, I will demonstrate how to read an XML file using DataTable's ReadXml() method. While reading a file using DataSet's ReadXml() method is straightforward, using the DataTable's method might cause run-time or logical bugs. One example is that when using the DataTable's ReadXml() function, the DataTable object does not contain any records or nothing at all given that fact that the method has successfully executed. To proceed, we will read an XML file below called books.xml taken from MSDN page using DataTable's ReadXml() method.
<?xml version="1.0" encoding="utf-8" ?>
<catalog>
  <book id="bk101">
    <author>Gambardella, Matthew</author>
    <title>XML Developer's Guide</title>
    <genre>Computer</genre>
    <price>44.95</price>
    <publish_date>2000-10-01</publish_date>
    <description>
      An in-depth look at creating applications
      with XML.
    </description>
  </book>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-12-16</publish_date>
    <description>
      A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.
    </description>
  </book>
  <book id="bk103">
    <author>Corets, Eva</author>
    <title>Maeve Ascendant</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2000-11-17</publish_date>
    <description>
      After the collapse of a nanotechnology
      society in England, the young survivors lay the
      foundation for a new society.
    </description>
  </book>
  <book id="bk104">
    <author>Corets, Eva</author>
    <title>Oberon's Legacy</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2001-03-10</publish_date>
    <description>
      In post-apocalypse England, the mysterious
      agent known only as Oberon helps to create a new life
      for the inhabitants of London. Sequel to Maeve
      Ascendant.
    </description>
  </book>
  <book id="bk105">
    <author>Corets, Eva</author>
    <title>The Sundered Grail</title>
    <genre>Fantasy</genre>
    <price>5.95</price>
    <publish_date>2001-09-10</publish_date>
    <description>
      The two daughters of Maeve, half-sisters,
      battle one another for control of England. Sequel to
      Oberon's Legacy.
    </description>
  </book>
  <book id="bk106">
    <author>Randall, Cynthia</author>
    <title>Lover Birds</title>
    <genre>Romance</genre>
    <price>4.95</price>
    <publish_date>2000-09-02</publish_date>
    <description>
      When Carla meets Paul at an ornithology
      conference, tempers fly as feathers get ruffled.
    </description>
  </book>
  <book id="bk107">
    <author>Thurman, Paula</author>
    <title>Splish Splash</title>
    <genre>Romance</genre>
    <price>4.95</price>
    <publish_date>2000-11-02</publish_date>
    <description>
      A deep sea diver finds true love twenty
      thousand leagues beneath the sea.
    </description>
  </book>
  <book id="bk108">
    <author>Knorr, Stefan</author>
    <title>Creepy Crawlies</title>
    <genre>Horror</genre>
    <price>4.95</price>
    <publish_date>2000-12-06</publish_date>
    <description>
      An anthology of horror stories about roaches,
      centipedes, scorpions  and other insects.
    </description>
  </book>
  <book id="bk109">
    <author>Kress, Peter</author>
    <title>Paradox Lost</title>
    <genre>Science Fiction</genre>
    <price>6.95</price>
    <publish_date>2000-11-02</publish_date>
    <description>
      After an inadvertant trip through a Heisenberg
      Uncertainty Device, James Salway discovers the problems
      of being quantum.
    </description>
  </book>
  <book id="bk110">
    <author>O'Brien, Tim</author>
    <title>Microsoft .NET: The Programming Bible</title>
    <genre>Computer</genre>
    <price>36.95</price>
    <publish_date>2000-12-09</publish_date>
    <description>
      Microsoft's .NET initiative is explored in
      detail in this deep programmer's reference.
    </description>
  </book>
  <book id="bk111">
    <author>O'Brien, Tim</author>
    <title>MSXML3: A Comprehensive Guide</title>
    <genre>Computer</genre>
    <price>36.95</price>
    <publish_date>2000-12-01</publish_date>
    <description>
      The Microsoft MSXML3 parser is covered in
      detail, with attention to XML DOM interfaces, XSLT processing,
      SAX and more.
    </description>
  </book>
  <book id="bk112">
    <author>Galos, Mike</author>
    <title>Visual Studio 7: A Comprehensive Guide</title>
    <genre>Computer</genre>
    <price>49.95</price>
    <publish_date>2001-04-16</publish_date>
    <description>
      Microsoft Visual Studio 7 is explored in depth,
      looking at how Visual Basic, Visual C++, C#, and ASP+ are
      integrated into a comprehensive development
      environment.
    </description>
  </book>
</catalog>
The first thing to do is to create a schema based from that file using Visual Studio. This is turn will generate a schema out from the books.xml file.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="catalog">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="book">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="author" type="xs:string" />
              <xs:element name="title" type="xs:string" />
              <xs:element name="genre" type="xs:string" />
              <xs:element name="price" type="xs:decimal" />
              <xs:element name="publish_date" type="xs:date" />
              <xs:element name="description" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="id" type="xs:string" use="required" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
In code-behind, create a DataTable object with name "book" that will match the node name from the XML file that holds the individual records. Next is to read the XML schema first followed by the XML file.
private void FBooks_Load(object sender, EventArgs e)
{
 string exeLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
 string xmlPath = Path.Combine(exeLocation, "books.xml");
 string xsdPath = Path.Combine(exeLocation, "books.xsd");
 DataTable books = new DataTable("book");
 books.ReadXmlSchema(xsdPath);
 books.ReadXml(xmlPath);
 dgvBooks.DataSource = books;
}
Output

Wednesday, January 24, 2018

Converting Rows To Columns with DateTime Column in MSSQL

Good evening!
An issue was brought up by a developer on how to convert rows of DateTime values into columns using MS Access DB with functionality similar to pivoting of MSSQL.The problem has an added complexity since specific portions of the DateTime will be extracted too. See original post here: Converting Columns to Rows in MS Access. The data presented is similar to a Timesheet entry wherein an employee has login/logout records. The solution presented in the thread involves usage of subquery and joins designed for MS Access Db. I modified the accepted answer to a T-SQL query using Group By, Max() function, Coalesce() and without subquery.
SELECT 
  LogInfo.fldMachineId, 
  Year(LogInfo.fldLogDate) as [Year], 
  Month(LogInfo.fldLogDate) as [Month], 
  Day(LogInfo.fldLogDate) as [Day],
  Max(Coalesce(Convert(varchar(5), T2.fldLogDate, 108),'')) as TimeIn1,
  Max(Coalesce(Convert(varchar(5), T3.fldLogDate, 108),'')) as TimeOut1, 
  Max(Coalesce(Convert(varchar(5), T4.fldLogDate, 108),'')) as TimeIn2, 
  Max(Coalesce(Convert(varchar(5), T5.fldLogDate, 108),'')) as TimeOut2
FROM LogInLogOut as LogInfo
  LEFT JOIN LogInLogOut as T2 ON (LogInfo.fldMachineId = T2.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T2.fldLogDate) AND T2.LogStatus = 1 AND DatePart(Hour, T2.fldLogDate) < 11)
  LEFT JOIN LogInLogOut as T3 ON (LogInfo.fldMachineId = T3.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T3.fldLogDate) AND T3.LogStatus = 2 AND DatePart(Hour, T3.fldLogDate) < 15)
  LEFT JOIN LogInLogOut as T4 ON (LogInfo.fldMachineId = T4.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T4.fldLogDate) AND T4.LogStatus = 1 AND DatePart(Hour, T4.fldLogDate) >= 11)
  LEFT JOIN LogInLogOut as T5 ON (LogInfo.fldMachineId = T5.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T5.fldLogDate) AND T5.LogStatus = 2 AND DatePart(Hour, T5.fldLogDate) >= 15)
Group By LogInfo.fldMachineId, 
         Year(LogInfo.fldLogDate), Month(LogInfo.fldLogDate), Day(LogInfo.fldLogDate) 
Another approach that I was thinking of will be using the partitioning logic of T-SQL. :-)

Sunday, January 21, 2018

Could not load file or assembly Microsoft.VisualStudio.ConnectedServices Version=2.0.0.0

Hello,
This issue happens when closing applications created by Visual Studio 2015 either Console, Winforms or ASP.NET. The resolution for this problem is explained here in detail Visual Studio 2015 Enterprise with Update 1 - Could not load file or assembly Microsoft.VisualStudio.ConnectedServices, Version=2.0.0.0.
Be aware that the ConnectedServices page has several versions and you might install the current package which is incorrect. Since the assembly required is 2.0.0.0, the correct package is in this page Microsoft.VisualStudio.ConnectedServices 2.0.0.

NuGet Package Manager Missing For Visual Studio 2015 Missing On Fresh Install

In the event that NuGet Package Manager Tool is not found on Visual Studio 2015 Update 1 after fresh install of the software, simply download the VSIX installer from Visual Studio MarketPlace and add it to the IDE. Here's the link of the NuGet Tool: NuGet Package Manager for Visual Studio 2015.
Cheers! :-)

Sunday, January 7, 2018

Repeater Web Server Control in ASP.NET with Bootstrap and Entity Framework

Happy New Year!
This post will illustrate on how to apply Bootstrap styling to a Repeater control given that the template used will be a Table element. To begin with, create an ASP.NET WebForm project. This application will retrieve Employee information from Northwinds database. Add an ADO.NET Entity Framework 6.0 to your project which will hook up with the Employee table. Add a WebForm page to your solution then drag a Repeater control inside the div tag below the form tag. Make sure to reference the bootstrap file in your head tag. The Repeater control will utilize the table element as it's template for displaying employee information. For the record, the employee photo will be shown on the left column, while the personal details are presented on the right column. The table also made use of Bootstrap's table css classes.
<head runat="server">
    <title></title>
    <link href="Content/bootstrap.css" rel="stylesheet" />
    <style type="text/css">
        .employeeData{
            width:800px;
            margin-left:auto;
            margin-right: auto;
            margin-top: 50px;
            margin-bottom: 50px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="rEmployees" runat="server">
            <HeaderTemplate>
                <table id="employeeData" class="employeeData table table-striped table-bordered table-bordered">
                    <tr>
                        <th>Employee Photo</th>
                        <th>Employee Details</th>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                     <tr>
                        <td>
                            <img src='Extensions/ImageHandler.ashx?EmployeeID=<%# Eval("EmployeeID") %>' />
                        </td>
                        <td>
                            <p>
                                 <asp:Label ID="lblName" runat="server" Text='<%# string.Format("{0} {1}",Eval("FirstName"), Eval("LastName")) %>' />                                
                            </p>
                            <p>
                                <asp:Label ID="lblBirthdate" runat="server" Text='<%# Eval("BirthDate") %>' />
                            </p>
                            <p>
                                <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>' />
                            </p>
                            <p>
                                <asp:Label ID="lblPhone" runat="server" Text='<%# Eval("HomePhone") %>' />
                            </p>
                        </td>
                    </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>
In the code behind, simply retrieve the records from the Employee model and bind them to the repeater control.
public partial class Repeater : System.Web.UI.Page
{
 private NorthwindEntities _context;

 public Repeater()
 {
  _context = new NorthwindEntities();
 }
 
 protected void Page_Load(object sender, EventArgs e)
 {
  if (!Page.IsPostBack)
  {
   BindRepeater();
  }
 }

 private void BindRepeater()
 {
  var model = _context.Employees.ToList();
  rEmployees.DataSource = model;
  rEmployees.DataBind();
 }
}
Output
That's it! :-)

Friday, December 22, 2017

Multiple PagedList Pager In A Single View

Good afternoon!
In a situation wherein a single page will contain multiple paging such as displaying master-details records or similar requirements, using a couple or more PagedList pager will cause logical errors such as when you click on the paging of details record to traverse to the next page, the paging of master record will move to the next record as well. In order to solve this dilemma, we need to assign a specific page variable to a PagedList object to prevent this issue from happening. In this example, I'll demonstrate showing products information from AdventureWorks database whose stocks are above or below a certain reorder point. The page model class below has two page properties namely BelowReorderPage and AboveReorderPage. And these properties are assigned to their specific PagedList object whether ProductsBelowReorder or ProductsAboveReorder.
PageModel.cs
public class PageModel
{
 public int BelowReorderPage { get; set; }
 public int AboveReorderPage { get; set; }
 public int Size { get; set; }

 public PageModel()
 {
  BelowReorderPage = 1;
  AboveReorderPage = 1;
  Size = 10;
 }
}

public class ProductPageModel
{
 public PageModel PageModel { get; set; }
 public ProductPageModel(PageModel pageModel)
 {
  ProductsBelowReorder = new PagedList<ProductViewModel>(new List<ProductViewModel>(), 1, pageModel.Size);
  ProductsAboveReorder = new PagedList<ProductViewModel>(new List<ProductViewModel>(), 1, pageModel.Size);
  PageModel = pageModel;
 }

 public IPagedList<ProductViewModel> ProductsBelowReorder { get; set; }
 public IPagedList<ProductViewModel> ProductsAboveReorder { get; set; }
}
The controller class will retrieve the records from the Product table and assign them to the PagedList properties. Note that the ProductsBelowReorder PagedList property is assigned with the BelowReorderPage page while the ProductsAboveReorder is allocated with AboveReorderPage page.
HomeController.cs
public class HomeController : Controller
{
 private AdventureWorksEntities _context;

 public HomeController()
 {
  _context = new AdventureWorksEntities();
 }
 
 // GET: Home
 public ActionResult Index(PageModel pageModel)
 {
  var productsBelowReorder = (from prod in _context.Products
         join prodInv in _context.ProductInventories
         on prod.ProductID equals prodInv.ProductID
         where prodInv.Quantity < prod.ReorderPoint
         select new ProductViewModel()
         {
          ProductID = prod.ProductID,
          ProductName = prod.Name,
          ProductNumber = prod.ProductNumber,
          ListPrice = prod.ListPrice,
          LocationID = prodInv.LocationID,
          Quantity = prodInv.Quantity,
          ReorderPoint = prod.ReorderPoint
         }).ToList();

  var productsAboveReorder = (from prod in _context.Products
         join prodInv in _context.ProductInventories
         on prod.ProductID equals prodInv.ProductID
         where prodInv.Quantity >= prod.ReorderPoint
         select new ProductViewModel()
         {
          ProductID = prod.ProductID,
          ProductName = prod.Name,
          ProductNumber = prod.ProductNumber,
          ListPrice = prod.ListPrice,
          LocationID = prodInv.LocationID,
          Quantity = prodInv.Quantity,
          ReorderPoint = prod.ReorderPoint
         }).ToList();

  var model = new ProductPageModel(pageModel)
  {
   ProductsBelowReorder = productsBelowReorder.ToPagedList(pageModel.BelowReorderPage, pageModel.Size),
   ProductsAboveReorder = productsAboveReorder.ToPagedList(pageModel.AboveReorderPage, pageModel.Size)
  };

  return View(model);
 }
}
The view will display each product information in a tabular format and is assigned with a PagedListPager helper. Each pager has been assigned with a particular model and paging options. For the products whose stocks are above the reorder point, the RouteValueDictionary parameter sets the AboveReorderPage with the current page while the BelowReorderPage is assigned with the BelowReorderPage page. The approach also applies to the products whose stocks are below the reorder point.
Index.cshtml
<div class="container">
    <div class="row table-responsive">
        <h3>Products Above ReorderPoint</h3>
        <table class="table table-bordered table-condensed table-striped">
            <thead>
                <tr>
                    <th>Product ID</th>
                    <th>Product Name</th>
                    <th>Product Number</th>
                    <th>ListPrice</th>
                    <th>Location ID</th>
                    <th>Quantity</th>
                    <th>Reorder Point</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var productAboveReorder in Model.ProductsAboveReorder)
                {
                    <tr>
                        <td>@productAboveReorder.ProductID</td>
                        <td>@productAboveReorder.ProductName</td>
                        <td>@productAboveReorder.ProductNumber</td>
                        <td>@productAboveReorder.ListPrice</td>
                        <td>@productAboveReorder.LocationID</td>
                        <td>@productAboveReorder.Quantity</td>
                        <td>@productAboveReorder.ReorderPoint</td>
                    </tr>
                }
            </tbody>
        </table>
        @Html.PagedListPager(Model.ProductsAboveReorder, page => Url.Action("Index",
                                    "Home",
                                      new RouteValueDictionary() {
                                        { "AboveReorderPage", page },
                                        { "BelowReorderPage", Model.PageModel.BelowReorderPage }
                                      }), PagedListRenderOptions.ClassicPlusFirstAndLast)
    </div>
    <div class="row table-responsive">
        <h3>Products Below ReorderPoint</h3>
        <table class="table table-bordered table-condensed table-striped">
            <thead>
                <tr>
                    <th>Product ID</th>
                    <th>Product Name</th>
                    <th>Product Number</th>
                    <th>ListPrice</th>
                    <th>Location ID</th>
                    <th>Quantity</th>
                    <th>Reorder Point</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var productBelowReorder in Model.ProductsBelowReorder)
                {
                    <tr>
                        <td>@productBelowReorder.ProductID</td>
                        <td>@productBelowReorder.ProductName</td>
                        <td>@productBelowReorder.ProductNumber</td>
                        <td>@productBelowReorder.ListPrice</td>
                        <td>@productBelowReorder.LocationID</td>
                        <td>@productBelowReorder.Quantity</td>
                        <td>@productBelowReorder.ReorderPoint</td>
                    </tr>
                }
            </tbody>
        </table>
        @Html.PagedListPager(Model.ProductsBelowReorder, page => Url.Action("Index",
                                    "Home",
                                      new RouteValueDictionary() {
                                        { "BelowReorderPage", page },
                                        { "AboveReorderPage", Model.PageModel.AboveReorderPage }
                                      }), PagedListRenderOptions.ClassicPlusFirstAndLast)
    </div>
</div>
Output Source Code: Github - Multiple PagedList Pager

Wednesday, December 20, 2017

DataList in ASP.NET MVC with Paging Using PagedList and Bootstrap

Here's an example ASP.NET MVC application that shows information in a DataList layout with pagination using PagedList Pager. The project also integrates Bootstrap for enhancing the UI and display the images from AdventureWorks Products table through an ImageHandler class. The significant files used in this project are:
1. PagingModel.cs/IndexModel.cs - These classes are responsible for defining the page number, size of page, and PagedList property that stores the information that are paged accordingly to it's size.
public class PagingModel
{
 public PagingModel()
 {
  Size = 24;
  Page = 1;
 }

 public int Page { get; set; }
 public int Size { get; set; }
}

public class IndexModel
{
 public PagingModel PageModel { get; set; }

 public IndexModel(PagingModel pageModel)
 {
  Products = new PagedList<ProductViewModel>(new List<ProductViewModel>(), 1, pageModel.Size);
  PageModel = pageModel;
 }

 public IPagedList<ProductViewModel> Products { get; set; }
}
2. ProductRepository.cs - this class is responsible for retrieving the products from the table and it's thumbnail photo.
public class ProductRepository
{
 AdventureWorks2012Entities context;

 public ProductRepository()
 {
  context = new AdventureWorks2012Entities();
 }

 public IEnumerable<ProductViewModel> GetProducts()
 {
  List<ProductViewModel> products = new List<ProductViewModel>();

  products = (from prod in context.Products
       join productProductPhoto in context.ProductProductPhotoes on prod.ProductID equals productProductPhoto.ProductID
       join productPhoto in context.ProductPhotoes on productProductPhoto.ProductPhotoID equals productPhoto.ProductPhotoID
       where prod.ListPrice > 0
       select new ProductViewModel()
       {
      ProductID = prod.ProductID,
      ProductName = prod.Name,
      ProductNumber = prod.ProductNumber,
      ListPrice = prod.ListPrice,
      Image = productPhoto.LargePhoto
       }).ToList();            
  return products;
 }

 public ProductViewModel Product(int productId)
 {
  ProductViewModel product = new ProductViewModel();
  product = (from prod in context.Products
       join productProductPhoto in context.ProductProductPhotoes on prod.ProductID equals productProductPhoto.ProductID
       join productPhoto in context.ProductPhotoes on productProductPhoto.ProductPhotoID equals productPhoto.ProductPhotoID
       where productId == prod.ProductID
       select new ProductViewModel()
       {
        Image = productPhoto.ThumbNailPhoto
       }).FirstOrDefault();

  return product;
 }
}
3. Index.cshtml - This is the page that will display the products information in a tabular format and has a PagedListPager helper that navigates the record through page numbers and other paging options.
@model DataListInASPMVCWithPaging.Models.IndexModel
@using PagedList
@using PagedList.Mvc

<h2>Product Listing</h2>

<div class="row">
    <div class="col-md-12">
        @Html.PagedListPager(Model.Products, page => Url.Action("Index",
                            "Product",
                                new RouteValueDictionary() {
                                { "Page", page }
                                }), PagedListRenderOptions.ClassicPlusFirstAndLast)
    </div>
</div>
<div class="row">
    @if (Model.Products.Count > 0)
    {
        foreach (var item in Model.Products)
        {
            <div class="col-md-2 col-sm-6 col-xs-12" style="text-align:left;">
                <div style="background-color:moccasin; padding:10px; border-radius:15px; margin-bottom:20px;">
                    <img src="~/Helpers/ImageHandler.ashx?ProductID=@item.ProductID" style="width:50%;" />
                    <h3>@item.ProductNumber</h3>
                    <p>@item.ProductName</p>
                    <p>@string.Format("{0:0,0.00}", Convert.ToDouble(item.ListPrice))</p>
                    <div>
                        @Html.ActionLink("Add To Cart", "", "", null, new { @class = "actionButton btn btn-xs btn-primary" })
                    </div>
                </div>
            </div>
        }
    }

</div>
Output Source Code
DataList In ASP.NET MVC With Paging (Github)

Saturday, December 16, 2017

ASP.NET GridView With Running Total Per Group using JavaScript

Basing from the post DataTable With Running Total Per Group which applies running total to a DataTable object, we can also achieve the same functionality through the front-end side by changing the structure of the GridView control given that this is an ASP.NET WebForm application. The snippet to set the control's data source is simple such as below:
 dt.Columns.AddRange(new DataColumn[5]
     { new DataColumn("SrNo", typeof(int)),
    new DataColumn("PartyName", typeof(string)),
    new DataColumn("ItemName",typeof(string)),
    new DataColumn("ChqAmt", typeof(int)),
    new DataColumn("PartyCode",typeof(string))});
 dt.Rows.Add(1, "ABC Water Supply", "Construction Service", 400, "ABC");
 dt.Rows.Add(2, "ABC Pump Services", "Type 24 Engine Pump", 150, "ABC");
 dt.Rows.Add(3, "ABC Water Supply", "12 Ft Water Tank", 600, "ABC");
 dt.Rows.Add(4, "XYZ Hardware", "Garden Soil", 250, "XYZ");
 dt.Rows.Add(5, "XYZ Hardware", "Power Generator", 245, "XYZ");
 dt.Rows.Add(6, "ACE Hardware", "Screw Driver", 16, "ACE");
 dt.Rows.Add(7, "ACE Hardware", "8W Led Bulb", 18, "ACE");
 GridView1.DataSource = dt;
 GridView1.DataBind();
}
The GridView control has BoundField columns used to display the information.
<div id="content">
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
  <Columns>
   <asp:BoundField DataField="SrNo" HeaderText="SrNo" />
   <asp:BoundField DataField="PartyName" HeaderText="PartyName" />
   <asp:BoundField DataField="ItemName" HeaderText="ItemName" />
   <asp:BoundField DataField="ChqAmt" HeaderText="ChqAmt" >
    <ItemStyle HorizontalAlign="Right" />
   </asp:BoundField>
   <asp:BoundField DataField="PartyCode" HeaderText="PartyCode" ItemStyle-CssClass="hiddencol" HeaderStyle-CssClass="hiddencol"/>
  </Columns>
  <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
  <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
  <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
  <RowStyle BackColor="White" ForeColor="#003399" />
  <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
  <SortedAscendingCellStyle BackColor="#EDF6F6" />
  <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
  <SortedDescendingCellStyle BackColor="#D6DFDF" />
  <SortedDescendingHeaderStyle BackColor="#002876" />
 </asp:GridView>
</div>
The JavaScript code will loop through the table and does the calculation plus adding another table row to display the running total.
$(document).ready(function () {
 var table = $('table');
 var subtotal = 0;
 var grandTotal = 0;
 var flag = $('table tr:eq(1) td:eq(4)').text(); //assign code to flag from second table row after th.
 var tblLength = $('table tr').length;
 for (var index = 0; index < tblLength; index++) {
  if ($('table tr:eq(' + index + ')').find('td:eq(4)').text() != "") {
   var temp = $('table tr:eq(' + index + ')').find('td:eq(4)').text();
   if (temp != flag) {
    var html = "<tr style='color:#003399;background-color:White;'>"
        + "<td></td>"
        + "<td>" + "Total For " + $('table tr:eq(' + parseInt(index - 1) + ')').find('td:eq(4)').text() + "</td>"
        + "<td></td>"
        + "<td align='right'>" + subtotal + "</td>"
        + "<td class='hiddencol'></td>"
        + "</tr>";
    $('table tbody tr:eq(' + parseInt(index - 1) + ')').after(html);
    tblLength = $('table tr').length;
    grandTotal = parseFloat(grandTotal) + parseFloat(subtotal);
    subtotal = 0;
    flag = $('table tbody tr:eq(' + parseInt(index + 1) + ')').find('td:eq(4)').text();
    continue;
   }
   else {
    subtotal = parseInt(subtotal) + parseInt($('table tr:eq(' + index + ')').find('td:eq(3)').text());
    var len = $('table tr').length - 1;
    if (index == len) {
     var html = "<tr  style='color:#003399;background-color:White;'>"
          + "<td></td>"
          + "<td>" + "Total For " + $('table tr:eq(' + index + ')').find('td:eq(1)').text() + "</td>"
          + "<td></td>"
          + "<td align='right'>" + subtotal + "</td>"
          + "<td class='hiddencol'></td>"
          + "</tr>";
     $('table tbody tr:eq(' + parseInt(index) + ')').after(html);
     grandTotal = parseFloat(grandTotal) + parseFloat(subtotal);

     //insert grand total
     var htmlGrandTotal = "<tr  style='color:#003399;background-color:White;'>"
         + "<td></td>"
         + "<td>" + "Grand Total </td>"
         + "<td></td>"
         + "<td align='right'>" + grandTotal + "</td>"
         + "<td class='hiddencol'></td>"
         + "</tr>";
     $('table tbody tr:eq(' + parseInt(index + 1) + ')').after(htmlGrandTotal);
    }
   }
  }
 }

 $('table tr').each(function () {
  $(this).find('td').eq(3).text(parseFloat($(this).find('td').eq(3).text()).toLocaleFixed(2));
 });
});

Number.prototype.toLocaleFixed = function (n) {
 return this.toLocaleString(undefined, {
  minimumFractionDigits: n,
  maximumFractionDigits: n
 });
};
Screenshot

Friday, December 15, 2017

DataTable With Running Total Per Group

Good Evening!
There was a question raised by a developer if it's possible to compute the running total of a specific DataTable column per grouping or category. The solution is to mark the category as a flag variable that indicates the group to be calculated. The sample code below creates a DataTable object with fictitious information of hardware materials that belong to a particular group specifically PartyCode column. If a column belongs to that group, it will perform calculations. If not, reset the flag variable and total so as to perform new calculations. When computing totals for large number of records, make sure to sort the DataTable using it's category.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[5] 
    { new DataColumn("SrNo", typeof(int)),
   new DataColumn("PartyName", typeof(string)),
   new DataColumn("ItemName",typeof(string)),
   new DataColumn("ChqAmt", typeof(int)),
   new DataColumn("PartyCode",typeof(string))});
dt.Rows.Add(1, "ABC Water Supply", "Construction Service", 400,"ABC");
dt.Rows.Add(2, "ABC Pump Services", "Type 24 Engine Pump", 150, "ABC");
dt.Rows.Add(3, "ABC Water Supply", "12 Ft Water Tank", 600, "ABC");
dt.Rows.Add(4, "XYZ Hardware", "Garden Soil", 250, "XYZ");
dt.Rows.Add(5, "XYZ Hardware", "Power Generator", 245, "XYZ");
dt.Rows.Add(6, "ACE Hardware", "Screw Driver", 16, "ACE");
dt.Rows.Add(7, "ACE Hardware", "8W Led Bulb", 18, "ACE");

string flag = dt.Rows[0][4].ToString();
int subTotal = 0;
int grandTotal = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
 if (dt.Rows[i][4].ToString() != flag)
 {
  DataRow toInsert = dt.NewRow();
  toInsert[0] = DBNull.Value;
  toInsert[1] = string.Format("Total For {0}", dt.Rows[i-1][1].ToString());
  toInsert[2] = "";
  toInsert[3] = subTotal;
  toInsert[4] = null;
  dt.Rows.InsertAt(toInsert, i);
  grandTotal += subTotal;
  subTotal = 0;
  flag = dt.Rows[i+1][4].ToString();
  continue;
 }
 else
 {
  subTotal += Convert.ToInt32(dt.Rows[i][3].ToString());
  if (i == dt.Rows.Count - 1)
  {
   DataRow toInsert = dt.NewRow();
   toInsert[0] = DBNull.Value;
   toInsert[1] = string.Format("Total For {0}", dt.Rows[i][1].ToString());
   toInsert[2] = "";
   toInsert[3] = subTotal;
   toInsert[4] = null;
   dt.Rows.InsertAt(toInsert, i+1);
   grandTotal += subTotal;

   //insert grand total
   int totalIndex = i + 1;
   DataRow grandTotalInsert = dt.NewRow();
   grandTotalInsert[0] = DBNull.Value;
   grandTotalInsert[1] = "Grand Total";
   grandTotalInsert[2] = "";
   grandTotalInsert[3] = string.Format("{0}", grandTotal);
   grandTotalInsert[4] = null;
   dt.Rows.InsertAt(grandTotalInsert, totalIndex + 1);
   break;
  }
 }
}

GridView1.DataSource = dt;
GridView1.DataBind();
Output