{

#region Multiple Statements in a Command

/// <summary>

/// Tests various configurations of queries and non-queries within a multiquery

/// </summary>

[Test]

[TestCase(new[] { true }, TestName = "SingleQuery")]

[TestCase(new[] { false }, TestName = "SingleNonQuery")]

[TestCase(new[] { true, true }, TestName = "TwoQueries")]

[TestCase(new[] { false, false }, TestName = "TwoNonQueries")]

[TestCase(new[] { false, true }, TestName = "NonQueryQuery")]

[TestCase(new[] { true, false }, TestName = "QueryNonQuery")]

public void MultipleStatements(bool[] queries)

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");

var sb = new StringBuilder();

foreach (var query in queries)

sb.Append(query ? "SELECT 1;" : "UPDATE data SET name='yo' WHERE 1=0;");

var sql = sb.ToString();

foreach (var prepare in new[] {false, true})

{

using (var cmd = new NpgsqlCommand(sql, conn))

{

if (prepare)

cmd.Prepare();

using (var reader = cmd.ExecuteReader())

{

var numResultSets = queries.Count(q => q);

for (var i = 0; i < numResultSets; i++)

{

Assert.That(reader.Read(), Is.True);

Assert.That(reader[0], Is.EqualTo(1));

Assert.That(reader.NextResult(), Is.EqualTo(i != numResultSets - 1));

}

}

}

}

}

}

[Test]

public void MultipleStatementsWithParameters([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SELECT @p1; SELECT @p2", conn))

{

var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer);

var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Text);

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

if (prepare == PrepareOrNot.Prepared)

cmd.Prepare();

p1.Value = 8;

p2.Value = "foo";

using (var reader = cmd.ExecuteReader())

{

Assert.That(reader.Read(), Is.True);

Assert.That(reader.GetInt32(0), Is.EqualTo(8));

Assert.That(reader.NextResult(), Is.True);

Assert.That(reader.Read(), Is.True);

Assert.That(reader.GetString(0), Is.EqualTo("foo"));

Assert.That(reader.NextResult(), Is.False);

}

}

}

}

[Test]

public void MultipleStatementsSingleRow([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SELECT 1; SELECT 2", conn))

{

if (prepare == PrepareOrNot.Prepared)

cmd.Prepare();

using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))

{

Assert.That(reader.Read(), Is.True);

Assert.That(reader.GetInt32(0), Is.EqualTo(1));

Assert.That(reader.Read(), Is.False);

Assert.That(reader.NextResult(), Is.False);

}

}

}

}

[Test, Description("Makes sure a later command can depend on an earlier one")]

[IssueLink("https://github.com/npgsql/npgsql/issues/641")]

public void MultipleStatementsWithDependencies()

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("CREATE TABLE pg_temp.data (a INT); INSERT INTO pg_temp.data (a) VALUES (8)");

Assert.That(conn.ExecuteScalar("SELECT * FROM pg_temp.data"), Is.EqualTo(8));

}

}

[Test, Description("Forces async write mode when the first statement in a multi-statement command is big")]

[IssueLink("https://github.com/npgsql/npgsql/issues/641")]

public void MultipleStatementsLargeFirstCommand()

{

using (var conn = OpenConnection())

using (var cmd = new NpgsqlCommand($"SELECT repeat('X', {conn.Settings.WriteBufferSize}); SELECT @p", conn))

{

var expected1 = new string('X', conn.Settings.WriteBufferSize);

var expected2 = new string('Y', conn.Settings.WriteBufferSize);

cmd.Parameters.AddWithValue("p", expected2);

using (var reader = cmd.ExecuteReader())

{

reader.Read();

Assert.That(reader.GetString(0), Is.EqualTo(expected1));

reader.NextResult();

reader.Read();

Assert.That(reader.GetString(0), Is.EqualTo(expected2));

}

}

}

#endregion

#region Prepare() corner cases

[Test]

public void PrepareMultipleCommandsWithParameters()

