Class PreparedCommand

Simplifies prepared Postgres command usage by combining the prepared command and its usage into a single type. It's also often useful to create derived custom types from this that handle the parameter definitions and subsitutions and perhaps precompute result column indexes to help abstract these details from the calling program.

Inheritance

object

PreparedCommand

Namespace: Neon.Postgres

Assembly: Neon.Postgres.dll

Syntax

public class PreparedCommand

Remarks

You can use this class directly in your code like:

var parameters = new Dictionary<string, NpgsqlDbType>()> 
    {
        { "name", NpgsqlDbType.Text }
    };
var preparedCommand = new PreparedCommand(connection, "SELECT Name, Age, Email FROM People WHERE Name = @name", parameters);
foreach (var name in new string[] { "jack", "jill", "john", "jane" })
{
var queryCommand = preparedCommand.Clone();
queryCommand.Parameters["name"].Value = name;

foreach (var row in queryCommand.ExecuteReader().ToEnumerable())
{
    Console.WriteLine($"Name: {row.GetString("Name")} Age: {row.GetInt32("Age"} Email: {row.GetString("Email")}");
}

}

In this example, we first created the prepared command that performs a query passing a person's name as the parameter. Note that we had to create a dictionary defining the parameter name and type. The below, we looped for perform four queries by cloning the prepared command, setting the parameter values and then executing the command.

Note how we used methods like row.GetString("Name") to access individual columns. This works and is convenient but will be somewhat inefficient because the method will need to map the column name into the corresponding column index. We could have specified column indexes here, but that's starting to be fragile and could break if we inserted or removed result columns. Even using names can be fragile since column names can be altered over time.

We recommend writing custom classes that inherit from PreparedCommand to help abstract these things efficiently and without needing to use ORM frameworks like Entity Framework, NHibernate, and Dapper which tend to be inefficient and somewhat cumbersome to setup. Here's an example of a class that wraps a prepared statement to implement the query from the example above:

public class QueryPeopleByName : PreparedCommand
{
    private const string query = "SELECT Name, Age, Email FROM People WHERE Name = @name";
public const int NameIndex  = 0;
public const int AgeIndex   = 1;
public const int EmailIndex = 2;

private static readonly Dictionary<string, NpgsqlDbType> paramDefinitions =
    new Dictionary<string, NpgsqlDbType>()
    {
        { "name", NpgsqlDbType.Text }
    };

public QueryPeopleByName(NpgsqlConnection connection)
    : base(connection, query, paramDefinitions)
{
    var queryCommand = queryPeopleByName.Clone();

    queryCommand.Parameters["name"] = name;

    return qiery
}

public ReaderEnumerator GetPeople(string name)
{
    var queryCommand = queryPeopleByName.Clone();

    queryCommand.Parameters["name"].Value = name;

    return queryCommand.ExecuteReader().ToEnumerable()
}

}
...
var queryPeopleByName = new QueryPeopleByName(connection);
foreach (var name in new string[] { "jack", "jill", "john", "jane" })
{
foreach (var row in queryCommand.ExecuteReader().ToEnumerable())
{
Console.WriteLine($"Name: {row.GetString(QueryPeopleByName.NameIndex)} Age: {row.GetInt32(QueryPeopleByName.AgeIndex} Email: {row.GetString(QueryPeopleByName.EmailIndex)}");
}
}

The example above abstracted the query SQL, the parameter subsitution, as well as the result column indexes to make this a little less fragile and easier to modify when necessary. You can extend this coding pattern by having your class handle conversion of the query result to nice .NET model objects:

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
    public string Email { get; set; }
}
public class QueryPeopleByName : PreparedCommand
{
private const string query = "SELECT Name, Age, Email FROM People WHERE Name = @name";
private const int NameIndex  = 0;
private const int AgeIndex   = 1;
private const int EmailIndex = 2;

private static readonly Dictionary<string, NpgsqlDbType> paramDefinitions =
    new Dictionary<string, NpgsqlDbType>()
    {
        { "name", NpgsqlDbType.Text }
    };

public QueryPeopleByName(NpgsqlConnection connection)
    : base(connection, query, paramDefinitions)
{
    var queryCommand = queryPeopleByName.Clone();

    queryCommand.Parameters["name"] = name;

    return qiery
}

public IEnumerable<Person> GetPeople(string name)
{
    var queryCommand = queryPeopleByName.Clone();

    queryCommand.Parameters["name"] = name;

    foreach (var row in queryCommand.ExecuteReader().ToEnumerable())
    {
        yield return new Person()
        {
            Name  = row.GetString(NameIndex),
            Age   = row.GetInt32(RowIndex),
            Email = row.GetString(EmailIndex);
        };
    }
}

}
...
var queryPeopleByName = new QueryPeopleByName(connection);
foreach (var name in new string[] { "jack", "jill", "john", "jane" })
{
foreach (var person in queryCommand.GetProple(name))
{
Console.WriteLine($"Name: {person.Name} Age: {person.Age} Email: {person.Email}");
}
}

This final example abstracted the parameter name and type as well as converted the query result to compile-time Person object instances. These patterns can provide a nice way to get some of the advantages of an ORM without extra runtime overhead.

Constructors

PreparedCommand(NpgsqlConnection, string, Dictionary<string, NpgsqlDbType>, bool)

Constructor.

Declaration

public PreparedCommand(NpgsqlConnection connection, string sqlText, Dictionary<string, NpgsqlDbType> paramDefinitions = null, bool prepareNow = false)

Parameters

Type	Name	Description
NpgsqlConnection	connection	The open Postgres connection.
string	sqlText	The command SQL.
Dictionary<string, NpgsqlDbType>	paramDefinitions	Optional parameter name and type definitions. note Not all possible parameter types are supported by the common ones are at this time.
bool	prepareNow	Optionally specifies that the command is to be prepared immediately rather than waiting for it's first execution (the default).

Properties

CommandText

Returns the command text.

Declaration

public string CommandText { get; }

Property Value

Type	Description
string

CommandTimeout

The command timeout in seconds. This defaults to 30 seconds.

Declaration

public int CommandTimeout { get; set; }

Property Value

Type	Description
int

Parameters

Returns the command parameters.

note

The collection returned should be considered tob read-only and must not be modified.

Declaration

public NpgsqlParameterCollection Parameters { get; }

Property Value

Type	Description
NpgsqlParameterCollection

Methods

Clone(NpgsqlTransaction)

Prepares the underlying command if it hasn't already been prepared and then creates a clone of the command that can be executed after parameter values are set when necessary.

Declaration

public NpgsqlCommand Clone(NpgsqlTransaction transaction = null)

Parameters

Type	Name	Description
NpgsqlTransaction	transaction	Optional transaction.

Returns

Type	Description
NpgsqlCommand