Fong coding resources

11 January 2016

How to create a scrollable table with header

See below example. The tricks are

Use jQuery to unhide the right column in the table when scroll bar is not visible.

Scrollbar is only visible if the row height * number of rows > table height

   table {
     table-layout: fixed;
   }

is important to control the layout

 @model C2.Models.dtSalary  
 <!doctype html>  
 @{  
   Layout = null;  
 }  
 <script src="~/Scripts/jquery-1.10.2.js"></script>  
 <STYLE type="text/css">  
   table {  
     table-layout: fixed;  
   }  
   .TableDiv {  
     overflow: auto;  
     height: 400px;  
     width: 320px;  
   }  
   .TableHeaderDiv {  
     width: 320px;  
   }  
   .TableFormat {  
     font-family: Arial;  
     font-size: 0.8em;  
     text-align: center;  
     border-collapse: collapse;  
     width: 100%;  
   }  
   .MediumColumn {  
     width: 100px;  
   }  
   .LongColumn {  
     width: 200px;  
   }  
   .TdHeight {  
     height: 20px;  
   }  
   th {  
     font-weight: 600;  
   }  
   .ColumnNoDisplay {  
     width: 17px;  
     display: none;  
   }  
 </STYLE>  
 <div class="TableHeaderDiv">  
   <table border="1" cellpadding="1" cellspacing="0" class="TableFormat">  
     <colgroup>  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col width="17px" />  
     </colgroup>  
     <tr>  
       <th>  
         IP Address  
       </th>  
       <th>  
         Country Name  
       </th>  
       <th>  
         City  
       </th>  
       <th></th>  
     </tr>  
   </table>  
 </div>  
 <div class="TableDiv">  
   <table border="1" cellpadding="1" cellspacing="0" class="TableFormat">  
     <colgroup>  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="ColumnNoDisplay" />  
     </colgroup>  
     @foreach (var item in Model.salaryData)  
     {  
       <tr>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.IPAddress)  
         </td>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.CountryName)  
         </td>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.City)  
         </td>  
         <td class="ColumnNoDisplay">&nbsp</td>  
       </tr>  
     }  
   </table>  
 </div>  
 <script>  
   var divHeight = 400  
   var rowHeight = 20  
   var rowcount = @Model.salaryData.Count;  
   if (rowcount * rowHeight > divHeight)  
   {  
   }  
   else  
   {  
     $(".ColumnNoDisplay").css('display','table-column');  
   }  
 </script>

06 January 2016

IsHex function for SQL Server

 IF EXISTS (  
   SELECT * FROM sysobjects WHERE id = object_id(N'fnIsHex')   
   AND xtype IN (N'FN', N'IF', N'TF')  
 )  
   DROP FUNCTION dbo.fnIsHex  
 GO  
 CREATE FUNCTION dbo.fnIsHex(@s VARCHAR(50) )   
 --Returns true if the string is a valid hexadecimalal number.   
 RETURNS bit  
 AS  
 BEGIN  
 /*  
 Description: Determine if a string represent an Hexadecimal value  
 Author:   Leo Fong  
 Created Date:06 Jan 2015  
 Usage: select dbo.fnIsHex('EX0429168AE12A80')  
     select dbo.fnIsHex('E0429168AE12A80')  
 */  
 DECLARE @i int, @temp char(1), @bool bit  
 SET @i=1  
 SET @bool=0  
 WHILE (@i<=LEN(@s))  
 BEGIN  
   SELECT @temp=SUBSTRING(@s,@i,1)  
   if ((@temp<='f') AND (@temp>='a')) OR ((@temp<='F') AND (@temp>='A')) OR ((@temp<='9') AND (@temp>='0'))   
     BEGIN  
       SET @bool=1  
     END  
   ELSE  
     BEGIN  
       SET @bool=0  
       RETURN @bool  
     END  
   SELECT @i=@i+1  
 END  
 RETURN @bool  
 END

20 December 2015

BizTalk consume web service using dynamic address

Create a dynamic port
Set URL of the port using expression

/* set port address */
portDynamicSend(Microsoft.XLANGs.BaseTypes.Address) = msgDynamicAddress.DestinationURL;
/* set port type */
portDynamicSend(Microsoft.XLANGs.BaseTypes.TransportType) = "WCF-BasicHttp";

Modify the message using expression

msgSend2 = msgSend;
/* set web service operation name */
msgSend2(BTS.Operation)="Propose";
msgSend2(WCF.PropagateFaultMessage)= true;

/* if security is required, add the below lines */
msgSend2(WCF.SecurityMode)="Transport";
msgSend2(WCF.TransportClientCredentialType)="None";

