Get hierarchical XML data in SqlServer

Select a.EmployeeID as '@EmployeeID', a.EmployeeName as '@EmployeeName',a.DOJ as 'DateOfJoining'
(
Select
b.ProjectID as '@ProjectID',
b.ProjectName as '@ProjectName' ,
b.StartDate as '@StartDate' from Projects b where a.EmployeeID = b.EmployeeID for xml path('Project'), type
) as 'Projects'
from Employee a
FOR xml PATH('Employee') , ROOT

It would give output like this:-

Protect/Encrypt config file in c#

Assume that we have web.config or app.config file like this:

XML:

<?xml version="1.0" encoding="utf-8"?>
>
>
name="AdventureData" connectionString="adventuredb"
providerName="System.Data.SqlClient" />
>
>
key="UserName" value="Vijay" />
key="password" value="dhiraj" />
key="dbIP" value="dbAdventure"/>
>
>

example :- ProtectSection("appSettings","DataProtectionConfigurationProvider");

private void ProtectSection(string sectionName,
                                  string provider) {     Configuration config =         WebConfigurationManager.             OpenWebConfiguration(Request.ApplicationPath);      ConfigurationSection section =
                config.GetSection(sectionName);      if (section != null &&
             !section.SectionInformation.IsProtected)     {         section.SectionInformation.ProtectSection(provider);         config.Save();     } }

private void UnProtectSection(string sectionName) {     Configuration config =         WebConfigurationManager.             OpenWebConfiguration(Request.ApplicationPath);      ConfigurationSection section =
             config.GetSection(sectionName);      if (section != null &&
         section.SectionInformation.IsProtected)     {         section.SectionInformation.UnprotectSection();         config.Save();     } }

Difference between @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Differences between ASMX and WCF Web Services

1. Message Structure and Serialization

Serialization is the process of translating binary objects into a data format that can be transmitted across process boundaries, computer boundaries, and network boundaries. When serialized data reaches the destination, or endpoint, it can be deserialized back into binary objects for use by an application. Both ASMX and WCF use SOAP for messages passed between two endpoints. However, for serialization, each uses different classes that implement different rules.

ASMX uses the XmlSerializer to translate classes into XML for communication, and to translate the XML back into classes on the receiver's end. All public members are serialized unless they are marked as non-serializable using the XmlIgnoreAttribute. A large number of attributes can also be used to control the structure of the XML. For example, a property can be represented as an attribute using theXmlAttributeAttribute, or as an element using the XmlElementAttribute. The use of these attributes provides a great deal of control over how a type is serialized into XML; however, that power comes with an unfortunate downside. It may be possible to create XML structures that are not easily translated by other type systems, such as Java; XML structures that are not easily translated can hamper interoperability.

WCF uses a DataContractSerializer to perform the same translation; however, the behavior is different from the XmlSerializer. The XmlSerializer uses an implicit model where all public properties are serialized unless they are marked with the XmlIgnoreAttribute, but the DataContractSerializer uses an explicit model where the properties and/or fields that you want to serialize must be marked with aDataMemberAttribute. It is important to note that WCF can also use the XmlSerializer to perform serialization operations.

A notable difference between ASMX and WCF is the WCF ability to serialize class members regardless of the access specifier used. This means it is now possible to serialize private fields. Using this capability, you can encapsulate fields in a data type. For example, you can provide read-only access to a private field by implementing only the get method on a property. You can then serialize that private field by adding the DataMemberattribute to the field in a WCF service.

Another important difference is that the DataContractSerializer generates a simplified XML structure that increases its ability to interoperate between different operating systems. In addition, the ability for users to control the XML structure is limited. This simplified structure also means that future versions of WCF will be able to target this structure for optimization. Finally, in comparison to the XmlSerializer, the serialization of data is greatly improved with the DataContractSerializer.

Recommendation

Using WCF, you can use the XmlSerializer for types that are already created. However, to maximize interoperability, it is recommended to use WCF data contracts and the DataContractSerializer. The Web Service Software Factory (ASMX) guidance package, also referred to as the ASMX guidance package, creates types that should also migrate to WCF data contracts if no additional XML serialization attributes, such asXmlAttributeAttribute, are added. The ASMX guidance package includes an XmlNamespaceAttribute to specify the namespace of the types.

2. SOAP Extensions

Developers can use the SOAP extensions feature of ASP.NET to interact directly with SOAP messages. By using SOAP extensions, you can intercept the SOAP message and insert your own code into the SOAP message pipeline, which allows you to extend the capabilities of SOAP. For example, features such as security, transaction management, routing, and tracing can be implemented by using SOAP extensions. The downside of this capability is that it reduces the SOAP message's ability to interoperate with other operating systems. In other words, other operating systems may not be able to handle a SOAP message that has been customized.

WCF does not support the use of SOAP extensions, but it does have other extensibility points that can be used to intercept and manipulate SOAP messages. For example, you can use a behavior extension to hook into the WCF Dispatcher with a class that implements IDispatchMessageInspector.

