C# NpgsqlConnection.ExecuteScalar方法代碼示例

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