Introduction
ASP.NET Web API is a framework that makes it easy to build HTTP services that reach a broad range of clients, including browsers and native apps on mobile devices. It borrows from the simplicity of the HTTP protocol to establish simple rules for communication between applications over the web.
Why Web API?
The ASP.NET MVC framework was released in 2009 and implemented the model-view-controller (MVC) pattern. MVC allowed applications to be split up into three layers,
-
Model, comprised the business objects in a domain
-
View, comprised the components used to build the User Interface for applications within a domain
-
Controller, comprised the rest of the system. Its functions included controlling the interaction between the Model & View layers. It is also by default the container of business logic for the domain in this three layer framework
While the model and view layers had clearcut roles that defined their responsibilities and functions, controllers tended to be entrusted with serving as entry points into the application, managing the interaction of the other two layers, containing the business logic for the domain, handling persistance, security, audit and all other responsibilities.
ASP.NET Web API was an evolutionary development that streamlined communication rules for applications and application components by bringing them in line with the corresponding rules of the HTTP protocol. A natural opening resulting from this development offers a way to a more granular division of controller activities into reusable and independent business logic and repository layers that could be shared by multiple applications.
Architectural Concerns
Despite the introduction of MVC and it focus on separation of responsibilities, the controller layer continues to be quite unwieldy as it ends up being responsible for all functionality that falls outside the scope of the model and view layers. This article is an attempt at splitting out the controller layer in order to meaningfully reduce its complexity and thus improve the quality and maintainability of the resulting code.
Design Goals
Domain or business logic is one of the major components within an application. The primary justification to have this component within a separate layer is that this component may be shared by various applications. If entwined with code handling complementary functions like security, audit and persistance of data, it would be less likely to be shared. There is a good likelihood then that each application within a domain implements its own business rules layer. Duplication of code, conflicts in implementation and balloning resource requirements for development and maintenance are common side effects of such a scenario. Our first goal in this article is to design an independent business (Services) layer independent of the rest of the controller code.
The repository or persistance layer deals with permanent storage and retrieval of data. This is often combined by default within the controller code. This can result in a lot of repository code duplication as various controllers implement persistance code. Such tight coupling can also make a change in the persistance layer (changing database software or even upgrading to a new version) a more resource-intensive and error-prone operation. An additional goal of this article is to extricate the repository layer independent of the controller and business layer.
Requirements for getting started
You will need Visual Studio 2013 or later version that supports web development, SQL Server 2008 or better and the Northwind database (https://northwinddatabase.codeplex.com/). The final section on testing our Web API will require the use of Fiddler (https://www.telerik.com/download/fiddler).
I assume you have downloaded and installed these or equivalent components, so without further deliberation, let's get started.
Step 1: Create an ASP.NET Web API project
Open Visual Studio and click 'New Project' on the Start Page. In the New Project dialog, select ASP.NET Web Application, type 'NorthwindWebAPI' as the name of our application and click OK.
This will bring up the New ASP.NET Project dialog. Select the Web API template. This should auto-check the MVC & Web API check boxes. Change authentication to "No Authentication" and click OK to proceed.
After a few seconds, your application template generation should be complete and you should see the following setup in your Solution Explorer window. Note, components with line through them denote redundant elements (for this use case) and can be deleted.
Switch back to Visual Studio and press F5 to see what we get from this default application shell. I will skip an explanation of the components generated by this template. If you would like to explore details before moving forward, I would recommend this short 4 minute video by Jon Galloway, "ASP.NET Web API, Part 1: Your First Web API".
Stop the running application before continuing on to the next step.
2) Setup Model, Repository & Service layers
As mentioned earlier in the Design Goals section, our goal is to build separate Service and Repository layers such that they could be used by multiple applications. We do this by creating standalone projects to house each layer.
As shown in the diagram above, each layer references the layer immediately below it. The Models layer is a common dependency for all other layers. It is the medium of communication between layers. The Repositories layer is responsible for all database operations, none of the other layers should have any database component code. The Services layer encapsulates the business logic for the domain. The Controllers (in the MVC project) react to events initiated by the UI of the application. After ensuring the validity of the request and validating any required parameters, they pass the request to the appropriate Service for processing.
Thus when the OrderController receives a Get request, it places a call to the relevant method of the OrderService. The OrderService assembles the Order by making separate calls to the OrderRepository and OrderDetailRepository. If necessary it may also contact the CustomerRepository to retrieve the Customer for the Order and the EmployeeRepository for the Employee handling the order. It returns this Order object back to the OrderController which can use it to populate a view or respond to a web service request.
Step 2a: Create separate projects for each layer
Right-click on the solution name in Solution Explorer and select "Add - New Project" once again. Type "NorthwindWebAPI.Models" as the name of the project and click OK. From the New ASP.NET Project dialog select the Empty template and click OK.
Add two more projects using the Empty template. Name them "NorthwindWebAPI.Repositories" and "NorthwindWebAPI.Services". Your solution structure should look as shown in the screen snippet below.
Note: You can get rid of the "package.config" and "web.config" files within the three projects. From this point on I'll refer to them as Models, Repositories & Services projects.
Step 2b: Set StartUp Project and add references
Right-click on the NorthwindWebAPI project and select "Set as StartUp Project". Visual Studio may set the last added project as your startup project, this will correct that assumption.
Right-click the "References" node within the Repositories project and select "Add Reference". Select the "NorthwindWebAPI.Models" from the Solution as shown below and click OK.
Similarly, right-click the "References" node within the Services project and select "Add Reference". Select the "NorthwindWebAPI.Models" as well as "NorthwindWebAPI.Repositories" from the Solution and click OK.
Finally, right-click the "References" node within the NorthwindWebAPI project and select "Add Reference". Select the "NorthwindWebAPI.Models" as well as "NorthwindWebAPI.Services" from the Solution and click OK.
3) Creating Components for the Model layer
We now add code that defines the members and constructors for each model component. These are Customer, Employee, Order, Order_Detail and Product.
Step 3a: Create Data Models
Right-click the Models project and select "Add - Class", name the class "Customer". Replace the code in the file with the following,
using System;
using System.Data;
namespace NorthwindWebAPI.Models
{
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { 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 string Fax { get; set; }
public Customer() { }
public Customer(IDataReader reader)
{
this.CustomerID = reader["CustomerID"].ToString();
this.CompanyName = reader["CompanyName"].ToString();
this.ContactName = reader["ContactName"].ToString();
this.ContactTitle = reader["ContactTitle"].ToString();
this.Address = reader["Address"].ToString();
this.City = reader["City"].ToString();
this.Region = reader["Region"] == DBNull.Value ? string.Empty : reader["Region"].ToString();
this.PostalCode = reader["PostalCode"].ToString();
this.Country = reader["Country"].ToString();
this.Phone = reader["Phone"].ToString();
this.Fax = reader["Fax"] == DBNull.Value ? string.Empty : reader["Fax"].ToString();
}
}
}
Use the same step to create the "Employee" class and paste the following code over the default class code.
using System;
using System.Data;
namespace NorthwindWebAPI.Models
{
public class Employee
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
public string TitleOfCourtesy { get; set; }
public DateTime? BirthDate { get; set; }
public 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 HomePhone { get; set; }
public string Extension { get; set; }
public byte[] Photo { get; set; }
public string Notes { get; set; }
public string PhotoPath { get; set; }
public Employee() { }
public Employee(IDataReader reader)
{
this.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
this.TitleOfCourtesy = reader["TitleOfCourtesy"] as string;
this.FirstName = reader["FirstName"].ToString();
this.LastName = reader["LastName"].ToString();
this.Title = reader["Title"] as string;
this.BirthDate = reader["BirthDate"] as DateTime?;
this.HireDate = reader["HireDate"] as DateTime?;
this.Address = reader["Address"] as string;
this.City = reader["City"] as string;
this.Region = reader["Region"] as string;
this.PostalCode = reader["PostalCode"] as string;
this.Country = reader["Country"] as string;
this.HomePhone = reader["HomePhone"] as string;
this.Extension = reader["Extension"] as string;
this.Photo = reader["Photo"] as byte[];
this.Notes = reader["Notes"] as string;
this.PhotoPath = reader["PhotoPath"] as string;
}
}
}
Use the same step to create the "Order" class and paste the following code over the default class code.
using System;
using System.Collections.Generic;
using System.Data;
namespace NorthwindWebAPI.Models
{
public class Order
{
public int OrderID { get; set; }
public DateTime? OrderDate { get; set; }
public DateTime? RequiredDate { get; set; }
public DateTime? ShippedDate { get; set; }
public int? ShipVia { get; set; }
public decimal? Freight { get; set; }
public string ShipName { get; set; }
public string ShipAddress { get; set; }
public string ShipCity { get; set; }
public string ShipRegion { get; set; }
public string ShipPostalCode { get; set; }
public string ShipCountry { get; set; }
public Customer Customer { get; set; }
public Employee Employee { get; set; }
public ICollection<Order_Detail> Order_Details { get; set; }
public Order() { }
public Order(IDataReader reader)
{
this.OrderID = Convert.ToInt32(reader["OrderID"]);
this.OrderDate = reader["OrderDate"] as DateTime?;
this.RequiredDate = reader["RequiredDate"] as DateTime?;
this.ShippedDate = reader["ShippedDate"] as DateTime?;
this.ShipVia = reader["ShipVia"] as int?;
this.Freight = reader["Freight"] as decimal?;
this.ShipName = reader["ShipName"] as string;
this.ShipAddress = reader["ShipAddress"] as string;
this.ShipCity = reader["ShipCity"] as string;
this.ShipRegion = reader["ShipRegion"] as string;
this.ShipPostalCode = reader["ShipPostalCode"] as string;
this.ShipCountry = reader["ShipCountry"] as string;
this.Customer = new Customer();
this.Customer.CustomerID = reader["CustomerID"] as string;
this.Customer.CustomerID = this.Customer.CustomerID;
this.Customer.CompanyName = reader["CompanyName"] as string;
this.Customer.ContactName = reader["ContactName"] as string;
this.Employee = new Employee();
this.Employee.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
this.Employee.EmployeeID = this.Employee.EmployeeID;
this.Employee.FirstName = reader["FirstName"] as string;
this.Employee.LastName = reader["LastName"] as string;
Order_Details = new HashSet<Order_Detail>();
}
}
}
Use the same step to create the "Order_Detail" class and paste the following code over the default class code.
using System;
using System.Data;
namespace NorthwindWebAPI.Models
{
public class Order_Detail
{
public int OrderID { get; set; }
public int ProductID { get; set; }
public decimal UnitPrice { get; set; }
public short Quantity { get; set; }
public float Discount { get; set; }
public Order Order { get; set; }
public Product Product { get; set; }
public Order_Detail() { }
public Order_Detail(IDataReader reader)
{
this.OrderID = Convert.ToInt32(reader["OrderID"]);
this.ProductID = Convert.ToInt32(reader["ProductID"]);
this.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);
this.Quantity = Convert.ToInt16(reader["Quantity"]);
this.Discount = Convert.ToInt32(reader["Discount"]);
this.Product = new Product();
this.Product.ProductID = this.ProductID;
this.Product.ProductName = reader["ProductName"].ToString();
}
}
}
Use the same step to create the "Product" class and paste the following code over the default class code.
namespace NorthwindWebAPI.Models
{
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
}
}
Step 3b: Create Helper Class for Data Models
Use the same step to create the "ExtensionMethods" class and paste the following code over the default class code.
using System;
using System.Data.SqlClient;
namespace NorthwindWebAPI.Models
{
public static class ExtensionMethods
{
public static SqlParameter[] ToParameterArray(this Customer customer)
{
return new SqlParameter[] { new SqlParameter("@CustomerID", customer.CustomerID)
, new SqlParameter("@CompanyName", customer.CompanyName)
, new SqlParameter("@ContactName", customer.ContactName)
, new SqlParameter("@ContactTitle", customer.ContactTitle)
, new SqlParameter("@Address", customer.Address)
, new SqlParameter("@City", customer.City)
, new SqlParameter("@Region", customer.Region ?? (object)DBNull.Value)
, new SqlParameter("@PostalCode", customer.PostalCode)
, new SqlParameter("@Country", customer.Country)
, new SqlParameter("@Phone", customer.Phone)
, new SqlParameter("@Fax", customer.Fax ?? (object)DBNull.Value)
};
}
public static SqlParameter[] ToParameterArray(this Employee employee)
{
return new SqlParameter[]
{
new SqlParameter("@EmployeeID", employee.EmployeeID)
, new SqlParameter("@LastName", employee.LastName)
, new SqlParameter("@FirstName", employee.FirstName)
, new SqlParameter("@Title", employee.Title ?? (object)DBNull.Value)
, new SqlParameter("@TitleOfCourtesy", employee.TitleOfCourtesy ?? (object)DBNull.Value)
, new SqlParameter("@BirthDate", employee.BirthDate ?? (object)DBNull.Value)
, new SqlParameter("@HireDate", employee.HireDate ?? (object)DBNull.Value)
, new SqlParameter("@Address", employee.Address ?? (object)DBNull.Value)
, new SqlParameter("@City", employee.City ?? (object)DBNull.Value)
, new SqlParameter("@Region", employee.Region ?? (object)DBNull.Value)
, new SqlParameter("@PostalCode", employee.PostalCode ?? (object)DBNull.Value)
, new SqlParameter("@Country", employee.Country ?? (object)DBNull.Value)
, new SqlParameter("@HomePhone", employee.HomePhone ?? (object)DBNull.Value)
, new SqlParameter("@Extension", employee.Extension ?? (object)DBNull.Value)
, new SqlParameter("@Photo", employee.Photo ?? (object)DBNull.Value)
, new SqlParameter("@Notes", employee.Notes ?? (object)DBNull.Value)
, new SqlParameter("@PhotoPath", employee.PhotoPath ?? (object)DBNull.Value)
};
}
public static SqlParameter[] ToParameterArray(this Order order)
{
return new SqlParameter[]
{
new SqlParameter("@OrderID", order.OrderID)
, new SqlParameter("@CustomerID", order.Customer.CustomerID)
, new SqlParameter("@EmployeeID", order.Employee.EmployeeID)
, new SqlParameter("@OrderDate", order.OrderDate)
, new SqlParameter("@RequiredDate", order.RequiredDate ?? (object)DBNull.Value)
, new SqlParameter("@ShippedDate", order.ShippedDate ?? (object)DBNull.Value)
, new SqlParameter("@ShipVia", order.ShipVia ?? (object)DBNull.Value)
, new SqlParameter("@Freight", order.Freight ?? (object)DBNull.Value)
, new SqlParameter("@ShipName", order.ShipName ?? (object)DBNull.Value)
, new SqlParameter("@ShipAddress", order.ShipAddress ?? (object)DBNull.Value)
, new SqlParameter("@ShipCity", order.ShipCity ?? (object)DBNull.Value)
, new SqlParameter("@ShipRegion", order.ShipRegion ?? (object)DBNull.Value)
, new SqlParameter("@ShipPostalCode", order.ShipPostalCode ?? (object)DBNull.Value)
, new SqlParameter("@ShipCountry", order.ShipCountry ?? (object)DBNull.Value)
};
}
public static SqlParameter[] ToParameterArray(this Order_Detail orderDetail)
{
return new SqlParameter[]
{
new SqlParameter("@OrderID", orderDetail.OrderID)
, new SqlParameter("@ProductID", orderDetail.ProductID)
, new SqlParameter("@UnitPrice", orderDetail.UnitPrice)
, new SqlParameter("@Quantity", orderDetail.Quantity)
, new SqlParameter("@Discount", orderDetail.Discount)
};
}
}
}
Step 3c: Add Connection String
Open web.config from the NorthwindWebAPI project and insert the connectionStrings node between the already existing <configuration> & <appSettings> tags. Note, do not paste the <configuration> & <appSettings> tags, they are show below as guides to where the connectionStrings node is to be inserted.
Replace the string "SQLSERVERINSTANCE" with the actual name of your SQL Server Instance in the node. Make sure the value for "initial catalog" matches the name of your Northwind database.
<configuration>
<connectionStrings>
<add name="NorthwindDbConnection" providerName="System.Data.SqlClient"
connectionString="Data Source=SQLSERVERINSTANCE;initial catalog=Northwind;integrated security=true;"/>
</connectionStrings>
<appSettings>
This completes our Model layer. Lets do a sanity check by pressing "Ctrl-Shift-B" to compile & build the solution. You should have no errors or warnings.
4) Creating Repository Layer Components
We will be using SQL Server as the database and using simple TSQL statements to store and retrieve data. The popular alternative is to use Entity Framework to simplify the repository layer. My choice was influenced by personal preference alone. However should someone desire to switch, it would be simpler to switch by rewriting this repository layer than if we had code embedded within each controller class.
Step 4a: Create the Repository base class
Right-click the Repositories project and select "Add - Class", name the class "Repository". Replace the code in the file with the following,
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace NorthwindWebAPI.Repositories
{
public abstract class Repository<T> where T : class
{
public string ConnectionString { get; set; }
public abstract T PopulateRecord(IDataReader reader);
public Repository()
{
ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindDbConnection"].ConnectionString;
}
#region Generic Get methods
public virtual IEnumerable<T> Get(string sqlCommand)
{
List<T> tList = new List<T>();
using (IDataReader reader = ExecuteReader(sqlCommand, null, CommandType.Text))
{
while (reader.Read())
{
var entity = PopulateRecord(reader);
tList.Add(entity);
}
}
return tList;
}
public virtual IEnumerable<T> Get(string sqlCommand, SqlParameter[] parameters
, CommandType commandType = CommandType.Text)
{
List<T> entityList = null;
using (IDataReader reader = ExecuteReader(sqlCommand, parameters, commandType))
{
entityList = new List<T>();
while (reader.Read())
{
entityList.Add(PopulateRecord(reader));
}
}
return entityList.Count > 0 ? entityList : null;
}
#endregion
#region Database command execution methods
public virtual SqlConnection CreateConnection()
{
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
return sqlConnection;
}
protected virtual IDataReader ExecuteReader(string commandText, SqlParameter[] parameters = null
, CommandType commandType = CommandType.Text
, SqlConnection connection = null)
{
bool isNewConnection = false;
if (connection == null || connection.State == ConnectionState.Closed)
{
connection = CreateConnection();
isNewConnection = true;
}
try
{
SqlCommand command = new SqlCommand(commandText, connection);
command.CommandType = commandType;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
var commandBehavior = isNewConnection ? CommandBehavior.CloseConnection : CommandBehavior.Default;
return command.ExecuteReader(commandBehavior);
}
catch (Exception)
{
if (isNewConnection && connection.State == ConnectionState.Open)
{
connection.Close();
}
throw;
}
}
protected virtual int ExecuteNonQuery(string commandText, SqlParameter[] parameters = null
, CommandType commandType = CommandType.Text
, SqlConnection connection = null
, SqlTransaction transaction = null)
{
bool isLocalConnection = false;
try
{
SqlCommand sqlCommand = BuildSqlCommand(commandText, parameters, commandType, ref connection, transaction, ref isLocalConnection);
return sqlCommand.ExecuteNonQuery();
}
finally
{
if (isLocalConnection && connection.State != ConnectionState.Closed)
connection.Close();
}
}
protected virtual object ExecuteScalar(string commandText, SqlParameter[] parameters = null
, CommandType commandType = CommandType.Text
, SqlConnection connection = null
, SqlTransaction transaction = null)
{
bool isLocalConnection = false;
try
{
SqlCommand sqlCommand = BuildSqlCommand(commandText, parameters, commandType, ref connection, transaction
, ref isLocalConnection);
return sqlCommand.ExecuteScalar();
}
finally
{
if (isLocalConnection && connection.State != ConnectionState.Closed)
connection.Close();
}
}
private SqlCommand BuildSqlCommand(string commandText, SqlParameter[] parameters, CommandType commandType
, ref SqlConnection connection
, SqlTransaction transaction
, ref bool isLocalConnection)
{
SqlCommand sqlCommand = new SqlCommand(commandText);
sqlCommand.CommandType = commandType;
if (parameters != null)
{
sqlCommand.Parameters.AddRange(parameters);
}
if (connection == null || connection.State == ConnectionState.Closed)
{
connection = CreateConnection();
isLocalConnection = true;
}
sqlCommand.Connection = connection;
if (transaction != null)
sqlCommand.Transaction = transaction;
return sqlCommand;
}
#endregion
}
}
Step 4b: Create the CustomerRepository class
Right-click the Repositories project and select "Add - Class", name the class "CustomerRepository". Replace the code in the file with the following,
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NorthwindWebAPI.Models;
namespace NorthwindWebAPI.Repositories
{
public class CustomerRepository : Repository<Customer>
{
public override Customer PopulateRecord(IDataReader reader)
{
return new Customer(reader);
}
public IEnumerable<Customer> Get()
{
var sqlCommand = "SELECT CustomerID, CompanyName, ContactName, ContactTitle "
+ ", [Address], City, Region, PostalCode, Country, Phone, Fax "
+ " FROM dbo.Customers";
return base.Get(sqlCommand);
}
public IEnumerable<Customer> Get(string customerId)
{
var sqlCommand = "SELECT CustomerID, CompanyName, ContactName, ContactTitle "
+ ", [Address], City, Region, PostalCode, Country, Phone, Fax "
+ " FROM dbo.Customers WHERE CustomerID = @CustomerID";
var parameters = new SqlParameter[] { new SqlParameter("@CustomerID", customerId) };
return base.Get(sqlCommand, parameters);
}
public void Add(Customer customer)
{
var sqlCommand = "IF NOT EXISTS(SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)"
+ "INSERT INTO dbo.Customers "
+ " (CustomerID, CompanyName, ContactName, ContactTitle "
+ ", [Address], City, Region, PostalCode, Country, Phone, Fax) "
+ "VALUES(@CustomerID, @CompanyName, @ContactName, @ContactTitle "
+ ", @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax) ";
base.ExecuteNonQuery(sqlCommand, customer.ToParameterArray());
}
public void Update(Customer customer)
{
var sqlCommand = "UPDATE dbo.Customers "
+ " SET CompanyName = @CompanyName, ContactName = @ContactName "
+ " , ContactTitle = @ContactTitle, [Address] = @Address "
+ " , City = @City, Region = @Region, PostalCode = @PostalCode "
+ " , Country = @Country, Phone = @Phone, Fax = @Fax "
+ " WHERE CustomerID = @CustomerID ";
base.ExecuteNonQuery(sqlCommand, customer.ToParameterArray());
}
public void Delete(string customerId)
{
var sqlCommand = "DELETE FROM dbo.Customers WHERE CustomerID = @CustomerID ";
var parameters = new SqlParameter[] { new SqlParameter("@CustomerID", customerId) };
base.ExecuteNonQuery(sqlCommand, parameters);
}
}
}
Step 4c: Create the EmployeeRepository class
Right-click the Repositories project and select "Add - Class", name the class "EmployeeRepository". Replace the code in the file with the following,
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NorthwindWebAPI.Models;
namespace NorthwindWebAPI.Repositories
{
public class EmployeeRepository : Repository<Employee>
{
public override Employee PopulateRecord(IDataReader reader)
{
return new Employee(reader);
}
public IEnumerable<Employee> Get()
{
var sqlCommand = "SELECT EMPLOYEE.EmployeeID, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Title, EMPLOYEE.TitleOfCourtesy "
+ ", EMPLOYEE.BirthDate, EMPLOYEE.HireDate, EMPLOYEE.HomePhone, EMPLOYEE.Extension "
+ ", EMPLOYEE.[Address], EMPLOYEE.City, EMPLOYEE.Region, EMPLOYEE.PostalCode, EMPLOYEE.Country "
+ ", EMPLOYEE.Photo, EMPLOYEE.Notes, EMPLOYEE.PhotoPath "
+ ", EMPLOYEE.ReportsTo, MANAGER.FirstName + ' ' + MANAGER.LastName as Manager "
+ "FROM dbo.Employees EMPLOYEE "
+ " INNER JOIN dbo.Employees MANAGER ON EMPLOYEE.ReportsTo = MANAGER.EmployeeID";
return base.Get(sqlCommand);
}
public IEnumerable<Employee> Get(int employeeId)
{
var sqlCommand = "SELECT EMPLOYEE.EmployeeID, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Title, EMPLOYEE.TitleOfCourtesy "
+ ", EMPLOYEE.BirthDate, EMPLOYEE.HireDate, EMPLOYEE.HomePhone, EMPLOYEE.Extension "
+ ", EMPLOYEE.[Address], EMPLOYEE.City, EMPLOYEE.Region, EMPLOYEE.PostalCode, EMPLOYEE.Country "
+ ", EMPLOYEE.Photo, EMPLOYEE.Notes, EMPLOYEE.PhotoPath "
+ ", EMPLOYEE.ReportsTo, MANAGER.FirstName + ' ' + MANAGER.LastName as Manager "
+ "FROM dbo.Employees EMPLOYEE "
+ " INNER JOIN dbo.Employees MANAGER ON EMPLOYEE.ReportsTo = MANAGER.EmployeeID "
+ "WHERE EMPLOYEE.EmployeeID = @EmployeeId ";
var parameters = new SqlParameter[] { new SqlParameter("@EmployeeId", employeeId) };
return base.Get(sqlCommand, parameters);
}
public void Add(Employee employee)
{
var sqlCommand =
"IF NOT EXISTS(SELECT 1 FROM dbo.Employees WHERE FirstName = @FirstName AND LastName = @LastName "
+ " AND Title = @Title AND ReportsTo = @ReportsTo) "
+ " INSERT INTO dbo.Employees "
+ " (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, [Address], City "
+ " , Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath) "
+ " VALUES(@LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City "
+ " , @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo, @PhotoPath) ";
base.ExecuteNonQuery(sqlCommand, employee.ToParameterArray());
}
public void Update(Employee employee)
{
var sqlCommand =
"UPDATE dbo.Employees "
+ "SET LastName = @LastName, FirstName = @FirstName, Title = @Title, TitleOfCourtesy = @TitleOfCourtesy "
+ " , BirthDate = @BirthDate, HireDate = @HireDate, [Address]=@Address,City=@City, Region=@Region "
+ " , PostalCode=@PostalCode, Country=@Country, HomePhone=@HomePhone, Extension=@Extension "
+ " , Photo=@Photo, Notes=@Notes, ReportsTo=@ReportsTo, PhotoPath=@PhotoPath "
+ "WHERE EmployeeID = @EmployeeID ";
base.ExecuteNonQuery(sqlCommand, employee.ToParameterArray());
}
public void Delete(int employeeId)
{
var sqlCommand = "DELETE FROM dbo.Employees WHERE EmployeeID = @EmployeeId";
var parameters = new SqlParameter[] { new SqlParameter("employeeId", employeeId) };
base.ExecuteNonQuery(sqlCommand, parameters);
}
}
}
Step 4d: Create the OrderDetailRepository class
Right-click the Repositories project and select "Add - Class", name the class "OrderDetailRepository". Replace the code in the file with the following,
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NorthwindWebAPI.Models;
namespace NorthwindWebAPI.Repositories
{
public class OrderDetailRepository : Repository<Order_Detail>
{
public override Order_Detail PopulateRecord(IDataReader reader)
{
return new Order_Detail(reader);
}
#region GET methods
public IEnumerable<Order_Detail> GET()
{
throw new NotImplementedException("This web service does not allow retrieving items outside of an order");
}
public IEnumerable<Order_Detail> Get(int orderId)
{
var sqlCommand =
"SELECT OrderID, DETAILS.ProductID, DETAILS.UnitPrice, Quantity, Discount, PRODUCT.ProductName "
+ "FROM dbo.[Order Details] DETAILS "
+ " INNER JOIN dbo.Products PRODUCT ON DETAILS.ProductID = PRODUCT.ProductID "
+ "WHERE OrderID = @OrderID ";
var parameters = new SqlParameter[] { new SqlParameter("@OrderID", orderId) };
return base.Get(sqlCommand, parameters);
}
#endregion
#region ADD methods
public void Add(IEnumerable<Order_Detail> orderItems, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
foreach (Order_Detail item in orderItems)
Add(item, sqlConnection, sqlTransaction);
}
public void Add(Order_Detail orderItem, SqlConnection sqlConnection = null, SqlTransaction sqlTransaction = null)
{
var sqlCommand =
"IF NOT EXISTS(SELECT 1 FROM dbo.[Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID) "
+ "INSERT INTO dbo.[Order Details] "
+ " (OrderID, ProductID, UnitPrice, Quantity, Discount) "
+ "VALUES(@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount) ";
base.ExecuteNonQuery(sqlCommand, orderItem.ToParameterArray(), CommandType.Text, sqlConnection, sqlTransaction);
}
#endregion
#region UPDATE methods
public void Update(IEnumerable<Order_Detail> orderItems, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
foreach (var item in orderItems)
Update(item, sqlConnection, sqlTransaction);
}
public void Update(Order_Detail orderDetail, SqlConnection sqlConnection = null, SqlTransaction sqlTransaction = null)
{
var sqlCommand =
"UPDATE dbo.[Order Details] "
+ " SET UnitPrice = @UnitPrice, Quantity = @Quantity, Discount = @Discount "
+ "WHERE OrderID = @OrderID AND ProductID = @ProductID ";
base.ExecuteNonQuery(sqlCommand, orderDetail.ToParameterArray(), CommandType.Text, sqlConnection, sqlTransaction);
}
#endregion
#region DELETE methods
public void Delete(int orderId, SqlConnection sqlConnection = null, SqlTransaction sqlTransaction = null)
{
var sqlCommand = "DELETE FROM dbo.[Order Details] WHERE OrderID = @OrderID ";
var parameters = new SqlParameter[] { new SqlParameter("@OrderID", orderId) };
base.ExecuteNonQuery(sqlCommand, parameters, CommandType.Text, sqlConnection, sqlTransaction);
}
public void Delete(int orderId, int productId, SqlConnection sqlConnection = null, SqlTransaction sqlTransaction = null)
{
var sqlCommand = "DELETE FROM dbo.[Order Details] "
+ "WHERE OrderID = @OrderID AND ProductID = @ProductID ";
var parameters = new SqlParameter[] { new SqlParameter("@OrderID", orderId)
, new SqlParameter("@ProductID", productId) };
base.ExecuteNonQuery(sqlCommand, parameters, CommandType.Text, sqlConnection, sqlTransaction);
}
#endregion
}
}
Step 4e: Create the OrderRepository class
Right-click the Repositories project and select "Add - Class", name the class "OrderRepository". Replace the code in the file with the following,
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NorthwindWebAPI.Models;
namespace NorthwindWebAPI.Repositories
{
public class OrderRepository : Repository<Order>
{
private OrderDetailRepository _orderDetailRepository;
public OrderRepository()
{
_orderDetailRepository = new OrderDetailRepository();
}
public override Order PopulateRecord(IDataReader reader)
{
return new Order(reader);
}
public IEnumerable<Order> Get()
{
var sqlCommand =
"SELECT OrderID, ORDERS.CustomerID, ORDERS.EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia "
+ " , Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry "
+ " , CUSTOMERS.CompanyName, CUSTOMERS.ContactName, EMPLOYEES.FirstName, EMPLOYEES.LastName "
+ "FROM dbo.Orders ORDERS "
+ " INNER JOIN dbo.Customers CUSTOMERS ON ORDERS.CustomerID = CUSTOMERS.CustomerID "
+ " INNER JOIN dbo.Employees EMPLOYEES ON ORDERS.EmployeeID = EMPLOYEES.EmployeeID ";
return base.Get(sqlCommand);
}
public IEnumerable<Order> Get(int OrderId)
{
var sqlCommand =
"SELECT OrderID, ORDERS.CustomerID, ORDERS.EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia "
+ " , Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry "
+ " , CUSTOMERS.CompanyName, CUSTOMERS.ContactName, EMPLOYEES.FirstName, EMPLOYEES.LastName "
+ "FROM dbo.Orders ORDERS "
+ " INNER JOIN dbo.Customers CUSTOMERS ON ORDERS.CustomerID = CUSTOMERS.CustomerID "
+ " INNER JOIN dbo.Employees EMPLOYEES ON ORDERS.EmployeeID = EMPLOYEES.EmployeeID "
+ "WHERE OrderID = @OrderID ";
var parameters = new SqlParameter[] { new SqlParameter("@OrderID", OrderId) };
return base.Get(sqlCommand, parameters);
}
public void Add(Order order)
{
var sqlCommand =
"INSERT INTO dbo.Orders "
+ " (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight "
+ " , ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) "
+ " VALUES "
+ " (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight "
+ " , @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry); "
+ "SELECT SCOPE_IDENTITY() ";
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
var orderId = base.ExecuteScalar(sqlCommand, order.ToParameterArray()
, CommandType.Text, sqlConnection, sqlTransaction);
if (orderId == DBNull.Value)
throw new NullReferenceException("Order creation failed");
order.OrderID = Convert.ToInt32(orderId);
foreach (var item in order.Order_Details)
item.OrderID = order.OrderID;
_orderDetailRepository.Add(order.Order_Details, sqlConnection, sqlTransaction);
}
catch (Exception)
{
sqlTransaction.Rollback();
sqlConnection.Close();
throw;
}
sqlTransaction.Commit();
sqlConnection.Close();
}
public void Update(Order order)
{
var sqlCommand =
"UPDATE dbo.Orders "
+ " SET CustomerID = @CustomerID, EmployeeID = @EmployeeID, OrderDate = @OrderDate "
+ " ,RequiredDate = @RequiredDate, ShippedDate = @ShippedDate, ShipVia = @ShipVia "
+ " ,Freight = @Freight, ShipName = @ShipName, ShipAddress = @ShipAddress, ShipCity = @ShipCity "
+ " , ShipRegion = @ShipRegion, ShipPostalCode = @ShipPostalCode, ShipCountry = @ShipCountry "
+ "WHERE OrderID = @OrderID ";
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
base.ExecuteScalar(sqlCommand, order.ToParameterArray()
, CommandType.Text, sqlConnection, sqlTransaction);
_orderDetailRepository.Update(order.Order_Details, sqlConnection, sqlTransaction);
}
catch (Exception)
{
sqlTransaction.Rollback();
sqlConnection.Close();
throw;
}
sqlTransaction.Commit();
sqlConnection.Close();
}
public void Delete(int orderId)
{
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
_orderDetailRepository.Delete(orderId, sqlConnection, sqlTransaction);
var sqlCommand = "DELETE FROM dbo.Orders WHERE OrderID = @OrderID ";
var parameters = new SqlParameter[] { new SqlParameter("@OrderID", orderId) };
ExecuteNonQuery(sqlCommand, parameters, CommandType.Text, sqlConnection, sqlTransaction);
}
catch (Exception)
{
sqlTransaction.Rollback();
sqlConnection.Close();
throw;
}
sqlTransaction.Commit();
sqlConnection.Close();
}
}
}
This completes our Repository layer. Lets do a sanity check by pressing "Ctrl-Shift-B" to compile & build the solution. You should have no errors.
5) Creating Service Layer Components
The Service layer comprises of the CustomerService, EmployeeService and OrderService. The OrderService will coordinate repository calls to retrieve order details and hence we do not need a separate OrderDetailService.
Step 5a: Create the CustomerService class
Right-click the Services project and select "Add - Class", name the class "CustomerService". Replace the code in the file with the following,
using System.Collections.Generic;
using System.Linq;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Repositories;
namespace NorthwindWebAPI.Services
{
public class CustomerService
{
private CustomerRepository _customerRepository;
private static List<Customer> _customerList;
public CustomerService()
{
_customerRepository = new CustomerRepository();
}
public IEnumerable<Customer> Get()
{
if (_customerList == null)
{
_customerList = _customerRepository.Get().ToList();
}
return _customerList;
}
public IEnumerable<Customer> Get(string customerId)
{
if (_customerList != null)
return _customerList.Where(c => c.CustomerID == customerId);
else return _customerRepository.Get(customerId);
}
public void Add(Customer customer)
{
_customerRepository.Add(customer);
if (_customerList != null)
_customerList.Add(customer);
}
public void Update(Customer customer)
{
_customerRepository.Update(customer);
if (_customerList != null)
{
_customerList.Remove(_customerList.FirstOrDefault(c => c.CustomerID == customer.CustomerID));
_customerList.Add(customer);
}
}
public void Delete(string customerId)
{
_customerRepository.Delete(customerId);
if (_customerList != null)
{
_customerList.Remove(_customerList.Where(c => c.CustomerID == customerId).FirstOrDefault());
}
}
}
}
Step 5b: Create the EmployeeService class
Right-click the Services project and select "Add - Class", name the class "EmployeeService". Replace the code in the file with the following,
using System.Collections.Generic;
using System.Linq;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Repositories;
namespace NorthwindWebAPI.Services
{
public class EmployeeService
{
private EmployeeRepository _employeeRepository;
private static List<Employee> _employeeList;
private static bool _employeeListStale = true;
public EmployeeService()
{ _employeeRepository = new EmployeeRepository(); }
public IEnumerable<Employee> Get()
{
if (_employeeList == null || _employeeListStale)
{
_employeeList = _employeeRepository.Get().ToList();
}
return _employeeList;
}
public IEnumerable<Employee> Get(int employeeId)
{
if (_employeeList != null && _employeeListStale == false)
return _employeeList.Where(emp => emp.EmployeeID == employeeId);
else return _employeeRepository.Get(employeeId);
}
public void Add(Employee employee)
{
_employeeRepository.Add(employee);
_employeeListStale = true;
}
public void Update(Employee employee)
{
_employeeRepository.Update(employee);
_employeeListStale = true;
}
public void Delete(int employeeId)
{
_employeeRepository.Delete(employeeId);
_employeeListStale = true;
}
}
}
Step 5c: Create the OrderService class
Right-click the Services project and select "Add - Class", name the class "OrderService". Replace the code in the file with the following,
using System.Collections.Generic;
using System.Linq;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Repositories;
namespace NorthwindWebAPI.Services
{
public class OrderService
{
private OrderRepository _orderRepository;
private OrderDetailRepository _orderDetailRepository;
private CustomerRepository _customerRepository;
private EmployeeRepository _employeeRepository;
private static List<Order> _orderList; private static bool _orderListStale = true;
public OrderService()
{
_orderRepository = new OrderRepository();
_orderDetailRepository = new OrderDetailRepository();
_customerRepository = new CustomerRepository();
_employeeRepository = new EmployeeRepository();
}
public IEnumerable<Order> Get()
{
if (_orderList == null || _orderListStale)
{
_orderList = _orderRepository.Get().ToList();
_orderListStale = false;
}
return _orderList;
}
public IEnumerable<Order> Get(int orderId)
{
List<Order> orderList = null;
if (_orderList != null && !_orderListStale)
{
orderList = _orderList.Where(ord => ord.OrderID == orderId).ToList();
}
if (orderList == null)
{
orderList = _orderRepository.Get(orderId).ToList();
if (orderList == null)
return orderList;
}
foreach (var order in orderList)
order.Order_Details = _orderDetailRepository.Get(order.OrderID).ToList();
return orderList;
}
public void Add(Order order)
{
_orderRepository.Add(order);
}
public void Update(Order order)
{
_orderRepository.Update(order);
}
public void Delete(int orderId)
{
_orderRepository.Delete(orderId);
}
}
}
This completes our Service layer. Lets do a sanity check by pressing "Ctrl-Shift-B" to compile & build the solution. You should have no errors.
6) Creating Controllers for the Web API application
Now that we have completed the code for the three layers as laid out in our design goals, all that remains to code are the controllers for our Web API application. Our effort upto this point should serve to make our code for this stage cleaner and easier to manage than if we had it all living inside the controllers.
Step 6a: Create the CustomerController class
Right-click the Controller folder of the NorthwindWebAPI project and select "Add - Controller"....
Select the "Web API 2 Controller - Empty" and click Add. Enter 'CustomerController' for the Controller name in the next dialog and click Add again.
Replace the code in the file with the following,
using System;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Services;
namespace NorthwindWebAPI.Controllers
{
public class CustomerController : ApiController
{
private CustomerService _customerService;
public CustomerController()
{
_customerService = new CustomerService();
}
public HttpResponseMessage Get()
{
var customers = _customerService.Get();
if (customers != null)
return Request.CreateResponse(HttpStatusCode.OK, customers);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound
, "No customers found");
}
public HttpResponseMessage Get(string id)
{
var customer = _customerService.Get(id);
if (customer != null)
return Request.CreateResponse(HttpStatusCode.OK, customer);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound
, "Customer with Id " + id + " does not exist");
}
public HttpResponseMessage Post([FromBody]Customer customer)
{
_customerService.Add(customer);
var message = Request.CreateResponse(HttpStatusCode.Created);
message.Headers.Location = new Uri(Request.RequestUri + customer.CustomerID);
return message;
}
public HttpResponseMessage Put([FromBody]Customer customer)
{
_customerService.Update(customer);
return Request.CreateResponse(HttpStatusCode.OK, string.Empty);
}
public HttpResponseMessage Delete(string id)
{
_customerService.Delete(id);
return Request.CreateResponse(HttpStatusCode.OK, string.Empty);
}
}
}
Step 6b: Create the EmployeeController class
Add another controller following the steps outlined in Step 6a above, name it 'EmployeeController'. Replace the code in the file with the following,
using System.Net;
using System.Net.Http;
using System.Web.Http;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Services;
namespace NorthwindWebAPI.Controllers
{
public class EmployeeController : ApiController
{
private EmployeeService _employeeService;
public EmployeeController()
{
_employeeService = new EmployeeService();
}
public HttpResponseMessage Get()
{
var employees = _employeeService.Get();
if (employees != null)
return Request.CreateResponse(HttpStatusCode.OK, employees);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound, "No employees found");
}
public HttpResponseMessage Get(int id)
{
var employee = _employeeService.Get(id);
if (employee != null)
return Request.CreateResponse(HttpStatusCode.OK, employee);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound
, "Employee with Id " + id.ToString() + " does not exist");
}
public HttpResponseMessage Post([FromBody]Employee employee)
{
_employeeService.Add(employee);
return Request.CreateResponse(HttpStatusCode.Created); ;
}
public HttpResponseMessage Put(int id, [FromBody]Employee employee)
{
_employeeService.Update(employee);
return Request.CreateResponse(HttpStatusCode.OK);
}
public HttpResponseMessage Delete(int id)
{
_employeeService.Delete(id);
return Request.CreateResponse(HttpStatusCode.OK);
}
}
}
Step 6c: Create the OrderController class
Add another controller following the steps outlined in Step 6a above, name it 'OrderController'. Replace the code in the file with the following,
using System.Net;
using System.Net.Http;
using System.Web.Http;
using NorthwindWebAPI.Models;
using NorthwindWebAPI.Services;
namespace NorthwindWebAPI.Controllers
{
public class OrderController : ApiController
{
private OrderService _orderService;
public OrderController()
{
_orderService = new OrderService();
}
public HttpResponseMessage Get()
{
var orders = _orderService.Get();
if (orders != null)
return Request.CreateResponse(HttpStatusCode.OK, orders);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound, "No orders found");
}
public HttpResponseMessage Get(int id)
{
var order = _orderService.Get(id);
if (order != null)
return Request.CreateResponse(HttpStatusCode.OK, order);
else return Request.CreateErrorResponse(HttpStatusCode.NotFound,
"Order #" + id.ToString() + " does not exist");
}
public HttpResponseMessage Post([FromBody]Order order)
{
_orderService.Add(order);
return Request.CreateResponse(HttpStatusCode.Created, order);
}
public HttpResponseMessage Put([FromBody]Order order)
{
_orderService.Update(order);
return Request.CreateResponse(HttpStatusCode.OK);
}
public HttpResponseMessage Delete(int id)
{
_orderService.Delete(id);
return Request.CreateResponse(HttpStatusCode.OK);
}
}
}
This completes the coding for our application. Lets do a sanity check by pressing "Ctrl-Shift-B" to compile & build the solution. You should have no errors.
7) Web API application ready, let the testing begin
Lets do some simple tests to verify our application works. Press 'F5' to run the application. The application generated by the default project template provides a placeholder page. The address bar of the browser should display the base URL for our application in the form, http://localhost:58656/ where the numeric portion could vary on your machine.
Step 7a: Testing the GET Customer method
We start our test by appending a path to our base URL displayed in the address bar of the browser, the type of call and parameters passed determine the specific controller method that will be activated. For eg., the following call should get a list of all customers,
/api/customer
We can get a specific customer by appending the customerID to the previous call
/api/customer/ALFKI
The corresponding calls to retrieve employee and order information are listed below,
/api/employee
/api/employee/3
/api/order
/api/order/10248
The Web API calls to Add, Update or Delete methods requires the use of Fiddler. Open Fiddler and navigate to the "Composer" tab and select the "Parsed" subtab. Let us start by replicating one of the calls above, the one where we request a specific customer by their customerID
To get details about a specific customer, set the action verb in the first input dropdown to "GET". Enter the associated URL in the second input field, then click the Execute button.
The panel on the left (see above) should show a new entry with a value of 200 in the second column. Double-clicking this entry should display the Web API response in the main panel as shown below. Note, you can see the output in its raw format by activating the "Raw" subtab.
Step 7b: Testing the POST (Add) Customer method
Switch back to the Composer tab and change the action verb to POST and the URL to,
http://localhost:99999/api/customer ...replace 99999 with the number for your port
Add the following string in the Request Header (directly below URL, see screenshot below),
Content-Type: application/json
Copy the text below into the Request Body field as shown in the image below.
{"CustomerID":"MYCST","CompanyName":"Customer King","ContactName":"Maria Von Trap","ContactTitle":"Sales Representative","Address":"Obere Str. 57","City":"Berlin","Region":"","PostalCode":"12209","Country":"Germany","Phone":"030-0074321","Fax":"030-0076545"}
Click Execute to add a new row into the Northwind Db. A record matching the JSON data should be added to the Customer table in the Northwind Db.
You can double-click the latest entry in the left panel (highlighted in image below) to see the response to our POST request. Note, our application responded to the POST request with a status code of '201' indicating that the record was successfully created.
Step 7c: Testing the PUT (Update) Customer method
Switch back to the Composer tab and change the action verb to PUT and leave URL pointed to /api/customer. Confirm that you still have the 'Content-Type' declaration from the previous step in the Request Header.
Copy the text below into the Request Body field as shown in the image below.
{"CustomerID":"MYCST","CompanyName":"Customer isKing","ContactName":"Anna-marie Cox","ContactTitle":"Sales Associate","Address":"Dungarten Rd.","City":"Bismarck","Region":"","PostalCode":"24098","Country":"Austria","Phone":"030-0074321","Fax":"030-0076545"}
Click Execute to update the previously added row from the Request Body into the Northwind Db. The record matching the CustomerID should be updated in the Customer table of the Northwind Db. Note, our application responded to the PUT request with a status code of '200' indicating that the record was successfully updated.
Step 7d: Testing the DELETE Customer method
Switch back to the Composer tab and change the action verb to DELETE and the URL to,
http://localhost:99999/api/customer/MYCST ...replace 99999 with the number for your port
The Request Body should be empty as the Delete method only needs the CustomerID as a parameter and we are supplying that in the URL above.
Click Execute to delete the previously added row from the Northwind Db. The record matching the CustomerID should be deleted. Note, our application responded to the PUT request with a status code of '200' indicating that the record was successfully deleted.
Execute a Web API call to the Customer GET method with the deleted customerID as the parameter to confirm the record was successfully deleted. The URL remains the same as the one used for the DELETE operation above since we want to use the same customerID as our parameter. The action verb needs to be changed to GET.
The Response Body returned should be empty unlike when we last executed a GET for a specific customer.
We can similarly test the Employee and Order controllers but this is a reasonable stopping point in what is already a long article. If there are enhancements that are of particular interest, feel free to mention them in the comments and I'll try to do a followup that addresses common requests. Thank you for your time and attention.
History
- 24 Nov 2016 - Initial Draft
