public ActionResult V2([FromUri] DALGetRefsInputParams inputParams)
{
inputParams.TryLoadFromCookies(System.Web.HttpContext.Current.Request);
DALGetRefsInputParams.ThrowIfWrong(inputParams);
return(View(inputParams));
}
private static void Main(string[] args)
{
var inputParams = new DALGetRefsInputParams()
{
ServerName = "(local)",
DatabaseName = "AMCHealth",
UserName = "******",
Password = "******",
RootTableNames = "Patient.Patients"
};
//var tables = DAL.GetTables( inputParams );
var refs = DAL.GetRefs(inputParams);
var grouped_refs = (from it in refs
group it by it.FKName into g
select g
).ToArray();
foreach (var g in grouped_refs)
{
Console.WriteLine(g.Key);
foreach (var it in g)
{
Console.WriteLine($"\t{it.TableName}.{it.Column} - {it.ForeignTableName}.{it.ForeignColumn}");
}
Console.WriteLine();
}
}
public static void TryLoadFromCookies(this DALGetRefsInputParams inputParams, HttpRequest request)
{
((DALGetTablesInputParams)inputParams).TryLoadFromCookies(request);
if (inputParams.GraphHeight == 0)
{
inputParams.GraphHeight = request.GetCookieInt32("GraphHeight").GetValueOrDefault(1024);
}
if (inputParams.GraphWidth == 0)
{
inputParams.GraphWidth = request.GetCookieInt32("GraphWidth").GetValueOrDefault(1280);
}
}
public TableCollection GetTables([FromUri] DALGetTablesInputParams inputParams)
{
try
{
inputParams.TryLoadFromCookies(HttpContext.Current.Request);
DALGetRefsInputParams.ThrowIfWrong(inputParams);
return(GetTablesInternal(inputParams));
}
catch (Exception ex)
{
return(new TableCollection(ex));
}
}
public static ICollection <RefItem> GetRefs(DALGetRefsInputParams inputParams)
{
DALGetTablesInputParams.ThrowIfWrong(inputParams);
using (var con = new SqlConnection(inputParams.ConnectionString))
using (var cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = inputParams.RootTableNamesSet.Any() ? COMMAND_TIMEOUT_60 : COMMAND_TIMEOUT_300;
cmd.CommandText = string.Format(SQL_GET_REFS_BY_FOREIGN_TABLENAME
, inputParams.RootTableNamesSet.Any() ? 0 : 1
, '\'' + string.Join("','", inputParams.RootTableNamesSet.Select(table => table.Replace("'", "''"))) + '\'');
con.Open();
var refs = new LinkedList <RefItem>();
using (var rd = cmd.ExecuteReader())
{
for (int idx_1 = rd.GetOrdinal("Level"),
idx_2 = rd.GetOrdinal("IsSelfRefs"),
idx_3 = rd.GetOrdinal("FKName"),
idx_4 = rd.GetOrdinal("TableName"),
idx_5 = rd.GetOrdinal("Column"),
idx_6 = rd.GetOrdinal("ForeignTableName"),
idx_7 = rd.GetOrdinal("ForeignColumn");
rd.Read();)
{
var refItem = new RefItem()
{
Level = rd.GetInt32(idx_1),
IsSelfRefs = (rd.GetInt32(idx_2) != 0),
FKName = rd.GetString(idx_3),
TableName = rd.GetString(idx_4),
Column = rd.GetString(idx_5),
ForeignTableName = rd.GetString(idx_6),
ForeignColumn = rd.GetString(idx_7),
};
refs.AddLast(refItem);
}
}
return(refs);
}
}
public Graph GetRefs([FromUri] DALGetRefsInputParams inputParams)
{
try
{
inputParams.TryLoadFromCookies(HttpContext.Current.Request);
DALGetRefsInputParams.ThrowIfWrong(inputParams);
#region [.check exists root-tables.]
if (inputParams.RootTableNamesSet.Any())
{
var tableCollection = GetTablesInternal(inputParams);
foreach (var rootTableName in inputParams.RootTableNamesSet)
{
if (!tableCollection.Contains(rootTableName))
{
throw (new TableNotExistsException(rootTableName));
}
}
}
#endregion
#if USE_CACHE
var refs = HttpContext.Current.Cache.Get(
inputParams.ConnectionString + '-' + inputParams.RootTableNames
, () => DAL.GetRefs(inputParams));
#else
var refs = DAL.GetRefs(inputParams);
#endif
if (!refs.Any())
{
if (inputParams.RootTableNamesSet.Any())
{
throw (new RefsNotFoundException(inputParams.RootTableNamesSet));
}
foreach (var rootTableName in inputParams.RootTableNamesSet)
{
refs.Add(RefItem.CreateForSingleTable(rootTableName));
}
}
var nodes = new Dictionary <string, Node>();
var links = new HashSet <Link>(new LinkEqualityComparer());
#region [.create-graph-nodes-&-links.]
var node_id = 0;
var link_id = 0;
if (inputParams.RootTableNamesSet.Any())
{
foreach (var rootTableName in inputParams.RootTableNamesSet)
{
//var linksTotal = refs.Count( it => it.Level <= 2 && it.TableName == rootTableName );
var centerNode = new Node(node_id, rootTableName, true);
nodes.Add(centerNode.Name, centerNode);
node_id++;
}
}
else
{
var rootTableName = refs.FirstOrDefault().ForeignTableName;
if (rootTableName.IsNullOrWhiteSpace())
{
throw (new RefsNotFoundException(inputParams.RootTableNamesSet));
}
//var linksTotal = refs.Count( it => it.Level <= 2 && it.TableName == rootTableName );
var centerNode = new Node(node_id, rootTableName, true);
nodes.Add(centerNode.Name, centerNode);
node_id++;
}
var tables = (from it in refs
select it.TableName
).Concat
(from it in refs
select it.ForeignTableName
);
foreach (var table in tables)
{
if (!nodes.ContainsKey(table))
{
var node = new Node(node_id, table);
nodes.Add(node.Name, node);
node_id++;
}
}
var grouped_refs = from it in refs
group it by it.FKName into g
select g;
foreach (var g in grouped_refs)
{
var it = g.First();
var node_1 = nodes[it.ForeignTableName];
var node_2 = nodes[it.TableName];
link_id++;
var link = new Link(link_id)
{
SourceNode = node_1.Id,
TargetNode = node_2.Id,
SourceFields = from _it in g select _it.ForeignColumn,
TargetFields = from _it in g select _it.Column,
};
links.Add(link);
/*var success = links.Add( link );
* if ( !success )
* System.Diagnostics.Debugger.Break();*/
}
#endregion
var graph = new Graph()
{
links = links,
nodes = nodes.Values,
documents = new string[0], //model.Documents.Select( fi => fi.Name ),
linksTotalCount = 0, //model.AllMTSTermLinksCount,
nodesTotalCount = 0, //model.AllMTSTermsCount,
};
CalcGraphCoords(graph, inputParams.GraphWidth, inputParams.GraphHeight);
return(graph);
}
catch (TableNotExistsException ex)
{
return(Graph.CreateError("Table with name '" + ex.TableName + "' not exists"));
}
catch (RefsNotFoundException ex)
{
var error = ex.RootTableNames.Any()
? ("Reference from table " + ex.RootTableNames.Join('\'', '\'', "','") + " not found")
: "Any tables not found";
return(Graph.CreateError(error));
}
catch (Exception ex)
{
return(Graph.CreateError(ex));
}
}
