Controller_Model. cs

Controller

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NZWalks.API.Helper;
using NZWalks.API.Models;
using System.Net;


namespace NZWalks.API.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ConnectionController : ControllerBase
    {
        private readonly IConfiguration _configuration;

        public ConnectionController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [HttpGet("validate")]
        public IActionResult ValidateAllConnections()
        {
            var hostname = Dns.GetHostName();
            var connectionStrings = new List<ConnectionItem>();
            var connSection = _configuration.GetSection("ConnectionStrings");

            foreach (var conn in connSection.GetChildren())
            {
                var connStr = conn.Value ?? "";
                bool hasPassword = connStr.ToLower().Contains("password=");

                string status = "Validation Not Supported";

                if (!hasPassword)
                {
                    status = "Not Connected";
                }
                else if (conn.Key.ToLower().Contains("ApplicationDBContextConnection") || conn.Key.ToLower().Contains("acd"))
                {
                    status = ConnectionChecker.IsSqlConnectionValid(connStr) ? "Connected" : "Invalid Connection";
                }
                else if (conn.Key.ToLower().Contains("oracle"))
                {
                    status = ConnectionChecker.IsOracleConnectionValid(connStr) ? "Connected" : "Invalid Connection";
                }

                connectionStrings.Add(new ConnectionItem
                {
                    Name = conn.Key,
                    ConnectionString = hasPassword ? ConnectionChecker.MaskPassword(connStr) : "Not Connected",
                    Status = status
                });
            }

            // Check Excel File
            var connectionChecker = new ConnectionChecker(_configuration);
            var excelMessage = connectionChecker.CheckExcelFileModification();

            connectionStrings.Add(new ConnectionItem
            {
                Name = "Excel File Check",
                ConnectionString = _configuration.GetValue<string>("ExcelFileSettings:FilePath"),
                Status = excelMessage
            });

            var response = new ConnectionResponse
            {
                Connections = connectionStrings,
                StatusCode = 200,
                Message = $"Connection validation complete. {excelMessage}",
                ServerHostName = hostname
            };

            return Ok(response);
        }
    }

}

_______
Model.cs
_____
namespace NZWalks.API.Models
{
    public class ConnectionResponse
    {
        public List<ConnectionItem> Connections { get; set; }
        public int StatusCode { get; set; }
        public string Message { get; set; }
        public string ServerHostName { get; set; }
        public string ExcelFilePath { get; set; }
    }

    public class ConnectionItem
    {
        public string Name { get; set; }
        public string ConnectionString { get; set; }  // Will show "Not Connected" if password missing
        public string Status { get; set; }            // Connected / Not Connected / Invalid Connection
    }

}

_____
ConnectionChecker.cs
________
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using NZWalks.API.Models;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace NZWalks.API.Helper
{
    public class ConnectionChecker
    {
        private readonly IConfiguration _configuration;

        public ConnectionChecker(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public static bool IsSqlConnectionValid(string connectionString)
        {
             var masked = MaskPassword(connectionString);
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool IsOracleConnectionValid(string connectionString)
        {
            try
            {
                using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
                {
                    conn.Open();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        public static string MaskPassword(string connectionString)
        {
            var pattern = @"(password\s*=\s*)([^;]+)";
            return Regex.Replace(connectionString, pattern, "$1***", RegexOptions.IgnoreCase);
        }

        public string CheckExcelFileModification()
        {
            var filePath = _configuration.GetValue<string>("ExcelFileSettings:FilePath");
            var lastKnownStr = _configuration.GetValue<string>("ExcelFileSettings:LastKnownModified");
            var excelMessage = "Excel File Not Found";

            try
            {
                if (System.IO.File.Exists(filePath))
                {
                    var lastModified = System.IO.File.GetLastWriteTime(filePath);
                    var lastKnownModified = DateTime.TryParse(lastKnownStr, out var knownTime) ? knownTime : DateTime.MinValue;

                    excelMessage = lastModified > knownTime ? "Excel File Modified" : "Excel File Not Modified";
                }
            }
            catch
            {
                excelMessage = "Error Checking Excel File";
            }

            return excelMessage;
        }
      
    }

}
_________
appsetting.json
________
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "AppConnection": "Server=DESKTOP-N1BS508;Database=NZwalksDB;Trusted_Connection=True;User ID=Sa;Password=Admin@123;Encrypt=false; MultipleActiveResultSets=true",
    "ApplicationDBContextConnection": "Server=(localdb)\\mssqllocaldb;Database=ASPDotCoreHandsOn;Trusted_Connection=True;MultipleActiveResultSets=true",
    "AcdDB_connection": "Server=server1;Database=AcdDB;User Id=user;Password=pass;",
    "oracle_connection": "Data Source=oracleHost;User Id=oracleUser;Password=oraclePass;",
    "sql_connection": "Server=sqlHost;Database=SqlDB;User Id=sqlUser;Password=sqlPass;"
  },
  "ExcelFileSettings": {
    "FilePath": "C:\\Files\\DataSheet.xlsx",
    "LastKnownModified": "2024-01-01T00:00:00"
  }

}




Comments

Post a Comment

Popular posts from this blog

String Program in C#

using System; using System.Linq; 1)Reverse Name String  in C# String Name="Sudam"; int Len=Name.Length-1; while(Len>=0) { Console.Write(Name[Len]); Len--; } ----------------------------------------- Output --madus ----------------------------------------- 2)Reverse Words of a String in C# string s = "I Name Is Sudam"; string[] a = s.Split(' '); int temp= a.Length - 1; while (temp >= 0) {     Console.Write(a[temp] + ' ');     temp--; } ------------------------------------------ Output --Sudam Is Name I ---------------------------------------- 3) How to Fine count how many double letters are in a string? int doubleLetters = 0; string characters = "Hello"; for (int i = 0; i < characters.Length - 1; i++) {     if (characters[i] == characters[i + 1])     {         doubleLetters++;         i++;     }     Console.WriteLine(doubleLetters); } -----------------------------------------...
Read more

CSV using XmlNode

 To create a CSV from an XmlNode in C#, you can follow these steps: 1. Parse the XML structure using XmlNode. 2. Extract the data you need from child nodes. 3. Write the CSV content using a StringBuilder or directly with StreamWriter. Here's a sample code snippet: using System; using System.IO; using System.Text; using System.Xml; public class XmlToCsvConverter {     public static void ConvertXmlNodeToCsv(XmlNode rootNode, string csvFilePath)     {         StringBuilder csvContent = new StringBuilder();         // Assuming all child nodes have the same structure         // Add header         if (rootNode.HasChildNodes)         {             XmlNode firstRow = rootNode.FirstChild;             for (int i = 0; i < firstRow.ChildNodes.Count; i++)             {     ...
Read more