SQL - 1NF, 2NF & 3NF

Normal Forms are fall under database normalization techniques

1NF (First Normal Form) : Each table cell should contain a single value, there should not be repetitive data in rows.

2NF (Second Normal Form) : Table should be in follow 1NF & Each column of table should depend on full primary key.
In this example We have composite key ProductId + SuplierId, here Price is depend on ProductId only not on SupplierId, so it should be moved in table where it depends on primary key that is Products table. In Product table Store is not depend on ProductId so it should be moved in new Store table

3NF (Third normal Form) : Each column should only depend on primary key.
While 2NF say column depend on full primary key.
In this example Total is depend on quantity and Price not on ProductId, Total can be calculated using Price and Quantity so it should removed.

SQL - Query Optimization Techniquess - I

Below given are some facts those can be used to optimize our query performance

• Unique Key: If there is no primary key defined on a table sql uses "Table Scan" operator.table scan operator reads records one by one until it gets required record, so this operator is costly if table having large number of rows.
  When we create primary key on table it improve performance drastically. As displayed in picture.

• Scan Vs Seek: If we've clustered / non clustered index on table then sql uses 'Index Scan' or 'Index Seek' operators.Index Scan : Touches every row whether it is qualified or not, start to end. So cost associated with this search is proportional total number of rows.
  This scan is right choice if table is small or most of the rows are qualified for condition of query.
  Index Seek : Index seek touches only rows / pages those are qualified for condition of query.
  So, if we provide some condition sql prefer to choose 'Index Seek', Even if condition provided result record number is large then optimizer change the plan uses Scan instead of Seek.
  This is right choice if table having large number of rows or qualified rows count is very much smaller(few) compare to all rows. 

SQL - Basics Sql execution plan

Below given is high level diagram shows how Sql queries get executed

Sql query processing steps : There are some steps for sql statements execution

1. Parsing : Parser checks syntax of sql statement including spellings, reserved keywords etc, and prepare sequence tree.
2. Normalization : Normalization process mainly perform binding for objects of sequence tree, checks tables and columns available or not, loading metadata, adding required information for conversations to sequence tree. Replacing views with their definitions.
3. Compilation : Compilation process include extraction of true transact statements(select, insert, update, delete etc) to pass to optimizer, because whole code may include variable declarations, assignments, loops, if conditions etc those not required optimization.
   

SOLID Principles

SOLID is an acronym for basic object oriented programming & design principals. Applying these principle on system makes system easy to maintain and extendable with time.

• Single Responsibility Principle (SRP)
• Open Closed Principle (OCP)
• Liskov Substitution Principle (LSP)
• Interface Segregation Principle (ISP)
• Dependency Inversion Principle (DIP) 

Also refer nice article on https://www.syncfusion.com/blogs/post/mastering-solid-principles-csharp 

• Single Responsibility Principle (SRP): Single responsibility principle says a software module / class should have only one reason to modify. It means if any class having more than one reasons to modify, this is violation of SRP.

    public class Circle

    {

        private int _radius;

        public int Radius

        {

            get

            {

                return _radius;

            }

            set

            {

                value = _radius;

            }

        }

        public int Area()

        {

            return Radius * Radius * 22 / 7;

        }

        public string PrintHTMLArea()

        {

            return "<h2>" + Area().ToString() + "</h2>";

        }

    }

Above class contains two responsibility
Calculating Area
Printing Area

If properties need to add/modify or print method need to be modified or need to add new print method, class need to be updated. So this is violation of SRP, entity class should have only entity specific things like variables / properties.

WCF netMSMQBinding

netMSMQBinding is uses Microsoft Message Queue Server, requests from clients get stored in this server in queue and service picks these message in first come first serve manner.

MS Message Queuing is a windows service than can be enabled / activated from windows features.

To install Message Queuing 4.0 on Windows Server 2008 or Windows Server 2008 R2

1. In Server Manager, click Features.
2. In the right-hand pane under Features Summary, click Add Features.
3. In the resulting window, expand Message Queuing.
4. Expand Message Queuing Services.
5. Click Directory Services Integration (for computers joined to a Domain), then click HTTP Support.
6. Click Next, then click Install.
   

