SQL - Paging Logic Stored Procedure and Between Dates Filter Query

Here is the store procedure by which you can fetch your require data with paging and filter you need

Paging Logic

--EXEC dbo.[Demo_Paging_Logic] 1,10,'LookupValue ASC','LookupValue like''%123%''
CREATE PROCEDURE dbo.[Demo_Paging_Logic] @PageNumber INT = 1
 ,@PageSize INT = 5
 ,@SortExpression NVARCHAR(20) = 'LookupValue DESC'
 ,@WhereCondition NVARCHAR(MAX) = ' 1=1 '
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @FirstRec INT
  ,@LastRec INT
  ,@TotalRows INT

 SET @FirstRec = (@PageNumber - 1) * @PageSize
 SET @LastRec = (@PageNumber * @PageSize + 1)
 SET @TotalRows = @FirstRec - @LastRec + 1

 DECLARE @SortExpression1 NVARCHAR(MAX)
 DECLARE @Query NVARCHAR(MAX)
 DECLARE @Query1 NVARCHAR(MAX)

 SET @Query = 'SELECT *
     FROM(SELECT Cast(ROW_NUMBER() OVER (ORDER BY  ' + @SortExpression + ') As int) AS ROWNUM, COUNT(*) OVER(PARTITION BY 1) TotalRecord,*
     FROM Master.tblLookupValues WHERE  ' + @WhereCondition + ') AS  tbl
     WHERE ROWNUM > ' + CONVERT(NVARCHAR(10), @FirstRec) + ' AND ROWNUM < ' + CONVERT(NVARCHAR(10), @LastRec) + ' ORDER BY ROWNUM ASC'

 PRINT @Query

 EXEC sp_Executesql @Query
END
GO

Filter between two dates in SQL

DECLARE @StartDate AS VARCHAR(10) = '10-03-2014'
DECLARE @EndDate AS VARCHAR(10) = '26-03-2014'

SELECT *
FROM tABLE
WHERE Cast(convert(DATETIME, Cast(CreatedDate AS DATETIME), 103) AS DATE) BETWEEN Cast(convert(DATETIME, @StartDate, 103) AS DATE)
AND Cast(convert(DATETIME, @EndDate, 103) AS DATE)

Disable browser cache in ASP.NET MVC

First you need to create a custom action filter. For that you need to inherit  the class from ActionFilterAttribute.

  public class CustomActionFilterAttribute : ActionFilterAttribute
    {
       public override void OnResultExecuted(ResultExecutedContext filterContext)
        {
            var cache = filterContext.HttpContext.Response.Cache;
            cache.SetCacheability(HttpCacheability.NoCache);
            cache.SetRevalidation(HttpCacheRevalidation.ProxyCaches);
            cache.SetExpires(DateTime.Now.AddYears(-5));
            cache.AppendCacheExtension("private");
            cache.AppendCacheExtension("no-cache=Set-Cookie");
            cache.SetProxyMaxAge(TimeSpan.Zero);
        }

    }

To Implement above Action filter to any controller or to any particular action, you need to decorate controller or action as shown below:

   [CustomActionFilter]
    public class DemoController : Controller

    {
        ..Your Code..
    }

In MVC4, to disable browser caching across all controllers, but retain it for anything not served by a controller, add this to FilterConfig.RegisterGlobalFilters:

filters.Add(new  CustomActionFilterAttribute ());

Custom ASP.NET MVC Authorize Attribute

AuthorizeAttribute allows you to secure controller actions. The Authorize attribute lets you indicate that authorization is restricted to predefined roles or to individual users. This gives you a high degree of control over who is authorized to view any page on the site.