Recommendation

Avoid the use of SOAP extensions when developing new ASMX services that will be migrated to WCF unless you are willing to rewrite them or if you are confident that WCF provides a similar capability.

3. Transport Protocols

ASMX services use the HTTP transport protocol for communications with Internet Information Services (IIS) as the host. An ASMX service file has the file name extension .asmx, which is accessed using a Uniform Resource Locator (URL); for example, http://localhost/ASMXEmployee/EmployeeManager.asmx.

WCF services can also use the HTTP protocol, but unlike ASMX services, you also have the option to use other transport protocols. The following protocols are supported by WCF:

• Hypertext Transfer Protocol (HTTP)
• Transmission Control Protocol (TCP)
• Message Queuing (also known as MSMQ)
• Named pipes

Many different hosts can also be used with WCF services. For example, IIS can be used as a host with the HTTP transport protocol. Windows services and stand-alone applications can be used as a host for other transport protocols.

Accessing a service hosted in IIS is similar to accessing an ASMX service; for example, http://localhost/WCFEmployee/EmployeeManager.svc.

The only difference between the two services is the file name extension. However, you can also configure a WCF service to use the .asmx file name extension, as described in the Service Configuration topic. WCF services that use other transport protocols are accessed using methods associated with the specific protocol.

When migrating an ASMX service to WCF, the protocol you choose is based on the client applications that will be accessing the service. If you need to support ASMX client applications, you also need to use the HTTP protocol. In addition, when configuring a WCF service for ASMX client applications, you need to configure the service to use BasicHttpBinding, as described in the Service Configuration topic.

Recommendation

When migrating services from ASMX to WCF, and if ASMX client applications need to access the migrated service, use the HTTP protocol and configure the new service to use BasicHttpBinding.

4. Security

Typically, authentication and authorization with ASMX is done using IIS and ASP.NET security configurations and transport layer security. In addition, Web Service Extensions (WSE) can be used to provide additional security capabilities, such as message layer security.

WCF can use the same security components as ASMX, such as transport layer security and WSE. However, WCF also has its own built-in security, which allows for a consistent security programming model for any transport. The security implemented by WCF supports many of the same capabilities as IIS and WS-* security protocols. However, when using IIS, you must also enable anonymous access to the service so that WCF security is implemented.

A powerful reason for migrating an ASMX service to WCF is to take advantage of new security capabilities that are provided by WCF. For example, WCF provides support for claims-based authorization that provides finer-grained control over resources than role-based security. In addition, instead of depending on a transport protocol such as HTTP and extensions such as WSE, security is built into WCF. The end result is that security is consistent regardless of the host that is used to implement a WCF service.

Recommendation

When possible, migrate both client applications and services to WCF to take advantage of the new security features that are available with WCF. Use of the WCF Security guidance package is also recommended when configuring security for WCF services and client applications.

If a migrated WCF service must support ASMX client applications, you can use transport security associated with HTTP and HTTPS. You can also use WSE 3.0, but you must configure a custom WCF binding for this to work. For additional information about using WSE 3.0 with WCF

5. Exception Handling

With ASMX services, unhandled exceptions are always returned to client applications as SOAP faults. An ASMX service can also throw the SoapException class, which provides more control over the content of the SOAP fault that is returned to the client application.

However, when an unhandled exception occurs, the default configuration of WCF protects sensitive data from exposure by not returning sensitive information in SOAP fault messages. You can override this behavior by adding a serviceDebug element to the service behavior in the configuration file that is associated with a WCF service. Overriding this behavior is not recommended for deployment; it should be used only in a development environment.

Similar to the SoapException class used with ASMX services, you can also throw a custom exception by using the FaultException type, where T is a data contract that contains the exception information. The use of custom exceptions also requires the declaration of a FaultContract on operations that will throw the exception.

The following code example demonstrates how a DataContract is used to define a FaultContract declared on a service operation in a ServiceContract.

This next code example demonstrates how to catch a FaultException that may be thrown by the service operation The filtering of exception data that is returned from a service is described using an Exception Shielding pattern. The pattern describes how exception handlers can be used to filter the data that is returned to a client application. To help facilitate the creation of exception handlers in WCF services, the Service Factory: Modeling Edition includes the Data Contract Model that contains a fault contract shape that you can use to create WCF fault contracts.

6. State Management

ASMX services have access to the HttpContext class, which provides access to state managers with a different scope, such as application scope and session scope. ASP.NET also provides control over how state data is managed. Consequently, you should minimize the use of state in a service because of the effect it has on the scalability of an application.

WCF provides extensible objects that can be used for state management. Extensible objects implement the System.ServiceModel.IExtensibleObject interface. The two main classes that implement theIExtensibleObject interface are ServiceHostBased and InstanceContext. The ServiceHostBased class provides the ability for all service instances in the same host to access the same state data. On the other hand, the InstanceContext class only allows access to state data within the same service instance.

