C# DataConnection.Query方法代码示例

		protected override List<TableInfo> GetTables(DataConnection dataConnection)
		{
			_currentUser = dataConnection.Execute<string>("select user from dual");

			if (IncludedSchemas.Length != 0 || ExcludedSchemas.Length != 0)
			{
				// This is very slow
				return dataConnection.Query<TableInfo>(
					@"
					SELECT
						d.OWNER || '.' || d.NAME                         as TableID,
						d.OWNER                                          as SchemaName,
						d.NAME                                           as TableName,
						d.IsView                                         as IsView,
						CASE :CurrentUser WHEN d.OWNER THEN 1 ELSE 0 END as IsDefaultSchema,
						tc.COMMENTS                                      as Description
					FROM
					(
						SELECT t.OWNER, t.TABLE_NAME NAME, 0 as IsView FROM ALL_TABLES t
							UNION ALL
							SELECT v.OWNER, v.VIEW_NAME NAME, 1 as IsView FROM ALL_VIEWS v
					) d
						JOIN ALL_TAB_COMMENTS tc ON
							d.OWNER = tc.OWNER AND
							d.NAME  = tc.TABLE_NAME
					ORDER BY TableID, isView
					",
					new { CurrentUser = _currentUser })
				.ToList();
			}
			else
			{
				// This is significally faster
				return dataConnection.Query<TableInfo>(
					@"
					SELECT
						:CurrentUser || '.' || d.NAME as TableID,
						:CurrentUser                  as SchemaName,
						d.NAME                        as TableName,
						d.IsView                      as IsView,
						1                             as IsDefaultSchema,
						tc.COMMENTS                   as Description
					FROM
					(
						SELECT t.TABLE_NAME NAME, 0 as IsView FROM USER_TABLES t
							UNION ALL
							SELECT v.VIEW_NAME NAME, 1 as IsView FROM USER_VIEWS v
					) d
						JOIN USER_TAB_COMMENTS tc ON
							d.NAME = tc.TABLE_NAME
					ORDER BY TableID, isView
					",
					new { CurrentUser = _currentUser })
				.ToList();
			}
		}

 protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
 {
     if (IncludedSchemas.Length != 0 || ExcludedSchemas.Length != 0)
     {
         // This is very slow
         return dataConnection.Query<ColumnInfo>(@"
             SELECT
                 c.OWNER || '.' || c.TABLE_NAME             as TableID,
                 c.COLUMN_NAME                              as Name,
                 c.DATA_TYPE                                as DataType,
                 CASE c.NULLABLE WHEN 'Y' THEN 1 ELSE 0 END as IsNullable,
                 c.COLUMN_ID                                as Ordinal,
                 c.DATA_LENGTH                              as Length,
                 c.DATA_PRECISION                           as Precision,
                 c.DATA_SCALE                               as Scale,
                 0                                          as IsIdentity,
                 cc.COMMENTS                                as Description
             FROM ALL_TAB_COLUMNS c
                 JOIN ALL_COL_COMMENTS cc ON
                     c.OWNER       = cc.OWNER      AND
                     c.TABLE_NAME  = cc.TABLE_NAME AND
                     c.COLUMN_NAME = cc.COLUMN_NAME
             ORDER BY TableID, Ordinal
             ")
         .ToList();
     }
     else
     {
         // This is significally faster
         return dataConnection.Query<ColumnInfo>(@"
             SELECT
                 (SELECT USER FROM DUAL) || '.' || c.TABLE_NAME as TableID,
                 c.COLUMN_NAME                                  as Name,
                 c.DATA_TYPE                                    as DataType,
                 CASE c.NULLABLE WHEN 'Y' THEN 1 ELSE 0 END     as IsNullable,
                 c.COLUMN_ID                                    as Ordinal,
                 c.DATA_LENGTH                                  as Length,
                 c.DATA_PRECISION                               as Precision,
                 c.DATA_SCALE                                   as Scale,
                 0                                              as IsIdentity,
                 cc.COMMENTS                                    as Description
             FROM USER_TAB_COLUMNS c
                 JOIN USER_COL_COMMENTS cc ON
                     c.TABLE_NAME  = cc.TABLE_NAME AND
                     c.COLUMN_NAME = cc.COLUMN_NAME
             ORDER BY TableID, Ordinal
             ")
         .ToList();
     }
 }

        protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
        {
            var sql = @"
                    SELECT
                        table_catalog || '.' || table_schema || '.' || table_name           as TableID,
                        column_name                                                         as Name,
                        is_nullable = 'YES'                                                 as IsNullable,
                        ordinal_position                                                    as Ordinal,
                        data_type                                                           as DataType,
                        character_maximum_length                                            as Length,
                        COALESCE(numeric_precision, datetime_precision, interval_precision) as Precision,
                        numeric_scale                                                       as Scale,
                        is_identity = 'YES' OR COALESCE(column_default ~* 'nextval', false) as IsIdentity,
                        is_generated <> 'NEVER'                                             as SkipOnInsert,
                        is_updatable = 'NO'                                                 as SkipOnUpdate
                    FROM
                        information_schema.columns";

            if (ExcludedSchemas.Length == 0 || IncludedSchemas.Length == 0)
                sql += @"
                    WHERE
                        table_schema NOT IN ('pg_catalog','information_schema')";

            return dataConnection.Query<ColumnInfo>(sql).ToList();
        }

		protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
		{
			return
			(
				from pk in dataConnection.Query(
					rd => new
					{
						id   = dataConnection.Connection.Database + "." + rd.ToString(0) + "." + rd.ToString(1),
						name = rd.ToString(2),
						cols = rd.ToString(3).Split('+').Skip(1).ToArray(),
					},@"
					SELECT
						TABSCHEMA,
						TABNAME,
						INDNAME,
						COLNAMES
					FROM
						SYSCAT.INDEXES
					WHERE
						UNIQUERULE = 'P' AND " + GetSchemaFilter("TABSCHEMA"))
				from col in pk.cols.Select((c,i) => new { c, i })
				select new PrimaryKeyInfo
				{
					TableID        = pk.id,
					PrimaryKeyName = pk.name,
					ColumnName     = col.c,
					Ordinal        = col.i
				}
			).ToList();
		}

 protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
 {
     return dataConnection.Query<ColumnInfo>(@"
         SELECT
             TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID,
             COLUMN_NAME                                           as Name,
             CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END       as IsNullable,
             ORDINAL_POSITION                                      as Ordinal,
             c.DATA_TYPE                                           as DataType,
             CHARACTER_MAXIMUM_LENGTH                              as Length,
             ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION)         as [Precision],
             NUMERIC_SCALE                                         as Scale,
             ISNULL(CONVERT(varchar(8000), x.Value), '')           as [Description],
             COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as IsIdentity,
             CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as SkipOnInsert,
             CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as SkipOnUpdate
         FROM
             INFORMATION_SCHEMA.COLUMNS c
             LEFT JOIN
                 sys.extended_properties x
             ON
                 OBJECT_ID(TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME) = x.major_id AND
                 ORDINAL_POSITION = x.minor_id AND
                 x.name = 'MS_Description'")
         .ToList();
 }

        protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
        {
            var sql = @"
                SELECT
                    TBCREATOR,
                    TBNAME,
                    NAME,
                    LENGTH,
                    SCALE,
                    NULLS,
                    CASE WHEN DEFAULT IN ('I', 'J') THEN 'Y' ELSE 'N' END,
                    COLNO,
                    COLTYPE,
                    REMARKS
                FROM
                    SYSIBM.SYSCOLUMNS
                WHERE
                    " + GetSchemaFilter("TBCREATOR");

            return dataConnection.Query(
                rd => new ColumnInfo
                {
                    TableID     = dataConnection.Connection.Database + "." + rd.GetString(0) + "." + rd.GetString(1),
                    Name        = rd.ToString(2),
                    Length      = Converter.ChangeTypeTo<int>(rd[3]),
                    Scale       = Converter.ChangeTypeTo<int>(rd[4]),
                    IsNullable  = rd.ToString(5) == "Y",
                    IsIdentity  = rd.ToString(6) == "Y",
                    Ordinal     = Converter.ChangeTypeTo<int>(rd[7]),
                    DataType    = rd.ToString(8),
                    Description = rd.ToString(9),
                },
                sql).ToList();
        }

        protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
        {
            var sql = @"
                SELECT
                    TABSCHEMA,
                    TABNAME,
                    COLNAME,
                    LENGTH,
                    SCALE,
                    NULLS,
                    IDENTITY,
                    COLNO,
                    TYPENAME,
                    REMARKS
                FROM
                    SYSCAT.COLUMNS
                WHERE
                    " + GetSchemaFilter("TABSCHEMA");

            return _columns = dataConnection.Query(
                rd => new ColumnInfo
                {
                    TableID     = dataConnection.Connection.Database + "." + rd.GetString(0) + "." + rd.GetString(1),
                    Name        = rd.ToString(2),
                    Length      = Converter.ChangeTypeTo<long>(rd[3]),
                    Scale       = Converter.ChangeTypeTo<int> (rd[4]),
                    IsNullable  = rd.ToString(5) == "Y",
                    IsIdentity  = rd.ToString(6) == "Y",
                    Ordinal     = Converter.ChangeTypeTo<int>(rd[7]),
                    DataType    = rd.ToString(8),
                    Description = rd.ToString(9),
                },
                sql).ToList();
        }

		protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
		{
			return _primaryKeys = dataConnection.Query(
				rd => new PrimaryKeyInfo
				{
					TableID        = dataConnection.Connection.Database + "." + rd.ToString(0) + "." + rd.ToString(1),
					PrimaryKeyName = rd.ToString(2),
					ColumnName     = rd.ToString(3),
					Ordinal        = Converter.ChangeTypeTo<int>(rd[4])
				},@"
					SELECT
						col.TBCREATOR,
						col.TBNAME,
						idx.NAME,
						col.NAME,
						col.KEYSEQ
					FROM
						SYSIBM.SYSCOLUMNS col
							JOIN SYSIBM.SYSINDEXES idx ON
								col.TBCREATOR = idx.TBCREATOR AND
								col.TBNAME    = idx.TBNAME
					WHERE
						col.KEYSEQ > 0 AND idx.UNIQUERULE = 'P' AND " + GetSchemaFilter("col.TBCREATOR") + @"
					ORDER BY
						col.TBCREATOR, col.TBNAME, col.KEYSEQ")
				.ToList();
		}

		protected override List<TableInfo> GetTables(DataConnection dataConnection)
		{
			return dataConnection.Query<TableInfo>(
				_isAzure ? @"
				SELECT
					TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID,
					TABLE_CATALOG                                         as CatalogName,
					TABLE_SCHEMA                                          as SchemaName,
					TABLE_NAME                                            as TableName,
					CASE WHEN TABLE_TYPE = 'VIEW' THEN 1 ELSE 0 END       as IsView,
					''                                                    as Description,
					CASE WHEN TABLE_SCHEMA = 'dbo' THEN 1 ELSE 0 END      as IsDefaultSchema
				FROM
					INFORMATION_SCHEMA.TABLES s
					LEFT JOIN
						sys.tables t
					ON
						OBJECT_ID('[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') = t.object_id
				WHERE
					t.object_id IS NULL OR t.is_ms_shipped <> 1"
				: @"
				SELECT
					TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID,
					TABLE_CATALOG                                         as CatalogName,
					TABLE_SCHEMA                                          as SchemaName,
					TABLE_NAME                                            as TableName,
					CASE WHEN TABLE_TYPE = 'VIEW' THEN 1 ELSE 0 END       as IsView,
					ISNULL(CONVERT(varchar(8000), x.Value), '')           as Description,
					CASE WHEN TABLE_SCHEMA = 'dbo' THEN 1 ELSE 0 END      as IsDefaultSchema
				FROM
					INFORMATION_SCHEMA.TABLES s
					LEFT JOIN
						sys.tables t
					ON
						OBJECT_ID('[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') = t.object_id
					LEFT JOIN
						sys.extended_properties x
					ON
						OBJECT_ID('[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') = x.major_id AND
						x.minor_id = 0 AND 
						x.name = 'MS_Description'
				WHERE
					t.object_id IS NULL OR
					t.is_ms_shipped <> 1 AND
					(
						SELECT
							major_id
						FROM
							sys.extended_properties
						WHERE
							major_id = t.object_id AND
							minor_id = 0           AND
							class    = 1           AND
							name     = N'microsoft_database_tools_support'
					) IS NULL")
				.ToList();
		}

 protected override List<TableInfo> GetTables(DataConnection dataConnection)
 {
     return dataConnection.Query<TableInfo>(@"
         SELECT
             TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID,
             TABLE_CATALOG                                         as CatalogName,
             TABLE_SCHEMA                                          as SchemaName,
             TABLE_NAME                                            as TableName,
             CASE WHEN TABLE_TYPE = 'VIEW' THEN 1 ELSE 0 END       as IsView,
             CASE WHEN TABLE_SCHEMA = 'dbo' THEN 1 ELSE 0 END      as IsDefaultSchema
         FROM
             INFORMATION_SCHEMA.TABLES s")
         .ToList();
 }

		protected override List<ProcedureInfo> GetProcedures(DataConnection dataConnection)
		{
			return dataConnection.Query<ProcedureInfo>(@"
				SELECT
					SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME                as ProcedureID,
					SPECIFIC_CATALOG                                                              as CatalogName,
					SPECIFIC_SCHEMA                                                               as SchemaName,
					SPECIFIC_NAME                                                                 as ProcedureName,
					CASE WHEN ROUTINE_TYPE = 'FUNCTION'                         THEN 1 ELSE 0 END as IsFunction,
					CASE WHEN ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE' THEN 1 ELSE 0 END as IsTableFunction,
					CASE WHEN SPECIFIC_SCHEMA = 'dbo'                           THEN 1 ELSE 0 END as IsDefaultSchema
				FROM
					INFORMATION_SCHEMA.ROUTINES")
				.ToList();
		}

        protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
        {
            var cs = ((DbConnection)dataConnection.Connection).GetSchema("Columns");

            return _columns =
            (
                from c in cs.AsEnumerable()
                let schema = c.Field<string>("TABLE_SCHEMA")
                let table  = c.Field<string>("TABLE_NAME")
                let name   = c.Field<string>("COLUMN_NAME")
                join c2 in
                    dataConnection.Query(
                        rd => new
                        {
                            schema     = rd.GetString(0),
                            table      = rd.GetString(1),
                            name       = rd.GetString(2),
                            length     = Converter.ChangeTypeTo<int>(rd[3]),
                            scale      = Converter.ChangeTypeTo<int>(rd[4]),
                            isIdentity = rd.GetString(5) == "Y"
                        },@"
                        SELECT
                            TABSCHEMA,
                            TABNAME,
                            COLNAME,
                            LENGTH,
                            SCALE,
                            IDENTITY
                        FROM
                            SYSCAT.COLUMNS").ToList()
                on new { schema, table, name } equals new { c2.schema, c2.table, c2.name }
                select new ColumnInfo
                {
                    TableID      = c.Field<string>("TABLE_CATALOG") + "." + schema + "." + table,
                    Name         = name,
                    IsNullable   = c.Field<string>("IS_NULLABLE") == "YES",
                    Ordinal      = Converter.ChangeTypeTo<int>(c["ORDINAL_POSITION"]),
                    DataType     = c.Field<string>("DATA_TYPE_NAME"),
                    Length       = c2.length,
                    Precision    = c2.length,
                    Scale        = c2.scale,
                    IsIdentity   = c2.isIdentity,
                    SkipOnInsert = c2.isIdentity,
                    SkipOnUpdate = c2.isIdentity,
                    Description  = c.Field<string>("REMARKS"),
                }
            ).ToList();
        }

		protected override List<TableInfo> GetTables(DataConnection dataConnection)
		{
			return dataConnection.Query<TableInfo>(@"
				SELECT
					id                                                 as TableID,
					@db                                                as CatalogName,
					USER_NAME(uid)                                     as SchemaName,
					name                                               as TableName,
					CASE WHEN type = 'V' THEN 1 ELSE 0 END             as IsView,
					CASE WHEN USER_NAME(uid) = 'dbo' THEN 1 ELSE 0 END as IsDefaultSchema
				FROM
					sysobjects
				WHERE
					type IN ('U','V')",
				new { @db = dataConnection.Connection.Database})
				.ToList();
		}

        protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
        {
            var sql = @"
                SELECT
                    TABSCHEMA,
                    TABNAME,
                    COLNAME,
                    LENGTH,
                    SCALE,
                    NULLS,
                    IDENTITY,
                    COLNO,
                    TYPENAME,
                    REMARKS,
                    CODEPAGE
                FROM
                    SYSCAT.COLUMNS
                WHERE
                    " + GetSchemaFilter("TABSCHEMA");

            return _columns = dataConnection.Query(rd =>
                {
                    var typeName = rd.ToString(8);
                    var cp   = Converter.ChangeTypeTo<int>(rd[10]);

                         if (typeName == "CHARACTER" && cp == 0) typeName = "CHAR () FOR BIT DATA";
                    else if (typeName == "VARCHAR"   && cp == 0) typeName = "VARCHAR () FOR BIT DATA";

                    var ci = new ColumnInfo
                    {
                        TableID     = dataConnection.Connection.Database + "." + rd.GetString(0) + "." + rd.GetString(1),
                        Name        = rd.ToString(2),
                        IsNullable  = rd.ToString(5) == "Y",
                        IsIdentity  = rd.ToString(6) == "Y",
                        Ordinal     = Converter.ChangeTypeTo<int>(rd[7]),
                        DataType    = typeName,
                        Description = rd.ToString(9),
                    };

                    SetColumnParameters(ci, Converter.ChangeTypeTo<long?>(rd[3]), Converter.ChangeTypeTo<int?> (rd[4]));

                    return ci;
                },
                sql).ToList();
        }

		protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
		{
			return
				dataConnection.Query<PrimaryKeyInfo>(@"
					SELECT
						FKCOLS.OWNER || '.' || FKCOLS.TABLE_NAME as TableID,
						FKCOLS.CONSTRAINT_NAME                   as PrimaryKeyName,
						FKCOLS.COLUMN_NAME                       as ColumnName,
						FKCOLS.POSITION                          as Ordinal
					FROM
						ALL_CONS_COLUMNS FKCOLS,
						ALL_CONSTRAINTS FKCON
					WHERE
						FKCOLS.OWNER           = FKCON.OWNER and
						FKCOLS.TABLE_NAME      = FKCON.TABLE_NAME and
						FKCOLS.CONSTRAINT_NAME = FKCON.CONSTRAINT_NAME AND
						FKCON.CONSTRAINT_TYPE  = 'P'")
				.ToList();
		}