#Sql -Transactions Basic

Transactions : A transaction is a set of sql statements that are executed as single sql statement,If the statement executes successfully Commit the transaction else Rollbacks on failure.

Usage :


Begin tran
insert into dbo.sampletable values('',null,GETDATE(),null)
insert into dbo.sampletable values(1,'',null,GETDATE(),null) //Inserting identity value will fail the query.
IF @@ERROR<>0
ROLLBACK
ELSE
COMMIT

In the above code,i am making an insert into an identity column which will fail the query and in turn roll back the transaction and also make a note that none of the rows would be inserted since transaction failed.

 

 

#Sql Basics – joins

Quick Basics of Join.

Types of Join :

1.Inner Join-Intersection of two tables.

2.Left Outer Join-Intersection of two tables + rows in left table.

3.Right Outer  Join-Intersection of two tables +rows in right table.

4. Outer Join-Left join +Right Join ,null values will be substituted when condition is not met.

5.Cross Join – A Cartesian product(No of rows in A * No of rows in B) between two tables,there is no “on” condition.Produces all combination of rows between two tables.

Syntax for cross join :


select * from tablea cross join tableb

(or )

select * from tablea,tableb

6.Self Join : Join among the same table.

 

#Sql Basics -Triggers

Triggers :

A trigger is a special kind of stored procedures that automatically executes when an event occurs in database.You cannot invoke triggers explicitly.

Types of triggers :

1.Instead of Trigger

2.After Trigger(for Triggers)–Does not supports view.

Instead of Triggers :

As the name suggest,you take control over Insert/Update/Delete and you decide if you really want to Delete a table record that has to be deleted even though you give a delete statement over table.


create trigger trigeer_name on your_table 

instead of delete&nbsp;

As

declare @id int;
Begin
select id=d.Id from deleted d;

/**your operation**/

End

After Trigger :

Events that has to occur after Insert/Update/Delete are termed as After Triggers.


Create trigger your_triggername on your_table;

for Insert;

as

begin

select id=d.Id from inserted (inserted is a special table provided by sql)

/**your operation **/

end

How to get column values after/before  statement(Insert/Update/Delete) gets executed in triggers ?

Sql provides special tables for triggers namely inserted,deleted which will hold the records/column values,we can make use of this table in after/instead of triggers.

 

#Common Performance Traps in Entity Framework

I recently read an article from SimpleTalk. It pretty much covered the common mistake and i am listing the one which i commonly do.

Bring only Columns you require,instead of All :

Lets assume we need only first name and last name from the table User  which holds information of user details (First Name, Last Name, Telephone No,Email Id,Location).

we need to write a query to bring all users whose location is  NewYork.

Most Common Query we write:


string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location==Location).ToList();

foreach(var usersinnewyork in result)

{

Console.WriteLine(usersinnewyork.FirstName);

Console.WriteLine(usersinnewyork.LastName);

}

In the above query the result brings all the columns while we need only first and last name.We might not be aware of impact of query we write,but identifying such queries in application would help us to avoid certain performance issues.

Modified Query with Anonymous Object :


string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location ==Location).Select( x=>new 

{
FirstName =x.FirstName,
LastName =x.LastName
});

In the above query we only bring what we need (first and last name).

 

#Basics of Views-Sql Server

Views : They are virtual table and are generally combination of 2 or 3 tables.Being said Virtual they do not occupy space like a normal table.

Indexes on View :

Views are virtual table by default,to use index it’s mandatory to have a physical column.To enable indexes its mandatory to use SCHEMABINDING with views.


create view yourviewname 

with schemabinding&amp;nbsp;

as

select &amp;nbsp;v1.column1 ,v1.column2 from v1&amp;nbsp;

Refreshing Views:

Since views are virtual tables,there might be cases where we need to refresh a view when underlying table used in view changes.

Execute SP_RefreshView "yourviewname"

 

Insert/Update/Delete with Views:

 Single select must be used in view with check option enabled.

#Sql Basics -Union vs Union All

Union – Merges the Content of two or more tables without duplicates.

Union All-Merges the Content of two or more tables which include duplicates.

For union/union all between two or more tables you must have same column name including the data type.

Performance : 

Union All does not requires elimination of duplicate records and the performance is high when compared with Union since it requires an additional operation to remove the duplicate values.

# Dealing with Constructor-Over InJection-Part 1

What is constructor-over Injection ?

while constructor-injection is the most common way to resolve the dependency,there are cases where design of improper system such as violating the SOLID  would lead to have many dependency getting loaded in the constructor.

Example :


public class SampleClass

{

public SampleClass(IDependency1 Dependency1 ,IDependency2 Dependency2 ,,IDependency3 Dependency3 ,IDependency4 Dependency4 ,,IDependency5 Dependency5 ,IDependency6 Dependency6 )

{

//Constructor Over injected as you need 6 Dependency to be resolved when you create instance of SampleClass.

}

}

In the above example to create the instance of SampleClass,it requires 6 Dependency to get resolved.

The above code clearly indicates the following

1.we have violated the SOLID principles,mainly the single responsibility principle,the sample class listed is clearly doing more than one thing.

2.Improper Design of classes.

How to deal with Constructor Over Injection :

Facade Services : You apply the Facade pattern and decompose the constructor arguments as many  as possible.

 

 

#Thread Safety Over Static -Tips

1.Are Static class with Static Method Thread Safe ?

Yes,when the static fields/members are of value type.

No when the static fields/members  are of reference type.

2.Are Static Fields inside a non-static method Thread Safe ?

Yes,when the static fields/members are of value type.

No when the static fields/members  are of reference type.

 

 

 

#Areas in MVC

It is hard for maintaining the controllers and views as project grows,

Areas in MVC provides solution by letting you to have controller,model,view per module.

For instance if you have  four modules,you could have four areas with its own controller,model and view.

Capture.PNG
Areas in MVC

In the example above,There are two modules  ExecutiveSponsors and Users put into an area.

Accessing and Configuring the area:

1.You can define the routes for the controller in the Corresponding AreaRegistration.

2.To access the controller from the browser,you must append the area name before the controller.

i.e http: //yourdomainname /Areaname/Controller

Example :

http://localhost:/ExecutiveSponsor/Sample.