Paging with OFFSET and FETCH in SQL 2012

Paging with OFFSET and FETCH

 

Created a sample table with few records. @PAGESIZE variable gives the no of records needed in each page, @STARTINGROW variable gives the starting record count, @SORTCONDITION gives the sorting attribute and order (functional requirement).

 

DECLARE @PAGESIZE INT

DECLARE @STARTINGROW INT

DECLARE @SORTCONDITION VARCHAR(250)

 

SET @PAGESIZE = 20

SET @STARTINGROW = 10 -1

SET @SORTCONDITION = 'EMPNO ACS'

 

SELECT        R1.ID 

,             R1.EMPNO 

,             R1.EMPNAME

,             R1.EMPADD1

FROM   SAMPLEFETCH R1       (NOLOCK)

ORDER BY

              CASE   @SORTCONDITION

              WHEN   'EMPNO ACS' THEN EMPNO

              WHEN   'EMPNAME ASC' THEN EMPNAME

              WHEN   'EMPADD1 ASC' THEN EMPADD1

              END ASC,

              CASE   @SORTCONDITION

              WHEN   'EMPNO DESC' THEN EMPNO

              WHEN   'EMPNAME DESC' THEN EMPNAME

              WHEN   'EMPADD1 DESC' THEN EMPADD1

              END DESC

OFFSET @STARTINGROW ROWS

FETCH NEXT @PAGESIZE ROWS ONLY

Query Execution Steps

Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree

Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.

Execution time is total time spent during execution of compiled plan.

It is really a good idea to split this kind of stored procedure to sub procedures that would help to reduce the parse and compile time.

--Clear the cache and buffer

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

--How much time a query is taking can be best measured by setting STATISTICS TIME ON

 

SET STATISTICS TIME ON

 

--Execute the Stored procedure

 

EXEC ExecutionTimeTest 'Supervisor', 123037

 

SET STATISTICS TIME  OFF

SQL Server parse and compile time:

   CPU time = 281 ms, elapsed time = 365 ms.

 

SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 209 ms.

 

SQL Server Execution Times:

  CPU time = 421 ms,  elapsed time = 574 ms.

Must Know-Database Design Models & Terminology

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

Conceptual- Involves entity relationship model at high level. Involves relationship defination w.r.t entity. This may constitutes high level entity boxes with rough relationship and missing cardinality and modality.

Logical Model- This contains ER diagram with details of cardinality and modality. Logical relationship with key entities member defined. Can layout normalization and denormalization as per business and application style specifications.

Physical - It replicates logical and it is phyical table and overall database defination in implementation mode.

Some Key Technology Jargaons

• Soft Delete
• Hard Delete
• Back Door Enteries
• Audit Trail
• Master Table
• Transaction Table
• DDL
• DML
• SandBox
• Table Lock
• Primary Key
• Refrencial Integrity
• Foreign Key
• Secondary Key
• Cardinality
• Modality
• Commit RollBack
• Transactions
• Records
• Fields
• Indexing
• Clustered vs Non clustered
• Execution plan
• Db Advisor
• Db Examiner
• SQL Lint
• Database Schema
• Replication
• Cursor
• Transaction Log
• Transaction File System
• Temp DB Shrink
• Partision Table
• Scalar Function
• Aggregate Function
• Joins-Set based query
• ACID
• Atomicity
• Link Server
• RDMS-Relational DB Management System
• Blob- Binary Large object

 

MDTC Transactionscope vs SqlTransaction

Microsoft distributed Transaction Coordinator is inbuilt transaction handling engine where it take care of integrity and execution of transaction scope. Now if we have same database operation then handling transaction integrity within database is the best option but if the transaction and business operation span across multiple database and data source then Transaction scope of MDTC is the best option to do so.

SQLTransaction ADO.net is for database Alternative can be done smartly in stored procedures itself with Begin Trans, Commit and rollback

TransactionScope is Operating system based.
http://codingcramp.blogspot.se/2009/06/how-to-setup-and-use-transactionscope.html

