Getting started with ASP.NET Web API part 2

This is the sequel from this post. Enter the micro ORM I happen to like micro ORM's. I love plain SQL. I love minimalism. So that rules out the Entity Framework for me. My favorite is PetaPoco. I use it all the time. After the AddressBook database has been created, you will need to create a Person class that matches the Person table in the database:

 public class Person
    {
        public int Person_Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Company { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string County { get; set; }
        public string State { get; set; }
        public string ZIP { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        public string Email { get; set; }
        public string Web { get; set; }
    }

And then open the Nuget package manager console and install PetaPoco:

Install-Package PetaPoco.Core

And now the CRUD. Retrieving persons from the database Edit PersonController.cs to make a call to the database using PetaPoco:

using System.Net.Http;
using System.Web.Http;
using System.Collections;
using AddressBook.Api.Models;
using SqlFu;
using System.Net;

namespace AddressBook.Api
{
   public class PersonController : ApiController
    {
        public PetaPoco.Database db =
            new PetaPoco.Database("AddressBookConnectionString");

         public IEnumerable Get()
            {
              var persons =
                  db.Query("SELECT * FROM PERSON ORDER BY LastName");
              return persons;
            }
     }
}

Next hit F5 and browse to http://localhost1234/Person or whatever port your webserver is running on.

That doesn't work:

Its throwing an exception: "The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'" The solution is disabling the XML formatter completely and have it return JSON all the time. This is done in Global.asax in the Application_Start method. You should add the following lines:

var json = config.Formatters.JsonFormatter;
json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;
config.Formatters.Remove(config.Formatters.XmlFormatter);

That makes the Application_Start method in Global.asax look like this:

protected void Application_Start(object sender, EventArgs e)
   {
            var config = GlobalConfiguration.Configuration;
            config.Routes.MapHttpRoute(
               name: "default",
               routeTemplate: "{controller}/{id}",
               defaults: new
               {
                   id = RouteParameter.Optional
               });

            //disable XML formatting and return JSON all the time
            var json = config.Formatters.JsonFormatter;
            json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;
            config.Formatters.Remove(config.Formatters.XmlFormatter);
        }

I honestly don't know why I had to remove the XMLFormatter to make this work. I guess the POCO is not XML compatible or whatsoever. Some investigation is in order I suppose. However disabling XML works. After altering Global.asax everything works as expected.

.

Adding a person to the database

Lets add a person to the database, using Fiddler. First, let's create a POST method in PersonController.cs. It returns the URI for the new Person:

public HttpResponseMessage Post([FromBody] Person person)
   {
      db.Insert("Person", "Person_Id", person);
      var response = Request.CreateResponse(HttpStatusCode.Created, person);

      string uri = Url.Link("Default", new { id = person.Person_Id });
      response.Headers.Location = new Uri(uri);
      return response;
    }

In Fiddler:

Click Execute.

. T

The result is, as you can see, the header location that contains the Id for the inserted record. Hello Kitty is added to the database. The WebApi has consumed the input and PetaPoco has duly delivered it to the Persons table.

Getting a single person from the database

Now we now how this works, getting a single person is simple: Add this method:

  public Person Get(int id)
         {
             var p = db.Single("SELECT * FROM PERSON WHERE Person_Id = @0", id);
             return p;
         }

Click Execute:

Updating a single person from the database

Add the following code to the PersonController:

 public void Put([FromBody] Person person)
         {
             var p = db.Single("SELECT * FROM PERSON WHERE Person_Id = @0", person.Person_Id);
             db.Update("Person", "Person_Id", person);
         }

Now choose PUT in fiddler and choose the Id you want edit: Click Execute. The person will be updated.

Removing a person from the database

To remove a person add this method to the PersonController.cs:

public void Delete(int id)
         {
             var p = db.Single("SELECT * FROM PERSON WHERE Person_Id = @0", id);
             db.Delete("Person", "Person_Id", p);
         }

To test it set Fiddler to DELETE: There you go, we now have a very basic Web API. We do not have a client yet. Also, we just have one table/entity.

Not very exciting so far. I'm going to turn this up a nudge. Next time, I will migrate the database to MongoLab and start building a client with Angularjs.