public class CustomAuthorizeAttribute : AuthorizeAttribute
    {
        // Custom property as you can get Page name which is passed from controller
        public string AccessLevel { get; set; }
        protected override bool AuthorizeCore(HttpContextBase httpContext)
        {
            if (httpContext == null)
                throw new ArgumentNullException("httpContext");

            if (!httpContext.User.Identity.IsAuthenticated)
                return false;
            else
            {
                //Check Roles
                HttpCookie authCookie =          httpContext.Request.Cookies[FormsAuthentication.FormsCookieName];
                if (authCookie == null || authCookie.Value == "")
                {
                }
                FormsAuthenticationTicket authTicket = null;
                try
                {
                    authTicket = FormsAuthentication.Decrypt(authCookie.Value);
                }
                catch
                {
                }        
                // retrieve roles from UserData
                string[] roles = authTicket.UserData.Split(';');
            }                
            return true;
    }

Here I just override the AuthorizeCore methods of AuthorizeAttribute class. Now, you have to decorate your controller or action with this custom attribute

[CustomAuthorize(AccessLevel = "DemoIndex")]
 public ActionResult DemoIndex()
{
       return View();
}

You can redirect an unauthorised user in your custom AuthorisationAttribute by overriding the HandleUnauthorizedRequest method:

protected override void HandleUnauthorizedRequest(AuthorizationContext filterContext)
    {
        filterContext.Result = new RedirectToRouteResult(
                    new RouteValueDictionary(
                        new
                            { 
                                controller = "Error", 
                                action = "Unauthorised" 
                            })
                    );
    }




Playing with Jquery Cookie in Chrome and All broswers

When i trying to store value in Local Store means in browser then I have got Good solution by using $.Cookie. But when I Have started to work with it then its working fine in with Mozilla and IE. But when I have started to work in chrome then I have got problem to set and get Cookie through $.cookie. Then I have got a very good solution to Store cookie value in Local Browser by Using jquery.Storage.js($.Storage) and Its solved my problem with ease.

Code : 

<script src="js/jquery-1.9.1.js" language="javascript" type="text/javascript"></script>
<script src="js/jquery.cookie.js" language="javascript" type="text/javascript"></script>
<script src="js/jquery.Storage.js" language="javascript" type="text/javascript"></script>

var isChrome = navigator.userAgent.toLowerCase().indexOf('chrome') > -1;

//get cookies
var Flag=(isChrome)?$.Storage.get("Flag"):$.cookies.get("Flag");

//set cookies
if(isChrome)$.Storage.set("Flag", "1");else $.cookies.set("Flag", "1");

Jquery Datatable Simple Initialization

Here is the require CSS and Javascript that you need to put in header of HTML to acquire functionality of DataTable JS

/******Script Require ****/

<script src="../../DT/jquery.js" type="text/javascript"></script>
<script src="../../DT/jquery.dataTables.js" type="text/javascript"></script>
<script src="../../Script.js" type="text/javascript"></script>


@* Datatable Css *@
 <link href="../../DT/demo_table.css" rel="stylesheet" type="text/css" />
 <link href="../../DT/demo_page.css" rel="stylesheet" type="text/css" />

You need to write following script to initialize data table.

 $(document).ready(function () {

var myURL = "/List";
                    $.ajax({
                        type: 'POST',
                        url: myURL,
                        contentType: 'application/json; charset=UTF-8',
                        dataType: 'json',
                        beforeSend: function () {
                        },
                        success: function (data) {
                              var objlist = JSON.parse(data);
                            if (objlist.toString() != '') {
                                rwAll = CreateDynamicTable(objlist);
                                //Create HTML Table in  DIV
                                $("#bind").html(rwAll);

                                $('#example').dataTable({
                                    "sPaginationType": "full_numbers",
                                    "fnDrawCallback": function () {
                                        oTable = $('#example').dataTable();
                                    },
                                    "fnStateLoaded": function (oSettings, oData) {

                                    },
                                    "fnInitComplete": function (oSettings, json) {
                                    },
                                });

                            }
                            else {
                                //if no data
                            }
                        },
                        error: function (result) {
                            alert('Please try after some time !');
                        }
                    }).always(function () {

                    });
    });
------------------------------------------

function CreateDynamicTable(objArray) {
    //var array = JSON.parse(objArray);
    var array = objArray;
    var str = '<table class="display" cellpadding="0" cellspacing="0" border="0" id="example">';
    str += '<thead><tr>';
    for (var index in array[0]) {
        str += '<th scope="col">' + index + '</th>';
    }
    str += '</tr></thead>';
    str += '<tbody>';
    for (var i = 0; i < array.length; i++) {
        str += (i % 2 == 0) ? '<tr class="gradeA">' : '<tr class="gradeA">';
        for (var index in array[i]) {
            if (index == "Date") {

                str += '<td>' + array[i][index] + '</td>';
            }
            else {
                str += '<td>' + array[i][index] + '</td>';
            }
        }
        str += '</tr>';
    }
    str += '</tbody>'
    str += '<tfoot><tr>';
    for (var index in array[0]) {
        str += '<th scope="col">' + index + '</th>';
    }
    str += '</tr></tfoot>';
    str += '</table>';
    return str;
}

Kendo Grid Detail Template with MVC

@using Kendo.Mvc.UI

<input type="button" id="Create" value="Add New" />
<input type="button" id="Delete" value="Delete" onclick="DeleteSelected();" />

//Initailization

@(Html.Kendo().Grid<iFacilitiesDemo.Models.sp_Result>()
     //@(Html.Kendo().Grid<iFacilitiesDemo.Models.Task>()
    .Name("Grid")
     //.BindTo((IEnumerable<iFacilitiesDemo.Models.sp_Result>)ViewBag.Products)
     //.EnableCustomBinding(true)
    .Columns(columns =>
    {
        columns.Bound(p => p.Status).HeaderTemplate("<input id='selectall' class='chkbx' type='checkbox' onclick='ToggleChkBox(this.checked);' />").ClientTemplate("<input id='checkbox' onclick='grdChkBoxClick(this); ' class='chkbxq' type='checkbox' />").Sortable(false).Filterable(false).Width(30);
        columns.Bound(p => p.Job_No).ClientFooterTemplate("Total Count: #=count# and Max #= max#");
        columns.Bound(p => p.Company);
        columns.Bound(p => p.Status);
        columns.Bound(p => p.Manager);
        columns.Bound(p => p.Client);
        columns.Bound(p => p.Start_Date).Format("{0:dd/MM/yyyy}");
        columns.Command(command => command.Custom("ViewDetails").Click("showDetails"));
        columns.Command(c =>
        {
            c.Custom("RefreshRecord").Text("Your Detail").Click("onClick");
        }).Width(80);
    })
    .Groupable()
    .Pageable(pager => pager
            .Input(true)
            .Numeric(true)
            .Info(true)
            .PreviousNext(true)
            .Refresh(true)
            .PageSizes(true)
            )
    .Selectable(selectable => selectable.Mode(GridSelectionMode.Multiple))
    .Sortable()
    .Filterable(filterable => filterable
            .Extra(true)
            .Operators(operators => operators
                .ForString(str => str.Clear()
                    .Contains("Contains")
                    .StartsWith("Start With")
                    .IsEqualTo("Is equal to")
             ))
             .Operators(operators => operators
                    .ForDate(str => str.Clear()
                    .IsGreaterThanOrEqualTo("From")
                    .IsLessThanOrEqualTo("To")
             ))
    )
    .Scrollable()
    .Resizable(resize => resize.Columns(true))
    .Filterable()
    .ClientDetailTemplateId("MyTemplate")
    .HtmlAttributes(new { style = "height:430px;" })
     //.Events(events => events.DataBound("dataBound"))
    .DataSource(dataSource => dataSource.Ajax()
         .Aggregates(aggregates =>
         {
             aggregates.Add(p => p.Job_No).Min().Max().Count();
         })
        .Model(
                model =>
                {
                    model.Id(p => p.Job_No);
                }
        )
        .ServerOperation(false)
        //.Read(read => read.Action("GetTaskAll", "Kendo"))
        .Read(read => read.Action("GetTaskAllBySP", "Kendo"))
     )
        //Bound Start    @*.Events(events => events.DataBound( @<text> function(e) { alert('bound')  }</text>*@
        //Bound End
     .Events(events => events.DataBound("gridDataBound"))
))


