RESTful webservice +JSON+SQL 存储过程项目的问题 [英] Issue with RESTful webservice +JSON+SQL stored procedure project

问题描述

我知道我想念一些东西.我的整个项目有点复制和粘贴各种如何..."，我的 C# 知识充其量只是基本的，我需要让它工作，因为我们的标准 Web 服务软件仅在发送时才是 RESTful.

I know for a fact that there is something I miss. My whole project is somewhat copy&paste of various "how to..."s, my knowledge of C# is basic at best and I need to have it working since our standard web service software is RESTful only when sending.

我的主要问题是我偶然发现的所有解决方案实际上都是代码片段，它们对我不起作用 - 我的 C# 知识是基本的，所以我不了解它是如何工作的，更不用说对其进行故障排除了.我很确定我什至没有捕获随传入请求一起发布的 JSON.但 OTOH 我可能错了.

My main problem is that all solutions I've stumbled upon are actually code snippets, which don't work for me - my C# knowledge is basic so I don't understand how it all works, much less troubleshoot it. I'm pretty sure I'm not even capturing the JSON that's being posted with incoming request. But OTOH I may be wrong.

要求:在 WS2012R2 上的 IIS 上工作的东西，可以通过 HTTPPost 接受 JSON 文件，将内容转储到 SQL Server 表中，并将刚刚创建的行的 Id 返回给 JSON 的发送者.我将不得不在它的基础上构建完整的 Web 服务，该服务发送和接收包含不同数据的多个 JSON 文件，所有这些文件都必须在 SQL Server 中结束.

Requirement: something that works off IIS on WS2012R2, can accept JSON files via HTTPPost, dump content into a SQL Server table and return Id for the row that just was created to the sender of the JSON. I will have to build on it to get full-blown web service that sends and receives multiple JSON files containing different data, all have to end up in SQL Server.

我有什么:

类:

    namespace NA.Models
{
    public class Note
    {
        public Note() { }

        //public Guid id { get; set; }
        public static string Client { get; set; }
        public static int Case { get; set; }
        public static string Text { get; set; }
        public static int NoteId { get; set; }
        public static string R1 { get; set; }
        public static string R2 { get; set; }
        public static string S1 { get; set; }
        public static DateTime Date { get; set; }
        public static bool Type { get; set; }
      }
    }

界面:

    namespace NA.Models
{
    interface INoteRepository
    {
        IEnumerable<Note> GetAll();
        void Add(Note item);
    }
}

存储库:

namespace NA.Models
{
  class NoteDataRepository : INoteRepository
  {
     public void Add(Note item)
     {
        if (item == null)
        {
            throw new ArgumentNullException("item");
        }
        else
        {
            String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "BL_IntegrationInsertNote";
            cmd.Parameters.Add("@Client", SqlDbType.VarChar).Value = item.Client.Trim();
            cmd.Parameters.Add("@Case", SqlDbType.VarChar).Value = item.case;
            cmd.Parameters.Add("@Text", SqlDbType.VarChar).Value = item.Text.Trim();
            cmd.Parameters.Add("@When", SqlDbType.DateTime).Value = item.Date;
            cmd.Parameters.Add("@Ext", SqlDbType.Bit).Value = item.Type;
            cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Connection = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                string id = cmd.Parameters["@return"].Value.ToString();
                string lblMessage = null;
                lblMessage = "Record inserted successfully. ID = " + id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }    
        return item;
    }

    IEnumerable<Note> INoteRepository.GetAll()
    {
        throw new NotImplementedException("getitems");
    }
}
}

控制器:

namespace NA.Controllers
{
   public class NC : ApiController
   {
      [Route("AddNote")]
      [HttpPost]
    public HttpResponseMessage PostNote(List<Note> item)
    {
        //NoteJson deserializednote = JsonConvert.DeserializeObject<NoteJson>(item);
        //Note notesdata = new Note(item);
        //foreach (deserializednote   
        NotesAccept.Models.INoteRepository Repository = new NotesAccept.Models.NoteDataRepository();
        item = Repository.Add(item);
        var response = Request.CreateResponse < NotesAccept.Models.Note>(HttpStatusCode.Created, item);

        return response;
    }
   }
}

尝试将测试 json 发送到服务时，我收到一个错误作为回报:

When trying to send test json to the service I get an error in return:

500:内部服务器错误，参数项的值不能为空

500: Internal server error, Value cannot be null at Parameter item

这是 posttestserver.com 发送请求的转储:

This is posttestserver.com dump of request sent:

Headers (Some may be inserted by server)
REQUEST_URI = /post.php
QUERY_STRING = 
REQUEST_METHOD = POST
GATEWAY_INTERFACE = CGI/1.1
REMOTE_PORT = 56926
REMOTE_ADDR = ip
HTTP_CONNECTION = close
HTTP_CACHE_CONTROL = max-age=259200
HTTP_X_FORWARDED_FOR = 172.16.3.87
HTTP_VIA = 1.1 koenig.local (squid/3.3.13)
HTTP_EXPECT = 100-continue
CONTENT_LENGTH = 153
HTTP_HOST = posttestserver.com
HTTP_ACCEPT = application/json
CONTENT_TYPE = application/json
UNIQUE_ID = Vri0cUBaMGUAABvGeesAAAAL
REQUEST_TIME_FLOAT = 1454945393.4611
REQUEST_TIME = 1454945393

