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 { get; set; }
public string the__schema { get; set; }
public string procedure__name { get; set; }
public string procedure__text { get; set; }
public int colid { get; set; }
}
public class FunctionDto
{
public string ROUTINE_NAME { get; set; }
public string ROUTINE_TYPE { get; set; }
public string ROUTINE_DEFINITION { get; set; }
public string ROUTINE_SCHEMA { get; set; }
public string DATA_TYPE { get; set; }
public DateTime CREATED { get; set; }
}
public class ViewDto
{
public string schema_name { get; set; }
public string view_name { get; set; }
public bool IsIndexed { get; set; }
public bool IsIndexable { get; set; }
}
public class Logger
{
private static readonly ILog log = LogManager.GetLogger(typeof(Logger));
public static ILog Log
{
get { return Logger.log; }
}
}
}