SQL Transaction Example
public void DoWorkSqlTransactions()
{
using (SqlConnection con1 = new SqlConnection("my connection string"))
using (SqlConnection con2 = new SqlConnection("my second connection string"))
{
try
{
con1.Open();
con2.Open();
SqlTransaction tran1 = con1.BeginTransaction();
SqlTransaction tran2 = con2.BeginTransaction();

try
{
SqlCommand cmd1 = new SqlCommand("update ...", con1, tran1);
cmd1.ExecuteNonQuery();

// Don't want select in transaction
cmd1 = new SqlCommand("select ...", con1);

SqlCommand cmd2 = new SqlCommand("insert ...", con2, tran2);
cmd2.ExecuteNonQuery();
tran1.Save("savepoint");
tran2.Save("savepoint");
tran1.Commit();
tran2.Commit();
}
catch (Exception)
{
tran1.Rollback();
tran2.Rollback();
}
}
catch (Exception)
{
// error handling for connection failure
}
finally
{
con1.Close();
con2.Close();
}
}
}
Transaction Scope
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
{}
Just sorted.

MVC Json Hijacking By Phil Haack

I m not going to write the whole story or article from Phil Haack. This is just for my reference and may people out there who is doing similar kind of blunder in implementing data in JSON response array by exposing sensitive data .

Here is what one should be doing when returning JSON response array.

Add [AcceptVerbs(HttpVerbs.Post)] to make it http request return response type.

Why we need this , you have to take little bit extra time to read Phil's blog for more detailed.
[AcceptVerbs(HttpVerbs.Post)]

Cross Site Request Forgery (CSRF) attack

http://haacked.com/archive/2009/06/25/json-hijacking.aspx

http://haacked.com/archive/2009/06/26/too-late-to-change-json.aspx

 

 

jQuery + JSON Action Methods = Cool -Some MVC controller example

It is easy to return a JSON object instead of a view.

   

public JsonResult Create(string CategoryName)
{
    var category = new Models.Category();
    category.Name = CategoryName;
    category.URLName = CategoryName.ToLower().Replace(" ", "-");
    categoryRepository.Add(category);
    categoryRepository.Save();

    return Json(category);
}

 

__defineSetter__ and .__defineGetter in javascript

The definesetter and definegetter reminds of how we used look into MSIL code using disassembler tool. On Similar line in javascript the line interpreter does this.


 if (Object.prototype.__defineGetter__)
    return obj.__defineGetter__(prop, get);
 if (Object.prototype.__defineSetter__)
    return obj.__defineSetter__(prop, set);

Courtesy By






http://whereswalden.com/2010/04/16/more-spidermonkey-changes-ancient-esoteric-very-rarely-used-syntax-for-creating-getters-and-setters-is-being-removed/

As you may have noticed, all examples here use Object.defineProperty in preference to either __defineGetter__ or __defineSetter__, using the latter two only as fallback when the former is absent. While many browsers support these methods, not all do. Object.defineProperty is the future, and it is the standard; Microsoft has even gone on the record to say that they will not implement __defineGetter__ or __defineSetter__ in IE given the existence of the standardized method (props to them for that choice, by the way). For greatest forward compatibility with all browsers, you should use Object.defineProperty if it exists, and only fall back to __define{G,S}etter__ if it does not.
In a distant future we would like to remove support for __defineGetter__ and __defineSetter__, after ES5 adoption has taken off, so as not to distract from the standardized support. The less new web developers have to know about legacy extensions superseded by standardized alternatives, the better. This action is at least several years in the future, likely longer; being able to make the change will require preparation and adjustment in anticipation of that time.



 

ASP.NET MVC Extensibility-Internals of Pipeline Requests.

Today I came across one good reference article in simpletalk. This article gives internal architecture of asp.net pipeline processing for MVC framework. This is something which I always feel was missing in tutorial or documentation site of asp.net. Even the MVC is creating a stir in web development but too less has been revealed in terms of documentation which entails the true story of framework.
https://www.simple-talk.com/dotnet/.net-framework/an-introduction-to-asp.net-mvc-extensibility/

At a very high level, the lifecycle of a request in ASP.NET MVC is:

• The browser sends the GET or POST HTTP request;
• The request is inspected and routed to the right controller and the right action;
• The action collects or creates the data that needs to be returned to the browser;
• This data is passed to the view that renders the response, which is sent back to the browser.

 

DataType Conversion in MVC Razor

The following table lists some common conversion and test methods for variables.

