WebAPI PUT call advice

Question

So I am using the .NET 4 WebAPI sample and I have most everything working properly BUT I am not sure how I can have a PUT back to the database with value that would be stored in the underlying table. I have a stored procedure that pull all data from the table. I have a class called Setting.cs, this is basically an object representation of the underlying table. This

// Model - Settings
public class Setting
{
    public int ID { get; set; }
    public string Category { get; set; }
    public string Sub_Category { get; set; }
    public int UnitSize { get; set; }
    public int UnitOfMeasureID { get; set; }
    public bool Facings { get; set; }
    public bool Quantity { get; set; }
    public double EverydayPrice { get; set; }
    public double PromotionPrice { get; set; }
}

This interface makes sure the proper methods are implmented

// We need to store a collection of settings.  It's a good idea to separate the collection from our service implementation.
// This allows the backing store to be changed without rewriting the service class.  This is a design pattern called REPOSITORY.
public interface ISettingRepository
{
    IEnumerable<Setting> GetAll();
    Setting Get(int id);
    Setting Add(Setting item);
    void Remove(int id);
    bool Update(Setting item);
}

Next I have the actual repository

public class SettingRepository : ISettingRepository
{
    private List<Setting> settings = new List<Setting>();
    private int _nextId = 1;

    public SettingRepository()
    {
        SqlConnection conn = new SqlConnection(Security.Security.Decrypt(ConfigurationManager.ConnectionStrings["myDatabase"].ConnectionString, "passwordString"));
        SqlCommand cmd = new SqlCommand("sp_GetAllSettings", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param_category = cmd.Parameters.Add("CATEGORY", SqlDbType.VarChar);
        param_category.Value = "Salad";
        param_category.Direction = ParameterDirection.Input;

        SqlParameter param_sub_catgegory = cmd.Parameters.Add("SUB_CATEGORY", SqlDbType.VarChar);
        param_sub_catgegory.Value = "Clamshell";
        param_sub_catgegory.Direction = ParameterDirection.Input;

        SqlParameter param_unit_size = cmd.Parameters.Add("UNIT_SIZE", SqlDbType.Int);
        param_unit_size.Value = 5;
        param_unit_size.Direction = ParameterDirection.Input;

        SqlParameter param_unit_of_measure_id = cmd.Parameters.Add("UNIT_OF_MEASURE_ID", SqlDbType.Int);
        param_unit_of_measure_id.Value = 6;
        param_unit_of_measure_id.Direction = ParameterDirection.Input;

        SqlParameter param_facings_bool = cmd.Parameters.Add("FACINGS", SqlDbType.Bit);
        param_facings_bool.Value = true;
        param_facings_bool.Direction = ParameterDirection.Input;

        SqlParameter param_quantity_bool = cmd.Parameters.Add("QUANTITY", SqlDbType.Bit);
        param_quantity_bool.Value = true;
        param_quantity_bool.Direction = ParameterDirection.Input;

        SqlParameter param_everyday_price = cmd.Parameters.Add("EVERYDAY_PRICE", SqlDbType.Money);
        param_everyday_price.Value = 9.99;
        param_everyday_price.Direction = ParameterDirection.Input;

        SqlParameter param_promotion_price = cmd.Parameters.Add("PROMOTION_PRICE", SqlDbType.Money);
        param_promotion_price.Value = 8.95;
        param_promotion_price.Direction = ParameterDirection.Input;

        try
        {
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            settings = Base.DataReaderMapToList<Setting>(dr);
            dr.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            ex.Message.ToString();
        }
    }
    public IEnumerable<Setting> GetAll()
    {
        return settings;
    }

    public Setting Get(int id)
    {
        return settings.Find(p => p.ID == id);
    }

    public Setting Add(Setting item)
    {
        item.ID = _nextId++;
        settings.Add(item);
        return item;
    }

    public void Remove(int id)
    {
        settings.RemoveAll(p => p.ID == id);
    }

    public bool Update(Setting item)
    {
        int index = settings.FindIndex(p => p.ID == item.ID);

        if (index == -1)
        {
            return false;
        }

        settings.RemoveAt(index);
        settings.Add(item);
        return true;
    }
}

And finally I have the SettingsController

public class SettingsController : ApiController
{
    static readonly ISettingRepository repository = new SettingRepository();

    // GET /api/values
    /// <summary>
    /// Returns the entire list of settings as an IEnumerable<Setting> type.
    /// </summary>
    /// <returns></returns>
    public IEnumerable<Setting> GetAllSettings()
    {
        return repository.GetAll();
    }

    // GET /api/values/5
    /// <summary>
    /// Looks up a single setting by ID
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Setting GetSettingById(int id)
    {
        Setting item = repository.Get(id);

        if (item == null)
        {
            throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
        }

        return item;
    }

    /// <summary>
    /// Returns all settings with a specified category.
    /// </summary>
    /// <param name="category"></param>
    /// <returns></returns>
    public IEnumerable<Setting> GetSettingsByCategory(string category)
    {
        return repository.GetAll().Where(p => string.Equals(p.Category, category, StringComparison.OrdinalIgnoreCase));
    }

    /// <summary>
    /// Returns all settings with a specified category and subcategory.
    /// </summary>
    /// <param name="category"></param>
    /// <param name="subCategory"></param>
    /// <returns></returns>
    public IEnumerable<Setting> GetDefaultPriceSettingsByCategoryAndSubCategory(string category, string subCategory)
    {
        return repository.GetAll().Where(p => string.Equals(p.Category, category, StringComparison.OrdinalIgnoreCase)).Where(p => string.Equals(p.Sub_Category, subCategory, StringComparison.OrdinalIgnoreCase));
    }

