Saturday

Top 10 Frequently asked SQL Query Interview Questions

Top 10 Frequently asked SQL Query Interview Questions
04:15 by Anjan Kant

Introduction

In this article, I am giving a few cases of SQL inquiries which is frequently approached when you go for a programming interviews, having maybe a couple year experience on this field. Regardless of whether you go for Java engineer position, QA, BA, supports professional,project manager or some other specialised position, may questioner anticipate that you will answer essential inquiries from Database and SQL. It's likewise clear that in the event that you are working from maybe a couple a long time on any venture there is great possibility that you run over to deal with database, composing SQL queries to insert, refresh, erase and select records. One straightforward yet viable approach to check competitor's SQL aptitude is by soliciting these sorts from basic inquiry. They are neither  complex nor big, yet they cover all key idea a software engineer should think about SQL.
Top 10 Frequently asked SQL Query Interview Questions

Earlier articles on Interview Questions and Answers frequently asked on MVC and OOPS as below:

SQL Query Interview Questions and Answers

• How to find second highest salary of Employee by SQL?
Second highest salary of an employee can be found in many ways, one can use either SQL Join or Sub-query in order to solve this problem.
select MAX(Salary) FROM tblEmployee WHERE Salary NOT IN (select MAX(Salary) from tblEmployee);

• How to find maximum salary from each office by SQL query?

One can find the maximum salary for every office by gathering all records by DepartmentID and afterward utilizing MAX() function to compute greatest salary in each group or each office.
SELECT DeptID, MAX(Salary) FROM tblEmployee GROUP BY DeparmentID.

• Compose SQL Query to show the present date.

SQL has inbuilt function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, different sellers like Oracle and MySQL also has comparable capacities.
SELECT GetDate(); 

• Compose a SQL Query to check whether date is of given format or not.

SQL has IsDate() function which is utilised to check passed value is a date or not of determined arrangement, it returns 1(true) or 0(false) in like manner.
SELECT  ISDATE('09/09/2017') AS "MM/DD/YYYY";

• Compose a SQL Query to print the name of the employee whose DOB is between 09/09/2016 to 09/09/2017.

This SQL question is precarious, however you can use BETWEEN condition to get all records whose date fall between two dates.
SELECT DISTINCT EmployeeName FROM tblEmployees WHERE DOB  BETWEEN ‘09/09/2016’ AND ‘09/09/2017’;

• Compose a SQL query to seek out names of employee begin with ‘T’ .

-- this will search only employee name starts from character 'T'
SELECT * FROM EmployeeName WHERE EmployeeName like 'T%';
-- OR you can also write a query to find all EmployeeName those who have character 'T' 
SELECT * FROM EmployeeName WHERE EmployeeName like '%T%';

• What is SQL Server Agent?

SQL Server agent plays a vital role within the every day tasks of a info administrator (DBA). Its purpose is to ease the implementation of tasks for the DBA, with its full-perform planning engine, that permits you to schedule your own jobs and scripts.

What is Trigger?

Trigger permits us to execute a batch of SQL code once AN insert, update or delete command is dead against a selected table. Triggers square measure special varieties of keep procedures that square measure outlined to execute mechanically in situation of or once knowledge modifications. They will be dead mechanically on the insert, delete and update operation.

• What is a Temporary table in SQL Server?

More or less, a temp table is a transitory storage structure. It implies you can utilise a temp table to store information incidentally so you can control and change it before it achieves its destination format.

• Types of Temporary Tables?

  • Local Temp Table
  • Global Temp Table