Method	Description	Example
AsInt(), IsInt()	Converts a string that represents a whole number (like "593") to an integer.	var myIntNumber = 0; var myStringNum = "539"; if(myStringNum.IsInt()==true){ myIntNumber = myStringNum.AsInt(); }
AsBool(), IsBool()	Converts a string like "true" or "false" to a Boolean type.	var myStringBool = "True"; var myVar = myStringBool.AsBool();
AsFloat(), IsFloat()	Converts a string that has a decimal value like "1.3" or "7.439" to a floating-point number.	var myStringFloat = "41.432895"; var myFloatNum = myStringFloat.AsFloat();
AsDecimal(), IsDecimal()	Converts a string that has a decimal value like "1.3" or "7.439" to a decimal number. (In ASP.NET, a decimal number is more precise than a floating-point number.)	var myStringDec = "10317.425"; var myDecNum = myStringDec.AsDecimal();
AsDateTime(), IsDateTime()	Converts a string that represents a date and time value to the ASP.NET DateTime type.	var myDateString = "12/27/2012"; var newDate = myDateString.AsDateTime();
ToString()	Converts any other data type to a string.	int num1 = 17; int num2 = 76; // myString is set to 1776 string myString = num1.ToString() + num2.ToString();

Make MVC @helper in more organized way

Step 1 : On The Project Create a New Director App_Code
Step 2 : To App_Code Directory Add a New Razor View Page Name It Content.cshtml
Step 3 : Paste The Helper , Here You can see the Code Modifications 

Content.chtml

@using System.web.mvc;

@helper GenerateHTMLString(string val)
{
 

val

}

http://mvc4beginner.com/Tutorial/MVC-Best-Practice-Managing-Scripts.html
 

Output caching in MVC

Disable MvcHandler.DisableMvcResponseHeader = true

We should place it somewhere to be executed within the Application_Start method. Additionally it makes sense to disable writing the standard MVC response header. Adding specific headers to the response is actually kind of a security issue (not a big one), since we are telling other people about the implementation of our system (here one could spy that obviously ASP.NET MVC is used).

MvcHandler.DisableMvcResponseHeader = true;
This leaves our web app behind with optimized headers .
http://www.codeproject.com/Articles/419054/Practical-ASP-NET-MVC-3-tips

http://www.codeproject.com/Articles/635324/Another-set-of-ASP-NET-MVC-4-tips#tip-01

Precompiled Your MVC View with MVCBuildView Option

The mvc razor view are non compiled markup with code behind interleaved. You'll come to know about your Razor view errors of c# only at runtime. This is area of concerns when you deployed your code in production and you're not sure if this works as expected even though look alike testing has been conducted in staging or UAT environment.
Well I'm sure we'll see this improvement in coming version of MVC framework where we will be having compile time/built time errors in IDE itself.

Now to our rescue a work around.

true in the .csproj.

See http://www.dotnetcurry.com/ShowArticle.aspx?ID=698 for more details.

http://haacked.com/archive/2011/05/09/compiling-mvc-views-in-a-build-environment.aspx

http://geekswithblogs.net/Aligned/archive/2013/05/28/pre-compiling-your-mvc-views.aspx

Visual Studio Magazine's Moving from Partial Views to AJAX Calls

This is one of the good example of replacing partial view and load them dynamically using Jquery Ajax.
There can be scenario where we must be looking at such option.It make use of Embedded Javascript. The article also eloborate on using Json w.r.t backbone.js and knockout.js.

http://visualstudiomagazine.com/articles/2013/05/01/moving-from-partial-views-to-ajax-calls.aspx?sc_lang=en

MVC Repository Design pattern

Its now quite long time I've been exploring various ways to code in MVC razor and I'm now quite confident to take MVC to next level. I personally feel, I m now an intermediate MVC Razor developer. The only point I was missing till now is to work on more structure architecture concerning to separate responsibility within controller and View. So far I can say I'm able to meet that. Now I can make thin controller and can make more structured and modular pattern within my code component.

Must suggest to look for repository Design pattern for MVC if you planning to develop any application using it. This is utmost important we follow right pattern and practice right at the beginning of MVC.

http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

This goes well with IOC and dependency injection if not still you can make use of it at greater extent.

Happy coding.

MVC- Multiple Views with Display Mode in MVC4

Another intersting white paper on MVC internals. I m just wanting them to file this references in my blog so that I can revisit them anytime at my leisure. Well If you're following my blogs you'll definately find some cool knowledge references to MVC.

Discalimer Content taken from below write up to short summary of content given.
As you know, you should also have a file named index.cshtml located under the Views/Home folder in the project. This file will provide the HTML for the browser. In the body of the Index method above you code (or better, you invoke from other components) the logic required to serve the request. If, by executing this piece of logic, data is produced which needs to be embedded in the view, then you pass this data down to the view object by adding an argument to the View method.

Now in ASP.NET MVC 4 there’s an extra piece of magic that you might not know about yet. To experience the thrill of it, you add a new file to the Views/Home folder named index.mobile.cshtml. You can give this file any content you like; just make sure the content is different from the aforementioned index.cshtml.

