In this post, I am going to explan you how easily you can display the data in hierarchal/Treeview style using HTML, ASP.NET WebApi and jQuery. This is the functionality that we commonly need in most of the application to present the data to end user. The user must have the basic knowledge of WebApi and jQuery as most of the work is done using both the technologies.
In my example, I will present the SOW and SOW Fin data where SOW is parent and SOW data is child. I hope you will like the post. Leave your inputs in comment area down below. Let’s dig in.
CDN for jQuery Libraries
<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
<script src="https://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
StyleSheet
<style>
.thSow{
background-color: lightSlateGrey;
color: #fff;
font-weight: bold;
line-height:20px;
font-family:Calibri;
font-size:14px;
width:80px;
border:1px solid lightGray;
text-align:center;
}
.thFin{
background-color: peru;
color: #fff;
font-weight: bold;
line-height:18px;
font-family:Calibri;
font-size:15px;
width:80px;
border:1px solid lightGray;
text-align:center;
}
.tdCol{
width:60px;
font-family:Calibri;
font-size:13px;
line-height:15px;
width:80px;
border:1px solid lightGray;
padding-left:10px;
}
</style>
HTML Code
<div id="serachResults" style="overflow-x:scroll"></div>
<input type="text" class="form-control text-box single-line" id="filterVal" style="width:200px" />
<input type="submit" value="Search" class="btn btn-success submit" style="margin-right:25px" onclick="SearchData(this); return true;" />
JavaScript/jQuery Code
$(document).ready(function () {
SearchData();
});
function SearchData() {
$.ajax({
type: "GET",
url: "@Url.Action("SearchData", "Home")",
data: { "filterColumn": $('#filterVal').val()},
contentType: "application/json; charset=utf-8",
success: function (result) {
//debugger;
var htm = "";
var startTable = ""
, endTable = "
var startThSow="" , startTh = "" , endTH = "
", startTR = "
//Add table Header
htm = startTable;
for (var i = 0; i < result.length; i++)
{
var onNumbers=[], offNumbers=[];
htm += startTR + startThSow + "" + endTH + startThSow + "Project Name" + endTH + startThSow + "Currency" + endTH + startThSow + "Location" + endTH + startThSow + "Start Date" + endTH + startThSow + "End Date" + endTH + endTR;
htm += startTR + startTD + "
+i+" onclick=HideShow(this);>" + endTD +
+i+" onclick=HideShow(this);>" + endTD +
startTD + result[i].ProjectName + endTD +
startTD + result[i].Currency + endTD +
startTD + result[i].Location + endTD +
startTD + result[i].SOWStartDate + endTD +
startTD + result[i].SOWEndDate + endTD;
htm += endTR + startTR + "
for (var j = 0; j < result[i].SOWFin.length; j++) {
htm += startTh + result[i].SOWFin[j].ColumnHeader + endTH;
}
htm += endTR + startTR + startTD + "Offshore" + endTD;//Offshore
for (var k = 1; k < result[i].SOWFin.length; k++) {
htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((result[i].SOWFin[k].OffshoreNumber) / selCurrRate) : (result[i].SOWFin[k].OffshoreNumber) * selCurrRate) : result[i].SOWFin[k].OffshoreNumber).toFixed(3) + endTD;
offNumbers.push(result[i].SOWFin[k].OffshoreNumber);
}
htm += endTR + startTR + startTD + "Onshore" + endTD;//Onshore
for (var k = 1; k < result[i].SOWFin.length; k++) {
htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((result[i].SOWFin[k].OnshoreNumber) / selCurrRate) : (result[i].SOWFin[k].OnshoreNumber) * selCurrRate) : result[i].SOWFin[k].OnshoreNumber).toFixed(3) + endTD;
onNumbers.push(result[i].SOWFin[k].OnshoreNumber);
}
htm += endTR + startTR + startTD + "Total" + endTD;//Total
for (var l = 0; l < onNumbers.length; l++)
{
htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((onNumbers[l] + offNumbers[0]) / selCurrRate):((onNumbers[l] + offNumbers[0]) * selCurrRate)) : (onNumbers[l] + offNumbers[0])).toFixed(3) + endTD;
}
htm += endTable+endTR;
}
htm += endTR + endTable;
$('#serachResults').html(htm);
},
error: function (data) {
alert(data)
}
});
}
Data Contract
public class SOWAdhoc
{
public string ProjectName { get; set; }
public string Currency { get; set; }
public string Location { get; set; }
public string SOWStartDate { get; set; }
public string SOWEndDate { get; set; }
public List<SOWFinancial> SOWFin { get; set; }
}
public class SOWFinancial
{
public int MonthId { get; set; }
public int YearId { get; set; }
public double OnshoreNumber { get; set; }
public double OffshoreNumber { get; set; }
public string ColumnName { get; set; }
public string ColumnHeader { get; set; }
}
WebAPI
[HttpGet]
[OutputCache(Duration = 0)]
public ActionResult GetAdhocResult(string filterColumn, string filterData, string sowType)
{
List<string> objList = new List<string>();
string sSQL = "";
sSQL = "select * from table";
openCon();
OleDbCommand oc = new OleDbCommand(sSQL, con);
OleDbDataAdapter oda = new OleDbDataAdapter(oc);
DataSet ds = new DataSet();
oda.Fill(ds);
ListSOWAdhoc> lstSOW = new ListSOWAdhoc>();
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dRow in ds.Tables[0].Rows)
{
lstSOW.Add(new Models.SOWAdhoc()
{
ProjectName = dRow["ProjectName"].ToString(),
Currency = dRow["Currency"].ToString(),
Location = dRow["location"].ToString(),
SOWStartDate = Convert.ToDateTime(dRow["StartSOW"]).ToShortDateString(),
SOWEndDate = Convert.ToDateTime(dRow["EndSOW"]).ToShortDateString(),
SOWFin = GetColumnName(dRow["Id"].ToString())
});
}
}
return Json(lstSOW, JsonRequestBehavior.AllowGet);
}
private List<SOWFinancial> GetColumnName(string Id)
{
string sSQL = "";
openCon();
OleDbCommand oc = new OleDbCommand(sSQL, con);
OleDbDataAdapter oda = new OleDbDataAdapter(oc);
DataSet ds = new DataSet();
oda.Fill(ds);
List<SOWFinancial> lstFin = new List<SOWFinancial>();
if (ds.Tables[0].Rows.Count > 0)
{
int loop = 0;
lstFin.Add(new SOWFinancial() { ColumnName = "LocationName", ColumnHeader = "Location Name" });
for (DateTime dt = Convert.ToDateTime(ds.Tables[0].Rows[0]["sSOW"]); dt <= Convert.ToDateTime(ds.Tables[0].Rows[0]["eSOW"]); dt = dt.AddMonths(1))
{
sSQL = "select * from finData where id=" + Id;
openCon();
OleDbCommand oChild = new OleDbCommand(sSQL, con);
OleDbDataAdapter odaChild = new OleDbDataAdapter(oChild);
DataSet dsChild = new DataSet();
odaChild.Fill(dsChild);
lstFin.Add(new SOWFinancial()
{
ColumnName = "Column" + loop.ToString(),
ColumnHeader = sMonthName[dt.Month - 1] + dt.Year.ToString(),
OffshoreNumber = (dsChild.Tables[0].Rows.Count > 0 ? Convert.ToDouble(dsChild.Tables[0].Rows[0]["OffshoreAmount"]) : 0),
OnshoreNumber = (dsChild.Tables[0].Rows.Count > 0 ? Convert.ToDouble(dsChild.Tables[0].Rows[0]["OnshoreAmount"]) : 0)
});
loop += 1;
}
}
return lstFin;
}
Sample Data
[{“ProjectName”:”test1”,”Currency”:”USD”,”Location”:”ONSHORE”,”SOWStartDate”:”4/1/2019”,”SOWEndDate”:”4/30/2019”,”SOWFin”:[{“MonthId”:”4”,”YearId”:”2019” ,”OnshoreNumber”:”0.000” ,”OffshoreNumber”:”0.000” ,”ColumnName”:”Column1” ,”ColumnHeader”:” Apr2019”}, {“MonthId”:”5”,”YearId”:”2019” ,”OnshoreNumber”:”0.000” ,”OffshoreNumber”:”0.000” ,”ColumnName”:”Column1” ,” ColumnHeader”:”May2019”}]}]
Final Output
