获得数据库对象信息

//获得数据表
            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;
            }
        }