Read More.

https://www.simple-talk.com/dotnet/asp.net/multiple-views-and-displaymode-providers-in-asp.net-mvc-4/

https://www.simple-talk.com/dotnet/.net-framework/an-introduction-to-asp.net-mvc-extensibility/

MVC Software Design-Convention Over Configuration

In recent time I was just looking for some interesting topic in MVC and I came across the interesting one. I've been using .net for10 years now but never thought of software design that are carved and paved into whole making of framework. Why I chosen .net its simple to use with no configuration unlike Java platform. More productive gain , can built website /software in less man hour as compare to Java platform. I m making a valid point here. the whole point is .net architecture and framework is based on CoC principle.

Must read this.
CoC is analogous to a math axiom—a statement that is accepted as true without requiring proof. A CoC behavior happens because the framework was coded in that way and it does not require any further ad-hoc code or configuration on the developer’s end.

https://www.simple-talk.com/dotnet/asp.net/asp.net-mvc-controllers-and-conventions/

MVC - Domain Model vs View Model vs Input Model

Synopsis Model

Domain Model- Is also called Entity Model or Data model. Now this domain model represents data entity that are mapped to business domain which later consumes and act as a container for presentation and vice versa.

View Model- The data are processed or referenced in presentation layer or view finds its place in View Model as a properties. Now these data can be string, data , int etc.

Input Model- This is rather confusing and we sometimes tends to term it as View Model. But these construct solely based on input data from view by end users to controller and eventually to backend. This is form post or Get or Update/Delete operations.

The below blog post entails the whole story of domain and give examples around to better understand them.
Look also for IModelBinder,Custom DefaultModelBinder and DefaultModelBinder.

https://www.simple-talk.com/dotnet/asp.net/the-three-models-of-asp.net-mvc-apps/

MVC Post -Redirect-Get PRG Pattern

Something worth to share.If you've closely watched and noticed the page refresh /page load of MVC web then you have to understand this pattern of Post Redirect and get-ie. PRG pattern. Now once page is loaded it is method Post in which data get processed and routed back to browser. After this if  user hit f5 or page refresh then page is not post back but indeed GET which is again a safe way to load page without being refresh. This is what MVC smartly do and identify the requests.

https://www.simple-talk.com/dotnet/asp.net/modal-input-forms-in-asp.net-mvc/

MVC -Performance Improvement-Removed unused ViewEngine-WebFormViewEngine Or RazorViewEngine

Nicely explained..
https://www.simple-talk.com/blogs/2013/02/27/tip-2-expanded-remove-unused-view-engines//

Just to tell long story short...

Sometimes we referenced View that may not exist in such case MVC framework throws error and specify that it tried to search view in different possible location but they do not exist,

Say--http://localhost/home
So View engine will try to search View\Home\index.chtml for source file but if the file don't exists it throws exception listing possible extension and location seach
 WebFormViewEngine
~views/home/index.aspx
~views/home/index.ascx
~views/Shared/index.aspx
~views/Shared/index.ascx

 RazorViewEngine
~views/home/index.chtml
~views/home/index.chtml
~views/Shared/index.chtml
so on.
~views/Shared/index.VBhtml

If we're using only RazorViewEngine then we can get rid of rest of ViewEngine just by adding below lines of code in Global.asax

ViewEngines.Engines.Clear();
 ViewEngines.Engines.Add(new RazorViewEngine());
 

MVC -HandleUnknownAction -Avoid Redundant Code Action Method in Controller

HandleUnknownAction:

Just overirde Controller Method HandleUnknownAction- Now we can merge redundant method which does the same thing of loading view .Such view does nothing just rendering view. There can be scenario when the action is called but there is no relevant action defined in controller in such case we can override this method to handle unknown calls.

public class CustomerController : Controller
{
    public ActionResult Index()
    {
        return View();
    }

    public ActionResult Details()
    {
        return View();
    }
   
    public ActionResult Help()
    {
        return View();
    }
}

Just overirde Controller Method HandleUnknownAction- Now we can merge redundant method which does the same thing of loading view .Such view does nothing just rendering view. There can be scenario when the action is called but there is no relevant action defined in controller in such case we can override this method to handle unknown calls.

public class CustomerController : Controller
{
    protected override void HandleUnknownAction(string actionName)
    {
        this.View(actionName).ExecuteResult(this.ControllerContext);
    }
}