SET NOCOUNT (Transact-SQL)

When SET NOCOUNT is ON, the  number of rows affected  is not returned. When SET NOCOUNT is OFF, number of rows affected  is returned.

Indexing in Sql

An index is used to fast searching  record  with select Query  and where Clause  but its slow down with  input data Insert and  Update and deletes

An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server has to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

Clustered index creates a physical order of rows Basically Primary Key

Nonclustered index is also a binary tree but it doesn't create a physical order of rows.

Unique Indexe  Unique Index does not  allow any dublicate value to  be inserted

create table TestIndex

(MemberId int ,

 Name nvarchar(50),

  Age int )

create unique index U_MemberId

on  TestIndex(MemberId)

Implicit Indexes  Index are automatically created when  primary and  unique   key  are created

Avoid Indexing  

• Index should not used small table
• Index should not be used that column that contains heigh number of null value
• Column that are frequently manipulated  should not be used

One Abstract Classcan Inherit Another Abstract Class

Yes you can inherit an abstract class from another abstract class

namespace ConsoleApplication9
{ 

    abstract class  A
    {
      public abstract  void Method();
    }
   abstract class B:A
 {
        public abstract void MethodB();
 }
     class C:B
    {
        public override void Method()
        {
            Console.WriteLine("A");
        }
       public override  void MethodB()
        {
            Console.WriteLine("B");
        }

    }

    class Program
    {
        static void Main(string[] args)
        {
            C obj = new C();
            obj.Method();
            obj.MethodB();
            Console.ReadLine();
        }
    }

}

Cursors in SQL

A cursor is a temporary work area created in system memory when a SQL statement is executed
a cursor can hold more than one row, but can process only one row at a time

Types of Cursors

There are the following two types of Cursors:

1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor

These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT command.

Explicit Cursor

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

DECLARE  @b INT =0;

DECLARE @MemberId INT   
    DECLARE db_Lotterycursor CURSOR  FOR
SELECT userid   FROM tblUserInfo ORDER BY userid
     OPEN db_Lotterycursor
  FETCH NEXT FROM db_Lotterycursor INTO @MemberId
       WHILE @@FETCH_STATUS = 0
   BEGIN 
    DECLARE @nam NVARCHAR(50)
         SET  @b=@MemberId;
         SET @nam = (SELECT tblUserInfo.AccountNo  FROM  tblUserInfo  WHERE UserID =@b)
         UPDATE Members SET Members.Email  =@nam  WHERE MemberId =@b
      

  FETCH NEXT FROM db_Lotterycursor INTO  @MemberId
  END
 
    CLOSE db_Lotterycursor
        DEALLOCATE db_Lotterycursor

What is difference between Abstract Class and Static Class

CLR marks all 'static' classes as 'abstract & sealed' behind the scene

Abstract Class (Base class): Enables other classes to inherit

Asp .net page life cycle

PreInit   event is the first event fo the page life cycle it check the  IsPostBack Property and determine whether  the page is postback and set theme  and MasterPage

Init: Init event initialize the control property and the control tree is built

InitComplete : InitComplete event allows tracking of view state all the controls turn on view state tracking

PreLoad Its raised when page loads view state and Load PostBackData

Load   Firstly Page calls  the OnLoad Method On Page Objects Then recursively OnLoad for each control is invoked

Load Complete  Page is completely loaded on client side

PreRender Raised  after page object has created all control that are required in order to render page

PreRenderComplete  Raised after each data bound control whose datasourceId property is set calls its databind method

SaveStateComplete  Raised after view stateand control state have been saved for the page and for all control


Unload  this event is first raised  for each control

Difference between union and union all

UNION	UNION ALL
UNION removes duplicate rows.	“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort	“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.	UNION ALL can work with all data type columns.

DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  

DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  

  

INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')  

INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')  

INSERT INTO @Table2 VALUES(1,'Tejas', '88996655')  