Scenarios for which this MSMQ server & netMSMQBinding can be used

1. Multiple clients calls service with high frequency : Multiple client calls the service and service got busy in that case messages from clients stored in queue and service picks them as got free
2. Messages get processed even service got down for some time : As service got up it pics all stored messages from queue

Fault Exception in WCF

WCF provides FaultException class that can be used to send exception details to client.

1. Through Exception class object from service 

<serviceDebug includeExceptionDetailInFaults="false"/>

2. Using default FaultException

Through Default FaultException class object from service

throw new FaultException(ex.Message);

OR

Enable includeExceptionDetailInFaults in config
<serviceDebug includeExceptionDetailInFaults="true"/>

3. Through Custom class object (Fault contract) using FaultException class

Service Code:

    [ServiceContract]

    public interface IService1

    {

        [OperationContract]

        [FaultContract(typeof(ClsCustomException))]

        int CalculateCost(string itemCount);

    }

    [DataContract]

    public class ClsCustomException

    {

        [DataMember]

        public string Message { get; set; }

        [DataMember]

        public string Description { get; set; }

        [DataMember]

        public string StackTrace { get; set; }

    }

    public int CalculateCost(string itemCount)

    {

        try

        {

            return int.Parse(itemCount) * 120;

        }

        catch (Exception ex)

        {

            //Without fault exception

            //throw ex;

            //Simple fault exception sending message only to client side

            //throw new FaultException(ex.Message);

            //Fault contract (using our custom class to send details of exception to client)

            ClsCustomException objMyException = new ClsCustomException();

            objMyException.Message = ex.Message;

            objMyException.Description = "Error in itemCount conversion to int";

            objMyException.StackTrace = ex.StackTrace;

            throw new FaultException<ClsCustomException>(objMyException);

        }

    }

Client Code:

        static void Main(string[] args)

        {

            ServiceReference1.Service1Client _client = new ServiceReference1.Service1Client();

            try

            {

                Console.WriteLine("Enter number...");

                Console.WriteLine( _client.CalculateCost(Console.ReadLine()));

                Console.ReadLine();

            }

            catch (FaultException<ServiceReference1.ClsCustomException> ex)

            {

                Console.WriteLine(ex.Message);

                Console.WriteLine(ex.Detail.Description);

                Console.WriteLine(ex.Detail.StackTrace);

                Console.ReadLine();

            }

        }

RESTful WCF Services

We will understand steps with an example of maintaining players list

Steps to create Wcf REST service 

1. It requires webHttpBinding.
2. It requires <webHttp />  tag inside behavior tag.
3. Create wcf service application
4. Create methods for add, update, delete, get player
   
           Player GetPlayer(string id);
           void AddPlayer(string name);
           void UpdatePlayer(string id, string name);
           void DeletePlayer(string id);
           List<Player> GetPlayers();
5. Mark created methods with below given methods
                1. WebGet
                2. WebInvoke
   
   
   
6. Assign values of parameters in WebGet and WebInvoke as required

• UriTemplate : Set url format to access the method

• GetPlayer/{id}
• UpdatePlayer/{id}/{name}

• Method

• POST : To submit data to process
• PUT : To update resource
• DELETE : To delete resource

• RequestFormat

• RequestFormat= WebMessageFormat.Xml
• RequestFormat= WebMessageFormat.Json

• ResponseFormat

• ResponseFormat= WebMessageFormat.Xml
• ResponseFormat= WebMessageFormat.Json

• BodyStyle

• Bare
• Wrapped
• WrappedRequest
• WrappedRespons

   
5. Service config settings

Note : In below given config settings 4th & 5th points are not compulsory.

REST Services

REST is an architectural style of making web app/services uses http with its methods and apply certain principals on it to accomplish REST app/services.
REST stand for Representational State Transfer.

Rest service is useful when you required to access service in client script like Javascript, REST services can return data in Json format that can be used in scripting languages.

Principals used to implement RESTful services 