{

using (var conn = OpenConnection())

{

using (var cmd1 = new NpgsqlCommand("SELECT @p1;", conn))

using (var cmd2 = new NpgsqlCommand("SELECT @p1; SELECT @p2;", conn))

{

var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer);

var p21 = new NpgsqlParameter("p1", NpgsqlDbType.Text);

var p22 = new NpgsqlParameter("p2", NpgsqlDbType.Text);

cmd1.Parameters.Add(p1);

cmd2.Parameters.Add(p21);

cmd2.Parameters.Add(p22);

cmd1.Prepare();

cmd2.Prepare();

p1.Value = 8;

p21.Value = "foo";

p22.Value = "bar";

using (var reader1 = cmd1.ExecuteReader())

{

Assert.That(reader1.Read(), Is.True);

Assert.That(reader1.GetInt32(0), Is.EqualTo(8));

}

using (var reader2 = cmd2.ExecuteReader())

{

Assert.That(reader2.Read(), Is.True);

Assert.That(reader2.GetString(0), Is.EqualTo("foo"));

Assert.That(reader2.NextResult(), Is.True);

Assert.That(reader2.Read(), Is.True);

Assert.That(reader2.GetString(0), Is.EqualTo("bar"));

}

}

}

}

#endregion

#region Timeout

[Test, Description("Checks that CommandTimeout gets enforced as a socket timeout")]

[IssueLink("https://github.com/npgsql/npgsql/issues/327")]

[Timeout(10000)]

public void Timeout()

{

// Mono throws a socket exception with WouldBlock instead of TimedOut (see #1330)

var isMono = Type.GetType("Mono.Runtime") != null;

using (var conn = OpenConnection(ConnectionString + ";CommandTimeout=1"))

using (var cmd = CreateSleepCommand(conn, 10))

{

Assert.That(() => cmd.ExecuteNonQuery(), Throws.Exception

.TypeOf<NpgsqlException>()

.With.InnerException.TypeOf<IOException>()

.With.InnerException.InnerException.TypeOf<SocketException>()

.With.InnerException.InnerException.Property(nameof(SocketException.SocketErrorCode)).EqualTo(isMono ? SocketError.WouldBlock : SocketError.TimedOut)

);

Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Broken));

}

}

[Test]

public void TimeoutFromConnectionString()

{

Assert.That(NpgsqlConnector.MinimumInternalCommandTimeout, Is.Not.EqualTo(NpgsqlCommand.DefaultTimeout));

var timeout = NpgsqlConnector.MinimumInternalCommandTimeout;

var connString = new NpgsqlConnectionStringBuilder(ConnectionString)

{

CommandTimeout = timeout

}.ToString();

using (var conn = new NpgsqlConnection(connString))

{

var command = new NpgsqlCommand("SELECT 1", conn);

conn.Open();

Assert.That(command.CommandTimeout, Is.EqualTo(timeout));

command.CommandTimeout = 10;

command.ExecuteScalar();

Assert.That(command.CommandTimeout, Is.EqualTo(10));

}

}

[Test, IssueLink("https://github.com/npgsql/npgsql/issues/395")]

public void TimeoutSwitchConnection()

{

using (var conn = new NpgsqlConnection(ConnectionString))

{

if (conn.CommandTimeout >= 100 && conn.CommandTimeout < 105)

TestUtil.IgnoreExceptOnBuildServer("Bad default command timeout");

}

using (var c1 = OpenConnection(ConnectionString + ";CommandTimeout=100"))

{

using (var cmd = c1.CreateCommand())

{

Assert.That(cmd.CommandTimeout, Is.EqualTo(100));

using (var c2 = new NpgsqlConnection(ConnectionString + ";CommandTimeout=101"))

{

cmd.Connection = c2;

Assert.That(cmd.CommandTimeout, Is.EqualTo(101));

}

cmd.CommandTimeout = 102;

using (var c2 = new NpgsqlConnection(ConnectionString + ";CommandTimeout=101"))

{

cmd.Connection = c2;

Assert.That(cmd.CommandTimeout, Is.EqualTo(102));

}

}

}

}

#endregion

#region Cancel

[Test, Description("Basic cancellation scenario")]

[Timeout(6000)]

public void Cancel()

{

using (var conn = OpenConnection())

{

using (var cmd = CreateSleepCommand(conn, 5))

{

Task.Factory.StartNew(() =>

{

Thread.Sleep(300);

cmd.Cancel();

});

Assert.That(() => cmd.ExecuteNonQuery(), Throws

.TypeOf<PostgresException>()

.With.Property(nameof(PostgresException.SqlState)).EqualTo("57014")

);

}

}

}

