Issue with RESTful webservice +JSON+SQL stored pro

Question

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.

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.

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.

What I have:

Class:

    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; }
      }
    }

Interface:

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

Repository:

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");
    }
}
}

Controller:

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;
    }
   }
}

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

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

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"
}]

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:

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

Answer 1

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

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

   public Note()
   {
   }
   

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

   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; }

3. 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.

4. 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.

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

   private Note item;

6. Get rid of those constructors that you have there

   public Note(string json)

   public Note(Note item)

   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.

EDIT: 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);

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.

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;

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)

to this

   public void Add(Note item)

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

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.