Monday, July 31, 2017

The SOLID Principles

SOLID stands for few set of object oriented principles and these principles helps in making the source code robust, salable, and extensible.


SOLID Principles

SOLID is a mnemonic acronym and each of the letters in it stands for:

·        O - Open Closed Principle


"every class should have a single responsibility", and that responsibility should be entirely encapsulated by the class. There should only be a single reason for making the change to a class and every behavior/function of class should have just one reason to exist.

"software application source codes should be open for extension but should be closed for modification."

If module satisfy the open-closed principle that have two benefits, its “open for extension:” means module’s behaviors can be changed but “closed for modification”: it is not allowed to change. This can be achieved by using Inheritance

Benefits of Open/Closed Principle:
·         Loose coupling
·         Reduced risk of breaking existing functionality be adding/inheriting new classes

"the derived classes should be perfectly substitutable for their base classes."

The object of a derived class should be able to replace an object of the base class without modifying the behavior of the base class.

"many client-specific interfaces are better than one general-purpose interface." and no clients should be forced to implement methods which it does not use and the contracts should be broken down to thin ones

Benefits of Interface Segregation Principle:
·               More granular and more specific interface intended to keep a system decoupled and thus easier to refactor, change, and redeploy.
·               Interface should be so smaller and more specific ones so that clients will only have to know about the methods that are of interest to them.

"depend upon abstractions, [not] concretions." the modules should be depend on the abstract type of module, not actual implemented concrete class.

This Principle helps to decouple code by ensuring that the class depend on abstractions rather than concrete class.

 Dependency Injection (DI) is an implementation of this principle

Thanks for Visiting!!

Thursday, July 27, 2017

Entity Framework: How to log SQL statements


In this blog, we will learn how to log the commands and SQL quries sent to database by EntityFramework. In Entity Framework, DBContext provides the DbContext.Database.Log property to log the SQL generated by DbContext.

Logging the generated SQL statement by entity framework always help to you to understand that how the entity framework are parsing the EF query and generating raw SQL statement and send to SQL engine to fetch records and  it helps you to tune EF query and improve system performance.

Here is an IQueryable EF query, which fetch the customer entity based on customer ID. We can use ToString() method to get the generated SQL statement.

IQueryable<Customer> query = _dbConext.Customers.Where(x => x.CustomerID == ID);
string sql = query.ToString();
Console.WriteLine(sql)

Output:

SELECT [Extent1].[CustomerID] AS [CustomerID], [Extent1].[CompanyName] AS [CompanyName]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerID] = @p0

In Entity Framework 6.0, there is a DbContext.Database.Log property, which used to setup delegate to log the commands and generated SQL queries sent to database by DBContext.

public Action<string> Log { get; set; }

Here is an example for logging EF activities to the console.

_dbConext.Database.Log = Console.Write;

IQueryable<Customer> query = _dbConext.Customers.Where(x => x.CustomerID == ID);

Other entity framework related posts:


Monday, July 24, 2017

SQL - Delete Duplicate Rows


This blog will demonstrates how to delete duplicate rows by using SQL Row_Number() function in sql server.

Here is a Student table which have few duplicate records (in yellow color)

ID
Code
Address
Created Date
1
CN01
Memphis, TN
7/1/2017
2
CN02
Nashville, TN
7/9/2017
3
CN03
Cordova, TN
7/10/2017
4
CN01
Memphis, TN
7/11/2017


First we need to decide, which row we are going to keep and which one we are going to delete.

Here is an example to delete all duplicate records except the oldest record.

Step 1: Get duplicate student records – here is a sub query which group by code and select only those code which are having more than 1 records (duplicate records)

Select Code, count(Code) [Ct] from vr.Student group by Code
having count(Code) > 1

Step 2: Generate Row Number by using SQL ROW_NUMBER() function for each duplicate records

With CTE AS
(
Select y.* , ROW_NUMBER() OVER(PARTITION BY y.Code ORDER BY y.Code, y.ID) AS RowRank from 
(
Select Code, count(Code) [Ct] from dbo.Student group by Code
having count(Code) > 1
) y
)
Select * from CTE

Output: 

ID
Code
Address
Created Date
RowRank 
1
CN01
Memphis, TN
7/1/2017
1
4
CN01
Memphis, TN
7/11/2017
2

In above output, the oldest duplicate row has low row rank and now we can delete all duplicate records except the oldest record (row rank = 1)

Delete from dbo.Student where ID IN (Select ID from CTE where CTE.RowRank <> 1)

Here is a complete SQL Query


