ASK DOT NET

PIVOT Query for Dynamic format (Dynamic)

2021-12-12T20:39:00.000-08:00

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)

2021-12-11T05:49:00.004-08:00

--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)

2021-12-11T05:46:00.001-08:00

--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

2019-08-18T10:23:00.001-07:00

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

2019-08-05T22:54:00.002-07:00

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

2019-08-01T22:43:00.001-07:00

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

2019-08-01T21:45:00.000-07:00

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)

2019-07-25T06:10:00.003-07:00

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

2019-07-25T06:07:00.001-07:00

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

2019-07-25T05:58:00.001-07:00

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

2019-07-25T05:29:00.001-07:00

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:

Implicit Cursor
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

2019-07-25T05:12:00.002-07:00

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

2019-07-25T04:51:00.001-07:00

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

2019-07-24T22:07:00.003-07:00

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

2019-07-24T09:09:00.002-07:00

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”?

2019-07-23T11:11:00.002-07:00

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 ?

2019-07-23T11:11:00.000-07:00

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

2019-07-23T08:50:00.002-07:00

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

What is collection in c#

2019-07-23T06:40:00.001-07:00

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

2019-07-23T06:21:00.001-07:00

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

2019-07-23T06:14:00.001-07:00

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()

2019-07-18T07:47:00.001-07:00

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

2019-07-18T07:44:00.001-07:00

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

2019-07-18T05:59:00.000-07:00

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

2019-07-17T09:09:00.001-07:00

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