Consume Web API in a .NET C# client

Recently I have written a console client in C# to consume those web services developed in my previous post by using APS.NET Web API 2 and Microsoft Entity Framework 6.

I started with a new Console Application (called WebApiClient) in C# in Visual Studio 2013, and then installed the Microsoft ASP.NET Web API Class Libraries using NuGet. Lastly, I added Employee.cs (model class) which was used by web services as my data structure.

The following is a web client program in C# (Program.cs):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net.Http;
using System.Net.Http.Headers;

namespace WebApiClient
{
    class Program
    {
        static void Main(string[] args)
        {
            HttpClient client = new HttpClient();
            string baseUrl = "http://localhost:55142";
            client.BaseAddress = new Uri(baseUrl);
            
            // Add an Accept header for JSON format.
            client.DefaultRequestHeaders.Accept.Add(
            new MediaTypeWithQualityHeaderValue("application/json"));

            //New Employee Record URL
            int anEmpId = -1;
            string serviceUrl;


            // 1. Post a new employee
            serviceUrl="api/employee";
            Console.WriteLine("1. Add an employee by sending POST {0}/{1}  \r\n", baseUrl, serviceUrl);
            Console.WriteLine("The following new employee is added  \r\n");
            var anEmployee = new Employee() { 
                lastname = "Smith",
                firstname = "Adam",
                title = "Economist",
                titleofcourtesy = "Dr.",
                birthdate = Convert.ToDateTime("1958-12-05 "),
                hiredate = Convert.ToDateTime("1990-01-01"),
                address = "188 Bay Street",
                city = "London",
                region = "",
                postalcode = "A1B 2Z3",
                country = "UK",
                phone = "(71) 234-8228",
                mgrid = 1};
            

            HttpResponseMessage response = client.PostAsJsonAsync(serviceUrl, anEmployee).Result;
            if  (response.IsSuccessStatusCode)
            {

                // display the new employee
                Console.WriteLine("firstname={0}", anEmployee.firstname);
                Console.WriteLine("lastname={0}", anEmployee.lastname);
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }

            Console.WriteLine("\r\nCheck your database table. Press any key to continue...\r\n");
            Console.ReadKey();


            // 2. Get all employees.
            serviceUrl="api/employee";
            Console.WriteLine("2. Get All Employee by sending GET {0}/{1} \r\n", baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if  (response.IsSuccessStatusCode)
            {
                // Parse the response body. Blocking!
                var employees = response.Content.ReadAsAsync<IEnumerable<Employee>>().Result;
                foreach (var e in employees)
                    {
                         
                        if (e.firstname.Equals("Adam") & e.lastname.Equals("Smith"))
                        { 
                            anEmpId = e.empid;
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("{0};\t{1};\t{2};\t{3}", e.empid, e.firstname, e.lastname, e.title);
                            Console.ForegroundColor = ConsoleColor.Yellow;
                            Console.WriteLine("\r\nThe new employee is with empid={0}\r\n", anEmpId);
                            Console.ForegroundColor = ConsoleColor.Gray;
                        }
                        else
                        {
                            Console.WriteLine("{0};\t{1};\t{2};\t{3}", e.empid, e.firstname, e.lastname, e.title);
                        }
                    }
            }
            else
            {
                    Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nPress any key to continue...\r\n");
            Console.ReadKey();

            // 3. Get an employee by empid
            if (anEmpId>0)
            {
                serviceUrl = "api/employee/" + anEmpId.ToString();

            }
            else
            {
                serviceUrl = "api/employee/1";
                anEmpId = 1;
            }

            Console.WriteLine("3. Get the employee with empid={0} by sending GET {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if (response.IsSuccessStatusCode)
            {
                // Parse the response body. Blocking!
                var a_employee = response.Content.ReadAsAsync<Employee>().Result;

                Console.WriteLine("emid={0}", a_employee.empid);
                Console.WriteLine("FirstName={0}", a_employee.firstname);
                Console.WriteLine("LastName={0}", a_employee.lastname);
                Console.WriteLine("Title={0}", a_employee.title);
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nPress any key to continue...\r\n");
            Console.ReadKey();

            // 4. Update an employee by empid
            Console.WriteLine("4. Update an employee with empid = {0} by sending PUT {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if (response.IsSuccessStatusCode)
            {
                // Retrieve the record first
                var a_employee = response.Content.ReadAsAsync<Employee>().Result;
                Console.WriteLine("The record is retrieved before update: \r\n");
                Console.WriteLine("emid={0}", a_employee.empid);
                Console.WriteLine("FirstName={0}", a_employee.firstname);
                Console.WriteLine("LastName={0}", a_employee.lastname);
                Console.WriteLine("Title={0}", a_employee.title);

                // Update the tile field of the record
                a_employee.title = "Senior Economist";

                response = client.PutAsJsonAsync(serviceUrl, a_employee).Result;

                if (response.IsSuccessStatusCode)
                {
                    Console.WriteLine("\r\nThe record with empid={0} was updated with the following: \r\n", anEmpId);
                    Console.ForegroundColor = ConsoleColor.Yellow; 
                    Console.WriteLine("Title={0}", a_employee.title);
                    Console.ForegroundColor = ConsoleColor.Gray;
                }
                else
                {
                    Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
                }
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nCheck your database table. Press any key to continue...\r\n");
            Console.ReadKey();

            // 5. delete an employee added in step 1
            Console.WriteLine("4. Delete an employee with empid = {0} by sending DELETE {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.DeleteAsync(serviceUrl).Result;
             if (response.IsSuccessStatusCode)
             {
                 Console.ForegroundColor = ConsoleColor.Yellow;
                 Console.WriteLine("The record with empid={0} was deleted.", anEmpId);
                 Console.ForegroundColor = ConsoleColor.Gray;
             }
             else
             {
                 Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
             }
             Console.WriteLine("\r\nCheck your database table.\r\n");
        }
    }
}

Using Entity Framework with ASP.NET Web API 2 to build RESTful applications

ASP.NET Web API 2 is a framework for building RESTful applications on the .NET Framework. A broad range of clients, including browsers and mobile devices, can be built to process and present the data through HTTP web services.

In this post, I will build a basic web service by using ASP.NET Web API 2 template, reversed engineer the data model from existing database table by using Entity Framework 6, and finally scaffold and code the web service controller.

ASP.NET Web API template

Create a new project EmployeeWebAPI2 by using C# ASP.NET Application and Web API template in Microsoft Visual Studio 2013.

Reversed Engineering Model From Database

Here is the database schema in Microsoft SQL Server T-SQL.

Right Click on the project in Solution Explorer, and choose ‘Entity Framework – Reversed Engineering Code First’. Then connect to the instance (local) and database (test), and generate the model from test database for table ‘HR.Employees’. For more detail, please refer to my another post.

It will create testContext.cs, Employee.cs and EmployeeMap.cs. I commented out the key and relationship self reference to simply the object class.

Build Web Service Controller

Right Click on Controllers folder, and choose ‘Add->New Scaffold Item”. Choose ‘Web API 2 Controller with read/write Access’. Type ‘EmployeeController’ as controller name.

It will generate a EmployeeController.cs with a list of GET, POST, PUT and DELETE methods. The following steps show how to complete the various web URI methods using EF 6.

1. use the model generated from EF 6.

using EmployeeWebAPI2.Models;

2. GET api/employee

        // GET api/employee
        public IEnumerable<Employee> Get()
        {
            using (testContext entities = new testContext())
            {
                return entities.Employees.ToList<Employee>();
            }
        }

3. GET api/employee/5

        // GET api/employee/5
        public Employee Get(int id)
        {
            using (testContext entities = new testContext())
            {
                return entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
            }
        }

4. POST api/employee

        // POST api/employee
        public HttpResponseMessage Post(Employee value)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (testContext entities = new testContext())
                    {
                        entities.Employees.Add(value);
                        entities.SaveChanges();
                        return Request.CreateResponse(HttpStatusCode.OK);
                    }
                }
                else
                {
                    return Request.CreateResponse(HttpStatusCode.InternalServerError, "invalid New Value");
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

5. PUT api/employee/5

        // PUT api/employee/5
        public HttpResponseMessage Put(int id, Employee value)
        {
            try
            {
                using (testContext entities = new testContext())
                {
                    Employee myEmp = entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
                    // update all the fields
                    myEmp.empid = value.empid;
                    myEmp.lastname = value.lastname;
                    myEmp.firstname = value.firstname;
                    myEmp.title = value.title;
                    myEmp.titleofcourtesy = value.titleofcourtesy;
                    myEmp.birthdate = value.birthdate;
                    myEmp.hiredate = value.hiredate;
                    myEmp.address = value.address;
                    myEmp.city = value.city;
                    myEmp.region = value.region;
                    myEmp.postalcode = value.postalcode;
                    myEmp.country = value.country;
                    myEmp.phone = value.phone;
                    myEmp.mgrid = value.mgrid;
                    // flush to disk
                    entities.SaveChanges();
                    return Request.CreateResponse(HttpStatusCode.OK);
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

6. DELETE api/employee/5

        // DELETE api/employee/5
        public HttpResponseMessage Delete(int id)
        {
            try
            {
                using (testContext entities = new testContext())
                {
                    Employee myEmp = entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
                    entities.Employees.Remove(myEmp);
                    entities.SaveChanges();
                    return Request.CreateResponse(HttpStatusCode.OK);
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

Here is the complete code for EmployeeController.cs.

Test the web service

Build the solution and run the web service. Point your localhost url to:
http://localhost:55142/api/employee/2

It will get the following JSON object from url for (empid=2):


{"empid":2,"lastname":"Funk","firstname":"Don","title":"Vice President, Sales","titleofcourtesy":"Dr.","birthdate":"1962-02-19T00:00:00","hiredate":"2002-08-14T00:00:00","address":"9012 W. Capital Way","city":"Tacoma","region":"WA","postalcode":"10001","country":"USA","phone":"(206) 555-0100","mgrid":1}

I also use a tool called ‘Fiddler Web Debugger’ to examine the JSON / XML response return from the web service.

In the next post, I will write a client application to consume all the URIs in the above web api services.

Generate model from database using Entity Framework Power Tool

The software I am using are:

  • Microsoft Visual Studion 2013 Premium
  • Microsoft SQL Server 2012 Enterprise
  • Entity Framework 6.0.1
  • Entity Framework Power Tools 1.0
  • Windows Server 2008 R2

Create Database Objects

I created a test database with a table HR.Employees as the following in my local Microsoft SQL 2012 instance (full T-SQL script is here):

CREATE TABLE HR.Employees
(
  empid           INT          NOT NULL IDENTITY,
  lastname        NVARCHAR(20) NOT NULL,
  firstname       NVARCHAR(10) NOT NULL,
  title           NVARCHAR(30) NOT NULL,
  titleofcourtesy NVARCHAR(25) NOT NULL,
  birthdate       DATETIME     NOT NULL,
  hiredate        DATETIME     NOT NULL,
  address         NVARCHAR(60) NOT NULL,
  city            NVARCHAR(15) NOT NULL,
  region          NVARCHAR(15) NULL,
  postalcode      NVARCHAR(10) NULL,
  country         NVARCHAR(15) NOT NULL,
  phone           NVARCHAR(24) NOT NULL,
  mgrid           INT          NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid)
    REFERENCES HR.Employees(empid),
  CONSTRAINT CHK_birthdate CHECK(birthdate <= CURRENT_TIMESTAMP)
);

Reversed Engineering DB Objects to Classes

I created a simple C# console project called ‘EmployeeConsole’ in visual studio, which will create two files: Program.cs and App.config. Right Click on the project in Solution Explorer, and choose ‘Entity Framework – Reversed Engineering Code First’. Then connect to the instance (local) and database (test), and generate the model from test database for table ‘HR.Employees’.

What Does Entity Framework Power Tool Do?

1. Visual Studio first will install Entity Framework (runtime) into the project by creating packages.config as the following:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.0.1" targetFramework="net45" />
</packages>

2. Create model: a database context (testContext.cs)

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using EmployeeConsole.Models.Mapping;

namespace EmployeeConsole.Models
{
    public partial class testContext : DbContext
    {
        static testContext()
        {
            Database.SetInitializer(null);
        }

        public testContext()
            : base("Name=testContext")
        {
        }

        public DbSet Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EmployeeMap());
        }
    }
}

3. Create model: a employees class in the model (Employee.cs)

using System;
using System.Collections.Generic;

namespace EmployeeConsole.Models
{
    public partial class Employee
    {
        public Employee()
        {
            this.Employees1 = new List();
        }

        public int empid { get; set; }
        public string lastname { get; set; }
        public string firstname { get; set; }
        public string title { get; set; }
        public string titleofcourtesy { get; set; }
        public System.DateTime birthdate { get; set; }
        public System.DateTime hiredate { get; set; }
        public string address { get; set; }
        public string city { get; set; }
        public string region { get; set; }
        public string postalcode { get; set; }
        public string country { get; set; }
        public string phone { get; set; }
        public Nullable mgrid { get; set; }
        public virtual ICollection Employees1 { get; set; }
        public virtual Employee Employee1 { get; set; }
    }
}

4. Create ORM: mapping POJO Employees class to physical table (EmployeeMap.cs) using Fluent API

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

namespace EmployeeConsole.Models.Mapping
{
    public class EmployeeMap : EntityTypeConfiguration
    {
        public EmployeeMap()
        {
            // Primary Key
            this.HasKey(t => t.empid);

            // Properties
            this.Property(t => t.lastname)
                .IsRequired()
                .HasMaxLength(20);

            this.Property(t => t.firstname)
                .IsRequired()
                .HasMaxLength(10);

            this.Property(t => t.title)
                .IsRequired()
                .HasMaxLength(30);

            this.Property(t => t.titleofcourtesy)
                .IsRequired()
                .HasMaxLength(25);

            this.Property(t => t.address)
                .IsRequired()
                .HasMaxLength(60);

            this.Property(t => t.city)
                .IsRequired()
                .HasMaxLength(15);

            this.Property(t => t.region)
                .HasMaxLength(15);

            this.Property(t => t.postalcode)
                .HasMaxLength(10);

            this.Property(t => t.country)
                .IsRequired()
                .HasMaxLength(15);

            this.Property(t => t.phone)
                .IsRequired()
                .HasMaxLength(24);

            // Table & Column Mappings
            this.ToTable("Employees", "HR");
            this.Property(t => t.empid).HasColumnName("empid");
            this.Property(t => t.lastname).HasColumnName("lastname");
            this.Property(t => t.firstname).HasColumnName("firstname");
            this.Property(t => t.title).HasColumnName("title");
            this.Property(t => t.titleofcourtesy).HasColumnName("titleofcourtesy");
            this.Property(t => t.birthdate).HasColumnName("birthdate");
            this.Property(t => t.hiredate).HasColumnName("hiredate");
            this.Property(t => t.address).HasColumnName("address");
            this.Property(t => t.city).HasColumnName("city");
            this.Property(t => t.region).HasColumnName("region");
            this.Property(t => t.postalcode).HasColumnName("postalcode");
            this.Property(t => t.country).HasColumnName("country");
            this.Property(t => t.phone).HasColumnName("phone");
            this.Property(t => t.mgrid).HasColumnName("mgrid");

            // Relationships
            this.HasOptional(t => t.Employee1)
                .WithMany(t => t.Employees1)
                .HasForeignKey(d => d.mgrid);

        }
    }
}

5. Update App.config to include entity framework config and SQL connection string.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="testContext" connectionString="Data Source=(local);Initial Catalog=test;Integrated Security=True;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Utilize your model

The following is a simple program to display a list of employee records by using EmployeeConsole.Models which was defined in testContext.cs.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EmployeeConsole.Models;

namespace EmployeeConsole
{
    class Program
    {
        static void Main(string[] args)
        {

            using (var db = new testContext())
            {
                // Get all the rows into var query by using LINQ if you want
                //var query = from el in db.Employees orderby el.firstname select el;

                // Get all the rows into var query by using SQL
                var query = db.Employees.SqlQuery("select * from hr.employees");

                // Display Listing Header
                Console.WriteLine("LIST OF EMPLOYEES WITH PHONES:");

                // just display firstnamelastnamephone
                foreach (var item in query)
                {
                    Console.WriteLine("{0}\t{1}\t{2}", item.firstname, item.lastname, item.phone);
                }

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }
        }
    }
}

Limitation on Processing Raw T-SQL queries

What if when the SQL query changed to the following:

            select * from employees
            select firstname,lastname,phone from hr.employees

Both are valid T-SQL queries, they won’t work from the model generated by Entity Framework because the model mapping has the following:

            this.ToTable("Employees", "HR");
            this.Property(t => t.empid).HasColumnName("empid");

The execution will complain either no schema object ’employees’ in the first case or requiring ’empid’ in the latter.

To use the model generated, it’s better to use either LINQ-TO-ENTITY or ENTITY-SQL. There are differences between Entity SQL and T-SQL.

Further Reading: ADO.NET Entity Framework & Entity SQL