Ejemplo n.º 1
0
        /// <summary>
        /// 根据数据名获取所有表集合
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static List<TableContext> GetTableContextListByDataBaseName(string databaseName)
        {
            var list = new List<TableContext>();
            const string sql = @"SELECT sysobject.name,extendpro.value FROM sysobjects AS sysobject
            LEFT JOIN sys.extended_properties AS extendpro ON sysobject.id=extendpro.major_id AND extendpro.minor_id=0 AND extendpro.name='MS_Description'
            WHERE sysobject.xtype ='u' order by sysobject.NAME
            ";

            var cmd = new SqlCommand(sql);

            using (var dataReader = new SqlDatabase(DataBaseConnectionConfig.GetDataBaseConnectionString(databaseName)).ExecuteReader(cmd))
            {
                // Processing code
                while (dataReader.Read())
                {
                    var table = new TableContext();

                    table.Name = dataReader.GetString(0);
                    table.Description = dataReader.GetValue(1)==null ?string.Empty:dataReader.GetValue(1).ToString();
                    //table.ColumnList = GetColumnContextListByTableName(table.TableName);

                    list.Add(table);

                }
            }

            return list;
        }
Ejemplo n.º 2
0
        public static List<ColumnContext> GetColumnContextListByTableName(string databaseName,string tableName)
        {
            var list = new List<ColumnContext>();

            var sql = @"	select
            col.COLUMN_NAME as columnName,
            (case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) +
            (case when KeyCol2.COLUMN_NAME is null then ''
              when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as kk,--PK=主键,FK=外键,PKFK=是主键同时外键
            col.DATA_TYPE as dataType,
            (case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as columnLength,
            col.IS_NULLABLE as isNullAble,--NO=不能为空,YES=可空
            col.COLUMN_DEFAULT as defaultValue,
            ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS columnDescription
            from INFORMATION_SCHEMA.COLUMNS as col
            LEFT OUTER JOIN
            (select COLUMN_NAME,TABLE_NAME FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol
            LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON
            KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND
            KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME
            WHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyCol
            ON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME
            LEFT OUTER JOIN
            (INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2
            INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON
            KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND
            KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)
            ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME
            LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" + tableName + "', 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME='" + tableName + "'";

            var cmd = new SqlCommand(sql);

            using (var dataReader = new SqlDatabase(DataBaseConnectionConfig.GetDataBaseConnectionString(databaseName)).ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    var column = new ColumnContext();

                    column.Name = dataReader.GetString(0);//列名
                    column.IsPk = dataReader.GetString(1).Equals("PK");
                    column.IsFk = dataReader.GetString(1).Equals("FK");
                    column.DateType = dataReader.GetString(2);//数据类型
                    column.Length = string.IsNullOrEmpty(dataReader.GetString(3)) ? 0 : int.Parse(dataReader.GetString(3));//数据长度
                    column.IsNull = dataReader.GetString(4).ToLower().Equals("yes");//是否为空
                    column.DefaultValue = dataReader.GetValue(5) == null ? string.Empty : dataReader.GetValue(5).ToString(); //string.IsNullOrEmpty(dataReader.GetString(5)) ? string.Empty : dataReader.GetString(5);//默认值
                    column.NameDescription = dataReader.GetString(6);//字段说明

                    list.Add(column);
                }
            }

            return list;
        }