@*For Custom Command*@
@(Html.Kendo().Window().Name("Details")
    .Title("My Details")
    .Visible(false)
    .Modal(true)
    .Draggable(true)
    .Width(300)
)


@*For Detail Template start*@
<script id="MyTemplate" type="text/kendo-tmpl">
    @(Html.Kendo().TabStrip()
            .Name("tabStrip_1")
            //.Name("tabStrip_#=Job_No#")
            .SelectedIndex(0)
            .Animation(false)
            .Events(e => e.Select("tabstrip_select"))
            //.Animation(animation => animation.Open(open => open.Fade(FadeDirection.In)))
            .Items(items =>
            {
                items.Add().Text("Orders").Content(@<text>
                    @(Html.Kendo().Grid<iFacilitiesDemo.Models.sp_Result>()
                        .Name("grid_#=Job_No#")

                        .Columns(columns =>
                        {
                            columns.Bound(o => o.Job_No).Title("ID").Width(56);
                            columns.Bound(o => o.Company).Width(110);
                            columns.Bound(o => o.Status);
                            columns.Bound(o => o.Client).Width(190);
                        })
                        .DataSource(dataSource => dataSource
                            .Ajax()
                            .PageSize(5)
                            .Read(read => read.Action("GetTaskAllBySP", "Kendo"))
                            //.Read(read => read.Action("GetTaskAllBySP", "Kendo", new { employeeID = "#=EmployeeID#" }))
                        )
                        .Pageable()
                        .Sortable()
                        .ToClientTemplate())
                </text>
                );
                items.Add().Text("aa").Content(
                    "<div class='employee-details'>hihi </div>"
                );
            })
            .ToClientTemplate())
