private DBTableInfo GetTableInformation(string tableName, SqlConnection connection)
{
string sql = "Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'";
var dt = ExecuteDataTable(sql, connection);
var sqlTableInfo = new DBTableInfo();
List<DBTableColumnInfo> colList = new List<DBTableColumnInfo>();
foreach (DataRow dr in dt.Rows)
{
var colinfo = new DBTableColumnInfo();
colinfo.ColumnName = dr["COLUMN_NAME"].ToString();
colinfo.DataType = dr["DATA_TYPE"].ToString();
if (dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "")
colinfo.CharacterMaximumLength = int.Parse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
if (dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"].ToString().Trim() != "")
colinfo.NumericPrecision = int.Parse(dr["NUMERIC_PRECISION"].ToString());
if (dr["NUMERIC_PRECISION_RADIX"] != null && dr["NUMERIC_PRECISION_RADIX"].ToString().Trim() != "")
colinfo.NumericPrecisionRadix = int.Parse(dr["NUMERIC_PRECISION_RADIX"].ToString());
if (dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"].ToString().Trim() != "")
colinfo.NumericScale = int.Parse(dr["NUMERIC_SCALE"].ToString());
if (dr["TABLE_SCHEMA"] != null && dr["TABLE_SCHEMA"].ToString().Trim() != "")
colinfo.Schema = dr["TABLE_SCHEMA"].ToString();
colinfo.IsIdentity = IsDinityColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.IsPrimaryKey = IsPrimaryColumn(dr["COLUMN_NAME"].ToString(), tableName);
colinfo.Exclude = IsExcludeColumn(colinfo);
colList.Add(colinfo);
}
sqlTableInfo.TableName = tableName;
sqlTableInfo.Columns = colList;
return sqlTableInfo;
}
private string GetColumnDispalyName(DBTableColumnInfo colInfo)
{
string diaplayName = colInfo.ColumnName;
if (colInfo.Exclude)
{
if (colInfo.IsIdentity)
diaplayName += " (IDENTITY)";
if (colInfo.DataType.ToUpperInvariant() == "TIMESTAMP")
diaplayName += " (TIMESTAMP)";
}
return diaplayName;
}
private bool IsExcludeColumn(DBTableColumnInfo colInfo)
{
return colInfo.IsIdentity
|| colInfo.DataType.ToUpperInvariant() == "TIMESTAMP"
|| colInfo.DataType.ToUpperInvariant() == "DATETIME" && colInfo.ColumnName.ToUpperInvariant() == "CREATIONDATE"; //autogenerated by convention
}
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;
}
