Update and Delete table with join condition:

UPDATE
UPDATE tblCalibrationReview
SET poNumber = tblCalibrationReview.[PONumber],
calCertNumber = tblCalibrationReview.[CalCertNumber],
q1Review = (CASE tblCalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END),
followUp = tblCalibrationReview.[Followup]
FROM CalibrationReview AS tblCalibrationReview
LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID
WHERE tblCalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID
_____________________________________________________________________________


Update t1
set t1.DESCRIPTION = t3.DESCRIPTION
from TBL1 t1 inner join TBL2 t2
on t1.UNIQUE_ID = t2.UNIQUE_ID_2
inner join TBL1 t3
on t2.UNIQUE_ID_1 = t3.UNIQUE_ID
 
______________________________________________________________________________
 
DELETE
 
Delete data from table using left outer join
delete t1 FROM projects AS t1 LEFT OUTER JOIN emp_project AS t2 on t1.serial_no= t2.projectid where t1.serial_no=7
what is wrong with query
delete t1,t2 FROM projects AS t1 LEFT OUTER JOIN emp_project AS t2 on t1.serial_no= t2.projectid where t1.serial_no=13
Note:
Your delete statement is incorrect. You've to have two seperate delete statement.
Alternatively you can use Delete Cascade with proper relartionship between two tables
That's what I told you that you can not delete from two tables in single query unless you use delete cascade or have two seperate query

SQL SERVER JOINs

In this article, we’ll see the basic concepts of SQL JOINs. In the later part of the article, we’ll focus on the advanced subject of Self-JOIN and some interesting observations on how inner JOIN can be simulated using left JOIN. The author has tried his best to amalgamate various topics in a single concept.

The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

There are mainly three types of JOIN


1.Inner: fetches data, that are present in both tables
     •Only JOIN means INNER JOIN

2.Outer: are of three types
   •LEFT OUTER - - fetches data present only in left table & matching condition
   •RIGHT OUTER - - fetches data present only in right table & matching condition
   •FULL OUTER - - fetches data present any or both table
    (LEFT or RIGHT or FULL) OUTER JOIN can be written w/o writing "OUTER"    

3.Cross Join: joins everything to everything

Inner JOIN
A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN. This is the default type of JOIN in the Query and View Designer.

Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You can create three different outer JOINs to specify the unmatched rows to be included:

Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.

Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.

Additional Notes related to JOIN
The following are three classic examples to demonstrate the cases where Outer JOIN is useful. You must have noticed several instances where developers write query as given below.

SELECT t1.* FROM Table1 t1 WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.* FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL

The above example can also be created using Right Outer JOIN.

 

 
NOT Inner JOIN
Remember, the term Not Inner JOIN does not exist in database terminology. However, when full Outer JOIN is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner JOIN. This JOIN will show all the results that were absent in Inner JOIN.

Cross JOIN
A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. One common example is when a company lists all its products in a pricing table to compare each product with others prices.

Self-JOIN
In this particular case, one table JOINs to itself with one or two aliases to stave off confusion. A self-JOIN can be of any type, as long as the joined tables are the same. A self-JOIN is unique in the sense that it involves a relationship with only one table. A common example is when a company has a hierarchal reporting structure whereby a member of staff reports to another member. Self-JOIN can either be an Outer JOIN or an Inner JOIN.

Self-JOIN is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values of the same column. Self-JOIN is a JOIN in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-JOIN can either be an inner JOIN or an outer JOIN. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data type of the inter-related columns must be of the same type or cast to the same type.

Now, think of a situation where all the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to employee information, where the table may have both an employee’s ID number for each record and also a field that displays the ID number of an employee’s supervisor or manager. To retrieve the data, it is mandatory for the tables to relate/JOIN to itself.

Another example that can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer to the sample database for table structure.

Note:
Before we continue further let me make it very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN, it should be done with priority.

Why Authentication and Authorization

 There are many technologies used to build .NET Web applications. To build effective application-level authentication and authorization strategies, you need to understand how to fine-tune the various security features within each product and technology area, and how to make them work together to provide an effective, defense-in-depth security strategy. This guide will help you do just that.

First we know ,
1. Authentication (to identify the clients of your application)
2. Authorization (to provide access controls for those clients)