</script>
//Css
<style scoped="scoped">
    .k-detail-cell .k-tabstrip .k-content
    {
        padding: 0.2em;
    }
   @*Td and tr Padding [height]*@
    .k-grid td
    {
        padding: 2px 4px;
    }
 
</style>

@*For Detail Template End*@

@*View Detail Start*@
<script type="text/x-kendo-template" id="ViewTemplate">
    <div id="details-container">
        <h2>#= Job_No # and #= Company #</h2>
        <em>#= Manager #</em>
        <dl>
            <dt>Status: #= Status #</dt>
            <dt>Client: #= Client #</dt>
        </dl>
    </div>
</script>

<script type="text/javascript">
    var detailsTemplate = kendo.template($("#ViewTemplate").html());

    //PreviewClick
    function showDetails(e) {
        //        $("div.k-widget.k-window").css("-webkit-transform", "scale(1)");

        e.preventDefault();
        var dataItem = this.dataItem($(e.currentTarget).closest("tr"));
        var wnd = $("#Details").data("kendoWindow");

        wnd.content(detailsTemplate(dataItem));

        wnd.center().open();
    }
</script>
@*View Detail End*@

<script>
    //Grid Data Bound Start
    function gridDataBound(e) {

        //$("div.k-widget.k-window").css("-webkit-transform", "scale(2)");
        //        var dds = $("#Details").parent('div');

        //        alert(dds.innerHTML)
        //        dds.css({
        //            '-moz-transform': 'scale(2)',
        //            '-webkit-transform': 'scale(2)'
        //        });
        var grdApplyColor = $('#Grid').data("kendoGrid");
        row = grdApplyColor.tbody.find(">tr:not(.k-grouping-row)");

        //6th row is Status on its value we want to apply color
        rowActive = row.find("td:nth-child(5)");
        $.each(rowActive, function (i, rowValue) {
            //debugger;
            //alert(rowValue.innerHTML);
            //check Status = Invoiced 'then' #ECF6CE
            if (rowValue.innerHTML == 'Invoiced') {
                objRow = grdApplyColor.tbody.find('tr:nth-child(' + (i + 1) + ')');
                objRow.css("background-color", "#ECF6CE"); ;
            }
            else if (rowValue.innerHTML == 'false') {
                objRow = grdApplyColor.tbody.find('tr:nth-child(' + (i + 1) + ')');
                objRow.css("background-color", "#848484");
            }
        });
    }
    //Grid Data Bound End
    //Grid Header Chkbox chk all chkbox
    function ToggleChkBox(flag) {
        $('.chkbxq').each(function () {
            $(this).attr('checked', flag);
        });
    }
    //On Delete button click
    function DeleteSelected() {
        //        if (confirm('Are you sure you want to Delete')) {
        //            return false;
        //        }
        var idArray = "";
        $('.chkbxq').each(function () {
            if (this.checked == true) {
                idArray = idArray + "," + this.id;
            }
        });
        alert('Data to delete: ' + idArray);

        //grid.dataSource.read();
        //grid.query({ page: 1, pageSize: 10, sort: { field: "POID", dir: "asc"} });
    }
    //To change tab strip in Grid
    function tabstrip_select(e) {
        //alert('tabstrip_select');
        var x = e.item;
        var selectedIndex = $(e.item).index();
    }

    //check box toggle state (problem 2 time toggle so did it third time for control click)
    function grdChkBoxClick(obj) {
        if (obj.checked == true) {
            obj.checked = false;
        }
        else if (obj.checked == false) {
            obj.checked = true;
        }
    }
    //Cell click Checkbox select
    $('#Grid').on("click", "td", function (e) {
        var selectedTd = $(e.target).closest("td");
        var cellText = $(e.target).closest("td").find('input:checkbox').context;
        cellText = cellText.textContent || cellText.outerText;
        if (cellText != "ViewDetails") {
            var grdChkBox = selectedTd.parents('tr').find("td:first").next("td").find('input:checkbox');
            grdChkBox.prop('checked', !grdChkBox.prop('checked'));
        }
    });
    function onClick(e) {
        grid = $("#Grid").data("kendoGrid");
        dataItem = grid.dataItem($(e.srcElement).closest("tr"));
        var jobNos = dataItem.Job_No;
        $.post("/Kendo/getViewDetailJobNo", { 'inJobNo': jobNos }, function (ViewDetaildatas) {
            if (ViewDetaildatas != null) {
                var winData = '<h2>' + ViewDetaildatas[0].OrderNo + ' and ' + ViewDetaildatas[0].ECSJobNo + '</h2>' +
                             '<em>' + ViewDetaildatas[0].Manager + '</em>' +
                             '<dl>' +
                                   '<dt>DescOfFault:' + ViewDetaildatas[0].DescOfFault + '</dt>' +
                                    '<dt>Type:' + ViewDetaildatas[0].Type + '</dt>' +
                               '</dl>';
                var wnd = $("#Details").data("kendoWindow");

                wnd.content(winData);
                wnd.center().open();
            }
        });
    }
