public TreeViewNode(string name,TreeViewNode parent, bool @checked = false)
{
this.Name = name;
this.Children = new List<TreeViewNode>();
this._parent =parent;
this.IsNodeEnabled = true;
this.IsChecked = @checked;
}
private List<DBTableColumnInfo> GetWhereClauseFields(TreeViewNode tableChildNode)
{
var selectedFields = new List<DBTableColumnInfo>();
var whereClauseNode = GetChildNodeByText(tableChildNode, Constants.whereConditionTreeNodeText);
if (whereClauseNode == null)
return selectedFields;
foreach (TreeViewNode fieldNode in whereClauseNode.Children)
{
if (fieldNode.IsChecked ?? true)
{
selectedFields.Add((DBTableColumnInfo)fieldNode.Tag);
}
}
return selectedFields;
}
private void PopulateTree(IEnumerable<DBTableInfo> sqlTableList, TreeViewWithCheckBox treeView1)
{
TreeViewNode root = new TreeViewNode(Constants.rootTreeNodeText, null);
foreach (var tbl in sqlTableList)
{
root.Children.Add(CreateTableNode(tbl, root));
}
root.IsNodeExpanded = true;
treeView1.DataContext = new List<TreeViewNode> { root };
rootNode = root;
}
private List<DBTableColumnInfo> GetSelectedFields(TreeViewNode tableChildNode)
{
var selectedFields = new List<DBTableColumnInfo>();
foreach (TreeViewNode fieldNode in tableChildNode.Children)
{
if ((fieldNode.IsChecked ?? true) && fieldNode.Name != Constants.whereConditionTreeNodeText)
{
selectedFields.Add((DBTableColumnInfo)fieldNode.Tag);
}
}
return selectedFields;
}
private TreeViewNode GetChildNodeByText(TreeViewNode parentNode, string childNodeText)
{
var childNode = parentNode.Children.OfType<TreeViewNode>()
.FirstOrDefault(node => node.Name.Equals(childNodeText));
return childNode;
}
private string GenerateSPForSingleTable(TreeViewNode tblNode)
{
var sb = new StringBuilder();
foreach (TreeViewNode childNode in tblNode.Children)
{
var selectedFields = GetSelectedFields(childNode);
var whereClauseSelectedFields = GetWhereClauseFields(childNode);
if (childNode.IsChecked ?? true)
{
try
{
var sp = model.GenerateSp((DBTableInfo)tblNode.Tag, childNode.Name, selectedFields, whereClauseSelectedFields);
sb.AppendLine(sp.Script);
}
catch (Exception e)
{
}
}
}
return sb.ToString();
}
private TreeViewNode CreateTableNode(DBTableInfo sqlTableInfo, TreeViewNode parent)
{
string tableDisplayName = sqlTableInfo.Schema == "dbo"
? sqlTableInfo.TableName
: sqlTableInfo.Schema + "." + sqlTableInfo.TableName;
TreeViewNode tblNode = new TreeViewNode(tableDisplayName, parent);
tblNode.Tag = sqlTableInfo;
TreeViewNode insertSp = new TreeViewNode(Constants.insertTreeNodeText, tblNode);
AddColumnNodes(insertSp, sqlTableInfo.Columns.Where(x => !x.Exclude), true);
tblNode.Children.Add(insertSp);
TreeViewNode deleteSp = new TreeViewNode(Constants.deleteTreeNodeText, tblNode);
TreeViewNode whereDelCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, deleteSp);
deleteSp.Children.Add(whereDelCondition);
tblNode.Children.Add(deleteSp);
AddColumnNodes(whereDelCondition, sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToList(), true);
var updateCols = sqlTableInfo.Columns.Where(x => !x.Exclude).ToArray();
var updateWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
if (updateCols.Any() && updateCols.All(x => !updateWhereCols.Contains(x)))
{
TreeViewNode updateSp = new TreeViewNode(Constants.updateTreeNodeText, tblNode);
AddColumnNodes(updateSp, updateCols, true);
tblNode.Children.Add(updateSp);
TreeViewNode whereUpdateCondition = new TreeViewNode(Constants.whereConditionTreeNodeText, updateSp);
updateSp.Children.Add(whereUpdateCondition);
AddColumnNodes(whereUpdateCondition, updateWhereCols, true);
}
AddColumnNodes(deleteSp, new List<DBTableColumnInfo>(), true);
var selectColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
TreeViewNode selectSp = new TreeViewNode(Constants.selectTreeNodeText, tblNode);
sqlTableInfo.Columns.CopyTo(selectColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
AddColumnNodes(selectSp, selectColumns, true);
tblNode.Children.Add(selectSp);
var selectOneColumns = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
var selectOneWhereCols = sqlTableInfo.Columns.Where(x => x.IsPrimaryKey).ToArray();
sqlTableInfo.Columns.CopyTo(selectOneColumns, 0);
Array.ForEach(selectColumns, x =>
{
x.Exclude = false;
});
if (selectOneColumns.Any() &&
selectOneWhereCols.All(x => selectOneColumns.Any(y => x.ColumnName != y.ColumnName)))
{
TreeViewNode selectOneSp = new TreeViewNode(Constants.selectOneTreeNodeText, tblNode);
AddColumnNodes(selectOneSp, selectOneColumns, true);
tblNode.Children.Add(selectOneSp);
TreeViewNode whereSelectOneCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
selectOneSp);
selectOneSp.Children.Add(whereSelectOneCondition);
AddColumnNodes(whereSelectOneCondition, selectOneWhereCols, true);
}
return tblNode;
}
private TreeViewNode AddColumnNodes(TreeViewNode parentNode, IEnumerable<DBTableColumnInfo> columns, bool disableExludeColumn)
{
foreach (var colInfo in columns)
{
TreeViewNode colNode = new TreeViewNode(GetColumnDispalyName(colInfo), parentNode);
colNode.Tag = colInfo;
if (disableExludeColumn)
{
colNode.IsNodeEnabled = !colInfo.Exclude;
}
parentNode.Children.Add(colNode);
}
return parentNode;
}
