LINQ

It stands for Language Integrated Query. LINQ is a set of features introduced in Visual Studio 2008 that provides the query facilities into .NET framework language like C#, VB.NET.

Basic LINQ Queries 

public class DALEmployee

    {

       //creating the datacontext instance

       DbConnectionDataContext data = new DbConnectionDataContext();

       //**Retrieve data using LINQ Query**

       public List<tbl_Employee> GetAllEmployees()

       {

           var empdata = from emp in data.tbl_Employees

                         orderby emp.EmployeeID descending

                         select emp;

           return empdata.ToList<tbl_Employee>();

       }

       //**Where Condition in LINQ Query**

       public List<tbl_Employee> GetEmployeebyID(int EmployeeID)

       {

           var empdata = from emp in data.tbl_Employees

                         where emp.EmployeeID == EmployeeID

                         select emp;

           return empdata.ToList();

       }

       //**Insert Data using LINQ Query**

       public int InsertData(tbl_Employee employee)

       {

           data.tbl_Employees.InsertOnSubmit(employee);

           data.SubmitChanges();

           return employee.EmployeeID;

       }

       //**Update Data using LINQ Query**

       public int UpdateData(tbl_Employee employee)

       {

           tbl_Employee objEmployee = data.tbl_Employees.Single(course => course.EmployeeID == employee.EmployeeID);

           objEmployee.Telephone= employee.Telephone;

           objEmployee.Address2 = employee.Address2;

           data.SubmitChanges();

           return employee.EmployeeID;

       }

       //**Delete Data using LINQ Query**

       public bool DeleteData(int employeeID)

       {

           tbl_Employee objEmployee = data.tbl_Employees.Single(course => course.EmployeeID == employeeID);

           data.tbl_Employees.DeleteOnSubmit(objEmployee);

           data.SubmitChanges();

           return true;

       }

       //**Retrieve data using SP**

       public void GetdatausingSP(int EmployeeID)

       {

           var returndata = (from c in data.SP_GetEmployeeData(1)

                             select new tbl_Employee

                             {

                                FirstName  = c.FirstName,

                                LastName=c.LastName

                             }).ToList<tbl_Employee>();

       }

      

    }

References:
http://msdn.microsoft.com/en-us/library/bb397926.aspx

Interview Questions and Answers:
http://aspnet-jitendra.blogspot.sg/2013/07/linq-interview-questions-and-answers.html

http://www.indiabix.com/technical/dotnet/language-integrated-query-linq/

WCF- Part1

WCF
WCF is a unified programming model for building service-oriented applications.It unites the following technologies
   •  NET remoting
   •  MSMQ
   •  Web services
   •  COM+

Service-Oriented Application

A service is a program that performs a task and that a client application can communicate with through well-defined messages.

SOAP defines the format of XML request and reply messages and how clients and services encode the data in the messages.

Service-oriented applications are loosely coupled. All communication occurs through messages. SOAP defines the format of messages. 

Endpoint 

An endpoint expresses all the information needed by a client to communicate with a service. An endpoint consists of an address, a binding, a contract and optional behaviours.

Contract – (What)

Contract is an agreement between two or more parties. It defines the protocol how client should communicate with the service. Technically it describes parameters and return value for a method.

Address - (Where)

The address specifies where the service is located (Address/URL). Clients will use this location to communicate with WCF service.

Bindings - (How)

Bindings Determine how Client and Server will communicate in term of transport and encoding and protocols. Bindings are objects that are used to specify the communication details that are required to connect to the endpoint of a Windows Communication Foundation (WCF) service. Each endpoint in a WCF service requires a binding to be well-specified.

Difference between WCF and Web services

• Support for sending messages using not only HTTP, but also TCP and other network protocols. 
• Support for hosting services on hosts other than a Web server. 
• Built-in support for the latest Web services standards (SOAP 1.2 and WS-*) and the ability to easily support new ones. 
• Support for security, transactions and reliability.
• Support for sending messages using formats other than SOAP, such as Representational State Transfer (REST). 

What are the various ways of hosting a WCF service

The following lists several common scenarios for hosting WCF services:

• IIS
• WAS (Windows Activation Service)
• Self-hosting
• Managed Windows Service

Four types of contracts in WCF

