SELF JOIN

 

Self-Join is a Join where we join the same table with itself. Self-Join can be sub divided into three types:

  1. Inner Self Join
  2. Outer Self Join( Left, Right and Full)
  3. Cross Self-Join

 

Inner Self Join

 

There are scenarios where we have to apply self-join in order to achieve the result. For example we have Person table which consist of following columns as shown below:

create table Person

(

PERSONID INT PRIMARY KEY IDENTItY(1,1),

FIRSTNAME NVARCHAR(50),

MOTHERID int

)

 

 


 

So the requirement is to show the Person FirstName and MotherName. So now in order to retrieve the same we can make use of Self Join.

 

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

LEFT JOIN

PERSON MOTHER

ON PERSON.MOTHERID=MOTHER.PERSONID

 

If I check the query designer for design we can see the actual how columns are mapped to each other

 

So now the same table is referred as two different table and left join are applied on based on the Person Table where we are applying self join on MotherId Column to table Mother e to PersonId.

 

Output:

 

 

SELF INNER JOIN

Now if we want to display the MotherName of the students whose MotherId has been defined than we can make use of Self Inner Join.

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

INNER JOIN

PERSON MOTHER

ON PERSON.MOTHERID=MOTHER.PERSONID

 

 

CROSS SELF JOIN

Mapping each each records from Person table to each records of Mother table.

 

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

CROSS JOIN

PERSON MOTHER

ORDER BY PERSON.FIRSTNAME

 


JOINS in DB

SQL server is a relational database management system, that means we may have two or more related tables which have some relations which each other with reference to the relationship I mean to say 1 to many relationships, 1-1, many to one relationship which is basically referenced using Foreign key constraints.

In order to understand the benefits of using joins let’s take an example of two tables Orders, Products.
1

So as per business requirement, we want to show the user order details information i.e. OrderId, StoreId from where the order has to be dispatched, Total Gross amount and total
Discount he has received, with the product description. So in order to show the report we need to join these tables to be shown to the user. So in order to achieve the same as we discussed, we need to have some relation here we have a foreign key relation on ProductId. So now in order achieve the output I have to join these two tables we will join ProductId as a relation as shown below:

In SQL we have three kinds of Joins:

  1. Inner Join
  2. Outer Join
  3. Cross Join

Outer Join is then subdivided into three different outer Joins:

  • Left Outer Join/Left Join
  • Right Outer Join/Right Join
  • Full Join or Full Outer Join

Inner Join:

An inner join is a kind of intersection between both the tables which means the condition on which join is performed should be present in both the tables i.e. matching rows only. To specify the join condition we use On clause. We can use Join or Inner Join both means the same.
2

SELECT Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT, PROD.Description
FROM Orders Ord
INNER JOIN
Product PROD
ON Ord.PRODUCTID=PROD.ID;

Output:

3
Left Outer Join or Left Join

So now what if we want all the orders whether they have product id associated or not. So in order to do the same, we will use Left Join. So in order to achieve the same, we will make use of Left Join or Left Outer Join. This will return all the Orders whether they have ProductId associated with or not, If not display ProductNotSpecified
4


 SELECT Ord.CUSTOMERID,Ord.GROSSTOTAL,Ord.TOTALORDERDISCOUNT,
 CASE
 WHEN PROD.Description IS NULL THEN
 'ProductNotSpecified'
 ELSE PROD.Description END
 FROM Orders Ord 
 LEFT
 OUTER
 JOIN
 Product PROD
 ON Ord.PRODUCTID=PROD.ID;
 

5
So left join returns all the matching row from both table and non-matching rows from the left table.

Right Outer Join/ Full Join

So now we want to reverse the scenarios and want all the rows from right table and only matching rows from the Order Table, in order to do the same we need to apply Right outer Join. i.e. now we will display all the product description and display matching or not matching orders for the respective Products as shown below:
6

SELECT
Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT,
PROD.Description
FROM Orders Ord right OUTER JOIN

Product PROD
ON
Ord.PRODUCTID=PROD.ID;

7

Cross Join

Cross join Cartesian product of two table, that means each record right table will be mapped with all records of the left table one by one. Cross join doesn’t require On clause.
8

Orders table:

9
Product table:

10
So in Orders table, we have 6 records and in Product table, we have 3 records so each Order records will be mapped to Product records i.e.

Number of Orders Records * Number of Product Records=18

SELECT
 Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT,
 PROD.Description
 FROM Orders Ord
 CROSS JOIN
 Product  PROD
 ORDER BY ORD.ID ASC
 

11

Full Join

Full join returns all the rows from both the table, including matching or not matching rows from both the tables
12

SELECT Ord.CUSTOMERID, Ord.GROSSTOTAL, Ord.TOTALORDERDISCOUNT, PROD.Description
 FROM Orders Ord 
 FULL  JOIN 
Product PROD 
ON ORD.PRODUCTID=PROD.ID ORDER
BY ORD.ID ASC
 

