API to connect a FileTable in Blazor Wasm

Question

Hi,
I've created a Filetable via SqL server 2019 to have an architecture of the file (quantities 100 000 files).
I would now like to display my database file on a razor page to open the files directly from my interface. Is this possible? Do you have any tips and/or tutorials on how to interface with a database filetable?

Answer 1

Hi @sblb ,

Whether you are using Entity Framework Core (EF core) or Entity Framework to map the database table and the entity classes in the application?

Generally, in Asp.net core application, we will use the EF core to map the database table and the entity classes. And use the Dependency Injection to register the DbContext and the Repository.

When we configure/register the DbContext, we can set the connection string via the DbContextOptionsBuilder, like this:

public class BloggingContext : DbContext  
{  
    public DbSet<Blog> Blogs { get; set; }  
    public DbSet<Post> Posts { get; set; }  

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
    {  
        optionsBuilder.UseSqlServer(  
            @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True");  
    }  
}  

Or register it in the Startup.cs (asp.net core 1 ~ 5) or Program.cs file (asp.net 6), like this:

        services.AddDbContext<SchoolContext>(options =>  
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));  

More detail information, refer to Tutorial: Get started with EF Core in an ASP.NET MVC web app (in WebAPI application, it will use the similar steps) and Configuration in ASP.NET Core (To get the connection string stored in the appsetting.json file).

Finally, you can refer this article and sample to use EF core with FileTable, and build a API application to query data from FileTable.

ASP.NET Core 5 MVC file upload / download with MS SQL Server FileTable

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

Hi, I'm sure I use the filetable.
See the picture below, so I can shared the code if you want.

You're right my schema does not match with the file table schema. So the target is to match the twices.

To do that I use the code below. But It's missed the connection string I don't know I can do that? Have you an idea

using System;  
using System.Collections.Generic;  
using System.Linq;  
using DataAccess.Model;  
   
namespace DataAccess  
{  
    [LifecycleTransient]  
    public class FileRepository : IFileRepository, IDisposable  
    {  
        private FileContext _context;  
        public FileRepository()  
        {  
            _context = new FileContext();  
        }  
   
        public IEnumerable<FileDescriptionShort> AddFileDescriptions(FileResult fileResult)  
        {  
            List<string> filenames = new List<string>();  
            for (int i = 0; i < fileResult.FileNames.Count(); i++)  
            {  
                var fileDescription = new FileDescription  
                {  
                    ContentType = fileResult.ContentTypes[i],  
                    FileName = fileResult.FileNames[i],  
                    Name = fileResult.Names[i],  
                    CreatedTimestamp = fileResult.CreatedTimestamp,  
                    UpdatedTimestamp = fileResult.UpdatedTimestamp,  
                    Description = fileResult.Description  
                };  
   
                filenames.Add(fileResult.FileNames[i]);  
                _context.FileDescriptions.Add(fileDescription);  
            }  
   
            _context.SaveChanges();  
            return GetNewFiles(filenames);  
        }  
   
        private IEnumerable<FileDescriptionShort> GetNewFiles(List<string> filenames)  
        {  
            IEnumerable<FileDescription> x = _context.FileDescriptions.Where(r => filenames.Contains(r.FileName));              
            return x.Select( t => new FileDescriptionShort { Name = t.Name, Id = t.Id, Description = t.Description });  
        }  
   
        public IEnumerable<FileDescriptionShort> GetAllFiles()  
        {  
            return _context.FileDescriptions.Select(  
                    t => new FileDescriptionShort {Name = t.Name, Id = t.Id, Description = t.Description});  
        }  
   
        public FileDescription GetFileDescription(int id)  
        {  
            return _context.FileDescriptions.Single(t => t.Id == id);  
        }  
   
   
        public void Dispose()  
        {  
            if (_context != null)  
            {  
                _context.Dispose();  
                _context = null;  
            }  
        }  
    }  
}  

And after the APi

RoutePrefix("api/test")]  
public class FileUploadController : ApiController  
{  
    private IFileRepository _fileRepository = new FileRepository();  
    private static readonly string ServerUploadFolder = "\\Home\terre\FileTableContainer\SQLShackDemo"; //Path.GetTempPath();  
   
    [Route("files")]  
    [HttpPost]  
    [ValidateMimeMultipartContentFilter]  
    public async Task<IEnumerable<FileDescriptionShort>> UploadFiles()  
    {  
        var streamProvider = new MultipartFormDataStreamProvider(ServerUploadFolder);  
        await Request.Content.ReadAsMultipartAsync(streamProvider);  
   
          
        var files =  new FileResult  
        {  
            FileNames = streamProvider.FileData.Select(entry => entry.LocalFileName.Replace(ServerUploadFolder + "\\","")).ToList(),  
            Names = streamProvider.FileData.Select(entry => entry.Headers.ContentDisposition.FileName).ToList(),  
            ContentTypes = streamProvider.FileData.Select(entry => entry.Headers.ContentType.MediaType).ToList(),  
            Description = streamProvider.FormData["description"],  
            CreatedTimestamp = DateTime.UtcNow,  
            UpdatedTimestamp = DateTime.UtcNow,   
        };  
        return _fileRepository.AddFileDescriptions(files);  
    }  

Answer 3

Hi,

I've tried to make the connection string as follow but I'm not sure, can you help me about it :

"connectionStrings": {
    "add": {
            "name": "FileContext",
            "providerName" : "System.Data.SqlClient",
            "connectionString" : "Data Source=.;Initial Catalog=SQLFileTable;Integrated Security=True"
            }
  },

Answer 4

I don’t see any code that sets the connection string in the dbcontext. Typically it’s done in the constructor. You also need code to read the connection string from the config file.

I also don’t see any code that maps the file table schema to the dbcontext schema.

Answer 5

Hi, the connection string is OK right now.
I follow the link asp-net-5-mvc-6-file-upload-with-ms-sql-server-filetable
But I don't understand the step 3. Do i have add manually item group for dataaccess?
Thanks in advance