1. Service contracts : Describe which operations the client can perform on the service.
2. Data contracts : Define which data types are passed to and from the service. WCF defines implicit contracts for built-in types such as int and string, but we can easily define explicit opt-in data contracts for custom types.
3. Fault contracts : Define which errors are raised by the service, and how the service handles and propagates errors to its clients.
4. Message contracts : Allow the service to interact directly with messages. Message contracts can be typed or untyped, and are useful in interoperability cases and when there is an existing message format we have to comply with.

What Message Exchange Patterns (MEPs) supported by WCF

1. Request/Response

It’s the default pattern. In this pattern, a response message will always be generated to consumer when the operation is called, even with the void return type. In this scenario, response will have empty SOAP body.

2. One Way

In some cases, we are interested to send a message to service in order to execute certain business functionality but not interested in receiving anything back. OneWay MEP will work in such scenarios.

If we want queued message delivery, OneWay is the only available option.

3. Duplex

The Duplex MEP is basically a two-way message channel. In some cases, we want to send a message to service to initiate some longer-running processing and require a notification back from service in order to confirm that the requested process has been completed.

 What is DataContractSerializer and How its different from XmlSerializer?

Serialization is the process of converting an object instance to a portable and transferable format. So, whenever we are talking about web services, serialization is very important.

Windows Communication Foundation has DataContractSerializer that is new in .NET 3.0 and uses opt-in approach as compared to XmlSerializer that uses opt-out. Opt-in means specify whatever we want to serialize while Opt-out means you don’t have to specify each and every property to serialize, specify only those you don’t want to serialize.

DataContractSerializer is about 10% faster than XmlSerializer but it has almost no control over how the object will be serialized. If we wanted to have more control over how object should be serialized that XmlSerializer is a better choice.

Choosing the appropriate WCF binding