[Test, Description("Cancels an async query with the cancellation token")]

[Ignore("Flaky on CoreCLR")]

public void CancelAsync()

{

var cancellationSource = new CancellationTokenSource();

using (var conn = OpenConnection())

using (var cmd = CreateSleepCommand(conn))

{

// ReSharper disable once MethodSupportsCancellation

Task.Factory.StartNew(() =>

{

Thread.Sleep(300);

cancellationSource.Cancel();

});

var t = cmd.ExecuteNonQueryAsync(cancellationSource.Token);

Assert.That(async () => await t.ConfigureAwait(false), Throws.Exception.TypeOf<NpgsqlException>());

// Since cancellation triggers an NpgsqlException and not a TaskCanceledException, the task's state

// is Faulted and not Canceled. This isn't amazing, but we have to choose between this and the

// principle of always raising server/network errors as NpgsqlException for easy catching.

Assert.That(t.IsFaulted);

Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Broken));

}

}

[Test, Description("Check that cancel only affects the command on which its was invoked")]

[Timeout(3000)]

public void CancelCrossCommand()

{

using (var conn = OpenConnection())

{

using (var cmd1 = CreateSleepCommand(conn, 2))

using (var cmd2 = new NpgsqlCommand("SELECT 1", conn))

{

var cancelTask = Task.Factory.StartNew(() =>

{

Thread.Sleep(300);

cmd2.Cancel();

});

Assert.That(() => cmd1.ExecuteNonQuery(), Throws.Nothing);

cancelTask.Wait();

}

}

}

#endregion

#region Cursors

[Test]

public void CursorStatement()

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");

using (var t = conn.BeginTransaction())

{

for (var x = 0; x < 5; x++)

conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES ('X')");

var i = 0;

var command = new NpgsqlCommand("DECLARE TE CURSOR FOR SELECT * FROM DATA", conn);

command.ExecuteNonQuery();

command.CommandText = "FETCH FORWARD 3 IN TE";

var dr = command.ExecuteReader();

while (dr.Read())

i++;

Assert.AreEqual(3, i);

dr.Close();

i = 0;

command.CommandText = "FETCH BACKWARD 1 IN TE";

var dr2 = command.ExecuteReader();

while (dr2.Read())

i++;

Assert.AreEqual(1, i);

dr2.Close();

command.CommandText = "close te;";

command.ExecuteNonQuery();

}

}

}

[Test]

public void CursorMoveRecordsAffected()

{

using (var connection = OpenConnection())

using (var transaction = connection.BeginTransaction())

{

var command = new NpgsqlCommand("DECLARE curs CURSOR FOR SELECT * FROM (VALUES (1), (2), (3)) as t", connection);

command.ExecuteNonQuery();

command.CommandText = "MOVE FORWARD ALL IN curs";

var count = command.ExecuteNonQuery();

Assert.AreEqual(3, count);

}

}

#endregion

#region CommandBehavior.CloseConnection

[Test, IssueLink("https://github.com/npgsql/npgsql/issues/693")]

public void CloseConnection()

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SELECT 1", conn))

using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))

while (reader.Read()) {}

Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));

}

}

[Test, IssueLink("https://github.com/npgsql/npgsql/issues/1194")]

public void CloseConnectionWithOpenReaderWithCloseConnection()

{

using (var conn = OpenConnection())

{

var cmd = new NpgsqlCommand("SELECT 1", conn);

var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

var wasClosed = false;

reader.ReaderClosed += (sender, args) => { wasClosed = true; };

conn.Close();

Assert.That(wasClosed, Is.True);

}

}

[Test]

public void CloseConnectionWithException()

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SE", conn))

Assert.That(() => cmd.ExecuteReader(CommandBehavior.CloseConnection), Throws.Exception.TypeOf<PostgresException>());

Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));

}

}

#endregion

[Test]

public void SingleRow([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SELECT 1, 2 UNION SELECT 3, 4", conn))

{

if (prepare == PrepareOrNot.Prepared)

cmd.Prepare();

using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))

{

Assert.That(reader.Read(), Is.True);

Assert.That(reader.Read(), Is.False);

}

}

}

}

[Test, Description("Makes sure writing an unset parameter isn't allowed")]

public void ParameterUnset()

