3

I'm storing JSON in a SQL Server field as varchar(max), but the .net web-api is returning backslashes "\", which I believe are causing the ajax requests to treat this field as a string rather than an object, like this:

{
    "name": "datamodule",
    "props": "{\"type\":\"object\",\"properties\":{\"label\":{\"type\":\"string\",\"title\":\"Label\"},\"desc\":{\"type\":\"string\",\"title\":\"Description\"}}}",
    "id": 1
}

As you can see, the web-api is returning JSON, but the string field that is storing JSON is showing up as an escaped string. At first, I thought that I'd have to serialize/deserialize in the EF model like this post recommends. But then I ran into this post, which seems to be exactly the problem I'm having, but don't understand either the accepted or top answer. I also tried replacing the backslash (as others suggest) in the model get/set, but that didn't work, which makes me think they're inserted later on.

Does anyone have a recommended approach? I'm using SQL Server 2012 so can't use the supposed SQL Server 2016 JSON data type.

Here's the ajax call (as you can see I'm forced to parse the props attribute):

    $.ajax({
        'type': 'GET',
        'dataType': 'json',
        'url': '../api/Dataschemas/'+m.id
    }).done(function (schema) {
        if(typeof schema.props === 'string') {
            schema.props = JSON.parse(schema.props);
        }
        self.createEditor(schema);
    });

Here's the auto-generated .NET controller:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.Description;
using Metadata.Models;

namespace Metadata.Controllers
{
    public class DataschemasController : ApiController
    {
        private flex_biEntities db = new flex_biEntities();

        // GET: api/Dataschemas
        public IQueryable<Dataschema> GetDataschemas()
        {
            return db.Dataschemas;
        }

        // GET: api/Dataschemas/5
        [ResponseType(typeof(Dataschema))]
        public async Task<IHttpActionResult> GetDataschema(int id)
        {
            Dataschema dataschema = await db.Dataschemas.FindAsync(id);
            if (dataschema == null)
            {
                return NotFound();
            }

            return Ok(dataschema);
        }

        // PUT: api/Dataschemas/5
        [ResponseType(typeof(void))]
        public async Task<IHttpActionResult> PutDataschema(int id, Dataschema dataschema)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (id != dataschema.id)
            {
                return BadRequest();
            }

            db.Entry(dataschema).State = EntityState.Modified;

            try
            {
                await db.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!DataschemaExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return StatusCode(HttpStatusCode.NoContent);
        }

        // POST: api/Dataschemas
        [ResponseType(typeof(Dataschema))]
        public async Task<IHttpActionResult> PostDataschema(Dataschema dataschema)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            db.Dataschemas.Add(dataschema);
            await db.SaveChangesAsync();

            return CreatedAtRoute("DefaultApi", new { id = dataschema.id }, dataschema);
        }

        // DELETE: api/Dataschemas/5
        [ResponseType(typeof(Dataschema))]
        public async Task<IHttpActionResult> DeleteDataschema(int id)
        {
            Dataschema dataschema = await db.Dataschemas.FindAsync(id);
            if (dataschema == null)
            {
                return NotFound();
            }

            db.Dataschemas.Remove(dataschema);
            await db.SaveChangesAsync();

            return Ok(dataschema);
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }

        private bool DataschemaExists(int id)
        {
            return db.Dataschemas.Count(e => e.id == id) > 0;
        }
    }
}

Here the auto-generated model:

namespace Metadata.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Dataschema
    {
        public string name { get; set; }
        public string desc { get; set; }
        public string props { get; set; }
        public System.DateTime created { get; set; }
        public System.DateTime modified { get; set; }
        public int id { get; set; }
    }

}
Community
  • 1
  • 1
Andrew
  • 1,532
  • 13
  • 24
  • can you show the ajax call and the API controller please. Are you sure it's .NET that is adding the slashes and not something in the SQL? – ADyson Dec 21 '16 at 22:49
  • I've added the ajax call and controller. I'm not sure where the slashes are being added, or even if that's the problem. Generally, storing JSON in a SQL Server field and accessing it with .net-web-api seems to be a major issue among the various forums, with a number of people just switching to mongodb or postgre. – Andrew Dec 21 '16 at 23:04

0 Answers0