Tag: .NET

If the requirement is to run dynamic query on each row, you might get inside Script comoponent. Here is how you can grab SQL connection and run your queries;

Your connection is defined here on script component;

public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyConn;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
    }

    public override void ReleaseConnections()
    {
        //base.ReleaseConnections();
        connMgr.ReleaseConnection(sqlConn);
    }

    public override void PreExecute()
    {
        base.PreExecute();
        string KeyValue = Variables.vFileOutputString;

        String vSqlStatement = "SELECT SqlStatement FROM [DataHub].[vw_LastStepForRglData] WHERE ADNumber = '" + KeyValue + "' "; 
        SqlCommand cmd = new SqlCommand(vSqlStatement, sqlConn);
        sqlReader = cmd.ExecuteReader();
    }

    public override void PostExecute()
    {
        base.PostExecute();
        sqlReader.Close();
    }

    public override void CreateNewOutputRows()
    {
        while (sqlReader.Read())
        {
            {
                DynamicSqlBuffer.AddRow();
                DynamicSqlBuffer.SqlStatement = sqlReader.GetString(0);
            }
        }
    }
}

Hope this will help.

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.

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;

Register these assemblies in your startup (startup.cs) to take advantage of dependency feature;

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

The command-line interface (CLI) tools for Entity Framework Core perform design-time development tasks. For example, they create migrations, apply migrations, and generate code for a model based on an existing database.

dotnet ef can be installed as either a global or local tool. Most developers prefer installing dotnet ef as a global tool using the following command:

dotnet tool install –global dotnet-ef

Update the tool tool using the following command:

dotnet tool update –global dotnet-ef

Before you can use the tools on a specific project, you’ll need to add the Microsoft.EntityFrameworkCore.Design package to it.

dotnet add package Microsoft.EntityFrameworkCore.Design

Verify installation

dotnet ef

The output from the command identifies the version of the tools in use:

For further details, refer to this link;

https://docs.microsoft.com/en-us/ef/core/cli/dotnet