13

Happy Learning.

Group By Clause in SQL

 

Group by clause is used to Group the records into a set of summary rows by one or more columns or expression. Group by is used when are using an aggregate function in our query. Aggregate function (SUM, COUNT, AVG, MIN etc).

In order to demonstrate the Group By clause we will take the example of below records or table Order:

 

 

So if we want the sum of GrossTotal of all order and by StoreId, so how we can achieve this. We can do the same using GroupBy clause.

 

SELECT STOREID,SUM(GROSSTOTAL)
as TotalGrossAll FROM [ORDER] GROUP
BY STOREID

 

Output:

 

 

Group by multiple columns, now I want to Show sum of Gross Total by StoreId and OrderCreatedDate.

 


SELECT
STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
as
DATE,
SUM(GROSSTOTAL)
AS
TOTALGROSSALL
FROM
[ORDER]


GROUP
BY
STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

 

Output:

 

 

Multiple Aggregate functions in a query, let say I want to calculate the count of no of order to a particular store based on Date and Sum of GrossAll.

 


SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]
GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

 

It is instructed to use specific column name rather than using count(*) in the query.

 

Applying filter on the Group by result using where clause as shown below:

 

SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]

where STOREID=‘3267’
GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)

 

 

We can also use HAVING statement to filter the records, the best advantage of HAVING clause is to use an aggregate function in filter i.e. we can use the aggregate function as a condition which is not possible in where clause. Having clause has to come after the group by clause.

SELECT STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
AS
DATE,
SUM(GROSSTOTAL)
AS TOTALGROSSALL,COUNT(STOREID) No_Of_Orders FROM [ORDER]

GROUP
BY STOREID,CONVERT(VARCHAR(10),ORDERCREATEDDATE,110)
HAVING
COUNT(STOREID)
>6

 

 

In WHERE clause the records are the first filterd and than applied aggregation, while in HAVING clause they are first aggregated and than filtered.

Having clause can only be used with the select statement while Where clause can be used with Update, Insert, delete, Select.

 

String Functions Available in SQL Server

We will be going through following String function:

  1. Replicate
  2. Space
  3. Pat index
  4. Replace
  5. Stuff

 

Replicate:

This function replaces the given string to the specified number of times. Syntax

REPLICATE(“STRING”,Number_of_Time)

select REPLICATE(‘SAILLESH’,3)

 

 

Let take an example of following table:


Take an example we have reporting page where we have to show the Email as first two name and then followed by (*).

In order to achieve this requirement we will make use of Replicate function.

 

In order to do the same we will break down our problem into smaller problems:

  • First we will get only starting two character of Email using SUBSTRING

SELECT
SUBSTRING([Email],1,2)
FROM Student

 

  • Next is to Show * 5 times using REPLICATE()

SELECT SUBSTRING([Email],1,2)+REPLICATE(‘*’,5)
FROM
Student

 

Output:

Now in order to show the rest of the characters starting from ‘@’ we will have to first calculate the how many characters are there after @. So in order to get that we will user LEN and CharIndex:

Select (LEN(EMAIL)-CHARINDEX(‘@’,EMAIL)+1)
AS EMAIL from student

 

This will return the length number which we will be using in SUBSTRING Function to set the length, we are than concatenating them with each other and at the end we are using SUBSTRING to get the @ and rest of the characters.

SELECT
[Name],
SUBSTRING([Email],1,2)+REPLICATE(‘*’,5)+      SUBSTRING([Email],CHARINDEX(‘@’,Email),
LEN(EMAIL)-CHARINDEX(‘@’,EMAIL)+1)
AS
EMAIL,[Course]
FROM
[SampleDb].[dbo].[Student]

 

Output:


 

Space Function:

If we want to show space in our SQL query we can make use of this function.

 

We want to display Name and Course column together with 2 spaces between them.

SELECT NAME+SPACE(2)+COURSE as StudentInfo FROM STUDENT

 

 

PatIndex function

PatIndex function returns the starting position of the pattern specified in the expression. The function takes two argument (pattern, Expression)

PatIndex is similar to CharIndex except PatIndex allow wildcards. In order to demonstrate the same let take below mentioned table as Source:

 

Now we will be showing position of the PatIndex matching pattern and using PatIndex in where clause. So I am trying to retrieve the position of all the Email which starts with ‘SHAIL’. So in order to find the same we will use wildcard % in our query.

select EMAIL,
PATINDEX(‘%SHAIL%’,EMAIL)
AS OCCURENCE FROM Student WHERE
PATINDEX(‘%SHAIL%’,EMAIL)
>0

 

We can even use where clause with Pat Index as shown below:

select EMAIL,
PATINDEX(‘%SHAIL%’,EMAIL)
AS OCCURENCE FROM Student

WHERE
PATINDEX(‘%SHAIL%’,EMAIL)
>1

 

 

