using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Net.Security;
using System.Net.Sockets;
using System.Runtime.CompilerServices;
using System.Security.Cryptography.X509Certificates;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using JetBrains.Annotations;
using Npgsql.Logging;
using Npgsql.NameTranslation;
using Npgsql.TypeMapping;
using Npgsql.Util;
using NpgsqlTypes;
using IsolationLevel = System.Data.IsolationLevel;
namespace Npgsql
{
///
/// This class represents a connection to a PostgreSQL server.
///
// ReSharper disable once RedundantNameQualifier
[System.ComponentModel.DesignerCategory("")]
public sealed class NpgsqlConnection : DbConnection, ICloneable
{
#region Fields
// Set this when disposed is called.
bool _disposed;
///
/// The connection string, without the password after open (unless Persist Security Info=true)
///
string _userFacingConnectionString = string.Empty;
///
/// The original connection string provided by the user, including the password.
///
string _connectionString = string.Empty;
internal string OriginalConnectionString => _connectionString;
///
/// The connector object connected to the backend.
///
internal NpgsqlConnector? Connector;
///
/// The parsed connection string set by the user
///
internal NpgsqlConnectionStringBuilder Settings { get; private set; } = DefaultSettings;
static readonly NpgsqlConnectionStringBuilder DefaultSettings = new NpgsqlConnectionStringBuilder();
ConnectorPool? _pool;
bool _wasBroken;
internal Transaction? EnlistedTransaction { get; set; }
///
/// The global type mapper, which contains defaults used by all new connections.
/// Modify mappings on this mapper to affect your entire application.
///
public static INpgsqlTypeMapper GlobalTypeMapper => TypeMapping.GlobalTypeMapper.Instance;
///
/// The connection-specific type mapper - all modifications affect this connection only,
/// and are lost when it is closed.
///
public INpgsqlTypeMapper TypeMapper => CheckReadyAndGetConnector().TypeMapper;
///
///
/// The default TCP/IP port for PostgreSQL.
///
public const int DefaultPort = 5432;
///
/// Maximum value for connection timeout.
///
internal const int TimeoutLimit = 1024;
static readonly NpgsqlLogger Log = NpgsqlLogManager.CreateLogger(nameof(NpgsqlConnection));
static bool _countersInitialized;
static readonly StateChangeEventArgs ClosedToOpenEventArgs = new StateChangeEventArgs(ConnectionState.Closed, ConnectionState.Open);
static readonly StateChangeEventArgs OpenToClosedEventArgs = new StateChangeEventArgs(ConnectionState.Open, ConnectionState.Closed);
#endregion Fields
#region Constructors / Init / Open
///
/// Initializes a new instance of the
/// NpgsqlConnection class.
///
public NpgsqlConnection()
=> GC.SuppressFinalize(this);
///
/// Initializes a new instance of with the given connection string.
///
/// The connection used to open the PostgreSQL database.
public NpgsqlConnection(string connectionString) : this()
=> ConnectionString = connectionString;
///
/// Opens a database connection with the property settings specified by the
/// ConnectionString.
///
public override void Open() => Open(false, CancellationToken.None).GetAwaiter().GetResult();
///
/// This is the asynchronous version of .
///
///
/// Do not invoke other methods and properties of the object until the returned Task is complete.
///
/// The token to monitor for cancellation requests.
/// A task representing the asynchronous operation.
public override Task OpenAsync(CancellationToken cancellationToken)
{
using (NoSynchronizationContextScope.Enter())
return Open(true, cancellationToken);
}
void GetPoolAndSettings()
{
if (PoolManager.TryGetValue(_connectionString, out _pool))
{
Settings = _pool.Settings; // Great, we already have a pool
return;
}
// Connection string hasn't been seen before. Parse it.
Settings = new NpgsqlConnectionStringBuilder(_connectionString);
if (!_countersInitialized)
{
Counters.Initialize(Settings.UsePerfCounters);
_countersInitialized = true;
}
// Maybe pooling is off
if (!Settings.Pooling)
return;
// The connection string may be equivalent to one that has already been seen though (e.g. different
// ordering). Have NpgsqlConnectionStringBuilder produce a canonical string representation
// and recheck.
var canonical = Settings.ConnectionString;
if (PoolManager.TryGetValue(canonical, out _pool))
{
// The pool was found, but only under the canonical key - we're using a different version
// for the first time. Map it via our own key for next time.
_pool = PoolManager.GetOrAdd(_connectionString, _pool);
return;
}
// Really unseen, need to create a new pool
// The canonical pool is the 'base' pool so we need to set that up first. If someone beats us to it use what they put.
// The connection string pool can either be added here or above, if it's added above we should just use that.
var newPool = new ConnectorPool(Settings, canonical);
_pool = PoolManager.GetOrAdd(canonical, newPool);
if (_pool == newPool)
{
// If the pool we created was the one that ended up being stored we need to increment the appropriate counter.
// Avoids a race condition where multiple threads will create a pool but only one will be stored.
Counters.NumberOfActiveConnectionPools.Increment();
NpgsqlEventSource.Log.PoolCreated();
}
_pool = PoolManager.GetOrAdd(_connectionString, _pool);
}
Task Open(bool async, CancellationToken cancellationToken)
{
// This is an optimized path for when a connection can be taken from the pool
// with no waiting or I/O
CheckConnectionClosed();
Log.Trace("Opening connection...");
if (_pool == null || Settings.Enlist || !_pool.TryAllocateFast(this, out Connector))
return OpenLong();
_userFacingConnectionString = _pool.UserFacingConnectionString;
Counters.SoftConnectsPerSecond.Increment();
// Since this pooled connector was opened, types may have been added (and ReloadTypes() called),
// or global mappings may have changed. Bring this up to date if needed.
var mapper = Connector.TypeMapper;
if (mapper.ChangeCounter != TypeMapping.GlobalTypeMapper.Instance.ChangeCounter)
{
// We always do this synchronously which isn't amazing but not very important, because
// it's supposed to be a pretty rare event and the whole point is to keep this method
// non-async
Connector.LoadDatabaseInfo(NpgsqlTimeout.Infinite, false).GetAwaiter().GetResult();
}
Debug.Assert(Connector.Connection != null, "Open done but connector not set on Connection");
Log.Debug("Connection opened", Connector.Id);
OnStateChange(new StateChangeEventArgs(ConnectionState.Closed, ConnectionState.Open));
return Task.CompletedTask;
async Task OpenLong()
{
CheckConnectionClosed();
Log.Trace("Opening connection...");
_wasBroken = false;
try
{
var timeout = new NpgsqlTimeout(TimeSpan.FromSeconds(ConnectionTimeout));
Transaction? transaction = null;
if (_pool == null) // Un-pooled connection (or user forgot to set connection string)
{
if (string.IsNullOrEmpty(_connectionString))
throw new InvalidOperationException("The ConnectionString property has not been initialized.");
if (!Settings.PersistSecurityInfo)
_userFacingConnectionString = Settings.ToStringWithoutPassword();
Connector = new NpgsqlConnector(this);
await Connector.Open(timeout, async, cancellationToken);
Counters.NumberOfNonPooledConnections.Increment();
}
else
{
_userFacingConnectionString = _pool.UserFacingConnectionString;
if (Settings.Enlist)
{
transaction = Transaction.Current;
if (transaction != null)
{
// First, check to see if we have a connection enlisted to this transaction which has been closed.
// If so, return that as an optimization rather than opening a new one and triggering escalation
// to a distributed transaction.
Connector = _pool.TryAllocateEnlistedPending(Transaction.Current);
if (Connector != null)
{
Connector.Connection = this;
EnlistedTransaction = transaction;
}
}
if (Connector == null)
{
// If Enlist is true, we skipped the fast path above, try it here first,
// before going to the long path.
// TODO: Maybe find a more elegant way to factor this code...
if (!_pool.TryAllocateFast(this, out Connector))
Connector = await _pool.AllocateLong(this, timeout, async, cancellationToken);
}
}
else // No enlist
Connector = await _pool.AllocateLong(this, timeout, async, cancellationToken);
// Since this pooled connector was opened, types may have been added (and ReloadTypes() called),
// or global mappings may have changed. Bring this up to date if needed.
mapper = Connector.TypeMapper;
if (mapper.ChangeCounter != TypeMapping.GlobalTypeMapper.Instance.ChangeCounter)
await Connector.LoadDatabaseInfo(NpgsqlTimeout.Infinite, async);
}
// We may have gotten an already enlisted pending connector above, no need to enlist in that case
if (transaction != null && EnlistedTransaction == null)
EnlistTransaction(Transaction.Current);
}
catch
{
if (Connector != null)
{
if (_pool == null)
Connector.Close();
else
_pool.Release(Connector);
Connector = null;
}
throw;
}
Debug.Assert(Connector.Connection != null, "Open done but connector not set on Connection");
Log.Debug("Connection opened", Connector.Id);
OnStateChange(ClosedToOpenEventArgs);
}
}
#endregion Open / Init
#region Connection string management
///
/// Gets or sets the string used to connect to a PostgreSQL database. See the manual for details.
///
/// 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.
///
#nullable disable
public override string ConnectionString
#nullable enable
{
get => _userFacingConnectionString;
set
{
CheckConnectionClosed();
if (value == null)
value = string.Empty;
_userFacingConnectionString = _connectionString = value;
GetPoolAndSettings();
}
}
///
/// Gets or sets the delegate used to generate a password for new database connections.
///
///
/// This delegate is executed when a new database connection is opened that requires a password.
/// Password and
/// Passfile connection string
/// properties have precedence over this delegate. It will not be executed if a password is
/// specified, or the specified or default Passfile contains a valid entry.
/// Due to connection pooling this delegate is only executed when a new physical connection
/// is opened, not when reusing a connection that was previously opened from the pool.
///
public ProvidePasswordCallback? ProvidePasswordCallback { get; set; }
#endregion Connection string management
#region Configuration settings
///
/// Backend server host name.
///
[Browsable(true)]
[PublicAPI]
public string? Host => Settings.Host;
///
/// Backend server port.
///
[Browsable(true)]
[PublicAPI]
public int Port => Settings.Port;
///
/// Gets the time to wait while trying to establish a connection
/// before terminating the attempt and generating an error.
///
/// The time (in seconds) to wait for a connection to open. The default value is 15 seconds.
public override int ConnectionTimeout => Settings.Timeout;
///
/// Gets the time to wait while trying to execute a command
/// before terminating the attempt and generating an error.
///
/// The time (in seconds) to wait for a command to complete. The default value is 20 seconds.
public int CommandTimeout => Settings.CommandTimeout;
///
/// Gets the name of the current database or the database to be used after a connection is opened.
///
/// 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.
public override string? Database => Settings.Database ?? Settings.Username;
///
/// Gets the string identifying the database server (host and port)
///
public override string DataSource => Settings.DataSourceCached;
///
/// Whether to use Windows integrated security to log in.
///
[PublicAPI]
public bool IntegratedSecurity => Settings.IntegratedSecurity;
///
/// User name.
///
[PublicAPI]
public string? UserName => Settings.Username;
internal string? Password => Settings.Password;
// The following two lines are here for backwards compatibility with the EF6 provider
// ReSharper disable UnusedMember.Global
internal string? EntityTemplateDatabase => Settings.EntityTemplateDatabase;
internal string? EntityAdminDatabase => Settings.EntityAdminDatabase;
// ReSharper restore UnusedMember.Global
#endregion Configuration settings
#region State management
///
/// Gets the current state of the connection.
///
/// A bitwise combination of the ConnectionState values. The default is Closed.
[Browsable(false)]
public ConnectionState FullState
{
get
{
if (Connector == null || _disposed)
{
return _wasBroken ? ConnectionState.Broken : ConnectionState.Closed;
}
switch (Connector.State)
{
case ConnectorState.Closed:
return ConnectionState.Closed;
case ConnectorState.Connecting:
return ConnectionState.Connecting;
case ConnectorState.Ready:
return ConnectionState.Open;
case ConnectorState.Executing:
return ConnectionState.Open | ConnectionState.Executing;
case ConnectorState.Copy:
case ConnectorState.Fetching:
case ConnectorState.Waiting:
return ConnectionState.Open | ConnectionState.Fetching;
case ConnectorState.Broken:
return ConnectionState.Broken;
default:
throw new InvalidOperationException($"Internal Npgsql bug: unexpected value {Connector.State} of enum {nameof(ConnectorState)}. Please file a bug.");
}
}
}
///
/// Gets whether the current state of the connection is Open or Closed
///
/// ConnectionState.Open, ConnectionState.Closed or ConnectionState.Connecting
[Browsable(false)]
public override ConnectionState State
{
get
{
var s = FullState;
if ((s & ConnectionState.Open) != 0)
return ConnectionState.Open;
if ((s & ConnectionState.Connecting) != 0)
return ConnectionState.Connecting;
return ConnectionState.Closed;
}
}
#endregion State management
#region Commands
///
/// Creates and returns a DbCommand
/// object associated with the IDbConnection.
///
/// A DbCommand object.
protected override DbCommand CreateDbCommand()
{
return CreateCommand();
}
///
/// Creates and returns a NpgsqlCommand
/// object associated with the NpgsqlConnection.
///
/// A NpgsqlCommand object.
public new NpgsqlCommand CreateCommand()
{
CheckDisposed();
return new NpgsqlCommand("", this);
}
#endregion Commands
#region Transactions
///
/// Begins a database transaction with the specified isolation level.
///
/// The isolation level under which the transaction should run.
/// An DbTransaction
/// object representing the new transaction.
///
/// Currently the IsolationLevel ReadCommitted and Serializable are supported by the PostgreSQL backend.
/// There's no support for nested transactions.
///
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return BeginTransaction(isolationLevel);
}
///
/// Begins a database transaction.
///
/// A NpgsqlTransaction
/// object representing the new transaction.
///
/// Currently there's no support for nested transactions. Transactions created by this method will have Read Committed isolation level.
///
public new NpgsqlTransaction BeginTransaction() => BeginTransaction(IsolationLevel.Unspecified);
///
/// Begins a database transaction with the specified isolation level.
///
/// The isolation level under which the transaction should run.
/// A NpgsqlTransaction
/// object representing the new transaction.
///
/// Currently the IsolationLevel ReadCommitted and Serializable are supported by the PostgreSQL backend.
/// There's no support for nested transactions.
///
public new NpgsqlTransaction BeginTransaction(IsolationLevel level)
{
if (level == IsolationLevel.Chaos)
throw new NotSupportedException("Unsupported IsolationLevel: " + level);
var connector = CheckReadyAndGetConnector();
// Note that beginning a transaction doesn't actually send anything to the backend
// (only prepends), so strictly speaking we don't have to start a user action.
// However, we do this for consistency as if we did (for the checks and exceptions)
using (connector.StartUserAction())
{
if (connector.InTransaction)
throw new InvalidOperationException("A transaction is already in progress; nested/concurrent transactions aren't supported.");
connector.Transaction.Init(level);
return connector.Transaction;
}
}
///
/// Enlist transaction.
///
#nullable disable
public override void EnlistTransaction(Transaction transaction)
#nullable enable
{
if (EnlistedTransaction != null)
{
if (EnlistedTransaction.Equals(transaction))
return;
try
{
if (EnlistedTransaction.TransactionInformation.Status == System.Transactions.TransactionStatus.Active)
throw new InvalidOperationException($"Already enlisted to transaction (localid={EnlistedTransaction.TransactionInformation.LocalIdentifier})");
}
catch (ObjectDisposedException)
{
// The MSDTC 2nd phase is asynchronous, so we may end up checking the TransactionInformation on
// a disposed transaction. To be extra safe we catch that, and understand that the transaction
// has ended - no problem for reenlisting.
}
}
var connector = CheckReadyAndGetConnector();
EnlistedTransaction = transaction;
if (transaction == null)
return;
// Until #1378 is implemented, we have no recovery, and so no need to enlist as a durable resource manager
// (or as promotable single phase).
// Note that even when #1378 is implemented in some way, we should check for mono and go volatile in any case -
// distributed transactions aren't supported.
transaction.EnlistVolatile(new VolatileResourceManager(this, transaction), EnlistmentOptions.None);
Log.Debug($"Enlisted volatile resource manager (localid={transaction.TransactionInformation.LocalIdentifier})", connector.Id);
}
#endregion
#region 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.
///
public override void Close() => Close(false);
internal void Close(bool wasBroken)
{
if (Connector == null)
return;
var connectorId = Connector.Id;
Log.Trace("Closing connection...", connectorId);
_wasBroken = wasBroken;
Connector.CloseOngoingOperations();
// The connector has closed us during CloseOngoingOperations due to an underlying failure.
if (Connector == null)
return;
if (Settings.Pooling)
{
if (EnlistedTransaction == null)
_pool!.Release(Connector);
else
{
// A System.Transactions transaction is still in progress, we need to wait for it to complete.
// Close the connection and disconnect it from the resource manager but leave the connector
// in a enlisted pending list in the pool.
_pool!.AddPendingEnlistedConnector(Connector, EnlistedTransaction);
Connector.Connection = null;
EnlistedTransaction = null;
}
}
else // Non-pooled connection
{
if (EnlistedTransaction == null)
Connector.Close();
// If a non-pooled connection is being closed but is enlisted in an ongoing
// TransactionScope, simply detach the connector from the connection and leave
// it open. It will be closed when the TransactionScope is disposed.
Connector.Connection = null;
EnlistedTransaction = null;
}
Log.Debug("Connection closed", connectorId);
Connector = null;
OnStateChange(OpenToClosedEventArgs);
}
///
/// Releases all resources used by the
/// NpgsqlConnection.
///
/// true when called from Dispose();
/// false when being called from the finalizer.
protected override void Dispose(bool disposing)
{
if (_disposed)
return;
if (disposing)
Close();
base.Dispose(disposing);
_disposed = true;
}
#endregion
#region Notifications and Notices
///
/// Fires when PostgreSQL notices are received from PostgreSQL.
///
///
/// PostgreSQL notices are non-critical messages generated by PostgreSQL, either as a result of a user query
/// (e.g. as a warning or informational notice), or due to outside activity (e.g. if the database administrator
/// initiates a "fast" database shutdown).
///
/// Note that notices are very different from notifications (see the event).
///
public event NoticeEventHandler? Notice;
///
/// Fires when PostgreSQL notifications are received from PostgreSQL.
///
///
/// PostgreSQL notifications are sent when your connection has registered for notifications on a specific channel via the
/// LISTEN command. NOTIFY can be used to generate such notifications, allowing for an inter-connection communication channel.
///
/// Note that notifications are very different from notices (see the event).
///
public event NotificationEventHandler? Notification;
internal void OnNotice(PostgresNotice e)
{
try
{
Notice?.Invoke(this, new NpgsqlNoticeEventArgs(e));
}
catch (Exception ex)
{
// Block all exceptions bubbling up from the user's event handler
Log.Error("User exception caught when emitting notice event", ex);
}
}
internal void OnNotification(NpgsqlNotificationEventArgs e)
{
try
{
Notification?.Invoke(this, e);
}
catch (Exception ex)
{
// Block all exceptions bubbling up from the user's event handler
Log.Error("User exception caught when emitting notification event", ex);
}
}
#endregion Notifications and Notices
#region SSL
///
/// Returns whether SSL is being used for the connection.
///
internal bool IsSecure => CheckConnectionOpen().IsSecure;
///
/// Selects the local Secure Sockets Layer (SSL) certificate used for authentication.
///
///
/// See
///
public ProvideClientCertificatesCallback? ProvideClientCertificatesCallback { get; set; }
///
/// Verifies the remote Secure Sockets Layer (SSL) certificate used for authentication.
/// Ignored if is set.
///
///
/// See
///
public RemoteCertificateValidationCallback? UserCertificateValidationCallback { get; set; }
#endregion SSL
#region Backend version, capabilities, settings
///
/// Version of the PostgreSQL backend.
/// This can only be called when there is an active connection.
///
[Browsable(false)]
public Version PostgreSqlVersion => CheckConnectionOpen().DatabaseInfo.Version;
///
/// PostgreSQL server version.
///
public override string ServerVersion => PostgreSqlVersion.ToString();
///
/// Process id of backend server.
/// This can only be called when there is an active connection.
///
[Browsable(false)]
// ReSharper disable once InconsistentNaming
public int ProcessID => CheckConnectionOpen().BackendProcessId;
///
/// Reports whether the backend uses the newer integer timestamp representation.
/// Note that the old floating point representation is not supported.
/// Meant for use by type plugins (e.g. NodaTime)
///
[Browsable(false)]
[PublicAPI]
public bool HasIntegerDateTimes => CheckConnectionOpen().DatabaseInfo.HasIntegerDateTimes;
///
/// The connection's timezone as reported by PostgreSQL, in the IANA/Olson database format.
///
[Browsable(false)]
[PublicAPI]
public string Timezone => CheckConnectionOpen().Timezone;
///
/// Holds all PostgreSQL parameters received for this connection. Is updated if the values change
/// (e.g. as a result of a SET command).
///
[Browsable(false)]
[PublicAPI]
public IReadOnlyDictionary PostgresParameters => CheckConnectionOpen().PostgresParameters;
#endregion Backend version, capabilities, settings
#region Copy
///
/// Begins a binary COPY FROM STDIN operation, a high-performance data import mechanism to a PostgreSQL table.
///
/// A COPY FROM STDIN SQL command
/// A which can be used to write rows and columns
///
/// See http://www.postgresql.org/docs/current/static/sql-copy.html.
///
public NpgsqlBinaryImporter BeginBinaryImport(string copyFromCommand)
{
if (copyFromCommand == null)
throw new ArgumentNullException(nameof(copyFromCommand));
if (!copyFromCommand.TrimStart().ToUpper().StartsWith("COPY"))
throw new ArgumentException("Must contain a COPY FROM STDIN command!", nameof(copyFromCommand));
var connector = CheckReadyAndGetConnector();
Log.Debug("Starting binary import", connector.Id);
connector.StartUserAction(ConnectorState.Copy);
try
{
var importer = new NpgsqlBinaryImporter(connector, copyFromCommand);
connector.CurrentCopyOperation = importer;
return importer;
}
catch
{
connector.EndUserAction();
throw;
}
}
///
/// Begins a binary COPY TO STDOUT operation, a high-performance data export mechanism from a PostgreSQL table.
///
/// A COPY TO STDOUT SQL command
/// A which can be used to read rows and columns
///
/// See http://www.postgresql.org/docs/current/static/sql-copy.html.
///
public NpgsqlBinaryExporter BeginBinaryExport(string copyToCommand)
{
if (copyToCommand == null)
throw new ArgumentNullException(nameof(copyToCommand));
if (!copyToCommand.TrimStart().ToUpper().StartsWith("COPY"))
throw new ArgumentException("Must contain a COPY TO STDOUT command!", nameof(copyToCommand));
var connector = CheckReadyAndGetConnector();
Log.Debug("Starting binary export", connector.Id);
connector.StartUserAction(ConnectorState.Copy);
try
{
var exporter = new NpgsqlBinaryExporter(connector, copyToCommand);
connector.CurrentCopyOperation = exporter;
return exporter;
}
catch
{
connector.EndUserAction();
throw;
}
}
///
/// 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 .
///
/// A COPY FROM STDIN SQL command
///
/// A TextWriter that can be used to send textual data.
///
/// See http://www.postgresql.org/docs/current/static/sql-copy.html.
///
public TextWriter BeginTextImport(string copyFromCommand)
{
if (copyFromCommand == null)
throw new ArgumentNullException(nameof(copyFromCommand));
if (!copyFromCommand.TrimStart().ToUpper().StartsWith("COPY"))
throw new ArgumentException("Must contain a COPY FROM STDIN command!", nameof(copyFromCommand));
var connector = CheckReadyAndGetConnector();
Log.Debug("Starting text import", connector.Id);
connector.StartUserAction(ConnectorState.Copy);
try
{
var writer = new NpgsqlCopyTextWriter(connector, new NpgsqlRawCopyStream(connector, copyFromCommand));
connector.CurrentCopyOperation = writer;
return writer;
}
catch
{
connector.EndUserAction();
throw;
}
}
///
/// 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 .
///
/// A COPY TO STDOUT SQL command
///
/// A TextReader that can be used to read textual data.
///
/// See http://www.postgresql.org/docs/current/static/sql-copy.html.
///
public TextReader BeginTextExport(string copyToCommand)
{
if (copyToCommand == null)
throw new ArgumentNullException(nameof(copyToCommand));
if (!copyToCommand.TrimStart().ToUpper().StartsWith("COPY"))
throw new ArgumentException("Must contain a COPY TO STDOUT command!", nameof(copyToCommand));
var connector = CheckReadyAndGetConnector();
Log.Debug("Starting text export", connector.Id);
connector.StartUserAction(ConnectorState.Copy);
try
{
var reader = new NpgsqlCopyTextReader(connector, new NpgsqlRawCopyStream(connector, copyToCommand));
connector.CurrentCopyOperation = reader;
return reader;
}
catch
{
connector.EndUserAction();
throw;
}
}
///
/// 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, 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.
///
/// A COPY TO STDOUT or COPY FROM STDIN SQL command
/// A that can be used to read or write raw binary data.
///
/// See http://www.postgresql.org/docs/current/static/sql-copy.html.
///
public NpgsqlRawCopyStream BeginRawBinaryCopy(string copyCommand)
{
if (copyCommand == null)
throw new ArgumentNullException(nameof(copyCommand));
if (!copyCommand.TrimStart().ToUpper().StartsWith("COPY"))
throw new ArgumentException("Must contain a COPY TO STDOUT OR COPY FROM STDIN command!", nameof(copyCommand));
var connector = CheckReadyAndGetConnector();
Log.Debug("Starting raw COPY operation", connector.Id);
connector.StartUserAction(ConnectorState.Copy);
try
{
var stream = new NpgsqlRawCopyStream(connector, copyCommand);
if (!stream.IsBinary)
{
// TODO: Stop the COPY operation gracefully, no breaking
connector.Break();
throw new ArgumentException("copyToCommand triggered a text transfer, only binary is allowed", nameof(copyCommand));
}
connector.CurrentCopyOperation = stream;
return stream;
}
catch
{
connector.EndUserAction();
throw;
}
}
#endregion
#region Enum mapping
///
/// Maps a CLR enum to a PostgreSQL enum type for use with this connection.
///
///
/// CLR enum labels are mapped by name to PostgreSQL enum labels.
/// The translation strategy can be controlled by the parameter,
/// which defaults to .
/// You can also use the 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 method.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
/// The .NET enum type to be mapped
[PublicAPI]
[Obsolete("Use NpgsqlConnection.TypeMapper.MapEnum() instead")]
public void MapEnum(string? pgName = null, INpgsqlNameTranslator? nameTranslator = null)
where TEnum : struct, Enum
=> TypeMapper.MapEnum(pgName, nameTranslator);
///
/// Maps a CLR enum to a PostgreSQL enum type for use with all connections created from now on. Existing connections aren't affected.
///
///
/// CLR enum labels are mapped by name to PostgreSQL enum labels.
/// The translation strategy can be controlled by the parameter,
/// which defaults to .
/// You can also use the 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 method.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
/// The .NET enum type to be mapped
[PublicAPI]
[Obsolete("Use NpgsqlConnection.GlobalTypeMapper.MapEnum() instead")]
public static void MapEnumGlobally(string? pgName = null, INpgsqlNameTranslator? nameTranslator = null)
where TEnum : struct, Enum
=> GlobalTypeMapper.MapEnum(pgName, nameTranslator);
///
/// Removes a previous global enum mapping.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
[PublicAPI]
[Obsolete("Use NpgsqlConnection.GlobalTypeMapper.UnmapEnum() instead")]
public static void UnmapEnumGlobally(string? pgName = null, INpgsqlNameTranslator? nameTranslator = null)
where TEnum : struct, Enum
=> GlobalTypeMapper.UnmapEnum(pgName, nameTranslator);
#endregion
#region Composite registration
///
/// Maps a CLR type to a PostgreSQL composite type for use with this connection.
///
///
/// CLR fields and properties by string to PostgreSQL enum labels.
/// The translation strategy can be controlled by the parameter,
/// which defaults to .
/// You can also use the 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 method.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
/// The .NET type to be mapped
[PublicAPI]
[Obsolete("Use NpgsqlConnection.TypeMapper.MapComposite() instead")]
public void MapComposite(string? pgName = null, INpgsqlNameTranslator? nameTranslator = null) where T : new()
=> TypeMapper.MapComposite(pgName, nameTranslator);
///
/// Maps a CLR type to a PostgreSQL composite type for use with all connections created from now on. Existing connections aren't affected.
///
///
/// CLR fields and properties by string to PostgreSQL enum labels.
/// The translation strategy can be controlled by the parameter,
/// which defaults to .
/// You can also use the 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 method.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
/// The .NET type to be mapped
[PublicAPI]
[Obsolete("Use NpgsqlConnection.GlobalTypeMapper.MapComposite() instead")]
public static void MapCompositeGlobally(string? pgName = null, INpgsqlNameTranslator? nameTranslator = null) where T : new()
=> GlobalTypeMapper.MapComposite(pgName, nameTranslator);
///
/// Removes a previous global enum mapping.
///
///
/// A PostgreSQL type name for the corresponding enum type in the database.
/// If null, the name translator given in will be used.
///
///
/// A component which will be used to translate CLR names (e.g. SomeClass) into database names (e.g. some_class).
/// Defaults to
///
[PublicAPI]
[Obsolete("Use NpgsqlConnection.GlobalTypeMapper.UnmapComposite() instead")]
public static void UnmapCompositeGlobally(string pgName, INpgsqlNameTranslator? nameTranslator = null) where T : new()
=> GlobalTypeMapper.UnmapComposite(pgName, nameTranslator);
#endregion
#region Wait
///
/// Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and
/// exits immediately. The asynchronous message is delivered via the normal events
/// (, ).
///
///
/// The time-out value, in milliseconds, passed to .
/// The default value is 0, which indicates an infinite time-out period.
/// Specifying -1 also indicates an infinite time-out period.
///
/// true if an asynchronous message was received, false if timed out.
public bool Wait(int timeout)
{
if (timeout != -1 && timeout < 0)
throw new ArgumentException("Argument must be -1, 0 or positive", nameof(timeout));
var connector = CheckConnectionOpen();
Log.Debug($"Starting to wait (timeout={timeout})...", connector.Id);
return connector.Wait(timeout);
}
///
/// Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and
/// exits immediately. The asynchronous message is delivered via the normal events
/// (, ).
///
///
/// The time-out value is passed to .
///
/// true if an asynchronous message was received, false if timed out.
[PublicAPI]
public bool Wait(TimeSpan timeout) => Wait((int)timeout.TotalMilliseconds);
///
/// Waits until an asynchronous PostgreSQL messages (e.g. a notification) arrives, and
/// exits immediately. The asynchronous message is delivered via the normal events
/// (, ).
///
[PublicAPI]
public void Wait() => Wait(0);
///
/// Waits asynchronously until an asynchronous PostgreSQL messages (e.g. a notification)
/// arrives, and exits immediately. The asynchronous message is delivered via the normal events
/// (, ).
///
/// The token to monitor for cancellation requests. The default value is .
[PublicAPI]
public Task WaitAsync(CancellationToken cancellationToken = default)
{
if (cancellationToken.IsCancellationRequested)
return Task.FromCanceled(cancellationToken);
var connector = CheckConnectionOpen();
Log.Debug("Starting to wait asynchronously...", connector.Id);
return connector.WaitAsync(cancellationToken);
}
#endregion
#region State checks
[MethodImpl(MethodImplOptions.AggressiveInlining)]
NpgsqlConnector CheckConnectionOpen()
{
CheckDisposed();
return Connector ?? throw new InvalidOperationException("Connection is not open");
}
[MethodImpl(MethodImplOptions.AggressiveInlining)]
void CheckConnectionClosed()
{
CheckDisposed();
if (Connector != null)
throw new InvalidOperationException("Connection already open");
}
[MethodImpl(MethodImplOptions.AggressiveInlining)]
void CheckDisposed()
{
if (_disposed)
throw new ObjectDisposedException(typeof(NpgsqlConnection).Name);
}
[MethodImpl(MethodImplOptions.AggressiveInlining)]
internal NpgsqlConnector CheckReadyAndGetConnector()
{
CheckDisposed();
// This method gets called outside any lock, and might be in a race condition
// with an ongoing keepalive, which may break the connector (setting the connection's
// Connector to null). We capture the connector to the stack and return it here.
var conn = Connector;
if (conn == null)
throw new InvalidOperationException("Connection is not open");
return conn;
}
#endregion State checks
#region Schema operations
///
/// Returns the supported collections
///
public override DataTable GetSchema()
=> GetSchema("MetaDataCollections", null);
///
/// Returns the schema collection specified by the collection name.
///
/// The collection name.
/// The collection specified.
public override DataTable GetSchema(string? collectionName) => GetSchema(collectionName, null);
///
/// Returns the schema collection specified by the collection name filtered by the restrictions.
///
/// The collection name.
///
/// The restriction values to filter the results. A description of the restrictions is contained
/// in the Restrictions collection.
///
/// The collection specified.
public override DataTable GetSchema(string? collectionName, string?[]? restrictions)
=> NpgsqlSchema.GetSchema(this, collectionName, restrictions);
#endregion Schema operations
#region Misc
///
/// Creates a closed connection with the connection string and authentication details of this message.
///
object ICloneable.Clone()
{
CheckDisposed();
var conn = new NpgsqlConnection(_connectionString) {
ProvideClientCertificatesCallback = ProvideClientCertificatesCallback,
UserCertificateValidationCallback = UserCertificateValidationCallback,
_userFacingConnectionString = _userFacingConnectionString
};
return conn;
}
///
/// 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)
///
[PublicAPI]
public NpgsqlConnection CloneWith(string connectionString)
{
CheckDisposed();
var csb = new NpgsqlConnectionStringBuilder(connectionString);
if (csb.Password == null && Password != null)
csb.Password = Password;
return new NpgsqlConnection(csb.ToString()) {
ProvideClientCertificatesCallback = ProvideClientCertificatesCallback,
UserCertificateValidationCallback = UserCertificateValidationCallback
};
}
///
/// This method changes the current database by disconnecting from the actual
/// database and connecting to the specified.
///
/// The name of the database to use in place of the current database.
public override void ChangeDatabase(string dbName)
{
if (dbName == null)
throw new ArgumentNullException(nameof(dbName));
if (string.IsNullOrEmpty(dbName))
throw new ArgumentOutOfRangeException(nameof(dbName), dbName, $"Invalid database name: {dbName}");
CheckConnectionOpen();
Close();
_pool = null;
Settings = Settings.Clone();
Settings.Database = dbName;
ConnectionString = Settings.ToString();
Open();
}
///
/// DB provider factory.
///
protected override DbProviderFactory DbProviderFactory => NpgsqlFactory.Instance;
///
/// Clear connection pool.
///
public static void ClearPool(NpgsqlConnection connection) => PoolManager.Clear(connection._connectionString);
///
/// Clear all connection pools.
///
public static void ClearAllPools() => PoolManager.ClearAll();
///
/// Unprepares all prepared statements on this connection.
///
[PublicAPI]
public void UnprepareAll()
{
var connector = CheckReadyAndGetConnector();
using (connector.StartUserAction())
connector.UnprepareAll();
}
///
/// Flushes the type cache for this connection's connection string and reloads the types for this connection only.
/// Type changes will appear for other connections only after they are re-opened from the pool.
///
public void ReloadTypes()
{
var conn = CheckReadyAndGetConnector();
NpgsqlDatabaseInfo.Cache.TryRemove(_connectionString, out var _);
conn.LoadDatabaseInfo(NpgsqlTimeout.Infinite, false).GetAwaiter().GetResult();
// Increment the change counter on the global type mapper. This will make conn.Open() pick up the
// new DatabaseInfo and set up a new connection type mapper
TypeMapping.GlobalTypeMapper.Instance.RecordChange();
}
#endregion Misc
}
#region Delegates
///
/// Represents a method that handles the event.
///
/// The source of the event.
/// A that contains the notice information (e.g. message, severity...).
public delegate void NoticeEventHandler(object sender, NpgsqlNoticeEventArgs e);
///
/// Represents a method that handles the event.
///
/// The source of the event.
/// A that contains the notification payload.
public delegate void NotificationEventHandler(object sender, NpgsqlNotificationEventArgs e);
///
/// Represents the method that allows the application to provide a certificate collection to be used for SSL client authentication
///
/// A X509CertificateCollection to be filled with one or more client certificates.
public delegate void ProvideClientCertificatesCallback(X509CertificateCollection certificates);
///
/// Represents the method that allows the application to provide a password at connection time in code rather than configuration
///
/// Hostname
/// Port
/// Database Name
/// User
/// A valid password for connecting to the database
public delegate string ProvidePasswordCallback(string host, int port, string database, string username);
#endregion
}