asp.net core web api reading BLOB

Question

We have the following table:

With the following c# model

 public record PSSEGMENTPICTURE
    {
        public int PSID { get; init; }
        public int PSEDISEGMENTID { get; init; }
        public byte[] PSIMAGE { get; init; }
    }

Executing the following query I get the following results

  public JsonResult GetSegmentPictures()
        {
            return _sql.ExecuteQuery(@"SELECT * FROM PSSEGMENTPICTURES");
        }

The response

[
  {
    "PSID": 1,
    "PSEDISEGMENTID": 1,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:48:59"
  },
  {
    "PSID": 2,
    "PSEDISEGMENTID": 2,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:00"
  },
  {
    "PSID": 3,
    "PSEDISEGMENTID": 3,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:01"
  },
  {
    "PSID": 4,
    "PSEDISEGMENTID": 4,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:02"
  },
  {
    "PSID": 5,
    "PSEDISEGMENTID": 5,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:02"
  },
  {
    "PSID": 6,
    "PSEDISEGMENTID": 6,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:03"
  },
  {
    "PSID": 7,
    "PSEDISEGMENTID": 7,
    "PSIMAGE": null,
    "created_at": "2021-10-29T12:49:05"
  }]

What am I doing wrong? Ofcourse all the entries have blob values in the PSIMAGE column. Looking on the web I found that the type corresponding to blob is byte[] but this should not be the issue in this case since i'm returning the query result straight to json without mapping it to the model.

have a look at https://stackoverflow.com/questions/9337255/serialize-deserialize-a-byte-array-in-json-net , I think a byte-Array is not serialized automatically — Michael Rall, Oct 29 '21 at 11:45

score 2 · Answer 1 · answered Oct 29 '21 at 11:44

2

JSON is a text based format. It cannot contain raw byte arrays. The return type of your query should not be JSON if you want to retrieve the byte arrays.

A solution could be to return a collection of instances of your model class.

As an aside, when a byte array needs to be included in a JSON file, it needs first to be transformed into a string representation, using for instance base64 strings See Put byte array to JSON and vice versa But in your case I don't think it should be something to try to achieve, since I don't know if you have any way to enforce this when the JSON is created. I merely mention it for information.

answered Oct 29 '21 at 11:44

Laurent Gabiot

1,251
9
15

Thanks for your time Laurent, I can clearly understand what you are saying, do you believe its worth converting the blob to a base64 string and then create the response? How would you go about this? – SecurityObscurity Oct 29 '21 at 11:46
What do you use to access your database? Entity framework? something else? If you use Entity Framework, simply return a collection of your model. Note that your model cannot be a record, it must be a class, since EF engine will track objects changes, it cannot track immutable objects such as records. – Laurent Gabiot Oct 29 '21 at 11:50
I'm using the mysql.data package , i think I got what you are explaining if you can find a sample repository i would be grateful – SecurityObscurity Oct 29 '21 at 11:57
1

Ok, so you are using ADO.Net. You should first see if you want to stick to ADO.Net or use an ORM such as EF Core or Dapper. For ADO.Net you have to look into the MySqlDataReader class. See https://stackoverflow.com/questions/5371222/getting-binary-data-using-sqldatareader for an example. – Laurent Gabiot Oct 29 '21 at 12:10
Lots of good information on your answer , thanks a ton – SecurityObscurity Oct 29 '21 at 12:14

asp.net core web api reading BLOB

1 Answers1