public void ExplicitEnlist() { using (var conn = new NpgsqlConnection(ConnectionString)) using (var scope = new TransactionScope()) { conn.Open(); conn.EnlistTransaction(Transaction.Current); Assert.That(conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES('test')"), Is.EqualTo(1)); scope.Complete(); } AssertNoPreparedTransactions(); using (var conn = OpenConnection()) Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(1)); }
public void Rollback() { var connectionString = ConnectionString + ";enlist=true"; using (var scope = new TransactionScope()) using (var conn = new NpgsqlConnection(connectionString)) { conn.Open(); Assert.That(conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES('test')"), Is.EqualTo(1)); // No commit } AssertNoPreparedTransactions(); using (var conn = OpenConnection()) Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(0)); }
public void ManyOpenClose() { // The connector's _sentRfqPrependedMessages is a byte, too many open/closes made it overflow for (var i = 0; i < 255; i++) { using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); } } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); } using (var conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1)); } }
public void ChangeDatabaseDoesNotAffectOtherConnections() { using (var conn1 = new NpgsqlConnection(ConnectionString)) using (var conn2 = new NpgsqlConnection(ConnectionString)) { // Connection 1 changes database conn1.Open(); conn1.ChangeDatabase("template1"); Assert.That(conn1.ExecuteScalar("SELECT current_database()"), Is.EqualTo("template1")); // Connection 2's database should not changed conn2.Open(); Assert.That(conn2.ExecuteScalar("SELECT current_database()"), Is.Not.EqualTo(conn1.Database)); } }
public void BreakConnectorInPool(bool keepAlive) { var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { MaxPoolSize = 1 }; if (keepAlive) csb.KeepAlive = 1; using (var conn = new NpgsqlConnection(csb)) { conn.Open(); var connectorId = conn.ProcessID; conn.Close(); // Use another connection to kill the connector currently in the pool using (var conn2 = OpenConnection()) conn2.ExecuteNonQuery($"SELECT pg_terminate_backend({connectorId})"); // Allow some time for the terminate to occur Thread.Sleep(2000); conn.Open(); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open)); if (keepAlive) { Assert.That(conn.ProcessID, Is.Not.EqualTo(connectorId)); Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1)); } else { Assert.That(conn.ProcessID, Is.EqualTo(connectorId)); Assert.That(() => conn.ExecuteScalar("SELECT 1"), Throws.Exception.TypeOf<NpgsqlException>()); } } }
/// <summary> /// Execute scalar /// </summary> /// <param name="conn"></param> /// <param name="cmdText"></param> /// <param name="commandType"></param> /// <param name="transaction"></param> /// <returns></returns> public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText, CommandType commandType, NpgsqlTransaction transaction) { conn.CheckNull(nameof(conn)); return(conn.ExecuteScalar(cmdText, null, commandType, transaction)); }
/// <summary> /// Execute scalar /// </summary> /// <param name="conn"></param> /// <param name="cmdText"></param> /// <returns></returns> public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText) { conn.CheckNull(nameof(conn)); return(conn.ExecuteScalar(cmdText, null, CommandType.Text, null)); }
/// <summary> /// Execute scalar /// </summary> /// <param name="conn"></param> /// <param name="cmdText"></param> /// <param name="parameters"></param> /// <param name="commandType"></param> /// <returns></returns> public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText, NpgsqlParameter[] parameters, CommandType commandType) { conn.CheckNull(nameof(conn)); return(conn.ExecuteScalar(cmdText, parameters, commandType, null)); }
/// <summary> /// Checks that the connector state is properly managed for COPY operations /// </summary> void StateAssertions(NpgsqlConnection conn) { Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Copy)); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open | ConnectionState.Fetching)); Assert.That(() => conn.ExecuteScalar("SELECT 1"), Throws.Exception.TypeOf<InvalidOperationException>()); }
static void Main(string[] args) { var store = Wireup .Init() .UsingSqlPersistence("db") .WithDialect(new PostgreSqlDialect()) .InitializeStorageEngine() .Build(); var id = Guid.NewGuid().ToString(); //Insert some events using (var stream = store.OpenStream(id)) { var events = Enumerable.Range(1, 10).Select(i => new EventMessage { Body = new Foo {Id = i, Value = "Bar"}, Headers = new Dictionary<string, object> { ["UserName"] = i < 8 ? "Bob" : "John" } }); foreach (var e in events) { stream.Add(e); stream.CommitChanges(Guid.NewGuid()); } } //Direct sql query of json using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString)) { var countOfUpdatesByJohn = con .ExecuteScalar<int>(@" select count(1) from commits, jsonb_array_elements(commits.payload) as events where streamidoriginal = @id and events->'Headers'->>'UserName' = 'John'", new { id }); } //Load events directly, bypassing neventstore using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString)) { var events = con .Query<string>(@" select payload from commits where streamidoriginal = @id order by streamrevision", new { id }) .SelectMany(json => JsonConvert.DeserializeObject<List<EventMessage>>(json, new JsonSerializerSettings { TypeNameHandling = TypeNameHandling.Auto, DefaultValueHandling = DefaultValueHandling.Ignore, MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead, NullValueHandling = NullValueHandling.Ignore })) .ToList(); } }
public void TransactionOnRecycledConnection() { // Use application name to make sure we have our very own private connection pool using (var conn = new NpgsqlConnection(ConnectionString + $";Application Name={TestUtil.GetUniqueIdentifier(nameof(TransactionOnRecycledConnection))}")) { conn.Open(); var prevConnectorId = conn.Connector.Id; conn.Close(); conn.Open(); Assert.That(conn.Connector.Id, Is.EqualTo(prevConnectorId), "Connection pool returned a different connector, can't test"); var tx = conn.BeginTransaction(); conn.ExecuteScalar("SELECT 1"); tx.Commit(); NpgsqlConnection.ClearPool(conn); } }
public void FailedTransactionOnCloseWithCustom() { var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { Pooling = true }; using (var conn = new NpgsqlConnection(csb)) { conn.Open(); var backendProcessId = conn.ProcessID; conn.BeginTransaction(); using (var badCmd = new NpgsqlCommand("SEL", conn)) { badCmd.CommandTimeout = NpgsqlCommand.DefaultTimeout + 1; Assert.That(() => badCmd.ExecuteNonQuery(), Throws.Exception.TypeOf<PostgresException>()); } // Connection now in failed transaction state, and a custom timeout is in place conn.Close(); conn.Open(); Assert.That(conn.ProcessID, Is.EqualTo(backendProcessId)); Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1)); } }