的WebAPI PUT来电咨询 [英] WebAPI PUT call advice
问题描述
所以，我现在用的是.NET 4的WebAPI的样品，我有最一切工作正常，但我不知道我怎么能有放回数据库，将存储在基础表中的值。我有拉的所有数据表中的存储过程。我有一类称为Setting.cs，这基本上是一个对象重新基础表的presentation。这
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; }
}
此接口可确保正确的方法是implmented
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;
}
}
最后，我有SettingsController
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);
}
}
下面就是如果所有值都包含否则返回选择插入我的存储过程，允许一行。 ID列是一个自动递增的标识值。
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
我试图弄清楚的是：使用此方法时，是什么让一个新的设置将存储在数据库中的最佳方法是什么？我认为一个 PUT 通话将是最好的。
好像我需要一个新的存储过程或者，也许我可以使用相同的存储过程使用默认 NULL 值，如果传递的值，然后我就插入而不是 SELECT 。
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.
有什么建议？
更新：我已经更新了我的code与最新的存储过程。部分我真的很缺少的是如何创建一个 PUT 在URL中调用，所以我基本上可以给你在存储过程中看到从上面执行语句。我希望这样的事情：
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.
推荐答案
您的API有必要code通过此信息库将新的项目到数据库：
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?
您需要创建一个你想要来测试和使用的产品，如的提琴手（现在 的Telerik产品），并使用Composer的标签。
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.
接下来，您将要执行POST到以下网址：
Next you will want to perform a POST to the following URL:
HTTP：// [你的基础URL] / API /设置
http://[your base url]/api/settings
和通过JSON格式设置类。
and pass the JSON formatted setting class.
您可以看到这个在这里的一个例子：的ASP.NET Web API - 在Scott Hanselman
You can see an example of this here: ASP.NET Web API - Scott Hanselman
这篇关于的WebAPI PUT来电咨询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
