-1

I'd like to save s JSON file as a field in a SQL database, for debugging purposes. What field type should I use? I am deserializing the JSON fine and saving the fields OK, but I'd like to save the original JSON file too.

public class WixOrder
{
    [Key]
    public Guid OrderId { get; set; }

    public string Json { get; set; }

    [MaxLength(50)]
    public string OrderNumber { get; set; }
    [MaxLength(50)]
    public string RestaurantId { get; set; }
    [MaxLength(50)]
    public string Locale { get; set; }
    [MaxLength(50)]
    public string OrderDate { get; set; }
    [MaxLength(4000)]
    public string Comment { get; set; }
    public int ? Price { get; set; }
    [MaxLength(50)]
    public string Currency { get; set; }
    public List<WixOrderItem> OrderItems { get; set; }
    public WixDelivery Delivery { get; set; }
    public WixContact Contact { get; set; }
    public WixAddress Address { get; set; }
    public WixPayment Payment { get; set; }
    public long ? Created { get; set; }
    public long ? Modified { get; set; }
    public int ? ItemsCount { get; set; }
    [MaxLength(10)]
    public string TotalPrice { get; set; }
    public int SaveCount { get; set; }
    public long OrderProcessMilliseconds { get; set; }
    public long OrderSaveMilliseconds { get; set; }
    public DateTime ? DateReceived { get; set; }
    public DateTime ? DateLastPrinted { get; set; }
}

The public string Json { get; set; } is an nvarchar(MAX) field but it blank if the JSON file is over 8000 characters. The files I'd like to store are sometimes over 400KB.

Paul Clark
  • 43
  • 5
  • 1
    `but it blank if the JSON file is over 8000 characters` - then you are not storing it properly. – GSerg Mar 11 '21 at 11:52
  • Do you want to store the JSON _data_ that comes from the file, or the actual _file_? – ADyson Mar 11 '21 at 11:55
  • `in a SQL database`...what sort of SQL database? Oracle? SQL Server? mySQL? Etc. Some/all of these have specific columns types for JSON, in their more recent versions. Do a bit of research. – ADyson Mar 11 '21 at 11:56
  • 2
    In SQL Server, data type `nvarchar(MAX)` can hold 2Gb – Nick.Mc Mar 11 '21 at 11:59
  • I'm using a SQL Server Express 2019 database. I'm storing the JSON data OK, but I would like to store the original JSON file for debugging. I I trim the JSON string to 8000 characters, the string is stored OK, but a blank string is stored if I don't trim it – Paul Clark Mar 11 '21 at 12:00
  • 1
    Like I noted before, it means the code you are using to store the string is wrong. Given the lack of information you have provided, I'm willing to assume https://stackoverflow.com/q/21087950/11683. – GSerg Mar 11 '21 at 12:06
  • Thanks @GSerg. I'm using Code First and Linq to store the large string. – Paul Clark Mar 11 '21 at 12:15
  • 1
    Please show that then. – GSerg Mar 11 '21 at 12:15
  • Why not use `NVARCHAR(MAX)` and just store the whole thing? – Nick.Mc Mar 12 '21 at 03:01

1 Answers1

-1

I decided to store the JSON file on the server. It's not elegant but I just need a copy of the JSON file for debugging.

string json = null;

        using (StreamReader reader = new StreamReader(Request.InputStream))
        {
            json = await reader.ReadToEndAsync();
        }

        //store JSON for debugging
        string fileName = "wix-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".json";
        string tempFileName = Path.Combine(@"C:\Temp\Wix", fileName);
        using (StreamWriter outputFile = new StreamWriter(tempFileName, true))
        {
            await outputFile.WriteAsync(json);
        }

        Root Wix;
        try
        {
            Wix = JsonConvert.DeserializeObject<Root>(json);
        }
        catch (System.Exception ex)
        {
            string message = ex.Message;
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }

        if (!ValidWixJson(Wix)) return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

        await GetOrderAsync(Wix, json);

        return new HttpStatusCodeResult(HttpStatusCode.OK);

The JSON files are stores on C:\Temp\Wix. N.B. I needed to set directory security to allow the website process to save the files

Paul Clark
  • 43
  • 5