App Services – Page 52 – My Point of View

Download Excel Files

Recently i had to download binary format data using ASP.NET Core, Console Core and SSIS applications;

ASP.NET Core application;

Here is a work around for ASP.NET Core Where we are getting back File Result;

[HttpGet]
public IActionResult GetExcelData2(Guid? documentId)
{
            var sql = "SELECT TOP(1) FileName, ContentType, FileStreamCol FROM [dbo].[STG_Document] WHERE 1 = 1 AND DocumentId = @documentId";

            byte[] fileContent = null;
            string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "C:\\Business\\myfile.xlsx";

            using (var connection = new SqlConnection(AppConnectionString.Value.PMBImportsConn))
            {
                connection.Open();
                var result = connection.Query(sql, new { DocumentId = documentId });
                result.AsList().ForEach(x =>
                {
                    fileContent = x.FileStreamCol;
                    mimeType = x.ContentType;
                    fileName = x.FileName;
                });

                return File(new MemoryStream(fileContent, 0, fileContent.Length - 1), mimeType, fileName);
            }
}

I am subtracting 1 element from byte array to fix a problem I encountered in a VB6 application. Otherwise, you don’t need to subtract this element.

 return File(new MemoryStream(fileContent, 0, fileContent.Length), mimeType, fileName);

This is how we call it from view;

<a asp-controller="Project" asp-action="GetExcelData2" asp-route-documentId="@p.DocumentId">Download</a>

If everything is correct, the download starts automatically.

C# Console application;

Here is how this will work in C# Console application;

internal void DownloadExcelFiles()
{
            // Create folder to save downloaded files
            string myFolderPath = @"C:\WorkToDo\";
                        Directory.CreateDirectory(myFolderPath + "\\FilesDownloaded");

            //run sql query to grab image files
            var sql = "SELECT DocumentId AS DocumentId, ContentType, FileName, FileStreamCol  FROM dbo.STG_Document WHERE 1 = 1";

            byte[] fileContent = null;
            string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "";

            using (var connection = new SqlConnection(DB2SqlConnectionString))
            {
                connection.Open();
                var result = connection.Query(sql);
                result.AsList().ForEach(x =>
                {
                    string fileExtension = "xlsx";
                    switch (x.ContentType)
                    {
                        case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                        case "application/vnd.ms-excel":
                        case "application/msexcel":
                            fileExtension = "xlsx";
                            break;
                        case "application/vnd.ms-excel.sheet.macroEnabled.12":
                            fileExtension = "xlsm";
                            break;
                        default:
                            break;
                    }
                    string fileToWrite = $"{myFolderPath}\\FilesDownloaded\\{x.DocumentId}.{fileExtension}";
                    fileContent = x.FileStreamCol;
                    mimeType = x.ContentType;
                    fileName = x.FileName;

                    FileStream fileStream = File.OpenWrite(fileToWrite);
                    fileStream.Write(fileContent, 0, fileContent.Length - 1);
                    fileStream.Flush();
                    fileStream.Close();
                });
                Console.WriteLine("Download complete..");
            }           
}

I am subtracting 1 element from byte array to fix a problem I encountered in a VB6 application. Otherwise, you don’t need to subtract this element.

FileStream fileStream = File.OpenWrite(fileToWrite);
fileStream.Write(fileContent, 0, fileContent.Length);

I ma using Dapper and SqlClient libraries ASP.NET Core and Console application to access data from Database.

SSIS application;

Here is how this will work in SQL Server Integration services;

ADO.NET Source

SELECT DocumentId, [FileName], [ContentType], [FileStreamCol]
FROM [dbo].[Document]
WHERE 1=1

SC-Transform data

This is a SSIS Transformation script component. This prepares the data that will be used later on in Export Column task;

Here is the code block;

private byte[] fileContent = null;
private string fileExtension = String.Empty;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
        //determine content type
        switch (Row.ContentType)
        {
            case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
            case "application/vnd.ms-excel":
            case "application/msexcel":
                fileExtension = "xlsx";
                break;
            case "application/vnd.ms-excel.sheet.macroEnabled.12":
                fileExtension = "xlsm";
                break;
            default:
                break;
        }
Row.fileContentName = String.Concat(Row.DocumentId, ".", fileExtension);
//A VB6 application saves excel files as BLOB objects. For download it subtracts one element from 
//byte array. Following same dance here
        var blobLength = Convert.ToInt32(Row.FileStreamCol.Length);
        fileContent = Row.FileStreamCol.GetBlobData(0, blobLength - 1);
        Row.fileContent.AddBlobData(fileContent);
}

Derived column transform;

Export column settings;

Checking Force Truncate overwrites extracted files.

Hope this will help.

Load Bootstrap tabs dynamically

Need Bootstrap tabs, but with dynamic content? Use this JS to fetch the content before showing the tab. Works for in-content links as well.

//the reason for the odd-looking selector is to listen for the click event
// on links that don't even exist yet - i.e. are loaded from the server.
$('#tabs').on('click','.tablink,#prodTabs a',function (e) {
    e.preventDefault();
    var url = $(this).attr("data-url");

    if (typeof url !== "undefined") {
        var pane = $(this), href = this.hash;

        // ajax load from data-url
        $(href).load(url,function(result){      
            pane.tab('show');
        });
    } else {
        $(this).tab('show');
    }
});