No Post Params.

== Begin post body ==
[{
    "Client": "Client1",
    "Case": 1,
    "Text": "Text",
    "NoteId": 2,
    "R1": "R1",
    "R2": "R2",
    "S1": "S1",
    "Date": "2015-10-26T09:06:46",
    "Type":"1"
}]
== End post body ==

Upload contains PUT data:
[{
    "Client": "Client1",
    "Case": 1,
    "Text": "Text",
    "NoteId": 2,
    "R1": "R1",
    "R2": "R2",
    "S1": "S1",
    "Date": "2015-10-26T09:06:46",
    "Type":"1"
}]

以上转储来自 POST 请求，与我发送到我的 Web 服务的请求相同，但 URL 除外.所以可以作为实际请求处理.这是一个 IIS 日志:

The above dump is from POST request which is identical to one I'm sending to my web service, with exception of URL. So can be treated as actual request. And here is an IIS log:

字段:日期时间 s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatussc-win32-status 耗时 2016-02-08 15:49:52 ::1 POST/blz/AddNote -80 - ::1 - - 500 0 0 2937

Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken 2016-02-08 15:49:52 ::1 POST /blz/AddNote - 80 - ::1 - - 500 0 0 2937

推荐答案

好的，您需要更改以下几项才能使其正常工作:

OK, so there are several things that you need to change to make it working:

1. 向 Note 添加无参数构造函数，因为反序列化需要它:

1. Add parameterless constructor to Note as it will be needed for deserialization:

public Note()
{
}

摆脱注释字段中的静态":

Get rid of "static" in Note's fields:

public static string Client { get;放;}

public static string Client { get; set; }

public static int Case { get;放;}

public static int Case { get; set; }

public static string Text { get;放;}

public static string Text { get; set; }

public static int NoteId { get;放;}

public static int NoteId { get; set; }

public static string R1 { get;放;}

public static string R1 { get; set; }

public static string R2 { get;放;}

public static string R2 { get; set; }

public static string S1 { get;放;}

public static string S1 { get; set; }

public static DateTime Date { get;放;}

public static DateTime Date { get; set; }

public static bool Type { get;放;}

public static bool Type { get; set; }

如果您只需要 1 个对象，请不要发送 JSON 数组，它不会反序列化.您期望的是单个对象，而不是数组，所以不要发送数组.

Don't send JSON array if you want just 1 object, it won't deserialize. You are expecting single object, not array, so don't send array.

您将 Type 设为 bool，但您发送的是字符串1"，这不会像您预期的那样反序列化为真正的值.发送真/假(不是真"/假")或将 Type 的类型更改为字符串.

You have Type as bool, but you are sending string "1", this will not deserialize to true value as you might expected. Either send true/false (not "true"/"false") or change type of Type to string.

去掉那个私有项目字段，你不需要它:

Get rid of that private item field, you don't need it:

私人笔记项目；

摆脱那些你拥有的构造函数

Get rid of those constructors that you have there

公开说明(字符串json)

公共笔记(笔记项目)

不仅它们没有意义且不起作用，而且您不需要它们，因为 JSON 反序列化器将为您填充字段.

Not only that they make no sense and won't work, you don't need them as JSON deserializer will fill the fields for you.

例如，您说它不会构建，因为不再有带有一个参数的构造函数.当然不建，有这条线

For example, you say it does not build because there is no more a constructor with one parameter. Of course it does not build, there is this line

Note notesdata = new Note(item);

但你不需要那条线.这条线背后的想法是什么?你想要一个 Note 类的实例，但你已经在item"变量中拥有它.您不需要创建它的第二个副本.所以也摆脱这个.

but you do not need that line. What is the idea behind this line? You want an instance of Note class, but you already have it in "item" variable. You do not need to create a second copy of that. So get rid of this too.

它无法编译的另一个原因是你去掉了那些静态字段，而你的 Add 方法中仍然有这个:

Another reason, why it won't compile is that you get rid of those static fields, while you still have this in your Add method:

        cmd.Parameters.Add("@Text", SqlDbType.VarChar).Value = Note.Text.Trim();
        cmd.Parameters.Add("@When", SqlDbType.DateTime).Value = Note.Date;

而且我很确定您不希望那样.相反，您想使用发送给您的对象实例:

and I am quite sure you do not want that. Instead, you want to use the instance of the object that were sent to you:

        cmd.Parameters.Add("@Text", SqlDbType.VarChar).Value = item.Text.Trim();
        cmd.Parameters.Add("@When", SqlDbType.DateTime).Value = item.Date;

另一件事是，Add 方法通常没有理由返回被添加到数据库的对象.所以请随意更改此

Another thing is that there is usually no reason why would Add method return the object being added to DB. So feel free to change this

   public Note Add(Note item)

到这里

   public void Add(Note item)

不要返回任何东西，你不需要它.

and do not return anything, you do not need it.

我不是 SqlConnection 及其周围事物的专家，所以我不评论这部分.我在我的项目中使用 EF 来处理 DB.所以那部分可能有一些问题，但我不能对此发表评论.

I am no expert on SqlConnection and things around it, so that part I do not comment. I use EF in my projects for working with DB. So there might be some problems in that part, but I can't comment on that.

这篇关于RESTful webservice +JSON+SQL 存储过程项目的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！