@manhnguyenv

Welcome to my blog!

Log4Net

October 15, 2019 21:54

Log4Net (edit)

http://rion.io/2017/11/18/knowing-when-to-reflect-with-caller-info-attributes/

Trace Log

using System.Runtime.CompilerServices;
...
public static class Helpers
{
public static string GetCallerName([CallerMemberName] string caller = null)
{
return caller;
}
}

Solution 1: Without namespace

using System.Diagnostics;
...
​Logger.Log.Info(string.Format("{0} called at {1}",
new System.Diagnostics.StackFrame(1, true).GetMethod(),
System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));

Solution 2: Without namespace (BEST SOLUTION)

Logger.Log.Info(string.Format("{0} called at {2}"
, Helpers.GetCallerName()
, System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));

Solution 3: With namespace

using System.Reflection;
...
Logger.Log.Info(string.Format("{0}.{1} called at {2}"
, System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Namespace
, Helpers.GetCallerName()
, System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));

Web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />

...
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="Logs\\WebApplication1.log" />
      <appendToFile value="true" />
      <encoding value="UTF-8" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="5" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <lockingModel type="log4net.Appender.FileAppender+MinimalLock" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="TraceAppender" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5level %message%newline" />
      </layout>
    </appender>
    <root>
      <level value="All" />
      <appender-ref ref="RollingFileAppender" />
      <appender-ref ref="TraceAppender" />
      <appender-ref ref="ConsoleAppender" />
    </root>
  </log4net>
</configuration>

Logger.cs

public class Logger
{
    private static readonly ILog log = LogManager.GetLogger(typeof(Logger));

    public static ILog Log
    {
        get { return Logger.log; }
    }
}

AssemblyInfo.cs

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

Global.asax.cs

public class MvcApplication : System.Web.HttpApplication
{