Local temporary tables can be available to current user connection and they are automatically deleted once the user connection discontinue from database. Local temp tables are used with (#) sign.
Global Temporary tables are like permanent tables in database and it is available to any user by any connection and it can be deleted only when all connections are close.

Conclusion

Despite the fact that requesting that hopefuls compose SQL query is a superior approach to check one's SQL abilities, some of the time it likewise pays to make theoretical inquiries just to check whether knows about fundamental ideas or not, especially amid telephone interviews.

Relevant Reading

Sunday

SQL PERFORMANCE: HOW TO IMPROVE SQL QUERY PERFORMANCE

SQL PERFORMANCE: HOW TO IMPROVE SQL QUERY PERFORMANCE
07:03 by Anjan Kant

INTRODUCTION

SQL query performance improvement is an extremely intriguing topic amongst developers and the client group. Clients dependably needs a quick reaction on their data recovery actions and developers set forth their earnest attempts to give the information in the most brief time, be that as it may, there is no clear approach to characterise what is the best performance. At some point it's easily proven wrong what is good and what is bad execution of a question however generally on the off chance that you take after prescribed procedures amid development, you can give the best inquiry reaction to clients and avoid such discussions about optimal performance.

HOW TO IMPROVE SQL QUERY PERFORMANCE

There are multiple approaches to improve SQL query performance, which falls under different classes like re-composing the SQL query, creation and use of Indexes, management of statistics, etc.
Here are some SQL best practices to ensure query optimisation:
Avoid multiple joins: Avoid writing a SQL query using multiple joins with outer joins, cross apply, outer apply and other complex sub queries.
Eliminate cursors: Remove cursors from query and opt for set-based query as it is more efficient than cursor-based.
Avoid using non-correlated Scalar Sub query: Re-write query to eliminate non-correlated scalar sub query as a separate query and store output in a variable.
Avoid Multi-statement Table Valued Functions: Avoid Multi-statement TVFs as they are costlier than inline TFVs.
Clustered and Non-Clustered index: Create Clustered and Non-clustered index in SQL. These allows SQL server to rapidly and proficiently discover rows associated with key values.
Restrict size of working data set: Analyse the tables utilised as a part of the SELECT statement to check whether you can apply filters in the WHERE clause of your statement. An example case will be when a query initially functioned well when there were just a few  thousand lines in the table. As the application grew the query slowed. The answer might be as basic as limiting the query to look  at the present month's data. When you have queries that have sub-selects, apply filters to the internal statement of the sub-selects rather than the external statements.
Eliminate unnecessary tables: Writing SQL statements is a procedure that generally takes various iterations as you write and test your SQL statements. Amid development it is conceivable that you add tables to the query that might not have any effect on the data returned by the SQL code. By deleting these unnecessary tables you decrease the degree of processing the database has to do.
Exclude prefix “sp_”: Avoid prefix “sp_” with user defined stored process name since SQL server search the user defined process in the master database before in the current session database.
UNION ALL in place of UNION: UNION ALL does not need to make the additional stride of checking the result sets and separating just the unique values, so use UNION ALL over UNION, since it doesn't need to sort the result set.

SQL Performance

Specify All Primary Keys and Foreign Key Relationships: Primary keys and foreign key relationships that are effectively characterised help ensure that you can compose optimal queries. One common consequence of incorrect relationships is adding DISTINCT clauses to dispose of excess data from result sets.
Examine Your Server Specifications and Performance: You should have a database maintenance plan with focus on index management and database reduction. Your database server should have required RAM to support SQL Server. SSIS ETL processes should run on an ETL or staging server if they are memory/CPU intensive for long periods of time.
Use Stored Procedures or Parameterized Queries: The SQL server saves execution plans for stored procedures and parameterized queries under most circumstances. This allows them to be reused on later calls.

Add Schema Name before your SQL object name
To improve your SQL query performance, you are required to apply schema name followed by  “.”
 -- dbo is here used as schema name
SELECT col_1, col_2, col_3 from dbo.tblMyTable;
-- Should Avoid T-SQL statement like this
SELECT col_1, col_2, col_3 from tblMyTable;
Best Practice by Applying SET NOCOUNT ON
After applying SET NOCOUNT ON, it is quite sure to improve your SQL statement performance. SQL Server returns no of rows affected after statements SELECT, INSERT, UPDATE and DELETE statement, after applying SET NOCOUNT ON we can stop it.
CREATE PROCEDURE USP_MyFirstProcedure
AS
SET NOCOUNT ON
BEGIN
.
.
.
END
Always Apply TRY-Catch
By practising TRY-Catch, we can cut deadlocks caused by complex queries.
BEGIN TRY  
     { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
     [ { sql_statement | statement_block } ]  
END CATCH  

CONCLUSION

These are some quick and simple techniques for enhancing query performance for some long-running process. While these systems won't have any significant bearing to each issue you may experience but, they will help in a few cases.

Relevant Reading

.Net Core: What Is New In ASP.NET Core 2.0

.Net Core: What Is New In ASP.NET Core 2.0
04:25 by Anjan Kant

Introduction

ASP.NET is the next evolution of Microsoft for the web pages that are supposed to be server driven. Rather than modifying and amending the ASP and adding new features and functionalities into it, new software called the ASP.NET has come up. It is completely and overhauls as it provides an interface for programming that lets the developers keep coding in an advanced way for the internet. These are the developers who are familiar with the concepts of Visual Basic as well as the other tools that are based on the development of relations between client and server. The most well-known providers of hosting services support the technologies that have a .NET extension; they support both ASP.NET core 2.0 and ASP.NET core 3.5.  The new .net core 2.0 features are also supported by these providers.

Things to know about the latest ASP.NET Core 2.0:

In the latest ASP.NET core 2.0 hosting those developers who have a past record of developing different stuff in the world of client and server are allowed for transferring their skills seamlessly to the web. Those developers and programmers who also have been writing programs in different languages including Java, Visual Basic, C++, etc. for the purpose of development on the internet are allowed for using the already existing components. This lets them keep working with their web pages of ASP.NET core and the web forms as well. In the latest release of ASP.NET, these functionalities have been enhanced to a great level.
What Is New In ASP.NET Core 2.0

New features added in ASP.NET core:

Support for Razor Pages

The support for Razor pages is one of the features that have been recently added to the functionality of ASP.NET core. MVC has the same features as the Razor pages.

New framework capabilities

The APIs, as well as the ability for referencing the complete Framework Libraries, has been expanded in the .NET core 2.0.

New OS addons

There is an expanded support of Operating System.

New Optimisations

Now .NET core is supported by the Visual Beginners All-purpose Symbolic Instruction Code. It allows the users to make console applications and the class libraries.

Csproj is the new game

The project files of xproj are to be replaced by the csproj’s new version.

Legacy Support

The MS builds have started working again in the new version.

New updates for visual studio

There are certain tooling enhancements done by Visual Studio 2017 in the new version of .NET.

Conclusion

You should keep over viewing ASP.NET Core 2.0, and its latest versions so that you do not miss out any of its new functionalities. Recently, the tools for developers and the integration of the database for the latest version of ASP.NET have also been designed in such a way that it becomes very easy to use. By following the easy steps, you can go for .net core 2.0 download and further explore about the software.

Suggested Reading

OPTIMAL PERFORMANCE: UNION VS UNION ALL IN SQL SERVER

OPTIMAL PERFORMANCE: UNION VS UNION ALL IN SQL SERVER
03:12 by Anjan Kant

Introduction

From time to time and there is a need to join information from numerous tables or perspectives into one extensive data set. This might be for tables with comparable data inside a similar database or perhaps there is a need to consolidate comparative data crosswise over databases or even across servers. In this article we will analyse how to use UNION versus UNION ALL commands and how they contrast. In my previous article, I explained differences between a clustered and a non-clustered index.

Optimal Performance Comparison Union vs. Union All

The UNION command is utilised to choose the related data from two tables, which resembles a JOIN command. In any case, when utilising UNION order, all the chose columns should be of similar data type. With UNION, just distinct values are chosen.
UNION ALL command is equivalent to UNION command, aside from that UNION ALL chooses every values. Hence, it does not remove any duplicate values.
Since this does not evacuate duplicate values this procedure is speedier, but rather on the off chance that you don't need duplicate records you should utilise the UNION command.
The decision is easy if you know the answer to the Question "Would i like to take out duplicate rows when I join 2 result sets with this operation?”
On the off chance that yes, at that point use UNION . In any case, know that a UNION will make the additional stride of checking the outcome set and giving you ONLY unmistakable rows. So it will perform slower on the grounds that it needs to sort the result set.
On the off chance that its ensured that your two result sets which you need to join won't have any overlapping rows, or you couldn't care less if there are duplicate/over lapping rows, at that point you should use UNION ALL. Since UNION ALL does not need to make the additional stride of checking the result sets and separating just the unique values, so UNION ALL is generally preferable performing over UNION, since it doesn't need to sort the result set.

Video: UNION VS UNION ALL IN SQL SERVER

Creating Database Tables For Check Comparison


create table compareUnionvUnionaAll (col varchar(10))

insert into compareUnionvUnionaAll values ('technology')
insert into compareUnionvUnionaAll values ('crowds')
insert into compareUnionvUnionaAll values ('sql')
insert into compareUnionvUnionaAll values ('.net')

create table compareUnionvUnionaAll1 (col varchar(10))

insert into compareUnionvUnionaAll1 values ('android')
insert into compareUnionvUnionaAll1 values ('MEAN')
insert into compareUnionvUnionaAll1 values ('sql')
insert into compareUnionvUnionaAll1 values ('.net')
List Records From Both Tables
SELECT * FROM compareUnionvUnionaAll;
SELECT * FROM compareUnionvUnionaAll1;

Results Overview

Table Results Overview

Lets Execute Union All Command

select * from compareUnionvUnionaAll
Union ALL
select * from compareUnionvUnionaAll1

Result Overview of Union ALL Command

Result Overview of Union ALL Command

Lets Check Execution Plan For Union ALL Optimal Comparison

Check Execution Plan For Union ALL Optimal Comparison

UNION Command Results Overview

select * from compareUnionvUnionaAll
Union
select * from compareUnionvUnionaAll1
UNION Command Results Overview

Lets Check For Union Optimal Comparison

Check For Union Optimal Comparison

I think you may notice that UNION query cost is more than UNION ALL which is marked red (63%).

Conclusion

UNION and UNION ALL collated as they are completely apart from each other, they both have different purposes.

Relevant Reading

Angular 4: What is New in Angular 4

Angular 4: What is New in Angular 4
03:41 by Anjan Kant

INTRODUCTION 

In the realm of web application development, Angular is viewed as a standout amongst other open-source JavaScript frameworks. Angular 2 was released on September 14, 2016 and roughly 6 month after the release of Angular 2 , Angular 4 was made available.The final version was released on March 23, 2017. Let's examine why this update was released and what it adds to web development.

What is New in Angular 4

What is new in ANGULAR 4 and how it differs from ANGULAR 2?

When contrasted with Angular 2, there are heaps of new things added. New components as well as a few changes that enhanced old abilities. So how about we proceed onward to see the list.

  • Compact and Faster: With Angular 4, projects will expend less space and run faster than past variants.
  • View Engine size reduced: Size of view engine is reduced by approximately 60 percent.
  • Animation package: Angular 4.0 have pulled the animations out of @angular/core and set them into their own package. This means that if we don’t use animations, the extra code will not end up in production bundles.
  • *ngIf and *ngFor: NgIf syntax is extended to support the else clause to show the template when the condition is false.
  • Angular Universal: With Angular Universal, it's conceivable to render Angular applications outside of the program, for example specifically on the web server.
  • Compatible with Type Script 2.1 and 2.2: Type security of Angular applications and the speed of ngc-Compiler have been elevated.
  • Templates: Source maps are for templates too.Template Compiler will create source maps  for templates.

ANGULAR 4 Tutorial

The Video tutorial on Angular 4 is available on the YouTube channel Technology Crowds link is here.

Conclusion

Developers who have knowledge of AngularJS and Angular 2 will find the new update easy and helpful to use. With this new update Angular has shown the world that they are always ready to listen to developers’ community.

Suggested Reading

Thursday

What are the differences between a clustered and a non-clustered index

What are the differences between a clustered and a non-clustered index
06:42 by Anjan Kant

Introduction

Hope you liked my previous article on Understanding The Relationships in SQL Server. In this article, I am going to discuss clustered and non-clustered indexes in SQL. An index is a disk structure related with a table or view that accelerates the extraction of rows from the table or view. It contains keys created from one or more columns in the table or view. These keys are stored in a B-tree structure that allows SQL Server to rapidly and proficiently discover the rows associated with the key values.
differences between a clustered and a non-clustered index

SQL supports two types of indexes:

  • Clustered index
  • Non-clustered index

Clustered index

Clustered indexes sorts and stores data in table or view based on their key values. There is only one clustered index per table and this table with clustered index is known as clustered table.

Non-Clustered index

Non-clustered indexes have a separate structure from data rows. A non-clustered index comprises of non-clustered index key values. Each one of these key value entry has a pointer to the data rows that includes the key value. It is important for columns that have repeated values.

Difference between Clustered and Non-clustered index in SQL

The difference between clustered and non-clustered indexes is one of the most asked questions during interviews.
Given below are some of the differences between clustered and non-clustered indexes:
  1. Clustered index sorts data rows whereas non-clustered doesn't.
  2. One table can have one clustered index, but there is no such condition on non-clustered index.
  3. In many databases, a clustered index is directly made on primary key.
  4. Clustered Index is more faster to non clustered index because non clustered  physically stored in index order. 
  5. Non clustered index is more faster for insert and update rather than clustered index.
  6. Clustered index is stored logical so it don't require extra storage.

Conclusion

That’s all on difference between clustered and non-clustered indexes in SQL. Both indexes are extremely critical in SQL.

Relevant Reading

Wednesday

How to avoid duplicate records in SQL view using Entity Framework

How to avoid duplicate records in SQL view using Entity Framework
09:25 by Anjan Kant

Introduction

Sometimes while using SQL we experience that SQL query returns same or duplicate results. Since we expect and need a single result from the query, it is a problem. Here we will discuss what causes duplicate records and how to avoid them in SQL view using Entity Framework.

You can also know about how to Fix SQL error: 26 - A network-related error occurred in SQL Server by reading my previous article.

Duplicate rows in Entity Framework SQL View 

In Entity Framework primary key is known as entity key. Entity Framework auto set fields as entity key for those not null column and return the row that match those entity key that causes the problem.
The problem is on the application side Entity framework mapping, as object mapping rules in Entity framework are different with database. The Entity framework does the mapping on application memory with accordance to its designed logic when the query results returns from database. Entity Model has a unique way of handling rows with equal primary key values. To fix issue duplicate records in SQL view using Entity Framework, you need to write below code:
 How to avoid duplicate records in SQL view using Entity Framework

You can set AsNoTracking option directly on your view to resolve this issue.

context.myviewname.AsNoTracking().Where(x => x.ColumnName != null);

Conclusion

To avoid this problem the primary key columns should be chosen correctly. If primary key couldn't be chosen correctly due to null-values, we add a column to our view that always have a unique value for each row and make it primary key in entity model and set AsNoTracking. This is how explained  How to avoid duplicate records in SQL view using Entity Framework.

Relevant Reading

Saturday

HTML: Difference Between Id And Name In Html

HTML: Difference Between Id And Name In Html
04:02 by Anjan Kant

INTRODUCTION 

Web development is a very vast topic as it covers a lot of materials, and even sometimes professional web developers cannot remember all of it.
Here we will be discussing the difference between name attribute and ID attribute in HTML.
Difference Between Id And Name In Html

NAME ATTRIBUTE

Name attribute in HTML indicates the name of an <input> element. The Name attribute is used to reference form data after a form is submitted or to reference elements in JavaScript. The name attribute is used for posting or sending data in a form submission in DOM (document object model).

ID ATTRIBUTE

ID attribute indicates a unique ID for HTML element. The ID attribute is mostly used in CSS, and by JavaScript who controls the element with the unique ID. The ID of a form has nothing to do with the data and information contained in it.
Both name attribute and ID attribute appear to have same purpose that of giving an identifier. The distinction is a name attribute exchanges transfers program to server and can be different in relation to the ID. ID is relied upon to be unique within the page.

CONCLUSION

Name and ID vary because the server is expecting the similar name from various forms in the similar document. The ID must be unique, the name most certainly not.

Suggested Reading

Key Relationships: Understanding The Relationships in SQL Server

Key Relationships: Understanding The Relationships in SQL Server
01:29 by Anjan Kant

Introduction
A web application is a client-server software application in which the user interface runs in a web browser.Modern web applications today interact with databases , usually with a language called SQL. SQL (Structure Query Language) is a language designed for interacting with relational database management systems (RDBMS) like SQL Server, MySQL, Oracle, Sq Lite etc. In my earlier article, I explained about how to fix SQL network error 26.

What is THE RELATIONAL MODEL?

The Relational database management systems are based on relational model. The relational model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic. It is a prescription for how to represent and manipulate data. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and allow database management software ensure that it describes data structures for storing the data and retrieval procedures for answering queries.
Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in an SQL database scheme corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates.
Database designs are closely associated with database relationships, the association among two columns in a single or more tables.In SQL server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table using database relationships.Now you must be wondering what is the key? Well Key is a set of one or more columns whose combined values are unique among all occurrences in a given table. A key is the relational means of specifying uniqueness.

What are the different types of relationships in SQL?

  • One-to-One
  • One-to-Many
  • Many-to-Many

One-to-One Relationships

This is relationship between the primary and foreign keys relationships with primary key relating to foreign key only one record. A One-to-One relationship means that we have two tables that have a relationship, but that relationship only exists in such a way that any given row from Table A can have at most one matching row in Table B.
A real world example of this could be the relationship between a person and a drivers license. A person cannot have more than one driving licence and in reverse a licence cannot be owned by more than one person. This is a one-to-one relationship. Other examples could be: Person-to-mailing address , school-to-principal , person-to-passport,etc. It is called one-to-one because you can have only one record pointing to one other record.
one-to-one relationship
A generic example of a one-to-one relationship.

One-to-Many Relationships

This is the most commonly used type of relationship.This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records.The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table.
The common example of a one to many relationship is when we're talking about Users and Addresses. A User can have one or more addresses (mailing and billing address).The key here is that any one address can only belong to one user and only one user.
A one-to-many relationship
A one-to-many relationship from the perspective of TABLE B

Many-to-Many Relationships

In a Many-to-Many relationship, a record in Table B can potentially belong to several records in Table A. And vice versa, a record in Table A can potentially belong to several records in Table B. A simple many-to-many relationship includes two dimensions.
The many-to-many database relationship is used when the rows in the first table can map to multiple rows in the second table and those rows in the second table can also map to multiple different rows in the first table.

Common examples of Many-to-Many include:

  • Doctors have many patients; patients have many doctors.
  • Customers have many bank accounts; bank accounts might belong to more than one customer.
  • Students are enrolled in many courses; each course has many students.

many-to-many relationship
A typical example of a many-to-many relationship

Conclusion

There are, without a doubt, any number of ways in which you can relate the tables in these examples , depending on each table's role within a given database. The manner in which we use the examples here is not important; But the technique we use establish the relationship between these tables is. Once we learn these techniques, we can identify and establish relationships between the tables .

Relevant Reading

Sunday

Error: CS1617: Invalid option 6 for langversion

Error: CS1617: Invalid option 6 for langversion
00:39 by Anjan Kant

Introduction

I was upgrading my MVC app from MVC 4 to MVC 5 in visual studio 2013 then eventually got message CS1617: Invalid option '6' for /langversion. I looked into issue very closely and then finally come up with the right solution. I am sharing the resolution with you all as given below. In my earlier article, I discussed also How to implement custom error pages using MVC application using Visual Studio.
Error CS1617 Invalid option 6 for langversion

Error Description

Error: CS1617: Invalid option '6' for /langversion; must be ISO-1, ISO-2, 3, 4, 5 or Default

Resolution

I have just added langversion:5 in my application web.config, it was earlier langversion:6 and it resolved my issue. You just need to lookup into system.codedom and modify compilerOptions attribute from langversion:6 to langversion:5 and modify it accordingly. I have provided web.config code part needed to change as below:

 
    
      
        
      
      
          
      
    
  

Conclusion

While we are working on real time world application then we need to upgrade/change our existing application code to meet up real time challenge, sometimes changes can direct into new challenges. In the same way, today here, I have provided a resolution of error CS1617: Invalid option 6 for langversion.

Relevant Reading

Saturday

MVC Compression: How to use GZIP Compression in ASP.net MVC

MVC Compression: How to use GZIP Compression in ASP.net MVC
09:01 by Anjan Kant

Introduction

GZIP compression can help to our website to reduce its bandwidth usage. In this article, we can incorporate GZIP feature in our MVC app. We can enable GZIP header through our website, alternatively we can also enable GZIP through our website IIS. Here describing, how to use GZIP compression in our ASP.net MVC app.

Already Discussed on ASP.Net MVC Security & Performance

Main benefits of GZIP compression are under below:

  • It helps to reduce sizes of our website pages.
  • It helps to increase speed of web pages.
  • It mounts to cost-benefit ratio high.

Namespace:

We need to add two namespaces before to write code of GZIP compression.

using System.Web.Mvc;
using System.IO.Compression;

Code of GZIP Compress under FilterConfig.cs

Write this code under FilterConfig.cs in App_Start folder of your MVC App.

public class CompressAttribute : ActionFilterAttribute
    {
        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {

            var _encodingsAccepted = filterContext.HttpContext.Request.Headers["Accept-Encoding"];
            if (string.IsNullOrEmpty(_encodingsAccepted)) return;

            _encodingsAccepted = _encodingsAccepted.ToLowerInvariant();
            var _response = filterContext.HttpContext.Response;

            if (_encodingsAccepted.Contains("deflate"))
            {
                _response.AppendHeader("Content-encoding", "deflate");
                _response.Filter = new DeflateStream(_response.Filter, CompressionMode.Compress);
            }
            else if (_encodingsAccepted.Contains("gzip"))
            {
                _response.AppendHeader("Content-encoding", "gzip");
                _response.Filter = new GZipStream(_response.Filter, CompressionMode.Compress);
            }
        }
    }

Call Compress Attribute in MVC Controller

Now call Compress attribute on your controller action to get done this functionality in your MVC application.

Working Sample:

Here, I am showing statistics of my web page difference between zipped and unzipped webpage.

Unzip Webpage:

unzipped Webpage

Unzip Webpage

Requests 34
Size124 KB
Finish5.33s
DOContentLoaded617 ms
Load689 ms

Gzip Webpage:

Gzip Webpage
Gzip Webpage

Requests 34
Size123 KB
Finish1.36s
DOContentLoaded523 ms
Load582 ms

Finally, you can find difference between unzip and zip web pages. It is very required that your web pages should be Gzip enabled through IIS or visual studio project so that it will increase performance of your web pages in web browser (Chrome, IE, FF, Opera etc.). Here, I have explained how to use GZIP compression in ASP.net MVC app.

[Compress]
public ActionResult Index()
{
    /// write here your logic to pull data from database
    return View();
}

Conclusion

GZIP compression helps to our site pages and other resource files before sending them over to the program. This radically decreases exchange time since the documents are considerably littler. Here, I have explained how to use GZIP compression in asp.net MVC.

Monday

MVC Custom Error: How to implement custom error pages using MVC

MVC Custom Error: How to implement custom error pages using MVC
06:20 by Anjan Kant

Introduction

Custom Error pages can make your MVC application more elegant and informative. I often see many developers more struggles for hours to implement this functionality in ASP.Net MVC application. I’ll throw full light on all aspects how to implement MVC custom error pages. I have here demonstrated very common errors of MVC application as listed below:
  • Not Found: Whenever page is not founmd (MVC 404 Page).
  • Access Denied: Whenever we are trying to access folder (CAS Code Access Security) permissions or someone like same.
  • Internal Server Error: This error is related to internal server error. 
  • Error Occurred: If any other error occurred in our MVC application.
MVC custom error page
MVC custom error page
You may conquers this example very easily, if once you go through also this example because it is extended from the earlier example. If you are already regular reader of Technology Crowds then it is OK.
You may also track Exception code and refer to custom page error in your ASP.Net MVC application.
Namespace Used:

using System.Web.Routing;

Add below code into Global.asax

You are required to add below code into Global.asax page on Application_Error page in your MVC application to get working custom error page.
protected void Application_Error(object sender, EventArgs e)
{
    var httpContext = ((MvcApplication)sender).Context;
    var currentController = " ";
    var currentAction = " ";
    var currentRouteData = RouteTable.Routes.GetRouteData(new HttpContextWrapper(httpContext));

    if (currentRouteData != null)
    {
    if (currentRouteData.Values["controller"] != null && !String.IsNullOrEmpty(currentRouteData.Values["controller"].ToString()))
        {
            currentController = currentRouteData.Values["controller"].ToString();
        }

        if (currentRouteData.Values["action"] != null && !String.IsNullOrEmpty(currentRouteData.Values["action"].ToString()))
        {
            currentAction = currentRouteData.Values["action"].ToString();
        }
    }

    var ex = Server.GetLastError();
    var controller = new ErrorController();
    var routeData = new RouteData();
    var action = "Index";

    if (ex is HttpException)
    {
        var httpEx = ex as HttpException;

        switch (httpEx.GetHttpCode())
        {
            case 404:
                action = "NotFound";
                break;

            case 401:
                action = "AccessDenied";
                break;

            case 500:
                action = "InternalServerError";
                break;
        }
    }

    httpContext.ClearError();
    httpContext.Response.Clear();
    httpContext.Response.StatusCode = ex is HttpException ? ((HttpException)ex).GetHttpCode() : 500;
    httpContext.Response.TrySkipIisCustomErrors = true;

    routeData.Values["controller"] = "Error";
    routeData.Values["action"] = action;

    controller.ViewData.Model = new HandleErrorInfo(ex, currentController, currentAction);
    ((IController)controller).Execute(new RequestContext(new HttpContextWrapper(httpContext), routeData));
}

Error Controller Actions

Here are written controller actions list like Index, NotFound, AccessDenied, InternalServerError etc.
public class ErrorController : Controller
{
    ///     /// Track Common Page Error
    ///     /// 
    [HttpGet]
    public ActionResult Index()
    {
        TempData["error"] = "Error Occurred!";
    return View();
    }
    ///     /// Track Page Not Found Error
    /// 
    /// 
    [HttpGet]
    public ActionResult NotFound()
    {
        TempData["error"] = "Page not Found";
        return View();
    }
    /// 
    /// Track Access Denied
    ///     /// 
    [HttpGet]
    public ActionResult AccessDenied()
    {
        TempData["error"] = "Access Denied";
        return View("Index");
    }

    ///     /// Track Internal Server Error
    ///     /// 
    [HttpGet]
    public ActionResult InternalServerError()
    {
        TempData["error"] = "Internal Server Error";
        return View("Index");
    }
}

Custom Error Razor Views

Below is completely provided code snippet of Razor view engine to display custom error pages to end user. Razor views will remain same to all custom error pages like IndexNotFoundAccessDeniedInternalServerError etc.

@{
    ViewBag.Title = @TempData["error"];
    Layout = null;
}

Error:

@TempData["error"]

Error Description:

@Model.Exception.Message.ToString() @Html.Raw(" ") @Model.ControllerName @Html.Raw(" ") @Model.ActionName

MVC custom error pages

Conclusion

To standardise your ASP.net MVC application, we are all required to customise error pages and show on a particular page with detailed error to make more trustworthy your website in to your online audience. Here, I have lined up all aspects of how to implement custom error pages using MVC application with attached working sample project to setup on your machine to see how it happen in really.

Download Complete Project (visual studio source files)
Download

Saturday

MVC: Sorting Filter in AngularJS using MVC

MVC: Sorting Filter in AngularJS using MVC
03:32 by Anjan Kant

Introduction

I am here fully explaining how to sorting filtering in AngularJS using MVC application. In my recent article, I have already demonstrated Live Character Count using AngularJS

Sorting Filter in AngularJS using MVC

Thursday

AngularJS: Live Character Count using AngularJS

AngularJS: Live Character Count using AngularJS
05:15 by Anjan Kant

Introduction

In my earlier article. I have explained all awesome features of AngularJS MVW Framework, now taking over next steps. Here, I am showing how we can live count of text area characters in AngularJS. Revising again main features of AngularJS.
Live Character Count using AngularJS

Tuesday

Fix SQL error: 26 - A network-related error occurred in SQL Server

Fix SQL error: 26 - A network-related error occurred in SQL Server
08:24 by Anjan Kant

Introduction

This article explains how to configure an instance of the SQL Server Database Engine to listen on a particular fixed port by utilising the SQL Server Configuration Manager to fix SQL error 26. In my earlier article, I have thoroughly highlighted how to get database tables size in SQL Server. The default example of the SQL Server Database Engine responses (listens) especially on TCP port 1433. Named instances of the Database Engine and SQL Server Compact are arranged for dynamic ports. This implies they select an accessible port when the SQL Server administration is started over. When you are interacting with a named instance through a firewall, configure the Database Engine to listen on a particular port, so that the fitting port can be opened in the firewall.

Friday

Know About Transparent Data Encryption (TDE) in SQL Server

Know About Transparent Data Encryption (TDE) in SQL Server
01:59 by Daniel Jones

Introduction

The basic purpose of encryption feature ensures the confidentiality of any digital data stored on a system. Also, the data, which is transmitted through the internet or via network of another computer. Encryption brings data in such a state that it becomes very difficult to read or analyse it. It is not possible for a user to access the encrypted data without access to decryption key/password/certificates. In the following section we will learn what is transparent data encryption (TDE) in SQL Server, the method to enable TDE and also, its advantage and disadvantages.

What Is Transparent Data Encryption

SQL Server has various built-in technologies for data protection, and one of the most essential is Transparent Data Encryption. This feature is introduced in SQL Server 2008 and present in all the later versions for bulk encryption at the database file level, which includes logs, data and backup file. Moreover, to fulfil the demands of corporate data security standards, SQL Server provides the option to enable TDE on the database level or at column/cell level. This feature is completely transparent to your application. Users can even use file level encryption, which is provided by Windows for database files. In the following section we will discuss the method to enable Transparent Data Encryption along with the advantage and disadvantages of TDE.

How To Enable Transparent Data Encryption

These are the mentioned steps you need to perform to enable TDE on a database. You can follow these steps only if, you have the permission to create a database master key and certificates in the master database and also, control permissions on the user database.
  • Firstly, you need to create a master key: It is a very symmetrical to the key that is used to create certificates and also, asymmetric keys.
  • Then, obtain or create a certificate protected by the master key: Certificates can be used for the encryption of data directly or to create symmetric keys to encrypt the database.
  • Generate a key of database encryption for the protection by the certificate.
  • Next, set the database to use encryption: Encryption, for tempdb data, is automatically enabled once you enable TDE on any of the user database. This results in the prevention of temporary objects (used by the user database) from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE.

Transparent Data Encryption (TDE) in SQL Server

It’s very essential to take backup of the keys and certificates. This restores the encrypted database on another instance of SQL Server after restoring the keys or certificates there.
Whenever you try to use a certificate without taking a backup of it, SQL Server gives a warning like this:

The certificate used for encrypting the database encrption key has not been backed up

Pros And Cons Of Transparent Data Encryption

Pros
  • Quite simple to implement.
  • No modification is needed for the application tier.
  • Is invisible to the user.
  • Works with high availability features, such as mirroring, AlwaysOn and log shipping.
Cons
  • Overall database is encrypted not only the data, which is sensitive.
  • There is a small performance impact.
  • File Stream data is not encrypted.
  • Data, which is in motion or held within the application is not encrypted

Conclusion

After considering the need of encryption we have discussed the Transparent data encryption (TDE), which is a feature of SQL Server. We have gone through the process to enable TDE in SQL Server. We have also learned about the advantages and disadvantages of TDE. This article provides a deep understanding of Transparent data encryption in SQL.

Suggested Reading

Thursday

URL Access: How to Prevent Direct URL Access In MVC

URL Access: How to Prevent Direct URL Access In MVC
08:54 by Anjan Kant

Introduction

Here is writing another article to make more secure of your MVC application. I’ll explore all aspects here, how to prevent direct URL access in MVC application. Before to go through this article, you are required to detail about these articles as given below.
  1. Asp.net mvc session management example
  2. Prevent Cross-Site Request Forgery using AntiForgeryToken() in MVC

Namespace Used

To apply this feature into your MVC application is used System.Web.Routing namespace to prevent direct URL access in MVC.
How to Prevent Direct URL Access In MVC

using System.Web.Routing

Apply this feature in FilterConfig.cs file

We have to call this feature under OnActionExecuting of Action filter. We have to apply filter as below written lines to prevent direct URL access in MVC. If we are tempering URL in browser then it will forcibly throw you to Logout action of Home Controller lying under Main area.

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method)]
public class NoDirectAccessAttribute : ActionFilterAttribute
{
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        if (filterContext.HttpContext.Request.UrlReferrer == null ||
 filterContext.HttpContext.Request.Url.Host != filterContext.HttpContext.Request.UrlReferrer.Host)
        {
     filterContext.Result = new RedirectToRouteResult(new
                               RouteValueDictionary(new { controller = "Home", action = "Logout", area = "Main" }));
        }
    }
}

