//DB setup
//https://stackoverflow.com/questions/9218847/how-do-i-handle-database-connections-with-dapper-in-net
//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/obtaining-a-dbproviderfactory
// Given a provider name and connection string,
// create the DbProviderFactory and DbConnection.
// Returns a DbConnection on success; null on failure.
public static DbConnection CreateOpenedDbConnection(string providerName, string connectionString)
{
// Assume failure.
DbConnection connection = null;
// Create the DbProviderFactory and DbConnection.
if (connectionString != null)
{
try
{
/*
* -- list providers
* DataTable table = DbProviderFactories.GetFactoryClasses();
* foreach (DataRow row in table.Rows)
* {
* foreach (DataColumn column in table.Columns)
* {
* Console.WriteLine(row[column]);
* }
* }
*/
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName); // OracleClientFactory.Instance;
connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
}
catch (Exception ex)
{
// Set the connection to null if it was created.
if (connection != null)
{
connection = null;
}
Console.WriteLine(ex.Message);
SimpleLog.WriteException("**** Opening DB connection with connection failed. Provider: " + providerName);
SimpleLog.WriteException(ex.Message);
System.Environment.Exit((int)ExitCodes.DbConnectionFailed);
}
}
// Return the connection.
return(connection);
}
private ExcelWorksheet GetWs(ExcelPackage package, Tab tab)
{
ExcelWorksheet ws;
if (templateFile == null)
{
ws = package.Workbook.Worksheets.Add(tab.name);
}
else
{
ws = package.Workbook.Worksheets[tab.name];
if (ws == null)
{
SimpleLog.WriteException("Worksheet " + tab.name + " does not exists in template" + def.template);
System.Environment.Exit((int)ExitCodes.MissingTemplate);
}
}
return(ws);
}
public void SetupEnviroment()
{
// Check definition file
if (defFileName == null)
{
SimpleLog.WriteException("**** EpSqlGen aborted, missing definition file in arguments ");
System.Environment.Exit((int)ExitCodes.MissingParams);
}
else
{
// definition file in actual directory ?
FileInfo definitionFile = new FileInfo(defFileName);
if (!definitionFile.Exists && Path.GetDirectoryName(defFileName) == "")
{
// Check work directiries
var defDir = System.Configuration.ConfigurationManager.AppSettings.Get("DefinitionsDir");
if (defDir == null || defDir == "")
{
SimpleLog.WriteException("**** EpSqlGen aborted, definition for Definitions directory does not exist!");
System.Environment.Exit((int)ExitCodes.MissingConfiguration);
}
else
{
inputDir = new DirectoryInfo(defDir);
}
if (!inputDir.Exists)
{
SimpleLog.WriteException("**** EpSqlGen aborted, definitions for input directory does not exist!");
System.Environment.Exit((int)ExitCodes.MissingWorkingDirs);
}
// look in input dir from setup
definitionFile = new FileInfo(Path.Combine(inputDir.FullName, defFileName));
}
else
{
inputDir = new DirectoryInfo(Path.GetDirectoryName(definitionFile.FullName));
}
if (!definitionFile.Exists)
{
SimpleLog.WriteException("**** EpSqlGen aborted, definitions file does not exist!");
System.Environment.Exit((int)ExitCodes.MissingFiles);
}
else
{
// Open def
string defFileContent = File.ReadAllText(definitionFile.FullName);
if (definitionFile.Extension == ".sql")
{
def = new ExcelDef {
fileName = definitionFile.Name.Substring(0, definitionFile.Name.ToLower().IndexOf(".sql")), timestamp = true, autofilter = true, tabs = new List <Tab> {
}
};
def.tabs.Add(new Tab {
name = (outFormat == ".json" ? "rows" : def.fileName), title = "", query = defFileContent.TrimEnd()
});
}
else
{
def = JsonConvert.DeserializeObject <ExcelDef>(defFileContent);
}
}
outDef = new ExcelDef {
fileName = def.fileName, template = def.template, autofilter = def.autofilter, timestamp = def.timestamp, tabs = new List <Tab>()
};
// Verify template file existence
if (!(def.template == null || def.template == ""))
{
templateFile = new FileInfo(inputDir.FullName);
if (!templateFile.Exists)
{
if (!inputDir.Exists)
{
SimpleLog.WriteException("**** EpSqlGen aborted, definitions for input directory does not exist!");
System.Environment.Exit((int)ExitCodes.MissingWorkingDirs);
}
templateFile = new FileInfo(Path.Combine(inputDir.FullName, def.template));
}
if (!templateFile.Exists)
{
SimpleLog.WriteException("Template file: " + def.template + " does not exist! (" + templateFile.FullName + ")");
System.Environment.Exit((int)ExitCodes.MissingFiles);
}
}
// Check how output file is defined - wih full path or file name only
if (outFileName == "" || outFileName == null)
{
// if outfile in params is empty , check json definition -> def.fileName
if (definitionFile.Extension == ".sql")
{
outFileName = Path.GetFileNameWithoutExtension(defFileName);
}
else
{
outFileName = ((def.fileName == "" || def.fileName == null) ? Path.GetFileNameWithoutExtension(defFileName) : def.fileName);
}
if (new FileInfo(defFileName).Exists)
{
outputDir = new DirectoryInfo(Path.GetDirectoryName(definitionFile.FullName));
}
}
if (outputDir == null)
{
if (Path.GetDirectoryName(outFileName) != "")
{
outputDir = new DirectoryInfo(Path.GetDirectoryName(outFileName));
}
else
{
var outDir = System.Configuration.ConfigurationManager.AppSettings.Get("OutputsDir");
if (outDir == null || outDir == "")
{
SimpleLog.WriteException("**** EpSqlGen aborted, definition for Definitions directory does not exist!");
System.Environment.Exit((int)ExitCodes.MissingConfiguration);
}
outputDir = new DirectoryInfo(outDir);
}
}
// Verify output file existence
if (!outputDir.Exists)
{
SimpleLog.WriteException("**** EpSqlGen aborted, definitions for output directory does not exist! (" + outputDir.FullName + ")");
SimpleLog.WriteException(outputDir.FullName);
System.Environment.Exit((int)ExitCodes.MissingWorkingDirs);
}
// switch mode, if output file is defined as json
if (Path.GetExtension(outFileName) == ".json" && outFormat != ".json")
{
outFormat = ".json";
}
// fix extension
if (Path.GetExtension(outFileName) != outFormat)
{
outFileName = outFileName + outFormat;
}
// Create output file
newFile = new FileInfo(Path.Combine(outputDir.FullName, Path.GetFileName(outFileName)));
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(Path.Combine(outputDir.FullName, Path.GetFileName(outFileName)));
}
SimpleLog.WriteLog("Started generate output for " + defFileName + " definition file.");
}
}
