Class ConnectionExtensions

Extends Npgsql.NpgsqlConnection with useful methods.

Inheritance

object

ConnectionExtensions

Namespace: Neon.Postgres

Assembly: Neon.Postgres.dll

Syntax

public static class ConnectionExtensions

Methods

ExecuteBatch(NpgsqlConnection, string, NpgsqlTransaction)

Executes a batch of SQL commands saeparated by lines including go separators. This works like Microsoft SQL server related tools.

Declaration

public static void ExecuteBatch(this NpgsqlConnection connection, string batchText, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	batchText	The SQL commands possibly separated by go lines.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Remarks

It's often necessary to execute a sequence of SQL commands that depend on each other. One example is a command that creates a table followed by commands that write rows. You might think that you could achieve this by executing the following as one command:

CREATE TABLE my_table (name text);
INSERT INTO my_table (name) values ('Jack');
INSERT INTO my_table (name) values ('Jill');

but this won't actually work because the database generates a query plan for the entire command and when it does this and sees the inserts into [my_table] but the table doesn't actually exist at the time the query plan is being created. So the command will fail.

What you really need to do is create the table first as a separate command and then do the inserts as one or more subsequent commands. This is not terribly convenient so we've introduced the concept of a batch of commands via this method. Here's what this would look like:

CREATE TABLE my_table (name text);
go
INSERT INTO my_table (name) values ('Jack');
INSERT INTO my_table (name) values ('Jill');

See how the go line separates the table creation from the inserts. This method will split the batchText into separate commands on any go lines and then execute these commands in order.

note

go is case insensitive and any leading or trailing space on the line will be ignored.

ExecuteBatchAsync(NpgsqlConnection, string, CancellationToken, NpgsqlTransaction)

Asynchronously a batch of SQL commands saeparated by lines including go separators. This works like Microsoft SQL server related tools.

Declaration

public static Task ExecuteBatchAsync(this NpgsqlConnection connection, string batchText, CancellationToken cancellationToken = default, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	batchText	The SQL commands possibly separated by go lines.
CancellationToken	cancellationToken	Optional cancellation token.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
Task	The tracking Task.

Remarks

It's often necessary to execute a sequence of SQL commands that depend on each other. One example is a command that creates a table followed by commands that write rows. You might think that you could achieve this by executing the following as one command:

CREATE TABLE my_table (name text);
INSERT INTO my_table (name) values ('Jack');
INSERT INTO my_table (name) values ('Jill');

but this won't actually work because the database generates a query plan for the entire command and when it does this and sees the inserts into [my_table] but the table doesn't actually exist at the time the query plan is being created. So the command will fail.

What you really need to do is create the table first as a separate command and then do the inserts as one or more subsequent commands. This is not terribly convenient so we've introduced the concept of a batch of commands via this method. Here's what this would look like:

CREATE TABLE my_table (name text);
go
INSERT INTO my_table (name) values ('Jack');
INSERT INTO my_table (name) values ('Jill');

See how the go line separates the table creation from the inserts. This method will split the batchText into separate commands on any go lines and then execute these commands in order.

note

go is case insensitive and any leading or trailing space on the line will be ignored.

ExecuteNonQuery(NpgsqlConnection, string, NpgsqlTransaction)

Executes a SQL command that does not perform a query.

Declaration

public static int ExecuteNonQuery(this NpgsqlConnection connection, string cmdText, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
int	The number of rows impacted.

Remarks

note

Although this method is convienent, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

ExecuteNonQueryAsync(NpgsqlConnection, string, CancellationToken, NpgsqlTransaction)

Asynchronously executes a SQL command that does not perform a query.

Declaration

public static Task<int> ExecuteNonQueryAsync(this NpgsqlConnection connection, string cmdText, CancellationToken cancellationToken = default, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
CancellationToken	cancellationToken	Optional cancellation token.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
Task<int>	The number of rows impacted.

Remarks

note

Although this method is convienent, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

ExecuteReader(NpgsqlConnection, string, CommandBehavior, NpgsqlTransaction)

Executes a SQL query and returns the data reader to be used to process the results.

Declaration

public static NpgsqlDataReader ExecuteReader(this NpgsqlConnection connection, string cmdText, CommandBehavior behavior = CommandBehavior.Default, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
CommandBehavior	behavior	Optionally specifies the command behavior.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
NpgsqlDataReader	The Npgsql.NpgsqlDataReader.

Remarks

note

Although this method is convenient, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

ExecuteReaderAsync(NpgsqlConnection, string, CommandBehavior, CancellationToken, NpgsqlTransaction)

Asynchronously executes a SQL query and returns the data reader to be used to process the results.

Declaration

public static Task<NpgsqlDataReader> ExecuteReaderAsync(this NpgsqlConnection connection, string cmdText, CommandBehavior behavior = CommandBehavior.Default, CancellationToken cancellationToken = default, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
CommandBehavior	behavior	Optionally specifies the command behavior.
CancellationToken	cancellationToken	Optional cancellation token.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
Task<NpgsqlDataReader>	The Npgsql.NpgsqlDataReader.

Remarks

note

Although this method is convenient, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

ExecuteScalar(NpgsqlConnection, string, NpgsqlTransaction)

Executes a SQL query and returns the first column from the first row returned. All other rows and columns will be ignored.

Declaration

public static object ExecuteScalar(this NpgsqlConnection connection, string cmdText, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
object	The value of the first column on the first row returned by the command.

Remarks

note

Although this method is convenient, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

ExecuteScalarAsync(NpgsqlConnection, string, CancellationToken, NpgsqlTransaction)

Asynchronously executes a SQL query and returns the first column from the first row returned. All other rows and columns will be ignored.

Declaration

public static Task<object> ExecuteScalarAsync(this NpgsqlConnection connection, string cmdText, CancellationToken cancellationToken = default, NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The database connection.
string	cmdText	The SQL command.
CancellationToken	cancellationToken	Optional cancellation token.
NpgsqlTransaction	transaction	Optionally specifies the transaction.

Returns

Type	Description
Task<object>	The value of the first column on the first row returned by the command.

Remarks

note

Although this method is convenient, consider explictly creating and preparing Npgsql.NpgsqlCommand for frequently executed commands for better performance.

OpenDatabase(NpgsqlConnection, string)

Clones an existing database connection by retaining all connection settings except that the new connection will be opened to target a new database.

Declaration

public static NpgsqlConnection OpenDatabase(this NpgsqlConnection connection, string database)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The existing connection.
string	database	The target database for the new connection.

Returns

Type	Description
NpgsqlConnection	The new Npgsql.NpgsqlConnection.

OpenDatabaseAsync(NpgsqlConnection, string)

Asynchronously clones an existing database connection by retaining all connection settings except that the connection will be opened to target a new database.

Declaration

public static Task<NpgsqlConnection> OpenDatabaseAsync(this NpgsqlConnection connection, string database)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The existing connection.
string	database	The target database for the new connection.

Returns

Type	Description
Task<NpgsqlConnection>	The new Npgsql.NpgsqlConnection.