Prevent Direct Access to Class (Apply on Controller Class)

We can apply NoDirectAccess Attribute to Class and it will follow to all containing methods, if any methods accessed directly under the whole controller. It will throw you specified action (here’s throwing Logout action) like MyWebsiteURL.com/Main/PersonalDetail/Index

[NoDirectAccess]
public class PersonalDetailController : Controller
{
      //
      // GET: /Main/PersonalDetail/
      public ActionResult Index()
      {
          return View();
      }
}

Apply NoDirectAccess Attribute to Action

Alternatively, we can apply NoDirectAccess Attribute to specific Action rather than to whole Controller class. Suppose, we are accessing directly like MyWebsiteURL.com/Main/Home/login

[NoDirectAccess]
public ActionResult Login()
{
   return View();
}

Conclusion

I have here demonstrated all necessary steps to prevent direct URL access in MVC to make our MVC application more secured and robust over internet. These are the healthy features to make our MVC application more reliable across the internet.

Tuesday

MVC Session: Asp.net mvc session management example

MVC Session: Asp.net mvc session management example
08:02 by Anjan Kant

Introduction

In this example, showing how to use and validate session (HttpContext.Current.Session) in MVC application. In my earlier article, one of more secured feature to keep up your website healthy cross-site request forgery explained. MVC application has provided us facility to apply filter like
  1.  Authorization 
  2.  Action Filter 
  3.  Result Filter 
  4.  On Error Filter
