PIVOT Query for Dynamic format (Dynamic)

 

create table tbl_Dummy(ID int identity, ROWID int,VALUE nvarchar(100))

insert tbl_Dummy values(1,'+')

insert tbl_Dummy values(2,'IDIB000F508')

insert tbl_Dummy values(3,'IDIB')

insert tbl_Dummy values(4,'00F508')

insert tbl_Dummy values(5,'INDIABANK')

insert tbl_Dummy values(6,'FARRUKHABAD')

declare @cols nvarchar(max),@query nvarchar(max);

select @cols=STUFF((select ','+QUOTENAME([ROWID]) from tbl_Dummy for XML PATH('')),1,1,'')

print @cols

select @query='select * from (select ROWID,VALUE from tbl_Dummy)as s PIVOT(MAX(VALUE) FOR [ROWID] IN ('+@cols+')) AS pvt'

EXEC SP_EXECUTESQL @query

OUTPUT :

PIVOT Query for static format (value int)

 

--create table tbl_invoice(ID int identity, ROWID int,Amount int)

--insert tbl_invoice values(1,100)

--insert tbl_invoice values(2,300)

--insert tbl_invoice values(3,345)

--insert tbl_invoice values(4,11)

--insert tbl_invoice values(5,567)

--insert tbl_invoice values(6,543)

select * from (select id,Amount from tbl_invoice)as s PIVOT(SUM(Amount) FOR [id] IN ([1], [2],[3],[4],[5],[6]))AS pvt

OUTPUT :

PIVOT Query for static format (value varchar)

--create table tbl_Dummy(ID int identity, ROWID int,VALUE nvarchar(100))

--insert tbl_Dummy values(1,'+')

--insert tbl_Dummy values(2,'IDIB000F508')

--insert tbl_Dummy values(3,'IDIB')

--insert tbl_Dummy values(4,'00F508')

--insert tbl_Dummy values(5,'INDIABANK')

--insert tbl_Dummy values(6,'FARRUKHABAD')

select *

from (select ROWID,VALUE from tbl_Dummy)as s PIVOT(MAX(VALUE) FOR [ROWID] IN ([1], [2],[3],[4],[5],[6]))AS pvt

OUTPUT :

 

User Control and Custom Control Asp.net

User Control is a Container contain various Server Control and these control  use web page using drag and drop  extension is ascx

User Control  can create  individual assembly  but Custom Control create DLL and add VS tool box

Routing in MVC 4 and 5

•  Routing  is a pattern matching system
•  Routing maps incoming request (from browser) to a particular resource(controller and action   Method)

  Every Request come routing   match Route Table then  call particular  controller

 Route Table

  We define route for each action method

  All the routes  are stored in route table

  Each incoming request mapped to this route table

There are 2 type use Routing in MVC Application

Traditional Way

Attribute routing

                                                     Traditional Way

In  a traditional   way  routing define route particular file routeconfig.cs with in app-start folder
Routeconfig.cs  Code

   public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "allstudents",
                url: "students",
                defaults:new {controller= "Student" ,action= "GetAllStudent" }
             
           );
            routes.MapRoute(
                 name: "Students",
                 url: "students/{id}",
                 defaults: new { controller = "Student", action = "GetSingleStudent" },
                   constraints: new { id = @"\d+" }
            );
            routes.MapRoute(
              name: "StudentsAddress",
              url: "students/{id}/Address",
              defaults: new { controller = "Student", action = "StudentAddress" }
         );
         routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
            );
           
        }
    }

Difference between Static Constructors and Non-Static Constructors

A static constructor is used to initialize any static data, or to perform a particular action that needs to be performed once only. It is called automatically before the first instance is created or any static members are referenced.
class SimpleClass { // Static variable that must be initialized at run time. static readonly long baseline; // Static constructor is called at most one time, before any // instance constructor is invoked or member is accessed. static SimpleClass() { baseline = DateTime.Now.Ticks; } }

• A static constructor does not take access modifiers or have parameters.
• A class or struct can only have one static constructor.
• Static constructors cannot be inherited or overloaded.

Non-static constructors are used to initializing the non-static members of the class

Times of Execution: A static constructor will always execute once in the entire life cycle of a class. But a non-static constructor can execute zero time if no instance of the class is created and n times if the n instances are created.

SQL Server Isolation Level

Read Committed

Read UnCommitted

Repeatable Read

Seriazable

Snapshot

Read Committed

READ Comitted  take only Committed value if any transaction pending or incomplete wait for till operation complete

Read Uncommitted

If any table is (updated Insert delete update) and transaction is waiting for complete or roll back  uncomitted  value display  it is called dirty read

if we want to read only committed rows use  keyword with(nolock)

  select * from Emp with(nolock)

http://www.besttechtools.com/articles/article/sql-server-isolation-levels-by-example

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