There are two closely interlinked concepts at the heart of security for distributed applications - authentication and authorization. Authentication is the process of obtaining some sort of credentials from the users and using those credentials to verify the user's identity. Authorization is the process of allowing an authenticated user access to resources. Authentication is always precedes to Authorization; even if your application lets anonymous users connect and use the application, it still authenticates them as being anonymous.

ASP.net provides flexible set of alternatives for authentication. You can perform authentication yourself in code or delegate authentication to other authorities (such as Microsoft Passport). In fact sometimes it seems ASP.net authentication is a bit too flexible; it can be difficult for a new developer to know just where to start. In this article, we review the settings in ASP.net and Internet Information Services (IIS) that control authentication and authorization in ASP.net applications.

An ASP.net application has two separate authentication layers. That is because ASP.net is not a standalone product. Rather it is a layer on top of IIS. All requests flow through IIS before they are handed to ASP.net. As a result, IIS can decide to deny access without the ASP.net process even knowing that someone requested a particular page. Here is an overview of the steps in the joint IIS and ASP.net authentication process.

1. IIS first checks to make sure the incoming request comes from an IP address that is allowed access to the domain. If not it denies the request.

2. Next IIS performs its own user authentication if it configured to do so. By default IIS allows anonymous access, so requests are automatically authenticated, but you can change this default on a per - application basis with in IIS.

3. If the request is passed to ASP.net with an authenticated user, ASP.net checks to see whether impersonation is enabled. If impersonation is enabled, ASP.net acts as though it were the authenticated user. If not ASP.net acts with its own configured account.

4. Finally the identity from step 3 is used to request resources from the operating system. If ASP.net authentication can obtain all the necessary resources it grants the users request otherwise it is denied. Resources can include much more than just the ASP.net page itself you can also use .Net's code access security features to extend this authorization step to disk files, Registry keys and other resources.

Authentication providers

Assuming IIS passes a requesta to ASP.net, what happens next? The answer depends on the configuration of ASP.net itself. The ASP.net architecture includes the concept of and authentication provider a piece of code whose job is to verify credentials and decide whether a particular request should be considered authenticated. Out of the box ASP.net gives you a choice of three different authentication providers.

* The windows Authentication provider lets you authenticates users based on their windows accounts. This provider uses IIS to perform the authentication and then passes the authenticated identity to your code. This is the default provided for ASP.net.

* The passport authentication provider uses Microsoft's passport service to authenticate users.

* The forms authentication provider uses custom HTML forms to collect authentication information and lets you use your own logic to authenticate users. The user's credentials are stored in a cookie for use during the session.

Selecting an authentication provider is as simple as making an entry in the web.config file for the application. You can use one of these entries to select the corresponding built in authentication provider:

authentication mode="windows"
authentication mode="passport"
authentication mode="forms"

ASP.net also supports custom authentication providers. This simply means that you set the authentication mode for the application to none, then write your own custom code to perform authentication. For example, you might install an ISAPI filter in IIS that compares incoming requests to list of source IP addresses, and considers requests to be authenticated if they come from an acceptable address. In that case, you would set the authentication mode to none to prevent any of the .net authentication providers from being triggered.

 Windows Authentication Provider

The Windows authentication provider is the default provider for ASP .NET. It authenticates users based on the users' Windows accounts. Windows authentication in ASP.NET actually relies on IIS to do the authentication. IIS can be configured so that only users on a Windows domain can log in. If a user attempts to access a page and is not authenticated, they'll be shown a dialog box asking them to enter their username and password. This information is then passed to the Web server

and checked against the list of users in the domain. If the user has supplied valid credentials, access is granted. The identity of the user is then passed to the ASP.NET engine. More information about the Windows authentication provider can be found here.

There are four different kinds of Windows authentication options available that can be configured in IIS:

 * Anonymous Authentication: IIS doesn't perform any authentication check. IIS allows any user to access the ASP .NET application.

 * Basic Authentication: For this kind of authentication, a Windows user name and password have to be provided to connect. However, this information is sent over the network in plain text and hence this is an insecure kind of authentication. Basic Authentication is the only mode of authentication older, non-Internet Explorer browsers support.

 * Digest Authentication: It is same as Basic Authentication but for the fact that the password is hashed before it is sent across the network. However, to be using Digest Authentication, we must use IE 5.0 or above.

 * Integrated Windows Authentication: In this kind of authentication technique, passwords are not sent across the network. The application here uses either the kerberos or challenge/response protocols to authenticate users. Kerberos, a network authentication protocol, is designed to provide strong authentication for client-server applications. It provides the tools of authentication and strong cryptography over the network to help to secure information in systems across entire enterprise.

 Passport Authentication Provider