1. Frame every thing like it is a resource
   Ex. User, Order
2. Every resource should be represent with a unique url
   Ex. http://gstomar.blogspot.com/users/10
3. Every request/response should be done with a representation
   Every call should present some view
4. Interfaces should be simple & uniform
5. Every request/response should be stateless
   Every request/response should be independent from each other

Methods used to accomplish RESTful services

S.No.	Method	Description
1	GET	Request a specific representation of a resource
2	PUT	Create or Update a resource with supplied representation
3	POST	Submits data to be processed by a resource
4	DELETE	Delete a resource
5	HEAD	Same like GET but accesses only header information
6	OPTIONS	Requests list of supported methods by resource

WCF Duplex(Two way) contracts

Duplex contract is two way contract, two channels establishes between service & client, client to service & service to client.

Requirement: Whenever we have big service operation that we want to execute one way (Asynchronously), but we want to get notified also when service operation get completed.

Benefit: This way without getting halted for service method completion client get notified when operation completed also can get some return value from service.

Supported bindings : wsDualHttpBinding, netNamedPiepeBinding, netTCPBinding
Only these bindings are able to establish two transport channels.

Steps to implement Two way binding:

Service Side-

1. Create new callback interface, with one method that will execute on completion of main method
   
   public interface IClientCallBack
   {
       [OperationContract(IsOneWay=true)]
       void OperationCompleted(string message);
   }
2. Assign type of new created callback interface to attribute CallbackContract in ServiceContract of main interface
   
   [ServiceContract(CallbackContract= typeof(IClientCallBack))]
   public interface IService1
   {   
       [OperationContract(IsOneWay=true)]
       void SetData(int value);
   }
   

WCF Single (One way) contracts

Single Contract
Need to mark IsOneWay attribute true in operation contract to the service method that we want to execute in one way mode.
Its good practice to use one way for service methods returns void and methods having independent operations.
Benefit: Client code execution not halt (wait) till service method execution completes.

    [ServiceContract]

    public interface IService1

    {   

        [OperationContract(IsOneWay=true)]

        void SetData(int value);

    }

    public class Service1 : IService1

    {

        public void SetData(int value)

        {

            Thread.Sleep(value);

        }          

    }

Client Code:

    static void Main(string[] args)

    {

        SerDuplex.Service1Client _client = new SerDuplex.Service1Client();

        Console.WriteLine("Client execution started..." + DateTime.Now.ToString());

        _client.SetData(10000);

        Console.WriteLine("Client execution completed..." + DateTime.Now.ToString());

        Console.ReadLine();

    }

Results:

Concurrency in WCF Services

Concurrency in WCF refers controlling number of threads in a given session context. ConcurrencyMode can be used with ServiceBehavior attribute.

Concurrency Modes

• Single: Only one thread allowed to run in a session context, multiple calls to service served one by one.
• Multiple: Multiple threads allowed to run in a session context so multiple calls to the service could be served simultaneously, calls could be from same client or different clients.To use this mode service should be designed thread safe. Operations on object's states(operations changing state of objects) should be locked, so that other thread couldn't access those in between operations.
• Reentrant:  This is modified mode of 'Single'. Only one thread is allowed to run, any other call to the service in queue may start to serve when current one calls some other wcf service.So when proceed thread calls some other wcf service the thread released locks and other queued call may start.

     [ServiceContract]

    public interface IService1

    {

        [OperationContract]

        string GetData();

    }

    [ServiceBehavior(InstanceContextMode= InstanceContextMode.PerCall, ConcurrencyMode= ConcurrencyMode.Multiple)]

    public class Service1 : IService1

    {

        int instanceCount =0;

        object thislock = new object();

        public string GetData()

        {

            lock (thislock) //Loc is important when you using "Multiple" concurrency mode

            {

            Thread.Sleep(1000);

            return string.Format("Instance Count:{0}, Session Id:{1}, Thread Name: {2}, Time: {3}", ++instanceCount, OperationContext.Current.SessionId, Thread.CurrentThread.Name, DateTime.Now.ToString());

            }

        }    

    }