StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWebAPI PUT call advice
text
Body
copied!<p>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</p> <pre><code>// 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; } } </code></pre> <p>This interface makes sure the proper methods are implmented</p> <pre><code>// 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); } </code></pre> <p>Next I have the actual repository</p> <pre><code>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; } } </code></pre> <p>And finally I have the SettingsController</p> <pre><code>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); } } </code></pre> <p>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.</p> <pre><code>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 </code></pre> <p>A simple execute statement would allow an insert: </p> <pre><code>exec [sp_GetAllSettings] "Salad", "Clamshell", 4, 5, 1, 1, 3.99, 2.75 </code></pre> <p>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 <code>PUT</code> call would be best.</p> <p>It seems like I would need a new stored procedure OR maybe I could use the same stored proc with default <code>NULL</code> values and if a value was passed then I would <code>INSERT</code> instead of <code>SELECT</code>.</p> <p>Any suggestions?</p> <p><strong>UPDATE</strong>: I have updated my code with the latest stored procedure. The part I am really missing is how to create a <code>PUT</code> 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:</p> <pre><code>mysite:123/api/settings/salad/clamshell/4/5/1/1/3.99/2.75 </code></pre> <p>However, I guess that isn't the correct way.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload