Log4net + Dapper + Stored Procedures + Views + Functions + Triggers (edit)
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; } } } }