I am here applying OnActionExecuting filter helps us to manage ASP.net MVC session management whether session is preserving or not, if session is expired, it will not let you access your authorised area and throw away to login area or someone page.
Asp.net mvc session management example

Add below code in FilterConfig.cs under App_Start folder

This code is written under OnActionExecuting in FilterConfig.cs file
public class UserSessionActionFilter : ActionFilterAttribute, IActionFilter
{
    public override void OnActionExecuting(ActionExecutingContext filterContextORG)
    {
        HttpContext ctx = HttpContext.Current;
        if (HttpContext.Current.Session["User"] == null)
        {
            /// this handles session when data is requested through Ajax json
            if (filterContextORG.HttpContext.Request.IsAjaxRequest())
            {
                JsonResult result = new JsonResult { Data = "Session Timeout!" };
                filterContextORG.Result = result;
            }
            else
            {
                /// If session is expired then redirected to logout page which further redirect to login page. 
            filterContextORG.Result = new RedirectResult("~/Main/Home/Logout");
                return;
            }
        }
}

In Global.asax Should register FilterConfig.cs

protected void Application_Start()
{
 AreaRegistration.RegisterAllAreas();
 WebApiConfig.Register(GlobalConfiguration.Configuration);
 FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
 RouteConfig.RegisterRoutes(RouteTable.Routes);
}

Checking Session is expired or not

We have to call action attribute [UserSessionActionFilter] in MVC controller to check whether session is preserving or not. If session is expired it will throw to other page.
[UserSessionActionFilter]
public ActionResult ContactDetail()
{
 return View();
}

Conclusion

This example is showing how to handle session in ASP.net MVC application. This example helps us to asp.net MVC session management example with all required steps.

Suggested Reading