DatabaseUploadStorage

If Anybody Interested in storing files and image binaries in database

Add table to store files binaries in database.

using FluentMigrator;

using Serenity.Extensions;

namespace SereneExtensions.Migrations.DefaultDB
{
    [Migration(20161029140000)]
    public class DefaultDB_20161029_140000_FilesStore : AutoReversingMigration
    {
        public override void Up()
        {
            Create.Table("FilesStore")
             .WithColumn("Path").AsString(512).PrimaryKey()
             .WithColumn("Content").AsBinary(int.MaxValue).NotNullable()
             .WithColumn("MetadataJson").AsString(int.MaxValue).Nullable();

        }
    }
}

Create a service DatabaseUploadStorage by implementing IUploadStorage

using Serenity.Web;
using System.Collections.Generic;
using System.IO;
using System;
using Newtonsoft.Json;
using Serenity.Data;
using System.Linq;

namespace SereneExtensions.Web.Services
{
    public class DatabaseUploadStorage : IUploadStorage
    {
        private readonly ISqlConnections _sqlConnections;

        public DatabaseUploadStorage(ISqlConnections sqlConnections)
        {
            _sqlConnections=sqlConnections;
        }
        public string WriteFile(string path, Stream source, OverwriteOption overwrite)
        {
            byte[] fileBytes;
            using (var ms = new MemoryStream())
            {
                source.CopyTo(ms);
                fileBytes = ms.ToArray();
            }

            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                var result = connection.Execute(
                        "INSERT INTO FilesStore (Path, Content) VALUES (@Path, @Content)",
                        new { Path = path, Content = fileBytes });
            }
            return path;
        }

        public Stream OpenFile(string path)
        {
            byte[] fileBytes;
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                fileBytes = connection.Query<byte[]>(
                    "SELECT Content FROM FilesStore WHERE Path = @Path",
                    new { Path = path }).FirstOrDefault();
            }
            return fileBytes != null ? new MemoryStream(fileBytes) : null;
        }

        public void DeleteFile(string path)
        {
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                connection.Execute(
                    "DELETE FROM FilesStore WHERE Path = @Path",
                    new { Path = path });

                if (IsImage(path))
                {
                    string tempThum = $"{path.Substring(0, path.Length - 4)}_t.jpg";
                    connection.Execute(
                        "DELETE FROM FilesStore WHERE Path = @Path",
                        new { Path = tempThum });
                }
            }
        }
        public bool FileExists(string path)
        {
            bool fileExits = false;
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                fileExits = Dapper.SqlMapper.ExecuteScalar<bool>(connection,
                    "SELECT COUNT(*) FROM FilesStore WHERE Path = @Path",
                    new { Path = path });
            }
            return fileExits;
        }
        public string ArchiveFile(string path)
        {
            // Implement based on your archiving strategy
            return path;
        }
        public string CopyFrom(IUploadStorage source, string path, string targetPath, OverwriteOption overwrite)
        {
            byte[] fileBytes;
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                fileBytes = connection.Query<byte[]>(
                    "SELECT Content FROM FilesStore WHERE Path = @Path",
                    new { Path = path }).FirstOrDefault();

                var tempfileName = Path.GetFileNameWithoutExtension(path);

                var ext = Path.GetExtension(path);
                string tempThum = $"{path.Substring(0, path.Length - 4)}_t.jpg";
                string targetPathThum = $"{targetPath.Substring(0, targetPath.Length - 4)}_t.jpg";

                var result = connection.Execute(
                        "INSERT INTO FilesStore (Path, Content) VALUES (@Path, @Content)",
                        new { Path = targetPath, Content = fileBytes });
                if (IsImage(path))
                {
                    connection.Execute(
                               "Update FilesStore Set Path = @targetPathThum where Path =@tempThum ",
                               new { targetPathThum = targetPathThum, tempThum = tempThum });
                }

            }
            return targetPath;
        }
        public string[] GetFiles(string path, string searchPattern)
        {
            // Implement this method based on your requirements
            return Array.Empty<string>();
        }
        public long GetFileSize(string path)
        {
            long fileSize = 0;
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                fileSize =  Dapper.SqlMapper.ExecuteScalar<long>(connection,
                    "SELECT DATALENGTH(Content) FROM FilesStore WHERE Path = @Path",
                    new { Path = path });
            }
            return fileSize;
        }
        public string GetFileUrl(string path)
        {
            // Not applicable for database storage
            return null;
        }
        public void PurgeTemporaryFiles()
        {
            // Implement your purge logic here
        }
        public IDictionary<string, string> GetFileMetadata(string path)
        {
            IDictionary<string, string> fileMetadata = new Dictionary<string, string>();
            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                var metadataJson = Dapper.SqlMapper.QuerySingleOrDefault<string>(connection,
                    "SELECT MetadataJson FROM FilesStore WHERE Path = @Path",
                    new { Path = path });

                if (!string.IsNullOrEmpty(metadataJson))
                {
                    fileMetadata = JsonConvert.DeserializeObject<IDictionary<string, string>>(metadataJson);
                }
                else
                {
                    return null;
                }
                return fileMetadata;
            }
        }

        public void SetFileMetadata(string path, IDictionary<string, string> metadata, bool overwriteAll)
        {
            var metadataJson = JsonConvert.SerializeObject(metadata);

            using (var connection = _sqlConnections.NewByKey("Default"))
            {
                connection.Execute(
                    "UPDATE FilesStore SET MetadataJson = @MetadataJson WHERE Path = @Path",
                    new { Path = path, MetadataJson = metadataJson });
            }
        }

        private bool IsImage(string filePath)
        {
            // Extract the file extension
            string extension = Path.GetExtension(filePath).ToLower();

            // List of image extensions
            string[] imageExtensions = new[] { ".jpg", ".jpeg", ".png", ".gif", ".bmp", ".tiff", ".ico" };

            // Check if the file extension is in the list of image extensions
            return Array.Exists(imageExtensions, e => e.Equals(extension));
        }

    }

    internal class StoredFile
    {
        public string Path { get; set; }
        public byte[] Content { get; set; }
        public string MetadataJson { get; set; }
    }
}

Add in DI in startup.cs after services.AddScriptBundling();

  services.AddSingleton<IUploadStorage, DatabaseUploadStorage>();