示例#1
0
        /// <summary>
        /// 获取查询表结构
        /// </summary>
        /// <param name="type"></param>
        /// <param name="tableName"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private async Task <DataTable> GetTableStruct(string tableName, DbParam db)
        {
            DataTable dt = null;
            string    sql;

            switch (db.DbType)
            {
            case "MySql":
                sql = @"SELECT 
                            COLUMN_NAME AS Name, 
                            DATA_TYPE AS Type, 
                            IFNULL(CHARACTER_MAXIMUM_LENGTH , 0) AS TypeLength,
                            COLUMN_COMMENT AS Comment,
                            IS_NULLABLE AS nullable,
                            COLUMN_KEY AS pk
                            FROM INFORMATION_SCHEMA.Columns WHERE table_name = @tableName AND table_schema = @dbName;";
                dt  = await dbClient.Ado.GetDataTableAsync(sql, new { tableName, dbName = db.DbName });

                break;

            case "SqlServer":
                sql = @"SELECT col.name AS Name ,  
                                    ISNULL(ep.[value], '') AS Comment,  
                                    t.name AS Type, 
                                    col.length AS TypeLength,
                                    CASE WHEN EXISTS ( SELECT   1  
                                                       FROM     dbo.sysindexes si  
                                                                INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id  
                                                                                          AND si.indid = sik.indid  
                                                                INNER JOIN dbo.syscolumns sc ON sc.id = sik.id  
                                                                                          AND sc.colid = sik.colid  
                                                                INNER JOIN dbo.sysobjects so ON so.name = si.name  
                                                                                          AND so.xtype = 'PK'  
                                                       WHERE    sc.id = col.id  
                                                                AND sc.colid = col.colid ) THEN 'PRI'  
                                         ELSE ''  
                                    END AS pk ,  
                                    CASE WHEN col.isnullable = 1 THEN 'true'  
                                         ELSE 'false'  
                                    END AS nullable
                            FROM    dbo.syscolumns col  
                                    LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype  
                                    inner JOIN dbo.sysobjects obj ON col.id = obj.id  
                                                                     AND obj.xtype = 'U'  
                                                                     AND obj.status >= 0  
                                    LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id  
                                    LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id  
                                                                                  AND col.colid = ep.minor_id  
                                                                                  AND ep.name = 'MS_Description'  
                                    LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id  
                                                                                     AND epTwo.minor_id = 0  
                                                                                     AND epTwo.name = 'MS_Description'  
                            WHERE   obj.name = @tableName  
                            ORDER BY col.colorder;";
                dt  = await dbClient.Ado.GetDataTableAsync(sql, new { tableName });

                break;

            case "Sqlite":
                sql = "";
                break;

            case "Oracle":
                sql = "";
                break;

            case "PostgreSQL":
                sql = "";
                break;

            case "DbType.Dm":
                sql = "";
                break;

            case "Kdbndp":
                sql = "";
                break;

            default:
                break;
            }
            return(dt);
        }