INSERT INTO @Table2 VALUES(2,'Purvi', '99986655')  

SELECT * FROM @Table1  

UNION   

SELECT * FROM @Table2  

Find duplicate Record and delete duplicate Record

CREATE TABLE tbl_RemoveDuplicate
(
ID INTEGER PRIMARY KEY
,Name VARCHAR(150)
)
GO

INSERT INTO tbl_RemoveDuplicate VALUES
(1,'ABC'),(2,'XYZ')
,(3,'XYZ'),(4,'RFQ')
,(5,'PQR'),(6,'EFG')
,(7,'EFG'),(8,'ABC')

select Name from tbl_RemoveDuplicate group by Name having count(*) >1

;with cte as
(select Name, ROW_NUMBER()over (partition by Name order by Name desc) as Id from tbl_RemoveDuplicate
)
delete from cte where id>1

What is the difference between “HTML.TextBox” vs “HTML.TextBoxFor”?

Both of them provide the same HTML output, “HTML.TextBoxFor” is strongly typed while “HTML.TextBox” isn’t.

Difference between LEN() and DATALENGTH() in sql server ?

Len will give you how many characters are there in data(Any data type) whereas DataLenth() will give you how much memory required to store the same data

Difference between List and Arraylist

Arraylist is the non-generic  collection while list is generic collection

What is collection in c#

Collections represents group of objects which used to perform various functions such as storing,updaing,retrieving,searching,sorting e.g List<T>

//List e.g
static void Main(string[] args)
        {
            var student = new List<string>();
            student.Add("A");
            student.Add("B");
             foreach (var item in student)
            {
                Console.WriteLine(item);


            }
            Console.ReadLine();
        }

stack e.g
  static void Main(string[] args)
        {
            Stack<int> i = new Stack<int>();
            i.Push(8);
            i.Push(3);
            foreach (var item in i)
            {
                Console.WriteLine(item);


            }
            Console.ReadLine();
        }
// Queue e.g
  static void Main(string[] args)
        {
            Queue<int> i = new Queue<int>();
            i.Enqueue(1);
            i.Enqueue(2);
            i.Enqueue(3);
            foreach (var item in i)
            {
                Console.WriteLine(item);
            }
            i.Dequeue();
            foreach (var item in i)
            {
                Console.WriteLine(item);
            }
            Console.ReadLine();
        }
List,Queue,stack dulicate element  can add

Difference between var and dynamic keyword

Statically typed – This means the type of variable declared is decided by the compiler at compile time.
Dynamically typed - This means the type of variable declared is decided by the compiler at runtime time.

Need to initialize at the time of declaration.
No need to initialize at the time of declaration.

Errors are caught at compile time.
Errors are caught at runtime

Difference Between IQueryable, IEnumerable, And IList In LINQ

 IEnumerable and IQueryable, both are interfaces to a .NET collection
 IQueryable is Subset of  IEnumerable  and inheritance IEnumerable 

 IQueryable executed all the filters on the server-side and fetched the records that are matching all conditions and filters


 the filter is not applied on the server side but first, it fetches all the records from the server and applies on the client side


IEnumerable is useful when we want to iterate the collection of objects which deals with in-process memory


IQueryable is useful when we want to iterate a collection of objects which deals with ad-hoc queries against the data source or remote database, like SQL Server

The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

Row_Number()

Syntax

 Row_Number() Over( partition by clause order by clause)

This function will assign a unique id to each row returned from the query.

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER ( partition by col_value ORDER BY Col_Value) AS 'RowID'
FROM     @Table;   

OUTPUT

Col_Value     RowID
   A                 1
   A                 2
   A                 3
   B                 1
   B                 2
   C                 1
   C                 2

SELECT
      Col_Value,
      ROW_NUMBER() OVER ( ORDER BY Col_Value) AS 'RowID'

FROM     @Table;  