Replace Function

Replace function replace all occurrence of specified string with another passed string. For example in Email column I want to replace all gmail to yahoo. We can make use of Replace function in order to implement the same.

 

Before execution:

select name,REPLACE(EMAIL,‘GMAIL’,‘YAHOO’)
FROM Student

 

 

STUFF Function

Stuff function delete the sequence of characters from the source string and then inserts another sequence of characters
into the source string at the specified position.


Now here we are trying to use Course column and will start at 2, and insert the new string in place of number of chars as shown below:

 

select
STUFF(course,2,3,‘****’)
from Student

 

Select Statment

Select DML(Data Manipulation Langauge) statement in SQL i.e. It retrieves the records from the database and shows to the user.

In order to retrieve records from the table we query the below-mentioned query:

Syntax: Select * from [db].[Schema].[Table ]

If we are already in selected database and schema we can directly run the below query.

SELECT * FROM Student

SQL server returns the query result.


Here (*) means that return all the column records of the specified table. In order to retrieve the selective columns records from the DB, we generally specify those records in the query. Here we are only retrieving Name and Email from the Student table.

For performance benefit, we should mention column names rather than * in a Select query.

SELECT NAME,EMAIL FROM Student

Different criteria of Select

  • Returning distinct rows from the table based on a column which may be duplicate and you want them to appear once. In above table, we have duplicate Course in our table so in order to return only the distinct Course we will just write a distinct query.

What if we use distinct along with another column as shown below:

SELECT DISTINCT COURSE, NAME FROM STUDENT

When we use distinct with another column we are specifying SQL server that value should be distinct across both the column selected. In order to demonstrate the same let’s take an example, we have following records in our Student table.

So now if we try to run the distinct query along with Name it will return only one row for Vipin and MCA.

SELECT DISTINCT COURSE, NAME FROM STUDENT

  • Where Clause filtering

In the majority of cases, we use Where clause which helps us to filter the records as per our condition. We have following new records in order Student table and now I want to query the Student table show me all the records where Course is BTECH.

SELECT * FROM Student WHERE Course=‘BTECH’

Result:

There are a variety of wildcard operators that we can use in our where clause in order to get desired output.

AND clause

And clause is used along with where clause where we want to filter the records on where and other condition. I want to retrieve record where course= BTECH and student name is JAMES

SELECT FROM Student WHERE Course=‘BTECH’ and Name=‘JAMES’

Order by

Order by clause is used to sort the result of the query based on ascending or descending of a column. Now we want all the students in the table with email in ascending order of the alphabet.

SELECT * FROM STUDENT ORDER BY EMAIL ASC

If we don’t specify the sorting order by default it’s ascending order.

Top

Top keyword helps us to retrieve top records from the table this keyword is quite helpful.

SELECT TOP(4)* FROM STUDENT

Top with Percent

I actually never used Top expression percent. But when I found that there is [percent] parameter present in count function.  Let’s explore the same.

The percent indicates that the query should return only the first expression top(100) percent of rows from the result set. Fractional values returned will be rounded up.

 4

The person table contains 3005 records. Now if try to execute top with percent it will perform percent on the number of rows present in the table.

2
 

Increase you search performance by proper Indexes

Abstract

This is a small article on the Index in DB and defining Clustered and Non-Clustered Index with reference to the Microsoft SQL server DB.

  1. Introduction

    In our day to day business application huge amount of transaction information is being stored on our database. In the beginning when a number of records is less and everything is working fine and then suddenly after some time the support guy comes and say query is taking the time to execute and the system is taking the huge amount of time to process the records. Indexes come to prevention where we forecast our SQL queries and how DB generating the Execution SQL plan for the query. Index result in a tremendous improvement in SQL query if used properly and vice versa also is true.

2. Indexes

Indexes are used to explicitly speed up the SQL statement execution on a table. The index points to the location of the rows containing the value. The index is similar to index as in Book to easily go to the specific chapter.

The default type of the Index used in the DB are B-Tree index

By default, when we execute a select command with no primary key and index based on search criteria e.g. Take the below numbers as an Id and we will be writing our select command with id in where clause. So when the database will execute the query it will search sequentially row by row the Id right from the beginning till it finds the desired row. So we can imagine the scenario if we have billions of data.

So in order to minimize the sequential search problem there when B-Tree comes into the picture. B-Tree is a self-balancing tree data structure which is an extension of the Binary tree that keeps data sorted and allows searches, sequential, insertion, and deletion in a quick manner.

Figure 2: B-Tree

Image Source:
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Sequential Search is called Table Scan and when they are searched using B-tree they are called Index seek or index scan. Table scan is very bad from performance point of view as we have mentioned above.

Let’s do a demo to check what kind of scan does SQL server does when there are no indexes and primary key in the table.

So below we have a table called CityName
which doesn’t have any indexer and will contain information regarding the City.

Figure 3: TblCustomer Data

