PDF Upload and Download from SQL Server

Question

using blazor and dapper and MSSQLserver want to upload a PDF file into the database table and then save a record . and again preview the file.

Sarthak Varshney · Answer

To upload, save, and preview a PDF file using Blazor, Dapper, and SQL Server, you'll need to follow several steps. Here is a detailed guide to help you achieve this: Set Up the Database : Create a table in your SQL Server database to store the PDF files. CREATE TABLE PdfFiles ( Id INT PRIMARY KEY IDENTITY, FileName NVARCHAR(255), FileData VARBINARY(MAX) ); Create a Blazor Project : If you haven't already, create a new Blazor Server project. dotnet new blazorserver -n PdfUploadDemo cd PdfUploadDemo Install Required Packages : Add the necessary NuGet packages to your Blazor project. dotnet add package Dapper dotnet add package System.Data.SqlClient Create a Service to Handle PDF Operations : Create a service that uses Dapper to interact with the database. This service will handle uploading and retrieving PDF files. using Dapper; using System.Data.SqlClient; using System.Threading.Tasks; public class PdfService { private readonly string _connectionString; public PdfService(string connectionString) { _connectionString = connectionString; } public async Task UploadPdfAsync(string fileName, byte[] fileData) { using (var connection = new SqlConnection(_connectionString)) { var query = "INSERT INTO PdfFiles (FileName, FileData) VALUES (@FileName, @FileData); SELECT CAST(SCOPE_IDENTITY() as int)"; var parameters = new { FileName = fileName, FileData = fileData }; var id = await connection.QuerySingleAsync (query, parameters); return id; } } public async Task GetPdfAsync(int id) { using (var connection = new SqlConnection(_connectionString)) { var query = "SELECT FileName, FileData FROM PdfFiles WHERE Id = @Id"; var result = await connection.QuerySingleOrDefaultAsync (query, new { Id = id }); return result; } } } Register the Service in Blazor : Register the PdfService in the Startup.cs or Program.cs file. builder.Services.AddSingleton(new PdfService("YourConnectionStringHere")); Create Blazor Components : Create components for uploading and previewing PDF files. Upload.razor : @page "/upload" @inject PdfService PdfService Upload PDF @code { private async Task UploadFile(InputFileChangeEventArgs e) { var file = e.File; var fileName = file.Name; using (var stream = new MemoryStream()) { await file.OpenReadStream().CopyToAsync(stream); var fileData = stream.ToArray(); var id = await PdfService.UploadPdfAsync(fileName, fileData); // Handle the uploaded file ID as needed (e.g., display a message or redirect) } } } Preview.razor : @page "/preview/{id:int}" @inject PdfService PdfService Preview PDF @if (pdfData != null) { } else { Loading... } @code { [Parameter] public int Id { get; set; } private byte[] pdfData; private string pdfBase64; protected override async Task OnInitializedAsync() { var result = await PdfService.GetPdfAsync(Id); pdfData = result.FileData; pdfBase64 = Convert.ToBase64String(pdfData); } } Add Navigation Links : Add navigation links in your NavMenu.razor to access the upload and preview pages. Upload PDF Run Your Application : Run your Blazor application and navigate to the upload page to upload a PDF file, then navigate to the preview page to view the uploaded PDF. This setup covers the basic functionality for uploading and previewing PDF files using Blazor, Dapper, and SQL Server. You can expand this further by adding more features like file validation, error handling, and user authentication.