Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Windows Communication Foundation (WCF)

Definition:
Windows Communication Foundation (WCF) is a framework in Microsoft .Net for building connected, service-oriented applications.

Description:

Using WCF web service, you can send data as asynchronous messages from one service endpoint to another.  It is unified programming model introduced in .Net Framework 3.0. WCF is a combined feature of Web Service, Remoting, MSMQ and COM+. WCF provides a common platform for all .NET communication.


ABC of WCF (Address, Binding, Contract):

Address: where this WCF service is hosted .Client will use this URL to connect to the service.
Binding: how client communicate with WCF service.
Contract: what operations available in WCF service.  

Some advantages using WCF service over Classic ASMX web services:

a. WCF services provide better reliability and security in compared to ASMX web services.
b. Provides transaction feature.
c. Small changes in the configuration will meet your requirements.
d. Support hosting in IIS,WAS(Windows activation service),Self hosting, Windows Service.
e. Support different types of bindings((TCP/IP, HTTP,HTTPS , PIPE, MSMQ etc.).
f. Better Exception handling.
g . Supports multi-threading by using ServiceBehaviour class.
h. Improved performance compared to ASMX web services.


Self Join

Self Join:

Let’s explain the need for a self join with an example,

 Employee Table:


 

I want to retrieve employee’s data for country India.

We can retrieve data using classic sub query use but this is not efficient solution.

SELECT * FROM [Employee] WHERE COUNTRY IN (SELECT Country FROM [Employee] WHERE EmployeeName='Nagnath')

What if I want to check manager name for employee?

Answer: Efficient solution is self join   

Definition: A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table. And that is exactly why it is called a self join – because of the fact that it’s just the same table being joined to another copy of itself rather than being joined with a different table.

Note: for self join use aliases otherwise the column names would be ambiguous.

Self join example:

As with any join there must be condition upon which self join is performed (i.e. inner join, outer join)

SELECT e1.*
FROM employee e1, employee e2
WHERE e1.Country = e2.Country
AND e2.EmployeeName='Nagnath';

 


 

 

Find managers for employee we can do this by self join:

SELECT e1.EmployeeName,e1.Country,e1.PrimaryLanguage,e1.Gender ,e2.EmployeeName as Manager
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.ID



 

 

Transaction Isolation levels in SQL Server

Transaction Isolation levels in SQL Server:

To ensure quality of your data in RDBMS transaction isolation level is important.

This article will cover the five transaction isolation settings - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION.

Syntax:

SET TRANSACTION ISOLATION
LEVEL
    {READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]
 

 

READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

 For more info ….http://www.codechef4u.com/post/2015/04/05/read-uncommitted

READ COMMITTED:

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

REPEATABLE READ:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

 For more info ….http://www.codechef4u.com/post/2015/04/07/repeatable-read1

 SERIALIZABLE:

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads.

For more info ….http://www.codechef4u.com/post/2015/04/07/serializable

 

SNAPSHOT:

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

 

To use the snapshot isolation level you need to enable it on the database by running the following command


Syntax: 


ALTER DATABASE DatabseName

SET ALLOW_SNAPSHOT_ISOLATION ON

 

 Example:

 

ALTER DATABASE LocalTest 

SET ALLOW_SNAPSHOT_ISOLATION ON

 

--Query1  

BEGIN TRAN 

SELECT * FROM Employee 

WAITFOR DELAY '00:00:10' 

SELECT * FROM Employee 

ROLLBACK

 

--Query2

INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)

 

VALUES ('Anushka', 'England','English','London',5)