Project and folder structure in MVC4

Here is project and folder structure offered by MVC4. Please click on http://www.codeproject.com/Articles/492833/Deep-Dive-into-ASP-NET-MVC-4-Project-Items to view the information

How to override IIS error pages with the custom pages in MVC

First we should turn off custom error pages in <system.web> section:

<system.web>

    <customErrors mode="Off" />

</system.web>




And then enable it <system.webServer> section:




<system.webServer>

    <!-- Custom Error Pages -->

    <httpErrors errorMode="Custom" existingResponse="Replace">

      <remove statusCode="404" subStatusCode="-1" />

      <remove statusCode="500" subStatusCode="-1" />

      <error statusCode="404" path="/Error/404" responseMode="ExecuteURL" />

      <error statusCode="500" path="/Error" responseMode="ExecuteURL" />

    </httpErrors>

</system.webServer>

Explain transactions and transaction modes in sql server?

A transaction is a set of dml operations which must be make perminent as a whole or must be undone as a whole

Sql server runs in following transaction modes.

1.Autocommit
2.Implicit transaction mode.
3.Explicit transaction mode.

AutoCommit: If sql server runs in autocommit mode then the transactionwill be started automatically when user submits dml command and transaction ends with commit automatically.
In Autocommit mode user cannot control transactions


Implicit Transaction mode : In this transaction starts automatically with any dml command but transaction ends with commit or rollback

Ex

updaet or sql statement--transaction starts
commit/rollback -- transaction ends

Explicit transaction mode : In explicit transaction mode the transaction starts with 'begin transaction' command and the transaction ends with 'commit transaction' or 'rollback transaction'

begin transaction
update statement
commit/rollback transaction


set implicit_transaction on --> it sets the implicit transaction mode on.

Explain about the cursor?

cursors are used to process multiple records(row by row processing)

A cursor is a pointer or handler to context area which stores records returned select statement.

Using cursors in a single request we can get required data from server can be loaded into client side temporary memory called context area.

Steps to create a cursor
1.declare a cursor

syntax: declare cursorname cursor for select statement.
Ex
Declare c1 cursor for select eno, ename, sal from emp

Some memory is created or allocated when we declare a cursor. that memory is called context area.

2. Open a cursor

Syntax: open <cursor name>
open c1;

when we open a cursor.
1.select statement is submitted to db server.
2dbserver executes the select statement and returns data and data returned by select statement is loaded into context area.
3.cursor will point to a context area.

3.Fetch the records from cursor

Syntax:
Fetch first\next\last\prior\absolute\relative n from <cursorname> into variables

Ex

Fetch next from c1 into @eno, @sal, @comm

Fetch statement can fetches only one record at a time to process multiple records fetch should be executed number of times
Fetch statement should be placed inside a loop.
4.Closing a cursor

syntax:
close cursorname;
ex
close c1;

5. Deallocate cursor

When the cursor is closed the point to context area is removed.
when the cursor is de allocated the memory allocated for cursor is released.

deallocate cursorname
deallocate c1;


Ex

declare @ename varchar(50)
declare @sal smallmoney

declare c1 cursor for select ename,sal from emp
open c1
fetch next c1 into @ename,@sal
while(@@Fetch_status=0)
begin
print @ename +'earns' +cast(@sal as varchar(20))
Fetch next from c1 into @ename, @sal
end
close c1
deallocate c1

Differences between clustered index and non clustered index in sql server?

Non-Clustered Index : If the order of values in index and order of values in the table are not same then it is called non clustered index.

Clustered Index: If the order of records in the table and order of the records in the index is same then it is called clustered index.

Non-Clustered Index : Non clustered index will be created automatically one you create unique key on a column.

Clustered Index : Clustered index will be automatically once you create a primary key.

We can have only one clustered index for a table.

we can create up to 249 non clustered indexes on a table.

Non-Clustered Index : The logical order of the index does not match the physical stored order of rows on disk.

Clustered Index : The logical order of the index matches the physical stored order of rows on disk.

Clustered Index is faster than non-clustered index. 
Because the leaf level of a clustered index is the actual 
data and the data is resorted in case of clustered index. 
In case of non-clustered index the leaf level is actually a 
pointer to the data in rows. The data is stored in one 
place, the index in another, with pointers to the storage 
location of the data. The items in the index are stored in 
the order of the index key values, but the information in 
the table is stored in a different order.

What is the trigger and explain the types of triggers?

A trigger is an operation that is executed when some kind of event occured in database. It can be an object or data change.

Types of triggers

DML Triggers

     After Triggers(only on table)

     Instead of triggers(table and views)

DDL Triggers

SQL CLR triggers

 After Trigger: If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

Ex

create trigger trg1 on emp

after insert,update,delete

as

begin

if datename(dw,getdate())='sunday'

begin

rollback

raiseerror('Today is sunday',15,1)

end

end

Instead of trigger : If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN

DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification

IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

Rules of triggers

cannot create or modify dbobjects.

cannot perform administrative tasks

cannot pass any parameters

cannot directly call triggers.

Advantages of triggers

-----------------------

Triggers are useful for auditing data changes or auditing database as well as managing the business rules.

Can access both new values and old values in data base when going to do any insert, update and delete.

Disadvantages of triggers

Triggers hide db operations

It effects performance

DDL triggers can be created in database or server. If you want to monitor table creations and drops you can use ddl triggers

Joins available in sql server.

Equi join or inner join
Non-equi join
Self join
Outer join
Cross join

Inner join : join the table based on the common column(s) available in both the tables.
ex
select e.ename,d.dname from emp e inneroin department d on d.deptno=e.deptno

non-equi join: joining 2 tables not based on the common column.
ex
select e.name,g.grade from emp e innerjoin grade g on e.sal between e.lowsal and e.highsal

self join: Joining a table to itself is known as self join
it is performed if it has self referential intigrity

ex

select x.ename,y.ename as mgrname from emp x join emp y on x.managerid=y.empid

outer join : returns unmatched records from the tables.
Left outer join and right outer join

cross join: It returns cross product of 2 tables.
if we submit the select statement with out join condition then sql server performs cross join.

select e.ename, d.dname from emp e dept d