</script>

Login Page - MVC

-----------------------HTML---------------------------------------------
<fieldset>
                <legend>Login</legend>
                <div>
                    <div>
                        User:
                    </div>
                    <div>
                        @Html.TextBox(UserName)
                        &nbsp;&nbsp;<span style="font-weight: bold"> Hint: </span>admin
                    </div>
                </div>
                <div>
                    <div>
                        Password
                    </div>
                    <div>
                        @Html.Password(Password)
                        &nbsp;&nbsp;<span style="font-weight: bold"> Hint: </span>admin
                    </div>
                </div>
                <div>
                    <div>
                        <input type="submit" value="Sign In" title="Sing In" onclick="return UserValidate();"
                            id="btnSave" />
                        <input type="button" value="Cancel" title="Clear" id="btnClear" />
                    </div>
                    <div>
                    </div>
                </div>
            </fieldset>
-------------------------------Script---------------------------------------


 function UserValidate() {
        if ($('#UserName') != null) {
            if (trim($('#UserName').val()) == '') {
                alert('Please enter Username.')
                document.getElementById('UserName').focus();
                return false;
            }
        }
        if ($('#Password') != null) {
            if (trim($('#Password').val()) == '') {
                alert('Please enter Password.')
                document.getElementById('Password').focus();
                return false;
            }
        }
/*Validate Here*/


        $.post('/Controller/Action', { Param: $('#UserName').val() }, function (Data) {
            if (Data != null) {
             if(Data.toString() == 'exist')
                 {
                  Alert('Name already Exist')
                return False;
                   }
              }
        });

    }

 function trim(str) {
        return str.replace(/^\s+|\s+$/g, "");
    }
------------------------------------------Controller.cs ----------------
public JsonnResult Action(string Param)
{

if(objUserMasterRep.IsExist(Param))
{
return Json("exist");
}
return "";
}