(Ref:http://www.topwcftutorials.net/2012/07/WCF-Service-Bindings.html)

Interoperability Scenario:

If we are going to develop a WCF service that will be consumed by non-WCF client applications, then we can expose our service using BasicHttpBinding or WsHttpBinding. So, how these two bindings differ from each other is explain as:

1. BasicHttpBinding is designed to replace ASMX Web services. It supports both HTTP and Secure HTTP. As far as encoding is concerned, it provides support for Text as well as MTOM encoding methods. BasicHttpBinding doesn’t support WS-* standards like WS-Addressing, WS-Security and WS-ReliableMessaging.

2. WsHttpBinding also supports interoperability. With this binding, the SOAP message is, by default, encrypted. It supports HTTP and HTTPS. In terms of encoding, it provides support for Text as well as MTOM encoding methods. It supports WS-* standards like WS-Addressing, WS-Security and WS-ReliableMessaging. By default, reliable sessions are disabled because it can cause a bit of performance overhead. 

3. WsDualHttpBinding has all features of WsHttpBinding with addition that it supports Duplex MEP (Message Exchange Pattern). In this MEP, service can communicate with client via callback. Its basically a two way communication.

4. WsFederationHttpBinding is a specialized form of WS Binding that offers support for federated security.

Single Computer Scenario:

If our WCF service resides on a single computer, then netNamedPipeBinding will be the best choice.

5. NetNamedPipeBinding is secure and reliable binding on a single WCF computer across process communication. It provides support for binary encoding which is the best choice in this scenario and uses named pipes as transport for SOAP messages.

Intranet/Cross Computers .NET Communication Scenario:

If we need to communicate across computers with same .NET technology on intranet, then netTcpBinding or netPeerTcpBinding options are available. It’s basically the replacement or enhancement of earlier .NET Remoting technology.

6. NetTcpBinding supports reliability, transactions and security. It also supports TCP protocol and binary as encoding method. We can say that it’s the most optimized or fastest binding because both client and service are on the same WCF technology. 

7. NetPeerTcpBinding supports features as that of netTcpBinding but it provides secure binding for peer-to-peer environment with WCF Services.

Disconnected Queued Scenario:

8. NetMsmqBinding is required in a cross machine environment with secure and reliable queued communication. This uses MSMQ as transport.

Refer the following

 For further details on WS-* Standards, refer the following article

http://blogs.jinishans.com/2007/03/what-ws-standards-supported-by-wcf.html

References

1. http://www.topwcftutorials.net/2012/07/WCF-Service-Bindings.html

2. http://blogs.jinishans.com/2007/03/what-ws-standards-supported-by-wcf.html

3. http://www.pradyblog.com/index.php/2012/09/03/wcf-faqs-part-1/

How to use the same temporary table twice in a stored procedure

Let's make an example
you  want to use a temporary table in the same Stored Procedure based on different Condition, as below:

IF(Condition)
BEGIN
select something  & Insert into #TempTable
where condition
END
ELSE
BEGIN
select something  & Insert into #TempTable
where condition
END

If you try to compile this kind of code, it will return an error like 'There is already an object named #myTempTable in the database'. SQL doesn't allow you to use the ‘into #TempTable’ twice in the same Stored Procedure.

So the simplest way is, create the temporary table first, then make the if-else condition

DECLARE @VAL INT=1;

--Create temp Table

select * into #Temp  from dbo.tblCountries where 0=1

 --insert records to temp table

IF(@VAL=1)

      BEGIN

            INSERT INTO #Temp

            select *

            FROM dbo.tblCountries

            --Where

      END

ELSE

      BEGIN

            INSERT INTO #Temp

            select *

            FROM dbo.tblCountries

            --Where

      END

DROP TABLE #Temp

Coded UI Test: Cannot perform 'SetProperty of SelectedItem with value "Mrs" on the hidden control

During the playback in a Web test I got the following exception:
Cannot perform 'SetProperty of SelectedItem with value " " on the hidden control. Additional Details:
TechnologyName:  'Web'
ControlType:  'ComboBox'
Id:  'ddlTitle'
Name:  'ddlTitle'
TagName:  'SELECT'
I am using VS2010 & IE-9, same code works on an another machine which has got IE-8
Or 
While recording, navigation to a new page, followed by some action results in the below error. No action gets recorded:
“The following element is no longer available :IE web control”

Simple fix for this error

This is a security update issue on Internet Explorer. Find the below link

http://blogs.msdn.com/b/visualstudioalm/archive/2013/09/17/coded-ui-mtm-issues-on-internet-explorer-with-kb2870699.aspx 

SQL SERVER – Difference between Temp table,Table variable and CTE

To store some data temporarily, you can use either temporary table or table variable or CTE. By seeing these tables we get a question, what is the suitable option for our scenario.
Here, I am going to explain the difference between each of them.

Temporary table
Temporary tables are created in the tempdb. These tables are like normal tables you can have constraints and index.
This has two types:
     1. Local Temporary table- These Tables are created with single hash ex: #Temp. This table exists for a particular session. This table created in one session and cannot be accessed in other session.

     2. Global Temporary table-These Tables are created with double hash Ex: ## Temp. This table is accessible in all the session until the session the table was created is not closed.

Session is, when you opens a query window on management studio. Each query window you open is a session. 

Table variable:
This is just like a variable. This table exists for a particular batch of execution. It gets dropped once it comes out of batch. Table variable allows creating primary key at the time of declaration but can not create Non clustered index. This is very important detail to note that table variable is not affected from transaction.

CTE
CTE is used when you want the result set to be used in the consecutive queries more than once or if you need recursive queries. CTE does not store data in tempdb rather it uses memory to store data. You cannot create any index on CTE.
More info can be found in the following link:
Common table expressions (CTE)

Note:
     • Temp Table and Table Variable,both are created in TempDB and not in memory
     • Scope of Temporary table is within the session
     • Scope of table variable is within the batch
     • Table variable is not affected from transaction

References:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

http://skatageri.blogspot.com/p/temp-tabletable-variablecte.html

http://support.microsoft.com/kb/305977

How to debug stored procedures in SQL server management studio

SQL Server 2008 introduced "TSQL Debugger" feature to debug the stored procedures. This allows tracking or watching the things inside logic.

In this article, I am going to demonstrate the steps to debug the stored procedures in SQL server management studio

To Begin, Create stored procedure which you want to debug. Here I have created following stored procedure:

CREATE PROCEDURE  [dbo].[SP_TestDebug]

@Input1  INT,

@Input2  INT,

@Output INT out

AS

BEGIN

     

         IF ( @Input1 IS NULL ) SET @Input1 = 0

         IF ( @Input2 IS NULL ) SET @Input2 = 0

        

         SELECT @Input1 + @Input2 as Count

END

 After creating stored procedure, write an execute procedure statement and add breakpoints.

DECLARE @Result INT

EXEC SP_TestDebug 10, 20,@Result OUTPUT

         

After that, click on debug button or press F11 / Alt + F5.

To go next lines press F11 key or click on step into button. Control will automatically move to code of stored procedure as shown in the following figure:

        

SQL server management studio allows to edit the value of variables in debugging mode.