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(); } } } }