private void OnVertexTraverse(List <string> path)
{
string newpath = string.Join("/", path);
//path
// .ForEach(
// (s) =>
// {
// Debug.Write(@"/" + s);
// }
// );
if (!this.TraversedPath.Exists(x => x == newpath))
{
this.TraversedPath.Add(newpath);
if (!this.ContinueTraversingCheck)
{
Model.TablePathModel modal = this.PendingPathtoTravers.Where(x => x.TableName == path[path.Count - 2]).FirstOrDefault();
List <Model.TablePathModel> PathsToRemove = this.PendingPathtoTravers.Where(x => x.Sequence > modal.Sequence).ToList();
PathsToRemove.ForEach(
(m) =>
{
this.PendingPathtoTravers.Remove(m);
});
this.ContinueTraversingCheck = !this.ContinueTraversingCheck; //change the flag
}
}
}
private void CreateQueries(List <Model.TablePathModel> dividedPath)
{
//traverse till input table
//use passed parameter to generate query
//generate sucessesor query first and then traverse to top
Model.TablePathModel tPath = dividedPath.FirstOrDefault(x => x.TableName == SQLToolMain.InputTable);
object[] objInputvalue = { SQLToolMain.InputValue };
//execute if tpath is not null (do not execute if input table not exists in path)
if (tPath != null)
{
#region traverse right side of input
for (int i = tPath.Sequence - 1; i < dividedPath.Count; i++) //sequence start from 1
{
string[] spliter = { "p" };
if (dividedPath[i].TableName == SQLToolMain.InputTable)
{
upsertMainTableObject(dividedPath[i].TableName, string.Format("select * from {0} {1}", dividedPath[i].TableName, SQLToolMain.InputWhereCondition), SQLToolMain.InputWhereColumn, objInputvalue);
}
else
{
//get preivous table
//use current table
//get dependency between tables
//run select depedency column from previous table query
//use value in current query
string ParentTable = dividedPath[i - 1].TableName;
string CurrentTable = dividedPath[i].TableName;
List <Model.DependencyModel> currentDepedency = this.DependencyModelList.Where(x => (x.RERERENCING_TABLE_NAME == ParentTable) && (x.REFERENCED_TABLE_NAME == CurrentTable)).ToList();
for (int j = 0; j < currentDepedency.Count; j++)
{
string selectColumnFromParent = currentDepedency[j].RERERENCING_COLUMN_NAME;
string selectColumnFromParentDataType = currentDepedency[j].RERERENCING_COLUMN_DATA_TYPE;
string whereColumnFromChild = currentDepedency[j].REFERENCED_COLUMN_NAME;
Model.TableMainModel parentModel = this.objMainTableModel.Find(x => x.TableName == ParentTable);
if (parentModel.SelectStatements.Count > 0)
{
for (int k = 0; k < parentModel.SelectStatements.Count; k++)
{
string query = parentModel.SelectStatements[k].Query;
query = query.Replace("*", selectColumnFromParent + " AS OUTPUTCOLUMN");
//////execute query
//////to do get multiple value with comma seperated (check type and use comma seperation)
////string columnValue = GetValuefromQuery(query, selectColumnFromParentDataType);
//////string columnValue = Convert.ToString(SqlHelper.ExecuteScalar(Configuration.Configuration.ConnectionString, CommandType.Text, query));
////if (!string.IsNullOrEmpty(columnValue)) // do not add query if return value is 0
//// upsertMainTableObject(CurrentTable, string.Format("select * from {0} (nolock) where {1} in ({2})", CurrentTable, whereColumnFromChild, columnValue));
//execute query
//to do get multiple value with comma seperated (check type and use comma seperation)
object[] columnValue = GetValuefromQuery(query, selectColumnFromParentDataType);
//string columnValue = Convert.ToString(SqlHelper.ExecuteScalar(Configuration.Configuration.ConnectionString, CommandType.Text, query));
if (columnValue != null && !(columnValue.Length == 1 && columnValue[0] == null)) // do not add query if return value is 0
{
upsertMainTableObject(CurrentTable, string.Format("select * from {0} (nolock) where {1} in ({2})", CurrentTable, whereColumnFromChild, string.Join(",", columnValue)), whereColumnFromChild, columnValue);
}
}
}
}
}
}
#endregion
}
//execute if tpath is null (fill tpath as last table in the dividedPath list )
if (tPath == null)
{
tPath = dividedPath.LastOrDefault();
}
#region traverse Left side of input
for (int i = tPath.Sequence - 1; i > 0; i--) //reverse loop input table -1 to top one
{
//get the input table and its ID again
//get the parent table
//get the relationship
//generate the query
string CurrentTable = dividedPath[i].TableName;
string ParentTable = dividedPath[i - 1].TableName;
List <Model.DependencyModel> currentDepedency = this.DependencyModelList.Where(x => (x.RERERENCING_TABLE_NAME == ParentTable) && (x.REFERENCED_TABLE_NAME == CurrentTable)).ToList();
for (int j = 0; j < currentDepedency.Count; j++)
{
string selectColumnFromParent = currentDepedency[j].RERERENCING_COLUMN_NAME;
string selectColumnFromParentDataType = currentDepedency[j].RERERENCING_COLUMN_DATA_TYPE;
string whereColumnFromChild = currentDepedency[j].REFERENCED_COLUMN_NAME;
Model.TableMainModel childModel = this.objMainTableModel.Find(x => x.TableName == CurrentTable);
//expecting childmodel will not be null
if (childModel.SelectStatements == null)
{
continue;
}
if (childModel.SelectStatements.Count > 0)
{
for (int k = 0; k < childModel.SelectStatements.Count; k++)
{
string query = childModel.SelectStatements[k].Query;
query = query.Replace("*", whereColumnFromChild + " AS OUTPUTCOLUMN");
//////execute query
////string columnValue = GetValuefromQuery(query, selectColumnFromParentDataType);
//////string columnValue = Convert.ToString(SqlHelper.ExecuteScalar(Configuration.Configuration.ConnectionString, CommandType.Text, query));
////if (!string.IsNullOrEmpty(columnValue)) // do not add query if return value is 0
//// upsertMainTableObject(ParentTable, string.Format("select * from {0} (nolock) where {1} in ({2})", ParentTable, selectColumnFromParent, columnValue),);
//execute query
object[] columnValue = GetValuefromQuery(query, selectColumnFromParentDataType);
//string columnValue = Convert.ToString(SqlHelper.ExecuteScalar(Configuration.Configuration.ConnectionString, CommandType.Text, query));
if (columnValue != null) // do not add query if return value is 0
{
upsertMainTableObject(ParentTable, string.Format("select * from {0} (nolock) where {1} in ({2})", ParentTable, selectColumnFromParent, string.Join(",", columnValue)), whereColumnFromChild, columnValue);
}
}
}
}
//if (dividedPath[i].TableName == SQLToolMain.InputTable)
// upsertMainTableObject(dividedPath[i].TableName, string.Format("select * from {0} {1}", dividedPath[i].TableName, SQLToolMain.InputWhereCondition));
//else
//upsertMainTableObject(dividedPath[i].TableName, string.Format("select * from {0} {1}", dividedPath[i].TableName, SQLToolMain.InputWhereCondition));
}
#endregion
}