C# NpgsqlCommand.GetReader方法代码示例

        private IEnumerable<string> GetPrimaryKeys(String tablename)
        {
            if (string.IsNullOrEmpty(tablename))
            {
                yield break;
            }

            String getPKColumns =
                "select a.attname from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i  WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid  AND a.attrelid=ci.oid AND i.indisprimary AND ct.relname = :tablename";

            using (NpgsqlConnection metadataConn = _connection.Clone())
            {
                using (NpgsqlCommand c = new NpgsqlCommand(getPKColumns, metadataConn))
                {
                    c.Parameters.Add(new NpgsqlParameter("tablename", NpgsqlDbType.Text));
                    c.Parameters["tablename"].Value = tablename;

                    using (NpgsqlDataReader dr = c.GetReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess))
                    {
                        while (dr.Read())
                        {
                            yield return dr.GetString(0);
                        }
                    }
                }
            }
        }

        private Dictionary<string, Column> GetColumns()
        {
            StringBuilder sb = new StringBuilder();

            // the column index is used to find data.
            // any changes to the order of the columns needs to be reflected in struct Columns
            sb.Append(
                "SELECT a.attname AS column_name, a.attnotnull AS column_notnull, a.attrelid AS table_id, a.attnum AS column_num, d.adsrc as column_default");
            sb.Append(
                " FROM pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND (");
            bool first = true;
            for (int i = 0; i < CurrentDescription.NumFields; ++i)
            {
                if (CurrentDescription[i].TableOID != 0)
                {
                    if (!first)
                    {
                        sb.Append(" OR ");
                    }
                    sb.AppendFormat("(a.attrelid={0} AND a.attnum={1})", CurrentDescription[i].TableOID,
                                    CurrentDescription[i].ColumnAttributeNumber);
                    first = false;
                }
            }
            sb.Append(')');

            // if the loop ended without setting first to false, then there will be no results from the query
            if (first)
            {
                return null;
            }

            using (NpgsqlConnection connection = _connection.Clone())
            {
                using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
                {
                    using (NpgsqlDataReader reader = command.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)
                        )
                    {
                        Dictionary<string, Column> columnLookup = new Dictionary<string, Column>();
                        while (reader.Read())
                        {
                            Column column = new Column(reader);
                            columnLookup.Add(column.Key, column);
                        }
                        return columnLookup;
                    }
                }
            }
        }

        private KeyLookup GetKeys(Int32 tableOid)
        {
            string getKeys =
                "select a.attname, ci.relname, i.indisprimary from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisunique AND ct.oid = :tableOid order by ci.relname";

            KeyLookup lookup = new KeyLookup();

            using (NpgsqlConnection metadataConn = _connection.Clone())
            {
                NpgsqlCommand c = new NpgsqlCommand(getKeys, metadataConn);
                c.Parameters.Add(new NpgsqlParameter("tableOid", NpgsqlDbType.Integer)).Value = tableOid;

                using (NpgsqlDataReader dr = c.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
                {
                    string previousKeyName = null;
                    string possiblyUniqueColumn = null;
                    string columnName;
                    string currentKeyName;
                    // loop through adding any column that is primary to the primary key list
                    // add any column that is the only column for that key to the unique list
                    // unique here doesn't mean general unique constraint (with possibly multiple columns)
                    // it means all values in this single column must be unique
                    while (dr.Read())
                    {
                        columnName = dr.GetString(0);
                        currentKeyName = dr.GetString(1);
                        // if i.indisprimary
                        if (dr.GetBoolean(2))
                        {
                            // add column name as part of the primary key
                            lookup.primaryKey.Add(columnName);
                        }
                        if (currentKeyName != previousKeyName)
                        {
                            if (possiblyUniqueColumn != null)
                            {
                                lookup.uniqueColumns.Add(possiblyUniqueColumn);
                            }
                            possiblyUniqueColumn = columnName;
                        }
                        else
                        {
                            possiblyUniqueColumn = null;
                        }
                        previousKeyName = currentKeyName;
                    }
                    // if finished reading and have a possiblyUniqueColumn name that is
                    // not null, then it is the name of a unique column
                    if (possiblyUniqueColumn != null)
                    {
                        lookup.uniqueColumns.Add(possiblyUniqueColumn);
                    }
                    return lookup;
                }
            }
        }

        private Dictionary<long, Table> GetTablesFromOids(List<int> oids)
        {
            if (oids.Count == 0)
            {
                return new Dictionary<long, Table>(); //Empty collection is simpler than requiring tests for null;
            }

            // the column index is used to find data.
            // any changes to the order of the columns needs to be reflected in struct Tables
            StringBuilder sb =
                new StringBuilder(
                    "SELECT current_database(), nc.nspname, c.relname, c.oid FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND (c.relkind = 'r' OR c.relkind = 'v') AND c.oid IN (");
            bool first = true;
            foreach (int oid in oids)
            {
                if (!first)
                {
                    sb.Append(',');
                }
                sb.Append(oid);
                first = false;
            }
            sb.Append(')');

            using (NpgsqlConnection connection = _connection.Clone())
            {
                using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
                {
                    using (NpgsqlDataReader reader = command.GetReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)
                        )
                    {
                        Dictionary<long, Table> oidLookup = new Dictionary<long, Table>(oids.Count);
                        int columnCount = reader.FieldCount;
                        while (reader.Read())
                        {
                            Table t = new Table(reader);
                            oidLookup.Add(t.Id, t);
                        }
                        return oidLookup;
                    }
                }
            }
        }

        internal ForwardsOnlyDataReader GetReader(CommandBehavior cb)
        {
            try
            {
                CheckConnectionState();

                // reset any responses just before getting new ones
                Connector.Mediator.ResetResponses();

                // Set command timeout.
                m_Connector.Mediator.CommandTimeout = CommandTimeout;

                using (m_Connector.BlockNotificationThread())
                {
                    ForwardsOnlyDataReader reader;
                    if (parse == null)
                    {
                        reader = new ForwardsOnlyDataReader(m_Connector.QueryEnum(this), cb, this,
                                                            m_Connector.BlockNotificationThread(), false);
                        if (type == CommandType.StoredProcedure
                            && reader.FieldCount == 1
                            && reader.GetDataTypeName(0) == "refcursor")
                        {
                            // When a function returns a sole column of refcursor, transparently
                            // FETCH ALL from every such cursor and return those results.
                            StringBuilder sb = new StringBuilder();
                            while (reader.Read())
                            {
                                sb.Append("fetch all from \"").Append(reader.GetString(0)).Append("\";");
                            }
                            sb.Append(";"); // Just in case the list of cursors is empty.

                            //reader = new NpgsqlCommand(sb.ToString(), Connection).GetReader(reader._behavior);

                            // Passthrough the commandtimeout to the inner command, so user can also control its timeout.
                            // TODO: Check if there is a better way to handle that.

                            NpgsqlCommand c = new NpgsqlCommand(sb.ToString(), Connection);

                            c.CommandTimeout = this.CommandTimeout;

                            reader = c.GetReader(reader._behavior);

                        }
                    }
                    else
                    {
                        BindParameters();
                        reader = new ForwardsOnlyDataReader(m_Connector.ExecuteEnum(new NpgsqlExecute(bind.PortalName, 0)), cb, this,
                                                            m_Connector.BlockNotificationThread(), true);
                    }
                    return reader;
                }
            }
            catch (IOException ex)
            {
                throw ClearPoolAndCreateException(ex);
            }
        }