NJ Driving Written Test Cheetsheet

 

Display data in hierarchal/Treeview style using jQuery

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 = " " , endTR = "", startTD = "", endTD = "";

                    //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 +

                            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 + "+i+">" + 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

Web scraping through WebBrowser (.Net WinForm Control)

In this post, I am going to explain how it is easy to extract and change the the webpage content through WebBrowser control. The WebBrowser is a Microsoft windows form control and easy to use. This post will explain you how you can use this control for your need.

The WebBrowser control is used to display the webpage in your application. The WebBrowser class is a powerful class that give you leverage to manipulate the html code, interact with JavaScript, automate the web scrapping and many more.

You can find more about the WebBrowser from MSDN library. The following steps will guide you how to use the WebBrowser.

1.       The first thing that you should do is to create a windows application project.

2.       Add a WebBrowser and two button controls on web form.  

3.       Set the “Url” property of the control or you can do this programmatically using the below code snippet.

webBrowser.Navigate("https://www.google.com");

4.       When you run the application, you will see the screen look as per below screen print

5.       If you want to set anything to webpage control use the below code and even you can fire the control events as well. Here, I am going to set the value to the search box (for this example I am going to set my name “Mohd Azharuddin Ansari” to search box) and then I will fire “Click” event of search button programmatically. If everything will go as per plan then google will present me the result based on the search criteria.

Code (This code will go on first button click event)

                webBrowser.Document.GetElementById("q").SetAttribute("value", "Mohd Azharuddin Ansari");

            HtmlElement button = webBrowser.Document.GetElementById("btnK");

            button.InvokeMember("click");

Result

6.       Now if you need to extract this results on somewhere your code then you can do it using the below code

Code (This code will go on second button click event)

string searchResultText = "";

            HtmlElementCollection searchResult = webBrowser.Document.GetElementsByTagName("h3");

            foreach(HtmlElement he in searchResult)

            {

                searchResultText += he.InnerText + System.Environment.NewLine;

            }

            MessageBox.Show(searchResultText);

Result