C#, Azure: Create REST API Server on-premises or in Azure App Service


This example explains how to create REST API server. It could be deployed on-premises or in Azure (cloud).

Technology used:

  • Visual Studio 2019 (C# Windows Web App)
  • SQL Server 2019 (on premises)
  • Azure

Authentication: Basic (base64 encoded Username:Password)

Requests:

HTTPSQL
GETSELECT
POSTINSERT
PUTUPDATE
DELETEDELETE

Database

Create SQL database in Azure or on-premises SQL Server:

USE [master];
GO

IF (DB_ID(N'REST_Server') IS NOT NULL)
	BEGIN
		ALTER DATABASE REST_Server SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE REST_Server;
	END
GO

CREATE DATABASE REST_Server;
GO

USE REST_Server;
GO

CREATE TABLE dbo.Person
(
	PersonID INT IDENTITY(1, 1) PRIMARY KEY
	, FirstName VARCHAR(32)
	, LastName VARCHAR(32)
	, PayRate DECIMAL(19, 4)
	, StartDate DATETIME
	, EndDate DATETIME
	, DateCreated DATETIME DEFAULT (GETDATE())
	, DateLastModified DATETIME
);

INSERT dbo.Person (FirstName, LastName, PayRate, StartDate, EndDate, DateCreated)
SELECT	'John', 'Smith', 1.2351, '1966-03-07', '2087-01-13', GETDATE()
UNION ALL SELECT 'George', 'Benson', 432.5218, '1987-11-23', '2003-04-07', GETDATE()
UNION ALL SELECT	'Anna', 'Larson', 2.4127, '1954-03-17', '1966-07-15', GETDATE()
UNION ALL SELECT	'Bilbo', 'Beggins', 77.4537, '1968-11-14', '1970-03-08', GETDATE()
UNION ALL SELECT	'Stephanie', 'Garcia', -34.5123, '2073-03-17', '2086-12-21', GETDATE()
UNION ALL SELECT	'Gregory', 'Jackson', 413.1424, '2005-08-05', '2009-07-03', GETDATE();

SELECT *
FROM dbo.Person;

Visual Studio 2019

Create new C#, Windows, WebASP.NET Web Application (.NET Framework) project:

Project Name: REST_Server

Choose Empty and Web API:

Add model Person

using System;

namespace REST_Server.Models
{
    /// <summary>
    /// The structure of Person
    /// </summary>
    public class Person
    {
        public long PersonID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public double PayRate { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    }
}

Add class PersonPersistance:

using REST_Server.Models;
using System;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;

namespace REST_Server
{
    public class PersonPersistence
    {
        readonly SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["REST_Server"].ConnectionString);

        /// <summary>
        /// Get all the persons from database
        /// </summary>
        /// <returns></returns>
        public ArrayList GetPersons()
        {
            try
            {
                ArrayList personList = new ArrayList();

                string qs = "SELECT PersonID, FirstName, LastName, PayRate, StartDate, EndDate "
                    + "FROM dbo.Person; ";
                SqlCommand cmd = new SqlCommand(qs, cn);

                cn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())
                {
                    Person p = new Person
                    {
                        PersonID = Convert.ToInt64(sdr["PersonID"]),
                        FirstName = sdr["FirstName"].ToString(),
                        LastName = sdr["LastName"].ToString(),
                        PayRate = Convert.ToDouble(sdr["PayRate"]),
                        StartDate = Convert.ToDateTime(sdr["StartDate"]),
                        EndDate = Convert.ToDateTime(sdr["EndDate"])
                    };

                    personList.Add(p);
                }

                return personList;
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }

        /// <summary>
        /// Get Person by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Person GetPerson(long id)
        {
            try
            {
                Person p = new Person();

                string qs = "SELECT PersonID, FirstName, LastName, PayRate, StartDate, EndDate "
                    + "FROM dbo.Person "
                    + "WHERE PersonID = @ID; ";
                SqlCommand cmd = new SqlCommand(qs, cn);
                cmd.Parameters.AddWithValue("ID", id);

                cn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                if (sdr.Read())
                {
                    p.PersonID = Convert.ToInt64(sdr["PersonID"]);
                    p.FirstName = sdr["FirstName"].ToString();
                    p.LastName = sdr["LastName"].ToString();
                    p.PayRate = Convert.ToDouble(sdr["PayRate"]);
                    p.StartDate = Convert.ToDateTime(sdr["StartDate"]);
                    p.EndDate = Convert.ToDateTime(sdr["EndDate"]);
                }
                else { p = null; }

                return p;
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }

        /// <summary>
        /// Get person by FirstName, LastName
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <returns></returns>
        public Person GetPerson(string firstName, string lastName)
        {
            try
            {
                Person p = new Person();

                string qs = "SELECT PersonID, FirstName, LastName, PayRate, StartDate, EndDate "
                    + "FROM dbo.Person "
                    + "WHERE "
                    + "  FirstName = @FirstName "
                    + "  AND LastName = @LastName; ";
                SqlCommand cmd = new SqlCommand(qs, cn);
                cmd.Parameters.AddWithValue("FirstName", firstName);
                cmd.Parameters.AddWithValue("LastName", lastName);

                cn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                if (sdr.Read())
                {
                    p.PersonID = Convert.ToInt64(sdr["PersonID"]);
                    p.FirstName = sdr["FirstName"].ToString();
                    p.LastName = sdr["LastName"].ToString();
                    p.PayRate = Convert.ToDouble(sdr["PayRate"]);
                    p.StartDate = Convert.ToDateTime(sdr["StartDate"]);
                    p.EndDate = Convert.ToDateTime(sdr["EndDate"]);
                }
                else { p = null; }

                return p;
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }

        /// <summary>
        /// Insert new person in the database
        /// </summary>
        /// <param name="personToInsert"></param>
        /// <returns></returns>
        public long InsertPerson(Person personToInsert)
        {
            try
            {
                string qs = "INSERT dbo.Person (FirstName, LastName, PayRate, StartDate, EndDate) "
                    + "OUTPUT INSERTED.PersonID "
                    + "VALUES ('"
                    + personToInsert.FirstName + "','"
                    + personToInsert.LastName + "',"
                    + personToInsert.PayRate + ",'"
                    + personToInsert.StartDate + "','"
                    + personToInsert.EndDate + "');";

                SqlCommand cmd = new SqlCommand(qs, cn);

                cn.Open();
                object result = cmd.ExecuteScalar();

                long id = 0;
                if (result != null) { id = Convert.ToInt64(result); }

                return id;
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }

        /// <summary>
        /// Update existing person
        /// </summary>
        /// <param name="id"></param>
        /// <param name="personToUpdate"></param>
        /// <returns></returns>
        public bool UpdatePerson(long id, Person personToUpdate)
        {
            try
            {
                string qs = "SELECT * "
                    + "FROM dbo.Person "
                    + "WHERE PersonID = @ID; ";
                SqlCommand cmd = new SqlCommand(qs, cn);
                cmd.Parameters.AddWithValue("ID", id);

                cn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                if (sdr.Read())
                {
                    sdr.Close();

                    qs = "UPDATE dbo.Person "
                        + "SET"
                        + "  FirstName = '" + personToUpdate.FirstName + "'"
                        + "  , LastName = '" + personToUpdate.LastName + "'"
                        + "  , PayRate = " + personToUpdate.PayRate
                        + "  , StartDate = '" + personToUpdate.StartDate + "'"
                        + "  , EndDate = '" + personToUpdate.EndDate + "'"
                        + "  , DateLastModified = '" + DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'" +
                        "WHERE PersonID = @ID; ";

                    cmd = new SqlCommand(qs, cn);
                    cmd.Parameters.AddWithValue("ID", id);
                    cmd.ExecuteNonQuery();

                    return true;
                }
                else
                { return false; }
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }

        /// <summary>
        /// Delete existing person
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeletePerson(long id)
        {
            try
            {
                string qs = "SELECT 1 "
                    + "FROM dbo.Person "
                    + "WHERE PersonID = @ID; ";
                SqlCommand cmd = new SqlCommand(qs, cn);
                cmd.Parameters.AddWithValue("ID", id);

                cn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                if (sdr.Read())
                {
                    sdr.Close();

                    qs = "DELETE dbo.Person "
                        + "WHERE PersonID = @ID; ";

                    cmd = new SqlCommand(qs, cn);
                    cmd.Parameters.AddWithValue("ID", id);
                    cmd.ExecuteNonQuery();

                    return true;
                }
                else
                { return false; }
            }
            catch (SqlException ex) { throw ex; }
            finally { cn.Close(); }
        }
    }
}

Add configuration file for the database connections DBConnection.config:

<?xml version="1.0"?>
<connectionStrings>
  <add name="REST_Server"
       connectionString="Server=.\MyInstanceName;Database=REST_Server;User ID=my_username;Password=my_password;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Add DBConection.config in Web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings></appSettings>
  <connectionStrings configSource="DBConnection.config" />
  <system.web>
    <compilation debug="true" targetFramework="4.7.2" />
    <httpRuntime targetFramework="4.7.2" />
  </system.web>
...

Add class AuthenticationFilter:

using System;
using System.Text;
using System.Web.Http.Controllers;

namespace REST_Server
{
    public class AuthenticationFilter : System.Web.Http.Filters.ActionFilterAttribute
    {

        // Basic Authentication (Username:Password)
        // Also in Controllers\PersonController.cs

        public override void OnActionExecuting(HttpActionContext actionContext)
        {
            if (actionContext.Request.Headers.Authorization == null)
            { actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized); }
            else
            {
                string authenticationToken = actionContext.Request.Headers.Authorization.Parameter;
                string decodedToken = Encoding.UTF8.GetString(Convert.FromBase64String(authenticationToken));
                string userName = decodedToken.Substring(0, decodedToken.IndexOf(":"));
                string userPassword = decodedToken.Substring(decodedToken.IndexOf(":") + 1);

                if (userName == "Jack" && userPassword == "Jones")
                { /* authorized */ }
                else
                { actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized); }
            }
        }
    }
}

Add Controler PersonController:

Choose Web API 2 Controller with read/write actions:

using REST_Server.Models;
using System;
using System.Collections;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace REST_Server.Controllers
{
    // Basic Authentication (Username:Password)
    [AuthenticationFilter] // Also in AuthenticationFilter.cs

    public class PersonController : ApiController
    {
        /// <summary>
        /// Get all persons
        /// </summary>
        /// <returns></returns>

        // GET: api/Person (SELECT)
        public ArrayList Get()
        {
            PersonPersistence pp = new PersonPersistence();
            ArrayList persons = pp.GetPersons();
            return persons;
        }

        /// <summary>
        /// Get a specific person by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>

        // GET: api/Person/5 (SELECT... WHERE)
        public Person Get(long id)
        {
            PersonPersistence pp = new PersonPersistence();
            Person person = pp.GetPerson(id);
            if (person == null) { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound)); }
            return person;
        }

        /// <summary>
        /// Get a specific person by name
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <returns></returns>

        // GET: api/Person?firstName=John&lastName=Smith (SELECT... WHERE)
        public Person Get(string firstName, string lastName)
        {
            PersonPersistence pp = new PersonPersistence();
            Person person = pp.GetPerson(firstName, lastName);
            if (person == null) { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound)); }
            return person;
        }

        /// <summary>
        /// Insert new person
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>

        // POST: api/Person (INSERT)
        public HttpResponseMessage Post([FromBody] Person value)
        {
            PersonPersistence pp = new PersonPersistence();
            long id = pp.InsertPerson(value);
            value.PersonID = id;
            HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created);
            response.Headers.Location = new Uri(Request.RequestUri, string.Format("person/{0}", id));
            return response;
        }

        /// <summary>
        /// Update existing person by ID
        /// </summary>
        /// <param name="id"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        // PUT: api/Person/5 (UPDATE)
        public HttpResponseMessage Put(long id, [FromBody] Person value)
        {
            PersonPersistence pp = new PersonPersistence();
            bool recordExosted = false;
            recordExosted = pp.UpdatePerson(id, value);
            HttpResponseMessage response;
            if (recordExosted)
            { response = Request.CreateResponse(HttpStatusCode.NoContent); }
            else
            { response = Request.CreateResponse(HttpStatusCode.NotFound); }
            return response;
        }

        /// <summary>
        /// Delete existing person by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>

        // DELETE: api/Person/5 (DELETE)
        public HttpResponseMessage Delete(long id)
        {
            PersonPersistence pp = new PersonPersistence();
            bool recordExosted = false;
            recordExosted = pp.DeletePerson(id);
            HttpResponseMessage response;
            if (recordExosted)
            { response = Request.CreateResponse(HttpStatusCode.NoContent); }
            else
            { response = Request.CreateResponse(HttpStatusCode.NotFound); }
            return response;
        }
    }
}

Postman

Postman is a collaboration platform for API development. Postman’s features simplify each step of building an API and streamline collaboration so you can create better APIs—faster.

The status of the request is 401 Unauthorized. We need to pass the username and the password, as defined in AuthenticationFilter.cs:

Get all the persons from database:

Get Person by ID:

Get person by FirstName, LastName:

Insert new person in the database:

Update existing person:

Delete existing person:

Azure

Deploy Visual Studio project in Azure:

Copy the database connection string from the SQL database:

Go to App Service à Configuration à New connection string:

Paste the connection string:

Test in Postman: