1

I am building an API in Asp.Core, where different requests are made. One of them is that a user should be able to add on or more files to the request if wanted. The database table is created as following:

CREATE TABLE OptionalFile (
    FileID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FileName NVARCHAR(100) NOT NULL,
    FileBloB VARBINARY(MAX) NOT NULL,
    CreatedDate DATETIME DEFAULT getdate() NOT NULL,
    CreatedBy NVARCHAR(20) DEFAULT suser_sname() NOT NULL,
    UpdatedDate DATETIME DEFAULT getdate() NOT NULL,
    UpdatedBy NVARCHAR(20) DEFAULT suser_sname() NOT NULL
    );

//cross table 
CREATE TABLE ClaimCrossOptionalFile(
        ClaimID int FOREIGN KEY REFERENCES Claim(ID) NOT NULL,
        FileID int FOREIGN KEY REFERENCES OptionalFile(FileID) NOT NULL,
        CreatedDate DATETIME DEFAULT getdate() NOT NULL,
        CreatedBy NVARCHAR(20) DEFAULT suser_sname() NOT NULL,
        UpdatedDate DATETIME DEFAULT getdate() NOT NULL,
        UpdatedBy NVARCHAR(20) DEFAULT suser_sname() NOT NULL
    );

Now, i would like to only have a field in the API called "files" or something similar to upload the file, and that the database would accept the filename and the binary data of the file. For the rest of my API I'm using Dapper to insert data to the database. I currently have the following code for just inserting the filename:

if (item.fileuploadresults != null) {

  try {

    foreach(FileUploadResult f in item.fileuploadresults) {
      var parameters = new DynamicParameters();
      parameters.Add("filename", f.filename);

      var filemessage = await _sqlconnection.QueryAsync < int > ($ @ "INSERT INTO 
      [dbo].[OptionalFile] ([FileName])
        VALUES(@filename); SELECT SCOPE_IDENTITY();
        ", parameters);

        int FileMessageID = filemessage.First();

        //update crosstable
        await _sqlconnection.QueryAsync < int > ($ @ "INSERT INTO[dbo].
        [ClaimCrossOptionalFile]
          (ClaimID, FileID) VALUES({
            claimID
          }, {
            FileMessageID
          });
          ");

        }

      }

      catch (Exception ex) {
        int k = 0;
      }

    }

The model looks like following:

public class FileUploadResult
    {
        public IFormFile files { get; set; } 

        [MaxLength]
        public byte[] FileBlob { get; set; }

        public long Length { get; set; }

        public string filename { get; set; }
    }


 public class Service
    {
        public IList<FileUploadResult> fileuploadresults { get; set; } 
    }

What I'm stuck on is how to proceed, to be able to store filename and the binary file data in the database. (I'm using postman to post data). I'm open to ideas on how to proceed, if anyone has them.

ajd871
  • 57
  • 1
  • 20
  • https://stackoverflow.com/a/39355095/5298150 – abdusco Jun 30 '21 at 07:43
  • side note: `QueryFirstAsync` / `QuerySingleAsync` might be useful to you - and you probably don't need `DynamicParameters` here – Marc Gravell Jun 30 '21 at 07:45
  • 1
    i included a parameter using byte[], thank you. Now, the next problem is that postman does not like when you want to send both form-data for the file-upload and raw json for the rest of the api... Anyone know how to mix these 2? – ajd871 Jun 30 '21 at 08:19

1 Answers1

1

You can store blobs via dapper simply by including a parameter with type byte[] (and including it in the insert). If the problem is that the file is particularly large (making a large byte[] inappropriate), then you might need to loop and append chunks with +, like this

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900