{

using (var conn = OpenConnection())

{

using (var cmd = new NpgsqlCommand("SELECT @p", conn))

{

cmd.Parameters.Add(new NpgsqlParameter("@p", NpgsqlDbType.Integer));

Assert.That(() => cmd.ExecuteScalar(), Throws.Exception.TypeOf<InvalidCastException>());

}

}

}

[Test]

public void ParametersGetName()

{

var command = new NpgsqlCommand();

// Add parameters.

command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean));

command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32));

command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime));

command.Parameters.Add(new NpgsqlParameter("Parameter4", DbType.DateTime));

var idbPrmtr = command.Parameters["Parameter1"];

Assert.IsNotNull(idbPrmtr);

command.Parameters[0].Value = 1;

// Get by indexers.

Assert.AreEqual("Parameter1", command.Parameters["Parameter1"].ParameterName);

Assert.AreEqual("Parameter2", command.Parameters["Parameter2"].ParameterName);

Assert.AreEqual("Parameter3", command.Parameters["Parameter3"].ParameterName);

//Assert.AreEqual("Parameter4", command.Parameters["Parameter4"].ParameterName); //Should this work?

Assert.AreEqual("Parameter1", command.Parameters[0].ParameterName);

Assert.AreEqual("Parameter2", command.Parameters[1].ParameterName);

Assert.AreEqual("Parameter3", command.Parameters[2].ParameterName);

Assert.AreEqual("Parameter4", command.Parameters[3].ParameterName);

}

[Test]

public void SameParamMultipleTimes()

{

using (var conn = OpenConnection())

using (var cmd = new NpgsqlCommand("SELECT @p1, @p1", conn))

{

cmd.Parameters.AddWithValue("@p1", 8);

using (var reader = cmd.ExecuteReader())

{

reader.Read();

Assert.That(reader[0], Is.EqualTo(8));

Assert.That(reader[1], Is.EqualTo(8));

}

}

}

[Test]

public void GenericParameter()

{

using (var conn = OpenConnection())

using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3,@p4", conn))

{

cmd.Parameters.Add(new NpgsqlParameter<int>("p1", 8));

cmd.Parameters.Add(new NpgsqlParameter<short>("p2", 8) { NpgsqlDbType = NpgsqlDbType.Integer });

cmd.Parameters.Add(new NpgsqlParameter<string>("p3", "hello"));

cmd.Parameters.Add(new NpgsqlParameter<char[]>("p4", new[] { 'f', 'o', 'o' }));

using (var reader = cmd.ExecuteReader())

{

reader.Read();

Assert.That(reader.GetInt32(0), Is.EqualTo(8));

Assert.That(reader.GetInt32(1), Is.EqualTo(8));

Assert.That(reader.GetString(2), Is.EqualTo("hello"));

Assert.That(reader.GetString(3), Is.EqualTo("foo"));

}

}

}

[Test]

public void EmptyQuery()

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("");

conn.ExecuteNonQuery(";");

}

}

[Test]

public void ExecuteScalar()

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");

using (var command = new NpgsqlCommand("SELECT name FROM data", conn))

{

Assert.That(command.ExecuteScalar(), Is.Null);

conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES (NULL)");

Assert.That(command.ExecuteScalar(), Is.EqualTo(DBNull.Value));

conn.ExecuteNonQuery(@"TRUNCATE data");

for (var i = 0; i < 2; i++)

conn.ExecuteNonQuery("INSERT INTO data (name) VALUES ('X')");

Assert.That(command.ExecuteScalar(), Is.EqualTo("X"));

}

}

}

[Test]

public void ExecuteNonQuery()

{

using (var conn = OpenConnection())

{

conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");

using (var cmd = new NpgsqlCommand { Connection = conn })

{

cmd.CommandText = "INSERT INTO data (name) VALUES ('John')";

Assert.That(cmd.ExecuteNonQuery(), Is.EqualTo(1));

cmd.CommandText = "INSERT INTO data (name) VALUES ('John'); INSERT INTO data (name) VALUES ('John')";

Assert.That(cmd.ExecuteNonQuery(), Is.EqualTo(2));

cmd.CommandText = $"INSERT INTO data (name) VALUES ('{new string('x', conn.Settings.WriteBufferSize)}')";

Assert.That(cmd.ExecuteNonQuery(), Is.EqualTo(1));

cmd.Parameters.AddWithValue("not_used", DBNull.Value);

Assert.That(cmd.ExecuteNonQuery(), Is.EqualTo(1));

}

}

}

