public void UseAcrossConnectionChange([Values(PrepareOrNot.Prepared, PrepareOrNot.NotPrepared)] PrepareOrNot prepare)
{
using (var c = new NpgsqlConnection(ConnectionString))
{
using (var cmd = c.CreateCommand())
{
c.Open();
cmd.CommandText = "SELECT 1";
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
cmd.Connection = Conn;
Assert.That(cmd.IsPrepared, Is.False);
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1));
}
}
}
public async Task SingleRow([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
{
return;
}
using var conn = await OpenConnectionAsync();
using var cmd = new NpgsqlCommand("SELECT 1, 2 UNION SELECT 3, 4", conn);
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow);
Assert.That(() => reader.GetInt32(0), Throws.Exception.TypeOf <InvalidOperationException>());
Assert.That(reader.Read(), Is.True);
Assert.That(reader.GetInt32(0), Is.EqualTo(1));
Assert.That(reader.Read(), Is.False);
}
public void HasRows([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var command = new NpgsqlCommand("SELECT 1; SELECT * FROM data WHERE name='does_not_exist'", conn);
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var dr = command.ExecuteReader())
{
Assert.That(dr.HasRows, Is.True);
Assert.That(dr.HasRows, Is.True);
Assert.That(dr.Read(), Is.True);
Assert.That(dr.HasRows, Is.True);
Assert.That(dr.Read(), Is.False);
dr.NextResult();
Assert.That(dr.HasRows, Is.False);
}
}
}
public async Task SchemaOnly([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
// if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
// return;
using (var conn = await OpenConnectionAsync())
{
await using var _ = await CreateTempTable(conn, "name TEXT", out var table);
var query = $@"
SELECT 1 AS some_column;
UPDATE {table} SET name='yo' WHERE 1=0;
SELECT 1 AS some_other_column, 2";
using (var cmd = new NpgsqlCommand(query, conn))
{
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
{
Assert.That(reader.Read(), Is.False);
var t = reader.GetSchemaTable() !;
Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_column"));
Assert.That(reader.NextResult(), Is.True);
Assert.That(reader.Read(), Is.False);
t = reader.GetSchemaTable() !;
Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_other_column"));
Assert.That(t.Rows[1]["ColumnName"], Is.EqualTo("?column?"));
Assert.That(reader.NextResult(), Is.False);
}
// Close reader in the middle
using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
reader.Read();
}
}
}
public async Task RollbackAsync([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var tx = conn.BeginTransaction();
var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES ('X')", conn, tx);
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
cmd.ExecuteNonQuery();
Assert.That(conn.ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(1));
await tx.RollbackAsync();
Assert.That(tx.IsCompleted);
Assert.That(conn.ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(0));
Assert.That(() => tx.Connection, Throws.Exception.TypeOf <InvalidOperationException>());
tx.Dispose();
Assert.That(() => tx.Connection, Throws.Exception.TypeOf <ObjectDisposedException>());
}
}
public async Task TooManyParameters([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
{
return;
}
using var conn = await OpenConnectionAsync();
using var cmd = new NpgsqlCommand { Connection = conn };
var sb = new StringBuilder("SOME RANDOM SQL ");
for (var i = 0; i < ushort.MaxValue + 1; i++)
{
var paramName = "p" + i;
cmd.Parameters.Add(new NpgsqlParameter(paramName, 8));
if (i > 0)
{
sb.Append(", ");
}
sb.Append('@');
sb.Append(paramName);
}
cmd.CommandText = sb.ToString();
if (prepare == PrepareOrNot.Prepared)
{
Assert.That(() => cmd.Prepare(), Throws.Exception
.InstanceOf <NpgsqlException>()
.With.Message.EqualTo("A statement cannot have more than 65535 parameters"));
}
else
{
Assert.That(() => cmd.ExecuteNonQueryAsync(), Throws.Exception
.InstanceOf <NpgsqlException>()
.With.Message.EqualTo("A statement cannot have more than 65535 parameters"));
}
}
public void ParameterizedSelectMoneyArrayRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
decimal[] data = new decimal[1000];
for (int i = 0; i < 1000; i++)
{
data[i] = i;
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Money | NpgsqlTypes.NpgsqlDbType.Array;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectBigIntRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data1, :data2, :data3, :data4, :data5, :data6, :data7, :data8, :data9, :data10";
for (int i = 0; i < 10; i++)
{
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint;
dataParameter.ParameterName = string.Format("data{0}", i + 1);
command.Parameters.Add(dataParameter);
dataParameter.Value = 0xFFFFFFFFFFFFFFF;
}
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
using (IDataReader r = command.ExecuteReader())
{
r.Read();
Int64 r10 = (Int64)r[9];
}
metrics.IncrementIterations();
}
}
}
}
public async Task UseAcrossConnectionChange([Values(PrepareOrNot.Prepared, PrepareOrNot.NotPrepared)] PrepareOrNot prepare)
{
if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
{
return;
}
using (var conn1 = await OpenConnectionAsync())
using (var conn2 = await OpenConnectionAsync())
using (var cmd = new NpgsqlCommand("SELECT 1", conn1))
{
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
cmd.Connection = conn2;
Assert.That(cmd.IsPrepared, Is.False);
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(1));
}
}
public async Task SendUnknown([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
{
if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
{
return;
}
using var conn = await OpenConnectionAsync();
using var cmd = new NpgsqlCommand("SELECT @p::TIMESTAMP", conn);
cmd.CommandText = "SELECT @p::TIMESTAMP";
cmd.Parameters.Add(new NpgsqlParameter("p", NpgsqlDbType.Unknown)
{
Value = "2008-1-1"
});
if (prepare == PrepareOrNot.Prepared)
{
cmd.Prepare();
}
using var reader = await cmd.ExecuteReaderAsync();
reader.Read();
Assert.That(reader.GetValue(0), Is.EqualTo(new DateTime(2008, 1, 1)));
}
public void ParameterizedSelectDecimalRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Numeric;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = 12345678.12345678;
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
if (prepare == PrepareOrNot.Prepared) {
command.Prepare();
}
var data2 = (decimal)command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedInsert(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO data (field_text) values (:data)";
IDbDataParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.DbType = DbType.String;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
if (prepare == PrepareOrNot.Prepared)
command.Prepare();
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
dataParameter.Value = "yo";
command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectMoneyArrayRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
decimal[] data = new decimal[1000];
for (int i = 0 ; i < 1000 ; i++)
{
data[i] = i;
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Money | NpgsqlTypes.NpgsqlDbType.Array;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectByteaArrayRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
byte[] bytes = new byte[50000];
for (int i = 0 ; i < bytes.Length ; i++)
{
bytes[i] = (byte)(i % 255);
}
byte[][] data = new byte[][] { bytes, bytes };
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea | NpgsqlTypes.NpgsqlDbType.Array;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
try
{
command.ExecuteScalar();
}
catch (NpgsqlException e)
{
if (e.Message.Length > 500)
{
Console.WriteLine(string.Format("Error: {0}", e.Message.Substring(0, 100)));
throw new Exception(e.Message.Substring(0, 500));
}
else
{
throw;
}
}
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectTextArrayRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
string[] data = new string[1000];
for (int i = 0 ; i < 1000 ; i++)
{
data[i] = string.Format("A string with the number {0}, a ', a \", and a \\.", i);
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text | NpgsqlTypes.NpgsqlDbType.Array;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare == PrepareOrNot.Prepared)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
try
{
command.ExecuteScalar();
}
catch (NpgsqlException e)
{
if (e.Message.Length > 500)
{
Console.WriteLine(string.Format("Error: {0}", e.Message.Substring(0, 100)));
throw new Exception(e.Message.Substring(0, 500));
}
else
{
throw;
}
}
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectBigIntRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data1, :data2, :data3, :data4, :data5, :data6, :data7, :data8, :data9, :data10";
for (int i = 0 ; i < 10 ; i++)
{
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint;
dataParameter.ParameterName = string.Format("data{0}", i + 1);
command.Parameters.Add(dataParameter);
dataParameter.Value = 0xFFFFFFFFFFFFFFF;
}
if (prepare == PrepareOrNot.Prepared) {
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
using (IDataReader r = command.ExecuteReader())
{
r.Read();
Int64 r10 = (Int64)r[9];
}
metrics.IncrementIterations();
}
}
}
}
public void ParameterizedSelectByteaRoundTrip(PrepareOrNot prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
byte[] data = new byte[100000];
for (int i = 0 ; i < data.Length ; i++)
{
data[i] = (byte)(i % 255);
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare == PrepareOrNot.Prepared) {
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (! metrics.TimesUp)
{
var data2 = (byte[])command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
