Creating a Web API that Supports CRUD Operations

This tutorial shows how to support CRUD operations in an HTTP service using ASP.NET Web API.

CRUD stands for “Create, Read, Update, and Delete,” which are the four basic database operations. Many HTTP services also model CRUD operations through REST or REST-like APIs.

In this tutorial, you will build a very simple web API to manage a list of products. Each product will contain a name, price, and category (such as “toys” or “hardware”), plus a product ID.

Download the completed project.

The products API will expose following methods.

Action HTTP method Relative URI
Get a list of all products GET /api/products
Get a product by ID GET /api/products/id
Get a product by category GET /api/products?category=category
Create a new product POST /api/products
Update a product PUT /api/products/id
Delete a product DELETE /api/products/id

Notice that some of the URIs include the product ID in path. For example, to get the product whose ID is 28, the client sends a GET request for http://hostname/api/products/28.


The products API defines URIs for two resource types:

Resource URI
The list of all the products. /api/products
An individual product. /api/products/id


The four main HTTP methods (GET, PUT, POST, and DELETE) can be mapped to CRUD operations as follows:

  • GET retrieves the representation of the resource at a specified URI. GET should have no side effects on the server.
  • PUT updates a resource at a specified URI. PUT can also be used to create a new resource at a specified URI, if the server allows clients to specify new URIs. For this tutorial, the API will not support creation through PUT.
  • POST creates a new resource. The server assigns the URI for the new object and returns this URI as part of the response message.
  • DELETE deletes a resource at a specified URI.

Note: The PUT method replaces the entire product entity. That is, the client is expected to send a complete representation of the updated product. If you want to support partial updates, the PATCH method is preferred. This tutorial does not implement PATCH.

Create a New Web API Project

Start by running Visual Studio and select New Project from the Start page. Or, from the File menu, select Newand then Project.

In the Templates pane, select Installed Templates and expand the Visual C# node. Under Visual C#, select Web. In the list of project templates, select ASP.NET MVC 4 Web Application. Name the project “ProductStore” and click OK.

In the New ASP.NET MVC 4 Project dialog, select Web API and click OK.

Adding a Model

model is an object that represents the data in your application. In ASP.NET Web API, you can use strongly typed CLR objects as models, and they will automatically be serialized to XML or JSON for the client. Continue reading “Creating a Web API that Supports CRUD Operations”

ActionLink htmlAttributes “data-“

The problem is that your anonymous object property data-icon has an invalid name. C# properties cannot have dashes in their names. There are two ways you can get around that:

Use an underscore instead of dash (MVC will automatically replace the underscore with a dash in the emitted HTML):

@Html.ActionLink("Edit","edit","markets",new{ id =1},new{@class="ui-btn-right", data_icon="gear"})

Use the overload that takes in a dictionary:

@Html.ActionLink("Edit", "edit", "markets",
      new { id = 1 },
      new Dictionary<string, object> { { "class", "ui-btn-right" }, { "data-icon", "gear" } });