So let’s run our select command with one particular Cityid selected

As shown in the diagram and we will use, Display Estimated Execution plan.

Figure 4: Demonstrating Display Estimated Execution plan option.

Execution plan tells us how the SQL query performs when we don’t have any index in our table.

Figure 5: Demonstrating the execution plan of the query.

We will make use of STATISTICS IO. STATISTICS IO provides the detailed information about the query impact on the DB server. It tells the number of logical reads, physical reads and how many tables was scanned. A good performing query is that query which has less number of logical reads.

Once we execute below sets of command and check the output statistics:

STATISTICS IO ON
SELECT * FROM CityName WHERE CityID='6137'

Figure 6:
Number of logical reads 26

  • Scan Count: This count specifies that the optimizer has chosen a plan that caused 1 Scan count.
  • Logical Reads: This number specifies the number of pages read from the cache. This is the important parameter that is needed to be focused. This number can be decreased using index structure.
  • Physical Read: This number specifies the number of pages actually read from the disk. These are pages that were not in data cache. SQL server performs everything on cache if the requested page is not present it will read the page from the disk and then put the same in the cache, then use that page.
  • Lob logical Reads: This number grows if request any large object such as an image, varchar(max), nvarchar(max).
  • Lob physical read: is same as physical read for lob pages.
  • Lob read-ahead reads: This number specifies Number of textntextimage or large value type pages placed into the cache for the query.

Now let’s follow the below steps and add an index into the table and check the performance.

Figure 7:
Table Design View and Add Indexes/Keys

Figure 8:
Adding an Index and setting Create as Clustered Index as True

Now let’s again run our execution plan and check is it again using Table Scan?

Figure 9:
Demonstrating Index scan now.

Now let’s run our performance statistics and check is there any difference?

(1 row(s) affected)

Table ‘CityName’. Scan count 1, logical reads 2 and physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

We can see the difference before the logical reads were 26 and now they have decreased to 2. So we can say there is a performance improvement by using an Index.

Let’s talk about Clustered and Non Clustered Index.

3. CLUSTERED INDEX:

Clustered Index determines the physical order of data in the table. In Clustered Index the rows are stored physically on the disk in the same order as the Index. So therefor there can only be one Clustered Index because the records can only be ordered in one order.

Figure 10:
Structure of a clustered index in a single partition

Image Source:
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

By the statement Clustered Index determines the physical order of data in the table means that data inserted in the table is order wise reference to the Index Column. That means that if we insert data in unordered way still SQL     server will insert in order wise according to the index. If the table is not a Clustered Index, its rows are inserted in unordered structure known as heap.

Let take an example and create two same table with different name and create index in one table and leave the second and try to insert the values in the table and fetch the same.

CREATE TABLE tblOrder
(
order_id int, sku_id int, description varchar(50), mrp decimal, sp decimal
)

CREATE TABLE tblOrderWithIndex
(
order_id int primary key, sku_id int, description varchar(50), mrp decimal, sp decimal )

To check the Index in the table we can use the following command

Exec Sp_helptIndex tableName

Figure 11:
Demonstrating Index created in SQL Server

So here we have created two tables, one with primary key and leaving the other table without an index. Let’s now insert some values into the table.

Inserting values in tblOrderWithIndex table

insert into tblOrderWithIndex values(10,1234,‘Apple Iphone 5’,23000,22000);

insert into tblOrderWithIndex values(1,1235,‘Samsung Note 3’,21000,20000);

Let’s run the select command to check how the records are being retrieved in an order or not?

Figure 12:
Order wise records are retrieved from the database.

Let’s try the same with table without index.


insert into tblOrder values(10,1234,‘Apple Iphone 5’,23000,22000);

insert into tblOrder values(1,1235,‘Samsung Note 3’,21000,20000);

Figure 13.
Checking if there is any Index present in table
tblOrder.

Figure 14.
Un Ordered records are retrieved from the table.

I.e the Clustered Index stores the records in an ordered format while if there is no index the records are not stored in order format.

We can have only one Clustered Index but however the index can have multiple columns can be termed as (Composite Index)

In order to create the Composite Index, we just add the column name to the Index

CREATE CLUSTERED INDEX
In_tblOrderWithIndex_skui_id_description
on
tblOrderWithIndex(description asc,sku_id asc)

If we try to execute the same we will face an error as mentioned below:

Msg 1902, Level 16, State 3, Line 2

Cannot create more than one clustered index on table ‘tblOrderWithIndex’. Drop the existing clustered index ‘PK__tblOrder__46596229992B8137’ before creating another.

So we need to first drop the first Index and create the new composite index.

Figure 15.
Deleting Index.

Delete the Index and create the composite index keys

CREATE CLUSTERED INDEX
In_tblOrderWithIndex_skui_id_description
on
tblOrderWithIndex(description asc,sku_id asc)

