Class NpgsqlConnection
This class represents a connection to a PostgreSQL server.
Inheritance
Implements
Namespace: Npgsql
Assembly: Npgsql.dll
Syntax
public sealed class NpgsqlConnection : DbConnection, ICloneable
Constructors
NpgsqlConnection()
Initializes a new instance of the NpgsqlConnection class.
Declaration
public NpgsqlConnection()
NpgsqlConnection(String)
Initializes a new instance of NpgsqlConnection with the given connection string.
Declaration
public NpgsqlConnection(string connectionString)
Parameters
Type | Name | Description |
---|---|---|
System.String | connectionString | The connection used to open the PostgreSQL database. |
Fields
DefaultPort
The default TCP/IP port for PostgreSQL.
Declaration
public const int DefaultPort = null
Field Value
Type | Description |
---|---|
System.Int32 |
Properties
CommandTimeout
Gets the time to wait while trying to execute a command before terminating the attempt and generating an error.
Declaration
public int CommandTimeout { get; }
Property Value
Type | Description |
---|---|
System.Int32 | The time (in seconds) to wait for a command to complete. The default value is 20 seconds. |
ConnectionString
Gets or sets the string used to connect to a PostgreSQL database. See the manual for details.
Declaration
public override string ConnectionString { get; set; }
Property Value
Type | Description |
---|---|
System.String | The connection string that includes the server name, the database name, and other parameters needed to establish the initial connection. The default value is an empty string. |
ConnectionTimeout
Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.
Declaration
public override int ConnectionTimeout { get; }
Property Value
Type | Description |
---|---|
System.Int32 | The time (in seconds) to wait for a connection to open. The default value is 15 seconds. |
Database
Gets the name of the current database or the database to be used after a connection is opened.
Declaration
public override string Database { get; }
Property Value
Type | Description |
---|---|
System.String | The name of the current database or the name of the database to be used after a connection is opened. The default value is the empty string. |
DataSource
Gets the string identifying the database server (host and port)
Declaration
public override string DataSource { get; }
Property Value
Type | Description |
---|---|
System.String |
DbProviderFactory
DB provider factory.
Declaration
protected override DbProviderFactory DbProviderFactory { get; }
Property Value
Type | Description |
---|---|
DbProviderFactory |
FullState
Gets the current state of the connection.
Declaration
public ConnectionState FullState { get; }
Property Value
Type | Description |
---|---|
ConnectionState | A bitwise combination of the |
Host
Backend server host name.
Declaration
public string Host { get; }
Property Value
Type | Description |
---|---|
System.String |
IntegratedSecurity
Whether to use Windows integrated security to log in.
Declaration
public bool IntegratedSecurity { get; }
Property Value
Type | Description |
---|---|
System.Boolean |
Port
Backend server port.
Declaration
public int Port { get; }
Property Value
Type | Description |
---|---|
System.Int32 |
PostgreSqlVersion
Version of the PostgreSQL backend. This can only be called when there is an active connection.
Declaration
public Version PostgreSqlVersion { get; }
Property Value
Type | Description |
---|---|
Version |
ProcessID
Process id of backend server. This can only be called when there is an active connection.
Declaration
public int ProcessID { get; }
Property Value
Type | Description |
---|---|
System.Int32 |
ProvideClientCertificatesCallback
Selects the local Secure Sockets Layer (SSL) certificate used for authentication.
Declaration
public ProvideClientCertificatesCallback ProvideClientCertificatesCallback { get; set; }
Property Value
Type | Description |
---|---|
ProvideClientCertificatesCallback |
Remarks
ServerVersion
PostgreSQL server version.
Declaration
public override string ServerVersion { get; }
Property Value
Type | Description |
---|---|
System.String |
State
Gets whether the current state of the connection is Open or Closed
Declaration
public override ConnectionState State { get; }
Property Value
Type | Description |
---|---|
ConnectionState | ConnectionState.Open, ConnectionState.Closed or ConnectionState.Connecting |
SupportsEStringPrefix
Report whether the backend understands the string literal E prefix (>= 8.1).
Declaration
public bool SupportsEStringPrefix { get; }
Property Value
Type | Description |
---|---|
System.Boolean |
UseConformantStrings
Report whether the backend is expecting standard conformant strings. In version 8.1, Postgres began reporting this value (false), but did not actually support standard conformant strings. In version 8.2, Postgres began supporting standard conformant strings, but defaulted this flag to false. As of version 9.1, this flag defaults to true.
Declaration
public bool UseConformantStrings { get; }
Property Value
Type | Description |
---|---|
System.Boolean |
UserCertificateValidationCallback
Verifies the remote Secure Sockets Layer (SSL) certificate used for authentication. Ignored if TrustServerCertificate is set.
Declaration
public RemoteCertificateValidationCallback UserCertificateValidationCallback { get; set; }
Property Value
Type | Description |
---|---|
RemoteCertificateValidationCallback |
Remarks
UserName
User name.
Declaration
public string UserName { get; }
Property Value
Type | Description |
---|---|
System.String |
UseSslStream
If true, the connection will attempt to use SslStream instead of an internal TlsClientStream.
Declaration
public bool UseSslStream { get; }
Property Value
Type | Description |
---|---|
System.Boolean |
Methods
BeginBinaryExport(String)
Begins a binary COPY TO STDOUT operation, a high-performance data export mechanism from a PostgreSQL table.
Declaration
public NpgsqlBinaryExporter BeginBinaryExport(string copyToCommand)
Parameters
Type | Name | Description |
---|---|---|
System.String | copyToCommand | A COPY TO STDOUT SQL command |
Returns
Type | Description |
---|---|
NpgsqlBinaryExporter | A NpgsqlBinaryExporter which can be used to read rows and columns |
Remarks
BeginBinaryImport(String)
Begins a binary COPY FROM STDIN operation, a high-performance data import mechanism to a PostgreSQL table.
Declaration
public NpgsqlBinaryImporter BeginBinaryImport(string copyFromCommand)
Parameters
Type | Name | Description |
---|---|---|
System.String | copyFromCommand | A COPY FROM STDIN SQL command |
Returns
Type | Description |
---|---|
NpgsqlBinaryImporter | A NpgsqlBinaryImporter which can be used to write rows and columns |
Remarks
BeginDbTransaction(Data.IsolationLevel)
Begins a database transaction with the specified isolation level.
Declaration
protected override DbTransaction BeginDbTransaction(Data.IsolationLevel isolationLevel)
Parameters
Type | Name | Description |
---|---|---|
System.Data.IsolationLevel | isolationLevel | The |
Returns
Type | Description |
---|---|
DbTransaction | An |
Remarks
Currently the IsolationLevel ReadCommitted and Serializable are supported by the PostgreSQL backend. There's no support for nested transactions.
BeginRawBinaryCopy(String)
Begins a raw binary COPY operation (TO STDOUT or FROM STDIN), a high-performance data export/import mechanism to a PostgreSQL table. Note that unlike the other COPY API methods, BeginRawBinaryCopy(String) doesn't implement any encoding/decoding and is unsuitable for structured import/export operation. It is useful mainly for exporting a table as an opaque blob, for the purpose of importing it back later.
Declaration
public NpgsqlRawCopyStream BeginRawBinaryCopy(string copyCommand)
Parameters
Type | Name | Description |
---|---|---|
System.String | copyCommand | A COPY TO STDOUT or COPY FROM STDIN SQL command |
Returns
Type | Description |
---|---|
NpgsqlRawCopyStream | A NpgsqlRawCopyStream that can be used to read or write raw binary data. |
Remarks
BeginTextExport(String)
Begins a textual COPY TO STDOUT operation, a data export mechanism from a PostgreSQL table.
It is the user's responsibility to parse the textual input according to the format specified
in copyToCommand
.
Declaration
public TextReader BeginTextExport(string copyToCommand)
Parameters
Type | Name | Description |
---|---|---|
System.String | copyToCommand | A COPY TO STDOUT SQL command |
Returns
Type | Description |
---|---|
TextReader | A TextReader that can be used to read textual data. |
Remarks
BeginTextImport(String)
Begins a textual COPY FROM STDIN operation, a data import mechanism to a PostgreSQL table.
It is the user's responsibility to send the textual input according to the format specified
in copyFromCommand
.
Declaration
public TextWriter BeginTextImport(string copyFromCommand)
Parameters
Type | Name | Description |
---|---|---|
System.String | copyFromCommand | A COPY FROM STDIN SQL command |
Returns
Type | Description |
---|---|
TextWriter | A TextWriter that can be used to send textual data. |
Remarks
BeginTransaction()
Begins a database transaction.
Declaration
public NpgsqlTransaction BeginTransaction()
Returns
Type | Description |
---|---|
NpgsqlTransaction | A NpgsqlTransaction object representing the new transaction. |
Remarks
Currently there's no support for nested transactions. Transactions created by this method will have Read Committed isolation level.
BeginTransaction(Data.IsolationLevel)
Begins a database transaction with the specified isolation level.
Declaration
public NpgsqlTransaction BeginTransaction(Data.IsolationLevel level)
Parameters
Type | Name | Description |
---|---|---|
System.Data.IsolationLevel | level | The |
Returns
Type | Description |
---|---|
NpgsqlTransaction | A NpgsqlTransaction object representing the new transaction. |
Remarks
Currently the IsolationLevel ReadCommitted and Serializable are supported by the PostgreSQL backend. There's no support for nested transactions.
ChangeDatabase(String)
This method changes the current database by disconnecting from the actual database and connecting to the specified.
Declaration
public override void ChangeDatabase(string dbName)
Parameters
Type | Name | Description |
---|---|---|
System.String | dbName | The name of the database to use in place of the current database. |
ClearAllPools()
Clear all connection pools.
Declaration
public static void ClearAllPools()
ClearPool(NpgsqlConnection)
Clear connection pool.
Declaration
public static void ClearPool(NpgsqlConnection connection)
Parameters
Type | Name | Description |
---|---|---|
NpgsqlConnection | connection |
CloneWith(String)
Clones this connection, replacing its connection string with the given one. This allows creating a new connection with the same security information (password, SSL callbacks) while changing other connection parameters (e.g. database or pooling)
Declaration
public NpgsqlConnection CloneWith(string connectionString)
Parameters
Type | Name | Description |
---|---|---|
System.String | connectionString |
Returns
Type | Description |
---|---|
NpgsqlConnection |
Close()
releases the connection to the database. If the connection is pooled, it will be made available for re-use. If it is non-pooled, the actual connection will be shutdown.
Declaration
public override void Close()
CreateCommand()
Creates and returns a NpgsqlCommand object associated with the NpgsqlConnection.
Declaration
public NpgsqlCommand CreateCommand()
Returns
Type | Description |
---|---|
NpgsqlCommand | A NpgsqlCommand object. |
CreateDbCommand()
Creates and returns a
Declaration
protected override DbCommand CreateDbCommand()
Returns
Type | Description |
---|---|
DbCommand | A |
Dispose(Boolean)
Releases all resources used by the NpgsqlConnection.
Declaration
protected override void Dispose(bool disposing)
Parameters
Type | Name | Description |
---|---|---|
System.Boolean | disposing | true when called from Dispose(); false when being called from the finalizer. |
EnlistTransaction(Transaction)
Enlist transation.
Declaration
public override void EnlistTransaction(Transaction transaction)
Parameters
Type | Name | Description |
---|---|---|
Transaction | transaction |
GetSchema()
Returns the supported collections
Declaration
public override DataTable GetSchema()
Returns
Type | Description |
---|---|
DataTable |
GetSchema(String)
Returns the schema collection specified by the collection name.
Declaration
public override DataTable GetSchema(string collectionName)
Parameters
Type | Name | Description |
---|---|---|
System.String | collectionName | The collection name. |
Returns
Type | Description |
---|---|
DataTable | The collection specified. |
GetSchema(String, String[])
Returns the schema collection specified by the collection name filtered by the restrictions.
Declaration
public override DataTable GetSchema(string collectionName, string[] restrictions)
Parameters
Type | Name | Description |
---|---|---|
System.String | collectionName | The collection name. |
System.String[] | restrictions | The restriction values to filter the results. A description of the restrictions is contained in the Restrictions collection. |
Returns
Type | Description |
---|---|
DataTable | The collection specified. |
MapComposite<T>(String, INpgsqlNameTranslator)
Maps a CLR type to a PostgreSQL composite type for use with this connection.
Declaration
public void MapComposite<T>(string pgName = null, INpgsqlNameTranslator nameTranslator = null)where T : new ()
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
T | The .NET type to be mapped |
Remarks
CLR fields and properties by string to PostgreSQL enum labels.
The translation strategy can be controlled by the nameTranslator
parameter,
which defaults to NpgsqlSnakeCaseNameTranslator.
You can also use the PgNameAttribute on your members to manually specify a PostgreSQL enum label.
If there is a discrepancy between the .NET and database labels while a composite is read or written,
an exception will be raised.
Can only be invoked on an open connection; if the connection is closed the mapping is lost.
To avoid mapping the type for each connection, use the MapCompositeGlobally<T>(String, INpgsqlNameTranslator) method.
MapCompositeGlobally<T>(String, INpgsqlNameTranslator)
Maps a CLR type to a PostgreSQL composite type for use with all connections created from now on. Existing connections aren't affected.
Declaration
public static void MapCompositeGlobally<T>(string pgName = null, INpgsqlNameTranslator nameTranslator = null)where T : new ()
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
T | The .NET type to be mapped |
Remarks
CLR fields and properties by string to PostgreSQL enum labels.
The translation strategy can be controlled by the nameTranslator
parameter,
which defaults to NpgsqlSnakeCaseNameTranslator.
You can also use the PgNameAttribute on your members to manually specify a PostgreSQL enum label.
If there is a discrepancy between the .NET and database labels while a composite is read or written,
an exception will be raised.
To map the type for a specific connection, use the MapEnum<TEnum>(String, INpgsqlNameTranslator) method.
MapEnum<TEnum>(String, INpgsqlNameTranslator)
Maps a CLR enum to a PostgreSQL enum type for use with this connection.
Declaration
public void MapEnum<TEnum>(string pgName = null, INpgsqlNameTranslator nameTranslator = null)where TEnum : struct
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
TEnum | The .NET enum type to be mapped |
Remarks
CLR enum labels are mapped by name to PostgreSQL enum labels.
The translation strategy can be controlled by the nameTranslator
parameter,
which defaults to NpgsqlSnakeCaseNameTranslator.
You can also use the PgNameAttribute on your enum fields to manually specify a PostgreSQL enum label.
If there is a discrepancy between the .NET and database labels while an enum is read or written,
an exception will be raised.
Can only be invoked on an open connection; if the connection is closed the mapping is lost.
To avoid mapping the type for each connection, use the MapEnumGlobally<TEnum>(String, INpgsqlNameTranslator) method.
MapEnumGlobally<TEnum>(String, INpgsqlNameTranslator)
Maps a CLR enum to a PostgreSQL enum type for use with all connections created from now on. Existing connections aren't affected.
Declaration
public static void MapEnumGlobally<TEnum>(string pgName = null, INpgsqlNameTranslator nameTranslator = null)where TEnum : struct
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
TEnum | The .NET enum type to be mapped |
Remarks
CLR enum labels are mapped by name to PostgreSQL enum labels.
The translation strategy can be controlled by the nameTranslator
parameter,
which defaults to NpgsqlSnakeCaseNameTranslator.
You can also use the PgNameAttribute on your enum fields to manually specify a PostgreSQL enum label.
If there is a discrepancy between the .NET and database labels while an enum is read or written,
an exception will be raised.
To map the type for a specific connection, use the MapEnum<TEnum>(String, INpgsqlNameTranslator) method.
Open()
Opens a database connection with the property settings specified by the ConnectionString.
Declaration
public override void Open()
OpenAsync(CancellationToken)
This is the asynchronous version of Open().
Declaration
public override Task OpenAsync(CancellationToken cancellationToken)
Parameters
Type | Name | Description |
---|---|---|
CancellationToken | cancellationToken | The cancellation instruction. |
Returns
Type | Description |
---|---|
Task | A task representing the asynchronous operation. |
Remarks
Do not invoke other methods and properties of the NpgsqlConnection object until the returned Task is complete.
ReloadTypes()
Flushes the type cache for this connection's connection string and reloads the types for this connection only.
Declaration
public void ReloadTypes()
UnmapCompositeGlobally<T>(String, INpgsqlNameTranslator)
Removes a previous global enum mapping.
Declaration
public static void UnmapCompositeGlobally<T>(string pgName, INpgsqlNameTranslator nameTranslator = null)where T : new ()
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
T |
UnmapEnumGlobally<TEnum>(String, INpgsqlNameTranslator)
Removes a previous global enum mapping.
Declaration
public static void UnmapEnumGlobally<TEnum>(string pgName = null, INpgsqlNameTranslator nameTranslator = null)where TEnum : struct
Parameters
Type | Name | Description |
---|---|---|
System.String | pgName | A PostgreSQL type name for the corresponding enum type in the database.
If null, the name translator given in |
INpgsqlNameTranslator | nameTranslator | A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class). Defaults to NpgsqlSnakeCaseNameTranslator |
Type Parameters
Name | Description |
---|---|
TEnum |
UnprepareAll()
Unprepares all prepared statements on this connection.
Declaration
public void UnprepareAll()
Wait()
Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and exits immediately. The asynchronous message is delivered via the normal events (Notification, Notice).
Declaration
public void Wait()
Wait(Int32)
Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and exits immediately. The asynchronous message is delivered via the normal events (Notification, Notice).
Declaration
public bool Wait(int timeout)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | timeout | The time-out value, in milliseconds, passed to |
Returns
Type | Description |
---|---|
System.Boolean | true if an asynchronous message was received, false if timed out. |
Wait(TimeSpan)
Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and exits immediately. The asynchronous message is delivered via the normal events (Notification, Notice).
Declaration
public bool Wait(TimeSpan timeout)
Parameters
Type | Name | Description |
---|---|---|
TimeSpan | timeout | The time-out value is passed to |
Returns
Type | Description |
---|---|
System.Boolean | true if an asynchronous message was received, false if timed out. |
WaitAsync()
Waits asynchronously until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and exits immediately. The asynchronous message is delivered via the normal events (Notification, Notice).
Declaration
public Task WaitAsync()
Returns
Type | Description |
---|---|
Task |
WaitAsync(CancellationToken)
Waits asynchronously until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and exits immediately. The asynchronous message is delivered via the normal events (Notification, Notice). CancelationToken can not cancel wait operation if underlying NetworkStream does not support it (see https://stackoverflow.com/questions/12421989/networkstream-readasync-with-a-cancellation-token-never-cancels ).
Declaration
public Task WaitAsync(CancellationToken cancellationToken)
Parameters
Type | Name | Description |
---|---|---|
CancellationToken | cancellationToken |
Returns
Type | Description |
---|---|
Task |
Events
Notice
Occurs on NoticeResponses from the PostgreSQL backend.
Declaration
public event NoticeEventHandler Notice
Event Type
Type | Description |
---|---|
NoticeEventHandler |
Notification
Occurs on NotificationResponses from the PostgreSQL backend.
Declaration
public event NotificationEventHandler Notification
Event Type
Type | Description |
---|---|
NotificationEventHandler |