1

I am building an ASP.NET Core Web API, 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 follows:

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.

3
  • stackoverflow.com/a/39355095/5298150 Commented Jun 30, 2021 at 7:43
  • side note: QueryFirstAsync / QuerySingleAsync might be useful to you - and you probably don't need DynamicParameters here Commented Jun 30, 2021 at 7: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? Commented Jun 30, 2021 at 8:19

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.