linq: LET clause (C# Reference)

In a query expression, it is sometimes useful to store the result of a sub-expression in order to use it in subsequent clauses. You can do this with the let keyword, which creates a new range variable and initializes it with the result of the expression you supply. Once initialized with a value, the range variable cannot be used to store another value. However, if the range variable holds a queryable type, it can be queried.

In the following example let is used in two ways:

  1. To create an enumerable type that can itself be queried.
  2. To enable the query to call ToLower only one time on the range variable word. Without using let, you would have to call ToLower in each predicate in the where clause.
class LetSample1
    static void Main()
        string[] strings = 
            "A penny saved is a penny earned.",
            "The early bird catches the worm.",
            "The pen is mightier than the sword." 

        // Split the sentence into an array of words 
        // and select those whose first letter is a vowel. 
        var earlyBirdQuery =
            from sentence in strings
            let words = sentence.Split(' ')
            from word in words
            let w = word.ToLower()
            where w[0] == 'a' || w[0] == 'e'
                || w[0] == 'i' || w[0] == 'o'
                || w[0] == 'u'
            select word;

        // Execute the query. 
        foreach (var v in earlyBirdQuery)
            Console.WriteLine("\"{0}\" starts with a vowel", v);

        // Keep the console window open in debug mode.
        Console.WriteLine("Press any key to exit.");
/* Output:
    "A" starts with a vowel
    "is" starts with a vowel
    "a" starts with a vowel
    "earned." starts with a vowel
    "early" starts with a vowel
    "is" starts with a vowel


Resizing an iframe based on content

If you do not need to handle iframe content from a different domain, try this code, it will solve the problem completely and it’s simple:

<script language="JavaScript">
function autoResize(id){
    var newheight;
    var newwidth;

        newheight=document.getElementById(id).contentWindow.document .body.scrollHeight;
        newwidth=document.getElementById(id).contentWindow.document .body.scrollWidth;

    document.getElementById(id).height= (newheight) + "px";
    document.getElementById(id).width= (newwidth) + "px";

<i frame src="usagelogs/default.aspx" width="100%" height="200px" id="iframe1" marginheight="0" frameborder="0" onLoad="autoResize('iframe1');"></iframe>


onLoad="autoResize('iframe1');" //this is executed after load frame


Custom Filters in MVC – Authorization , Action, Result, Exception Filters

Filters in MVC are attributes which you can apply to a controller action or an entire controller. This will allow us to add pre and post behavior to controller action methods.

There are 4 types of Filters. Which were described in above image.

Objective: Learn about filters and create custom filters for better understanding.

Step 1: Create a simple MVC Web application. Lets see the Output Cache filter first.
I created a View and pertaining Action method. See them below.


    ViewBag.Title = "OutPutTest";

Action Method:

//OutPutTest Action Method
 public ActionResult OutPutTest()
   ViewBag.Date = DateTime.Now.ToString("T");
   return View();

Output : so as per the current implementation, the view should be displaying current time with seconds. so for every refresh, the seconds part will be changed…
But observe the [OutputCache(Duration=10)] attribute applied to action method. This will make the response cached for 10 seconds. Thus the seconds part will not be changed for next 10 seconds.

Step 2: Authorization Filter : This filter will be executed once after user is authenticated
In this step lets create a custom Authorization filter. For this create a class which inherits AuthorizeAttribute or implements IAuthorizationFilter interface. All we are doing here is just passing a message to View.

public class CustAuthFilter : AuthorizeAttribute
        public override void OnAuthorization(AuthorizationContext filterContext)
            filterContext.Controller.ViewBag.AutherizationMessage = "Custom Authorization: Message from OnAuthorization method.";

Now we have our CustomAuthFilter which will be executed immedealty after user is authenticated. But inorder to make it happen we need to apply this [CustAuthFilter] attribute on top either a custom action or to an entire controller itself.
We have another method OnUnauthorizedRequest event to redirect the unauthorized users to some default pages.

Step 3: Action Filter : There are 4 events available in an action filter.
#1.OnActionExecuting – Runs before execution of Action method.
#2.OnActionExecuted – Runs after execution of Action method.
#3.OnResultExecuting – Runs before content is rendered to View.
#4.OnResultExecuted – Runs after content is rendered to view.
So lets create a CustomActionFilter. Create a class inherting ActionFilterAttribute class of implementing IActionFilter and IResultFilter interfaces.

public class CustomActionFilter : ActionFilterAttribute
        public override void OnActionExecuting(ActionExecutingContext filterContext)
            filterContext.Controller.ViewBag.CustomActionMessage1 = "Custom Action Filter: Message from OnActionExecuting method.";

        public override void OnActionExecuted(ActionExecutedContext filterContext)
            filterContext.Controller.ViewBag.CustomActionMessage2 = "Custom Action Filter: Message from OnActionExecuted method.";

        public override void OnResultExecuting(ResultExecutingContext filterContext)
            filterContext.Controller.ViewBag.CustomActionMessage3 = "Custom Action Filter: Message from OnResultExecuting method.";

        public override void OnResultExecuted(ResultExecutedContext filterContext)
            filterContext.Controller.ViewBag.CustomActionMessage4 = "Custom Action Filter: Message from OnResultExecuted method.";

Now all we need to do is to apply [CustomActionFilter] attribute on an Action Method.

Step 4: Exception Filter: This filter is used to capture any execptions if raised by controller or an action method. Create a class which will inherit FilterAttribute class and implement IExceptionFilter interface.

public class CustExceptionFilter : FilterAttribute, IExceptionFilter
        public  void OnException(ExceptionContext filterContext)
            filterContext.Controller.ViewBag.ExceptionMessage = "Custom Exception: Message from OnException method.";

Now all we need to do to handle any exceptions or erros is to apply [CustExceptionFilter]attribute on an Action Method.

Step 5: Now we have all custom filters created. Lets decorate our index action method with them.
Contoller code:

namespace CustomActionFilterMVC.Controllers
    public class HomeController : Controller
        public ActionResult Index()
            ViewBag.Message = "Welcome to ASP.NET MVC!";
            return View();            

        public ActionResult About()
            return View();

        public ActionResult OutPutTest()
            ViewBag.Date = DateTime.Now.ToString("T");
            return View();

Step 6: Now update Index view to reflect all the messages from cutom filters.

    ViewBag.Title = "Home Page";

<h2>Output Messages :</h2>
<br />
<br />
<br />
<br />
<br />
<br />

Step 7: Now execute the application and see the out put.

Now compare the output with the view definition and see the differences.

First thing is , we dint have any message from OnResultExecuted event.
Reason: That event will be executed after content is rendered to view, so by that time the view is rendered,  OnResultExecuted event is not and message is not yet assigned to ViewBag.

Second thing, we dint have any message from exception filter. If there is any exception raised by the code, then the Exception Filter codee will come into picture.

with this , we have covered different kinds of filters in MVC and how to create custom filters and how to apply them.


What is the best way to paginate results in SQL Server

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?

Microsoft SQL Server 2008 and previous

Getting the total number of results and paginating are two different operations. For the sake of this example, let’s assume that the query you’re dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

…which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don’t have to keep any state, except the row numbers to be returned.

Microsoft SQL Server 2012


source: What is the best way to paginate results in SQL Server

Entity framework – Multiple Diagrams per Model

This video and page shows how to split a model into multiple diagrams using the Entity Framework Designer (EF Designer). You might want to use this feature when your model becomes too large to view or edit.

In earlier versions of the EF Designer you could only have one diagram per the EDMX file. Starting with Visual Studio 2012, you can use the EF Designer to split your EDMX file into multiple diagrams.

See the video…

EF Designer Overview

When you create a model using the EF Designer’s Entity Data Model Wizard, an .edmx file is created and added to your solution. This file defines the shape of your entities and how they map to the database.

The EF Designer consists of the following components:

  • A visual design surface for editing the model. You can create, modify, or delete entities and associations.
  • Model Browser window that provides tree views of the model.  The entities and their associations are located under the [ModelName] folder. The database tables and constraints are located under the [ModelName].Store folder.
  • Mapping Details window for viewing and editing mappings. You can map entity types or associations to database tables, columns, and stored procedures.

The visual design surface window is automatically opened when the Entity Data Model Wizard finishes. If the Model Browser is not visible, right-click the main design surface and select Model Browser.

The following screenshot shows an .edmx file opened in the EF Designer. The screenshot shows the visual design surface (to the left) and the Model Browser window (to the right).

EF Designer showing a model and the Model Browser window

To undo an operation done in the EF Designer, click Ctrl-Z.

Working with Diagrams

By default the EF Designer creates one diagram called Diagram1. If you have a diagram with a large number of entities and associations, you will most like want to split them up logically. Starting with Visual Studio 2012, you can view your conceptual model in multiple diagrams.

As you add new diagrams, they appear under the Diagrams folder in the Model Browser window. To rename a diagram: select the diagram in the Model Browser window, click once on the name, and type the new name.  You can also right-click the diagram name and select Rename.

The diagram name is displayed next to the .edmx file name, in the Visual Studio editor. For example Model1.edmx[Diagram1].

Diagram name displayed in file name and in Model Browser

The diagrams content (shape and color of entities and associations) is stored in the .edmx.diagram file. To view this file, select Solution Explorer and unfold the .edmx file.

.diagram file shown in Solution Explorer

You should not edit the .edmx.diagram file manually, the content of this file maybe overwritten by the EF Designer.

Splitting Entities and Associations into a New Diagram

You can select entities on the existing diagram (hold Shift to select multiple entities). Click the right mouse button and select Move to new Diagram. The new diagram is created and the selected entities and their associations are moved to the diagram.

Alternatively, you can right-click the Diagrams folder in Model Browser and select Add new Diagram. You can then drag and drop entities from under the Entity Types folder in Model Browser onto the design surface.

You can also cut or copy entities (using Ctrl-X or Ctrl-C keys) from one diagram and paste (using Ctrl-V key) on the other. If the diagram into which you are pasting an entity already contains an entity with the same name, a new entity will be created and added to the model.  For example: Diagram2 contains the Department entity. Then, you paste another Department on Diagram2. The Department1 entity is created and added to the conceptual model.

To include related entities in a diagram, rick-click the entity and select Include Related. This will make a copy of the related entities and associations in the specified diagram.

Changing the Color of Entities

In addition to splitting a model into multiple diagrams, you can also change colors of your entities.

To change the color, select an entity (or multiple entities) on the design surface. Then, click the right mouse button and select Properties. In the Properties window, select the Fill Color property. Specify the color using either a valid color name (for example, Red) or a valid RGB (for example, 255, 128, 128).

Entities displayed in EF Designer with red and blue coloring applied