Col_Value     RowID
   A                 1
   A                 2
   A                 3
   B                 4
   B                 5
   C                 6
   C                 7


Rank() 

Syntax
Rank() Over( partition by clause order by clause)

Same  as Row_Number() except  provide same rank for equal rows and also gap  between the different rank we can avoid using Dense_Rank() keyword

Dense_Rank()  Provide same rank for equal rows   function do not have gap alway given consective Rank value

Syntax

Dense_Rank() Over( partition by clause order by clause)

How can improve Performance of stored procedure

• Keep database object name short ,meaningful and easy to remember
• Normalize data atleast upto 3rd form but not at the cost of query performance
• Do not use those column in the select statement which are not required. Never user select * statement.
•  Use primary key in the table to filter the data in where clause. Use execution plan to analyze the query
• Use SET NOCOUNT ON at the beginning of your stored procedures  This statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE It will remove this extra overhead from the network.
• Use table variables instead of temporary tables.

What is Angular Directive

Directive are marker of DOM  tells compiler  to attach a specified behaviour to DOM  element
Angular js come some predefined directive

Directve List

ng-app
ng-model
ng-init
ng-repeat
ng-bind
ng-controller
ng-value
ng-show
ng-hide
ng-disabled
ng-required
ng-click


It will extend the functionality of HTML like range no,input length etc

Bundling and Minification MVC

Bundling is  a technique to improve performance by reducing the number of request to  the server
Instead of fetching all resource one by  one we create bundle and fetch bundle that bundle in one single resource

To add a new bundle we can use BundleConfig  file
In this file we use Bundlecollection  class Which is available in System.Web.Optimization
Bundle need to registered Global.asax


Render Bundle
To Create js bundle we use @Script.Render(path)
To Create css bundle we use @Styes.Render(path)

Minification is the process  of removing unnecessary  data without changing its functionality

This include removing
Comments
Extra Space
convert large variabe to small size

etc

Difference Between TRUNCATE, DELETE, And DROP In SQL

• TRUNCATE and Drop  is a DDL command
• DELETE is a DML command

• DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back

Data Annotations MVC

Data Annotations are attributes which are used to perform server side validations as well as client side   validations

Difference between Encapsulation and Abstraction

Abstraction is used to hiding the  unwanted data and giving only relevant data
Encapsulation means hide code and data to single unit protect from outside world

Abstraction  Outer Layout used to in term of design
e.g outer look of mobile like   screen  and Keypad button to dial the number

Encapsulation   Inner layout used  in term of implementation
e.g Inner implementation detail of  mobile phone screen ,keypad linked  circuit

MVC Life Cycle

 1 -When request hit Server from browser its  come global.asax  first start Application start registered bundle ,route ,filer 


 2 then request come routing
come
 3 MvcHandler

 4 IControllerfactory create  insantance of controller
5 CreateTtempDatProvider
6 Invoke Action Method
7 Authentication Fiter
8 Authorization Filter
9 Modal Binder
10 Execute ActionFilter and ActionMethod
11 execute resulte and execute filter
12 dispose controller

@Html.Raw MVC

@Html.Raw return   html format pass string inside render html format without encoding

    @Html.Raw("<h1>hello word</h1>")

Difference between == and === in JavaScript

The ‘==’ operator tests for abstract equality i.e. it does the necessary type conversions before doing the equality comparison.

But the ‘===’ operator tests for strict equality i.e it will not do the type conversion hence if the two values are not of the same type, when compared, it will return false.


x = 5  
x == 8 return false
x == "5" return true
x == 5  return true

x === 5   return true
x === "5"  return false

Handle Error MVC

Asp.net MVC framework provies a built in filter  to handle exception and this filter is known
HandleError



To use HandleError  in MVC application following three things are required

• Enable custom error in web config
• Add Error.cshtml view in shared folder
• Use HandleError at Action/Controller/Global

        [HandleError ]  // filter at action level
        public ActionResult Index()
        {
            throw new Exception("this is exception");
        }