Passport authentication is a centralized authentication service. This uses Microsoft's Passport Service to authenticate the users of an application. If the authentication mode of the application is configured as Passport and if the users have signed up with Microsoft's Passport Service, then the authentication formalities are pushed over to Passport servers.

Passport uses an encrypted cookie mechanism to identify and indicate authenticated users. If the users have already been signed into passport when they visit the application page, ASP.NET will consider them as authenticated. Otherwise, the users will be redirected to Passport servers to login. Upon successful login, the user is redirected back to the ASP.NET Web page that they initially tried to access. If you use Hotmail you already have a Passport account and are familiar with the sign-in process from an end-user's perspective.

Forms Authentication Provider

The forms authentication provider uses custom HTML forms to collect authentication information. As an ASP.NET developer using forms authentication, you must write your own logic/code to check the user's supplied credentials against a database or some other data store. When a user is successfully identified via forms authentication, the user's credentials are stored in a cookie for use during the session

Configuring Authorization

After your application has authenticated users, you can proceed to authorize their access to resources. But there is a question to answer first: Just who is the user to whom your are grating access? It turns out that there are different answers to that question, depending on whether you implement impersonation. Impersonation is a technique that allows the ASP.NET process to act as the authenticated user, or as an arbitrary specified user

ASP.NET impersonation is controlled by entries in the applications web.config file. The default setting is "no impersonation". You can explicitly specify that ASP.NET shouldn't use impersonation by including the following code in the file

identity impersonate="false" /identity

With this setting ASP.NET does not perform impersonation. It means that ASP.NET will runs with its own privileges. By default ASP.NET runs as an unprivileged account named ASPNET. You can change this by making a setting in the processModel section of the machine.config file. When you make this setting, it automatically applies to every site on the server. To user a high-privileged system account instead of a low-privileged, set the userName attribute of the processModel element to SYSTEM. Using this setting is a definite security risk, as it elevates the privileges of the ASP.NET process to a point where it can do bad things to the operating system.

When you disable impersonation, all the request will run in the context of the account running ASP.NET: either the ASPNET account or the system account. This is true when you are using anonymous access or authenticating users in some fashion. After the user has been authenticated, ASP.NET uses it own identity to request access to resources.

The second possible setting is to turn on impersonation.

identity impersonate="true" /identity

In this case, ASP.NET takes on the identity IIS passes to it. If you are allowing anonymous access in IIS, this means ASP.NET will impersonate the IUSR_ComputerName account that IIS itself uses. If you aren't allowing anonymous access,ASP.NET will take on the credentials of the authenticated user and make requests for resources as if it were that user. Thus by turning impersonation on and using a non-anonymous method of authentication in IIS, you can let users log on and use their identities within your ASP.NET application.

Finally, you can specify a particular identity to use for all authenticated requests

identity impersonate="true" password="password" username="DOMAIN\username" /identity

With this setting, all the requests are made as the specified user (Assuming the password it correct in the configuration file). So, for example you could designate a user for a single application, and use that user's identity every time someone authenticates to the application. The drawback to this technique is that you must embed the user's password in the web.config file in plain text. Although ASP.NET won't allow anyone to download this file, this is still a security risk if anyone can get the file by other means.
 The above sequence of steps is pictorially depicted in the diagram below: 

Video: This video explained basic creation of authentication & authorization just go ahead, it's help to start



Best practices
Now that you know what the choices are for ASP.NET authentication, here are some points that tell which to choose.
    * If there is nothing sensitive about the application, stick with no authentication in ASP.NET and anonymous authentication in IIS. That lets anyone who can reach the host computer use the application.
    * If you have to authenticate users, there are several choices. If all users have accounts on your network, use Windows authentication in ASP.net with one of the strong IIS authentication settings. If users don't have network accounts, own custom authentication scheme is preferred, means forms authorization.
    * If different users must have different privileges, impersonation in ASP.net configuration files needs to be turn on.

