Tuesday, February 13, 2018

Find out who Delete your data from SQL table

Find out who Delete your data from SQL table

Have you lost your data from SQL table ? Have you lost your SQL table ? want to know who ran DELETE or DROP Query on your database ? Then go through this step by step article, it will help you to find the culprit


Last month my friend has called me and said, some has delete his important data from SQL table and now no one confess it. He asked me to search a way to find culprit. After couple of here and there i got a way and now want to share with you. So, let's enjoy this article.

This article will help you find the user who fire DELETE or DROP statement on your table or database

Things we need

To search culprit, we need to read transaction log entries of database. Yes...You heard is right, you can read SQL transaction log data (i.e. LDF file).  let's begin with the steps

  1.  We will create some sample table with data
  2. Delete rows from it
  3. Try to track the user who delete (soft or hard) data entries (Here Soft Delete means delete records using Query and Hard delete indicates delete data using 'DEL' button (or may be with mouse) from SQL table directly)
         (Those who don't know what is LDF) LDF is a file extension for log data files these files are exist with MDF files (which contains actual data). LDF file store all transactions with time stamp and help to recover database in case of data loss.

         Now, to read LDF file we need to use 'fn_dblog' function, (which is undocumented function of SQL), after executing this function on particular database you will able to see live transaction logs and operations executed on that database.

Let's create sample database and table, with the help of following Query

( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 

sample table:

USE [Sample]
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)

Now let's check what has been recorded in LDF logs

run 'fn_dblog' function in Sample database

select * from fn_dblog(null,null)

If you see above result pane, there are almost 35 rows are recorded for just CREATE DATABASE and CREATE TABLE script

Let's insert some rows in table

Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

Track DELETE Activity

Now, just go and delete all rows from database, use below simple query

Delete from Emp

Our Emp table is now empty as, we have delete all the queries

Let's examine the log table, having operation type is 'LOP_DELETE_ROWS', fire fn_dblob function again and see what you get

select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'


Above result pane show us, all the transaction rows which are having 'DELETE' entries on specific database table, you need to search for the your 'specific' table (from where you have lost your data), check out column 'AllocUnitName', this column contains your table name on which 'DELETE' statement has fired.

In our case, Table name is 'Emp', now get the transaction ID for that particular 'table' entry record, execute below query to get record of particular table

select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'


in our case, transaction ID is same, as all entries are deleted with single 'DELETE' statement (at once) (e.g.  0000:0000079f)

with the help of above transaction ID, we will find when our entries are deleted from database. for that purpose we need to search record with operation LOP_BEGIN_XACT, fire below query on database

select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

above query will give you Start time of the transaction

Now we got the exact time when someone fire DELETE query on database, to know the activity End Time, you can try below query

   [Begin Time], [End Time]
    fn_dblog(NULL, NULL)
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

Here is the result of above query

Now let's find who is the culprit, we will find the real database user who fire delete query

Transaction SID column contains encrypted Hexa decimal text which is nothing but the user name who fire 'Delete' query

Fire below query to get [Transaction SID] column with the help of Transaction ID and Operation = 'DELETE'

select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

Output of above query is

Just you need to copy, encrypted hexadecimal contents from [Transaction SID] column and execute below query on master database, as per our result my Hexadecimal string is 0x01


**SUSER_SNAME is the inbuilt function, it just checks security identification number (SID) and back with the login name associated it.

when i run above query i got below output

Yes...we finally got real culprit who fire Delete query

Track DROP activity

Similarly, if anyone DROP your table from database we can track that activity by using following queries,
Let's Drop table with below simple query

Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'
check below query

SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

i got below result

Same as DELETE scenario execute below query on master database, as per our result Hexadecimal string is 0x01


which is the same user 'sa'

So, to conclude

SQL store all its transactions in log table, we can read transaction log file using fn_dblob function, we can do more research on each transaction with the help of this function.  All transactions are logged with different operations,  With the help of SUSER_SNAME function we can easily trace out encrypted user name.

In my next article i will cover deep dive points on 'Reading Transaction Log of SQL (LDF)', so Please stay tuned

and Enjoy this article
**DO NOT alter enties of fn_dblog or DO NOT run these command on production unless you have backup.

*Suggestions and comments are always welcome

-Happy Tracing

Tuesday, November 14, 2017

FIX : A network-related or instance-specific error occurred while establishing a connection to SQL

In this article i explained you to Fix SQL error occurred while establishing a connection to remote computer, so we can say we can resolve error "Network-related or instance-specific error occurred while establishing a connection to SQL Server [2005/2008/2012]"


Couple of years back when i was not much familier with SQL, i try to connect to my SQL Server from my client. I put servername and try to login with SQL Server authentication with user name and password and i got error "A network-related or instance-specific error occurred while establishing a connection to SQL Server...", after spending couple of hours on it i was able to get rid of it, this article is for those who got same error and still they are put their head in SQL to resolve the issue.


Many times we are trying to connect to SQL server with SQL management studio either by express version or by enterprise version and we got following error
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "
Above error is really headache for sql users.
Here is the snap of error

Fix it

To fix the issue we might need to do some configuration changes in SQL management studio (Here i use SQL 2008 for it)

Allow remote connections 
The very first thing you want to check is, your Remote Connections are enabled on your SQL Server database, to check it go through below steps
1. Select SQL server on object explorer -> Right click and click on properties -> select 'connections' -> under Remote server connections sections -> check 'Allow Remote connections to this server'
see below snap

This is the basic and simple check it may resolve your error, if not then continue with this article

Enable TCP/IP protocol
Next step is the enable the TCP/IP, just navigate to 'the SQL Server Configuration Manager' -> in left pane Open 'SQL server network configuration' -> select protocol for SQLEXPRESS in right pane select TCP/IP and make it 'enable'
see below snap

This is one of the helpful step and resolve many users issue, if the problem still continues then continue with this article

Handle Firewall port
Many times your firewall security settings does not allow SQL to pass through, you need to handle that firewall port to work with SQL stuff. To allow in firewall there need to configure couple of settings 
1. set 1433 port in TCP/IP proerpties
2. set Inbound and Outbound rules in windows firewall settings (Applicable for window 7 and above operating system)

set TCP port 1433 in configuration window as below snap shows

Now to set Inbound and Outbound rule we need to navigate to windows firewall, Just Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
go through the following snap sequence it will help you to enable and set the windows firewall settings
Open 'windows firewall with advanced security' screen, select 'Inbound Rule' and click on 'New Rule', see below snap

New Inbound wizard screen' will open, Now in left hand side of the window select 'Rule Types', in right hand side select 'Port' now click 'Next'

In protocol and ports section select 'specific local ports' and give port number as '1433' (default sql port), now click 'Next'

In action section, select 'Allow the connection', and click on Next

In Name section give name and description of the rule, which help us to identify the rule in firewall, now click on Finish

Yes, we are almost done with the settings now close SQL server and restart it again and try to connect, it will resolve your issue.

Summing up

Above error is very basic Many time occur due to different conditions, if you have database installed on SQL 2008 and if you try to connect it with SQL 2005 management console then also same error occurred, when you have install a new instance of SQL server on machine then also this error arise, above are some settings that help you to get rid of the error

Suggestion and Queries most welcome


Monday, August 7, 2017

New Features of C# 7.0


C# 7.0 is planned to release in parts, Microsoft has release Most of its features with Visual Studio “15” Preview 4, it was release in last month (Aug 2016), See Release Post
Code simplification and improve performance are the main key role of this build, Tuples, Pattern matching are some of the really good features introduced. Hope you will enjoy these features and be more productive.
 Let's see new features of C# 7.0

1. Tuples (with types and literals)

Return multiple values from a method is now a common practice, we generally use custom datatype, out parameters, Dynamic return type or a tuple object but here C# 7.0 brings tuple types and tuple literals for you it just return multiple values/ multiple type inform of tuple object. see below snippet

( string, string, string, string) getEmpInfo()
    //read EmpInfo from database or any other source and just return them
    string strFirstName = "abc";
    string strAddress = "Address";
    string strCity= "City";
    string strState= "State";
     return (strFirstName, strAddress, strCity, strState); // tuple literal

//Just call above method and it will return multiple values 
 var empInfo= getEmpInfo();
WriteLine("Emp info as  {empInfo .Item1} {empInfo .Item2} {empInfo .Item3} {empInfo .Item4}.");

In above sample we can easily retrieve multiple values from tuples, but Item1, Item2 name are bit ir-relevant so let's assign some meaningful names before return, see below sample

(string strFName, string strAdd, string strC, string strSt) getEmpInfo()
    //code goes here

//Now when you call method get values with specific name as below 
var empInfo= getEmpInfo();
WriteLine("Emp info as {empInfo.strFName} {empInfo.strAdd} {empInfo.strC} {empInfo.strSt}.");

Additionally you can return their name directly in tuple literal as below

return (strFName: strFirstName, strAdd: strAddress, strCity: strC, strState: strSt);

Tuples are very useful thing where you can replace hash table or dictionary easily, even you can return multiple values for a single key, Additionally you can use it instead of List where you store multiple values at single position.
.NET also has a Tuple type (See here) but it is a reference type and that leads to performance issue, but C# 7.0 bring a Tuple with value type which is faster in performance and a mutable type.
Most of the time we don't want to access whole tuple bundle or we just need internal values then we can use Deconstruction features of C# 7.0, we can easily de-construct a tuple and fetch values that we need, following snippet will clear your doubt

( string strFName,  string strAdd,  string strC, string strSt) = getEmpInfo(); 
Console.WriteLine($"Address: { strAdd }, Country: { strC }");

2. Record Type

C# support record type, which is nothing but a container of a properties and variables, most of the time classes are full with properties and variables, we need lot of code to just declare them but with the help of Record Type you can reduce your effort, see below snippet

class studentInfo
    string _strFName;
    string _strMName;
    string _strLName;
    studentInfo(string strFN, string strMN, string strLN){
        this._strFName = strFN;
        this._strMName = strMN;
        this._strLName = strLN;
    public string StudentFName {get{ return this._strFName;}}
    public string StudentMName {get{ return this._strMName;}}
    public string StudentLName {get{ return this._strLName;}}

In above code we have a class with property, constructor and variable, so access and declare variable i need to write more code.
To avoid it i can use  Record Type in C#, see below snippet

class studentInfo(string StudentFName, string StudentMName, string StudentLName);

That's it and we have Done !
above snippet produce same output as earlier snippet.

3. Minimizing OUT

Out parameter is very popular when we want to return multiple values from method, By nature out parameters are ref type and works as an argument, we can use it easily but the only condition is out variable should be initialized before it passed. see below snippet

class SetOut
    void AssignVal(out string strName)
        strName = "I am from OUT";
    static void Main()
        string strArgu;
        AssignVal(out strArgu);
        // here contents of strArgu is "I am from OUT"

C# 7.0 reduce your pain of writing extra code and you can just pass argument without initialize them, see below snippet

 static void Main()
        AssignVal(out string szArgu);
        // here contents of szArgu is "I am from OUT"

You can either use var as argument type instead to declare them.
Note that variable are used here, are in limited scope only, thus we can not use them outside method

Since we can define variable as argument directly, C# 7.0 gives us freedom to declare them as var also. so you don't need to worry about datatype, see below snippet

static void Main()
        AssignVal(out var szArgu);
        // here contents of szArgu is "I am from OUT"

4. Non-'NULL' able reference type

Null reference is really a headache for all programmers, it is a million dollar exception. If you don't check them you got runtime exception or if you check them for each object then your code goes long and long, To deal with this problem C# 7.0 come with non-nullable reference types
**I think syntax for it yet not fixed still they have release following syntax
'?' is for nullable value-type and '!' is for non-nullable reference type

int objNullVal;     //non-nullable value type
int? objNotNullVal;    //nullable value type
string! objNotNullRef; //non-nullable reference type
string objNullRef;  //nullable reference type

Now look at the following complier effect after we run this snippet

MyClass objNullRef;  // Nullable reference type
MyClass! objNotNullRef; // Non-nullable reference type
objNullRef = null;   // this is nullable, so no problem in assigning
objNotNullRef = null;   // Error, as objNotNullRef is non-nullable
objNotNullRef = objNullRef;      // Error, as nullable object can not be refered
WriteLine(objNotNullRef.ToString()); // Not null so can convert to tostring
WriteLine(objNullRef.ToString()); // could be null
if (objNullRef != null) { WriteLine(objNullRef.ToString); } // No error as we have already checked it
WriteLine(objNullRef!.Length); // No error

5. Local Methods/Functions

Local methods and functions is already there in current version of C# (Yes, we can achieve them using Func and Action types, see here Func  and Action), but still there are some limitations to local method, we can not have following features in it
  • Generic
  • out parameters
  • Ref
  • params
Now with C# 7.0 we can overcome this problems, see below snippet

private static void Main(string[] args)
    int local_var = 100;
    int LocalFunction(int arg1)
        return local_var * arg1;

in above snippet we have define 'LocalFunction' as local function which is inside Main Function 'Main', here we can use out or ref in it.

6. Readability Improvement with Literals

Many times we use literals in code, if they are too long then we might loose Readability,   to sort out such issues C# 7.0 comes with some improvement in Literals. Now C# allows '_' (underscore) in Literals for betterment of understand, it does not effect on its value. see below snippet

private static void Main(string[] args)
    int local_var = 100;
    int LocalFunction(int arg1)
        return local_var * arg1;

**Literals are nothing but a constant value (hard-coded value) which may be with predefined meaning. (Litearls in C#)

7. Pattern matching

C# 7.0 allows user to use pattern in IS statement and with SWITCH statement, so we can match pattern with any datatype, patterns can be constant patterns, Type patterns, Var patterns. following sample snippet will clear your concepts, let's start with IS pattern

 public  void Method1( object obj)
    //following null is constant pattern
     if (obj  is null)  return;
    //datatype pattern, string is datatype that we check directly     
     if (obj  is  string st)
    { //code goes here }

Switch pattern helps a lot as it uses any datatype for matching additionally 'case' clauses also can have its pattern so it bit flexible implementation
see below snippet

class Calculate();
class Add(int a, int b, int c) : Calculate;
class Substract(int a, int b) : Calculate;
class Multiply(int a, int b, int c) : Calculate;
Calculate objCal = new Multiply(2, 3, 4);
switch (objCal)
    case Add(int a, int b, int c):
        //code goes here
    case Substract(int a, int b):
        //code goes here
    case Multiply(int a, int b, int c):
        //code goes here
        //default case

in above sample switch case checks pattern and call 'Multiply' method

8. 'return' by Ref

Have you tried to return your variable from method/function as Ref ? Yes, C# 7.0 allows you to do that. Infect you can pass a variable with Ref return them as Ref  and also store them as Ref, isn't it amazing.
see below snippet

ref string getFromList(string strVal, string[] Values)
 foreach (string val1 in Values)
     if (strVal == val1)
        return ref val1; //return location as ref not actual value

string[] values = { "a", "b", "c", "d" };
ref string strSubstitute = ref getFromList("b", values);
strSubstitute = "K"; // replaces 7 with 9 in the array
System.Write(values[1]); // it prints "K"

In above sample we have find and replace a string, by return a Ref from method.

9. Throw Exception from Expression

You read it right, in C# 7.0 Now you can throw exception from your expression directly. see below snippet

public string getEmpInfo( string EmpName)
        string[] empArr = EmpName.Split(",");
        return (empArr.Length > 0) ? empArr[0] : throw new Exception("Emp Info Not exist");

In above snippet we can directly throw exception from return statement, isn't it really good !

Point to be Notice

All above features are expected to be a part of C# 7.0, yet Microsoft has release some of it with Visual studio 2015 Release 4.
Hope you enjoy these new features of C# 7.0

- Happy Coding

Saturday, March 25, 2017

New features of SQL 2016



SQL Server 2016 was (finally) released on June 1st, 2016 with an initial build number of 13.0.1601.5. Microsoft build SQL 2016 keeps a lot of things in mind like Cloud first, Security enhancement, JSON support, Temporal database support, Row level security, Windows server 2016 connectivity, Non-relational database connectivity (e.g. Hadoop), rich visual effects, etc.
In this article, we will take a walk-through all fresh SQL 2016 features and cover them one by one.

Always Encrypted

 As the word suggests, 'Always Encrypted' feature of SQL 2016 'Always' keeps your sensitive data 'Encrypted' either at rest (local environment) or at remote (Cloud/Azure). It will help to protect data from people who may play around it like DBAs, Cloud operators, high-privileged but unauthorized users.
How It Works
You can set Always Encrypted to individual column (where your sensitive data resides). While configuring columns, you need to specify encryption algorithm and cryptographic keys for data protection. There are basically two keys you need to define:
  1. Encryption Key for column data encryption (It will be used to encrypt data for specific column)
  2. Master Key: for Encryption of column encryption keys
So basically, it's a double encryption protection, only program can access it, client application will automatically encrypt and decrypt data before fetching data from database

JSON Support

SQL 2016 gives direct support to JSON (Java Script Object Notation), SQL has the facility to read JSON format data, load them in table, support index properties in JSON columns.
JSON data will be stored in NVARCHAR type. Due to NVARCHAR type, an application has the following benefits:
  • Already stored JSON data (as text) can be easily migrated on new feature.
  • As NVARCHAR is supported by all SQL components so is the JSON too.
You can easily fetch data FOR JSON from SQL with the below syntax:

SELECT column, expression, column as alias
FROM table1, table2, table3

It is a SELECT command so when we fire the above query, SQL will format each row/cell value and return as JSON object.
SQL has also provided in-built functions for JSON.

Dynamic Data Masking

This is again one of the security features of SQL 2016. As the name suggests, it just wraps MASK on your data, in short, it hides your confidential data that you don't want to display. It just avoids disclosure of your sensitive data.
After masking, SQL User with limited rights will not view original text, he can view only Masked Text, SQL has pre-defined masking functions you just need to apply it on different columns, see below:

Sr NoFunctionsApplied onPlain text (Input)Masking text(output)
1DefaultString, NumberABCDxxxx
2EmailEmail texttest@test.comtxxx@xxxx.com
4Custom StringTextRABBITRXXXX

To apply this on specific columns, you just need to ALTER column with 'MASKED WITH' function name, see below syntax:

//here I used function as Default(), you can change it to any of the above types
ALTER TABLE tablename ALTER COLUMN columnname MASKED WITH (FUNCTION=‚default()‘)

Row Level Security  

This is again one of the security features of SQL 2016. It allows you to secure your data row wise, in short you can define a row, that will be viewed by a particular SQL user only. So depending upon the SQL user access permission, we can restrict row level data, e.g., we can ensure if employees can view only their department data though department table is the same.
To implement Row level security, you need to define Security policy with a predicate and function.
Security policy:
We need to create a policy for security, here is simple syntax:


In the above syntax, FILTER and BLOCK are the predicates that will either FILTER rows and display only those that are available for read or BLOCK rows for write operation.

Function: Function is a simple user defined function, but here are some restrictions for user defined function that are used in Row Level Security syntax:
  • Database modification operations are not allowed
  • OUTPUT INTO clause is not allowed in function
  • Multiple result set should not be returned from function 

Stretch Database

As the name suggests, it gives flexibility to the user. In short, we can store portion of database to remote (Here, we can say cloud/Azure). The portion of data can be called as COLD DATA. (It is useful for those where transactional data needs to be keep for long time as industry requirement.) So we can say it's a cost-effective solution for COLD data storage, your data is available anytime for query and manage. You can access your data without changing queries either it is present on local or at stretch database.
To configure it, you need an Azure account and database instance that you need to stretch. The following snap will clear your idea.


Multiple TempDB

It is always a good practice to have a Multiple Temp data files, if you are working on a big crucial data, up till now (SQL 2014), you need to manually add temp db files to your database but SQL 2016 provides you temp DB configuration settings, in which you can configure Number of TempDB files at the time of SQL installation. Default number of files are 8 with default size of 64 MB will be given.
So you no longer need to configure/create it manually.

Query Store

Up till now, to check Query plan and execution statistics, we need dynamic management views in SQL but neither will it give you Query plan that executed by past/old queries nor will it store them anywhere so that you can review, but SQL 2016 provides you 'Query Store' that takes you through query plan, statistics and Query execution for current and past queries.
To enable it, just right click on database (obviously, you need SQL 2016 SSMS), go to properties. You will see 'Query store' at the left corner, select it and click on Enable 'true' or you can do it using Query as follows:


Temporal Table

Do you want to store history of your SQL table? So you want to review your old records after table updation? Then you can go with this features. SQL 2016 provides record version facility in which it keeps a history of changed record and maintains it for timely analysis. This feature can be useful for Audit, checking data trend, accidental update/delete data and many more.

How It Works

Basically, the system keeps pair of a table for history and adds two additional columns in it named 'SysStartTime' and 'SysEndTime' for start time and end time for row respectively. Live table contains current record of row, whereas history table contains previous record of row. We can fetch data from History table, with the following query:

BETWEEN date1 AND date2
WHERE condition; 

R Introduction

Have you stored statistical data in SQL? Want to use R to analyze it? You export data each time from SQL to R? Then your headache will now be covered in SQL 2016, because it is now with R. You can run R script on SQL. You need to install this feature at the time of SQL setup.

We cannot cover all features in details, maybe I will be planning soon to cover them one by one.
Till then, you can enjoy this article.
Suggestion and queries are always welcome.

Happy querying!

**This article is already published at CodeProject

Thursday, February 16, 2017

Difference between Build, Rebuild and Clean

This article gives you explanation about Difference between Build, Rebuild and Clean

If you are using Visual studio you might have question that what is difference between Build, Rebuild and Clean ? here we are going to understand the diversity between them.

Build :
 When we build any solution then Visual studio will build/compile all assemblies (Dlls and EXE's) that are changed, it is basically incremental or partial process in which only CHANGED assemblies are compiled if there is no changed assemblies it won't get build

Rebuild :
It is full compiled case, in which all assemblies (either changed or not) are deleted and recompiled again, irrespective of changed or not

Clean :
It is not compile case, it just delete all assemblies (Dlls and EXE's) from folder but not compiled them again, it remove all compiled files from previous build

Hope you understand this simple article

Thanks and Happy building


Wednesday, July 20, 2016

ASP.NET MVC Interview Questions and Answers

ASP.NET MVC is getting popular more and more day by day, it has different features, it is popular due to its Modal-View-Controller architecture.
This article contains ASP.NET MVC interview questions that will help you to brush up your asp.net MVC concepts, this is the one point stop where you can get interview breaker Q & A.

Let's go through it

1. What is ASP.NET MVC ?
This is the most common asked question, it is an architectural pattern which separates the UI and code. It's basically divided into Model, View, and Controller section.
- As name suggest, The View is responsible for the look and feel.
- Model is real world object and provides data to the View.
- This is the heart of the architecture, it responsible for request and loading the appropriate Model and View.

2. What are the features of ASP.NET MVC?
- Clear separation of application concerns.
- Supports for Test Driven Development
- Supports Membership and roles, authentication and authorization, provider model and caching

3. What is Routing in ASP.NET MVC?
Routing is very easy in ASP.NET MVC. it shows user friendly URL in application, it is just like a URL rewriting. ASP.NET MVC framework uses a routing engine, that maps URLs to controller classes
see below image in which you can see difference between ASP.NET path and ASP.NET MVC path, We can define routing rules for the engine, so that it can map incoming request URLs to appropriate controller
When URL redirect to another page it collect routing rules that are defined in Global.asax file and process the request

4. How to navigate from One view to another ?
You can easily do it using Hyperlink, by using 'ActionLink' method we can navigate to destination action
see below snippet
<%= Html.ActionLink("Home","GotoDestination") %>

5. What is VIEWDATA in ASP.NET MVC ?
To pass data from controller to view and in next subsequent request, ASP.NET MVC uses VIEWDATA. But it is limited to only server call and to main the state. When i redirect to another page
it gives me 'null' value, it acts between a link in VIEW and CONTROLLER. ViewData being a dictionary object is accessible using strings as keys.
ViewData requires typecasting for complex data type and check for null values to avoid error.
see below sample
//add some value to VIEWDATA
public ActionResult Index()
 ViewData["Text"] = "This is from view data";
 return View();

//access it in dictonary

6. What is VIEWBAG in ASP.NET MVC ?
It is simply same as VIEWDATA, which used to maintain state and transfer values between controller to view and in next subsequent request, just the difference is ViewBag is a dynamic property that takes advantage of the new dynamic features
ViewBag doesn't require typecasting for complex data type, both VIEWDATA and VIEWBAG has short life means, they are 'null' if redirect to another page
see below sample

//add some value to VIEWDATA
public ActionResult Index()
 ViewBag.Name = "This is from VIEWBAG";
 return View();

//access it
7. What is TEMPDATA in ASP.NET MVC ?
TEMPDATA is also dictionary type but it access request data and maintain state between multiple redirect, from one controller to the other controller or from one action to other action.
it uses HTTP request to transfer data. It internally uses session variables. it can be used to store only one time messages like exceptions.

see below sample
//in controller 1
public ActionResult Index()
 var model = new Review()
 Body = "i am controller 1",
 TempData["CON1"] = model;
 return RedirectToAction("Test");

//in controller 2
public ActionResult Test() 
 var model= TempData["CON1"]; 
 return View(model); 
8. How to transfer values in ASP.NET MVC between Controller and View ?
There are following ways to transfer values in ASP.NET MVC
1. ViewData : Transfer values only between Controller to View
2. ViewBag : Transfer values only between Controller to View
3. TempData : Transfer values only between Controller to Controller
4.Session Variables : It is very strong and dynamic way to Transfer values in ASP.NET MVC, it supports values transfer between Controller to Controller, Controller to View and View and Controller
5. Hidden Fields : Transfer values only between View to Controller

9. Is TempData can persist values between multiple request ?
This is very weird question The answer of this question is YES and NO,
YES cause it can persist values between request and NO cause once the value of TempData is read, it is empty

10. What is Model ?
Model is the layer between UI and input, in simple ways it handles all business logic, validation and database part, it manipulates it and saved back to database

11. Can i use ASP.NET MVC in Windows application
No. Model, View, controller framework is used to create web applications only, in windows you can Model, View, Presenter model

12. What is Razor in ASP.NET MVC ?
As word suggest Razor is acts like cleaner, it just trim the coding brackets and allow new style of coding
see below snippet

//in Classic ASP.NET we uses brackets to render variable
<% foreach (var empname in Model)
{ %>
 <li> <% empname.name %> </li>
<% }%>

//in ASP.MVC, Razor cleans unused tags and make it faster
@foreach (var empname in Model)
 <li> @empname.name </li>
13. What is partial view
It is like a user control in asp.net we can use them multiple time, its a reusable view.Partial and RenderPartial methods are used to show partial view
see below snippet
14. What are the different result ASP.NET MVC can produce
Basically ASP.NET MVC produce only one result, that is 'ActionResult' but it has again different sub class like,
1. ViewResult - Get the response and render specific view.
2. PartialViewResult - Renders a specific but partial view to the response
3. EmptyResult - Return an empty result
4. RedirectResult - Returns an HTTP redirection to a given URL
5. RedirectToRouteResult - Depending on the routing engine get the URL and redirect the result
6. JsonResult - Return given ViewData object to JSON format
7. JavaScriptResult - Return code piece of Javascript that can later execute on client machine
8. ContentResult - Return content to the response
9. FileContentResult - Returns a file with content to the client
10.FileStreamResult - Returns a file to the client but in the form of a Stream
11.FilePathResult - Returns a file with path to the client

15. How to add style sheet in ASP.NET MVC
it is same like your classic ASP.NET
<link rel="StyleSheet" href="@Href(test.css)" type="text/css" />
16.What is the HTML Helper in ASP.NET MVC ?
HTML Helper is like a Classic ASP.NET controls but they are very light weight in comparison with traditional controls, an HTML helper does not have an event model and a view state.
all HTML links and HTML form elements are the part of HTML helper class
e.g. see below HTML link control in HTML Helper

@Html.ActionLink("Check this Website", "Newwebsite")
//when we run this code on Browser we get following output in HTML
<a href="Newwebsite">Check this Website</a>
17. What is minification, and why it is necessary ?
Minification is the process of reducing the size of script and CSS files by removing blank spaces, comments. This process removes whitespaces and comments, it will leads to minimize in file size, and thus increasing performance.
see below snippet
//simple javascript code is below
var a = 10;
a = a + 1;
a = a * 2;

//after minification we will get
var a=10;a=a+1;a=a*2;
Now with above code you can see your .js file size get reduced

18.What is Web API 's in ASP.NET MVC ?
- Web API is a new framework for building on HTTP to consuming Services over REST, it support wide range of Web browsers,desktop,mobile, smart phones

19. What is display Modes ?
with the help of Display Modes, Views selected automatically by application depending on the browser. e.g. for desktop browser application return URL as 'Views\test\Login.cshtml' and for same application mobile browsers will redirect to 'Views\test\Login.mobile.cshtml'

Summing up
We have seen very basic and frequently asked Q&A series, In next part of this Article I will cover more complex scenario's about ASP.NET MVC, till then enjoy this stuff.

Comments/Suggestions/Doubts are always welcome


Thursday, May 5, 2016

Check all functionality using Javascript in Gridview

This is a small article that helps you to implement popular functionality of check all, it helps you to put checkbox in gridview view header and on click of that checkbox, all check boxes will checked at once

We have to developed a customized outlook a small interface that take care of all incoming mails. This interface is also used to delete the mails if not necessary.
for that use gridview to show all incoming mails and a facility in the form of checkbox to delete them either one by one or all at once. Now a list of problems come up
- How to add a checkbox in gridview header ?
- How check all columns on single click ?
- Can we do it on client side so we can save additional load on server execuion ?

In this article we go through the concept adding checkbox at gridview header, check/uncheck all checkboxes with the help of javascript on client side
here we go to collect cooking material. This simple demo is ready to use and handy in many codes.

What we need to make it ?
we need a gridview, A auto generated column of checkbox, gridview header temaple to keep checkbox in header and finally a javascript to work on checkboxes

Code in action
- Create a new ASP.NET website
- on default.aspx, add a gridview from toolbox and add asp:TemplateField for checkbox
- Set HeaderStyle, ItemStyle, Row style as desired
- Now we have design our gridview successfully, but how to add checkbox in gridview header ?
- it's simple, add checkbox in under , it will generate a checkbox in gridview header.

Header template

           <asp:CheckBox ID="chkHeaderSelect" runat="server" onclick="return callCheckAll()" />

I have put a code for you to copy it directly, copy the code and add it in ASPX page.
//paste the code in ASPX page
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" BackColor="White"
        BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px" Font-Size="10" 
        GridLines="Both" Width="80%" HorizontalAlign="Center">
            <asp:TemplateField HeaderText="Select" ItemStyle-HorizontalAlign="Center">
                    <asp:CheckBox ID="chkSelect" runat="server" />
                <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="20%" />
                <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="20%" />
                    <asp:CheckBox ID="chkHeaderSelect" runat="server" onclick="return callCheckAll()" />
        <RowStyle HorizontalAlign="Center" />
        <HeaderStyle BackColor="#336699" ForeColor="White" Height="20" />

now, use the javascript to work "checkAll" functionality
see below script snippet

//copy following code in <head> section
  <script language="Javascript" type="text/javascript">
        function callCheckAll() 
          var checkList = GridView1.getElementsByTagName("input");
      var bChecked = false;
      if (checkList[0].checked)
           bChecked = true;
     for (var i = 0; i < checkList.length; i++) 
         //The First check box is Header Checkbox
             var headerCheck = checkList[0];
             var checked = true;
             if (checkList[i].type == "checkbox" && checkList[i] != headerCheck)
        checkList[i].checked = bChecked;

now fill the gridview and Ready for the effect

//Add this code on page load, to fill gridview using Datatable
         DataTable dt = new DataTable();

        //Put some columns in it. 
        dt.Columns.Add(new DataColumn("No", typeof(int)));
        dt.Columns.Add(new DataColumn("Name", typeof(string)));
        dt.Columns.Add(new DataColumn("Address", typeof(string)));

        for (int iCount = 0; iCount < 10; iCount++)
            // Create the record 
            DataRow dr = dt.NewRow();
            dr["No"] = iCount + 1;// i; 
            dr["Name"] = "DemoName" + iCount;//xmn2[1].InnerText;  //value from textbox on screen 
            dr["Address"] = "Address" + iCount;//xmn4[1].InnerText; //value from textbox on screen 

        //Bind the GridView to the data in the data table for display. 
        this.GridView1.Visible = true;
        GridView1.DataSource = dt;
This simple snippet/article really helpful to you to implement checkAll functionality.

Suggestion are most welcome.