2 // Npgsql.NpgsqlDataReader.cs
5 // Francisco Jr. (fxjrlists@yahoo.com.br)
7 // Copyright (C) 2002 The Npgsql Development Team
8 // npgsql-general@gborg.postgresql.org
9 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
12 // This library is free software; you can redistribute it and/or
13 // modify it under the terms of the GNU Lesser General Public
14 // License as published by the Free Software Foundation; either
15 // version 2.1 of the License, or (at your option) any later version.
17 // This library is distributed in the hope that it will be useful,
18 // but WITHOUT ANY WARRANTY; without even the implied warranty of
19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
20 // Lesser General Public License for more details.
22 // You should have received a copy of the GNU Lesser General Public
23 // License along with this library; if not, write to the Free Software
24 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
28 using System
.Collections
;
36 /// Provides a means of reading a forward-only stream of rows from a PostgreSQL backend. This class cannot be inherited.
38 public sealed class NpgsqlDataReader
: IDataReader
, IEnumerable
40 private NpgsqlConnection _connection
;
41 private ArrayList _resultsets
;
42 private ArrayList _responses
;
43 private Int32 _rowIndex
;
44 private Int32 _resultsetIndex
;
45 private Int32 _recordsAffected
;
46 private NpgsqlResultSet _currentResultset
;
47 private DataTable _currentResultsetSchema
;
48 private CommandBehavior _behavior
;
49 private Boolean _isClosed
;
50 private NpgsqlCommand _command
;
53 // Logging related values
54 private static readonly String CLASSNAME
= "NpgsqlDataReader";
56 internal NpgsqlDataReader( ArrayList resultsets
, ArrayList responses
, CommandBehavior behavior
, NpgsqlCommand command
)
58 _resultsets
= resultsets
;
59 _responses
= responses
;
60 _connection
= command
.Connection
;
63 _recordsAffected
= -1;
65 // positioned before the first results.
66 // move to the first results
74 private Boolean
HaveResultSet()
76 return (_currentResultset
!= null);
79 private Boolean
HaveRow()
81 return (HaveResultSet() && _rowIndex
>= 0 && _rowIndex
< _currentResultset
.Count
);
84 private void CheckHaveResultSet()
86 if (! HaveResultSet())
88 throw new InvalidOperationException("Cannot read data. No result set.");
92 private void CheckHaveRow()
98 throw new InvalidOperationException("DataReader positioned before beginning of result set. Did you call Read()?");
100 else if (_rowIndex
>= _currentResultset
.Count
)
102 throw new InvalidOperationException("DataReader positioned beyond end of result set.");
108 /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
110 public void Dispose()
116 /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
118 protected void Dispose (bool disposing
)
120 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "Dispose");
128 /// Gets a value indicating the depth of nesting for the current row. Always returns zero.
134 NpgsqlEventLog
.LogPropertyGet(LogLevel
.Debug
, CLASSNAME
, "Depth");
140 /// Gets a value indicating whether the data reader is closed.
142 public Boolean IsClosed
146 NpgsqlEventLog
.LogPropertyGet(LogLevel
.Debug
, CLASSNAME
, "IsClosed");
152 /// Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
154 public Int32 RecordsAffected
158 NpgsqlEventLog
.LogPropertyGet(LogLevel
.Debug
, CLASSNAME
, "RecordsAffected");
159 return _recordsAffected
;
164 /// Indicates if NpgsqlDatareader has rows to be read.
167 public Boolean HasRows
171 return (HaveResultSet() ? _currentResultset
.Count
> 0 : false);
177 /// Closes the data reader object.
181 if ((_behavior
& CommandBehavior
.CloseConnection
) == CommandBehavior
.CloseConnection
)
187 if (this.ReaderClosed
!= null)
188 this.ReaderClosed(this, EventArgs
.Empty
);
192 /// Is raised whenever Close() is called.
194 public event EventHandler ReaderClosed
;
197 /// Advances the data reader to the next result, when multiple result sets were returned by the PostgreSQL backend.
199 /// <returns>True if the reader was advanced, otherwise false.</returns>
200 public Boolean
NextResult()
202 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "NextResult");
204 _currentResultset
= null;
205 while((_resultsetIndex
+ 1) < _resultsets
.Count
&& !HaveResultSet())
209 _currentResultset
= (NpgsqlResultSet
)_resultsets
[_resultsetIndex
];
211 if (!HaveResultSet())
213 String
[] _returnStringTokens
= ((String
)_responses
[_resultsetIndex
]).Split(null); // whitespace separator.
214 int responseAffectedRows
= 0;
218 responseAffectedRows
= Int32
.Parse(_returnStringTokens
[_returnStringTokens
.Length
- 1]);
220 catch (FormatException
)
222 responseAffectedRows
= -1;
225 if (responseAffectedRows
!= -1)
227 if (_recordsAffected
== -1)
229 _recordsAffected
= responseAffectedRows
;
233 _recordsAffected
+= responseAffectedRows
;
238 return HaveResultSet();
243 /// Advances the data reader to the next row.
245 /// <returns>True if the reader was advanced, otherwise false.</returns>
246 public Boolean
Read()
248 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "Read");
250 if (!HaveResultSet())
253 if (_rowIndex
< _currentResultset
.Count
)
256 return (_rowIndex
< _currentResultset
.Count
);
267 /// Returns a System.Data.DataTable that describes the column metadata of the DataReader.
269 public DataTable
GetSchemaTable()
272 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetSchemaTable");
274 if(_currentResultsetSchema
== null)
275 _currentResultsetSchema
= GetResultsetSchema();
277 return _currentResultsetSchema
;
281 /// Gets the number of columns in the current row.
283 public Int32 FieldCount
288 NpgsqlEventLog
.LogPropertyGet(LogLevel
.Debug
, CLASSNAME
, "FieldCount");
290 if (! HaveResultSet()) //Executed a non return rows query.
293 return _currentResultset
.RowDescription
.NumFields
;
301 /// Return the column name of the column at index <param name="Index"></param>.
303 public String
GetName(Int32 Index
)
305 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetName");
307 CheckHaveResultSet();
309 return _currentResultset
.RowDescription
[Index
].name
;
313 /// Return the data type OID of the column at index <param name="Index"></param>.
315 /// FIXME: Why this method returns String?
316 public String
GetDataTypeOID(Int32 Index
)
318 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetDataTypeName");
320 CheckHaveResultSet();
322 NpgsqlBackendTypeInfo TI
= GetTypeInfo(Index
);
324 return _currentResultset
.RowDescription
[Index
].type_oid
.ToString();
328 /// Return the data type name of the column at index <param name="Index"></param>.
330 public String
GetDataTypeName(Int32 Index
)
332 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetDataTypeName");
334 CheckHaveResultSet();
336 NpgsqlBackendTypeInfo TI
= GetTypeInfo(Index
);
340 return _currentResultset
.RowDescription
[Index
].type_oid
.ToString();
349 /// Return the data type of the column at index <param name="Index"></param>.
351 public Type
GetFieldType(Int32 Index
)
353 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetFieldType");
355 CheckHaveResultSet();
357 NpgsqlBackendTypeInfo TI
= GetTypeInfo(Index
);
361 return typeof(String
); //Default type is string.
370 /// Return the data DbType of the column at index <param name="Index"></param>.
372 public DbType
GetFieldDbType(Int32 Index
)
374 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetFieldType");
376 CheckHaveResultSet();
378 NpgsqlBackendTypeInfo TI
= GetTypeInfo(Index
);
382 return DbType
.String
;
387 return DbType
.String
;
392 /// Return the data NpgsqlDbType of the column at index <param name="Index"></param>.
394 public NpgsqlDbType
GetFieldNpgsqlDbType(Int32 Index
)
396 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetFieldType");
398 CheckHaveResultSet();
400 NpgsqlBackendTypeInfo TI
= GetTypeInfo(Index
);
404 return NpgsqlDbType
.Text
;
408 return TI
.NpgsqlDbType
;
415 /// Return the value of the column at index <param name="Index"></param>.
417 public Object
GetValue(Int32 Index
)
419 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetValue");
421 if (Index
< 0 || Index
>= _currentResultset
.RowDescription
.NumFields
)
423 throw new IndexOutOfRangeException("Column index out of range");
428 return ((NpgsqlAsciiRow
)_currentResultset
[_rowIndex
])[Index
];
432 /// Copy values from each column in the current row into <param name="Values"></param>.
434 /// <returns>The number of column values copied.</returns>
435 public Int32
GetValues(Object
[] Values
)
437 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetValues");
441 // Only the number of elements in the array are filled.
442 // It's also possible to pass an array with more that FieldCount elements.
443 Int32 maxColumnIndex
= (Values
.Length
< FieldCount
) ? Values
.Length
: FieldCount
;
445 for (Int32 i
= 0; i
< maxColumnIndex
; i
++)
447 Values
[i
] = GetValue(i
);
450 return maxColumnIndex
;
455 /// Return the column name of the column named <param name="Name"></param>.
457 public Int32
GetOrdinal(String Name
)
459 CheckHaveResultSet();
460 return _currentResultset
.RowDescription
.FieldIndex(Name
);
464 /// Gets the value of a column in its native format.
466 public Object
this [ Int32 i
]
470 NpgsqlEventLog
.LogIndexerGet(LogLevel
.Debug
, CLASSNAME
, i
);
476 /// Gets the value of a column in its native format.
478 public Object
this [ String name
]
482 NpgsqlEventLog
.LogIndexerGet(LogLevel
.Debug
, CLASSNAME
, name
);
483 Int32 fieldIndex
= _currentResultset
.RowDescription
.FieldIndex(name
);
484 if (fieldIndex
== -1)
485 throw new IndexOutOfRangeException("Field not found");
486 return GetValue(fieldIndex
);
491 /// Gets the value of a column as Boolean.
493 public Boolean
GetBoolean(Int32 i
)
495 // Should this be done using the GetValue directly and not by converting to String
496 // and parsing from there?
497 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetBoolean");
499 return (Boolean
) GetValue(i
);
503 /// Gets the value of a column as Byte. Not implemented.
505 public Byte
GetByte(Int32 i
)
507 throw new NotImplementedException();
511 /// Gets raw data from a column.
513 public Int64
GetBytes(Int32 i
, Int64 fieldOffset
, Byte
[] buffer
, Int32 bufferoffset
, Int32 length
)
518 result
= (Byte
[]) GetValue(i
);
521 return result
.Length
;
524 // We just support read all the field for while. So, any fieldOffset value other than 0 will not read
525 // anything and return 0.
527 if (fieldOffset
!= 0)
530 // [TODO] Implement blob support.
532 result
.CopyTo(buffer
, 0);
535 return result
.Length
;
540 /// Gets the value of a column as Char. Not implemented.
542 public Char
GetChar(Int32 i
)
544 throw new NotImplementedException();
548 /// Gets raw data from a column.
550 public Int64
GetChars(Int32 i
, Int64 fieldoffset
, Char
[] buffer
, Int32 bufferoffset
, Int32 length
)
558 str
.ToCharArray(bufferoffset
, length
).CopyTo(buffer
, 0);
559 return buffer
.GetLength(0);
563 /// Gets the value of a column converted to a Guid. Not implemented.
565 public Guid
GetGuid(Int32 i
)
567 throw new NotImplementedException();
571 /// Gets the value of a column as Int16.
573 public Int16
GetInt16(Int32 i
)
575 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetInt16");
577 return (Int16
) GetValue(i
);
581 /// Gets the value of a column as Int32.
583 public Int32
GetInt32(Int32 i
)
585 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetInt32");
587 return (Int32
) GetValue(i
);
591 /// Gets the value of a column as Int64.
593 public Int64
GetInt64(Int32 i
)
595 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetInt64");
597 return (Int64
) GetValue(i
);
601 /// Gets the value of a column as Single.
603 public Single
GetFloat(Int32 i
)
605 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetFloat");
607 return (Single
) GetValue(i
);
611 /// Gets the value of a column as Double.
613 public Double
GetDouble(Int32 i
)
615 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetDouble");
617 return (Double
) GetValue(i
);
621 /// Gets the value of a column as String.
623 public String
GetString(Int32 i
)
625 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetString");
627 return (String
) GetValue(i
);
631 /// Gets the value of a column as Decimal.
633 public Decimal
GetDecimal(Int32 i
)
635 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetDecimal");
637 return (Decimal
) GetValue(i
);
641 /// Gets the value of a column as DateTime.
643 public DateTime
GetDateTime(Int32 i
)
645 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetDateTime");
647 return (DateTime
) GetValue(i
);
653 public IDataReader
GetData(Int32 i
)
655 throw new NotImplementedException();
659 /// Report whether the value in a column is DBNull.
661 public Boolean
IsDBNull(Int32 i
)
663 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "IsDBNull");
665 return (GetValue(i
) == DBNull
.Value
);
668 internal NpgsqlBackendTypeInfo
GetTypeInfo(Int32 FieldIndex
)
670 return _currentResultset
.RowDescription
[FieldIndex
].type_info
;
673 private DataTable
GetResultsetSchema()
675 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "GetResultsetSchema");
676 DataTable result
= null;
678 NpgsqlRowDescription rd
= _currentResultset
.RowDescription
;
680 Int16 numFields
= rd
.NumFields
;
683 result
= new DataTable("SchemaTable");
685 result
.Columns
.Add ("ColumnName", typeof (string));
686 result
.Columns
.Add ("ColumnOrdinal", typeof (int));
687 result
.Columns
.Add ("ColumnSize", typeof (int));
688 result
.Columns
.Add ("NumericPrecision", typeof (int));
689 result
.Columns
.Add ("NumericScale", typeof (int));
690 result
.Columns
.Add ("IsUnique", typeof (bool));
691 result
.Columns
.Add ("IsKey", typeof (bool));
692 result
.Columns
.Add ("BaseCatalogName", typeof (string));
693 result
.Columns
.Add ("BaseColumnName", typeof (string));
694 result
.Columns
.Add ("BaseSchemaName", typeof (string));
695 result
.Columns
.Add ("BaseTableName", typeof (string));
696 result
.Columns
.Add ("DataType", typeof(Type
));
697 result
.Columns
.Add ("AllowDBNull", typeof (bool));
698 result
.Columns
.Add ("ProviderType", typeof (string));
699 result
.Columns
.Add ("IsAliased", typeof (bool));
700 result
.Columns
.Add ("IsExpression", typeof (bool));
701 result
.Columns
.Add ("IsIdentity", typeof (bool));
702 result
.Columns
.Add ("IsAutoIncrement", typeof (bool));
703 result
.Columns
.Add ("IsRowVersion", typeof (bool));
704 result
.Columns
.Add ("IsHidden", typeof (bool));
705 result
.Columns
.Add ("IsLong", typeof (bool));
706 result
.Columns
.Add ("IsReadOnly", typeof (bool));
708 if (_connection
.Connector
.BackendProtocolVersion
== ProtocolVersion
.Version2
)
710 FillSchemaTable_v2(result
);
712 else if (_connection
.Connector
.BackendProtocolVersion
== ProtocolVersion
.Version3
)
714 FillSchemaTable_v3(result
);
722 private void FillSchemaTable_v2(DataTable schema
)
724 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "FillSchemaTable_v2");
725 NpgsqlRowDescription rd
= _currentResultset
.RowDescription
;
726 ArrayList keyList
= null;
728 if ((_behavior
& CommandBehavior
.KeyInfo
) == CommandBehavior
.KeyInfo
)
730 keyList
= GetPrimaryKeys(GetTableNameFromQuery());
735 for (Int16 i
= 0; i
< rd
.NumFields
; i
++)
737 row
= schema
.NewRow();
739 row
["ColumnName"] = GetName(i
);
740 row
["ColumnOrdinal"] = i
+ 1;
741 if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && (rd
[i
].type_info
.Name
== "varchar" || rd
[i
].type_info
.Name
== "bpchar"))
742 row
["ColumnSize"] = rd
[i
].type_modifier
- 4;
743 else if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && (rd
[i
].type_info
.Name
== "bit" || rd
[i
].type_info
.Name
== "varbit"))
744 row
["ColumnSize"] = rd
[i
].type_modifier
;
746 row
["ColumnSize"] = (int) rd
[i
].type_size
;
747 if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && rd
[i
].type_info
.Name
== "numeric")
749 row
["NumericPrecision"] = ((rd
[i
].type_modifier
-4)>>16)&ushort.MaxValue
;
750 row
["NumericScale"] = (rd
[i
].type_modifier
-4)&ushort.MaxValue
;
754 row
["NumericPrecision"] = 0;
755 row
["NumericScale"] = 0;
757 row
["IsUnique"] = false;
758 row
["IsKey"] = IsKey(GetName(i
), keyList
);
759 row
["BaseCatalogName"] = "";
760 row
["BaseSchemaName"] = "";
761 row
["BaseTableName"] = "";
762 row
["BaseColumnName"] = GetName(i
);
763 row
["DataType"] = GetFieldType(i
);
764 row
["AllowDBNull"] = IsNullable(null, i
);
765 if (rd
[i
].type_info
!= null)
767 row
["ProviderType"] = rd
[i
].type_info
.Name
;
769 row
["IsAliased"] = false;
770 row
["IsExpression"] = false;
771 row
["IsIdentity"] = false;
772 row
["IsAutoIncrement"] = false;
773 row
["IsRowVersion"] = false;
774 row
["IsHidden"] = false;
775 row
["IsLong"] = false;
776 row
["IsReadOnly"] = false;
778 schema
.Rows
.Add(row
);
782 private void FillSchemaTable_v3(DataTable schema
)
784 NpgsqlEventLog
.LogMethodEnter(LogLevel
.Debug
, CLASSNAME
, "FillSchemaTable_v3");
785 NpgsqlRowDescription rd
= _currentResultset
.RowDescription
;
787 Hashtable oidTableLookup
= null;
788 KeyLookup keyLookup
= new KeyLookup();
789 Hashtable columnLookup
= null;
791 if ((_behavior
& CommandBehavior
.KeyInfo
) == CommandBehavior
.KeyInfo
)
793 ArrayList tableOids
= new ArrayList();
794 for(short i
=0; i
<rd
.NumFields
; ++i
)
796 if (rd
[i
].table_oid
!= 0 && !tableOids
.Contains(rd
[i
].table_oid
))
797 tableOids
.Add(rd
[i
].table_oid
);
799 oidTableLookup
= GetTablesFromOids(tableOids
);
801 if (oidTableLookup
!= null && oidTableLookup
.Count
== 1)
803 // only 1, but we can't index into the Hashtable
804 foreach(DictionaryEntry entry
in oidTableLookup
)
806 keyLookup
= GetKeys((Int32
)entry
.Key
);
810 columnLookup
= GetColumns();
814 for (Int16 i
= 0; i
< rd
.NumFields
; i
++)
816 row
= schema
.NewRow();
818 string baseColumnName
= GetBaseColumnName(columnLookup
, i
);
820 row
["ColumnName"] = GetName(i
);
821 row
["ColumnOrdinal"] = i
+ 1;
822 if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && (rd
[i
].type_info
.Name
== "varchar" || rd
[i
].type_info
.Name
== "bpchar"))
823 row
["ColumnSize"] = rd
[i
].type_modifier
- 4;
824 else if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && (rd
[i
].type_info
.Name
== "bit" || rd
[i
].type_info
.Name
== "varbit"))
825 row
["ColumnSize"] = rd
[i
].type_modifier
;
827 row
["ColumnSize"] = (int) rd
[i
].type_size
;
828 if (rd
[i
].type_modifier
!= -1 && rd
[i
].type_info
!= null && rd
[i
].type_info
.Name
== "numeric")
830 row
["NumericPrecision"] = ((rd
[i
].type_modifier
-4)>>16)&ushort.MaxValue
;
831 row
["NumericScale"] = (rd
[i
].type_modifier
-4)&ushort.MaxValue
;
835 row
["NumericPrecision"] = 0;
836 row
["NumericScale"] = 0;
838 row
["IsUnique"] = IsUnique(keyLookup
, baseColumnName
);
839 row
["IsKey"] = IsKey(keyLookup
, baseColumnName
);
840 if (rd
[i
].table_oid
!= 0 && oidTableLookup
!= null)
842 row
["BaseCatalogName"] = ((object[])oidTableLookup
[rd
[i
].table_oid
])[Tables
.table_catalog
];
843 row
["BaseSchemaName"] = ((object[])oidTableLookup
[rd
[i
].table_oid
])[Tables
.table_schema
];
844 row
["BaseTableName"] = ((object[])oidTableLookup
[rd
[i
].table_oid
])[Tables
.table_name
];
848 row
["BaseCatalogName"] = "";
849 row
["BaseSchemaName"] = "";
850 row
["BaseTableName"] = "";
852 row
["BaseColumnName"] = baseColumnName
;
853 row
["DataType"] = GetFieldType(i
);
854 row
["AllowDBNull"] = IsNullable(columnLookup
, i
);
855 if (rd
[i
].type_info
!= null)
857 row
["ProviderType"] = rd
[i
].type_info
.Name
;
859 row
["IsAliased"] = string.CompareOrdinal((string)row
["ColumnName"], baseColumnName
) != 0;
860 row
["IsExpression"] = false;
861 row
["IsIdentity"] = false;
862 row
["IsAutoIncrement"] = IsAutoIncrement(columnLookup
, i
);
863 row
["IsRowVersion"] = false;
864 row
["IsHidden"] = false;
865 row
["IsLong"] = false;
866 row
["IsReadOnly"] = false;
868 schema
.Rows
.Add(row
);
873 private Boolean
IsKey(String ColumnName
, ArrayList ListOfKeys
)
875 if (ListOfKeys
== null || ListOfKeys
.Count
== 0)
878 foreach(String s
in ListOfKeys
)
888 private ArrayList
GetPrimaryKeys(String tablename
)
891 if (tablename
== String
.Empty
)
894 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";
896 ArrayList result
= new ArrayList();
897 NpgsqlConnection metadataConn
= _connection
.Clone();
899 NpgsqlCommand c
= new NpgsqlCommand(getPKColumns
, metadataConn
);
900 c
.Parameters
.Add(new NpgsqlParameter("tablename", NpgsqlDbType
.Text
));
901 c
.Parameters
["tablename"].Value
= tablename
;
904 NpgsqlDataReader dr
= c
.ExecuteReader();
911 metadataConn
.Close();
916 private bool IsKey(KeyLookup keyLookup
, string fieldName
)
918 if (keyLookup
.primaryKey
== null || keyLookup
.primaryKey
.Count
== 0)
921 for (int i
=0; i
<keyLookup
.primaryKey
.Count
; ++i
)
923 if (fieldName
== (String
)keyLookup
.primaryKey
[i
])
930 private bool IsUnique(KeyLookup keyLookup
, string fieldName
)
932 if (keyLookup
.uniqueColumns
== null || keyLookup
.uniqueColumns
.Count
== 0)
935 for (int i
=0; i
<keyLookup
.uniqueColumns
.Count
; ++i
)
937 if (fieldName
== (String
)keyLookup
.uniqueColumns
[i
])
944 private struct KeyLookup
947 /// Contains the column names as the keys
949 public ArrayList primaryKey
;
951 /// Contains all unique columns
953 public ArrayList uniqueColumns
;
956 private KeyLookup
GetKeys(Int32 tableOid
)
959 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";
961 KeyLookup lookup
= new KeyLookup();
962 lookup
.primaryKey
= new ArrayList();
963 lookup
.uniqueColumns
= new ArrayList();
965 using (NpgsqlConnection metadataConn
= _connection
.Clone())
967 NpgsqlCommand c
= new NpgsqlCommand(getKeys
, metadataConn
);
968 c
.Parameters
.Add(new NpgsqlParameter("tableOid", NpgsqlDbType
.Integer
)).Value
= tableOid
;
970 using (NpgsqlDataReader dr
= c
.ExecuteReader())
972 string previousKeyName
= null;
973 string possiblyUniqueColumn
= null;
975 string currentKeyName
;
976 // loop through adding any column that is primary to the primary key list
977 // add any column that is the only column for that key to the unique list
978 // unique here doesn't mean general unique constraint (with possibly multiple columns)
979 // it means all values in this single column must be unique
983 columnName
= dr
.GetString(0);
984 currentKeyName
= dr
.GetString(1);
986 if (dr
.GetBoolean(2))
988 // add column name as part of the primary key
989 lookup
.primaryKey
.Add(columnName
);
991 if (currentKeyName
!= previousKeyName
)
993 if (possiblyUniqueColumn
!= null)
995 lookup
.uniqueColumns
.Add(possiblyUniqueColumn
);
997 possiblyUniqueColumn
= columnName
;
1001 possiblyUniqueColumn
= null;
1003 previousKeyName
= currentKeyName
;
1005 // if finished reading and have a possiblyUniqueColumn name that is
1006 // not null, then it is the name of a unique column
1007 if (possiblyUniqueColumn
!= null)
1008 lookup
.uniqueColumns
.Add(possiblyUniqueColumn
);
1015 private Boolean
IsNullable(Hashtable columnLookup
, Int32 FieldIndex
)
1017 if (columnLookup
== null || _currentResultset
.RowDescription
[FieldIndex
].table_oid
== 0)
1020 string lookupKey
= _currentResultset
.RowDescription
[FieldIndex
].table_oid
.ToString() + "," + _currentResultset
.RowDescription
[FieldIndex
].column_attribute_number
;
1021 object[] row
= (object[])columnLookup
[lookupKey
];
1023 return !(bool)row
[Columns
.column_notnull
];
1028 private string GetBaseColumnName(Hashtable columnLookup
, Int32 FieldIndex
)
1030 if (columnLookup
== null || _currentResultset
.RowDescription
[FieldIndex
].table_oid
== 0)
1031 return GetName(FieldIndex
);
1033 string lookupKey
= _currentResultset
.RowDescription
[FieldIndex
].table_oid
.ToString() + "," + _currentResultset
.RowDescription
[FieldIndex
].column_attribute_number
;
1034 object[] row
= (object[])columnLookup
[lookupKey
];
1036 return (string)row
[Columns
.column_name
];
1038 return GetName(FieldIndex
);
1041 private bool IsAutoIncrement(Hashtable columnLookup
, Int32 FieldIndex
)
1043 if (columnLookup
== null || _currentResultset
.RowDescription
[FieldIndex
].table_oid
== 0)
1046 string lookupKey
= _currentResultset
.RowDescription
[FieldIndex
].table_oid
.ToString() + "," + _currentResultset
.RowDescription
[FieldIndex
].column_attribute_number
;
1047 object[] row
= (object[])columnLookup
[lookupKey
];
1049 return row
[Columns
.column_default
].ToString().StartsWith("nextval(");
1056 /// This methods parses the command text and tries to get the tablename
1059 private String
GetTableNameFromQuery()
1061 Int32 fromClauseIndex
= _command
.CommandText
.ToLower().IndexOf("from");
1063 String tableName
= _command
.CommandText
.Substring(fromClauseIndex
+ 4).Trim();
1065 if (tableName
== String
.Empty
)
1066 return String
.Empty
;
1068 /*if (tableName.EndsWith("."));
1069 return String.Empty;
1071 foreach (Char c
in tableName
.Substring (0, tableName
.Length
- 1))
1072 if (!Char
.IsLetterOrDigit (c
) && c
!= '_' && c
!= '.')
1073 return String
.Empty
;
1080 private struct Tables
1082 public const int table_catalog
= 0;
1083 public const int table_schema
= 1;
1084 public const int table_name
= 2;
1085 public const int table_id
= 3;
1088 private Hashtable
GetTablesFromOids(ArrayList oids
)
1090 if (oids
.Count
== 0)
1093 StringBuilder sb
= new StringBuilder();
1095 // the column index is used to find data.
1096 // any changes to the order of the columns needs to be reflected in struct Tables
1097 sb
.Append("SELECT current_database() AS table_catalog, nc.nspname AS table_schema, c.relname AS table_name, c.oid as table_id");
1098 sb
.Append(" FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND (c.relkind = 'r' OR c.relkind = 'v') AND c.oid IN (");
1100 foreach(int oid
in oids
)
1109 using (NpgsqlConnection connection
= _connection
.Clone())
1110 using (NpgsqlCommand command
= new NpgsqlCommand(sb
.ToString(), connection
))
1111 using (NpgsqlDataReader reader
= command
.ExecuteReader())
1113 Hashtable oidLookup
= new Hashtable();
1114 int columnCount
= reader
.FieldCount
;
1115 while (reader
.Read())
1117 object[] values
= new object[columnCount
];
1118 reader
.GetValues(values
);
1119 oidLookup
[Convert
.ToInt32(reader
[Tables
.table_id
])] = values
;
1125 private struct Columns
1127 public const int column_name
= 0;
1128 public const int column_notnull
= 1;
1129 public const int table_id
= 2;
1130 public const int column_num
= 3;
1131 public const int column_default
= 4;
1134 private Hashtable
GetColumns()
1136 StringBuilder sb
= new StringBuilder();
1138 // the column index is used to find data.
1139 // any changes to the order of the columns needs to be reflected in struct Columns
1140 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");
1141 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 (");
1143 for(int i
=0; i
<_currentResultset
.RowDescription
.NumFields
; ++i
)
1145 if (_currentResultset
.RowDescription
[i
].table_oid
!= 0)
1149 sb
.AppendFormat("(a.attrelid={0} AND a.attnum={1})", _currentResultset
.RowDescription
[i
].table_oid
, _currentResultset
.RowDescription
[i
].column_attribute_number
);
1155 // if the loop ended without setting first to false, then there will be no results from the query
1159 using (NpgsqlConnection connection
= _connection
.Clone())
1160 using (NpgsqlCommand command
= new NpgsqlCommand(sb
.ToString(), connection
))
1161 using (NpgsqlDataReader reader
= command
.ExecuteReader())
1163 Hashtable columnLookup
= new Hashtable();
1164 int columnCount
= reader
.FieldCount
;
1165 while(reader
.Read())
1167 object[] values
= new object[columnCount
];
1168 reader
.GetValues(values
);
1169 columnLookup
[reader
[Columns
.table_id
].ToString() + "," + reader
[Columns
.column_num
].ToString()] = values
;
1171 return columnLookup
;
1175 IEnumerator IEnumerable
.GetEnumerator ()
1177 return new System
.Data
.Common
.DbEnumerator (this);