Fundamental concepts in WCF

•  End Point
• Bindings and Behavior
• Contracts and Service host
• Message and Channel
• WCF client and Metadata

EndPoint

All communication with a Windows Communication Foundation (WCF) service occurs through the endpoints of the service. Endpoints provide clients access to the functionality offered by a WCF service.

Each endpoint consists of four properties:

• An Address that indicates where the endpoint can be found.
• A Binding that specifies how a client can communicate with the endpoint.
• A Contract that identifies the operations available.
• A set of Behaviors that specify local implementation details of the endpoint.

Address:

The address uniquely identifies the endpoint and tells potential consumers of the service where it is located. It is represented in the WCF object model by the  EndpointAddress class. An EndpointAddress class contains:

• A Uri property, which represents the address of the service. 
• An  Identity property, which represents the security identity of the service and a collection of optional message headers. The optional message headers are used to provide additional and more detailed addressing information to identify or interact with the endpoint

 Binding:

The binding specifies how to communicate with the endpoint. This includes:

• The transport protocol to use (for example, TCP or HTTP).
• The encoding to use for the messages (for example, text or binary).
• The necessary security requirements (for example, SSL or SOAP message security). 

Contracts:

The contract outlines what functionality the endpoint exposes to the client. A contract specifies:

• What operations can be called by a client.
• The form of the message.
• The type of input parameters or data required to call the operation.
• What type of processing or response message the client can expect.

 Behaviors:

You can use endpoint behaviors to customize the local behavior of the service endpoint. Endpoint behaviors achieve this by participating in the process of building a WCF runtime. An example of an endpoint behavior is the ListenUri property, which allows you to specify a different listening address than the SOAP or Web Services Description Language (WSDL) address.

Introduction to WCF

 Windows Communication Foundation (Code named Indigo) is a programming platform and runtime system for building, configuring and deploying network-distributed services. It is the latest service oriented technology; Interoperability is the fundamental characteristics of WCF. It is unified programming model provided in .Net Framework 3.0. WCF is a combined features of Web Service, Remoting, MSMQ and COM+. WCF provides a common platform for all .NET communication.

Why Use Windows Communication Foundation?

A distributed application built with Web services is a service-oriented application. ASP.NET Web Services (ASMX) has been available for building Web services since .NET was first released.  Why then do you need Windows Communication Foundation? WCF provides a number of benefits over ASP.NET Web Services, including:

• Support for sending messages using not only HTTP, but also TCP and other network protocols.
• The ability to switch message protocols with minimal effort.
• 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). 

Advantage:

1. WCF is interoperable with other services when compared to .Net Remoting,where the client and service have to be .Net.
2. WCF services provide better reliability and security in compared to ASMX web services.
3. In WCF, there is no need to make much change in code for implementing the security model and changing the binding. Small changes in the configuration will make your requirements.
4. WCF has integrated logging mechanism, changing the configuration file settings will provide this functionality. In other technology developer has to write the code.

Difference between WCF and Web service

Web service is a part of WCF. WCF offers much more flexibility and portability to develop a service when comparing to web service. Still we are having more advantages over Web service, following table provides detailed difference between them.

Features	Web Service	WCF
Hosting	It can be hosted in IIS	It can be hosted in IIS, windows activation service, Self-hosting, Windows service
Programming	[WebService] attribute has to be added to the class	[ServiceContraact] attribute has to be added to the class
Model	[WebMethod] attribute represents the method exposed to client	[OperationContract] attribute represents the method exposed to client
Operation	One-way, Request- Response are the different operations supported in web service	One-Way, Request-Response, Duplex are different type of operations supported in WCF
XML	System.Xml.serialization name space is used for serialization	System.Runtime.Serialization namespace is used for serialization
Encoding	XML 1.0, MTOM(Message Transmission Optimization Mechanism), DIME, Custom	XML 1.0, MTOM, Binary, Custom
Transports	Can be accessed through HTTP, TCP, Custom	Can be accessed through HTTP, TCP, Named pipes, MSMQ,P2P, Custom
Protocols	Security	Security, Reliable messaging, Transactions

More details about Vs Wcf & WebService Click here