private void GetHeaderRows(ref DataTableExporter csv, ref List <string> headers, string table,
ref List <string> seentables, string where, int depth = 0)
{
if (seentables.Contains(table))
{
return;
}
//get data
var tableValues = RunQuery(table, @where);
//insert
var i = 0;
foreach (var r in tableValues)
{
if (depth == 0)
{
seentables = new List <string>();
}
seentables.Add(table);
//
var list = headers;
var unique = r.Keys.Where(s => list.Contains(s) == false).ToList();
if (headers.Any() == false)
{
headers.Add("");
}
headers.AddRange(unique);
//
var ts = GetParsedForeignKeyTables(r.Keys.ToList(), table);
//recurse with these tables
foreach (var t in ts)
{
var key = t.KeyName;
var val = r[key];
var lwhere = $" where {key} = {val}";
GetHeaderRows(ref csv, ref headers, t.TableName, ref seentables, lwhere, depth + 1);
}
if (depth == 0)
{
return;
}
i++;
}
}
private void AddRows(ref DataTableExporter csv, string table, ref List <string> seentables, string where,
bool alphaHeaderCols, int depth = 0)
{
if (seentables.Contains(table))
{
return;
}
//get data
var tableValues = RunQuery(table, @where);
//insert
var i = 0;
foreach (var r in tableValues)
{
if (depth == 0)
{
seentables = new List <string>();
}
seentables.Add(table);
csv.AddRow(r, table);
var ts = GetParsedForeignKeyTables(r.Keys.ToList(), table);
//recurse with these tables
foreach (var t in ts)
{
var key = t.KeyName;
var val = r[key];
var lwhere = $" where {key} = {val}";
AddRows(ref csv, t.TableName, ref seentables, lwhere, alphaHeaderCols, depth + 1);
}
if (depth == 0)
{
//finalise
csv.AddPage();
}
i++;
}
}
public void Generate(string table, string @where, bool openFile, bool alphaHeaderCols)
{
var cs = new DataTableExporter();
var ts = new List <string>();
var headers = new List <string>();
if (alphaHeaderCols)
{
GetHeaderRows(ref cs, ref headers, table, ref ts, @where);
//order
headers = headers.OrderBy(s => s).ToList();
//init
cs.HeaderRows = headers;
ts = new List <string>();
}
AddRows(ref cs, table, ref ts, @where, alphaHeaderCols);
//output
cs.ExportXLS("SQLAUTOJOIN" + FileExtras.GenerateRandomFileName("xlsx"), openFile);
}
public void Generate(string table, string @where, bool openFile, bool alphaHeaderCols)
{
var cs = new DataTableExporter();
var ts = new List<string>();
var headers = new List<string>();
if (alphaHeaderCols)
{
GetHeaderRows(ref cs, ref headers, table, ref ts, @where);
//order
headers = headers.OrderBy(s => s).ToList();
//init
cs.HeaderRows = headers;
ts = new List<string>();
}
AddRows(ref cs, table, ref ts, @where, alphaHeaderCols);
//output
cs.ExportXLS("SQLAUTOJOIN" + FileExtras.GenerateRandomFileName("xlsx"), openFile);
}
private void GetHeaderRows(ref DataTableExporter csv, ref List<string> headers, string table,
ref List<string> seentables, string where, int depth = 0)
{
if (seentables.Contains(table))
return;
//get data
var tableValues = RunQuery(table, @where);
//insert
var i = 0;
foreach (var r in tableValues)
{
if (depth == 0)
{
seentables = new List<string>();
}
seentables.Add(table);
//
var list = headers;
var unique = r.Keys.Where(s => list.Contains(s) == false).ToList();
if (headers.Any() == false)
headers.Add("");
headers.AddRange(unique);
//
var ts = GetParsedForeignKeyTables(r.Keys.ToList(), table);
//recurse with these tables
foreach (var t in ts)
{
var key = t.KeyName;
var val = r[key];
var lwhere = $" where {key} = {val}";
GetHeaderRows(ref csv, ref headers, t.TableName, ref seentables, lwhere, depth + 1);
}
if (depth == 0)
{
return;
}
i++;
}
}
private void AddRows(ref DataTableExporter csv, string table, ref List<string> seentables, string where,
bool alphaHeaderCols, int depth = 0)
{
if (seentables.Contains(table))
return;
//get data
var tableValues = RunQuery(table, @where);
//insert
var i = 0;
foreach (var r in tableValues)
{
if (depth == 0)
{
seentables = new List<string>();
}
seentables.Add(table);
csv.AddRow(r, table);
var ts = GetParsedForeignKeyTables(r.Keys.ToList(), table);
//recurse with these tables
foreach (var t in ts)
{
var key = t.KeyName;
var val = r[key];
var lwhere = $" where {key} = {val}";
AddRows(ref csv, t.TableName, ref seentables, lwhere, alphaHeaderCols, depth + 1);
}
if (depth == 0)
{
//finalise
csv.AddPage();
}
i++;
}
}
