コード例 #1
0
ファイル: DAOHelper.cs プロジェクト: test123test111/Tools
        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);
        }
コード例 #2
0
ファイル: DAOHelper.cs プロジェクト: test123test111/Tools
        /// <summary>
        /// 根据表名获取所有字段集合
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<ColumnContext> GetColumnContextListByTableName(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 = _db.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;
        }