[Test, Description("Makes sure a command is unusable after it is disposed")]

public void Dispose()

{

using (var conn = OpenConnection())

{

var cmd = new NpgsqlCommand("SELECT 1", conn);

cmd.Dispose();

Assert.That(() => cmd.ExecuteScalar(), Throws.Exception.TypeOf<ObjectDisposedException>());

Assert.That(() => cmd.ExecuteNonQuery(), Throws.Exception.TypeOf<ObjectDisposedException>());

Assert.That(() => cmd.ExecuteReader(), Throws.Exception.TypeOf<ObjectDisposedException>());

Assert.That(() => cmd.Prepare(), Throws.Exception.TypeOf<ObjectDisposedException>());

}

}

[Test, Description("Disposing a command with an open reader does not close the reader. This is the SqlClient behavior.")]

public void DisposeCommandDoesNotCloseReader()

{

using (var conn = OpenConnection())

{

var cmd = new NpgsqlCommand("SELECT 1, 2", conn);

cmd.ExecuteReader();

cmd.Dispose();

cmd = new NpgsqlCommand("SELECT 3", conn);

Assert.That(() => cmd.ExecuteScalar(), Throws.Exception.TypeOf<NpgsqlOperationInProgressException>());

}

}

[Test]

public void StringEscapeSyntax()

{

using (var conn = OpenConnection())

{

//the next command will fail on earlier postgres versions, but that is not a bug in itself.

try

{

conn.ExecuteNonQuery("set standard_conforming_strings=off;set escape_string_warning=off");

}

catch

{

}

var cmdTxt = "select :par";

var command = new NpgsqlCommand(cmdTxt, conn);

var arrCommand = new NpgsqlCommand(cmdTxt, conn);

var testStrPar = "This string has a single quote: ', a double quote: \", and a backslash: \\";

var testArrPar = new string[,] {{testStrPar, ""}, {testStrPar, testStrPar}};

command.Parameters.AddWithValue(":par", testStrPar);

using (var rdr = command.ExecuteReader())

{

rdr.Read();

Assert.AreEqual(rdr.GetString(0), testStrPar);

}

arrCommand.Parameters.AddWithValue(":par", testArrPar);

using (var rdr = arrCommand.ExecuteReader())

{

rdr.Read();

Assert.AreEqual(((string[,]) rdr.GetValue(0))[0, 0], testStrPar);

}

try //the next command will fail on earlier postgres versions, but that is not a bug in itself.

{

conn.ExecuteNonQuery("set standard_conforming_strings=on;set escape_string_warning=on");

}

catch

{

}

using (var rdr = command.ExecuteReader())

{

rdr.Read();

Assert.AreEqual(rdr.GetString(0), testStrPar);

}

using (var rdr = arrCommand.ExecuteReader())

{

rdr.Read();

Assert.AreEqual(((string[,]) rdr.GetValue(0))[0, 0], testStrPar);

}

}

}

[Test]

public void ParameterAndOperatorUnclear()

{

using (var conn = OpenConnection())

{

//Without parenthesis the meaning of [, . and potentially other characters is

//a syntax error. See comment in NpgsqlCommand.GetClearCommandText() on "usually-redundant parenthesis".

using (var command = new NpgsqlCommand("select :arr[2]", conn))

{

command.Parameters.AddWithValue(":arr", new int[] {5, 4, 3, 2, 1});

using (var rdr = command.ExecuteReader())

{

rdr.Read();

Assert.AreEqual(rdr.GetInt32(0), 4);

}

}

}

}

[Test]

public void StatementMappedOutputParameters()

{

using (var conn = OpenConnection())

{

var command = new NpgsqlCommand("select 3, 4 as param1, 5 as param2, 6;", conn);

var p = new NpgsqlParameter("param2", NpgsqlDbType.Integer);

p.Direction = ParameterDirection.Output;

p.Value = -1;

command.Parameters.Add(p);

p = new NpgsqlParameter("param1", NpgsqlDbType.Integer);

p.Direction = ParameterDirection.Output;

p.Value = -1;

command.Parameters.Add(p);