OutputCache Filter MVC and Location property

• It 's type of Action filter
• This filter is used to cache data of particular action method  for a specific time 
• To set the time use Duration property

       [OutputCache(Duration =20) ]    //Apply filter to Action Method
        public ActionResult Getdate()
        {
            return View();

        }

 //Apply filter to all  Action Method  inside controller
  
   [OutputCache(Duration = 20)]
    public class HomeController : Controller
    {
        // GET: Home
       
        public ActionResult Getdate()
        {
            return View();
        }
        public int gettime()
        {
            return DateTime.Now.Date.Day;
        }

    }

Location Property Caching when we cache data using output cache filter it need some loation  to store that data 

[OutputCache(Duration = 20,Location =System.Web.UI.OutputCacheLocation.Server)]
        public ActionResult Getdate()
        {
            return View();

        }

Fiter in MVC

Filters are attribute which  are used to perform some logic before and  after a Action Method is called
e.g
caching ,error handling,logging,permision many more etc
MVC  4 has four filter
MVC 5 has 5 filter

• Authentication filter
• Authorization filter
• Action filter
• Result filter
• Exception filter

 Note  this  is also order of execution of filter
Can  we create own filters
Yes
There are three place where we can use filters  in  asp.net mvc

• Action Method   : Any Filter apply Action method  work  only this filters
• Controller : Filter may apply  Controler apply all Action Method  with in Controller
• Global  Apply all Controller global.asax file

Authentication Filters

Authentication filter runs before any other filter or action method. Authentication confirms that you are a valid or invalid user

Authorization Filters

Authorization Filters are responsible for checking User Access

Action Filters 

Action Filter is an attribute that you can apply to a controller action or an entire controller

Result Filters

These filters contains logic that is executed before and after a view result is executed

ExceptionFilters

Polymorphism

Polymorphism is often expressed as 'one interface, multiple functions'

Polymorphism can be static or dynamic. In static polymorphism, the response to a function is determined at the compile time. In dynamic polymorphism, it is decided at run-time.
Static polymorhism:Function Overoading,Operator Overloading

Run time polymorphism or method overriding means same method names with same signatures different class.

Function Overlading   One Function Name overloaded with multiple job is kown as Function Overloading

void f1(int z);  //generate error
int f1(int a)

return type does not matter same or different
void f1(int z);  //ok
int   f1(double a)


public   int  print()  tricks
        {
            return 1;
        }

       public  void print(int i, int k, string abc)
        {
            Console.WriteLine("Printing int: {0}", i);
        }
     
      public   void print(double f)
        {
            Console.WriteLine("Printing float: {0}", f);
        }
      public   void print(string s)
        {
            Console.WriteLine("Printing string: {0}", s);
        }

Difference between Array and ArrayList

Array Array is a fixed length data structure whose length cannot be modified once array object is created.
ArrayList   ArrayList is dynamic in nature which means it can resize itself to grow when required.



Arrays can be multi-dimensional
ArrayList is single dimensional.

Iterating over an array is faster than iterating over an ArrayList.Iterating over an ArrayList is significantly slower in terms of performance.


Takes less memory than ArrayList to store specified elements or objects.Takes more memory than the Array to store objects.

Left Join,Right Join,Full Outer Join

CREATE TABLE LEFTTABLE
(
ID INT ,
NAME  NVARCHAR(50)
)
INSERT INTO LEFTTABLE VALUES(1,'MONU')
INSERT INTO LEFTTABLE VALUES(2,'SONU')
INSERT INTO LEFTTABLE VALUES(3,'SYAM')
INSERT INTO LEFTTABLE VALUES(4,'RAM')

CREATE TABLE RIGHTTABLE
(
ID INT ,
DEP  NVARCHAR(50)
)

INSERT INTO RIGHTTABLE VALUES(1,'COMPUTER')
INSERT INTO RIGHTTABLE VALUES(2,'COMERCE')

