Contentful Database Synchronization Controller

WebhookController.cs

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Web.Http;

// Note: for this to work, you need to insert this line in the Application_Start event of your global.asax
//    GlobalConfiguration.Configuration.Formatters.JsonFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue("application/vnd.contentful.management.v1+json"));
// This tells the model binder that the Contentful webhook request carries a JSON payload in the body

namespace Website.Controllers
{
    public class WebhookController : ApiController
    {
        private const string TOPIC_HEADER_NAME = "X-Contentful-Topic";
        private const string PUBLISHED_TOPIC_HEADER_VALUE = "ContentManagement.Entry.publish";
        private const string INSERTED_RESPONSE = "Inserted/Refreshed";
        private const string DELETED_RESPONSE = "Deleted";

        private string connectionString = ConfigurationManager.ConnectionStrings["blog"].ToString();

        [HttpPost]
        public string Index([FromBody] JObject data)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // Always execute a delete
                // We don't care if the record actually exists or not
                var deleteCommand = new SqlCommand("DELETE FROM posts WHERE id = @id", conn);
                deleteCommand.Parameters.AddWithValue("id", (string)data.SelectToken("sys.id"));
                deleteCommand.ExecuteNonQuery();

                // The "X-Contentful-Topic" tells us what Contentful was doing.
                if (Request.Headers.GetValues(TOPIC_HEADER_NAME).First() != PUBLISHED_TOPIC_HEADER_VALUE)
                {
                    // We weren't publishing, so we're done
                    return DELETED_RESPONSE;
                }
                else
                {
                    // We're publishing, so insert the entry
                    // This is either a brand new record or it will replace a record we just deleted
                    var insertCommand = new SqlCommand("INSERT INTO posts (id, title, body, published) VALUES (@id, @title, @body, @published)", conn);
                    insertCommand.Parameters.AddWithValue("id", (string)data.SelectToken("sys.id"));
                    insertCommand.Parameters.AddWithValue("title", (string)data.SelectToken("fields.title.en-US"));
                    insertCommand.Parameters.AddWithValue("body", (string)data.SelectToken("fields.body.en-US"));
                    insertCommand.Parameters.AddWithValue("published", (DateTime)data.SelectToken("fields.date.en-US"));
                    insertCommand.ExecuteNonQuery();

                    return INSERTED_RESPONSE;
                }
            }
        }
    }
}