How to add legend to Chart.js graph

 @model C2.Models.ChartModel  
 @{  
   ViewBag.Title = "Chart";  
 }  
 <h2>Chart</h2>  
 <div>  
   <h3>Data</h3>  
   @*@Html.Raw(Model.ChartHtmlDisplay)*@  
 </div>  
 <div style="width:50%">  
   <div>  
     <canvas id="canvas" height="450" width="600"></canvas>  
   </div>  
   <div id="divLegend">  
   </div>  
 </div>  
 <script>  
   var lineChartData = {  
   @Html.Raw(Model.ChartHtml)  
   };  
   window.onload = function(){  
     var options = {  
       legendTemplate : '<ul>'  
               +'<% for (var i=0; i<datasets.length; i++) { %>'  
                +'<li>'  
                +'<span style=\"color:<%= datasets[i].pointColor %>;font-family:Arial;font-size:1em\">'  
                +'<% if (datasets[i].label) { %><%= datasets[i].label %><% } %>'  
                +'</span>'  
               +'</li>'  
              +'<% } %>'  
             +'</ul>'  
     }  
     var ctx = document.getElementById("canvas").getContext("2d");  
     //don't forget to pass options in when creating new Chart  
     var lineChart = new Chart(ctx).Line(lineChartData, options);  
     //then you just need to generate the legend  
     var legend = lineChart.generateLegend();  
     //and append it to your page somewhere  
     $('#divLegend').html(legend);  
     var ctx = document.getElementById("canvas").getContext("2d");  
     window.myLine = new Chart(ctx).Line(lineChartData, {  
          responsive: true  
     });  
   }  
 </script>

17 November 2015

An easy way to generating year, month list with SQL

DECLARE @startmonth      int = 1
DECLARE @endmonth        int = 12
DECLARE @startyear       int = 2012
DECLARE @endyear         int = 2015
;
WITH monthlist AS
(
    SELECT @startmonth AS m
    UNION ALL
    SELECT m+1 FROM monthlist WHERE m+1<=@endmonth
)
,yearlist AS
(
    SELECT @startyear AS y
    UNION ALL
    SELECT y+1 FROM yearlist WHERE y+1<=@endyear
)
        SELECT y,
               m
          FROM yearlist
    CROSS JOIN monthlist
      ORDER BY y,
               m
        OPTION (maxrecursion 1000)

12 November 2015

MVC web service in JSON

Here is an example

    public class HomeController : Controller
    {
        public ActionResult ListJobs()
        {
            List jobs;
            GraphEntities db = new GraphEntities();
            jobs = db.Jobs.ToList();
            return Json(jobs, JsonRequestBehavior.AllowGet);
        }

Here is the output

[{"JobID":1,"Job1":"Software Engineer"},{"JobID":2,"Job1":"Junior Programmer"},{"JobID":3,"Job1":"Senior Programmer"},{"JobID":4,"Job1":"Database Administrator"},{"JobID":5,"Job1":"Business Analyst"}]

How to use partial view in MVC, an example

Here is the index page

@model WebApplication1.Models.GridData
<!doctype html>
@{Layout = null;}
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Home</title>
    <script src="~/Scripts/jquery-1.10.2.js"></script>
</head>
<body>
   <h1>Header</h1>
        <input type="button" name="btnGetJob" id="btnGetJob" value="GetJob" class="Button" onclick="GetJob();" />
        <div id="divDataGrid">
            @Html.Partial("_DataGrid", Model)
        </div>
    <script>
        function GetJob()
        {$.ajax({
                        type: "POST",
                        url: "/Home/_DataGrid",
                        success: function (data) {
                            $('#divDataGrid').html(data);
                        },
                        error: function () {
                            alert('error');
                        },
                        complete: function () {}
                   })
        }
    </script>
</body>
</html>

Here is the partial view

@model WebApplication1.Models.GridData
@{Layout = null;}
<table class="table">
    <tr>
        <th>
            @Html.DisplayTextFor(model => model.message)
        </th>
        <th></th>
    </tr>
    @foreach (var item in Model.jobs)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Job1)
            </td>

        </tr>
    }
</table>

Here is the data model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication1.Models
{
    public class GridData
    {
        public string message { get; set; }
        public List<Job> jobs { get; set; }

        public GridData()
        {
            GraphEntities db = new GraphEntities();
            jobs = db.Jobs.ToList();
        }
    }
}

Here is the controller

    public class HomeController : Controller
    {
        private static int SessionCount = 0;
        public ActionResult Index()
        {
            GridData gd = new GridData();
            gd.message = SessionCount.ToString();
            return View(gd);
        }
        public ActionResult _DataGrid()
        {
            SessionCount++;
            GridData gd = new GridData();
            gd.message = SessionCount.ToString();
            return View(gd);
        }
        ...

Here is the output