Now let’s execute our select command to retrieve the rows and check how the records are now stored.

Figure 16.
Fetching records with Composite Clustered Index.

We can see that now we are getting records based on sku id and description in asc mode.

4. Non-clustered Index

Non-clustered index is a pointer to the data. The data is stored in one place, the index in another place. The index will have a pointer to the location of the data. Since, the data is stored in another place than the actual data we can have a different Non-Clustered Index. We can determine the following as shown below where we have a pointer table with a row address pointing to the actual data.

order_id

sku_id

Description

mrp

sp

2

1233

Apple Iphone 4

11000

10000

10

1234

Apple Iphone 5

23000

22000

1

1235

Samsung Note 3

21000

20000

Table 1.
Actual Data Stored.

order_id ROW Locator

2

ROW ADDRESS

10

ROW ADDRESS

1

ROW ADDRESS

Table 2.
Reference Table pointing towards the Actual Table rows address.

`

Figure 17.
Non Clustered Index pointing towards Clustered Index

Image source: https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx

A Non Clustered Index can be typically be imagined as Book Index.

Img source: https://www.prismnet.com/~hcexres/textbook/images/print_index.gif

Figure 18.
Book Index Page.

We can have a many Non-Clustered Index as shown in above diagram where we have a number of Indexes. In a Non-Clustered Index the data is stored in the ascending and descending order of the Non-Clustered Index key. The Non-Clustered Index doesn’t influence the way records get inserted in the table. The leaf node of the Non-Clustered index is made up of index pages rather than data pages.

Concluding the Clustered and Non-Clustered.

  • We can have only one clustered Index while we can have as many Non-clustered Index.
  • Clustered Index are faster than the Non Clustered as Clustered Index determines the physical order of the records.

    Disadvantage of Clustered Index: As we have already discussed about the advantages of Clustered Index lets discuss the disadvantage of Clustered index.

    As we know that in Clustered Index determines the physical order of data in the table. So if update a record and change the value of the indexed column, the database will need to move an entire column to a new position to make sure the every row is sorted. So the resulting in update query into a delete query followed by an Insert query which obviously leads to decrease in performance.

References

http://odetocode.com/articles/70.aspx

https://www.simple-talk.com/content/article.aspx?article=934


ASP.NET WEB API Quick Start

Abstract

This article is based on learning concepts of ASP.NET Web API in order to create REST based API. In this article we will cover from basics to vast topics. So let’s get started with Day 1.

1 Introduction

As I am an ASP.NET developer I have been working in ASP.NET Web Forms, ASP.NET MVC, and Web Services etc. Today in this article I will try to demonstrate ASP.NET Web API into simpler terms that would indeed be easy to understand and implement them practically. If you love to watch and learn my honest suggestion would go and watch below series of Web API and be API Developer as well.


https://www.youtube.com/watch?v=cBpHqLukHp4

I won’t make any false assumptions for any technology. But as I have been working with Web Services and Web API. I can certainly found Web API to be much simpler, robust in all scenarios. Most of you would think why I am talking about Web Services when we have WCF. Yeah you are correct, as I don’t have in depth knowledge of WCF so I won’t comment on that. I would be happy if as a reader you dig into the open web and find the benefits of Web API over WCF as well. So let’s get started starting with definition of ASP.NET Web API.

“ASP.NET Web API is a HTTP services which follows HTTP REST protocols that can reach broad range of client’s i.e. Browser, mobile, tablets”

1.1 ASP.NET Web API

In short ASP.NET Web Api is a programming interface to define request and response message system. REST is a principle, way to use HTTP. HTTP Hyper Text Transfer Protocol was made of passing information from client to server and vice versa. ASP.NET Web API makes use of HTTP as their transfer protocol. ASP.NET Web API can be used to build or consume HTTP services as compared to SOAP. ASP.NET Web Api don’t force you to use HTTP principles which we are going to see in a while. ASP.NET Web Api takes advantage of HTTP like Media Type, HTTP message, Status Code, Content Negotiation, Caching etc. ASP.NET Web Api is an Open Source Project. These days Microsoft has been open sourcing there each and every new product, as a thought of that we can say that .net is moving towards below quote.

“NET –> May the Source be with you”


Figure 1: Source: https://twitter.com/markemalek

Scott Hanselman:
Principal Program Manager – Community Architect – Web Platform and Tools · Microsoft.

1.2 HTTP Protocol

HTTP is a text based protocol. You can read the specification from this URI https://www.w3.org/Protocols/rfc2616/rfc2616.html

HTTP request starts with a first line containing HTTP verbs (HTTP defined methods are called HTTP verbs) URL and HTTP Version. The HTTP header is a name-value field separated by a colon (:) and blank space called Payload.

POST http://localhost:51180/api/TodoItems HTTP/1.1 –HTTP Verbs
content-type: application/json --Headers
Host: localhost:51180 –Headers
 --Payload
{
Title:"Saillesh"
}

HTTP Response starts with Status Code and its corresponding description and subsequently headers each line as shown below: The HTTP header is a name-value field separated by a colon (:) and blank space called Payload.

HTTP/1.1 201 Created
Cache-Control: no-cache
Pragma: no-cache
Content-Type: application/json; charset=utf-8
Expires: -1
Location: http://localhost:51180/api/TodoItems/2
Server: Microsoft-IIS/10.0
--Payload
{"TodoItemId":2,"Title":"Saillesh","IsDone":false}
 

1.3 ASP.NET WEB API Framework


Figure2: HTTP Web API mechanism


Figure3: ASP.NET WEB API can be used in variety of scenario.

1.3.1 HTTP Verbs

Before moving ahead towards coding let’s have a look over HTTP verbs. Http Verbs HTTP method that have been set in the HTTP specification.

GET :
GET Verb is used to get information about the resource. Resource is basically specified in the URI as shown below:

GET http://localhost:51180/api/TodoItems/2 HTTP/1.1
Host: localhost:51180

GET is the Verb followed by URI

HOST: Where the request is going with respect to domain

POST

POST Verb is used when we send data to save/created in the body section.

POST http://localhost:51180/api/TodoItems HTTP/1.1
Content-type: application/json
Host: localhost:51180
{
Title:"Saillesh"
}

POST is the Verb followed by URI

HOST: Where the request is going with respect to domain

Content-Type: Type of content I am sending to the server i.e. application/json because I am sending json format.

Body: Body contains the Request format

PUT

PUT Verb is used when we want to update the resource information

Put http://localhost:51180/api/TodoItems HTTP/1.1

content-type: application/json

Host: localhost:51180

{
TodoItemId:3,
Title:"Rahul"
}

DELETE

DELETE Verb is used to delete resource from the server.

DELETE http://localhost:51180/api/TodoItems/3

content-type: application/json

Host: localhost:51180

1.3.2 HTTP STATUS CODE

As we are following REST Principle which uses HTTP, we have to be aware of HTTP Status Codes

  • HTTP/1.1 200 OK – Specifies everything is ok the verb you performed has been successfully completed i.e. server found the resource
  • 4o4 Server could not be found
  • 503: Service Unavailable

Status codes are divided into 5 classes as show below:

  • 1XX: Informational
  • 2XX: Success i.e. Client request was successfully identified and accepted
  1. 200 OK GET
  2. 201 CREATED POST
  • 3XX: Redirection i.e. client need further action in order to complete the request.
  • 4XX: Client Error i.e. where client seems to have some wrong
  1. 401 Unauthorized
  2. 400 Bad Request
  • 5XX: Server Problem

1.3.3 Content Negotiation

The word Content Negotiation means the client would send Accept Headers to the server that I understand these media type formatter i.e. (application/json, application/xml, application/bson etc). Please provide the response to be represented in this format. This part of HTTP Is referred as Content Negotiation or Conneg.

An Accept Header is initiated by the client. This header tell the server that the client is supporting these media types. The server gives back the response of its server driven content negotiation based on Conneg algorithm. When a client sends Accept Header to the server that tell the server the media type(s) the client want from the server.

Accept

  • If a request is send to the server without any accept header and with a content type in headers. Than Conneg algorithm will choose Content type header to decide about Response media type to respond.
  • If a request is send with an Accept Header, Conneg algorithm will use the Accept Header information to decide the Response media type to send the response.
  • If client give us content type as application/json and accept-header: application/xml than conneg algorithm will use content type to serialize the input and accept header to decide the response media type formatter to write.
  • If somehow conneg algo. Is not able to find the best fit media type formatter to be send in the Accept Header, than it’s uses the best effort approach and uses the content-type header to decide the response media type formatter to write.
  • If in worst scenario if conneg algorithm is not able to find the correct response for the request, it uses the first media type formatter in collection i.e. application/json.

3. Implementing simple ASP.NET Web API Practically

To Demonstrate the ASP.NET Web API I will be using visual studio 2015. So let’s gets started.

Figure4:
Create New Project


Figure5:
Selecting Web API and Press Ok

Note* ASP.NET WEB API can be used within

  • Web form Application
  • MVC Application
  • Console Application
  • Windows
  • WPF etc.


Figure6:
Project Loading Finished.

ASP.NET WEB API uses MVC architecture which means Model, View, and Controller. To learn about MVC architecture my honest suggestion for you is to watch the below video and clear your concepts of ASP.NET MVC.


https://www.youtube.com/watch?v=-c1sI8666Ac

In MVC our MODEL is our C# class, Controller is also an object which handles the client request and get the required model applies the logic with business class and send the data integrated with html to the client in the form of view.


Figure7:
Project Structure.

So let’s add our Model. In this Web Api we will performing CRUD Operations (Create, Read, Update, and Delete) operation on our Customer Model. We will be using Entity Framework which is an ORM tools which will communicate with our database schema. If you want to learn about Entity Framework my honest suggestion for you is to watch the below video and clear your concepts of Entity Framework.


https://www.youtube.com/watch?v=b6vTIiBNcJ0

Adding a Model


Figure8:
Adding a Model to your Project.

namespace WebApiDemo.Models
{
 public class Customer
 {
 public int Id { get; set; }
  [Required,MaxLength(50)]
 //Name field is mandatory and max length is 50
 public string Name { get; set; }
  [Required, MaxLength(10)]
 //Mobile field is mandatory and max length is 10
 public string Mobile { get; set; }
 } }
 

Adding a Controller


Figure9:
Adding a
Controller class to your Project


Figure10:
Selecting an Empty Controller


Figure11:
Naming the Controller with Model Name

Note* I won’t be implementing dependency injection and other design patterns. In Enterprise application our model, API, interface and dal has to be in separate class libraries.

Now let’s set up our DAL Class which will communicate with Database. I will be using EF code first approach with existing db table

Script for creating table:
 
CREATE TABLE [dbo].[Customer](    [Id] [int] NOT NULL identity(1,1),
     [Name] [varchar](50) NOT NULL,
     [Mobile] [varchar](50) NOT NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
    [Id]
 AS
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
 OFF, IGNORE_DUP_KEY
 =
 OFF, ALLOW_ROW_LOCKS
 =
 ON, ALLOW_PAGE_LOCKS
 =
 ON) ON
 [PRIMARY]
)
 ON
 [PRIMARY]


Figure13:
Table structure and columns and data types

Add DAL Class which will be responsible for talking with database table Customer.

Inherit the class from DbContext. DbContext gives the class all the setup that is needed to do the operation you want to do with DB Schema, or we can say it allows us to communicate with a DB.

namespace WebApiDemo.Models
{
 //DbContext gives the class all the setup that is needed to do the operation 
 //you want to perform with DB Schema, or we can say it allows us to communicate with a DB.

 public class DAL:DbContext
 {
 // DB Set maintains the DB connection with database 
 
public DbSet<Customer> customers { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 //mapping with the Customer Table 
  modelBuilder.Entity<Customer>().ToTable("Customer");
 }
 }
}

Adding a Connection string Click on Server Explorer


Figure14:
Configuring the connection


Figure15:
Add Connection


Figure16:
Select your server name and enter your database name click ok.


Figure17: Demonstrating Right Click on Properties and go to Connection string


Figure18: Demonstrating connection string on Connection properties

<add
name=DAL
connectionString=Data Source=DEVELOPER-VAIO;Initial Catalog=Demos;Integrated Security=True
providerName=System.Data.SqlClient/>

Adding our first HTTP VERB into the controller

  1. POST
//used for help page to get the request and response type
 [ResponseType(typeof(Customer))]
 public HttpResponseMessage POST(Customer cust)
 {
 //if model validation gets fail return the Bad Request and return the model state
 if (!ModelState.IsValid)
 {
 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
 }
  cust= _context.customers.Add(cust);
 _context.SaveChanges();
 // data inserted successfully return the 201 created status and return the resource uri
 var response = Request.CreateResponse<Customer>(HttpStatusCode.Created, cust);
 response.Headers.Location = new
 Uri(Request.RequestUri, string.Format("customer/{0}", cust.Id));
 return response;
  }
 
  1. GET All
public HttpResponseMessage Get()
 {
 //get the list of customers from tabl
 var customers = _context.customers.ToList();
 //if customers is not null
  if (customers != null)
 {
 //return Customers with status 200 and response
 if (customers.Any())
  return Request.CreateResponse(HttpStatusCode.OK, customers);
 }
 // return not found response 
 return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Customers not found");
 }
  1. Get Single Customer
 [ResponseType(typeof(Customer))]
 public HttpResponseMessage Get(int Id)
 {
 //get the list of customer from table
 var customer = _context.customers.Where(cust => cust.Id == Id).Single();
 //if customer is not null
 if (customer!=null)
 {//return Customer with status 200 and response
 return Request.CreateResponse(HttpStatusCode.OK, customer);
 }
 // return not found response 
  return Request.CreateResponse(HttpStatusCode.NotFound, "Customer not found");
 }

4.PUT

public HttpResponseMessage Put(int id, [FromBody]Customer Cust)
  {
 if (!ModelState.IsValid)
 {
 return Request.CreateResponse(HttpStatusCode.BadRequest, ModelState);
 }
 if (id != Cust.Id)
 {
 return Request.CreateResponse(HttpStatusCode.BadRequest);
}
 _context.Entry(Cust).State = EntityState.Modified;
 try
 {
 _context.SaveChanges();
 }
 catch (DbUpdateConcurrencyException)
 {
 throw;
 }
 return Request.CreateResponse(HttpStatusCode.NoContent);
 }
 
  1. Delete
public HttpResponseMessage DeleteCustomer(int id)
 {
 //retrieve the customer first
 Customer customer = _context.customers.Find(id);
 //if customer object is null
 if (customer== null)
 {
 //return HttpStatusCode.NotFound response
 Request.CreateResponse(HttpStatusCode.NotFound, "No records found");
 }
 //remove from the table
 _context.customers.Remove(customer);
 //save changes in database
 _context.SaveChanges();
 //return response 200
 return Request.CreateResponse(HttpStatusCode.OK);
}
 

That’s it we have our working ASP.NET WebApi. Each Controller HTTP method has their corresponding URI. In ASP.NET Web API we use host/API/ControllerName which quiet differ from normal ASP.NET MVC routes. In ASP.NET Web API project we have WebApiConfig class for Web API routes

config.Routes.MapHttpRoute(

name: “DefaultApi”,

routeTemplate: “api/{controller}/{id}”,

defaults: new { id = RouteParameter.Optional }

We can see api/controller is the key here. Reason why using api in the routes is to avoid collision with ASP.NET MVC routing.

ControllerMethod

URI

POST API/POST
Get API/GET
Get(int Id) API/GET/ID
Put API/GET/ID
DeleteCustomer API/DELETE/ID
  1. Consuming Your Web API

In order to consume these API method we need a test client. We have various test clients softwares eg. Fiddler, POSTMAN, WebApiTest Package in Nuget, SOAP UI etc. I will be using google chrome extension POSTMAN for the same. Let get started.

If you don’t have POSTMAN my honest suggestion would be add the extension in your Google Chrom from below link.

https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en

When you install the extension and Chrome App Launcher icon will created click on it and open POSTMAN or you can search for POSTMAN as shown below:


Figure19: Demonstrating searching POSTMAN via search.

Once you open the POSTMAN following window will open


Figure20: HomePage of POSTMAN REST client.


Figure21: Demonstrating BASIC terminology with POSTMAN.

Now In order to perform POST we will need to keep few points.

  • Change HTTP verb from GET to POST.
  • Define the Content type in header to tell the API that I am sending this media type.
  • Define the Accept Header to tell the server I understand this media type more easily.
  • Body: Sending JSON Body to be saved on the server.

POSTMAN Provide us autocomplete features by which we can easily enter the required details.


Figure22: Adding Content Type Header in POSTMAN.

Once we are done with headers our POSTMAN window will look like this as shown below:


Figure23: Configuring Headers and URI in POSTMAN.

Once we are done with headers now add your request body to be inserted to the DB. In order to add the request body click on body


Figure24: Configuring request Body.

Now click on Raw and enter your request in the json format, as we have written in headers that the content we are sending is of type application/json.


Figure25: Request body with properties names and value to be binded to object.

Wow we are ready fire our first HTTP verb POST just click on Send. Put a break point using f9 in your POST method.

POST:


Figure26: Break Point hits and we can see the passed json details are de-serialized to the customer object.

If the model state is valid it will perform the adding of the customer.


Figure27: Demonstrating Details being successfully stored in DB.

Once the details have been saved in DB HTTP StatusCode.Created is send to the client i.e. 201 and the location where the created resource present.


Figure28: Demonstrating Details being successfully stored in DB.

Now let’s GET the resource using the Location we received from POSTMAN. Add a new tab and insert all the details (Headers, verb). In GET method we not need to supply the body as we will pass id in query string.

http://localhost:56850/API/customer/1


Figure29: Demonstrating configuring GET verb in POSTMAN.

As we are done with configuring POSTMAN GET request hit send, the break point will get hit and the passed id will be binded to the Id variable.

GET:


Figure30: Step by Step Get Verb.

Once the whole statements are executed we will get below json response:

{
 "Id": 1,
 "Name": "Saillesh",
 "Mobile": "1234567890"
}

Headers:


Figure31: GET response.

PUT:


Figure32: Demonstrating configuring PUT verb in POSTMAN.

Now hit the send button.


Figure33: Step by Step PUT Verb.

Here I am updating old details of id 1 “Saillesh, 1234567890” to “Pawar,12345”.


Figure34: PUT Response

204 No Content

The server has recognized the request and has successfully processed but doesn’t need to return an entity body.

Let check the output in DB.


Figure35: Confirming records got updated.

DELETE:


Figure36 Step by Step DELETE Verb

Once the breakpoint is hit customer details will fetched and if it found then it will be deleted with success code 200. OK.

    

Figure37: Delete Response.


Acknowledgements

We found how easy it’s is to create REST based services using ASP.NET web API. Wow we have finally created a simple ASP.NET Web API give yourself high 5 from my side. There’s lot more to learn in ASP.NET web API stay tuned for more ASP.NET web API. The source code has been upload to Git Hub Repository. Feel free to download and test.

https://github.com/SailleshPawar/ASP.NET-WEB-API-DEMO/

References: