获得数据库对象信息

来源：互联网发布：华迈千里眼软件下载编辑：程序博客网时间：2024/05/17 02:30

//获得数据表            switch(DBConnForm.DatabaseType)            {                case DBConnForm.DatabaseTypeTag.SQLServer:                    tables = DBCommand.QueryStrs("select name from sysobjects where xtype='U' order by name");                    break;                case DBConnForm.DatabaseTypeTag.MySQL:                    tables = ValuesToStrings(DBCommand.QueryValues_MySQL("SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='" + database + "' AND TABLE_TYPE='base table' ORDER BY TABLE_NAME;"));                    break;            }

//获取数据表字段信息public class ColumnInfo        {            public string DataType { get; set; }            public string ColumnName { get; set; }            public bool IsNullable { get; set; }            public bool Auto_Increment { get; set; }            //COLUMN_COMMENT            public string Comment { get; set; }            public bool IsPrimaryKey { get; set; }        }        public interface IDbHelper        {            List<ColumnInfo> GetDBColumns(string DatabaseName, string TableName);            string GetTableComment(string DatabaseName, string TableName);            string GetRepositoryDBBaseExtension();        }        public class MySQLDbHelper : IDbHelper        {            public List<ColumnInfo> GetDBColumns(string DatabaseName, string TableName)            {                var sql= string.Format(@"SELECT COLUMN_NAME,IS_NULLABLE,DATA_TYPE,COLUMN_COMMENT,COLUMN_KEY,EXTRA FROM information_schema.`COLUMNS`                                             WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' ORDER BY ORDINAL_POSITION;", DatabaseName,                                            TableName);                List<ColumnInfo> Columns = new List<ColumnInfo>();                DBComm.DBCommand.QuerySomeMySQL(sql, dr =>                {                    while (dr.Read())                    {                        var col = new ColumnInfo()                        {                            ColumnName = Convert.ToString(dr.GetValue(0)),                            IsNullable = Convert.ToString(dr.GetValue(1)).ToLower() == "yes",                            DataType = Convert.ToString(dr.GetValue(2)),                            Comment = Convert.ToString(dr.GetValue(3)),                            IsPrimaryKey = Convert.ToString(dr.GetValue(4)).ToUpper() == "PRI",                            Auto_Increment= Convert.ToString(dr.GetValue(5)).ToLower() == "auto_increment",                        };                        Columns.Add(col);                        Columns[Columns.Count - 1].DataType = Common.ColumnInfo.DBTypeToSystemType(col.DataType, col.IsNullable);                    }                });                return Columns;            }            public string GetRepositoryDBBaseExtension()            {                return "RepositoryMySQLBaseExtension";            }            public string GetTableComment(string DatabaseName, string TableName)            {                return Convert.ToString(DBComm.DBCommand.QueryValue_MySQL(string.Format(@"select TABLE_COMMENT FROM information_schema.`TABLES`                                                         WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}';", DatabaseName, TableName)));            }        }        public class SQLServerDBHelper : IDbHelper        {            public List<ColumnInfo> GetDBColumns(string DatabaseName, string TableName)            {                var sql = @" select                                                 col.name as ColumnName,                                                  col.isnullable as IsNullable,                                                  tp.name as DataType,                                                  ep.value as Descript,                                                  (                                                    select count(*) from sys.sysobjects                                                    where parent_obj=obj.id                                                    and name=(                                                        select top 1 name from sys.sysindexes ind                                                        inner join sys.sysindexkeys indkey                                                        on ind.indid=indkey.indid                                                        and indkey.colid=col.colid                                                        and indkey.id=obj.id                                                        where ind.id=obj.id                                                        and ind.name like 'PK_%'                                                    )                                                  ) as IsPrimaryKey                                                 ,COLUMNPROPERTY(col.id,col.name,'IsIdentity') as IsIdentity                                                  from sys.sysobjects obj                                                  inner join sys.syscolumns col                                                  on obj.id = col.id                                                  left join sys.systypes tp                                                  on col.xtype=tp.xusertype                                                  left join sys.extended_properties ep                                                  on ep.major_id=obj.id                                                  and ep.minor_id=col.colid                                                  and ep.name='MS_Description'" +                                                                 string.Format(" where obj.name=\'{0}\'", TableName);                List<ColumnInfo> Columns = new List<ColumnInfo>();                DBComm.DBCommand.QuerySome(sql, sr =>                {                    while(sr.Read())                    {                        ColumnInfo cinfo = new ColumnInfo()                        {                            ColumnName = sr.GetString(0),                            IsNullable = Convert.ToInt32(sr.GetValue(1)) == 0,                            DataType = sr.GetString(2),                            Comment = Convert.ToString(sr.GetValue(3)),                            IsPrimaryKey = Convert.ToInt32(sr.GetValue(4)) == 0,                            Auto_Increment = Convert.ToInt32(sr.GetValue(5)) == 1                        };                        Columns.Add(cinfo);                        Columns[Columns.Count - 1].DataType = Common.ColumnInfo.DBTypeToSystemType(cinfo.DataType, cinfo.IsNullable);                    }                    return null;                });                return Columns;            }            public string GetRepositoryDBBaseExtension()            {                return "RepositoryMSSQLBaseExtension";            }            public string GetTableComment(string DatabaseName, string TableName)            {                return DBComm.DBCommand.QueryStr(@"select                                            isnull(g.[value],'') AS 说明                                        from                                           sys.tables a left join sys.extended_properties g                                           on (a.object_id = g.major_id AND g.minor_id = 0) where a.name='" + TableName + "'  order by a.name");            }        }        public static class DBHelperFactory        {            public  static IDbHelper GetInstance(DBConnForm.DatabaseTypeTag tag)            {                IDbHelper dbhelper = null;                switch (DBConnForm.DatabaseType)                {                    case DBConnForm.DatabaseTypeTag.MySQL:                        dbhelper = new MySQLDbHelper();                        break;                    case DBConnForm.DatabaseTypeTag.SQLServer:                        dbhelper = new SQLServerDBHelper();                        break;                }                return dbhelper;            }        }

1 0