Video chat application challenges

Would you like to build video chat application using WebRTC. Read this what the challenges could be;

Simplest way to test Node.js is working

I am assuming that Node.Js is installed on your machine. Use any editor and write this JavaScript;

var http = require('http');
var url = require('url');

http.createServer(function (req, res) {
    console.log("Request: " + req.method + " to " + req.url);

    res.writeHead(200, "OK");
    res.write("<h1>Hello</h1>Node.js is working");
    res.end();
}).listen(80);
console.log("Ready on port 80");

Save it as NodeTest.js.

Open a command prompt and run this;

node NodeTest.js

You should see this;

ASP.NET Core Configuration

I have seen this common pattern to get connection strings, application configuration settings and logger in ASP.NET core application;

public class HomeController 
{
   private IUnitOfWork _unitOfWork;
   private IConfiguration _configuration;        
   private AppConfig _appConfiguration;
   private readonly ILogger _logger;

   public HomeController(IUnitOfWork unitOfWork, IConfiguration configuration, IOptions<AppConfig> appConfiguration, ILogger<ProjectController> logger)
   {
        _unitOfWork = unitOfWork;
	_configuration = configuration;
        _appConfiguration = appConfiguration.Value;
        _logger = logger;
   }

This works fine but it’s reparative for configurations and logger and violates DRY principal. A good alternative is this;

public void ConfigureServices(IServiceCollection services)
        {
            //Add functionality to inject IOptions<T>
            services.AddOptions();

            //Add our Config object so it can be injected
            services.Configure<ConnectionString>(Configuration.GetSection("ConnectionStrings"));
            services.Configure<AppConfig>(Configuration.GetSection("AppConfig"));
        }

UPDATE @10/18/2022
For .NET 6, the configuration is;
builder.Services.AddOptions();

builder.Services.Configure<ConnectionString>(builder.Configuration.GetSection("ConnectionStrings"));

builder.Services.Configure<AppConfig>(builder.Configuration.GetSection("AppConfig"));

Create AppConfig POCO class;

public class AppConfig
{
   public string ApplicationName { get; set; }
   public string Version { get; set; }
}

Create a base class and declare these services as properties.

using System;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

using ConfigDemo.Models;

   public abstract class BaseController<T> : Controller where T : BaseController<T>
    {
        private ILogger<T> _logger;
        private IConfiguration _configuration;
        private IOptions<ConnectionString> _appConnectionString;
        private IOptions<AppConfig> _appConfiguration;

        protected ILogger<T> Logger => _logger ?? (_logger = HttpContext?.RequestServices.GetService<ILogger<T>>());
        protected IConfiguration Configuration => _configuration ?? (_configuration = HttpContext?.RequestServices.GetService<IConfiguration>());
        protected IOptions<ConnectionString> AppConnectionString => _appConnectionString ?? (_appConnectionString = (HttpContext?.RequestServices.GetService<IOptions<ConnectionString>>()));
        protected IOptions<AppConfig> AppConfiguration => _appConfiguration ?? (_appConfiguration = (HttpContext?.RequestServices.GetService<IOptions<AppConfig>>()));

        protected string DisplayMessage
        {
            get { return TempData["DisplayMessage"] == null ? String.Empty : TempData["DisplayMessage"].ToString(); }
            set { TempData["DisplayMessage"] = value; }
        }
    }

We have these values in our appsettings.json file that we would like to use in our application;

{
  "ConnectionStrings": {
    "DBConnection1": "Data Source=(local);Initial Catalog=MyDb1;Persist Security Info=True;Integrated Security=True",
    "DBConnection2": "Data Source=(local);Initial Catalog=MyDb2;Persist Security Info=True;Integrated Security=True"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  //application configratuion here
  "AppConfig": {
    "ApplicationName": "MyApp",
    "Version": "1.0.0"
  }
}

Let’s create a demo view to display output values;

@model String
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Result</title>
</head>
<body>
    <div>
        @Model
    </div>
</body>
</html>

Finally create our first HomeController by inheriting from base controller to read config values;

       public class HomeController : BaseController<HomeController>
    {
        //Navigate to URL, for example https://localhost:44320/home/simpleconfig
        public string Index()
        {
            Logger.LogInformation("I am using dependency injection created in the base cotroller");
            return "Navigate to URL to show an example";
        }

        //using configuration
        public ViewResult SimpleConfig()
        {
            var configValue = Configuration.GetSection("AppConfig").GetChildren();
            string result = configValue.Select(i => i.Value).Aggregate((i, j) => i + "," + j );
            // generate the view
            return View("Result",
            (object)String.Format("Simple Config value: {0}", result));
        }

        //using strong type
        public ViewResult ConfigValueFromConfig()
        {
            string configValue = AppConfiguration.Value.ApplicationName;
            // generate the view
            return View("Result",
            (object)String.Format("App Config value: {0}", configValue));
        }

Run your application and navigate to action method like this;

You can download sample project from GitHub;

https://github.com/shahzy1/ConfigDemoApplication/tree/main

Resources

https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?view=aspnetcore-5.0

https://stackoverflow.com/questions/47433027/configuration-getsection-in-asp-net-core-2-0-getting-all-settings