SELECT L.ID ,NAME ,DEP FROM LEFTTABLE L  left   JOIN RIGHTTABLE R ON L.ID=R.ID
 Out Put

ID	NAME	DEP
1	MONU	COMPUTER
2	SONU	COMERCE
3	SYAM	Null
4	RAM	Null

What will happen if all the three segments of the "for loop" are missing?

It means the condition is true and the loop goes into infinite mode

How do you know how many users are online on a website?(asp.net)

Application Session variable can be used in global.asax to count. Increase variable on Session_Start and decrease variable on Session_End

What is a shared assembly

A shared assembly is an assembly that resides in a centralized location known as the GAC (Global Assembly Cache) and that provides resources to multiple applications. If an assembly is shared then multiple copies will not be created even when used by multiple applications.

Difference Between First() and FirstOrDefault() in LINQ

First()
Member

ID	Name	Year	Address	Income	UserName
1	PQR	2010-2011	C	50000	S123
2	PQR	2012-2013	C	180000	S123
3	XYZ	2013-2014	B	200000	S789
4	ABC	2013-2014	A	350000	S253

Var x=(from m in Member  

Where m.UserName=’S000’  

Select m.Name,m.Income,m.Year ).First()

In The Member table There Is no Such Record Which Will Match the expression so Above Query will Throw: InvalidOperationException: Sequence contains no elements

Var x=(from m in Member  
Where m.UserName=’s123’  

Select m.Name,m.Income,m.Year).First() 

Then You Will get The Result Like:

PQR

2010-11

50000

S123

1. First() returns First Element Of Sequence.
2. First() throws Exception when There IS No element Presnt In Table.

FirstORDefault():

When we Use FirstORDefault () in LINQ in Query Syntax Or Method Syntax, At that Time If we Do not Get any Record Corresponding To Criteria in Where Clause then It Will return Some Default Value (Null)


Var x=(from m in Member  
Where m.UserName=’S000’  

Select m.Name,m.Income,m.Year ).FirstOrDefault() 

In The Member table There Is no Such Record Which Will Match the expression so Above Query will Return Default value as Null but Not An Exception

We Can Handle This Exception In C# by using try Catch like:


try  
{  
   Var x=(from m in Member  
   Where m.UserName=’S000’  
   Select m.Name,m.Income,m.Year ). FirstOrDefault ()  
   If(x!=null)  
   {  
       Console.WriteLine(x.Name);  
   }  
   else  
   {  
     Console.WriteLine("No Record Found");  
   }  
}  
catch (Exception ex)  
{  
   Console.WriteLine(ex.Message);  

}  

1. FirstOrDefault () returns First Element Of Sequence.
   
2. FirstOrDefault () does not throws Exception when There IS No element Present in Table.

View in sql

view is a virtual table based on the result-set of an SQL statement

CREATE VIEW

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

 CREATE TABLE dbo.Employee
 (
        Id   INT IDENTITY NOT NULL,
        [Age]  INT NOT NULL ,
        Name VARCHAR (50)
 )
---------------Create View-------------------
 CREATE VIEW view_Employee
 as 
 select age,Name from Employee
----------------------------------------------
   UPDATE view_Employee   SET AGE = 35   WHERE name = 'Monu';
   insert into view_Employee values(13,'sou')

   delete from view_Employee where age=13
-----------------------------------------------

WITH CHECK OPTION

The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

Updating a View

A view can be updated under certain conditions which are given below −

• The SELECT clause may not contain the keyword DISTINCT.
• The SELECT clause may not contain summary functions.
• The SELECT clause may not contain set functions.
• The SELECT clause may not contain set operators.
• The SELECT clause may not contain an ORDER BY clause.
• The FROM clause may not contain multiple tables.
• The WHERE clause may not contain subqueries.
• The query may not contain GROUP BY or HAVING.
• Calculated columns may not be updated.
• All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.
•