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; }