To implement state management in WCF, you need to define a class that implements the IExtension interface, which is used to hold the state data. Instances of this class can then be added to one of theIExtensibleObject classes using the Extensions property. The following code example shows how to implement state using the InstanceContext.

Gang of Four Design Pattern

The Gang of Four are the four authors of the book, "Design Patterns: Elements of Reusable Object-Oriented Software". In this article their twenty-three design patterns are described with links to UML diagrams, source code and real-world examples for each.

What are Design Patterns?

Design patterns provide solutions to common software design problems. In the case of object-oriented programming, design patterns are generally aimed at solving the problems of object generation and interaction, rather than the larger scale problems of overall software architecture. They give generalised solutions in the form of templates that may be applied to real-world problems.

Design patterns are a powerful tool for software developers. However, they should not be seen as prescriptive specifications for software. It is more important to understand the concepts that design patterns describe, rather than memorising their exact classes, methods and properties. It is also important to apply patterns appropriately. Using the incorrect pattern for a situation or applying a design pattern to a trivial solution can overcomplicate your code and lead to maintainability issues

Gang of Four Design Patterns

This section gives a high-level description of the twenty-three design patterns described by the Gang of Four. Each pattern description includes a link to a more detailed article describing the design pattern and including a UML diagram, template source code and a real-world example programmed using C#.

Creational Patterns

The first type of design pattern is the creational pattern. Creational patterns provide ways to instantiate single objects or groups of related objects. There are five such patterns:

• Abstract Factory. The abstract factory pattern is used to provide a client with a set of related or dependant objects. The "family" of objects created by the factory are determined at run-time.
• Builder. The builder pattern is used to create complex objects with constituent parts that must be created in the same order or using a specific algorithm. An external class controls the construction algorithm.
• Factory Method. The factory pattern is used to replace class constructors, abstracting the process of object generation so that the type of the object instantiated can be determined at run-time.
• Prototype. The prototype pattern is used to instantiate a new object by copying all of the properties of an existing object, creating an independent clone. This practise is particularly useful when the construction of a new object is inefficient.
• Singleton. The singleton pattern ensures that only one object of a particular class is ever created. All further references to objects of the singleton class refer to the same underlying instance.

Structural Patterns

The second type of design pattern is the structural pattern. Structural patterns provide a manner to define relationships between classes or objects.

• Adapter. The adapter pattern is used to provide a link between two otherwise incompatible types by wrapping the "adaptee" with a class that supports the interface required by the client.
• Bridge. The bridge pattern is used to separate the abstract elements of a class from the implementation details, providing the means to replace the implementation details without modifying the abstraction.
• Composite. The composite pattern is used to create hierarchical, recursive tree structures of related objects where any element of the structure may be accessed and utilised in a standard manner.
• Decorator. The decorator pattern is used to extend or alter the functionality of objects at run-time by wrapping them in an object of a decorator class. This provides a flexible alternative to using inheritance to modify behaviour.
• Facade. The facade pattern is used to define a simplified interface to a more complex subsystem.
• Flyweight. The flyweight pattern is used to reduce the memory and resource usage for complex models containing many hundreds, thousands or hundreds of thousands of similar objects.
• Proxy. The proxy pattern is used to provide a surrogate or placeholder object, which references an underlying object. The proxy provides the same public interface as the underlying subject class, adding a level of indirection by accepting requests from a client object and passing these to the real subject object as necessary.

Behavioural Patterns

The final type of design pattern is the behavioural pattern. Behavioural patterns define manners of communication between classes and objects.

• Chain of Responsibility. The chain of responsibility pattern is used to process varied requests, each of which may be dealt with by a different handler.
• Command. The command pattern is used to express a request, including the call to be made and all of its required parameters, in a command object. The command may then be executed immediately or held for later use.
• Interpreter. The interpreter pattern is used to define the grammar for instructions that form part of a language or notation, whilst allowing the grammar to be easily extended.
• Iterator. The iterator pattern is used to provide a standard interface for traversing a collection of items in an aggregate object without the need to understand its underlying structure.
• Mediator. The mediator pattern is used to reduce coupling between classes that communicate with each other. Instead of classes communicating directly, and thus requiring knowledge of their implementation, the classes send messages via a mediator object.
• Memento. The memento pattern is used to capture the current state of an object and store it in such a manner that it can be restored at a later time without breaking the rules of encapsulation.
• Observer. The observer pattern is used to allow an object to publish changes to its state. Other objects subscribe to be immediately notified of any changes.
• State. The state pattern is used to alter the behaviour of an object as its internal state changes. The pattern allows the class for an object to apparently change at run-time.
• Strategy. The strategy pattern is used to create an interchangeable family of algorithms from which the required process is chosen at run-time.
• Template Method. The template method pattern is used to define the basic steps of an algorithm and allow the implementation of the individual steps to be changed.
• Visitor. The visitor pattern is used to separate a relatively complex set of structured data classes from the functionality that may be performed upon the data that they hold.

.