private bool IsExcludeColumn(DBTableColumnInfo colInfo)
 {
     return(colInfo.IsIdentity ||
            colInfo.IsAutogeneratedGuid ||
            colInfo.DataType.ToUpperInvariant() == "TIMESTAMP" ||
            colInfo.DataType.ToUpperInvariant() == "DATETIME" && colInfo.ColumnName.ToUpperInvariant() == "CREATIONDATE");    //autogenerated by convention
 }
        private DBTableInfo GetTableInformation(string tableName, int schemaId, SqlConnection connection)
        {
            string sql          = $"Select * from INFORMATION_SCHEMA.COLUMNS c JOIN sys.schemas s ON c.TABLE_SCHEMA = s.name where TABLE_NAME='{tableName}' AND s.schema_id={schemaId}";
            var    dt           = ExecuteDataTable(sql, connection);
            var    sqlTableInfo = new DBTableInfo();
            List <DBTableColumnInfo> colList = new List <DBTableColumnInfo>();

            foreach (DataRow dr in dt.Rows)
            {
                if (string.IsNullOrEmpty(sqlTableInfo.Schema))
                {
                    string tableSchema = dr.Field <string>(InformationSchemaCols.TABLE_SCHEMA).Trim();
                    if (tableSchema != string.Empty)
                    {
                        sqlTableInfo.Schema = tableSchema;
                    }
                }
                string colName = dr.Field <string>(InformationSchemaCols.COLUMN_NAME);
                var    colinfo = new DBTableColumnInfo
                {
                    ColumnName = colName,
                    DataType   = dr.Field <string>(InformationSchemaCols.DATA_TYPE)
                };
                if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.CHARACTER_MAXIMUM_LENGTH]?.ToString()))
                {
                    colinfo.CharacterMaximumLength = int.Parse(dr[InformationSchemaCols.CHARACTER_MAXIMUM_LENGTH].ToString());
                }

                if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_PRECISION]?.ToString()))
                {
                    colinfo.NumericPrecision = int.Parse(dr[InformationSchemaCols.NUMERIC_PRECISION].ToString());
                }

                if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_PRECISION_RADIX]?.ToString()))
                {
                    colinfo.NumericPrecisionRadix = int.Parse(dr[InformationSchemaCols.NUMERIC_PRECISION_RADIX].ToString());
                }

                if (!string.IsNullOrWhiteSpace(dr[InformationSchemaCols.NUMERIC_SCALE]?.ToString()))
                {
                    colinfo.NumericScale = int.Parse(dr[InformationSchemaCols.NUMERIC_SCALE].ToString());
                }



                colinfo.IsIdentity          = IsIdentityColumn(colName, tableName, sqlTableInfo.Schema);
                colinfo.IsAutogeneratedGuid = IsAutogeneratedGuidColumn(dr);
                colinfo.IsPrimaryKey        = IsPrimaryColumn(colName, tableName, sqlTableInfo.Schema);
                colinfo.IsForeignKey        = IsForeignKeyColumn(colName, tableName, sqlTableInfo.Schema);
                colinfo.Exclude             = IsExcludeColumn(colinfo);

                colList.Add(colinfo);
            }

            sqlTableInfo.TableName = tableName;
            sqlTableInfo.Columns   = colList;
            return(sqlTableInfo);
        }
示例#3
0
        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);
        }
        protected string GenerateWhereStatement(List <DBTableColumnInfo> whereConditionFields)
        {
            var sb = new StringBuilder();

            sb.Append("\tWHERE ");
            for (int i = 0; i < whereConditionFields.Count; i++)
            {
                if (i != 0)
                {
                    sb.Append(Environment.NewLine + "\t\t AND ");
                }
                DBTableColumnInfo colInf = whereConditionFields[i];
                sb.Append(Wrap(colInf.ColumnName) + "=" + PrefixWhereParameter + colInf.ColumnName);
            }
            return(sb.ToString());
        }
示例#5
0
        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);
        }
示例#6
0
        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);
            }

            var selectByColumns   = new DBTableColumnInfo[sqlTableInfo.Columns.Count];
            var selectByWhereCols = sqlTableInfo.Columns.Where(x => x.IsForeignKey && !x.IsPrimaryKey).ToArray();

            sqlTableInfo.Columns.CopyTo(selectByColumns, 0);
            Array.ForEach(selectByColumns, x =>
            {
                x.Exclude = false;
            });
            if (selectByColumns.Any() &&
                selectByWhereCols.Any())
            {
                TreeViewNode selectBySp = new TreeViewNode(Constants.selectByTreeNodeText, tblNode);
                AddColumnNodes(selectBySp, selectByColumns, true);
                tblNode.Children.Add(selectBySp);
                TreeViewNode whereSelectByCondition = new TreeViewNode(Constants.whereConditionTreeNodeText,
                                                                       selectBySp);
                selectBySp.Children.Add(whereSelectByCondition);
                AddColumnNodes(whereSelectByCondition, selectByWhereCols, true);
            }

            return(tblNode);
        }