    protected void Application_Start()
    {
        log4net.Config.XmlConfigurator.Configure();

Log4net + Dapper + Stored Procedures + Views + Functions + Triggers

June 4, 2019 00:09

Log4net + Dapper + Stored Procedures + Views + Functions + Triggers (edit)

Source Code

1/ NuGet packages

  • Microsoft .NET Framework 4.5
  • Log4net version 2.0.8
  • Dapper version 1.50.2

2/ Properties\AssemblyInfo.cs

Snippet

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

3/ App.config

Snippet

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=MANHNV;Initial Catalog=Northwind;Uid=sa;Pwd=123456;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="ConsoleApp1.log" />
      <appendToFile value="true" />
      <encoding value="UTF-8" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="5" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <lockingModel type="log4net.Appender.FileAppender+MinimalLock" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="TraceAppender" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5level %message%newline" />
      </layout>
    </appender>
    <root>
      <level value="All" />
      <appender-ref ref="RollingFileAppender" />
      <appender-ref ref="TraceAppender" />
      <appender-ref ref="ConsoleAppender" />
    </root>
  </log4net>
</configuration>

4/ Program.cs

Snippet

using Dapper;
using log4net;
using log4net.Config;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace ConsoleApp1
{
    class Program
    {
        static string m_ConnectionString = @"
Data Source=MANHNV;
Initial Catalog=Northwind;
Uid=sa;
Pwd=123456;
";
 
        const string sqlStoredProcedure = @"
 SELECT db_name() AS the__database
, OBJECT_SCHEMA_NAME(P.object_id) AS the__schema
, P.name AS procedure__name 
, C.text AS procedure__text
, C.colid
 FROM sys.procedures P WITH(NOLOCK)
 LEFT JOIN sys.syscomments C ON P.object_id = C.id;";
 
        const string sqlFunction = @"
SELECT
    ROUTINE_NAME, 
	ROUTINE_TYPE, 
    ROUTINE_DEFINITION , 
    ROUTINE_SCHEMA, 
    DATA_TYPE,
    CREATED
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
    ROUTINE_TYPE = 'FUNCTION'
	AND DATA_TYPE = 'TABLE'
";
 
        const string sqlView = @"
SELECT SCHEMA_NAME(schema_id) AS schema_name
, name AS view_name
, OBJECTPROPERTYEX(OBJECT_ID, 'IsIndexed') AS IsIndexed
, OBJECTPROPERTYEX(OBJECT_ID, 'IsIndexable') AS IsIndexable
FROM sys.views;
";
 
        static void Main(string[] args)
        {
            //6 ways to get the current directory in C#
            //https://yetanotherchris.dev/csharp/6-ways-to-get-the-current-directory-in-csharp/
            string basePathStoredProcedures = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.StoredProcedures);
 
            string basePathFunction = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.Functions);
 
            string basePathView = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.Views);
 
            //How to: Create a File or Folder (C# Programming Guide)
            //https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/file-system/how-to-create-a-file-or-folder
            System.IO.Directory.CreateDirectory(basePathStoredProcedures);
            System.IO.Directory.CreateDirectory(basePathFunction);
            System.IO.Directory.CreateDirectory(basePathView);
 
            //Console.WriteLine(basePath);
 
            int totalCount = 0;
            using (IDbConnection db = new SqlConnection(m_ConnectionString))
            {
                var listProc = db.Query<StoredProcDto>(sqlStoredProcedure, CommandType.StoredProcedure).ToList();
                var listUniqueProcName = (from x in listProc select x.procedure__name).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Stored Procedures");
                Logger.Log.Info("Work with the Stored Procedures");
 
                foreach (var procName in listUniqueProcName)
                {
                    try
                    {
                        string fileName = procName.Trim().Replace("_""") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathStoredProcedures, fileName);
                        string storedProcedureText = string.Join("", listProc.Where(m => m.procedure__name == procName).Select(m => m.procedure__text).ToList());
                        System.IO.File.WriteAllText(filePath, storedProcedureText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} stored procedures successfully!", totalCount));
 
                /*
                 * Process for Functions
                 */
                var listFunction = db.Query<FunctionDto>(sqlFunction, CommandType.StoredProcedure).ToList();
                var listUniqueFunctionName = (from x in listFunction select x.ROUTINE_NAME).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Functions");
                Logger.Log.Info("Work with the Functions");
 
                foreach (var functionName in listUniqueFunctionName)
                {
                    try
                    {
                        string fileName = functionName.Trim().Replace("_""") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathFunction, fileName);
                        string functionText = string.Join("", listFunction.Where(m => m.ROUTINE_NAME == functionName).Select(m => m.ROUTINE_DEFINITION).ToList());
                        System.IO.File.WriteAllText(filePath, functionText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} functions successfully!", totalCount));
 
                /*
                 * Process for View
                 */
                var listView = db.Query<ViewDto>(sqlView, CommandType.StoredProcedure).ToList();
                var listUniqueViewName = (from x in listView select string.Concat(x.schema_name, Constants.Dot, x.view_name)).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Views");
                Logger.Log.Info("Work with the Views");
 
                foreach (var viewName in listUniqueViewName)
                {
                    try
                    {
 
 
                        string sqlTextView = @"
select definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id('" + viewName + @"') and o.type = 'V'
";
                        string fileName = viewName.Trim().Replace("_""") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathView, fileName);
                        string viewText = db.Query<string>(sqlTextView).FirstOrDefault();
                        System.IO.File.WriteAllText(filePath, viewText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} views successfully!", totalCount));
            }
 
            Console.ReadKey();
        }
    }
 
    public static class Constants
    {
        public const string SqlExtension = ".sql";
 
        public const string StoredProcedures = "StoredProcedures";
        public const string Functions = "Functions";
        public const string Views = "Views";
 
        public const string Dot = ".";
    }
 
    public class StoredProcDto
    {
        public string the__database { getset; }
        public string the__schema { getset; }
        public string procedure__name { getset; }
        public string procedure__text { getset; }
        public int colid { getset; }
 
    }
 
    public class FunctionDto
    {
        public string ROUTINE_NAME { getset; }
        public string ROUTINE_TYPE { getset; }
        public string ROUTINE_DEFINITION { getset; }
        public string ROUTINE_SCHEMA { getset; }
        public string DATA_TYPE { getset; }
        public DateTime CREATED { getset; }
    }
 
    public class ViewDto
    {
        public string schema_name { getset; }
        public string view_name { getset; }
        public bool IsIndexed { getset; }
        public bool IsIndexable { getset; }
    }
 
    public class Logger
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(Logger));

        public static ILog Log
        {
            get { return Logger.log; }
        }
    }
}

Categories

Recent posts