    // TODO: Refactor
    // NOTE: By default, parameters with complex types are deserialized from the request body.
    // Therefore, we expect the client to send us a serialized representation of a product object,
    // using either XML or JSON for the serialization.
    // RESPONSE CODE: By default, the Web API framework sets the response status code to 200 (OK).
    // Per the HTTP/1.1 protocol, when a POST request results in the creation of a resource,
    // the server should reply with status 201 (Created).
    // LOCATION: When the server creates a resource, it should include the URI of the new resource in the Location
    // header of the response.
    // By returning an HttpResponseMessage instead of a Product, we can control the details of the HTTP response message
    // including the status code and the Location header.
    public HttpResponseMessage PostSetting(Setting item)
    {
        item = repository.Add(item);
        //var response = Request.CreateResponse(HttpStatusCode.Created);
        var response = new HttpResponseMessage<Setting>(item) { StatusCode = HttpStatusCode.Created };

        string uri = Url.Route("DefaultApi", new { id = item.ID });
        response.Headers.Location = new Uri(uri);
        return response;
    }

    // PUT /api/values/5
    // This method name starts with a "Put....", so Web API matches it to PUT requests.
    // The contact parameter is deserialized from the request body.  By default, the ASP.NET Web API framework
    // takes simple parameter types from the route and complex types from the request body.
    public void PutSetting(int id, Setting contract)
    {
        contract.ID = id;

        if (!repository.Update(contract))
        {
            throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
        }
    }

    // DELETE /api/values/5
    // According to the HTTP specification, the DELETE method must be idempotent,
    // meaning that several DELETE requests to the same URI must have the same effect as a single DELETE request.
    // Therefore, the method should not return an error code if the contact was already deleted.
    //
    // If a DELETE request succeeds, it can return status 200 (OK) with an entity-body that describes the status,
    // or status 202 (Accepted) if the deletion is still pending, or status 204 (No Content) with no entity body.
    // In this example, the method returns status 204.
    public HttpResponseMessage DeleteSetting(int id)
    {
        repository.Remove(id);
        return new HttpResponseMessage(HttpStatusCode.NoContent);
    }
}

Here is my stored procedure that allows for a row to be inserted if all values are included otherwise it returns a select. The ID column is an an auto-increment identity value.

ALTER PROCEDURE [dbo].[sp_GetAllSettings]
@CATEGORY AS VARCHAR(10) = NULL,
@SUB_CATEGORY AS VARCHAR(10) = NULL,
@UNIT_SIZE AS INTEGER = NULL,
@UNIT_OF_MEASURE_ID AS INTEGER = NULL,
@FACINGS AS BIT = NULL,
@QUANTITY AS BIT = NULL,
@EVERYDAY_PRICE AS MONEY = NULL,
@PROMOTION_PRICE AS MONEY = NULL
AS

BEGIN
SET NOCOUNT ON;

IF NOT @CATEGORY IS NULL
    AND NOT @SUB_CATEGORY IS NULL
    AND NOT @UNIT_SIZE IS NULL
    AND NOT @UNIT_OF_MEASURE_ID IS NULL
    AND NOT @FACINGS IS NULL
    AND NOT @QUANTITY IS NULL
    AND NOT @EVERYDAY_PRICE IS NULL
    AND NOT @PROMOTION_PRICE IS NULL

    INSERT INTO Settings(Category,
                         Sub_Category,
                         UnitSize,
                         UnitOfMeasureID,
                         Facings,
                         Quantity,
                         EverydayPrice,
                         PromotionPrice)

    VALUES (@CATEGORY,
            @SUB_CATEGORY,
            @UNIT_SIZE,
            @UNIT_OF_MEASURE_ID,
            @FACINGS,
            @QUANTITY,
            @EVERYDAY_PRICE,
            @PROMOTION_PRICE)
ELSE
    SELECT [ID],
            Category,
            Sub_Category,
            UnitSize,
            UnitOfMeasureID,
            Facings,
            Quantity,
            EverydayPrice,
            PromotionPrice

    FROM Settings
END

A simple execute statement would allow an insert:

exec [sp_GetAllSettings] "Salad", "Clamshell", 4, 5, 1, 1, 3.99, 2.75

What I am trying to figure out is: When using this approach, what is the best way to allow a new setting to be stored in the database? I assume a PUT call would be best.

It seems like I would need a new stored procedure OR maybe I could use the same stored proc with default NULL values and if a value was passed then I would INSERT instead of SELECT.

Any suggestions?

UPDATE: I have updated my code with the latest stored procedure. The part I am really missing is how to create a PUT call in the URL so I can send basically what you see in the stored procedure execute statement from above. I was hoping for something like:

mysite:123/api/settings/salad/clamshell/4/5/1/1/3.99/2.75

However, I guess that isn't the correct way.

Answer 1

Your API has the necessary code to Insert the new item into the database via the repository here:

public HttpResponseMessage PostSetting(Setting item)
{
    item = repository.Add(item);
    //var response = Request.CreateResponse(HttpStatusCode.Created);
    var response = new HttpResponseMessage<Setting>(item) { StatusCode = HttpStatusCode.Created };

    string uri = Url.Route("DefaultApi", new { id = item.ID });
    response.Headers.Location = new Uri(uri);
    return response;
}

I think that your question is, how to test this? And what is the URL?

You need to create a JSON representation of the Setting class or item that you are wanting to test with and use a product like Fiddler (now a Telerik product) and use the Composer tab.

Next you will want to perform a POST to the following URL:

http://[your base url]/api/settings

and pass the JSON formatted setting class.

You can see an example of this here: ASP.NET Web API - Scott Hanselman