With CTE AS
(
  Select y.* , ROW_NUMBER() OVER(PARTITION BY y.Code ORDER BY y.Code, y.ID) AS RowRank  from 
(
Select Code, count(Code) [Ct] from dbo.Student group by Code
having count(Code) > 1

)

Delete from dbo.Student where ID IN (Select ID from CTE where CTE.RowRank <> 1)

Friday, July 21, 2017

Entity Framework : Create, Update and Delete Record

Entity Framework is ORM .net framework, which is used to update, create or delete record from table.

As Per MSDN “Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects

EntityFramework works as a layer between database and business layer (application code) and entity framework maps each database table to entity class ( C# CLR class) and with help of this entity class, developer can perform SQL DML operation like create, modify and delete on mapped database table.

Entity Framework Architecture




Create Record: 

Below code shows how to add new record of department entity

   Department department = newDepartment()
            {
                Name = "Admin"
            };

   DBContext DbContext = new DBContext ();

   DbContext.Departments.Add(department);

   DbContext.SaveChanges();

As you can see, we are performing below steps to create a new record of department with department name ‘Admin’ 
  1. Create new object of Department and set department Name to “Admin”.
  2. Create new DBContext Object.
  3. Add new department object to Department entity set.
  4. Call  saveChanges() method of DBContext Object to add new department record and this method  parse the entity graph and generate SQL script and execute on connected database.
Update Record:

Below code shows how to modified the existing record of department entity

   DBContext DbContext = new DBContext ();

   Department department = DbContext.Departments.FirstOrDefault(d => d.Name == "Admin");

   department.Code = "A";

   DbContext.SaveChanges();

As you can see, below steps are being used to update the existing record of department entity
  1. Create new DBContext Object.
  2. Fetch the existing record of department and load into local memory.
  3. Update desired fields or properties of department record.
  4. Call saveChanges() method of DBContext Object to update department record and this method parse the entity graph and generate SQL script 



Delete Record:

Below code shows how to delete the existing record of department entity

   DBContext DbContext = new DBContext ();

   Department department = DbContext.Departments.FirstOrDefault(d => d.Name == "Admin");

   DbContext.Departments.Remove(department);

   DbContext.SaveChanges();

As you can see, we are performing below steps to delete the existing record of department entity
  1. Create new DBContext Object.
  2. Load the existing record of department into memory.
  3. Remove the record from Department entity Set.
  4. Call SaveChanges() method of DBContext Object to delete the attached department record and this method parse the entity graph and generate SQL script and execute on the connected database.

Thanks for visiting !!


Other Entity framework related Links: 

Thursday, July 20, 2017

System.InvalidOperationException: This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet

I encountered error “This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.” when I tried to return JSON response from MVC controller.

Here is a MVC Controller method, which returns the JSON response.

 public JsonResult GetUserClaims(string userID)
        {
            var response =_userManager.GetClaims(userID);
            return Json(response);

        }


This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.

Here is a reason for this exception:
By default, the ASP.NET MVC framework does not allow you to respond to an HTTP GET request with a JSON payload.

If you need to send JSON in response to a GET, you'll need to explicitly allow the behavior by using JsonRequestBehavior.AllowGet as the second parameter to the Json method. However, there is a chance a malicious user can gain access to the JSON payload through a process known as JSON Hijacking. You do not want to return sensitive information using JSON in a GET request.

Here the way to set JsonRequestBehavior to AllowGet for JSON response

   public JsonResult GetUserClaims(string userID)
        {
            var response =_userManager.GetClaims(userID);
            return Json(response,JsonRequestBehavior.AllowGet);

        }

WCF MTOM MessageEncoding


In blog, we will learn about the Message Transmission Optimization Mechanism (MTOM) message encoding with a WSHttpBinding in WCF service and will demonstrates steps how to use the MTOM message encoding with C# example.


MTOM is an efficient technology for transmitting large binary data with SOAP in WCF messages. By default, the WSHttpBinding sends and received messages as normal text XML but transferring large chunk of data as normal XML text form, it is not better choice, if we choose the binary encoding, it only support to WCF-to- WCF communication and but exclude interoperability option for service.
MTOM encoder create a balance efficiency and interoperability and The MTOM encoding transmits most XML in textual form, but optimizes large blocks of binary data by transmitting them as-is, without conversion to text.

Here is pros and cons of message encoding:

·         Text Encoding :
o   Highly supports the interoperability
·         Binary Encoding:
o   High speed but only WCF –to- WCF communication
·         MTOM Encoding :
o   Message Transmission Optimization Protocol
o   Support both XML and binary

Here are steps how to use the MTOM message encoding in WCF Service

1.   Create Message Contract:

The method, which sends MTOM content, can only use [MessageContract] attribute decorated class or "primitive" types for receiving or sending data.

[MessageContract]
    public class OrderReport
    {
     [MessageHeader]
     public int OrderID { get; set; }

     [MessageBodyMember]
     public Byte OrderReportPDF { get; set; }
}

In [MessageContract] Class, the byte array properties must be decorated with a [MessageBodyMember] attribute and all the other properties of class must be decorated with [MessageHeader] attribute.


2.   Service Contract:

Here is a ServiceContract, which has a method GetOrderReportData to get Order Report Data based on input Order ID

namespace OrderWCF
{

    [ServiceContract]
    public interface IOrder
    {            
         [OperationContract]
             OrderReport GetOrderReportData(int OrderID);
     
    }
}


3.   WCF Binding Configuration:

Both client and server bindingConfiguation must have the messageEncoding attribute to Mtom

 &lt;bindings>
  <wsHttpBinding>
    <binding name="WsHttpMtomBinding" messageEncoding="Mtom" />
  </wsHttpBinding>
</bindings>
<services>
  <service behaviorConfiguration="Behavior" name="OrderWCF.OrderService">
    <endpoint binding="wsHttpBinding" bindingConfiguration="WsHttpMtomBinding" contract="OrderWCF.IOrder">
  </service
</services>




SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...