private void upsertMainTableObject(string TableName, string Query, string whereColumn, object[] whereValues)
{
Model.TableMainModel model = this.objMainTableModel.Find(x => x.TableName == TableName);
//see if model is null
// add table name and data in model
//if model is not null
// add the sql statements to the model
// add the max of sequence
if (model == null) //add table
{
model = new Model.TableMainModel {
TableName = TableName
};
this.objMainTableModel.Add(model);
}
//adding ColumnvaluePair
if (model.ColumnValuePair == null)
{
model.ColumnValuePair = new List <Model.ColumnValuePair>();
model.ColumnValuePair.Add(new Model.ColumnValuePair {
ColumnName = whereColumn, WhereValues = whereValues.ToList()
});
}
else
{
Model.ColumnValuePair existingPair = model.ColumnValuePair.Find(x => x.ColumnName == whereColumn);
if (existingPair != null)
{
//model.ColumnValuePair.Any(x => x.WhereValues == whereValues)
//var result = model.ColumnValuePair.Intersect(whereValues);
List <object> obj = whereValues.Except(existingPair.WhereValues).ToList();
if (obj != null && obj.Count > 0)
{
existingPair.WhereValues.AddRange(obj);
}
}
else
{
model.ColumnValuePair.Add(new Model.ColumnValuePair {
ColumnName = whereColumn, WhereValues = whereValues.ToList()
});
}
}
if (model.SelectStatements == null)
{
model.SelectStatements = new List <Model.SelectStatementModel>();
}
//validate same query already exists or not
if (model.SelectStatements.Count(x => x.Query == Query) > 0)
{
return;
}
int maxSequence = model.SelectStatements.Count == 0 ? 0 : model.SelectStatements.Max(t => t.Sequence);
model.SelectStatements.Add(new Model.SelectStatementModel {
Query = Query, Sequence = maxSequence + 1
});
}
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
}
