Upload files in ASP.Core: How to store the binary file in the database table, using Dapper to insert data

Question

I am building an API in ASP.NET 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 this:

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.

Answer 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